[Home] [Help]
76: 'COLUMN_NAME',
77: 'PO_HEADER_ID',
78: 'VALUE',
79: p_po_header_id_tbl(i),
80: DECODE(p_po_header_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_po_header_id_not_null,
81: PO_VAL_CONSTANTS.c_po_header_id_unique)
82: FROM DUAL
83: WHERE p_id_tbl(i) IS NULL OR EXISTS(SELECT 1
84: FROM po_headers_all poh
77: 'PO_HEADER_ID',
78: 'VALUE',
79: p_po_header_id_tbl(i),
80: DECODE(p_po_header_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_po_header_id_not_null,
81: PO_VAL_CONSTANTS.c_po_header_id_unique)
82: FROM DUAL
83: WHERE p_id_tbl(i) IS NULL OR EXISTS(SELECT 1
84: FROM po_headers_all poh
85: WHERE p_po_header_id_tbl(i) = poh.po_header_id);
159: 'DOCUMENT_NUM',
160: p_document_num_tbl(i),
161: 'VALUE',
162: p_document_num_tbl(i),
163: PO_VAL_CONSTANTS.c_document_num_unique
164: FROM DUAL
165: WHERE p_document_num_tbl(i) IS NOT NULL AND --8688769 bug
166: ( EXISTS (SELECT 1
167: FROM po_headers_interface
195: p_token1_name => 'COLUMN_NAME',
196: p_token1_value => 'DOCUMENT_NUM',
197: p_token2_name => 'VALUE',
198: p_token2_value => p_document_num_tbl(i),
199: p_validation_id => PO_VAL_CONSTANTS.c_document_num_not_null);
200:
201: x_result_type := po_validations.c_result_type_failure; -- bug5101044
202: ELSIF ((p_type_lookup_code_tbl(i) IN ('BLANKET', 'STANDARD')
203: AND p_manual_po_num_type = 'NUMERIC')
221: p_token1_name => 'COLUMN_NAME',
222: p_token1_value => 'DOCUMENT_NUM',
223: p_token2_name => 'VALUE',
224: p_token2_value => p_document_num_tbl(i),
225: p_validation_id => PO_VAL_CONSTANTS.c_document_num_ge_zero);
226: x_result_type := po_validations.c_result_type_failure;
227: END IF;
228:
229: EXCEPTION
237: p_token1_name => 'COLUMN_NAME',
238: p_token1_value => 'DOCUMENT_NUM',
239: p_token2_name => 'VALUE',
240: p_token2_value => p_document_num_tbl(i),
241: p_validation_id => PO_VAL_CONSTANTS.c_document_num_valid);
242: x_result_type := po_validations.c_result_type_failure;
243: END;
244: END IF;
245: END LOOP;
294: p_token1_name => 'COLUMN_NAME',
295: p_token1_value => 'TYPE_LOOKUP_CODE',
296: p_token2_name => 'VALUE',
297: p_token2_value => p_type_lookup_code_tbl(i),
298: p_validation_id => PO_VAL_CONSTANTS.c_type_lookup_code_not_null);
299: x_result_type := po_validations.c_result_type_failure;
300: ELSIF p_type_lookup_code_tbl(i) NOT IN('BLANKET', 'STANDARD', 'QUOTATION') THEN
301: x_results.add_result(p_entity_type => c_entity_type_header,
302: p_entity_id => p_id_tbl(i),
304: p_column_val => p_type_lookup_code_tbl(i),
305: p_message_name => 'PO_PDOI_INVALID_TYPE_LKUP_CD',
306: p_token1_name => 'VALUE',
307: p_token1_value => p_type_lookup_code_tbl(i),
308: p_validation_id => PO_VAL_CONSTANTS.c_type_lookup_code_valid);
309: x_result_type := po_validations.c_result_type_failure;
310: END IF;
311: END LOOP;
312:
370: 'COLUMN_NAME',
371: 'CURRENCY_CODE',
372: 'VALUE',
373: p_currency_code_tbl(i),
374: DECODE(p_currency_code_tbl(i), NULL, PO_VAL_CONSTANTS.c_currency_code_not_null,
375: PO_VAL_CONSTANTS.c_currency_code_valid)
376: FROM DUAL
377: WHERE p_currency_code_tbl(i) IS NULL
378: OR NOT EXISTS(
371: 'CURRENCY_CODE',
372: 'VALUE',
373: p_currency_code_tbl(i),
374: DECODE(p_currency_code_tbl(i), NULL, PO_VAL_CONSTANTS.c_currency_code_not_null,
375: PO_VAL_CONSTANTS.c_currency_code_valid)
376: FROM DUAL
377: WHERE p_currency_code_tbl(i) IS NULL
378: OR NOT EXISTS(
379: SELECT 1
458: p_token1_name => 'COLUMN_NAME',
459: p_token1_value => 'RATE',
460: p_token2_name => 'VALUE',
461: p_token2_value => p_rate_tbl(i),
462: p_validation_id => PO_VAL_CONSTANTS.c_rate_null);
463: x_result_type := po_validations.c_result_type_failure;
464: END IF;
465:
466: -- validate rate_type is null if currency is functional
473: p_token1_name => 'COLUMN_NAME',
474: p_token1_value => 'RATE_TYPE',
475: p_token2_name => 'VALUE',
476: p_token2_value => p_rate_type_tbl(i),
477: p_validation_id => PO_VAL_CONSTANTS.c_rate_type_null);
478: x_result_type := po_validations.c_result_type_failure;
479: END IF;
480:
481: -- validate rate_date is null if currency is functional
488: p_token1_name => 'COLUMN_NAME',
489: p_token1_value => 'RATE_DATE',
490: p_token2_name => 'VALUE',
491: p_token2_value => p_rate_date_tbl(i),
492: p_validation_id => PO_VAL_CONSTANTS.c_rate_date_null);
493: x_result_type := po_validations.c_result_type_failure;
494: END IF;
495: ELSE
496: -- currency is not functional
505: p_token1_name => 'COLUMN_NAME',
506: p_token1_value => 'RATE',
507: p_token2_name => 'VALUE',
508: p_token2_value => p_rate_tbl(i),
509: p_validation_id => PO_VAL_CONSTANTS.c_rate_not_null);
510: x_result_type := po_validations.c_result_type_failure;
511: END IF;
512:
513: -- validate rate is positive
520: p_token1_name => 'COLUMN_NAME',
521: p_token1_value => 'RATE',
522: p_token2_name => 'VALUE',
523: p_token2_value => p_rate_tbl(i),
524: p_validation_id => PO_VAL_CONSTANTS.c_rate_ge_zero);
525: x_result_type := po_validations.c_result_type_failure;
526: END IF;
527:
528: -- validate rate_type is not null if currency is functional
535: p_token1_name => 'COLUMN_NAME',
536: p_token1_value => 'RATE_TYPE',
537: p_token2_name => 'VALUE',
538: p_token2_value => p_rate_type_tbl(i),
539: p_validation_id => PO_VAL_CONSTANTS.c_rate_type_not_null);
540: x_result_type := po_validations.c_result_type_failure;
541: END IF;
542:
543: -- validate rate against g1_currency_api.get_rate()
560: p_column_val => p_rate_tbl(i),
561: p_message_name => 'PO_PDOI_INVALID_RATE',
562: p_token1_name => 'VALUE',
563: p_token1_value => p_rate_tbl(i),
564: p_validation_id => PO_VAL_CONSTANTS.c_rate_valid);
565: x_result_type := po_validations.c_result_type_failure;
566: END IF;
567: END IF;
568: END IF;
589: 'RATE_TYPE',
590: p_rate_type_tbl(i),
591: 'VALUE',
592: p_rate_type_tbl(i),
593: PO_VAL_CONSTANTS.c_rate_type_valid
594: FROM DUAL
595: WHERE p_func_currency_code <> NVL(p_currency_code_tbl(i), ' ')
596: AND p_rate_type_tbl(i) IS NOT NULL
597: AND NOT EXISTS(SELECT 1
664: 'COLUMN_NAME',
665: 'AGENT_ID',
666: 'VALUE',
667: p_agent_id_tbl(i),
668: DECODE(p_agent_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_agent_id_not_null,
669: PO_VAL_CONSTANTS.c_agent_id_valid)
670: FROM DUAL
671: WHERE p_agent_id_tbl(i) IS NULL
672: OR NOT EXISTS(
665: 'AGENT_ID',
666: 'VALUE',
667: p_agent_id_tbl(i),
668: DECODE(p_agent_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_agent_id_not_null,
669: PO_VAL_CONSTANTS.c_agent_id_valid)
670: FROM DUAL
671: WHERE p_agent_id_tbl(i) IS NULL
672: OR NOT EXISTS(
673: SELECT 1
771: 'COLUMN_NAME',
772: 'VENDOR_ID',
773: 'VALUE',
774: p_vendor_id_tbl(i),
775: DECODE(p_vendor_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_vendor_not_null,
776: PO_VAL_CONSTANTS.c_vendor_valid)
777: FROM DUAL
778: WHERE p_vendor_id_tbl(i) IS NULL
779: OR NOT EXISTS(SELECT 1
772: 'VENDOR_ID',
773: 'VALUE',
774: p_vendor_id_tbl(i),
775: DECODE(p_vendor_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_vendor_not_null,
776: PO_VAL_CONSTANTS.c_vendor_valid)
777: FROM DUAL
778: WHERE p_vendor_id_tbl(i) IS NULL
779: OR NOT EXISTS(SELECT 1
780: FROM po_suppliers_val_v psv
808: 'COLUMN_NAME',
809: 'VENDOR_SITE_ID',
810: 'VALUE',
811: p_vendor_site_id_tbl(i),
812: DECODE(p_vendor_site_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_vendor_site_not_null,
813: PO_VAL_CONSTANTS.c_vendor_site_valid)
814: FROM DUAL
815: WHERE p_vendor_id_tbl(i) IS NOT NULL
816: AND ( p_vendor_site_id_tbl(i) IS NULL AND p_type_lookup_code_tbl(i) <> 'QUOTATION' ) /* 8913559 */
809: 'VENDOR_SITE_ID',
810: 'VALUE',
811: p_vendor_site_id_tbl(i),
812: DECODE(p_vendor_site_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_vendor_site_not_null,
813: PO_VAL_CONSTANTS.c_vendor_site_valid)
814: FROM DUAL
815: WHERE p_vendor_id_tbl(i) IS NOT NULL
816: AND ( p_vendor_site_id_tbl(i) IS NULL AND p_type_lookup_code_tbl(i) <> 'QUOTATION' ) /* 8913559 */
817: OR ( p_vendor_site_id_tbl(i) IS NOT NULL
844: 'VENDOR_CONTACT_ID',
845: p_vendor_contact_id_tbl(i),
846: 'VALUE',
847: p_vendor_contact_id_tbl(i),
848: PO_VAL_CONSTANTS.c_vendor_contact_valid
849: FROM DUAL
850: WHERE p_vendor_id_tbl(i) IS NOT NULL
851: AND p_vendor_site_id_tbl(i) IS NOT NULL
852: AND p_vendor_contact_id_tbl(i) IS NOT NULL
887: p_token1_name => 'VENDOR_SITE_ID',
888: p_token1_value => p_vendor_site_id_tbl(i),
889: p_token2_name => 'VENDOR_ID',
890: p_token2_value => p_vendor_id_tbl(i),
891: p_validation_id => PO_VAL_CONSTANTS.c_vendor_site_ccr_valid);
892: x_result_type := po_validations.c_result_type_failure;
893: END IF;
894: END;
895: END LOOP;
960: 'COLUMN_NAME',
961: 'SHIP_TO_LOCATION_ID',
962: 'VALUE',
963: p_ship_to_location_id_tbl(i),
964: DECODE(p_ship_to_location_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_ship_to_location_id_not_null,
965: PO_VAL_CONSTANTS.c_ship_to_location_id_valid)
966: FROM DUAL
967: WHERE (p_ship_to_location_id_tbl(i) IS NULL
968: -- Bug 7007502: Allow bill_to_loc to be NULL for Quotations.
961: 'SHIP_TO_LOCATION_ID',
962: 'VALUE',
963: p_ship_to_location_id_tbl(i),
964: DECODE(p_ship_to_location_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_ship_to_location_id_not_null,
965: PO_VAL_CONSTANTS.c_ship_to_location_id_valid)
966: FROM DUAL
967: WHERE (p_ship_to_location_id_tbl(i) IS NULL
968: -- Bug 7007502: Allow bill_to_loc to be NULL for Quotations.
969: AND p_type_lookup_code_tbl(i) <> 'QUOTATION')
1043: 'COLUMN_NAME',
1044: 'BILL_TO_LOCATION_ID',
1045: 'VALUE',
1046: p_bill_to_location_id_tbl(i),
1047: DECODE(p_bill_to_location_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_bill_to_location_id_not_null,
1048: PO_VAL_CONSTANTS.c_bill_to_location_id_valid)
1049: FROM DUAL
1050: WHERE (p_bill_to_location_id_tbl(i) IS NULL
1051: -- Bug 7007502: Allow bill_to_loc to be NULL for Quotations.
1044: 'BILL_TO_LOCATION_ID',
1045: 'VALUE',
1046: p_bill_to_location_id_tbl(i),
1047: DECODE(p_bill_to_location_id_tbl(i), NULL, PO_VAL_CONSTANTS.c_bill_to_location_id_not_null,
1048: PO_VAL_CONSTANTS.c_bill_to_location_id_valid)
1049: FROM DUAL
1050: WHERE (p_bill_to_location_id_tbl(i) IS NULL
1051: -- Bug 7007502: Allow bill_to_loc to be NULL for Quotations.
1052: AND p_type_lookup_code_tbl(i) <> 'QUOTATION')
1120: 'SHIP_VIA_LOOKUP_CODE',
1121: p_ship_via_lookup_code_tbl(i),
1122: 'VALUE',
1123: p_ship_via_lookup_code_tbl(i),
1124: PO_VAL_CONSTANTS.c_ship_via_lookup_code
1125: FROM DUAL
1126: WHERE p_ship_via_lookup_code_tbl(i) IS NOT NULL
1127: AND NOT EXISTS(
1128: SELECT 1
1191: 'FOB_LOOKUP_CODE',
1192: p_fob_lookup_code_tbl(i),
1193: 'VALUE',
1194: p_fob_lookup_code_tbl(i),
1195: PO_VAL_CONSTANTS.c_fob_lookup_code
1196: FROM DUAL
1197: WHERE p_fob_lookup_code_tbl(i) IS NOT NULL
1198: AND NOT EXISTS(
1199: SELECT 1
1262: 'FREIGHT_TERMS_LOOKUP_CODE',
1263: p_freight_terms_lookup_tbl(i),
1264: 'VALUE',
1265: p_freight_terms_lookup_tbl(i),
1266: PO_VAL_CONSTANTS.c_freight_terms_lookup_code
1267: FROM DUAL
1268: WHERE p_freight_terms_lookup_tbl(i) IS NOT NULL
1269: AND NOT EXISTS(
1270: SELECT 1
1333: 'SHIPPING_CONTROL',
1334: p_shipping_control_tbl(i),
1335: 'VALUE',
1336: p_shipping_control_tbl(i),
1337: PO_VAL_CONSTANTS.c_shipping_control
1338: FROM DUAL
1339: WHERE p_shipping_control_tbl(i) IS NOT NULL
1340: AND NOT EXISTS(
1341: SELECT 1
1399: p_token1_name => 'COLUMN_NAME',
1400: p_token1_value => 'ACCEPTANCE_DUE_DATE',
1401: p_token2_name => 'VALUE',
1402: p_token2_value => p_acceptance_due_date_tbl(i),
1403: p_validation_id => PO_VAL_CONSTANTS.c_acceptance_due_date);
1404: x_result_type := po_validations.c_result_type_failure;
1405: END IF;
1406: END LOOP;*/
1407:
1451: p_token1_name => 'COLUMN_NAME',
1452: p_token1_value => 'CANCEL_FLAG',
1453: p_token2_name => 'VALUE',
1454: p_token2_value => p_cancel_flag_tbl(i),
1455: p_validation_id => PO_VAL_CONSTANTS.c_cancel_flag);
1456:
1457: x_result_type := po_validations.c_result_type_failure;
1458: END IF;
1459: END LOOP;
1506: p_token1_name => 'COLUMN_NAME',
1507: p_token1_value => 'CLOSED_CODE',
1508: p_token2_name => 'VALUE',
1509: p_token2_value => p_closed_code_tbl(i),
1510: p_validation_id => PO_VAL_CONSTANTS.c_closed_code);
1511:
1512: x_result_type := po_validations.c_result_type_failure;
1513: END IF;
1514: END LOOP;
1561: p_token1_name => 'COLUMN_NAME',
1562: p_token1_value => 'PRINT COUNT',
1563: p_token2_name => 'VALUE',
1564: p_token2_value => 0,
1565: p_validation_id => PO_VAL_CONSTANTS.c_print_count);
1566: x_result_type := po_validations.c_result_type_failure;
1567: END IF;
1568: END LOOP;
1569:
1610: p_entity_id => p_id_tbl(i),
1611: p_column_name => 'APPROVAL_STATUS',
1612: p_column_val => p_approval_status_tbl(i),
1613: p_message_name => 'PO_PDOI_INVALID_STATUS',
1614: p_validation_id => PO_VAL_CONSTANTS.c_approval_status);
1615: x_result_type := po_validations.c_result_type_failure;
1616: END IF;
1617: END LOOP;
1618:
1662: p_token1_name => 'COLUMN_NAME',
1663: p_token1_value => 'AMOUNT_TO_ENCUMBER',
1664: p_token2_name => 'VALUE',
1665: p_token2_value => p_amount_to_encumber_tbl(i),
1666: p_validation_id => PO_VAL_CONSTANTS.c_amount_to_encumber);
1667: x_result_type := po_validations.c_result_type_failure;
1668: END IF;
1669: END LOOP;
1670:
1725: 'STYLE_ID',
1726: p_style_id_tbl(i),
1727: 'STYLE_ID',
1728: p_style_id_tbl(i),
1729: PO_VAL_CONSTANTS.c_style_id_valid
1730: FROM DUAL
1731: WHERE NOT EXISTS(SELECT 1
1732: FROM po_doc_style_headers pdsh
1733: WHERE pdsh.style_id = p_style_id_tbl(i) AND
1760: 'STYLE_ID',
1761: p_style_id_tbl(i),
1762: 'STYLE_ID',
1763: p_style_id_tbl(i),
1764: PO_VAL_CONSTANTS.c_style_id_complex_work
1765: FROM DUAL
1766: WHERE EXISTS(SELECT 1
1767: FROM po_doc_style_headers pdsh
1768: WHERE pdsh.style_id = p_style_id_tbl(i) AND
1824: p_token1_name => 'COLUMN_NAME',
1825: p_token1_value => 'ACCEPTANCE_REQUIRED_FLAG',
1826: p_token2_name => 'VALUE',
1827: p_token2_value => p_acceptance_required_flag_tbl(i),
1828: p_validation_id => PO_VAL_CONSTANTS.c_acceptance_required_flag
1829: );
1830:
1831: x_result_type := po_validations.c_result_type_failure;
1832: END IF;
1840: p_entity_id => p_id_tbl(i),
1841: p_column_name => 'ACCEPTANCE_REQUIRED_FLAG',
1842: p_column_val => p_acceptance_required_flag_tbl(i),
1843: p_message_name => 'PO_PDOI_INVALID_ACC_REQD_FLAG',
1844: p_validation_id => PO_VAL_CONSTANTS.c_acceptance_required_flag
1845: );
1846:
1847: x_result_type := po_validations.c_result_type_failure;
1848: END IF;
1902: p_token1_name => 'COLUMN_NAME',
1903: p_token1_value => 'AMOUNT_LIMIT',
1904: p_token2_name => 'VALUE',
1905: p_token2_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_amount_limit_tbl),
1906: p_validation_id => PO_VAL_CONSTANTS.c_amount_limit_gt_zero,
1907: x_results => x_results,
1908: x_result_type => x_result_type
1909: );
1910:
1920: p_entity_id => p_id_tbl(i),
1921: p_column_name => 'AMOUNT_AGREED',
1922: p_column_val => p_amount_agreed_tbl(i),
1923: p_message_name => PO_MESSAGE_S.PO_AMT_LMT_NOT_NULL,
1924: p_validation_id => PO_VAL_CONSTANTS.c_amount_agreed_not_null
1925: );
1926:
1927:
1928: x_result_type := po_validations.c_result_type_failure;
1935: p_entity_id => p_id_tbl(i),
1936: p_column_name => 'AMOUNT_LIMIT',
1937: p_column_val => p_amount_limit_tbl(i),
1938: p_message_name => PO_MESSAGE_S.PO_PO_AMT_LIMIT_CK_FAILED,
1939: p_validation_id => PO_VAL_CONSTANTS.c_amount_limit_gt_amt_agreed
1940: );
1941:
1942: x_result_type := po_validations.c_result_type_failure;
1943: END IF;