[Home] [Help]
278: -- Cursor to check if the constraint for which the waiver is specified is
279: -- valid
280: CURSOR c_invld_cst_name_batch IS
281: SELECT Interface_id
282: FROM amw_cst_waiver_interface
283: WHERE batch_id = p_batch_id
284: AND constraint_rev_id IS NULL
285: AND (process_flag IS NULL OR process_flag = 'N');
286:
285: AND (process_flag IS NULL OR process_flag = 'N');
286:
287: CURSOR c_invld_cst_name IS
288: SELECT Interface_id
289: FROM amw_cst_waiver_interface
290: WHERE constraint_rev_id IS NULL
291: AND (process_flag IS NULL OR process_flag = 'N');
292:
293: -- Cursor to check if the responsibility constraint has responsibility waivers
292:
293: -- Cursor to check if the responsibility constraint has responsibility waivers
294: CURSOR c_invalid_resp_cst_batch IS
295: SELECT Interface_id
296: FROM amw_cst_waiver_interface
297: WHERE type_code in ('RESPALL','RESPME','RESPSET')
298: AND object_type = 'RESP'
299: AND batch_id = p_batch_id
300: AND pk1 IS NOT NULL
302: AND (process_flag IS NULL OR process_flag = 'N');
303:
304: CURSOR c_invalid_resp_cst IS
305: SELECT Interface_id
306: FROM amw_cst_waiver_interface
307: WHERE type_code in ('RESPALL','RESPME','RESPSET')
308: AND object_type = 'RESP'
309: AND pk1 IS NOT NULL
310: AND pk2 IS NOT NULL
312:
313: -- Cursor to check if a user waiver is defined without specifing a valid user
314: CURSOR c_invalid_user_waiver_batch IS
315: SELECT Interface_id
316: FROM amw_cst_waiver_interface
317: WHERE object_type = 'USER'
318: AND batch_id = p_batch_id
319: AND pk1 IS NULL
320: AND (process_flag IS NULL OR process_flag = 'N');
320: AND (process_flag IS NULL OR process_flag = 'N');
321:
322: CURSOR c_invalid_user_waiver IS
323: SELECT Interface_id
324: FROM amw_cst_waiver_interface
325: WHERE object_type = 'USER'
326: AND pk1 IS NULL
327: AND (process_flag IS NULL OR process_flag = 'N');
328:
330: -- Cursor to check if a responsibility waiver is defined without specifing a
331: -- valid responsibility
332: CURSOR c_invalid_resp_waiver_batch IS
333: SELECT Interface_id
334: FROM amw_cst_waiver_interface
335: WHERE object_type = 'RESP'
336: AND batch_id = p_batch_id
337: AND pk1 IS NULL
338: AND pk2 IS NULL
339: AND (process_flag IS NULL OR process_flag = 'N');
340:
341: CURSOR c_invalid_resp_waiver IS
342: SELECT Interface_id
343: FROM amw_cst_waiver_interface
344: WHERE object_type = 'RESP'
345: AND pk1 IS NULL
346: AND pk2 IS NULL
347: AND (process_flag IS NULL OR process_flag = 'N');
350: -- Cursor to check if a start_date is valid. The start date should be greater
351: -- or equal to sysdate
352: CURSOR c_invalid_start_date_batch IS
353: SELECT Interface_id
354: FROM amw_cst_waiver_interface
355: WHERE TRUNC(start_date)
357: AND (process_flag IS NULL OR process_flag = 'N');
358:
357: AND (process_flag IS NULL OR process_flag = 'N');
358:
359: CURSOR c_invalid_start_date IS
360: SELECT Interface_id
361: FROM amw_cst_waiver_interface
362: WHERE TRUNC(start_date)
364:
365: -- ptulasi : 06/01/2007 : Bug 6067714:
368: -- the waiver start date should be set to constraint start date if the constraint
369: -- start date is in future or else it is set to the sysdate.
370: CURSOR c_invalid_st_date_batch IS
371: SELECT interface_id, decode(sign(sysdate-acv.start_date),1,sysdate,acv.start_date) start_date
372: FROM amw_constraints_vl acv, amw_cst_waiver_interface acwi
373: WHERE acwi.constraint_name = acv.constraint_name
374: AND acwi.constraint_name IS NOT NULL
375: AND acv.start_date IS NOT NULL
376: AND TRUNC(acwi.start_date)
378: AND (process_flag IS NULL OR process_flag = 'N');
379:
380: CURSOR c_invalid_st_date IS
381: SELECT interface_id, decode(sign(sysdate-acv.start_date),1,sysdate,acv.start_date) start_date
382: FROM amw_constraints_vl acv, amw_cst_waiver_interface acwi
383: WHERE acwi.constraint_name = acv.constraint_name
384: AND acwi.constraint_name IS NOT NULL
385: AND acv.start_date IS NOT NULL
386: AND TRUNC(acwi.start_date)
389: -- Cursor to check if a end_date is valid. The end date should be not be less
390: -- than sysdate
391: CURSOR c_invalid_end_date_batch IS
392: SELECT Interface_id
393: FROM amw_cst_waiver_interface
394: WHERE ( TRUNC(end_date)< TRUNC(start_date)
395: OR TRUNC(end_date)
397: AND (process_flag IS NULL OR process_flag = 'N');
397: AND (process_flag IS NULL OR process_flag = 'N');
398:
399: CURSOR c_invalid_end_date IS
400: SELECT Interface_id
401: FROM amw_cst_waiver_interface
402: WHERE ( TRUNC(end_date)< TRUNC(start_date)
403: OR TRUNC(end_date)
405:
408: -- This check should consider the user waivers in the interface table as well
409: -- as the user waiver allready existing in the constraint
410: CURSOR c_duplicate_user_waiver_batch IS
411: SELECT acwi.Interface_id
412: FROM amw_cst_waiver_interface acwi,
413: amw_constraint_waivers_b cstw
414: WHERE acwi.object_type = 'USER'
415: AND cstw.object_type = 'USER'
416: AND acwi.batch_id = p_batch_id
421: AND acwi.constraint_rev_id IS NOT NULL
422: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
423: UNION
424: SELECT acwi.Interface_id
425: FROM amw_cst_waiver_interface acwi
426: WHERE acwi.object_type = 'USER'
427: AND acwi.batch_id = p_batch_id
428: AND acwi.constraint_rev_id IS NOT NULL
429: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
427: AND acwi.batch_id = p_batch_id
428: AND acwi.constraint_rev_id IS NOT NULL
429: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
430: AND EXISTS ( SELECT 'Y'
431: FROM amw_cst_waiver_interface acw
432: WHERE acw.batch_id = p_batch_id
433: AND acw.object_type = 'USER'
434: AND acw.pk1 = acwi.pk1
435: AND acw.object_type = acwi.object_type
440: );
441:
442: CURSOR c_duplicate_user_waiver IS
443: SELECT acwi.Interface_id
444: FROM amw_cst_waiver_interface acwi,
445: amw_constraint_waivers_b cstw
446: WHERE acwi.object_type = 'USER'
447: AND cstw.object_type = 'USER'
448: AND cstw.constraint_rev_id= acwi.constraint_rev_id
452: AND acwi.constraint_rev_id IS NOT NULL
453: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
454: UNION
455: SELECT acwi.Interface_id
456: FROM amw_cst_waiver_interface acwi
457: WHERE acwi.object_type = 'USER'
458: AND acwi.constraint_rev_id IS NOT NULL
459: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
460: AND EXISTS ( SELECT 'Y'
457: WHERE acwi.object_type = 'USER'
458: AND acwi.constraint_rev_id IS NOT NULL
459: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
460: AND EXISTS ( SELECT 'Y'
461: FROM amw_cst_waiver_interface acw
462: WHERE acw.object_type = 'USER'
463: AND acw.pk1 = acwi.pk1
464: AND acw.object_type = acwi.object_type
465: AND acw.Interface_id <> acwi.Interface_id
472: -- This check should consider the responsibility waivers in the interface table as well
473: -- as the user waiver allready existing in the constraint
474: CURSOR c_duplicate_resp_waiver_batch IS
475: SELECT acwi.Interface_id
476: FROM amw_cst_waiver_interface acwi,
477: amw_constraint_waivers_b cstw
478: WHERE acwi.object_type = 'RESP'
479: AND cstw.object_type = 'RESP'
480: AND acwi.batch_id = p_batch_id
487: AND acwi.type_code in ('ALL','ME','SET')
488: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
489: UNION
490: SELECT acwi.Interface_id
491: FROM amw_cst_waiver_interface acwi
492: WHERE acwi.object_type = 'RESP'
493: AND acwi.batch_id = p_batch_id
494: AND acwi.constraint_rev_id IS NOT NULL
495: AND acwi.type_code in ('ALL','ME','SET')
494: AND acwi.constraint_rev_id IS NOT NULL
495: AND acwi.type_code in ('ALL','ME','SET')
496: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
497: AND EXISTS ( SELECT 'Y'
498: FROM amw_cst_waiver_interface acw
499: WHERE acw.batch_id = p_batch_id
500: AND acw.object_type = 'RESP'
501: AND acw.pk1 = acwi.pk1
502: AND acw.pk2 = acwi.pk2
508: );
509:
510: CURSOR c_duplicate_resp_waiver IS
511: SELECT acwi.Interface_id
512: FROM amw_cst_waiver_interface acwi,
513: amw_constraint_waivers_b cstw
514: WHERE acwi.object_type = 'RESP'
515: AND cstw.object_type = 'RESP'
516: AND cstw.constraint_rev_id= acwi.constraint_rev_id
522: AND acwi.type_code in ('ALL','ME','SET')
523: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
524: UNION
525: SELECT acwi.Interface_id
526: FROM amw_cst_waiver_interface acwi
527: WHERE acwi.object_type = 'RESP'
528: AND acwi.constraint_rev_id IS NOT NULL
529: AND acwi.type_code in ('ALL','ME','SET')
530: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
528: AND acwi.constraint_rev_id IS NOT NULL
529: AND acwi.type_code in ('ALL','ME','SET')
530: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
531: AND EXISTS ( SELECT 'Y'
532: FROM amw_cst_waiver_interface acw
533: WHERE acw.object_type = 'RESP'
534: AND acw.pk1 = acwi.pk1
535: AND acw.pk2 = acwi.pk2
536: AND acw.object_type = acwi.object_type
616:
617: IF p_batch_id IS NOT NULL THEN
618:
619: -- If Last_update_date is null , then set it to system date
620: UPDATE amw_cst_waiver_interface
621: SET last_update_date = SYSDATE
622: WHERE batch_id = p_batch_id
623: AND last_update_date IS NULL
624: AND (process_flag IS NULL OR process_flag = 'N');
623: AND last_update_date IS NULL
624: AND (process_flag IS NULL OR process_flag = 'N');
625:
626: -- If creation_date is null , then set it to system date
627: UPDATE amw_cst_waiver_interface
628: SET creation_date = SYSDATE
629: WHERE batch_id = p_batch_id
630: AND creation_date IS NULL
631: AND (process_flag IS NULL OR process_flag = 'N');
630: AND creation_date IS NULL
631: AND (process_flag IS NULL OR process_flag = 'N');
632:
633: -- If last_updated_by is null , then set it to logged in user id
634: UPDATE amw_cst_waiver_interface
635: SET last_updated_by = g_user_id
636: WHERE batch_id = p_batch_id
637: AND last_updated_by IS NULL
638: AND (process_flag IS NULL OR process_flag = 'N');
637: AND last_updated_by IS NULL
638: AND (process_flag IS NULL OR process_flag = 'N');
639:
640: -- If created_by is null , then set it to logged in user id
641: UPDATE amw_cst_waiver_interface
642: SET created_by = g_user_id
643: WHERE batch_id = p_batch_id
644: AND created_by IS NULL
645: AND (process_flag IS NULL OR process_flag = 'N');
644: AND created_by IS NULL
645: AND (process_flag IS NULL OR process_flag = 'N');
646:
647: -- If last_update_login is null , then set it to logged in user id
648: UPDATE amw_cst_waiver_interface
649: SET last_update_login = g_user_id
650: WHERE batch_id = p_batch_id
651: AND last_update_login IS NULL
652: AND (process_flag IS NULL OR process_flag = 'N');
660:
661: Populating of the Constraint_Rev_Id will avoid the joining of interface table
662: with Amw_Constraint_Vl to get the constraint_rev_id from constraint name
663: */
664: UPDATE amw_cst_waiver_interface acwi
665: SET acwi.constraint_rev_id = ( SELECT acv.constraint_rev_id
666: FROM amw_constraints_vl acv
667: WHERE acwi.constraint_name = acv.constraint_name
668: AND acv.start_date IS NOT NULL
681:
682: Populating of the TYPE_CODE will avoid the joining of interface table
683: with Amw_Constraint_Vl to get the TYPE_CODE from constraint name
684: */
685: UPDATE amw_cst_waiver_interface acwi
686: SET acwi.type_code = ( SELECT acv.type_code
687: FROM amw_constraints_vl acv
688: WHERE acwi.constraint_rev_id = acv.constraint_rev_id
689: AND acv.start_date IS NOT NULL
703:
704: Populating of the Pk1 will avoid the joining of interface table
705: with FND_USER to get the user_id from User_Name
706: */
707: UPDATE amw_cst_waiver_interface acwi
708: SET acwi.pk1 = ( SELECT user_id
709: FROM fnd_user usr
710: WHERE usr.user_name = acwi.user_name
711: AND usr.start_date IS NOT NULL
727:
728: Populating of the Pk2 will avoid the joining of interface table with
729: FND_APPlication to get the application_id from Application_Short_Name
730: */
731: UPDATE amw_cst_waiver_interface acwi
732: SET acwi.pk2 = ( SELECT application_id
733: FROM fnd_application appl
734: WHERE appl.Application_short_name = acwi.application_short_name)
735: WHERE acwi.batch_id = p_batch_id
749:
750: Populating of the Pk1 will avoid the joining of interface table with
751: Fnd_Responsibility_Vl to get the responsibility_id from responsibility_name
752: */
753: UPDATE amw_cst_waiver_interface acwi
754: SET acwi.pk1 = ( SELECT responsibility_id
755: FROM fnd_responsibility_vl resp
756: WHERE resp.application_id = acwi.pk2
757: AND resp.responsibility_name = acwi.responsibility_name)
767:
768: This is to indicate that a responsibility waiver is invalid when pk1 and
769: pk2 is null
770: */
771: UPDATE amw_cst_waiver_interface acwi
772: SET acwi.pk2 = NULL
773: WHERE acwi.batch_id = p_batch_id
774: AND acwi.object_type = 'RESP'
775: AND acwi.pk1 IS NULL
787: fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldcst_rec.interface_id);
788: END LOOP;
789:
790: -- ptulasi : 06/01/2007 : Bug 6067714 :
791: -- Update all the invalid start date in amw_cst_waiver_interface
792: FOR invldstdate_rec IN c_invalid_st_date_batch
793: LOOP
794: UPDATE amw_cst_waiver_interface acwi
795: SET acwi.start_date = invldstdate_rec.start_date
790: -- ptulasi : 06/01/2007 : Bug 6067714 :
791: -- Update all the invalid start date in amw_cst_waiver_interface
792: FOR invldstdate_rec IN c_invalid_st_date_batch
793: LOOP
794: UPDATE amw_cst_waiver_interface acwi
795: SET acwi.start_date = invldstdate_rec.start_date
796: WHERE acwi.interface_id=invldstdate_rec.interface_id;
797: END LOOP;
798:
875: Should not upload the constraint waivers for a constraint, if any waiver
876: is invalid.
877: So set the error flag and the status.
878: */
879: UPDATE amw_cst_waiver_interface
880: SET error_flag = 'Y',
881: interface_status = 'Please correct the invalid waiver defined for this Constraint'
882: WHERE error_flag IS NULL
883: AND batch_id = p_batch_id
882: WHERE error_flag IS NULL
883: AND batch_id = p_batch_id
884: AND (process_flag IS NULL OR process_flag = 'N')
885: AND constraint_rev_id IN ( SELECT DISTINCT constraint_rev_id
886: FROM amw_cst_waiver_interface
887: WHERE error_flag = 'Y'
888: AND batch_id = p_batch_id
889: AND (process_flag IS NULL OR process_flag = 'N') );
890:
895: constraint waiver id by executing select sequence.nextval.
896:
897: This also helps us to insert all the data in one single query.
898: */
899: UPDATE amw_cst_waiver_interface
900: SET constraint_waiver_id = amw_constraint_waiver_s.nextval
901: WHERE error_flag IS NULL
902: AND (process_flag IS NULL OR process_flag = 'N')
903: AND batch_id = p_batch_id;
941: acwi.start_date,
942: acwi.end_date,
943: acwi.constraint_waiver_id,
944: 1
945: FROM amw_cst_waiver_interface acwi
946: WHERE acwi.error_flag IS NULL
947: AND acwi.batch_id = p_batch_id
948: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
949:
973: acwi.created_by,
974: acwi.last_update_login,
975: NULL
976: FROM fnd_languages l,
977: amw_cst_waiver_interface acwi
978: WHERE l.installed_flag IN ('I', 'B')
979: AND acwi.error_flag IS NULL
980: AND acwi.batch_id = p_batch_id
981: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
985: AND t.language = l.language_code);
986:
987: IF p_del_after_import = 'Y' THEN
988: -- Delete the uploaded constraint waiver
989: DELETE FROM amw_cst_waiver_interface
990: WHERE batch_id = p_batch_id
991: AND error_flag IS NULL;
992: ELSE
993: -- Set the process_flag for valid constraint waivers
990: WHERE batch_id = p_batch_id
991: AND error_flag IS NULL;
992: ELSE
993: -- Set the process_flag for valid constraint waivers
994: UPDATE amw_cst_waiver_interface
995: SET process_flag = 'Y'
996: WHERE batch_id = p_batch_id
997: AND error_flag IS NULL;
998: END IF;
997: AND error_flag IS NULL;
998: END IF;
999: ELSE
1000: -- If Last_update_date is null , then set it to system date
1001: UPDATE amw_cst_waiver_interface
1002: SET last_update_date = SYSDATE
1003: WHERE last_update_date IS NULL
1004: AND (process_flag IS NULL OR process_flag = 'N');
1005:
1003: WHERE last_update_date IS NULL
1004: AND (process_flag IS NULL OR process_flag = 'N');
1005:
1006: -- If creation_date is null , then set it to system date
1007: UPDATE amw_cst_waiver_interface
1008: SET creation_date = SYSDATE
1009: WHERE creation_date IS NULL
1010: AND (process_flag IS NULL OR process_flag = 'N');
1011:
1009: WHERE creation_date IS NULL
1010: AND (process_flag IS NULL OR process_flag = 'N');
1011:
1012: -- If last_updated_by is null , then set it to logged in user id
1013: UPDATE amw_cst_waiver_interface
1014: SET last_updated_by = g_user_id
1015: WHERE last_updated_by IS NULL
1016: AND (process_flag IS NULL OR process_flag = 'N');
1017:
1015: WHERE last_updated_by IS NULL
1016: AND (process_flag IS NULL OR process_flag = 'N');
1017:
1018: -- If created_by is null , then set it to logged in user id
1019: UPDATE amw_cst_waiver_interface
1020: SET created_by = g_user_id
1021: WHERE created_by IS NULL
1022: AND (process_flag IS NULL OR process_flag = 'N');
1023:
1021: WHERE created_by IS NULL
1022: AND (process_flag IS NULL OR process_flag = 'N');
1023:
1024: -- If last_update_login is null , then set it to logged in user id
1025: UPDATE amw_cst_waiver_interface
1026: SET last_update_login = g_user_id
1027: WHERE last_update_login IS NULL
1028: AND (process_flag IS NULL OR process_flag = 'N');
1029:
1036:
1037: Populating of the Constraint_Rev_Id will avoid the joining of interface table
1038: with Amw_Constraint_Vl to get the constraint_rev_id from constraint name
1039: */
1040: UPDATE amw_cst_waiver_interface acwi
1041: SET acwi.constraint_rev_id = ( SELECT acv.constraint_rev_id
1042: FROM amw_constraints_vl acv
1043: WHERE acwi.constraint_name = acv.constraint_name
1044: AND acv.start_date IS NOT NULL
1056:
1057: Populating of the TYPE_CODE will avoid the joining of interface table
1058: with Amw_Constraint_Vl to get the TYPE_CODE from constraint name
1059: */
1060: UPDATE amw_cst_waiver_interface acwi
1061: SET acwi.type_code = ( SELECT acv.type_code
1062: FROM amw_constraints_vl acv
1063: WHERE acwi.constraint_rev_id = acv.constraint_rev_id
1064: AND acv.start_date IS NOT NULL
1076:
1077: Populating of the Pk1 will avoid the joining of interface table
1078: with FND_USER to get the user_id from User_Name
1079: */
1080: UPDATE amw_cst_waiver_interface acwi
1081: SET acwi.pk1 = ( SELECT user_id
1082: FROM fnd_user usr
1083: WHERE usr.user_name = acwi.user_name
1084: AND usr.start_date IS NOT NULL
1098:
1099: Populating of the Pk2 will avoid the joining of interface table with
1100: FND_APPlication to get the application_id from Application_Short_Name
1101: */
1102: UPDATE amw_cst_waiver_interface acwi
1103: SET acwi.pk2 = ( SELECT application_id
1104: FROM fnd_application appl
1105: WHERE appl.Application_short_name = acwi.application_short_name)
1106: WHERE acwi.object_type = 'RESP'
1119:
1120: Populating of the Pk1 will avoid the joining of interface table with
1121: Fnd_Responsibility_Vl to get the responsibility_id from responsibility_name
1122: */
1123: UPDATE amw_cst_waiver_interface acwi
1124: SET acwi.pk1 = ( SELECT responsibility_id
1125: FROM fnd_responsibility_vl resp
1126: WHERE resp.application_id = acwi.pk2
1127: AND resp.responsibility_name = acwi.responsibility_name)
1136:
1137: This is to indicate that a responsibility waiver is invalid when pk1 and
1138: pk2 is null
1139: */
1140: UPDATE amw_cst_waiver_interface acwi
1141: SET acwi.pk2 = NULL
1142: WHERE acwi.object_type = 'RESP'
1143: AND acwi.pk1 IS NULL
1144: AND pk2 IS NOT NULL
1155: fnd_file.put_line (fnd_file.LOG, v_error_msg || ' for interface id ' || invldcst_rec.interface_id);
1156: END LOOP;
1157:
1158: -- ptulasi : 06/01/2007 : Bug 6067714 :
1159: -- Update all the invalid start date in amw_cst_waiver_interface
1160: FOR invldstdate_rec IN c_invalid_st_date
1161: LOOP
1162: UPDATE amw_cst_waiver_interface acwi
1163: SET acwi.start_date = invldstdate_rec.start_date
1158: -- ptulasi : 06/01/2007 : Bug 6067714 :
1159: -- Update all the invalid start date in amw_cst_waiver_interface
1160: FOR invldstdate_rec IN c_invalid_st_date
1161: LOOP
1162: UPDATE amw_cst_waiver_interface acwi
1163: SET acwi.start_date = invldstdate_rec.start_date
1164: WHERE acwi.interface_id=invldstdate_rec.interface_id;
1165: END LOOP;
1166:
1244: Should not upload the constraint waivers for a constraint, if any waiver
1245: is invalid.
1246: So set the error flag and the status.
1247: */
1248: UPDATE amw_cst_waiver_interface
1249: SET error_flag = 'Y',
1250: interface_status = 'Please correct the invalid waiver defined for this Constraint'
1251: WHERE error_flag IS NULL
1252: AND (process_flag IS NULL OR process_flag = 'N')
1250: interface_status = 'Please correct the invalid waiver defined for this Constraint'
1251: WHERE error_flag IS NULL
1252: AND (process_flag IS NULL OR process_flag = 'N')
1253: AND constraint_rev_id IN ( SELECT DISTINCT constraint_rev_id
1254: FROM amw_cst_waiver_interface
1255: WHERE error_flag = 'Y');
1256:
1257: /*
1258: Set the constraint waiver id for the valid constraint waivers.
1261: constraint waiver id by executing select sequence.nextval.
1262:
1263: This also helps us to insert all the data in one single query.
1264: */
1265: UPDATE amw_cst_waiver_interface
1266: SET constraint_waiver_id = amw_constraint_waiver_s.nextval
1267: WHERE error_flag IS NULL
1268: AND (process_flag IS NULL OR process_flag = 'N');
1269:
1305: acwi.start_date,
1306: acwi.end_date,
1307: acwi.constraint_waiver_id,
1308: 1
1309: FROM amw_cst_waiver_interface acwi
1310: WHERE acwi.error_flag IS NULL
1311: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N');
1312:
1313:
1336: acwi.created_by,
1337: acwi.last_update_login,
1338: NULL
1339: FROM fnd_languages l,
1340: amw_cst_waiver_interface acwi
1341: WHERE l.installed_flag IN ('I', 'B')
1342: AND acwi.error_flag IS NULL
1343: AND (acwi.process_flag IS NULL OR acwi.process_flag = 'N')
1344: AND NOT EXISTS ( SELECT NULL
1347: AND t.language = l.language_code);
1348:
1349: IF p_del_after_import = 'Y' THEN
1350: -- Delete the uploaded constraint waiver
1351: DELETE FROM amw_cst_waiver_interface
1352: WHERE error_flag IS NULL;
1353: ELSE
1354: -- Set the process_flag for valid constraint waivers
1355: UPDATE amw_cst_waiver_interface
1351: DELETE FROM amw_cst_waiver_interface
1352: WHERE error_flag IS NULL;
1353: ELSE
1354: -- Set the process_flag for valid constraint waivers
1355: UPDATE amw_cst_waiver_interface
1356: SET process_flag = 'Y'
1357: WHERE error_flag IS NULL;
1358: END IF;
1359: END IF;
1371: -- Procedure name
1372: -- update_waiver_intf_with_error
1373: -- Purpose
1374: -- Updates error flag and interface status of
1375: -- amw_cst_waiver_interface interface table
1376: -- ===============================================================
1377: PROCEDURE update_waiver_intf_with_error (
1378: p_err_msg IN VARCHAR2,
1379: p_interface_id IN NUMBER
1378: p_err_msg IN VARCHAR2,
1379: p_interface_id IN NUMBER
1380: )
1381: IS
1382: l_interface_status amw_cst_waiver_interface.interface_status%TYPE;
1383: BEGIN
1384: SELECT interface_status
1385: INTO l_interface_status
1386: FROM amw_cst_waiver_interface
1382: l_interface_status amw_cst_waiver_interface.interface_status%TYPE;
1383: BEGIN
1384: SELECT interface_status
1385: INTO l_interface_status
1386: FROM amw_cst_waiver_interface
1387: WHERE interface_id = p_interface_id;
1388:
1389: IF l_interface_status IS NOT NULL THEN
1390: l_interface_status := l_interface_status || ' ; ';
1391: END IF;
1392:
1393: l_interface_status := l_interface_status || p_err_msg || ' ';
1394:
1395: UPDATE amw_cst_waiver_interface
1396: SET interface_status = l_interface_status,
1397: error_flag = 'Y'
1398: WHERE interface_id = p_interface_id;
1399: