[Home] [Help]
107: 'OVER_TOLERANCE_ERROR_FLAG',
108: p_over_tolerance_err_flag_tbl(i),
109: 'OVER_TOLERANCE_ERROR_FLAG',
110: p_over_tolerance_err_flag_tbl(i),
111: PO_VAL_CONSTANTS.c_over_tolerance_error_flag
112: FROM DUAL
113: WHERE p_over_tolerance_err_flag_tbl(i) IS NOT NULL AND
114: NOT EXISTS(
115: SELECT 1
174: p_column_val => p_expiration_date_tbl(i),
175: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
176: p_token1_name => 'COLUMN_NAME',
177: p_token1_value => 'EXPIRATION_DATE',
178: p_validation_id => PO_VAL_CONSTANTS.c_expiration_date_blk_not_null);
179: x_result_type := po_validations.c_result_type_failure;
180: ELSIF p_expiration_date_tbl(i) IS NOT NULL
181: AND (p_header_start_date_tbl(i) > p_expiration_date_tbl(i)
182: OR p_header_end_date_tbl(i) < p_expiration_date_tbl(i)) THEN
184: p_entity_id => p_id_tbl(i),
185: p_column_name => 'EXPIRATION_DATE',
186: p_column_val => p_expiration_date_tbl(i),
187: p_message_name => 'POX_EXPIRATION_DATES',
188: p_validation_id => PO_VAL_CONSTANTS.c_expiration_date_blk_exc_hdr);
189: x_result_type := po_validations.c_result_type_failure;
190: END IF;
191: END LOOP;
192:
245: p_entity_id => p_id_tbl(i),
246: p_column_name => 'GLOBAL_AGREEMENT_FLAG',
247: p_column_val => p_global_agreement_flag_tbl(i),
248: p_message_name => 'PO_PDOI_SVC_NO_LOCAL_BLANKET',
249: p_validation_id => PO_VAL_CONSTANTS.c_ga_flag_temp_labor);
250: x_result_type := po_validations.c_result_type_failure;
251: END IF;
252: END LOOP;
253:
266: po_validations.c_result_type_failure,
267: c_entity_type_line,
268: p_id_tbl(i),
269: 'PO_PDOI_GA_OSP_NA',
270: PO_VAL_CONSTANTS.c_ga_flag_op
271: FROM DUAL
272: WHERE p_line_type_id_tbl(i) IS NOT NULL
273: AND EXISTS(
274: SELECT 1
331: p_entity_id => p_id_tbl(i),
332: p_column_name => 'AMOUNT',
333: p_column_val => p_amount_tbl(i),
334: p_message_name => 'PO_PDOI_SVC_BLKT_NO_AMT',
335: p_validation_id => PO_VAL_CONSTANTS.c_amount_blanket);
336: x_result_type := po_validations.c_result_type_failure;
337: ELSIF (p_order_type_lookup_code_tbl(i) = 'FIXED PRICE'
338: AND p_amount_tbl(i) IS NOT NULL
339: AND p_amount_tbl(i) < 0) THEN
345: p_token1_name => 'COLUMN_NAME',
346: p_token1_value => 'AMOUNT',
347: p_token2_name => 'VALUE',
348: p_token2_value => p_amount_tbl(i),
349: p_validation_id => PO_VAL_CONSTANTS.c_amount_ge_zero);
350: x_result_type := po_validations.c_result_type_failure;
351: END IF;
352: END LOOP;
353:
398: p_entity_id => p_id_tbl(i),
399: p_column_name => 'ORDER_TYPE_LOOKUP_CODE',
400: p_column_val => p_order_type_lookup_code_tbl(i),
401: p_message_name => 'PO_SVC_NOT_ENABLED',
402: p_validation_id => PO_VAL_CONSTANTS.c_order_type_lookup_code);
403: x_result_type := po_validations.c_result_type_failure;
404: END IF;
405: END LOOP;
406: END IF;
457: p_entity_id => p_id_tbl(i),
458: p_column_name => 'CONTRACTOR FIRST/LAST NAME',
459: p_column_val => p_contractor_last_name_tbl(i),
460: p_message_name => 'PO_PDOI_SVC_NO_NAME',
461: p_validation_id => PO_VAL_CONSTANTS.c_contractor_name);
462: x_result_type := po_validations.c_result_type_failure;
463: END IF;
464: END LOOP;
465:
516: p_entity_id => p_id_tbl(i),
517: p_column_name => 'JOB_ID',
518: p_column_val => p_job_id_tbl(i),
519: p_message_name => 'PO_PDOI_SVC_NO_JOB',
520: p_validation_id => PO_VAL_CONSTANTS.c_job_id_null );
521: x_result_type := po_validations.c_result_type_failure;
522: ELSIF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_job_id_tbl(i) IS NULL THEN
523: x_results.add_result(p_entity_type => c_entity_type_line,
524: p_entity_id => p_id_tbl(i),
524: p_entity_id => p_id_tbl(i),
525: p_column_name => 'JOB_ID',
526: p_column_val => p_job_id_tbl(i),
527: p_message_name => 'PO_PDOI_SVC_MUST_JOB',
528: p_validation_id => PO_VAL_CONSTANTS.c_job_id_not_null );
529: x_result_type := po_validations.c_result_type_failure;
530: END IF;
531: END LOOP;
532:
557: 'JOB_ID',
558: p_job_id_tbl(i),
559: 'JOB_BG_ID',
560: p_job_business_group_id_tbl(i),
561: PO_VAL_CONSTANTS.c_job_id_valid
562: FROM DUAL
563: WHERE p_purchase_basis_tbl(i) = 'TEMP LABOR'
564: AND NOT EXISTS(
565: SELECT 1
602: 'JOB_ID',
603: p_job_id_tbl(i),
604: 'JOB_BG_ID',
605: p_job_business_group_id_tbl(i),
606: PO_VAL_CONSTANTS.c_job_id_valid
607: FROM DUAL
608: WHERE p_job_business_group_id_tbl(i) IS NOT NULL
609: AND p_purchase_basis_tbl(i) = 'TEMP LABOR'
610: AND NOT EXISTS(
643: p_id_tbl(i),
644: 'PO_PDOI_SVC_INVALID_JOB_CAT',
645: 'JOB_ID',
646: p_job_id_tbl(i),
647: PO_VAL_CONSTANTS.c_job_id_valid_cat
648: FROM DUAL
649: WHERE p_purchase_basis_tbl(i) = 'TEMP LABOR'
650: AND p_category_id_tbl(i) IS NOT NULL
651: AND NOT EXISTS(
723: p_id_tbl(i),
724: 'PO_PDOI_SVC_CANNOT_CROSS_BG',
725: 'JOB_BUSINESS_GROUP_ID',
726: p_job_business_group_id_tbl(i),
727: PO_VAL_CONSTANTS.c_job_bg_id_not_cross_bg
728: FROM DUAL
729: WHERE p_job_business_group_id_tbl(i) IS NOT NULL
730: AND p_purchase_basis_tbl(i) = 'TEMP LABOR'
731: AND NOT EXISTS(SELECT 1
753: p_id_tbl(i),
754: 'PO_PDOI_SVC_INVALID_BG',
755: 'JOB_BUSINESS_GROUP_ID',
756: p_job_business_group_id_tbl(i),
757: PO_VAL_CONSTANTS.c_job_business_group_id_valid
758: FROM DUAL
759: WHERE p_job_business_group_id_tbl(i) IS NOT NULL
760: AND p_purchase_basis_tbl(i) = 'TEMP LABOR'
761: AND NOT EXISTS(
815: p_entity_id => p_id_tbl(i),
816: p_column_name => 'CAPITAL_EXPENSE_FLAG',
817: p_column_val => p_capital_expense_flag_tbl(i),
818: p_message_name => 'PO_SVC_NO_CAP_EXPENSE',
819: p_validation_id => PO_VAL_CONSTANTS.c_capital_expense_flag_null);
820: x_result_type := po_validations.c_result_type_failure;
821: END IF;
822: END LOOP;
823:
871: p_entity_id => p_id_tbl(i),
872: p_column_name => 'UN_NUMBER',
873: p_column_val => p_un_number_id_tbl(i),
874: p_message_name => 'PO_PDOI_SVC_NO_UNNUMBER',
875: p_validation_id => PO_VAL_CONSTANTS.c_un_number_id_null);
876: x_result_type := po_validations.c_result_type_failure;
877: END IF;
878: END LOOP;
879:
897: 'UN_NUMBER_ID',
898: p_un_number_id_tbl(i),
899: 'VALUE',
900: p_un_number_id_tbl(i),
901: PO_VAL_CONSTANTS.c_un_number_id_valid
902: FROM DUAL
903: WHERE p_un_number_id_tbl(i) IS NOT NULL
904: AND p_purchase_basis_tbl(i) <> 'TEMP LABOR'
905: AND NOT EXISTS(SELECT 1
962: p_entity_id => p_id_tbl(i),
963: p_column_name => 'HAZARD_CLASS',
964: p_column_val => p_hazard_class_id_tbl(i),
965: p_message_name => 'PO_PDOI_SVC_NO_HAZARD_CLASS',
966: p_validation_id => PO_VAL_CONSTANTS.c_hazard_class_id_null );
967: x_result_type := po_validations.c_result_type_failure;
968: END IF;
969: END LOOP;
970:
988: 'HAZARD_CLASS_ID',
989: p_hazard_class_id_tbl(i),
990: 'VALUE',
991: p_hazard_class_id_tbl(i),
992: PO_VAL_CONSTANTS.c_hazard_class_id_valid
993: FROM DUAL
994: WHERE p_hazard_class_id_tbl(i) IS NOT NULL
995: AND p_purchase_basis_tbl(i) <> 'TEMP LABOR'
996: AND NOT EXISTS(SELECT 'Y'
1060: p_column_val => p_item_id_tbl(i),
1061: p_message_name => 'PO_PDOI_COLUMN_NULL',
1062: p_token1_name => 'COLUMN_NAME',
1063: p_token1_value => 'ITEM_ID',
1064: p_validation_id => PO_VAL_CONSTANTS.c_item_id_null);
1065: x_result_type := po_validations.c_result_type_failure;
1066: ELSIF p_order_type_lookup_code_tbl(i) = 'AMOUNT' AND p_item_id_tbl(i) IS NOT NULL THEN
1067: x_results.add_result(p_entity_type => c_entity_type_line,
1068: p_entity_id => p_id_tbl(i),
1070: p_column_val => p_item_id_tbl(i),
1071: p_message_name => 'PO_PDOI_COLUMN_NULL',
1072: p_token1_name => 'COLUMN_NAME',
1073: p_token1_value => 'ITEM_ID',
1074: p_validation_id => PO_VAL_CONSTANTS.c_item_id_null);
1075: x_result_type := po_validations.c_result_type_failure;
1076: END IF;
1077: END LOOP;
1078:
1094: p_id_tbl(i),
1095: 'PO_PDOI_ITEM_NOT_NULL',
1096: 'ITEM_ID',
1097: p_item_id_tbl(i),
1098: PO_VAL_CONSTANTS.c_item_id_not_null
1099: FROM DUAL
1100: WHERE p_order_type_lookup_code_tbl(i) = 'QUANTITY'
1101: AND p_item_id_tbl(i) IS NULL
1102: AND EXISTS(
1131: 'ITEM_ID',
1132: p_item_id_tbl(i),
1133: 'VALUE',
1134: p_item_id_tbl(i),
1135: DECODE(plt.outside_operation_flag, 'N', PO_VAL_CONSTANTS.c_item_id_valid,
1136: PO_VAL_CONSTANTS.c_item_id_op_valid)
1137: FROM po_line_types_b plt
1138: WHERE p_item_id_tbl(i) IS NOT NULL
1139: AND p_line_type_id_tbl(i) IS NOT NULL
1132: p_item_id_tbl(i),
1133: 'VALUE',
1134: p_item_id_tbl(i),
1135: DECODE(plt.outside_operation_flag, 'N', PO_VAL_CONSTANTS.c_item_id_valid,
1136: PO_VAL_CONSTANTS.c_item_id_op_valid)
1137: FROM po_line_types_b plt
1138: WHERE p_item_id_tbl(i) IS NOT NULL
1139: AND p_line_type_id_tbl(i) IS NOT NULL
1140: AND p_line_type_id_tbl(i) = plt.line_type_id
1223: 'ITEM_DESCRIPTION',
1224: p_item_description_tbl(i),
1225: DECODE(p_item_description_tbl(i), NULL, 'COLUMN_NAME', NULL),
1226: DECODE(p_item_description_tbl(i), NULL, 'ITEM_DESCRIPTION', NULL),
1227: DECODE(p_item_description_tbl(i), NULL, PO_VAL_CONSTANTS.c_item_desc_not_null,
1228: PO_VAL_CONSTANTS.c_item_desc_not_updatable)
1229: FROM DUAL
1230: WHERE p_item_description_tbl(i) IS NULL
1231: OR ( p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
1224: p_item_description_tbl(i),
1225: DECODE(p_item_description_tbl(i), NULL, 'COLUMN_NAME', NULL),
1226: DECODE(p_item_description_tbl(i), NULL, 'ITEM_DESCRIPTION', NULL),
1227: DECODE(p_item_description_tbl(i), NULL, PO_VAL_CONSTANTS.c_item_desc_not_null,
1228: PO_VAL_CONSTANTS.c_item_desc_not_updatable)
1229: FROM DUAL
1230: WHERE p_item_description_tbl(i) IS NULL
1231: OR ( p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
1232: AND p_item_id_tbl(i) IS NOT NULL
1310: p_entity_id => p_id_tbl(i),
1311: p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1312: p_column_val => p_unit_meas_lookup_code_tbl(i),
1313: p_message_name => 'PO_PDOI_SVC_NO_UOM',
1314: p_validation_id => PO_VAL_CONSTANTS.c_unit_meas_lookup_null);
1315: x_result_type := po_validations.c_result_type_failure;
1316: ELSIF (p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_unit_meas_lookup_code_tbl(i) IS NULL) THEN
1317: x_results.add_result(p_entity_type => c_entity_type_line,
1318: p_entity_id => p_id_tbl(i),
1320: p_column_val => p_unit_meas_lookup_code_tbl(i),
1321: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1322: p_token1_name => 'COLUMN_NAME',
1323: p_token1_value => 'UNIT_MEAS_LOOKUP_CODE',
1324: p_validation_id => PO_VAL_CONSTANTS.c_unit_meas_lookup_not_null);
1325: x_result_type := po_validations.c_result_type_failure;
1326: END IF;
1327: END LOOP;
1328:
1354: 'ITEM',
1355: 'UNIT_MEAS_LOOKUP_CODE',
1356: p_unit_meas_lookup_code_tbl(i),
1357: p_item_id_tbl(i),
1358: PO_VAL_CONSTANTS.c_unit_meas_lookup_item
1359: FROM DUAL
1360: WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
1361: AND p_item_id_tbl(i) IS NOT NULL
1362: AND p_unit_meas_lookup_code_tbl(i) IS NOT NULL
1392: 'UNIT_MEAS_LOOKUP_CODE',
1393: p_unit_meas_lookup_code_tbl(i),
1394: 'VALUE',
1395: p_unit_meas_lookup_code_tbl(i),
1396: PO_VAL_CONSTANTS.c_unit_meas_lookup_valid
1397: FROM DUAL
1398: WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
1399: AND p_item_id_tbl(i) IS NULL
1400: AND p_unit_meas_lookup_code_tbl(i) IS NOT NULL
1435: 'LINE_TYPE',
1436: 'UNIT_MEAS_LOOKUP_CODE',
1437: p_unit_meas_lookup_code_tbl(i),
1438: pltb.unit_of_measure,
1439: PO_VAL_CONSTANTS.c_unit_meas_lookup_line_type
1440: FROM PO_LINE_TYPES_B pltb
1441: WHERE pltb.line_type_id = p_line_type_id_tbl(i)
1442: AND p_order_type_lookup_code_tbl(i) = 'AMOUNT'
1443: AND p_unit_meas_lookup_code_tbl(i) <> pltb.unit_of_measure;
1472: 'COLUMN_NAME',
1473: 'VALUE',
1474: 'UNIT_MEAS_LOOKUP_CODE',
1475: p_unit_meas_lookup_code_tbl(i),
1476: PO_VAL_CONSTANTS.c_unit_meas_lookup_svc_valid
1477: FROM DUAL
1478: WHERE p_order_type_lookup_code_tbl(i) = 'RATE'
1479: AND NOT EXISTS(SELECT 1
1480: FROM mtl_units_of_measure_vl muomv
1541: p_entity_id => p_id_tbl(i),
1542: p_column_name => 'ITEM_REVISION',
1543: p_column_val => p_item_revision_tbl(i),
1544: p_message_name => 'PO_PDOI_COLUMN_NULL',
1545: p_validation_id => PO_VAL_CONSTANTS.c_item_revision_null);
1546: x_result_type := po_validations.c_result_type_failure;
1547: END IF;
1548: END LOOP;
1549:
1577: 'ITEM',
1578: 'item_revision',
1579: p_item_revision_tbl(i),
1580: p_item_id_tbl(i),
1581: PO_VAL_CONSTANTS.c_item_revision_item
1582: FROM DUAL
1583: WHERE p_order_type_lookup_code_tbl(i) NOT IN('RATE', 'FIXED PRICE')
1584: AND p_item_revision_tbl(i) IS NOT NULL
1585: AND p_item_id_tbl(i) IS NOT NULL
1689: 'VALUE',
1690: p_category_id_tbl(i),
1691: 'ITEM',
1692: p_item_id_tbl(i),
1693: PO_VAL_CONSTANTS.c_category_id_item
1694: FROM DUAL
1695: WHERE p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
1696: AND p_item_id_tbl(i) IS NOT NULL
1697: AND p_category_id_tbl(i) IS NOT NULL
1731: 'CATEGORY_ID',
1732: p_category_id_tbl(i),
1733: 'VALUE',
1734: p_category_id_tbl(i),
1735: PO_VAL_CONSTANTS.c_category_id_valid
1736: FROM DUAL
1737: WHERE p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
1738: AND p_item_id_tbl(i) IS NULL
1739: AND p_category_id_tbl(i) IS NOT NULL
1772: 'CATEGORY_ID',
1773: p_category_id_tbl(i),
1774: 'VALUE',
1775: p_category_id_tbl(i),
1776: PO_VAL_CONSTANTS.c_category_id_valid
1777: FROM DUAL
1778: WHERE p_order_type_lookup_code_tbl(i) NOT IN('FIXED PRICE', 'RATE')
1779: AND p_item_id_tbl(i) IS NULL
1780: AND p_category_id_tbl(i) IS NOT NULL
1844: p_token1_name => 'COLUMN_NAME',
1845: p_token1_value => 'IP_CATEGORY_ID',
1846: p_token2_name => 'VALUE',
1847: p_token2_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_ip_category_id_tbl),
1848: p_validation_id => PO_VAL_CONSTANTS.c_ip_category_id_not_null,
1849: x_results => x_results,
1850: x_result_type => x_result_type);
1851:
1852: -- Validate if x_category_id is valid if not empty
1870: 'IP_CATEGORY_ID',
1871: p_ip_category_id_tbl(i),
1872: 'VALUE',
1873: p_ip_category_id_tbl(i),
1874: PO_VAL_CONSTANTS.c_ip_category_id_valid
1875: FROM DUAL
1876: WHERE p_ip_category_id_tbl(i) IS NOT NULL
1877: AND p_ip_category_id_tbl(i) <> -2
1878: AND NOT EXISTS(
1946: p_column_val => p_unit_price_tbl(i),
1947: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1948: p_token1_name => 'COLUMN_NAME',
1949: p_token1_value => 'UNIT_PRICE',
1950: p_validation_id => PO_VAL_CONSTANTS.c_unit_price_not_null);
1951: x_result_type := po_validations.c_result_type_failure;
1952: ELSIF p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_unit_price_tbl(i) < 0 THEN
1953: x_results.add_result(p_entity_type => c_entity_type_line,
1954: p_entity_id => p_id_tbl(i),
1958: p_token1_name => 'COLUMN_NAME',
1959: p_token1_value => 'UNIT_PRICE',
1960: p_token2_name => 'VALUE',
1961: p_token2_value => p_unit_price_tbl(i),
1962: p_validation_id => PO_VAL_CONSTANTS.c_unit_price_ge_zero);
1963: x_result_type := po_validations.c_result_type_failure;
1964: END IF;
1965: END LOOP;
1966:
2003: 'COLUMN_NAME',
2004: 'VALUE',
2005: 'UNIT_PRICE',
2006: X_li_unit_price,
2007: PO_VAL_CONSTANTS.c_unit_price_line_type
2008: FROM DUAL
2009: WHERE p_line_type_id_tbl(i) IS NOT NULL
2010: AND p_order_type_lookup_code_tbl(i) = 'AMOUNT'
2011: AND NOT EXISTS(SELECT 1
2042: 'COLUMN_NAME',
2043: 'VALUE',
2044: 'UNIT_PRICE',
2045: p_unit_price_tbl(i),
2046: PO_VAL_CONSTANTS.c_unit_price_line_type
2047: FROM DUAL
2048: WHERE p_line_type_id_tbl(i) IS NOT NULL
2049: AND p_order_type_lookup_code_tbl(i) = 'AMOUNT'
2050: AND NOT EXISTS(SELECT 1
2066: p_entity_id => p_id_tbl(i),
2067: p_column_name => 'UNIT_PRICE',
2068: p_column_val => p_unit_price_tbl(i),
2069: p_message_name => 'PO_PDOI_SVC_NO_PRICE',
2070: p_validation_id => PO_VAL_CONSTANTS.c_unit_price_null);
2071: x_result_type := po_validations.c_result_type_failure;
2072: END IF;
2073: END LOOP;
2074:
2127: p_token1_name => 'COLUMN_NAME',
2128: p_token1_value => 'QUANTITY',
2129: p_token2_name => 'VALUE',
2130: p_token2_value => p_quantity_tbl(i),
2131: p_validation_id => PO_VAL_CONSTANTS.c_quantity_ge_zero);
2132: x_result_type := po_validations.c_result_type_failure;
2133: -- If order_type_lookup_code is 'FIXED PRICE' or 'RATE', quantity
2134: -- must be null
2135: ELSIF (p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' OR p_order_type_lookup_code_tbl(i) = 'RATE') AND
2138: p_entity_id => p_id_tbl(i),
2139: p_column_name => 'QUANTITY',
2140: p_column_val => p_quantity_tbl(i),
2141: p_message_name => 'PO_SVC_NO_QTY',
2142: p_validation_id => PO_VAL_CONSTANTS.c_quantity_null);
2143: x_result_type := po_validations.c_result_type_failure;
2144: END IF;
2145: END LOOP;
2146:
2191: p_entity_id => p_id_tbl(i),
2192: p_column_name => 'AMOUNT',
2193: p_column_val => p_amount_tbl(i),
2194: p_message_name => 'PO_SVC_NO_AMT',
2195: p_validation_id => PO_VAL_CONSTANTS.c_amount_null);
2196: x_result_type := po_validations.c_result_type_failure;
2197: END IF;
2198: END LOOP;
2199:
2242: p_entity_id => p_id_tbl(i),
2243: p_column_name => 'RATE_TYPE',
2244: p_column_val => p_rate_type_tbl(i),
2245: p_message_name => 'PO_PDOI_SVC_RATE_TYPE_NO_USR',
2246: p_validation_id => PO_VAL_CONSTANTS.c_rate_type_no_usr);
2247: x_result_type := po_validations.c_result_type_failure;
2248: END IF;
2249: END LOOP;
2250:
2305: p_column_val => p_line_num_tbl(i),
2306: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
2307: p_token1_name => 'COLUMN_NAME',
2308: p_token1_value => 'LINE_NUM',
2309: p_validation_id => PO_VAL_CONSTANTS.c_line_num_not_null);
2310: x_result_type := po_validations.c_result_type_failure;
2311: ELSIF p_line_num_tbl(i) <= 0 THEN
2312: x_results.add_result(p_entity_type => c_entity_type_line,
2313: p_entity_id => p_id_tbl(i),
2317: p_token1_name => 'COLUMN_NAME',
2318: p_token1_value => 'LINE_NUM',
2319: p_token2_name => 'VALUE',
2320: p_token2_value => p_line_num_tbl(i),
2321: p_validation_id => PO_VAL_CONSTANTS.c_line_num_gt_zero);
2322: x_result_type := po_validations.c_result_type_failure;
2323: END IF;
2324: END LOOP;
2325:
2348: 'LINE_NUM',
2349: p_line_num_tbl(i),
2350: 'VALUE',
2351: p_line_num_tbl(i),
2352: PO_VAL_CONSTANTS.c_line_num_unique
2353: FROM DUAL
2354: WHERE p_po_header_id_tbl(i) IS NOT NULL
2355: AND p_line_num_tbl(i) IS NOT NULL
2356: AND (EXISTS(SELECT 'Y'
2435: 'COLUMN',
2436: 'PO_LINE_ID',
2437: 'VALUE',
2438: p_po_line_id_tbl(i),
2439: DECODE(p_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_po_line_id_not_null,
2440: PO_VAL_CONSTANTS.c_po_line_id_unique)
2441: FROM DUAL
2442: WHERE p_po_line_id_tbl(i) IS NULL
2443: OR ( p_po_header_id_tbl(i) IS NOT NULL
2436: 'PO_LINE_ID',
2437: 'VALUE',
2438: p_po_line_id_tbl(i),
2439: DECODE(p_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_po_line_id_not_null,
2440: PO_VAL_CONSTANTS.c_po_line_id_unique)
2441: FROM DUAL
2442: WHERE p_po_line_id_tbl(i) IS NULL
2443: OR ( p_po_header_id_tbl(i) IS NOT NULL
2444: AND EXISTS(SELECT 1
2510: 'COLUMN_NAME',
2511: 'LINE_TYPE_ID',
2512: 'VALUE',
2513: p_line_type_id_tbl(i),
2514: DECODE(p_line_type_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_line_type_id_not_null,
2515: PO_VAL_CONSTANTS.c_line_type_id_valid)
2516: FROM DUAL
2517: WHERE p_line_type_id_tbl(i) IS NULL OR
2518: NOT EXISTS(SELECT 1
2511: 'LINE_TYPE_ID',
2512: 'VALUE',
2513: p_line_type_id_tbl(i),
2514: DECODE(p_line_type_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_line_type_id_not_null,
2515: PO_VAL_CONSTANTS.c_line_type_id_valid)
2516: FROM DUAL
2517: WHERE p_line_type_id_tbl(i) IS NULL OR
2518: NOT EXISTS(SELECT 1
2519: FROM po_line_types_val_v pltv
2588: 'STYLE_ID',
2589: p_style_id_tbl(i),
2590: 'LINE_TYPE_ID',
2591: p_line_type_id_tbl(i),
2592: PO_VAL_CONSTANTS.c_line_style_on_line_type
2593: FROM po_doc_style_headers pdsh
2594: WHERE p_style_id_tbl(i) IS NOT NULL AND
2595: pdsh.style_id = p_style_id_tbl(i) AND
2596: pdsh.line_type_allowed = 'SPECIFIED' AND
2630: 'STYLE_ID',
2631: p_style_id_tbl(i),
2632: 'PURCHASE_BASIS',
2633: p_purchase_basis_tbl(i),
2634: PO_VAL_CONSTANTS.c_line_style_on_purchase_basis
2635: FROM DUAL
2636: WHERE NOT EXISTS(SELECT 1
2637: FROM po_doc_style_values pdsv
2638: WHERE pdsv.style_id = p_style_id_tbl(i)
2701: 'PRICE_TYPE_LOOKUP_CODE',
2702: p_price_type_lookup_code_tbl(i),
2703: 'VALUE',
2704: p_price_type_lookup_code_tbl(i),
2705: PO_VAL_CONSTANTS.c_price_type_lookup_code
2706: FROM DUAL
2707: WHERE p_price_type_lookup_code_tbl(i) IS NOT NULL
2708: AND NOT EXISTS(
2709: SELECT 'Y'
2992: p_token1_name => 'COLUMN_NAME',
2993: p_token1_value => 'AMOUNT',
2994: p_token2_name => 'VALUE',
2995: p_token2_value => p_amount_tbl(i),
2996: p_validation_id => PO_VAL_CONSTANTS.c_amount_gt_zero);
2997: x_result_type := po_validations.c_result_type_failure;
2998:
2999: END IF;
3000: END LOOP;
3155: p_entity_id => p_id_tbl(i),
3156: p_column_name => 'NOT_TO_EXCEED_PRICE',
3157: p_column_val => p_not_to_exceed_price_tbl(i),
3158: p_message_name => 'PO_PDOI_EXCEED_PRICE_NULL',
3159: p_validation_id => PO_VAL_CONSTANTS.c_not_to_exceed_price_null);
3160: x_result_type := po_validations.c_result_type_failure;
3161: ELSIF p_not_to_exceed_price_tbl(i) IS NOT NULL
3162: AND p_not_to_exceed_price_tbl(i) < p_unit_price_tbl(i) THEN
3163: -- If not_to_exceed_price is not null, then it cannot be less than
3170: p_token1_name => 'VALUE',
3171: p_token1_value => p_not_to_exceed_price_tbl(i),
3172: p_token2_name => 'UNIT_PRICE',
3173: p_token2_value => p_unit_price_tbl(i),
3174: p_validation_id => PO_VAL_CONSTANTS.c_not_to_exceed_price_valid);
3175: x_result_type := po_validations.c_result_type_failure;
3176: END IF;
3177: END LOOP;
3178:
3226: p_column_val => p_ip_category_id_tbl(i),
3227: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3228: p_token1_name => 'COLUMN_NAME',
3229: p_token1_value => 'IP_CATEGORY_ID',
3230: p_validation_id => PO_VAL_CONSTANTS.c_ip_cat_id_update_not_null);
3231: x_result_type := po_validations.c_result_type_failure;
3232: END IF;
3233: END LOOP;
3234:
3253: 'IP_CATEGORY_ID',
3254: p_ip_category_id_tbl(i),
3255: 'VALUE',
3256: p_ip_category_id_tbl(i),
3257: PO_VAL_CONSTANTS.c_ip_cat_id_update_valid
3258: FROM DUAL
3259: WHERE p_ip_category_id_tbl(i) IS NOT NULL
3260: AND p_ip_category_id_tbl(i) <> -2
3261: AND NOT EXISTS(
3318: p_column_val => p_unit_meas_lookup_code_tbl(i),
3319: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3320: p_token1_name => 'COLUMN_NAME',
3321: p_token1_value => 'UNIT_MEAS_LOOKUP_CODE',
3322: p_validation_id => PO_VAL_CONSTANTS.c_uom_update_not_null);
3323: x_result_type := po_validations.c_result_type_failure;
3324: END IF;
3325: END LOOP;
3326:
3347: 'UNIT_MEAS_LOOKUP_CODE',
3348: p_unit_meas_lookup_code_tbl(i),
3349: 'UNIT_MEAS_LOOKUP_CODE',
3350: p_unit_meas_lookup_code_tbl(i),
3351: PO_VAL_CONSTANTS.c_uom_update_valid
3352: FROM DUAL
3353: WHERE EXISTS(
3354: SELECT 1
3355: FROM po_lines_all pol
3428: p_column_val => p_item_description_tbl(i),
3429: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3430: p_token1_name => 'COLUMN_NAME',
3431: p_token1_value => 'ITEM_DESCRIPTION',
3432: p_validation_id => PO_VAL_CONSTANTS.c_item_desc_update_not_null);
3433: x_result_type := po_validations.c_result_type_failure;
3434: END IF;
3435: END LOOP;
3436:
3457: 'ITEM_DESCRIPTION',
3458: p_item_description_tbl(i),
3459: 'ITEM_DESCRIPTION',
3460: p_item_description_tbl(i),
3461: PO_VAL_CONSTANTS.c_item_desc_update_unupdatable
3462: FROM DUAL
3463: WHERE EXISTS(
3464: SELECT 1
3465: FROM mtl_system_items msi,
3538: p_token1_name => 'COLUMN_NAME',
3539: p_token1_value => 'NEGOTIATED_BY_PREPARER',
3540: p_token2_name => 'VALUE',
3541: p_token2_value => p_negotiated_by_preparer_tbl(i),
3542: p_validation_id => PO_VAL_CONSTANTS.c_negotiated_by_preparer);
3543: x_result_type := po_validations.c_result_type_failure;
3544: END IF;
3545: END LOOP;
3546:
3585: p_column_val => p_negotiated_by_preparer_tbl(i),
3586: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3587: p_token1_name => 'COLUMN_NAME',
3588: p_token1_value => 'NEGOTIATED_BY_PREPARER',
3589: p_validation_id => PO_VAL_CONSTANTS.c_nego_by_prep_update_not_null);
3590: x_result_type := po_validations.c_result_type_failure;
3591: ELSIF p_negotiated_by_preparer_tbl(i) NOT IN (NULL, 'Y', 'N') THEN
3592: x_results.add_result(p_entity_type => c_entity_type_line,
3593: p_entity_id => p_id_tbl(i),
3597: p_token1_name => 'COLUMN_NAME',
3598: p_token1_value => 'NEGOTIATED_BY_PREPARER',
3599: p_token2_name => 'VALUE',
3600: p_token2_value => p_negotiated_by_preparer_tbl(i),
3601: p_validation_id => PO_VAL_CONSTANTS.c_nego_by_prep_update_valid);
3602: x_result_type := po_validations.c_result_type_failure;
3603: END IF;
3604: END LOOP;
3605:
3660: p_column_val => p_category_id_tbl(i),
3661: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3662: p_token1_name => 'COLUMN_NAME',
3663: p_token1_value => 'CATEGORY_ID',
3664: p_validation_id => PO_VAL_CONSTANTS.c_cat_id_update_not_null);
3665: x_result_type := po_validations.c_result_type_failure;
3666: END IF;
3667: END LOOP;
3668:
3682: p_id_tbl(i),
3683: 'PO_PDOI_NO_PO_CAT_UPDATE',
3684: 'CATEGORY_ID',
3685: p_category_id_tbl(i),
3686: PO_VAL_CONSTANTS.c_cat_id_update_not_updatable
3687: FROM DUAL
3688: WHERE p_category_id_tbl(i) IS NOT NULL
3689: AND (p_item_id_tbl(i) IS NOT NULL OR p_job_id_tbl(i) IS NOT NULL)
3690: AND (EXISTS(SELECT 1
3765: p_column_val => p_unit_price_tbl(i),
3766: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3767: p_token1_name => 'COLUMN_NAME',
3768: p_token1_value => 'UNIT_PRICE',
3769: p_validation_id => PO_VAL_CONSTANTS.c_unit_price_update_not_null);
3770: x_result_type := po_validations.c_result_type_failure;
3771: ELSIF p_unit_price_tbl(i) IS NOT NULL AND p_unit_price_tbl(i) < 0 THEN
3772: x_results.add_result(p_entity_type => c_entity_type_line,
3773: p_entity_id => p_id_tbl(i),
3777: p_token1_name => 'COLUMN_NAME',
3778: p_token1_value => 'UNIT_PRICE',
3779: p_token2_name => 'VALUE',
3780: p_token2_value => p_unit_price_tbl(i),
3781: p_validation_id => PO_VAL_CONSTANTS.c_unit_price_update_ge_zero);
3782: x_result_type := po_validations.c_result_type_failure;
3783:
3784: END IF;
3785: END LOOP;
3802: p_id_tbl(i),
3803: 'PO_PDOI_SVC_NO_PRICE',
3804: 'UNIT_PRICE',
3805: p_unit_price_tbl(i),
3806: PO_VAL_CONSTANTS.c_unit_price_null
3807: FROM po_lines_all POL
3808: WHERE POL.po_line_id = p_po_line_id_tbl(i)
3809: AND POL.order_type_lookup_code = 'FIXED PRICE'
3810: AND p_unit_price_tbl(i) IS NOT NULL
3816: p_id_tbl(i),
3817: 'PO_PDOI_SVC_NO_PRICE',
3818: 'UNIT_PRICE',
3819: p_unit_price_tbl(i),
3820: PO_VAL_CONSTANTS.c_unit_price_null
3821: FROM po_lines_draft_all POL
3822: WHERE POL.po_line_id = p_po_line_id_tbl(i)
3823: AND POL.draft_id = p_draft_id_tbl(i)
3824: AND POL.order_type_lookup_code = 'FIXED PRICE'
3854: 'UNIT_PRICE',
3855: p_unit_price_tbl(i),
3856: 'UNIT_PRICE',
3857: p_unit_price_tbl(i),
3858: PO_VAL_CONSTANTS.c_not_to_exceed_price_valid
3859: FROM po_lines_all POL
3860: WHERE POL.po_line_id = p_po_line_id_tbl(i)
3861: AND POL.not_to_exceed_price < p_unit_price_tbl(i);
3862:
3882: p_id_tbl(i),
3883: 'PO_PDOI_ADJ_PRICE_UPDATE_NA',
3884: 'UNIT_PRICE',
3885: p_unit_price_tbl(i),
3886: PO_VAL_CONSTANTS.c_price_adjustment_exist
3887: FROM po_lines_all POL
3888: WHERE POL.po_line_id = p_po_line_id_tbl(i)
3889: AND POL.unit_price <> p_unit_price_tbl(i)
3890: AND EXISTS (SELECT 1
3959: p_id_tbl(i),
3960: 'PO_PDOI_SVC_BLKT_NO_AMT',
3961: 'AMOUNT',
3962: p_amount_tbl(i),
3963: PO_VAL_CONSTANTS.c_amount_blanket
3964: FROM po_lines_all POL
3965: WHERE POL.po_line_id = p_po_line_id_tbl(i)
3966: AND POL.order_type_lookup_code <> 'FIXED PRICE'
3967: AND p_amount_tbl(i) IS NOT NULL
3973: p_id_tbl(i),
3974: 'PO_PDOI_SVC_BLKT_NO_AMT',
3975: 'AMOUNT',
3976: p_amount_tbl(i),
3977: PO_VAL_CONSTANTS.c_amount_blanket
3978: FROM po_lines_draft_all POL
3979: WHERE POL.po_line_id = p_po_line_id_tbl(i)
3980: AND POL.draft_id = p_draft_id_tbl(i)
3981: AND POL.order_type_lookup_code <> 'FIXED PRICE'