[Home] [Help]
1: PACKAGE BODY PO_VAL_LINES2 AS
2: -- $Header: PO_VAL_LINES2.plb 120.35.12020000.3 2013/02/10 21:14:29 vegajula ship $
3: c_entity_type_line CONSTANT VARCHAR2(30) := po_validations.c_entity_type_line;
4: -- The module base for this package.
5: d_package_base CONSTANT VARCHAR2(50) := po_log.get_package_base('PO_VAL_LINES2');
6:
7: -- The module base for the subprogram.
77: IS
78: d_mod CONSTANT VARCHAR2(100) := d_over_tolerance_error_flag;
79: BEGIN
80: IF x_result_set_id IS NULL THEN
81: x_result_set_id := po_validations.next_result_set_id();
82: END IF;
83:
84: IF po_log.d_proc THEN
85: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
99: token1_name,
100: token1_value,
101: validation_id)
102: SELECT x_result_set_id,
103: po_validations.c_result_type_failure,
104: c_entity_type_line,
105: p_id_tbl(i),
106: 'PO_PDOI_INVALID_OVER_TOL_ERROR',
107: 'OVER_TOLERANCE_ERROR_FLAG',
118: AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1)
119: AND plc.lookup_code = p_over_tolerance_err_flag_tbl(i));
120:
121: IF (SQL%ROWCOUNT > 0) THEN
122: x_result_type := po_validations.c_result_type_failure;
123: ELSE
124: x_result_type := po_validations.c_result_type_success;
125: END IF;
126:
120:
121: IF (SQL%ROWCOUNT > 0) THEN
122: x_result_type := po_validations.c_result_type_failure;
123: ELSE
124: x_result_type := po_validations.c_result_type_success;
125: END IF;
126:
127: IF po_log.d_proc THEN
128: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
124: x_result_type := po_validations.c_result_type_success;
125: END IF;
126:
127: IF po_log.d_proc THEN
128: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
129: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
130: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
131: END IF;
132: EXCEPTION
155: IF (x_results IS NULL) THEN
156: x_results := po_validation_results_type.new_instance();
157: END IF;
158:
159: x_result_type := po_validations.c_result_type_success;
160:
161: IF po_log.d_proc THEN
162: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
163: po_log.proc_begin(d_mod, 'p_expiration_date_tbl', p_expiration_date_tbl);
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
183: x_results.add_result(p_entity_type => c_entity_type_line,
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:
193: IF po_log.d_proc THEN
219: IS
220: d_mod CONSTANT VARCHAR2(100) := d_global_agreement_flag;
221: BEGIN
222: IF x_result_set_id IS NULL THEN
223: x_result_set_id := po_validations.next_result_set_id();
224: END IF;
225:
226: IF (x_results IS NULL) THEN
227: x_results := po_validation_results_type.new_instance();
235: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
236: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
237: END IF;
238:
239: x_result_type := po_validations.c_result_type_success;
240:
241: FOR i IN 1 .. p_id_tbl.COUNT LOOP
242: IF NVL(p_global_agreement_flag_tbl(i), 'N') = 'N'
243: AND p_purchase_basis_tbl(i) = 'TEMP LABOR' THEN
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:
254: -- Bug 14017950 - Enabling OSP line type for GBPA - PDOI will now import OSP lines for GBPA.
262: entity_id,
263: message_name,
264: validation_id)
265: SELECT x_result_set_id,
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
277: AND NVL(p_global_agreement_flag_tbl(i), 'N') = 'Y'
278: AND NVL(plt.outside_operation_flag, 'N') = 'Y');
279:
280: IF (SQL%ROWCOUNT > 0) THEN
281: x_result_type := po_validations.c_result_type_failure;
282: END IF;
283: END IF ;
284:
285: IF po_log.d_proc THEN
282: END IF;
283: END IF ;
284:
285: IF po_log.d_proc THEN
286: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
287: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
288: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
289: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
290: END IF;
321: po_log.proc_begin(d_mod, 'p_amount_tbl', p_amount_tbl);
322: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
323: END IF;
324:
325: x_result_type := po_validations.c_result_type_success;
326:
327: -- If order_type_lookup_code is 'RATE', amount has to be null
328: FOR i IN 1 .. p_id_tbl.COUNT LOOP
329: IF p_order_type_lookup_code_tbl(i) = 'RATE' AND p_amount_tbl(i) IS NOT NULL THEN
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
340: x_results.add_result(p_entity_type => c_entity_type_line,
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:
354: IF po_log.d_proc THEN
385: po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
386: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
387: END IF;
388:
389: x_result_type := po_validations.c_result_type_success;
390:
391: -- If services procurement not enabled, order_type_lookup_code
392: -- cannot be 'FIXED PRICE' or 'RATE'
393: IF (po_setup_s1.get_services_enabled_flag = 'N') THEN
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;
407:
445: po_log.proc_begin(d_mod, 'p_contractor_first_name_tbl', p_contractor_first_name_tbl);
446: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
447: END IF;
448:
449: x_result_type := po_validations.c_result_type_success;
450:
451: FOR i IN 1 .. p_id_tbl.COUNT LOOP
452: IF (p_purchase_basis_tbl(i) <> 'TEMP LABOR'
453: OR p_doc_type <> 'STANDARD')
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:
466: IF po_log.d_proc THEN
491: IS
492: d_mod CONSTANT VARCHAR2(100) := d_job_id;
493: BEGIN
494: IF x_result_set_id IS NULL THEN
495: x_result_set_id := po_validations.next_result_set_id();
496: END IF;
497:
498: IF (x_results IS NULL) THEN
499: x_results := po_validation_results_type.new_instance();
507: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
508: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
509: END IF;
510:
511: x_result_type := po_validations.c_result_type_success;
512:
513: FOR i IN 1 .. p_id_tbl.COUNT LOOP
514: IF p_purchase_basis_tbl(i) <> 'TEMP LABOR' AND p_job_id_tbl(i) IS NOT NULL THEN
515: x_results.add_result(p_entity_type => c_entity_type_line,
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),
525: p_column_name => 'JOB_ID',
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:
533: --check that job_id is valid within the relevant business group.
547: token2_name,
548: token2_value,
549: validation_id)
550: SELECT x_result_set_id,
551: po_validations.c_result_type_failure,
552: c_entity_type_line,
553: p_id_tbl(i),
554: 'PO_PDOI_SVC_INVALID_JOB',
555: 'JOB_ID',
573: AND NVL(TRUNC(pj.date_to),
574: TRUNC(SYSDATE)));
575:
576: IF (SQL%ROWCOUNT > 0) THEN
577: x_result_type := po_validations.c_result_type_failure;
578: END IF;
579: ELSE
580: -- Cross Business group profile is 'Y'
581: FORALL i IN 1 .. p_id_tbl.COUNT
592: token2_name,
593: token2_value,
594: validation_id)
595: SELECT x_result_set_id,
596: po_validations.c_result_type_failure,
597: c_entity_type_line,
598: p_id_tbl(i),
599: 'PO_PDOI_SVC_INVALID_JOB',
600: 'JOB_ID',
621: TRUNC(SYSDATE))
622: AND NVL(TRUNC(pbg.date_to),
623: TRUNC(SYSDATE)));
624: IF (SQL%ROWCOUNT > 0) THEN
625: x_result_type := po_validations.c_result_type_failure;
626: END IF;
627: END IF;
628:
629: -- job must be valid for the specified category
637: column_name,
638: column_val,
639: validation_id)
640: SELECT x_result_set_id,
641: po_validations.c_result_type_failure,
642: c_entity_type_line,
643: p_id_tbl(i),
644: 'PO_PDOI_SVC_INVALID_JOB_CAT',
645: 'JOB_ID',
658: AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pj.date_from), TRUNC(SYSDATE))
659: AND NVL(TRUNC(pj.date_to), TRUNC(SYSDATE)));
660:
661: IF (SQL%ROWCOUNT > 0) THEN
662: x_result_type := po_validations.c_result_type_failure;
663: END IF;
664:
665: IF po_log.d_proc THEN
666: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
662: x_result_type := po_validations.c_result_type_failure;
663: END IF;
664:
665: IF po_log.d_proc THEN
666: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
667: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
668: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
669: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
670: END IF;
691: IS
692: d_mod CONSTANT VARCHAR2(100) := d_job_business_group_id;
693: BEGIN
694: IF x_result_set_id IS NULL THEN
695: x_result_set_id := po_validations.next_result_set_id();
696: END IF;
697:
698: IF po_log.d_proc THEN
699: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
702: po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
703: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
704: END IF;
705:
706: x_result_type := po_validations.c_result_type_success;
707:
708: IF NVL(hr_general.get_xbg_profile, 'N') = 'N' THEN
709: -- xbg profile is N but job_business_group_id not in FSP
710: FORALL i IN 1 .. p_id_tbl.COUNT
717: column_name,
718: column_val,
719: validation_id)
720: SELECT x_result_set_id,
721: po_validations.c_result_type_failure,
722: c_entity_type_line,
723: p_id_tbl(i),
724: 'PO_PDOI_SVC_CANNOT_CROSS_BG',
725: 'JOB_BUSINESS_GROUP_ID',
732: FROM financials_system_parameters fsp
733: WHERE fsp.business_group_id = p_job_business_group_id_tbl(i));
734:
735: IF (SQL%ROWCOUNT > 0) THEN
736: x_result_type := po_validations.c_result_type_failure;
737: END IF;
738: ELSE
739: -- Cross Business group profile is 'Y', need to validate job business group id
740: FORALL i IN 1 .. p_id_tbl.COUNT
747: column_name,
748: column_val,
749: validation_id)
750: SELECT x_result_set_id,
751: po_validations.c_result_type_failure,
752: c_entity_type_line,
753: p_id_tbl(i),
754: 'PO_PDOI_SVC_INVALID_BG',
755: 'JOB_BUSINESS_GROUP_ID',
765: AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pbg.date_from), TRUNC(SYSDATE))
766: AND NVL(TRUNC(pbg.date_to), TRUNC(SYSDATE)));
767:
768: IF (SQL%ROWCOUNT > 0) THEN
769: x_result_type := po_validations.c_result_type_failure;
770: END IF;
771: END IF;
772:
773: IF po_log.d_proc THEN
770: END IF;
771: END IF;
772:
773: IF po_log.d_proc THEN
774: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
775: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
776: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
777: END IF;
778: EXCEPTION
806: po_log.proc_begin(d_mod, 'p_capital_expense_flag_tbl', p_capital_expense_flag_tbl);
807: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
808: END IF;
809:
810: x_result_type := po_validations.c_result_type_success;
811:
812: FOR i IN 1 .. p_id_tbl.COUNT LOOP
813: IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_capital_expense_flag_tbl(i) = 'Y' THEN
814: x_results.add_result(p_entity_type => c_entity_type_line,
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:
824: IF po_log.d_proc THEN
847: IS
848: d_mod CONSTANT VARCHAR2(100) := d_un_number_id;
849: BEGIN
850: IF x_result_set_id IS NULL THEN
851: x_result_set_id := po_validations.next_result_set_id();
852: END IF;
853:
854: IF (x_results IS NULL) THEN
855: x_results := po_validation_results_type.new_instance();
862: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
863: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
864: END IF;
865:
866: x_result_type := po_validations.c_result_type_success;
867:
868: FOR i IN 1 .. p_id_tbl.COUNT LOOP
869: IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_un_number_id_tbl(i) IS NOT NULL THEN
870: x_results.add_result(p_entity_type => c_entity_type_line,
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:
880: FORALL i IN 1 .. p_id_tbl.COUNT
889: token1_name,
890: token1_value,
891: validation_id)
892: SELECT x_result_set_id,
893: po_validations.c_result_type_failure,
894: c_entity_type_line,
895: p_id_tbl(i),
896: 'PO_PDOI_INVALID_UN_NUMBER_ID',
897: 'UN_NUMBER_ID',
906: FROM po_un_numbers_val_v pun
907: WHERE pun.un_number_id = p_un_number_id_tbl(i));
908:
909: IF (SQL%ROWCOUNT > 0) THEN
910: x_result_type := po_validations.c_result_type_failure;
911: END IF;
912:
913: IF po_log.d_proc THEN
914: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
910: x_result_type := po_validations.c_result_type_failure;
911: END IF;
912:
913: IF po_log.d_proc THEN
914: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
915: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
916: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
917: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
918: END IF;
938: IS
939: d_mod CONSTANT VARCHAR2(100) := d_hazard_class_id;
940: BEGIN
941: IF x_result_set_id IS NULL THEN
942: x_result_set_id := po_validations.next_result_set_id();
943: END IF;
944:
945: IF (x_results IS NULL) THEN
946: x_results := po_validation_results_type.new_instance();
953: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
954: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
955: END IF;
956:
957: x_result_type := po_validations.c_result_type_success;
958:
959: FOR i IN 1 .. p_id_tbl.COUNT LOOP
960: IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_hazard_class_id_tbl(i) IS NOT NULL THEN
961: x_results.add_result(p_entity_type => c_entity_type_line,
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:
971: FORALL i IN 1 .. p_id_tbl.COUNT
980: token1_name,
981: token1_value,
982: validation_id)
983: SELECT x_result_set_id,
984: po_validations.c_result_type_failure,
985: c_entity_type_line,
986: p_id_tbl(i),
987: 'PO_PDOI_INVALID_HAZ_ID',
988: 'HAZARD_CLASS_ID',
997: FROM po_hazard_classes_val_v phc
998: WHERE phc.hazard_class_id = p_hazard_class_id_tbl(i));
999:
1000: IF (SQL%ROWCOUNT > 0) THEN
1001: x_result_type := po_validations.c_result_type_failure;
1002: END IF;
1003:
1004: IF po_log.d_proc THEN
1005: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1001: x_result_type := po_validations.c_result_type_failure;
1002: END IF;
1003:
1004: IF po_log.d_proc THEN
1005: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1006: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1007: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1008: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1009: END IF;
1031: IS
1032: d_mod CONSTANT VARCHAR2(100) := d_item_id;
1033: BEGIN
1034: IF x_result_set_id IS NULL THEN
1035: x_result_set_id := po_validations.next_result_set_id();
1036: END IF;
1037:
1038: IF (x_results IS NULL) THEN
1039: x_results := po_validation_results_type.new_instance();
1048: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1049: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1050: END IF;
1051:
1052: x_result_type := po_validations.c_result_type_success;
1053:
1054: FOR i IN 1 .. p_id_tbl.COUNT LOOP
1055: IF (p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' OR p_order_type_lookup_code_tbl(i) = 'RATE')
1056: AND p_item_id_tbl(i) IS NOT NULL THEN
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),
1069: p_column_name => 'ITEM_ID',
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:
1079: -- If order_type_lookup_code is Quantity and outside_operation flag is 'Y'
1088: column_name,
1089: column_val,
1090: validation_id)
1091: SELECT x_result_set_id,
1092: po_validations.c_result_type_failure,
1093: c_entity_type_line,
1094: p_id_tbl(i),
1095: 'PO_PDOI_ITEM_NOT_NULL',
1096: 'ITEM_ID',
1106: AND p_line_type_id_tbl(i) = plt.line_type_id
1107: AND plt.outside_operation_flag = 'Y');
1108:
1109: IF (SQL%ROWCOUNT > 0) THEN
1110: x_result_type := po_validations.c_result_type_failure;
1111: END IF;
1112:
1113: -- if item id is not null, it has to exist in mtl_system_items table
1114: FORALL i IN 1 .. p_id_tbl.COUNT
1123: token1_name,
1124: token1_value,
1125: validation_id)
1126: SELECT x_result_set_id,
1127: po_validations.c_result_type_failure,
1128: c_entity_type_line,
1129: p_id_tbl(i),
1130: DECODE(plt.outside_operation_flag, 'N', 'PO_PDOI_INVALID_ITEM_ID', 'PO_PDOI_INVALID_OP_ITEM_ID'),
1131: 'ITEM_ID',
1151: AND TRUNC(NVL(msi.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
1152: AND TRUNC(NVL(msi.end_date_active, SYSDATE)) >= TRUNC(SYSDATE));
1153:
1154: IF (SQL%ROWCOUNT > 0) THEN
1155: x_result_type := po_validations.c_result_type_failure;
1156: END IF;
1157:
1158: IF po_log.d_proc THEN
1159: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1155: x_result_type := po_validations.c_result_type_failure;
1156: END IF;
1157:
1158: IF po_log.d_proc THEN
1159: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1160: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1161: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1162: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1163: END IF;
1187: IS
1188: d_mod CONSTANT VARCHAR2(100) := d_item_description;
1189: BEGIN
1190: IF x_result_set_id IS NULL THEN
1191: x_result_set_id := po_validations.next_result_set_id();
1192: END IF;
1193:
1194: IF po_log.d_proc THEN
1195: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1201: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1202: END IF;
1203:
1204: /* Bug 5366732 Modified the inner query to select item description from mtl_system_items_tl instead of from mtl_system_items */
1205: x_result_type := po_validations.c_result_type_success;
1206: FORALL i IN 1 .. p_id_tbl.COUNT
1207: INSERT INTO po_validation_results_gt
1208: (result_set_id,
1209: result_type,
1215: token1_name,
1216: token1_value,
1217: validation_id)
1218: SELECT x_result_set_id,
1219: po_validations.c_result_type_failure,
1220: c_entity_type_line,
1221: p_id_tbl(i),
1222: DECODE(p_item_description_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_DIFF_ITEM_DESC'),
1223: 'ITEM_DESCRIPTION',
1246:
1247:
1248:
1249: IF (SQL%ROWCOUNT > 0) THEN
1250: x_result_type := po_validations.c_result_type_failure;
1251: END IF;
1252:
1253: IF po_log.d_proc THEN
1254: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1250: x_result_type := po_validations.c_result_type_failure;
1251: END IF;
1252:
1253: IF po_log.d_proc THEN
1254: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1255: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1256: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1257: END IF;
1258: EXCEPTION
1281: d_mod CONSTANT VARCHAR2(100) := d_unit_meas_lookup_code;
1282: l_service_uom_class VARCHAR2(2000);
1283: BEGIN
1284: IF x_result_set_id IS NULL THEN
1285: x_result_set_id := po_validations.next_result_set_id();
1286: END IF;
1287:
1288: IF (x_results IS NULL) THEN
1289: x_results := po_validation_results_type.new_instance();
1299: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1300: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1301: END IF;
1302:
1303: x_result_type := po_validations.c_result_type_success;
1304:
1305: -- If order_type_lookup_code is 'FIXED PRICE', unit_meas_lookup_code has
1306: -- to be null. Otherwise, it cannot be null.
1307: FOR i IN 1 .. p_id_tbl.COUNT LOOP
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),
1319: p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
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:
1329: FORALL i IN 1 .. p_id_tbl.COUNT
1342: token2_value,
1343: token3_value,
1344: validation_id)
1345: SELECT x_result_set_id,
1346: po_validations.c_result_type_failure,
1347: c_entity_type_line,
1348: p_id_tbl(i),
1349: 'PO_PDOI_ITEM_RELATED_INFO',
1350: 'UNIT_MEAS_LOOKUP_CODE',
1367: AND miuv.organization_id = p_inventory_org_id
1368: AND miuv.unit_of_measure = p_unit_meas_lookup_code_tbl(i));
1369:
1370: IF (SQL%ROWCOUNT > 0) THEN
1371: x_result_type := po_validations.c_result_type_failure;
1372: END IF;
1373:
1374: -- check to see if x_uom_code is valid in po_units_of_measure_val_v
1375: FORALL i IN 1 .. p_id_tbl.COUNT
1384: token1_name,
1385: token1_value,
1386: validation_id)
1387: SELECT x_result_set_id,
1388: po_validations.c_result_type_failure,
1389: c_entity_type_line,
1390: p_id_tbl(i),
1391: 'PO_PDOI_INVALID_UOM_CODE',
1392: 'UNIT_MEAS_LOOKUP_CODE',
1402: FROM po_units_of_measure_val_v pumv
1403: WHERE pumv.unit_of_measure = p_unit_meas_lookup_code_tbl(i));
1404:
1405: IF (SQL%ROWCOUNT > 0) THEN
1406: x_result_type := po_validations.c_result_type_failure;
1407: END IF;
1408:
1409: -- validation for AMOUNT based line type
1410: FORALL i IN 1 .. p_id_tbl.COUNT
1423: token2_value,
1424: token3_value,
1425: validation_id)
1426: SELECT x_result_set_id,
1427: po_validations.c_result_type_failure,
1428: c_entity_type_line,
1429: p_id_tbl(i),
1430: 'PO_PDOI_INVALID_LINE_TYPE_INFO',
1431: 'UNIT_MEAS_LOOKUP_CODE',
1442: AND p_order_type_lookup_code_tbl(i) = 'AMOUNT'
1443: AND p_unit_meas_lookup_code_tbl(i) <> pltb.unit_of_measure;
1444:
1445: IF (SQL%ROWCOUNT > 0) THEN
1446: x_result_type := po_validations.c_result_type_failure;
1447: END IF;
1448:
1449: -- validation for 'RATE' based line
1450: l_service_uom_class := NVL(FND_PROFILE.value('PO_RATE_UOM_CLASS'), '999');
1462: token1_value,
1463: token2_value,
1464: validation_id)
1465: SELECT x_result_set_id,
1466: po_validations.c_result_type_failure,
1467: c_entity_type_line,
1468: p_id_tbl(i),
1469: 'PO_PDOI_SVC_INVALID_UOM',
1470: 'UNIT_MEAS_LOOKUP_CODE',
1482: AND muomv.unit_of_measure = p_unit_meas_lookup_code_tbl(i)
1483: AND TRUNC(sysdate) < NVL(muomv.disable_date, TRUNC(sysdate) + 1));
1484:
1485: IF (SQL%ROWCOUNT > 0) THEN
1486: x_result_type := po_validations.c_result_type_failure;
1487: END IF;
1488:
1489: IF po_log.d_proc THEN
1490: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1486: x_result_type := po_validations.c_result_type_failure;
1487: END IF;
1488:
1489: IF po_log.d_proc THEN
1490: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1491: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1492: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1493: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1494: END IF;
1516: IS
1517: d_mod CONSTANT VARCHAR2(100) := d_item_revision;
1518: BEGIN
1519: IF x_result_set_id IS NULL THEN
1520: x_result_set_id := po_validations.next_result_set_id();
1521: END IF;
1522:
1523: IF po_log.d_proc THEN
1524: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1528: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1529: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1530: END IF;
1531:
1532: x_result_type := po_validations.c_result_type_success;
1533:
1534: -- if order_type_lookup_code is FIXED PRICE or RATE, or item id is null,
1535: -- then item revision has to be NULL
1536: FOR i IN 1 .. p_id_tbl.COUNT LOOP
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:
1550: -- check to see if there are x_item_revision exists in mtl_item_revisions
1565: token2_value,
1566: token3_value,
1567: validation_id)
1568: SELECT x_result_set_id,
1569: po_validations.c_result_type_failure,
1570: c_entity_type_line,
1571: p_id_tbl(i),
1572: 'PO_PDOI_ITEM_RELATED_INFO',
1573: 'ITEM_REVISION',
1588: WHERE mir.inventory_item_id = p_item_id_tbl(i)
1589: AND mir.revision = p_item_revision_tbl(i));
1590:
1591: IF (SQL%ROWCOUNT > 0) THEN
1592: x_result_type := po_validations.c_result_type_failure;
1593: END IF;
1594:
1595: IF po_log.d_proc THEN
1596: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1592: x_result_type := po_validations.c_result_type_failure;
1593: END IF;
1594:
1595: IF po_log.d_proc THEN
1596: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1597: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1598: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1599: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1600: END IF;
1627: x_flag mtl_category_sets_v.validate_flag%TYPE;
1628: x_category_set_id mtl_category_sets_v.category_set_id%TYPE;
1629: BEGIN
1630: IF x_result_set_id IS NULL THEN
1631: x_result_set_id := po_validations.next_result_set_id();
1632: END IF;
1633:
1634: IF (x_results IS NULL) THEN
1635: x_results := po_validation_results_type.new_instance();
1646: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1647: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1648: END IF;
1649:
1650: x_result_type := po_validations.c_result_type_success;
1651:
1652: -- Find out the default category_set_id and flag for function_area
1653: -- of PURCHASING".
1654: SELECT validate_flag,
1677: token3_name,
1678: token3_value,
1679: validation_id)
1680: SELECT x_result_set_id,
1681: po_validations.c_result_type_failure,
1682: c_entity_type_line,
1683: p_id_tbl(i),
1684: 'PO_PDOI_ITEM_RELATED_INFO',
1685: 'CATEGORY_ID',
1706: AND SYSDATE < NVL(mcs.disable_date, SYSDATE + 1)
1707: AND mcs.enabled_flag = 'Y');
1708:
1709: IF (SQL%ROWCOUNT > 0) THEN
1710: x_result_type := po_validations.c_result_type_failure;
1711: END IF;
1712:
1713: IF x_flag = 'Y' THEN
1714: FORALL i IN 1 .. p_id_tbl.COUNT
1723: token1_name,
1724: token1_value,
1725: validation_id)
1726: SELECT x_result_set_id,
1727: po_validations.c_result_type_failure,
1728: c_entity_type_line,
1729: p_id_tbl(i),
1730: 'PO_PDOI_INVALID_CATEGORY_ID',
1731: 'CATEGORY_ID',
1748:
1749: -- bug5111418
1750: -- fail the record if this validation fails
1751: IF (SQL%ROWCOUNT > 0) THEN
1752: x_result_type := po_validations.c_result_type_failure;
1753: END IF;
1754: ELSE
1755: FORALL i IN 1 .. p_id_tbl.COUNT
1756: INSERT INTO po_validation_results_gt
1764: token1_name,
1765: token1_value,
1766: validation_id)
1767: SELECT x_result_set_id,
1768: po_validations.c_result_type_failure,
1769: c_entity_type_line,
1770: p_id_tbl(i),
1771: 'PO_PDOI_INVALID_CATEGORY_ID',
1772: 'CATEGORY_ID',
1785: AND SYSDATE < NVL(mcs.disable_date, SYSDATE + 1)
1786: AND mcs.enabled_flag = 'Y');
1787:
1788: IF (SQL%ROWCOUNT > 0) THEN
1789: x_result_type := po_validations.c_result_type_failure;
1790: END IF;
1791: END IF;
1792:
1793: IF po_log.d_proc THEN
1790: END IF;
1791: END IF;
1792:
1793: IF po_log.d_proc THEN
1794: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1795: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1796: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1797: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1798: END IF;
1818: IS
1819: d_mod CONSTANT VARCHAR2(100) := d_ip_category_id;
1820: BEGIN
1821: IF x_result_set_id IS NULL THEN
1822: x_result_set_id := po_validations.next_result_set_id();
1823: END IF;
1824:
1825: IF (x_results IS NULL) THEN
1826: x_results := po_validation_results_type.new_instance();
1832: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1833: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1834: END IF;
1835:
1836: x_result_type := po_validations.c_result_type_success;
1837: -- validate ip_category_id is not null
1838: po_validation_helper.not_null(p_calling_module => c_program_pdoi,
1839: p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_ip_category_id_tbl),
1840: p_entity_id_tbl => p_id_tbl,
1862: token1_name,
1863: token1_value,
1864: validation_id)
1865: SELECT x_result_set_id,
1866: po_validations.c_result_type_failure,
1867: c_entity_type_line,
1868: p_id_tbl(i),
1869: 'PO_PDOI_INVALID_IP_CATEGORY_ID',
1870: 'IP_CATEGORY_ID',
1880: FROM icx_cat_categories_v
1881: WHERE rt_category_id = p_ip_category_id_tbl(i));
1882:
1883: IF (SQL%ROWCOUNT > 0) THEN
1884: x_result_type := po_validations.c_result_type_failure;
1885: END IF;
1886:
1887: IF po_log.d_proc THEN
1888: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1884: x_result_type := po_validations.c_result_type_failure;
1885: END IF;
1886:
1887: IF po_log.d_proc THEN
1888: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1889: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1890: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1891: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1892: END IF;
1918: d_mod CONSTANT VARCHAR2(100) := d_unit_price;
1919: X_li_unit_price NUMBER := NULL; --bug 12631717
1920: BEGIN
1921: IF x_result_set_id IS NULL THEN
1922: x_result_set_id := po_validations.next_result_set_id();
1923: END IF;
1924:
1925: IF (x_results IS NULL) THEN
1926: x_results := po_validation_results_type.new_instance();
1934: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1935: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1936: END IF;
1937:
1938: x_result_type := po_validations.c_result_type_success;
1939: -- If order_type_lookup_code is not 'FIXED PRICE', unit_price cannot be
1940: -- null and cannot be less than zero.
1941: FOR i IN 1 .. p_id_tbl.COUNT LOOP
1942: IF p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_unit_price_tbl(i) IS NULL THEN
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),
1955: p_column_name => 'UNIT_PRICE',
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:
1967:
1993: token1_value,
1994: token2_value,
1995: validation_id)
1996: SELECT x_result_set_id,
1997: po_validations.c_result_type_failure,
1998: c_entity_type_line,
1999: p_id_tbl(i),
2000: 'PO_PDOI_INVALID_LINE_TYPE_INFO',
2001: 'UNIT_PRICE',
2014: AND X_li_unit_price = plt.unit_price);
2015:
2016:
2017: IF (SQL%ROWCOUNT > 0) THEN
2018: x_result_type := po_validations.c_result_type_failure;
2019: END IF;
2020:
2021: ELSE
2022: INSERT INTO po_validation_results_gt
2032: token1_value,
2033: token2_value,
2034: validation_id)
2035: SELECT x_result_set_id,
2036: po_validations.c_result_type_failure,
2037: c_entity_type_line,
2038: p_id_tbl(i),
2039: 'PO_PDOI_INVALID_LINE_TYPE_INFO',
2040: 'UNIT_PRICE',
2052: WHERE p_line_type_id_tbl(i) = plt.line_type_id
2053: AND p_unit_price_tbl(i) = plt.unit_price);
2054:
2055: IF (SQL%ROWCOUNT > 0) THEN
2056: x_result_type := po_validations.c_result_type_failure;
2057: END IF;
2058: END IF;
2059: END LOOP;
2060: /*Bug 12631717: end */
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:
2075: IF po_log.d_proc THEN
2072: END IF;
2073: END LOOP;
2074:
2075: IF po_log.d_proc THEN
2076: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2077: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2078: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2079: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
2080: END IF;
2110: po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2111: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2112: END IF;
2113:
2114: x_result_type := po_validations.c_result_type_success;
2115:
2116: -- If order_type_lookup_code is not 'FIXED PRICE' or 'RATE', quantity cannot
2117: -- be less than zero
2118: FOR i IN 1 .. p_id_tbl.COUNT LOOP
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
2136: p_quantity_tbl(i) IS NOT NULL THEN
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:
2147: IF po_log.d_proc THEN
2179: po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2180: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2181: END IF;
2182:
2183: x_result_type := po_validations.c_result_type_success;
2184:
2185: -- If order_type_lookup_code is not 'FIXED PRICE' or 'RATE', amount has to
2186: -- be null
2187: FOR i IN 1 .. p_id_tbl.COUNT LOOP
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:
2200: IF po_log.d_proc THEN
2232: po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2233: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2234: END IF;
2235:
2236: x_result_type := po_validations.c_result_type_success;
2237:
2238: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2239: IF (p_order_type_lookup_code_tbl(i) = 'RATE' AND
2240: p_rate_type_tbl(i) = 'User') THEN
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:
2251: IF po_log.d_proc THEN
2281: x_results := po_validation_results_type.new_instance();
2282: END IF;
2283:
2284: IF x_result_set_id IS NULL THEN
2285: x_result_set_id := po_validations.next_result_set_id();
2286: END IF;
2287:
2288: IF po_log.d_proc THEN
2289: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2293: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2294: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2295: END IF;
2296:
2297: x_result_type := po_validations.c_result_type_success;
2298:
2299: -- Line num must be populated and cannot be <= 0.
2300: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2301: IF (p_line_num_tbl(i) IS NULL) THEN
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),
2314: p_column_name => 'LINE_NUM',
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:
2326:
2340: token1_name,
2341: token1_value,
2342: validation_id)
2343: SELECT x_result_set_id,
2344: po_validations.c_result_type_failure,
2345: c_entity_type_line,
2346: p_id_tbl(i),
2347: 'PO_PDOI_LINE_NUM_UNIQUE',
2348: 'LINE_NUM',
2369: AND PLD.line_num = p_line_num_tbl(i)
2370: AND NVL(PLD.delete_flag, 'N') <> 'Y'));
2371:
2372: IF (SQL%ROWCOUNT > 0) THEN
2373: x_result_type := po_validations.c_result_type_failure;
2374: END IF;
2375:
2376: IF po_log.d_proc THEN
2377: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2373: x_result_type := po_validations.c_result_type_failure;
2374: END IF;
2375:
2376: IF po_log.d_proc THEN
2377: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2378: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2379: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2380: END IF;
2381: EXCEPTION
2399: IS
2400: d_mod CONSTANT VARCHAR2(100) := d_po_line_id;
2401: BEGIN
2402: IF x_result_set_id IS NULL THEN
2403: x_result_set_id := po_validations.next_result_set_id();
2404: END IF;
2405:
2406: IF po_log.d_proc THEN
2407: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2409: po_log.proc_begin(d_mod, 'p_po_header_id_tbl', p_po_header_id_tbl);
2410: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2411: END IF;
2412:
2413: x_result_type := po_validations.c_result_type_success;
2414: FORALL i IN 1 .. p_id_tbl.COUNT
2415: INSERT INTO po_validation_results_gt
2416: (result_set_id,
2417: result_type,
2425: token2_name,
2426: token2_value,
2427: validation_id)
2428: SELECT x_result_set_id,
2429: po_validations.c_result_type_failure,
2430: c_entity_type_line,
2431: p_id_tbl(i),
2432: DECODE(p_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_LINE_ID_UNIQUE'),
2433: 'PO_LINE_ID',
2446: WHERE pln.po_header_id = p_po_header_id_tbl(i)
2447: AND pln.po_line_id = p_po_line_id_tbl(i)));
2448:
2449: IF (SQL%ROWCOUNT > 0) THEN
2450: x_result_type := po_validations.c_result_type_failure;
2451: END IF;
2452:
2453: IF po_log.d_proc THEN
2454: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2450: x_result_type := po_validations.c_result_type_failure;
2451: END IF;
2452:
2453: IF po_log.d_proc THEN
2454: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2455: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2456: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2457: END IF;
2458: EXCEPTION
2475: IS
2476: d_mod CONSTANT VARCHAR2(100) := d_line_type_id;
2477: BEGIN
2478: IF x_result_set_id IS NULL THEN
2479: x_result_set_id := po_validations.next_result_set_id();
2480: END IF;
2481:
2482: IF po_log.d_proc THEN
2483: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2484: po_log.proc_begin(d_mod, 'p_line_type_id_tbl', p_line_type_id_tbl);
2485: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2486: END IF;
2487:
2488: x_result_type := po_validations.c_result_type_success;
2489: FORALL i IN 1 .. p_id_tbl.COUNT
2490: INSERT INTO po_validation_results_gt
2491: (result_set_id,
2492: result_type,
2500: token2_name,
2501: token2_value,
2502: validation_id)
2503: SELECT x_result_set_id,
2504: po_validations.c_result_type_failure,
2505: c_entity_type_line,
2506: p_id_tbl(i),
2507: DECODE(p_line_type_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_LINE_TYPE_ID'),
2508: 'LINE_TYPE_ID',
2519: FROM po_line_types_val_v pltv
2520: WHERE pltv.line_type_id = p_line_type_id_tbl(i));
2521:
2522: IF (SQL%ROWCOUNT > 0) THEN
2523: x_result_type := po_validations.c_result_type_failure;
2524: END IF;
2525:
2526: IF po_log.d_proc THEN
2527: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2523: x_result_type := po_validations.c_result_type_failure;
2524: END IF;
2525:
2526: IF po_log.d_proc THEN
2527: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2528: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2529: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2530: END IF;
2531: EXCEPTION
2550: IS
2551: d_mod CONSTANT VARCHAR2(100) := d_style_related_info;
2552: BEGIN
2553: IF x_result_set_id IS NULL THEN
2554: x_result_set_id := po_validations.next_result_set_id();
2555: END IF;
2556:
2557: IF po_log.d_proc THEN
2558: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2560: po_log.proc_begin(d_mod, 'p_line_type_id_tbl', p_line_type_id_tbl);
2561: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2562: END IF;
2563:
2564: x_result_type := po_validations.c_result_type_success;
2565:
2566: -- validate line_type_id is valid for the given style
2567: FORALL i IN 1 .. p_id_tbl.COUNT
2568: INSERT INTO po_validation_results_gt
2578: token2_name,
2579: token2_value,
2580: validation_id)
2581: SELECT x_result_set_id,
2582: po_validations.c_result_type_failure,
2583: c_entity_type_line,
2584: p_id_tbl(i),
2585: 'PO_PDOI_LINE_TYPE_ID_STYLE',
2586: 'LINE_TYPE_ID',
2601: AND pdv.style_allowed_value = to_char(p_line_type_id_tbl(i))
2602: AND nvl(pdv.enabled_flag, 'N') = 'Y');
2603:
2604: IF (SQL%ROWCOUNT > 0) THEN
2605: x_result_type := po_validations.c_result_type_failure;
2606: END IF;
2607:
2608: -- validate the purchase_basis is valid for the given style
2609: FORALL i IN 1 .. p_id_tbl.COUNT
2620: token2_name,
2621: token2_value,
2622: validation_id)
2623: SELECT x_result_set_id,
2624: po_validations.c_result_type_failure,
2625: c_entity_type_line,
2626: p_id_tbl(i),
2627: 'PO_PDOI_PURCHASE_BASIS_STYLE',
2628: 'PURCHASE_BASIS',
2640: AND pdsv.style_allowed_value = p_purchase_basis_tbl(i)
2641: AND nvl(pdsv.enabled_flag, 'N') = 'Y');
2642:
2643: IF (SQL%ROWCOUNT > 0) THEN
2644: x_result_type := po_validations.c_result_type_failure;
2645: END IF;
2646:
2647: IF po_log.d_proc THEN
2648: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2644: x_result_type := po_validations.c_result_type_failure;
2645: END IF;
2646:
2647: IF po_log.d_proc THEN
2648: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2649: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2650: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2651: END IF;
2652: EXCEPTION
2670: IS
2671: d_mod CONSTANT VARCHAR2(100) := d_price_type_lookup_code;
2672: BEGIN
2673: IF x_result_set_id IS NULL THEN
2674: x_result_set_id := po_validations.next_result_set_id();
2675: END IF;
2676:
2677: IF po_log.d_proc THEN
2678: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2679: po_log.proc_begin(d_mod, 'p_price_type_lookup_code_tbl', p_price_type_lookup_code_tbl);
2680: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2681: END IF;
2682:
2683: x_result_type := po_validations.c_result_type_success;
2684: FORALL i IN 1 .. p_id_tbl.COUNT
2685: INSERT INTO po_validation_results_gt
2686: (result_set_id,
2687: result_type,
2693: token1_name,
2694: token1_value,
2695: validation_id)
2696: SELECT x_result_set_id,
2697: po_validations.c_result_type_failure,
2698: c_entity_type_line,
2699: p_id_tbl(i),
2700: 'PO_PDOI_INVALID_PRICE_TYPE',
2701: 'PRICE_TYPE_LOOKUP_CODE',
2712: AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1)
2713: AND plc.lookup_code = p_price_type_lookup_code_tbl(i));
2714:
2715: IF (SQL%ROWCOUNT > 0) THEN
2716: x_result_type := po_validations.c_result_type_failure;
2717: END IF;
2718:
2719: IF po_log.d_proc THEN
2720: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2716: x_result_type := po_validations.c_result_type_failure;
2717: END IF;
2718:
2719: IF po_log.d_proc THEN
2720: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2721: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2722: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2723: END IF;
2724: EXCEPTION
2756: po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
2757: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2758: END IF;
2759:
2760: x_result_type := po_validations.c_result_type_success;
2761:
2762: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2763: IF (p_purchase_basis_tbl(i) = 'TEMP LABOR') THEN
2764: IF (p_start_date_tbl(i) IS NULL) THEN
2767: p_entity_id => p_id_tbl(i),
2768: p_column_name => 'START_DATE',
2769: p_column_val => p_start_date_tbl(i),
2770: p_message_name => 'PO_PDOI_SVC_MUST_START_DATE');
2771: x_result_type := po_validations.c_result_type_failure;
2772: ELSIF(NVL(p_expiration_date_tbl(i), p_start_date_tbl(i)) < p_start_date_tbl(i)) THEN
2773: -- If expiration date provided, it must be later than the start date
2774: x_results.add_result(p_entity_type => c_entity_type_line,
2775: p_entity_id => p_id_tbl(i),
2775: p_entity_id => p_id_tbl(i),
2776: p_column_name => 'START_DATE',
2777: p_column_val => p_start_date_tbl(i),
2778: p_message_name => 'PO_SVC_NO_START_END_DATE');
2779: x_result_type := po_validations.c_result_type_failure;
2780: END IF;
2781: ELSE
2782: -- purchase basis is not 'TEMP LABOR'
2783: IF (p_start_date_tbl(i) IS NOT NULL) THEN
2786: p_entity_id => p_id_tbl(i),
2787: p_column_name => 'START_DATE',
2788: p_column_val => p_start_date_tbl(i),
2789: p_message_name => 'PO_SVC_NO_START_END_DATE');
2790: x_result_type := po_validations.c_result_type_failure;
2791: END IF;
2792:
2793: IF (p_start_date_tbl(i) IS NOT NULL) THEN
2794: -- expiration date must be null
2796: p_entity_id => p_id_tbl(i),
2797: p_column_name => 'EXPIRATION_DATE',
2798: p_column_val => p_expiration_date_tbl(i),
2799: p_message_name => 'PO_SVC_NO_START_END_DATE');
2800: x_result_type := po_validations.c_result_type_failure;
2801: END IF;
2802: END IF;
2803: END LOOP;
2804:
2829: IS
2830: d_mod CONSTANT VARCHAR2(100) := d_item_id_standard;
2831: BEGIN
2832: IF x_result_set_id IS NULL THEN
2833: x_result_set_id := po_validations.next_result_set_id();
2834: END IF;
2835:
2836: IF po_log.d_proc THEN
2837: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2840: po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
2841: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2842: END IF;
2843:
2844: x_result_type := po_validations.c_result_type_success;
2845: FORALL i IN 1 .. p_id_tbl.COUNT
2846: INSERT INTO po_validation_results_gt
2847: (result_set_id,
2848: result_type,
2853: column_val,
2854: token1_name,
2855: token1_value)
2856: SELECT x_result_set_id,
2857: po_validations.c_result_type_failure,
2858: c_entity_type_line,
2859: p_id_tbl(i),
2860: 'PO_ATO_ITEM_NA',
2861: 'ITEM_ID',
2872: AND msi.organization_id = p_inventory_org_id
2873: AND msi.bom_item_type IN(1, 2));
2874:
2875: IF (SQL%ROWCOUNT > 0) THEN
2876: x_result_type := po_validations.c_result_type_failure;
2877: END IF;
2878:
2879: IF po_log.d_proc THEN
2880: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2876: x_result_type := po_validations.c_result_type_failure;
2877: END IF;
2878:
2879: IF po_log.d_proc THEN
2880: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2881: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2882: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2883: END IF;
2884: EXCEPTION
2911: po_log.proc_begin(d_mod, 'p_quantity_tbl', p_quantity_tbl);
2912: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2913: END IF;
2914:
2915: x_result_type := po_validations.c_result_type_success;
2916:
2917: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2918: IF (p_quantity_tbl(i) IS NOT NULL AND p_quantity_tbl(i) = 0) THEN
2919: x_results.add_result(p_entity_type => c_entity_type_line,
2920: p_entity_id => p_id_tbl(i),
2921: p_column_name => 'QUANTITY',
2922: p_column_val => p_quantity_tbl(i),
2923: p_message_name => 'PO_PDOI_ZERO_QTY');
2924: x_result_type := po_validations.c_result_type_failure;
2925: ELSIF (p_quantity_tbl(i) IS NULL AND
2926: p_order_type_lookup_code_tbl(i) IN ('QUANTITY', 'AMOUNT')) THEN
2927: x_results.add_result(p_entity_type => c_entity_type_line,
2928: p_entity_id => p_id_tbl(i),
2928: p_entity_id => p_id_tbl(i),
2929: p_column_name => 'QUANTITY',
2930: p_column_val => p_quantity_tbl(i),
2931: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL');
2932: x_result_type := po_validations.c_result_type_failure;
2933: END IF;
2934: END LOOP;
2935:
2936: IF po_log.d_proc THEN
2970: po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2971: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2972: END IF;
2973:
2974: x_result_type := po_validations.c_result_type_success;
2975:
2976: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2977: IF (p_order_type_lookup_code_tbl(i) IN('FIXED PRICE', 'RATE') AND p_amount_tbl(i) IS NULL) THEN
2978: x_results.add_result(p_entity_type => c_entity_type_line,
2979: p_entity_id => p_id_tbl(i),
2980: p_column_name => 'AMOUNT',
2981: p_column_val => p_amount_tbl(i),
2982: p_message_name => 'PO_PDOI_SVC_MUST_AMT');
2983: x_result_type := po_validations.c_result_type_failure;
2984: ELSIF (p_order_type_lookup_code_tbl(i) IN ('FIXED PRICE', 'RATE')
2985: AND p_amount_tbl(i) IS NOT NULL
2986: AND p_amount_tbl(i) <= 0) THEN
2987: x_results.add_result(p_entity_type => c_entity_type_line,
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;
3001:
3028: IS
3029: d_mod CONSTANT VARCHAR2(100) := d_price_break_lookup_code;
3030: BEGIN
3031: IF x_result_set_id IS NULL THEN
3032: x_result_set_id := po_validations.next_result_set_id();
3033: END IF;
3034:
3035: IF (x_results IS NULL) THEN
3036: x_results := po_validation_results_type.new_instance();
3044: END IF;
3045:
3046: -- If price break lookup code is provided, it has to be a valid lookup
3047: -- code
3048: x_result_type := po_validations.c_result_type_success;
3049: FORALL i IN 1 .. p_id_tbl.COUNT
3050: INSERT INTO po_validation_results_gt
3051: (result_set_id,
3052: result_type,
3057: column_val,
3058: token1_name,
3059: token1_value)
3060: SELECT x_result_set_id,
3061: po_validations.c_result_type_failure,
3062: c_entity_type_line,
3063: p_id_tbl(i),
3064: 'PO_PDOI_INVALID_PRICE_BREAK',
3065: 'PRICE_BREAK_LOOKUP_CODE',
3073: FROM po_lookup_codes PLC
3074: WHERE PLC.lookup_type = 'PRICE BREAK TYPE');
3075:
3076: IF (SQL%ROWCOUNT > 0) THEN
3077: x_result_type := po_validations.c_result_type_failure;
3078: END IF;
3079:
3080: -- Cumulative Pricing is not allowed for GA
3081: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3088: p_column_val => p_price_break_lookup_code_tbl(i),
3089: p_message_name => 'PO_PDOI_GA_PRICE_BREAK_NA'
3090: );
3091:
3092: x_result_type := po_validations.c_result_type_failure;
3093: ELSIF ( p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' AND
3094: p_purchase_basis_tbl(i) = 'SERVICES') THEN
3095:
3096: x_results.add_result(p_entity_type => c_entity_type_line,
3099: p_column_val => p_price_break_lookup_code_tbl(i),
3100: p_message_name => 'PO_PDOI_SVC_NO_CUMULATIVE_PB'
3101: );
3102:
3103: x_result_type := po_validations.c_result_type_failure;
3104: END IF;
3105: END IF;
3106: END LOOP;
3107:
3105: END IF;
3106: END LOOP;
3107:
3108: IF po_log.d_proc THEN
3109: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3110: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3111: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3112: END IF;
3113: EXCEPTION
3146: po_log.proc_begin(d_mod, 'p_unit_price_tbl', p_unit_price_tbl);
3147: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3148: END IF;
3149:
3150: x_result_type := po_validations.c_result_type_success;
3151:
3152: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3153: IF (p_allow_price_override_tbl(i) = 'N' AND p_not_to_exceed_price_tbl(i) IS NOT NULL) THEN
3154: x_results.add_result(p_entity_type => c_entity_type_line,
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
3164: -- unit_price
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:
3179: IF po_log.d_proc THEN
3201: IS
3202: d_mod CONSTANT VARCHAR2(100) := d_ip_category_id_update;
3203: BEGIN
3204: IF x_result_set_id IS NULL THEN
3205: x_result_set_id := po_validations.next_result_set_id();
3206: END IF;
3207:
3208: IF (x_results IS NULL) THEN
3209: x_results := po_validation_results_type.new_instance();
3215: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3216: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3217: END IF;
3218:
3219: x_result_type := po_validations.c_result_type_success;
3220:
3221: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3222: IF p_ip_category_id_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_NUM THEN
3223: x_results.add_result(p_entity_type => c_entity_type_line,
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:
3235: -- Validate if x_category_id is valid if not empty
3245: token1_name,
3246: token1_value,
3247: validation_id)
3248: SELECT x_result_set_id,
3249: po_validations.c_result_type_failure,
3250: c_entity_type_line,
3251: p_id_tbl(i),
3252: 'PO_PDOI_INVALID_IP_CATEGORY_ID',
3253: 'IP_CATEGORY_ID',
3263: FROM icx_cat_categories_v
3264: WHERE rt_category_id = p_ip_category_id_tbl(i));
3265:
3266: IF (SQL%ROWCOUNT > 0) THEN
3267: x_result_type := po_validations.c_result_type_failure;
3268: END IF;
3269:
3270: IF po_log.d_proc THEN
3271: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3267: x_result_type := po_validations.c_result_type_failure;
3268: END IF;
3269:
3270: IF po_log.d_proc THEN
3271: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3272: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3273: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3274: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3275: END IF;
3296: IS
3297: d_mod CONSTANT VARCHAR2(100) := d_uom_update;
3298: BEGIN
3299: IF x_result_set_id IS NULL THEN
3300: x_result_set_id := po_validations.next_result_set_id();
3301: END IF;
3302:
3303: IF po_log.d_proc THEN
3304: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3308: po_log.proc_begin(d_mod, 'p_po_line_id_tbl', p_po_line_id_tbl);
3309: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3310: END IF;
3311:
3312: x_result_type := po_validations.c_result_type_success;
3313: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3314: IF p_unit_meas_lookup_code_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_VARCHAR THEN
3315: x_results.add_result(p_entity_type => c_entity_type_line,
3316: p_entity_id => p_id_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:
3327: --- Bug#13936604: Changing line UOM on BPA using the upload program, the error occurs
3339: token1_name,
3340: token1_value,
3341: validation_id)
3342: SELECT x_result_set_id,
3343: po_validations.c_result_type_failure,
3344: c_entity_type_line,
3345: p_id_tbl(i),
3346: 'PO_PDOI_INVALID_UOM_CODE',
3347: 'UNIT_MEAS_LOOKUP_CODE',
3370: p_unit_meas_lookup_code_tbl(i) NOT IN --- Bug#13936604
3371: (select unit_of_measure from po_units_of_measure_val_v));
3372:
3373: IF (SQL%ROWCOUNT > 0) THEN
3374: x_result_type := po_validations.c_result_type_failure;
3375: END IF;
3376:
3377: IF po_log.d_proc THEN
3378: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3374: x_result_type := po_validations.c_result_type_failure;
3375: END IF;
3376:
3377: IF po_log.d_proc THEN
3378: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3379: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3380: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3381: END IF;
3382: EXCEPTION
3405: IS
3406: d_mod CONSTANT VARCHAR2(100) := d_item_desc_update;
3407: BEGIN
3408: IF x_result_set_id IS NULL THEN
3409: x_result_set_id := po_validations.next_result_set_id();
3410: END IF;
3411:
3412: IF po_log.d_proc THEN
3413: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3418: po_log.proc_begin(d_mod, 'p_po_line_id_tbl', p_po_line_id_tbl);
3419: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3420: END IF;
3421:
3422: x_result_type := po_validations.c_result_type_success;
3423: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3424: IF p_item_description_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_VARCHAR THEN
3425: x_results.add_result(p_entity_type => c_entity_type_line,
3426: p_entity_id => p_id_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:
3437: /* Bug 5366732 Modified the inner query to select item description from mtl_system_items_tl instead of from mtl_system_items */
3449: token1_name,
3450: token1_value,
3451: validation_id)
3452: SELECT x_result_set_id,
3453: po_validations.c_result_type_failure,
3454: c_entity_type_line,
3455: p_id_tbl(i),
3456: 'PO_PDOI_DIFF_ITEM_DESC',
3457: 'ITEM_DESCRIPTION',
3491: mtl.language = USERENV('LANG') AND
3492: p_item_description_tbl(i) <> mtl.description);
3493:
3494: IF (SQL%ROWCOUNT > 0) THEN
3495: x_result_type := po_validations.c_result_type_failure;
3496: END IF;
3497:
3498: IF po_log.d_proc THEN
3499: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3495: x_result_type := po_validations.c_result_type_failure;
3496: END IF;
3497:
3498: IF po_log.d_proc THEN
3499: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3500: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3501: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3502: END IF;
3503: EXCEPTION
3526: po_log.proc_begin(d_mod, 'p_negotiated_by_preparer_tbl', p_negotiated_by_preparer_tbl);
3527: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3528: END IF;
3529:
3530: x_result_type := po_validations.c_result_type_success;
3531: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3532: IF p_negotiated_by_preparer_tbl(i) NOT IN ('Y', 'N') THEN
3533: x_results.add_result(p_entity_type => c_entity_type_line,
3534: p_entity_id => p_id_tbl(i),
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:
3547: IF po_log.d_proc THEN
3575: po_log.proc_begin(d_mod, 'p_negotiated_by_preparer_tbl', p_negotiated_by_preparer_tbl);
3576: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3577: END IF;
3578:
3579: x_result_type := po_validations.c_result_type_success;
3580: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3581: IF p_negotiated_by_preparer_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_VARCHAR THEN
3582: x_results.add_result(p_entity_type => c_entity_type_line,
3583: p_entity_id => p_id_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),
3594: p_column_name => 'NEGOTIATED_BY_PREPARER',
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:
3606: IF po_log.d_proc THEN
3635: IS
3636: d_mod CONSTANT VARCHAR2(100) := d_category_id_update;
3637: BEGIN
3638: IF x_result_set_id IS NULL THEN
3639: x_result_set_id := po_validations.next_result_set_id();
3640: END IF;
3641:
3642: IF po_log.d_proc THEN
3643: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3650: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3651: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3652: END IF;
3653:
3654: x_result_type := po_validations.c_result_type_success;
3655: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3656: IF p_category_id_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_NUM THEN
3657: x_results.add_result(p_entity_type => c_entity_type_line,
3658: p_entity_id => p_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:
3669: FORALL i IN 1 .. p_id_tbl.COUNT
3676: column_name,
3677: column_val,
3678: validation_id)
3679: SELECT x_result_set_id,
3680: po_validations.c_result_type_failure,
3681: c_entity_type_line,
3682: p_id_tbl(i),
3683: 'PO_PDOI_NO_PO_CAT_UPDATE',
3684: 'CATEGORY_ID',
3696: WHERE p_po_line_id_tbl(i) = pld.po_line_id
3697: AND p_category_id_tbl(i) <> pld.category_id));
3698:
3699: IF (SQL%ROWCOUNT > 0) THEN
3700: x_result_type := po_validations.c_result_type_failure;
3701: END IF;
3702:
3703: PO_VAL_LINES2.category_id(p_id_tbl => p_id_tbl,
3704: p_category_id_tbl => p_category_id_tbl,
3709: x_results => x_results,
3710: x_result_type => x_result_type);
3711:
3712: IF po_log.d_proc THEN
3713: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3714: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3715: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3716: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3717: END IF;
3744: x_results := po_validation_results_type.new_instance();
3745: END IF;
3746:
3747: IF x_result_set_id IS NULL THEN
3748: x_result_set_id := po_validations.next_result_set_id();
3749: END IF;
3750:
3751: IF po_log.d_proc THEN
3752: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3753: po_log.proc_begin(d_mod, 'p_unit_price_tbl', p_unit_price_tbl);
3754: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3755: END IF;
3756:
3757: x_result_type := po_validations.c_result_type_success;
3758:
3759: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3760:
3761: IF p_unit_price_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_NUM THEN
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),
3774: p_column_name => 'UNIT_PRICE',
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;
3786:
3796: column_name,
3797: column_val,
3798: validation_id)
3799: SELECT x_result_set_id,
3800: po_validations.c_result_type_failure,
3801: c_entity_type_line,
3802: p_id_tbl(i),
3803: 'PO_PDOI_SVC_NO_PRICE',
3804: 'UNIT_PRICE',
3810: AND p_unit_price_tbl(i) IS NOT NULL
3811: -- missin draft id
3812: UNION
3813: SELECT x_result_set_id,
3814: po_validations.c_result_type_failure,
3815: c_entity_type_line,
3816: p_id_tbl(i),
3817: 'PO_PDOI_SVC_NO_PRICE',
3818: 'UNIT_PRICE',
3824: AND POL.order_type_lookup_code = 'FIXED PRICE'
3825: AND p_unit_price_tbl(i) IS NOT NULL;
3826:
3827: IF (SQL%ROWCOUNT > 0) THEN
3828: x_result_type := po_validations.c_result_type_failure;
3829: END IF;
3830:
3831: -- bug5008026
3832: -- Make sure that the new price does not exceed price limit
3844: column_name,
3845: column_val,
3846: validation_id)
3847: SELECT x_result_set_id,
3848: po_validations.c_result_type_failure,
3849: c_entity_type_line,
3850: p_id_tbl(i),
3851: 'PO_PDOI_INVALID_PRICE',
3852: 'VALUE',
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:
3863: IF (SQL%ROWCOUNT > 0) THEN
3864: x_result_type := po_validations.c_result_type_failure;
3865: END IF;
3866:
3867: -- Enhanced Pricing Start
3868: -- For line with price adjustments, the unit_price should not changed
3876: column_name,
3877: column_val,
3878: validation_id)
3879: SELECT x_result_set_id,
3880: po_validations.c_result_type_failure,
3881: c_entity_type_line,
3882: p_id_tbl(i),
3883: 'PO_PDOI_ADJ_PRICE_UPDATE_NA',
3884: 'UNIT_PRICE',
3891: FROM PO_PRICE_ADJUSTMENTS ADJ
3892: WHERE ADJ.po_line_id = p_po_line_id_tbl(i));
3893:
3894: IF (SQL%ROWCOUNT > 0) THEN
3895: x_result_type := po_validations.c_result_type_failure;
3896: END IF;
3897: -- Enhanced Pricing End
3898:
3899: IF po_log.d_proc THEN
3930: x_results := po_validation_results_type.new_instance();
3931: END IF;
3932:
3933: IF x_result_set_id IS NULL THEN
3934: x_result_set_id := po_validations.next_result_set_id();
3935: END IF;
3936:
3937: IF po_log.d_proc THEN
3938: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3938: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3939: po_log.proc_begin(d_mod, 'p_amount_tbl', p_amount_tbl);
3940: END IF;
3941:
3942: x_result_type := po_validations.c_result_type_success;
3943:
3944: -- bug5258790
3945: -- For fixed price line update, unit price has to be NULL
3946: FORALL i IN 1..p_id_tbl.COUNT
3953: column_name,
3954: column_val,
3955: validation_id)
3956: SELECT x_result_set_id,
3957: po_validations.c_result_type_failure,
3958: c_entity_type_line,
3959: p_id_tbl(i),
3960: 'PO_PDOI_SVC_BLKT_NO_AMT',
3961: 'AMOUNT',
3967: AND p_amount_tbl(i) IS NOT NULL
3968: -- missin draft id
3969: UNION
3970: SELECT x_result_set_id,
3971: po_validations.c_result_type_failure,
3972: c_entity_type_line,
3973: p_id_tbl(i),
3974: 'PO_PDOI_SVC_BLKT_NO_AMT',
3975: 'AMOUNT',
3982: AND p_amount_tbl(i) IS NOT NULL;
3983:
3984:
3985: IF (SQL%ROWCOUNT > 0) THEN
3986: x_result_type := po_validations.c_result_type_failure;
3987: END IF;
3988:
3989: IF po_log.d_proc THEN
3990: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
4103: IF (x_results IS NULL) THEN
4104: x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4105: END IF;
4106: l_results_count := x_results.result_type.COUNT;
4107: x_result_type := po_validations.c_result_type_success;
4108: BEGIN
4109: SELECT mdsv.structure_id
4110: INTO x_structure_id
4111: FROM mtl_default_sets_view mdsv
4177: END IF;
4178: END LOOP;
4179:
4180: IF (l_results_count < x_results.result_type.COUNT) THEN
4181: x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4182: ELSE
4183: x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4184: END IF;
4185:
4179:
4180: IF (l_results_count < x_results.result_type.COUNT) THEN
4181: x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4182: ELSE
4183: x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4184: END IF;
4185:
4186: IF po_log.d_proc THEN
4187: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
4296: IF (x_results IS NULL) THEN
4297: x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
4298: END IF;
4299: l_results_count := x_results.result_type.COUNT;
4300: x_result_type := po_validations.c_result_type_success;
4301: BEGIN
4302: SELECT ffs.id_flex_num
4303: INTO x_structure_id
4304: FROM fnd_id_flex_structures ffs
4380: END IF;
4381: END LOOP;
4382:
4383: IF (l_results_count < x_results.result_type.COUNT) THEN
4384: x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4385: ELSE
4386: x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4387: END IF;
4388:
4382:
4383: IF (l_results_count < x_results.result_type.COUNT) THEN
4384: x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
4385: ELSE
4386: x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
4387: END IF;
4388:
4389: IF po_log.d_proc THEN
4390: po_log.proc_end(d_mod, 'x_result_type', x_result_type);