[Home] [Help]
172:
173: --Reject records if asl exist when mode is Create
174: SELECT user_key ,
175: 'po_approved_supplier_list_gt' ,
176: fnd_message.get_string('PO','DUPLICATE_ASL')
177: BULK COLLECT INTO
178: l_user_key_tbl ,
179: l_entity_name ,
180: l_reject_reason
196: l_reject_reason1 := l_reject_reason;
197: --Reject records if asl doesn't exist when mode is update.
198: SELECT user_key ,
199: 'po_approved_supplier_list_gt' ,
200: fnd_message.get_string('PO','ASL_DOES_NOT_EXIST')
201: BULK COLLECT INTO
202: l_user_key_tbl ,
203: l_entity_name ,
204: l_reject_reason
222: --Reject records if attributes mode is add, asl mode is update/delete and
223: --attributes already exists for that org
224: SELECT PAAGT.user_key ,
225: 'po_asl_attributes_gt' ,
226: fnd_message.get_string('PO','DUPLICATE_ATTRIBUTES')
227: BULK COLLECT INTO
228: l_user_key_tbl ,
229: l_entity_name ,
230: l_reject_reason
247: --Reject records if attributes mode is add, asl mode is create and
248: --attributes already exists for that org, i.e., duplicate input
249: SELECT PAAGT.user_key ,
250: 'po_asl_attributes_gt' ,
251: fnd_message.get_string('PO','DUPLICATE_ATTRIBUTES')
252: BULK COLLECT INTO
253: l_user_key_tbl ,
254: l_entity_name ,
255: l_reject_reason
272: --Reject records if attributes mode is delete, asl mode is update and
273: --attributes doesn't exists for that org
274: SELECT PAAGT.user_key ,
275: 'po_asl_attributes_gt' ,
276: fnd_message.get_string('PO','ATTRIBUTES_NOT_EXIST')
277: BULK COLLECT INTO
278: l_user_key_tbl ,
279: l_entity_name ,
280: l_reject_reason
297: --Reject records if document mode is add, asl mode is update and
298: --document already exists for that org
299: SELECT DOCGT.user_key ,
300: 'po_asl_documents_gt' ,
301: fnd_message.get_string('PO','DUPLICATE_DOCUMENT')
302: BULK COLLECT INTO
303: l_user_key_tbl ,
304: l_entity_name ,
305: l_reject_reason
322: --Reject records if document mode is delete, asl mode is update and
323: --document doesn't exists for that org
324: SELECT DOCGT.user_key ,
325: 'po_asl_documents_gt' ,
326: fnd_message.get_string('PO','DOCUMENT_NOT_EXIST')
327: BULK COLLECT INTO
328: l_user_key_tbl ,
329: l_entity_name ,
330: l_reject_reason
348: --Reject records if document mode is add, asl mode is create and
349: --document already exists for that org
350: SELECT DOCGT.user_key ,
351: 'po_asl_documents_gt' ,
352: fnd_message.get_string('PO','DUPLICATE_DOCUMENT')
353: BULK COLLECT INTO
354: l_user_key_tbl ,
355: l_entity_name ,
356: l_reject_reason
373: --Reject records if authorization process mode is add, asl mode is update
374: --and authorization code or sequence already exists for that org
375: SELECT CHVGT.user_key ,
376: 'chv_authorizations_gt' ,
377: fnd_message.get_string('PO','DUPLICATE_AUTHORIZATION')
378: BULK COLLECT INTO
379: l_user_key_tbl ,
380: l_entity_name ,
381: l_reject_reason
401: --Reject records if authorization process mode is delete, asl mode is update
402: --and authorization code and sequence not exists for that org
403: SELECT CHVGT.user_key ,
404: 'chv_authorizations_gt' ,
405: fnd_message.get_string('PO','AUTHORIZATION_NOT_EXIST')
406: BULK COLLECT INTO
407: l_user_key_tbl ,
408: l_entity_name ,
409: l_reject_reason
429: --Reject records if authorization process mode is add, asl mode is create
430: --and authorization code or sequence already exists for that org
431: SELECT CHVGT.user_key ,
432: 'chv_authorizations_gt' ,
433: fnd_message.get_string('PO','DUPLICATE_AUTHORIZATION')
434: BULK COLLECT INTO
435: l_user_key_tbl ,
436: l_entity_name ,
437: l_reject_reason
463: --Reject records if capacity process mode is delete, asl mode is update
464: --and record does not exist for that org
465: SELECT CAPGT.user_key ,
466: 'po_supplier_item_capacity_gt' ,
467: fnd_message.get_string('PO','CAPACITY_NOT_EXIST')
468: BULK COLLECT INTO
469: l_user_key_tbl ,
470: l_entity_name ,
471: l_reject_reason
493: --Reject records if tolerance process mode is add, asl mode is update
494: --and record already exists for that org
495: SELECT TOLGT.user_key ,
496: 'po_supplier_item_tolerance_gt' ,
497: fnd_message.get_string('PO','DUPLICATE_TOLERANCE')
498: BULK COLLECT INTO
499: l_user_key_tbl ,
500: l_entity_name ,
501: l_reject_reason
519: --Reject records if tolerance process mode is delete, asl mode is update
520: --and record does not exist for that org
521: SELECT TOLGT.user_key ,
522: 'po_supplier_item_tolerance_gt' ,
523: fnd_message.get_string('PO','TOLERANCE_NOT_EXIST')
524: BULK COLLECT INTO
525: l_user_key_tbl ,
526: l_entity_name ,
527: l_reject_reason
546: --Reject records if tolerance process mode is add, asl mode is create
547: --and authorization code or sequence already exists for that org
548: SELECT TOLGT.user_key ,
549: 'po_supplier_item_tolerance_gt' ,
550: fnd_message.get_string('PO','DUPLICATE_TOLERANCE')
551: BULK COLLECT INTO
552: l_user_key_tbl ,
553: l_entity_name ,
554: l_reject_reason
680: FROM (
681: --Reject records if item and category both exists for the same ASL
682: SELECT user_key ,
683: 'po_approved_supplier_list_gt' AS entity ,
684: fnd_message.get_string('PO','ITEM_CATEGORY_BOTH_EXIST') AS msg
685: FROM po_approved_supplier_list_gt ASLGT
686: WHERE ASLGT.item_id IS NOT NULL
687: AND ASLGT.category_id IS NOT NULL
688:
689: UNION ALL
690: --Reject records if item and category both empty
691: SELECT user_key ,
692: 'po_approved_supplier_list_gt' AS entity ,
693: fnd_message.get_string('PO','ITEM_CATEGORY_BOTH_EMPTY') AS msg
694: FROM po_approved_supplier_list_gt ASLGT
695: WHERE (ASLGT.item_id IS NULL
696: AND ASLGT.category_id IS NULL)
697: OR (Trim(ASLGT.item_id) IS NULL
700: UNION ALL
701: --Reject records if vendor business type is empty or null
702: SELECT user_key ,
703: 'po_approved_supplier_list_gt' AS entity ,
704: fnd_message.get_string('PO','INVALID_BUSINESS_TYPE') AS msg
705: FROM po_approved_supplier_list_gt ASLGT
706: WHERE ASLGT.vendor_business_type IS NULL
707: OR Upper(ASLGT.vendor_business_type) NOT IN
708: (SELECT lookup_code
712: UNION ALL
713: --Reject records if vendor_id is empty
714: SELECT user_key ,
715: 'po_approved_supplier_list_gt' AS entity ,
716: fnd_message.get_string('PO','VENDOR_EMPTY') AS msg
717: FROM po_approved_supplier_list_gt ASLGT
718: WHERE ASLGT.vendor_id IS NULL
719: AND Upper(vendor_business_type) <> 'MANUFACTURER'
720:
721: UNION ALL
722: --Reject records if status_id is empty
723: SELECT user_key ,
724: 'po_approved_supplier_list_gt' AS entity ,
725: fnd_message.get_string('PO','STATUS_EMPTY') AS msg
726: FROM po_approved_supplier_list_gt ASLGT
727: WHERE ASLGT.asl_status_id IS NULL
728: );
729:
740: --Reject records if manufacturer_asl_id is empty when business type is
741: --'DISTRIBUTOR'
742: SELECT user_key ,
743: 'po_approved_supplier_list_gt' AS entity ,
744: fnd_message.get_string('PO','MANUFACTURER_ASL_MANDATORY') AS msg
745: FROM po_approved_supplier_list_gt ASLGT
746: WHERE ASLGT.manufacturer_asl_id IS NULL
747: AND Upper(ASLGT.vendor_business_type) = 'DISTRIBUTOR'
748:
749: UNION ALL
750: --Reject records if review date entered and is past date
751: SELECT user_key ,
752: 'po_approved_supplier_list_gt' AS entity ,
753: fnd_message.get_string('PO','INVALID_REVIEW_DATE') AS msg
754: FROM po_approved_supplier_list_gt ASLGT
755: WHERE ASLGT.review_by_date IS NOT NULL
756: AND ASLGT.review_by_date < SYSDATE
757:
759: --During update ASL, business type can't be editable if the value is DB is
760: --'MANUFACTURER'
761: SELECT user_key ,
762: 'po_approved_supplier_list_gt' AS entity ,
763: fnd_message.get_string('PO','BUSINESS_TYPE_NOT_EDITABLE') AS msg
764: FROM po_approved_supplier_list_gt ASLGT
765: WHERE ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
766: AND
767: EXISTS (SELECT asl_id
774: --Reject records if business type is MANUFACTURER and manufacter id doesn't
775: --exist also reject if vendor exists for the same case
776: SELECT user_key ,
777: 'po_approved_supplier_list_gt' AS entity ,
778: fnd_message.get_string('PO','VENDOR_INVALID_EXP_MANUFACTUR') AS msg
779: FROM po_approved_supplier_list_gt ASLGT
780: WHERE Upper(ASLGT.vendor_business_type) = 'MANUFACTURER'
781: AND (ASLGT.manufacturer_id IS NULL
782: OR ASLGT.vendor_id IS NOT NULL)
799: --Reject records when business type is MANUFACTURER and if there are any
800: --child records exist
801: SELECT user_key ,
802: 'po_approved_supplier_list_gt' AS entity ,
803: fnd_message.get_string('PO','MANUFAC_INVALID_AUTH_ENTRY') AS msg
804: FROM po_approved_supplier_list_gt ASLGT
805: WHERE Upper(ASLGT.vendor_business_type) = 'MANUFACTURER'
806: AND
807: (EXISTS
914: FROM (
915: --Reject records if item or site is null and country code is not null
916: SELECT user_key ,
917: 'po_asl_attributes_gt' AS entity ,
918: fnd_message.get_string('PO','COUNTRY_CODE_NOT_EMPTY') AS msg
919: FROM po_asl_attributes_gt PAAGT
920: WHERE (PAAGT.item_id IS NULL
921: OR PAAGT.vendor_site_id IS NULL)
922: AND PAAGT.country_of_origin_code_dsp IS NOT NULL
925: --Reject records if Purchasing UOM empty and enable_plan_schedule_flag or
926: --enable_ship_schedule_flag is checked
927: SELECT user_key ,
928: 'po_asl_attributes_gt' AS entity ,
929: fnd_message.get_string('PO','PURCHASING_UOM_MANDATORY') AS msg
930: FROM po_asl_attributes_gt PAAGT
931: WHERE (PAAGT.enable_plan_schedule_flag_dsp = 'Y'
932: OR PAAGT.enable_ship_schedule_flag_dsp = 'Y')
933: AND (PAAGT.purchasing_unit_of_measure_dsp IS NULL
938: --enable_plan_schedule_flag_dsp and enable_ship_schedule_flag_dsp
939: --are unchecked.
940: SELECT user_key ,
941: 'po_asl_attributes_gt' AS entity ,
942: fnd_message.get_string('PO','INVALID_AUTOSCHEDULE_FLAG') AS msg
943: FROM po_asl_attributes_gt PAAGT
944: WHERE Nvl(PAAGT.enable_plan_schedule_flag_dsp,'N') <> 'Y'
945: AND Nvl(PAAGT.enable_ship_schedule_flag_dsp,'N') <> 'Y'
946: AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
949: --Reject records if plan bucket pattern empty if enable_autoschedule_flag
950: --and enable_plan_schedule_flag enabled
951: SELECT user_key ,
952: 'po_asl_attributes_gt' AS entity ,
953: fnd_message.get_string('PO','PLAN_BUCKET_MANDATORY') AS msg
954: FROM po_asl_attributes_gt PAAGT
955: WHERE PAAGT.enable_plan_schedule_flag_dsp = 'Y'
956: AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
957: AND PAAGT.plan_bucket_pattern_id IS NULL
960: --Reject records if plan schedule type empty if enable_autoschedule_flag
961: --and enable_plan_schedule_flag enabled
962: SELECT user_key ,
963: 'po_asl_attributes_gt' AS entity ,
964: fnd_message.get_string('PO','PLAN_SCHEDULE_MANDATORY') AS msg
965: FROM po_asl_attributes_gt PAAGT
966: WHERE PAAGT.enable_plan_schedule_flag_dsp = 'Y'
967: AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
968: AND (PAAGT.plan_schedule_type IS NULL
984: --Reject records if ship bucket pattern empty if enable_autoschedule_flag
985: --and enable_ship_schedule_flag enabled
986: SELECT user_key ,
987: 'po_asl_attributes_gt' AS entity ,
988: fnd_message.get_string('PO','SHIP_BUCKET_MANDATORY') AS msg
989: FROM po_asl_attributes_gt PAAGT
990: WHERE PAAGT.enable_ship_schedule_flag_dsp = 'Y'
991: AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
992: AND PAAGT.ship_bucket_pattern_id IS NULL
995: --Reject records if ship schedule type empty if enable_autoschedule_flag
996: --and enable_ship_schedule_flag enabled
997: SELECT user_key ,
998: 'po_asl_attributes_gt' AS entity ,
999: fnd_message.get_string('PO','SHIP_SCHEDULE_MANDATORY') AS msg
1000: FROM po_asl_attributes_gt PAAGT
1001: WHERE PAAGT.enable_ship_schedule_flag_dsp = 'Y'
1002: AND PAAGT.enable_autoschedule_flag_dsp = 'Y'
1003: AND (PAAGT.ship_schedule_type IS NULL
1007: --Reject records if there is an entry in po_supplier_item_capacity_gt
1008: -- when global_flag is N and VMI flag is not checked
1009: SELECT PAAGT.user_key ,
1010: 'po_asl_attributes_gt' AS entity ,
1011: fnd_message.get_string('PO','INVALID_SUPP_ITEM_CAP_ENTRY') AS msg
1012: FROM po_asl_attributes_gt PAAGT,
1013: po_approved_supplier_list_gt ASLGT
1014: WHERE PAAGT.user_key = ASLGT.user_key
1015: AND ASLGT.using_organization_id <> -1
1040: --Reject records if plan bucket pattern is not empty if
1041: --enable_autoschedule_flag or enable_plan_schedule_flag disabled
1042: SELECT user_key ,
1043: 'po_asl_attributes_gt' AS entity ,
1044: fnd_message.get_string('PO','INVALID_PLAN_BUCKET') AS msg
1045: FROM po_asl_attributes_gt PAAGT
1046: WHERE (Nvl(PAAGT.enable_plan_schedule_flag_dsp, 'N') <> 'Y'
1047: OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
1048: AND PAAGT.plan_bucket_pattern_id IS NOT NULL
1051: --Reject records if plan schedule type not empty if
1052: --enable_autoschedule_flag or enable_plan_schedule_flag disabled
1053: SELECT user_key ,
1054: 'po_asl_attributes_gt' AS entity ,
1055: fnd_message.get_string('PO','INVALID_PLAN_SCHEDULE') AS msg
1056: FROM po_asl_attributes_gt PAAGT
1057: WHERE (Nvl(PAAGT.enable_plan_schedule_flag_dsp, 'N') <> 'Y'
1058: OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
1059: AND PAAGT.plan_schedule_type IS NOT NULL
1062: --Reject records if ship bucket pattern not empty if
1063: --enable_autoschedule_flag or enable_ship_schedule_flag disabled
1064: SELECT user_key ,
1065: 'po_asl_attributes_gt' AS entity ,
1066: fnd_message.get_string('PO','INVALID_SHIP_BUCKET') AS msg
1067: FROM po_asl_attributes_gt PAAGT
1068: WHERE (Nvl(PAAGT.enable_ship_schedule_flag_dsp, 'N') <> 'Y'
1069: OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
1070: AND PAAGT.ship_bucket_pattern_id IS NOT NULL
1073: --Reject records if ship schedule type not empty if
1074: --enable_autoschedule_flag or enable_ship_schedule_flag disabled
1075: SELECT user_key ,
1076: 'po_asl_attributes_gt' AS entity ,
1077: fnd_message.get_string('PO','INVALID_SHIP_SCHEDULE') AS msg
1078: FROM po_asl_attributes_gt PAAGT
1079: WHERE (Nvl(PAAGT.enable_ship_schedule_flag_dsp, 'N') <> 'Y'
1080: OR Nvl(PAAGT.enable_autoschedule_flag_dsp, 'N') <> 'Y')
1081: AND PAAGT.ship_schedule_type IS NOT NULL
1097: --Reject records if authorization flag is not checked and
1098: --there is an entry for chv_authorizations in case of CREATE
1099: SELECT user_key ,
1100: 'po_asl_attributes_gt' AS entity ,
1101: fnd_message.get_string('PO','INVALID_AUTHORIZATION_ENTRY') AS msg
1102: FROM po_asl_attributes_gt PAAGT
1103: WHERE NVL(PAAGT.enable_authorizations_flag_dsp,'N') <> 'Y'
1104: AND EXISTS
1105: (SELECT 1
1113: --Reject records if there is an entry in po_supplier_item_tolerance_gt
1114: -- when global_flag is N and VMI flag is not checked
1115: SELECT PAAGT.user_key ,
1116: 'po_asl_attributes_gt' AS entity ,
1117: fnd_message.get_string('PO','INVALID_SUPP_ITEM_TOL_ENTRY') AS msg
1118: FROM po_asl_attributes_gt PAAGT,
1119: po_approved_supplier_list_gt ASLGT
1120: WHERE PAAGT.user_key = ASLGT.user_key
1121: AND ASLGT.using_organization_id <> -1
1131: UNION ALL
1132: --Reject records if Price Update tolerance is -ve number
1133: SELECT user_key ,
1134: 'po_asl_attributes_gt' AS entity ,
1135: fnd_message.get_string('PO','INVALID_PRICE_UPDATE_TOLERANCE') AS msg
1136: FROM po_asl_attributes_gt PAAGT
1137: WHERE PAAGT.price_update_tolerance_dsp < 0);
1138:
1139: l_progress := 40;
1152: --Reject records if delivery calendar code is entered when global_flag is N
1153: --or VMI flag is not checked
1154: SELECT user_key ,
1155: 'po_asl_attributes_gt' AS entity ,
1156: fnd_message.get_string('PO','DELIVERY_CALENDAR_NOT_NULL') AS msg
1157: FROM po_asl_attributes_gt PAAGT
1158: WHERE PAAGT.delivery_calendar_dsp IS NOT NULL
1159: AND PAAGT.using_organization_id <> -1
1160: AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
1162: UNION ALL
1163: --Reject records if delivery calendar code is entered and not valid
1164: SELECT user_key ,
1165: 'po_asl_attributes_gt' AS entity ,
1166: fnd_message.get_string('PO','INVALID_DELIVERY_CALENDAR_CODE') AS msg
1167: FROM po_asl_attributes_gt PAAGT
1168: WHERE PAAGT.delivery_calendar_dsp IS NOT NULL
1169: AND NOT EXISTS
1170: (SELECT 1
1176: --Reject records if delivery calendar code is entered when global_flag is N
1177: --or VMI flag is not checked
1178: SELECT user_key ,
1179: 'po_asl_attributes_gt' AS entity ,
1180: fnd_message.get_string('PO','PROCESSING_LEAD_TIME_NOT_NULL') AS msg
1181: FROM po_asl_attributes_gt PAAGT
1182: WHERE PAAGT.processing_lead_time_dsp IS NOT NULL
1183: AND PAAGT.using_organization_id <> -1
1184: AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
1186: UNION ALL
1187: --Reject records if Processing lead time is -ve number or zero
1188: SELECT user_key ,
1189: 'po_asl_attributes_gt' AS entity ,
1190: fnd_message.get_string('PO','INVALID_PROCESSING_LEAD_TIME') AS msg
1191: FROM po_asl_attributes_gt PAAGT
1192: WHERE PAAGT.processing_lead_time_dsp <= 0);
1193:
1194: l_progress := 50;
1206: FROM (
1207: --Reject records if Min Order Qty is -ve number or zero
1208: SELECT user_key ,
1209: 'po_asl_attributes_gt' AS entity ,
1210: fnd_message.get_string('PO','INVALID_MIN_ORDER_QTY') AS msg
1211: FROM po_asl_attributes_gt PAAGT
1212: WHERE PAAGT.min_order_qty_dsp <= 0
1213:
1214: UNION ALL
1214: UNION ALL
1215: --Reject records if fixed lot multiple is -ve number or zero
1216: SELECT user_key ,
1217: 'po_asl_attributes_gt' AS entity ,
1218: fnd_message.get_string('PO','INVALID_FIXED_LOT_MULTIPLE') AS msg
1219: FROM po_asl_attributes_gt PAAGT
1220: WHERE PAAGT.fixed_lot_multiple_dsp <= 0
1221:
1222: UNION ALL
1224: --ASL created for commodity or PO_THIRD_PARTY_STOCK_GRP.validate_local_asl
1225: --retunrs false
1226: SELECT user_key ,
1227: 'po_asl_attributes_gt' AS entity ,
1228: fnd_message.get_string('PO','INVALID_VMI_FLAG') AS msg
1229: FROM po_asl_attributes_gt PAAGT
1230: WHERE PAAGT.enable_vmi_flag_dsp = 'Y'
1231: AND (PAAGT.vendor_site_id IS NULL
1232: OR PAAGT.item_id IS NULL
1250: FROM (
1251: --Reject records if vmi_flag is not checked and automatic allowed is checked
1252: SELECT user_key ,
1253: 'po_asl_attributes_gt' AS entity ,
1254: fnd_message.get_string('PO','INVALID_AUTO_REPLENISH_FLAG') AS msg
1255: FROM po_asl_attributes_gt PAAGT
1256: WHERE Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
1257: AND PAAGT.enable_vmi_auto_replenish_flag = 'Y'
1258:
1260: --Reject records if vmi_flag is not checked and replenishment
1261: --method is entered
1262: SELECT user_key ,
1263: 'po_asl_attributes_gt' AS entity ,
1264: fnd_message.get_string('PO','INVALID_REPLENISH_METHOD') AS msg
1265: FROM po_asl_attributes_gt PAAGT
1266: WHERE Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y'
1267: AND PAAGT.replenishment_method IS NOT NULL
1268:
1270: --Reject records if automatic allowed is not checked and replenishment
1271: --approval is not 'SUPPLIER_OR_BUYER'
1272: SELECT user_key ,
1273: 'po_asl_attributes_gt' AS entity ,
1274: fnd_message.get_string('PO','INVALID_REPLENISH_APPROVAL') AS msg
1275: FROM po_asl_attributes_gt PAAGT
1276: WHERE Nvl(PAAGT.enable_vmi_auto_replenish_flag,'N') <> 'Y'
1277: AND PAAGT.vmi_replenishment_approval <>'SUPPLIER_OR_BUYER'
1278: AND PAAGT.vmi_replenishment_approval IS NOT NULL
1281: --Reject records if vmi_flag, automatic allowed is checked and
1282: --replenishment approval is empty
1283: SELECT user_key ,
1284: 'po_asl_attributes_gt' AS entity ,
1285: fnd_message.get_string('PO','REPLENISH_APPROVAL_REQUIRED') AS msg
1286: FROM po_asl_attributes_gt PAAGT
1287: WHERE PAAGT.enable_vmi_flag_dsp = 'Y'
1288: AND PAAGT.enable_vmi_auto_replenish_flag = 'Y'
1289: AND PAAGT.vmi_replenishment_approval IS NULL
1292: UNION ALL
1293: --Reject records if vmi_flag is checked and replenishment method is empty
1294: SELECT user_key ,
1295: 'po_asl_attributes_gt' AS entity ,
1296: fnd_message.get_string('PO','REPLENISH_METHOD_REQUIRED') AS msg
1297: FROM po_asl_attributes_gt PAAGT
1298: WHERE PAAGT.enable_vmi_flag_dsp = 'Y'
1299: AND PAAGT.replenishment_method IS NULL
1300:
1301: UNION ALL
1302: --Reject records if forecast horizon is not +ve integer or zero
1303: SELECT user_key ,
1304: 'po_asl_attributes_gt' AS entity ,
1305: fnd_message.get_string('PO','INVALID_FORECAST_HORIZON') AS msg
1306: FROM po_asl_attributes_gt PAAGT
1307: WHERE PAAGT.forecast_horizon_dsp <= 0
1308: OR Round(PAAGT.forecast_horizon_dsp) <> PAAGT.forecast_horizon_dsp
1309: OR (PAAGT.forecast_horizon_dsp IS NOT NULL
1325: --Reject records if VMI Min Qty is -ve number or is entered when
1326: --vmi_flag is not checked or replenishment method is 2/4
1327: SELECT user_key ,
1328: 'po_asl_attributes_gt' AS entity ,
1329: fnd_message.get_string('PO','INVALID_VIM_MIN_QTY') AS msg
1330: FROM po_asl_attributes_gt PAAGT
1331: WHERE PAAGT.vmi_min_qty_dsp < 0
1332: OR (PAAGT.vmi_min_qty_dsp <> 0
1333: AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
1339: --vmi flag is not checked or replenishment method is 2/3/4 or
1340: --this qty is less than vmi min qty
1341: SELECT user_key ,
1342: 'po_asl_attributes_gt' AS entity ,
1343: fnd_message.get_string('PO','INVALID_VIM_MAX_QTY') AS msg
1344: FROM po_asl_attributes_gt PAAGT
1345: WHERE PAAGT.vmi_max_qty_dsp < 0
1346: OR (PAAGT.vmi_max_qty_dsp < PAAGT.vmi_min_qty_dsp)
1347: OR (PAAGT.vmi_max_qty_dsp <> 0
1353: --Reject records if VMI Min Days is not +ve integer or is entered when
1354: --vmi flag is not checked or replenishment method is 1/3
1355: SELECT user_key ,
1356: 'po_asl_attributes_gt' AS entity ,
1357: fnd_message.get_string('PO','INVALID_VIM_MIN_DAYS') AS msg
1358: FROM po_asl_attributes_gt PAAGT
1359: WHERE PAAGT.vmi_min_days_dsp < 0
1360: OR Round(PAAGT.vmi_min_days_dsp) <> PAAGT.vmi_min_days_dsp
1361: OR (PAAGT.vmi_min_days_dsp <> 0
1367: --Reject records if VMI Max Days is not +ve integer or is entered when
1368: --vmi flag is not checked or replenishment method is 1/3/4
1369: SELECT user_key ,
1370: 'po_asl_attributes_gt' AS entity ,
1371: fnd_message.get_string('PO','INVALID_VIM_MAXS_DAYS') AS msg
1372: FROM po_asl_attributes_gt PAAGT
1373: WHERE PAAGT.vmi_max_days_dsp < 0
1374: OR Round(PAAGT.vmi_max_days_dsp) <> PAAGT.vmi_max_days_dsp
1375: OR (PAAGT.vmi_max_days_dsp < PAAGT.vmi_min_days_dsp)
1382: --Reject records if Fixed Order Quantity is -ve number or is entered when
1383: --vmi flag is not checked or replenishment method is 1/2
1384: SELECT user_key ,
1385: 'po_asl_attributes_gt' AS entity ,
1386: fnd_message.get_string('PO','INVALID_FIXED_ORDER_QTY') AS msg
1387: FROM po_asl_attributes_gt PAAGT
1388: WHERE PAAGT.fixed_order_quantity_dsp < 0
1389: OR (PAAGT.fixed_order_quantity_dsp <> 0
1390: AND Nvl(PAAGT.enable_vmi_flag_dsp,'N') <> 'Y')
1407: --ASL created for commodity or PO_THIRD_PARTY_STOCK_GRP.validate_local_asl
1408: --retunrs false
1409: SELECT user_key ,
1410: 'po_asl_attributes_gt' AS entity ,
1411: fnd_message.get_string('PO','INVALID_CONSIGNED_FLAG') AS msg
1412: FROM po_asl_attributes_gt PAAGT
1413: WHERE PAAGT.consigned_from_supp_flag_dsp = 'Y'
1414: AND (PAAGT.vendor_site_id IS NULL
1415: OR PAAGT.item_id IS NULL
1421: UNION ALL
1422: --Reject records if Consigned billing cycle is -ve number
1423: SELECT user_key ,
1424: 'po_asl_attributes_gt' AS entity ,
1425: fnd_message.get_string('PO','INVALID_CONSIGN_BILL_CYCLE') AS msg
1426: FROM po_asl_attributes_gt PAAGT
1427: WHERE PAAGT.consigned_billing_cycle_dsp < 0
1428: OR (PAAGT.consigned_billing_cycle_dsp IS NOT NULL
1429: AND Nvl(PAAGT.consigned_from_supp_flag_dsp,'N') <> 'Y')
1432: --Reject records if Consume on Aging flag is checked when Consigned flag
1433: --is not checked
1434: SELECT user_key ,
1435: 'po_asl_attributes_gt' AS entity ,
1436: fnd_message.get_string('PO','INVALID_CONSUME_AGING_FLAG') AS msg
1437: FROM po_asl_attributes_gt PAAGT
1438: WHERE PAAGT.consume_on_aging_flag_dsp = 'Y'
1439: AND Nvl(PAAGT.consigned_from_supp_flag_dsp,'N') <> 'Y'
1440:
1442: --Reject records if ageing period is not +ve number or zero or is entered
1443: --when consume of ageing flag is not checked
1444: SELECT user_key ,
1445: 'po_asl_attributes_gt' AS entity ,
1446: fnd_message.get_string('PO','INVALID_AGEING_PERIOD') AS msg
1447: FROM po_asl_attributes_gt PAAGT
1448: WHERE PAAGT.aging_period_dsp <= 0
1449: OR Round(PAAGT.aging_period_dsp) <> PAAGT.aging_period_dsp
1450: OR (Nvl(PAAGT.consume_on_aging_flag_dsp,'N') <> 'Y'
1454: --Reject records if vendor site is null and supplier scheduling tab details
1455: --are given
1456: SELECT user_key ,
1457: 'po_asl_attributes_gt' AS entity ,
1458: fnd_message.get_string('PO','SUPPLIER_SCHEDULING_DISABLED') AS msg
1459: FROM po_asl_attributes_gt PAAGT
1460: WHERE PAAGT.vendor_site_id IS NULL
1461: AND PAAGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
1462: AND (PAAGT.enable_plan_schedule_flag_dsp = 'Y'
1547: FROM (
1548: --Reject records if sequence number is empty or lessthan 1
1549: SELECT user_key ,
1550: 'po_asl_documents_gt' AS entity ,
1551: fnd_message.get_string('PO','INVALID_DOC_SEQUENCE') AS msg
1552: FROM po_asl_documents_gt DOCGT
1553: WHERE DOCGT.sequence_num IS NULL
1554: OR DOCGT.sequence_num < 1
1555:
1556: UNION ALL
1557: --Reject records if document type is null
1558: SELECT user_key ,
1559: 'po_asl_documents_gt' AS entity ,
1560: fnd_message.get_string('PO','DOC_TYPE_MANDATORY') AS msg
1561: FROM po_asl_documents_gt DOCGT
1562: WHERE DOCGT.document_type_code IS NULL
1563:
1564: UNION ALL
1564: UNION ALL
1565: --Reject records if header id is null
1566: SELECT user_key ,
1567: 'po_asl_documents_gt' AS entity ,
1568: fnd_message.get_string('PO','DOC_HEADER_MANDATORY') AS msg
1569: FROM po_asl_documents_gt DOCGT
1570: WHERE DOCGT.document_header_id IS NULL
1571:
1572: UNION ALL
1572: UNION ALL
1573: --Reject records if document type is Not CONTRACT, 'LINE_NUM' is null
1574: SELECT user_key ,
1575: 'po_asl_documents_gt' AS entity ,
1576: fnd_message.get_string('PO','LINE_NUM_MANDATORY') AS msg
1577: FROM po_asl_documents_gt DOCGT
1578: WHERE DOCGT.document_type_code <> 'CONTRACT'
1579: AND DOCGT.document_line_id IS NULL
1580: AND DOCGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
1650: FROM (
1651: --Reject records if sequence number is empty or not in 1,2,3,4
1652: SELECT user_key ,
1653: 'chv_authorizations_gt' AS entity ,
1654: fnd_message.get_string('PO','INVALID_AUTH_SEQUENCE') AS msg
1655: FROM chv_authorizations_gt CHVGT
1656: WHERE CHVGT.authorization_sequence_dsp IS NULL
1657: OR CHVGT.authorization_sequence_dsp NOT IN (1,2,3,4)
1658:
1659: UNION ALL
1660: --Reject records if timefence days is less than 1
1661: SELECT user_key ,
1662: 'chv_authorizations_gt' AS entity ,
1663: fnd_message.get_string('PO','INVALID_TIMEFENCE_DAYS') AS msg
1664: FROM chv_authorizations_gt CHVGT
1665: WHERE CHVGT.timefence_days_dsp IS NULL
1666: OR CHVGT.timefence_days_dsp <= 0
1667:
1741: FROM (
1742: --Reject records if from_date is less than current date
1743: SELECT user_key ,
1744: 'po_supplier_item_capacity_gt' AS entity ,
1745: fnd_message.get_string('PO','INVALID_FROM_DATE') AS msg
1746: FROM po_supplier_item_capacity_gt PSICGT
1747: WHERE (PSICGT.from_date_dsp IS NULL
1748: OR PSICGT.from_date_dsp < SYSDATE)
1749: AND PSICGT.process_action <> PO_ASL_API_PUB.g_ACTION_DELETE
1751: UNION ALL
1752: --Reject records if to_date is less than current date or from_date
1753: SELECT user_key ,
1754: 'po_supplier_item_capacity_gt' AS entity ,
1755: fnd_message.get_string('PO','INVALID_TO_DATE') AS msg
1756: FROM po_supplier_item_capacity_gt PSICGT
1757: WHERE PSICGT.to_date_dsp < SYSDATE
1758: OR PSICGT.to_date_dsp < PSICGT.from_date_dsp
1759:
1760: UNION ALL
1761: --Reject records if capacity is less than 1
1762: SELECT user_key ,
1763: 'po_supplier_item_capacity_gt' ,
1764: fnd_message.get_string('PO','INVALID_CAPACITY_PER_DAY') AS msg
1765: FROM po_supplier_item_capacity_gt PSICGT
1766: WHERE PSICGT.capacity_per_day_dsp IS NULL
1767: OR PSICGT.capacity_per_day_dsp <= 0);
1768:
1772: l_reject_reason1 := l_reject_reason;
1773:
1774: SELECT PSICGT.user_key ,
1775: 'po_supplier_item_capacity_gt' AS entity ,
1776: fnd_message.get_string('PO','DATES_OVERLAPPED') AS msg
1777: BULK COLLECT INTO
1778: l_user_key_tbl ,
1779: l_entity_name ,
1780: l_reject_reason
1798: l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
1799: --Reject records if dates overlapped when update
1800: SELECT PSICGT.user_key ,
1801: 'po_supplier_item_capacity_gt' AS entity ,
1802: fnd_message.get_string('PO','DATES_OVERLAPPED') AS msg
1803: BULK COLLECT INTO
1804: l_user_key_tbl ,
1805: l_entity_name ,
1806: l_reject_reason
1893: FROM (
1894: --Reject records if number_of_days_dsp is less than 1
1895: SELECT user_key ,
1896: 'po_supplier_item_tolerance_gt' AS entity,
1897: fnd_message.get_string('PO','INVALID_NUM_OF_DAYS') AS msg
1898: FROM po_supplier_item_tolerance_gt PSITGT
1899: WHERE PSITGT.number_of_days_dsp IS NULL
1900: OR PSITGT.number_of_days_dsp <= 0
1901:
1902: UNION ALL
1903: --Reject records if tolerance_dsp is less than 1
1904: SELECT user_key ,
1905: 'po_supplier_item_tolerance_gt' AS entity,
1906: fnd_message.get_string('PO','INVALID_TOLERANCE') AS msg
1907: FROM po_supplier_item_tolerance_gt PSITGT
1908: WHERE PSITGT.tolerance_dsp IS NULL
1909: OR PSITGT.tolerance_dsp <= 0
1910: );