[Home] [Help]
1: PACKAGE BODY PO_VAL_LINES2 AS
2: -- $Header: PO_VAL_LINES2.plb 120.29.12000000.2 2007/07/18 12:24:19 puppulur 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.
72: IS
73: d_mod CONSTANT VARCHAR2(100) := d_over_tolerance_error_flag;
74: BEGIN
75: IF x_result_set_id IS NULL THEN
76: x_result_set_id := po_validations.next_result_set_id();
77: END IF;
78:
79: IF po_log.d_proc THEN
80: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
94: token1_name,
95: token1_value,
96: validation_id)
97: SELECT x_result_set_id,
98: po_validations.c_result_type_failure,
99: c_entity_type_line,
100: p_id_tbl(i),
101: 'PO_PDOI_INVALID_OVER_TOL_ERROR',
102: 'OVER_TOLERANCE_ERROR_FLAG',
113: AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1)
114: AND plc.lookup_code = p_over_tolerance_err_flag_tbl(i));
115:
116: IF (SQL%ROWCOUNT > 0) THEN
117: x_result_type := po_validations.c_result_type_failure;
118: ELSE
119: x_result_type := po_validations.c_result_type_success;
120: END IF;
121:
115:
116: IF (SQL%ROWCOUNT > 0) THEN
117: x_result_type := po_validations.c_result_type_failure;
118: ELSE
119: x_result_type := po_validations.c_result_type_success;
120: END IF;
121:
122: IF po_log.d_proc THEN
123: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
119: x_result_type := po_validations.c_result_type_success;
120: END IF;
121:
122: IF po_log.d_proc THEN
123: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
124: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
125: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
126: END IF;
127: EXCEPTION
150: IF (x_results IS NULL) THEN
151: x_results := po_validation_results_type.new_instance();
152: END IF;
153:
154: x_result_type := po_validations.c_result_type_success;
155:
156: IF po_log.d_proc THEN
157: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
158: po_log.proc_begin(d_mod, 'p_expiration_date_tbl', p_expiration_date_tbl);
170: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
171: p_token1_name => 'COLUMN_NAME',
172: p_token1_value => 'EXPIRATION_DATE',
173: p_validation_id => PO_VAL_CONSTANTS.c_expiration_date_blk_not_null);
174: x_result_type := po_validations.c_result_type_failure;
175: ELSIF p_expiration_date_tbl(i) IS NOT NULL
176: AND (p_header_start_date_tbl(i) > p_expiration_date_tbl(i)
177: OR p_header_end_date_tbl(i) < p_expiration_date_tbl(i)) THEN
178: x_results.add_result(p_entity_type => c_entity_type_line,
180: p_column_name => 'EXPIRATION_DATE',
181: p_column_val => p_expiration_date_tbl(i),
182: p_message_name => 'POX_EXPIRATION_DATES',
183: p_validation_id => PO_VAL_CONSTANTS.c_expiration_date_blk_exc_hdr);
184: x_result_type := po_validations.c_result_type_failure;
185: END IF;
186: END LOOP;
187:
188: IF po_log.d_proc THEN
214: IS
215: d_mod CONSTANT VARCHAR2(100) := d_global_agreement_flag;
216: BEGIN
217: IF x_result_set_id IS NULL THEN
218: x_result_set_id := po_validations.next_result_set_id();
219: END IF;
220:
221: IF (x_results IS NULL) THEN
222: x_results := po_validation_results_type.new_instance();
230: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
231: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
232: END IF;
233:
234: x_result_type := po_validations.c_result_type_success;
235:
236: FOR i IN 1 .. p_id_tbl.COUNT LOOP
237: IF NVL(p_global_agreement_flag_tbl(i), 'N') = 'N'
238: AND p_purchase_basis_tbl(i) = 'TEMP LABOR' THEN
241: p_column_name => 'GLOBAL_AGREEMENT_FLAG',
242: p_column_val => p_global_agreement_flag_tbl(i),
243: p_message_name => 'PO_PDOI_SVC_NO_LOCAL_BLANKET',
244: p_validation_id => PO_VAL_CONSTANTS.c_ga_flag_temp_labor);
245: x_result_type := po_validations.c_result_type_failure;
246: END IF;
247: END LOOP;
248:
249: FORALL i IN 1 .. p_id_tbl.COUNT
254: entity_id,
255: message_name,
256: validation_id)
257: SELECT x_result_set_id,
258: po_validations.c_result_type_failure,
259: c_entity_type_line,
260: p_id_tbl(i),
261: 'PO_PDOI_GA_OSP_NA',
262: PO_VAL_CONSTANTS.c_ga_flag_op
269: AND NVL(p_global_agreement_flag_tbl(i), 'N') = 'Y'
270: AND NVL(plt.outside_operation_flag, 'N') = 'Y');
271:
272: IF (SQL%ROWCOUNT > 0) THEN
273: x_result_type := po_validations.c_result_type_failure;
274: END IF;
275:
276: IF po_log.d_proc THEN
277: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
273: x_result_type := po_validations.c_result_type_failure;
274: END IF;
275:
276: IF po_log.d_proc THEN
277: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
278: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
279: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
280: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
281: END IF;
312: po_log.proc_begin(d_mod, 'p_amount_tbl', p_amount_tbl);
313: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
314: END IF;
315:
316: x_result_type := po_validations.c_result_type_success;
317:
318: -- If order_type_lookup_code is 'RATE', amount has to be null
319: FOR i IN 1 .. p_id_tbl.COUNT LOOP
320: IF p_order_type_lookup_code_tbl(i) = 'RATE' AND p_amount_tbl(i) IS NOT NULL THEN
323: p_column_name => 'AMOUNT',
324: p_column_val => p_amount_tbl(i),
325: p_message_name => 'PO_PDOI_SVC_BLKT_NO_AMT',
326: p_validation_id => PO_VAL_CONSTANTS.c_amount_blanket);
327: x_result_type := po_validations.c_result_type_failure;
328: ELSIF (p_order_type_lookup_code_tbl(i) = 'FIXED PRICE'
329: AND p_amount_tbl(i) IS NOT NULL
330: AND p_amount_tbl(i) < 0) THEN
331: x_results.add_result(p_entity_type => c_entity_type_line,
337: p_token1_value => 'AMOUNT',
338: p_token2_name => 'VALUE',
339: p_token2_value => p_amount_tbl(i),
340: p_validation_id => PO_VAL_CONSTANTS.c_amount_ge_zero);
341: x_result_type := po_validations.c_result_type_failure;
342: END IF;
343: END LOOP;
344:
345: IF po_log.d_proc THEN
376: po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
377: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
378: END IF;
379:
380: x_result_type := po_validations.c_result_type_success;
381:
382: -- If services procurement not enabled, order_type_lookup_code
383: -- cannot be 'FIXED PRICE' or 'RATE'
384: IF (po_setup_s1.get_services_enabled_flag = 'N') THEN
390: p_column_name => 'ORDER_TYPE_LOOKUP_CODE',
391: p_column_val => p_order_type_lookup_code_tbl(i),
392: p_message_name => 'PO_SVC_NOT_ENABLED',
393: p_validation_id => PO_VAL_CONSTANTS.c_order_type_lookup_code);
394: x_result_type := po_validations.c_result_type_failure;
395: END IF;
396: END LOOP;
397: END IF;
398:
436: po_log.proc_begin(d_mod, 'p_contractor_first_name_tbl', p_contractor_first_name_tbl);
437: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
438: END IF;
439:
440: x_result_type := po_validations.c_result_type_success;
441:
442: FOR i IN 1 .. p_id_tbl.COUNT LOOP
443: IF (p_purchase_basis_tbl(i) <> 'TEMP LABOR'
444: OR p_doc_type <> 'STANDARD')
449: p_column_name => 'CONTRACTOR FIRST/LAST NAME',
450: p_column_val => p_contractor_last_name_tbl(i),
451: p_message_name => 'PO_PDOI_SVC_NO_NAME',
452: p_validation_id => PO_VAL_CONSTANTS.c_contractor_name);
453: x_result_type := po_validations.c_result_type_failure;
454: END IF;
455: END LOOP;
456:
457: IF po_log.d_proc THEN
482: IS
483: d_mod CONSTANT VARCHAR2(100) := d_job_id;
484: BEGIN
485: IF x_result_set_id IS NULL THEN
486: x_result_set_id := po_validations.next_result_set_id();
487: END IF;
488:
489: IF (x_results IS NULL) THEN
490: x_results := po_validation_results_type.new_instance();
498: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
499: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
500: END IF;
501:
502: x_result_type := po_validations.c_result_type_success;
503:
504: FOR i IN 1 .. p_id_tbl.COUNT LOOP
505: IF p_purchase_basis_tbl(i) <> 'TEMP LABOR' AND p_job_id_tbl(i) IS NOT NULL THEN
506: x_results.add_result(p_entity_type => c_entity_type_line,
508: p_column_name => 'JOB_ID',
509: p_column_val => p_job_id_tbl(i),
510: p_message_name => 'PO_PDOI_SVC_NO_JOB',
511: p_validation_id => PO_VAL_CONSTANTS.c_job_id_null );
512: x_result_type := po_validations.c_result_type_failure;
513: ELSIF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_job_id_tbl(i) IS NULL THEN
514: x_results.add_result(p_entity_type => c_entity_type_line,
515: p_entity_id => p_id_tbl(i),
516: p_column_name => 'JOB_ID',
516: p_column_name => 'JOB_ID',
517: p_column_val => p_job_id_tbl(i),
518: p_message_name => 'PO_PDOI_SVC_MUST_JOB',
519: p_validation_id => PO_VAL_CONSTANTS.c_job_id_not_null );
520: x_result_type := po_validations.c_result_type_failure;
521: END IF;
522: END LOOP;
523:
524: --check that job_id is valid within the relevant business group.
538: token2_name,
539: token2_value,
540: validation_id)
541: SELECT x_result_set_id,
542: po_validations.c_result_type_failure,
543: c_entity_type_line,
544: p_id_tbl(i),
545: 'PO_PDOI_SVC_INVALID_JOB',
546: 'JOB_ID',
564: AND NVL(TRUNC(pj.date_to),
565: TRUNC(SYSDATE)));
566:
567: IF (SQL%ROWCOUNT > 0) THEN
568: x_result_type := po_validations.c_result_type_failure;
569: END IF;
570: ELSE
571: -- Cross Business group profile is 'Y'
572: FORALL i IN 1 .. p_id_tbl.COUNT
583: token2_name,
584: token2_value,
585: validation_id)
586: SELECT x_result_set_id,
587: po_validations.c_result_type_failure,
588: c_entity_type_line,
589: p_id_tbl(i),
590: 'PO_PDOI_SVC_INVALID_JOB',
591: 'JOB_ID',
612: TRUNC(SYSDATE))
613: AND NVL(TRUNC(pbg.date_to),
614: TRUNC(SYSDATE)));
615: IF (SQL%ROWCOUNT > 0) THEN
616: x_result_type := po_validations.c_result_type_failure;
617: END IF;
618: END IF;
619:
620: -- job must be valid for the specified category
628: column_name,
629: column_val,
630: validation_id)
631: SELECT x_result_set_id,
632: po_validations.c_result_type_failure,
633: c_entity_type_line,
634: p_id_tbl(i),
635: 'PO_PDOI_SVC_INVALID_JOB_CAT',
636: 'JOB_ID',
649: AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pj.date_from), TRUNC(SYSDATE))
650: AND NVL(TRUNC(pj.date_to), TRUNC(SYSDATE)));
651:
652: IF (SQL%ROWCOUNT > 0) THEN
653: x_result_type := po_validations.c_result_type_failure;
654: END IF;
655:
656: IF po_log.d_proc THEN
657: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
653: x_result_type := po_validations.c_result_type_failure;
654: END IF;
655:
656: IF po_log.d_proc THEN
657: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
658: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
659: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
660: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
661: END IF;
682: IS
683: d_mod CONSTANT VARCHAR2(100) := d_job_business_group_id;
684: BEGIN
685: IF x_result_set_id IS NULL THEN
686: x_result_set_id := po_validations.next_result_set_id();
687: END IF;
688:
689: IF po_log.d_proc THEN
690: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
693: po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
694: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
695: END IF;
696:
697: x_result_type := po_validations.c_result_type_success;
698:
699: IF NVL(hr_general.get_xbg_profile, 'N') = 'N' THEN
700: -- xbg profile is N but job_business_group_id not in FSP
701: FORALL i IN 1 .. p_id_tbl.COUNT
708: column_name,
709: column_val,
710: validation_id)
711: SELECT x_result_set_id,
712: po_validations.c_result_type_failure,
713: c_entity_type_line,
714: p_id_tbl(i),
715: 'PO_PDOI_SVC_CANNOT_CROSS_BG',
716: 'JOB_BUSINESS_GROUP_ID',
723: FROM financials_system_parameters fsp
724: WHERE fsp.business_group_id = p_job_business_group_id_tbl(i));
725:
726: IF (SQL%ROWCOUNT > 0) THEN
727: x_result_type := po_validations.c_result_type_failure;
728: END IF;
729: ELSE
730: -- Cross Business group profile is 'Y', need to validate job business group id
731: FORALL i IN 1 .. p_id_tbl.COUNT
738: column_name,
739: column_val,
740: validation_id)
741: SELECT x_result_set_id,
742: po_validations.c_result_type_failure,
743: c_entity_type_line,
744: p_id_tbl(i),
745: 'PO_PDOI_SVC_INVALID_BG',
746: 'JOB_BUSINESS_GROUP_ID',
756: AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(pbg.date_from), TRUNC(SYSDATE))
757: AND NVL(TRUNC(pbg.date_to), TRUNC(SYSDATE)));
758:
759: IF (SQL%ROWCOUNT > 0) THEN
760: x_result_type := po_validations.c_result_type_failure;
761: END IF;
762: END IF;
763:
764: IF po_log.d_proc THEN
761: END IF;
762: END IF;
763:
764: IF po_log.d_proc THEN
765: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
766: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
767: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
768: END IF;
769: EXCEPTION
797: po_log.proc_begin(d_mod, 'p_capital_expense_flag_tbl', p_capital_expense_flag_tbl);
798: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
799: END IF;
800:
801: x_result_type := po_validations.c_result_type_success;
802:
803: FOR i IN 1 .. p_id_tbl.COUNT LOOP
804: IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_capital_expense_flag_tbl(i) = 'Y' THEN
805: x_results.add_result(p_entity_type => c_entity_type_line,
807: p_column_name => 'CAPITAL_EXPENSE_FLAG',
808: p_column_val => p_capital_expense_flag_tbl(i),
809: p_message_name => 'PO_SVC_NO_CAP_EXPENSE',
810: p_validation_id => PO_VAL_CONSTANTS.c_capital_expense_flag_null);
811: x_result_type := po_validations.c_result_type_failure;
812: END IF;
813: END LOOP;
814:
815: IF po_log.d_proc THEN
838: IS
839: d_mod CONSTANT VARCHAR2(100) := d_un_number_id;
840: BEGIN
841: IF x_result_set_id IS NULL THEN
842: x_result_set_id := po_validations.next_result_set_id();
843: END IF;
844:
845: IF (x_results IS NULL) THEN
846: x_results := po_validation_results_type.new_instance();
853: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
854: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
855: END IF;
856:
857: x_result_type := po_validations.c_result_type_success;
858:
859: FOR i IN 1 .. p_id_tbl.COUNT LOOP
860: IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_un_number_id_tbl(i) IS NOT NULL THEN
861: x_results.add_result(p_entity_type => c_entity_type_line,
863: p_column_name => 'UN_NUMBER',
864: p_column_val => p_un_number_id_tbl(i),
865: p_message_name => 'PO_PDOI_SVC_NO_UNNUMBER',
866: p_validation_id => PO_VAL_CONSTANTS.c_un_number_id_null);
867: x_result_type := po_validations.c_result_type_failure;
868: END IF;
869: END LOOP;
870:
871: FORALL i IN 1 .. p_id_tbl.COUNT
880: token1_name,
881: token1_value,
882: validation_id)
883: SELECT x_result_set_id,
884: po_validations.c_result_type_failure,
885: c_entity_type_line,
886: p_id_tbl(i),
887: 'PO_PDOI_INVALID_UN_NUMBER_ID',
888: 'UN_NUMBER_ID',
897: FROM po_un_numbers_val_v pun
898: WHERE pun.un_number_id = p_un_number_id_tbl(i));
899:
900: IF (SQL%ROWCOUNT > 0) THEN
901: x_result_type := po_validations.c_result_type_failure;
902: END IF;
903:
904: IF po_log.d_proc THEN
905: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
901: x_result_type := po_validations.c_result_type_failure;
902: END IF;
903:
904: IF po_log.d_proc THEN
905: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
906: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
907: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
908: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
909: END IF;
929: IS
930: d_mod CONSTANT VARCHAR2(100) := d_hazard_class_id;
931: BEGIN
932: IF x_result_set_id IS NULL THEN
933: x_result_set_id := po_validations.next_result_set_id();
934: END IF;
935:
936: IF (x_results IS NULL) THEN
937: x_results := po_validation_results_type.new_instance();
944: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
945: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
946: END IF;
947:
948: x_result_type := po_validations.c_result_type_success;
949:
950: FOR i IN 1 .. p_id_tbl.COUNT LOOP
951: IF p_purchase_basis_tbl(i) = 'TEMP LABOR' AND p_hazard_class_id_tbl(i) IS NOT NULL THEN
952: x_results.add_result(p_entity_type => c_entity_type_line,
954: p_column_name => 'HAZARD_CLASS',
955: p_column_val => p_hazard_class_id_tbl(i),
956: p_message_name => 'PO_PDOI_SVC_NO_HAZARD_CLASS',
957: p_validation_id => PO_VAL_CONSTANTS.c_hazard_class_id_null );
958: x_result_type := po_validations.c_result_type_failure;
959: END IF;
960: END LOOP;
961:
962: FORALL i IN 1 .. p_id_tbl.COUNT
971: token1_name,
972: token1_value,
973: validation_id)
974: SELECT x_result_set_id,
975: po_validations.c_result_type_failure,
976: c_entity_type_line,
977: p_id_tbl(i),
978: 'PO_PDOI_INVALID_HAZ_ID',
979: 'HAZARD_CLASS_ID',
988: FROM po_hazard_classes_val_v phc
989: WHERE phc.hazard_class_id = p_hazard_class_id_tbl(i));
990:
991: IF (SQL%ROWCOUNT > 0) THEN
992: x_result_type := po_validations.c_result_type_failure;
993: END IF;
994:
995: IF po_log.d_proc THEN
996: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
992: x_result_type := po_validations.c_result_type_failure;
993: END IF;
994:
995: IF po_log.d_proc THEN
996: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
997: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
998: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
999: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1000: END IF;
1022: IS
1023: d_mod CONSTANT VARCHAR2(100) := d_item_id;
1024: BEGIN
1025: IF x_result_set_id IS NULL THEN
1026: x_result_set_id := po_validations.next_result_set_id();
1027: END IF;
1028:
1029: IF (x_results IS NULL) THEN
1030: x_results := po_validation_results_type.new_instance();
1039: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1040: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1041: END IF;
1042:
1043: x_result_type := po_validations.c_result_type_success;
1044:
1045: FOR i IN 1 .. p_id_tbl.COUNT LOOP
1046: IF (p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' OR p_order_type_lookup_code_tbl(i) = 'RATE')
1047: AND p_item_id_tbl(i) IS NOT NULL THEN
1052: p_message_name => 'PO_PDOI_COLUMN_NULL',
1053: p_token1_name => 'COLUMN_NAME',
1054: p_token1_value => 'ITEM_ID',
1055: p_validation_id => PO_VAL_CONSTANTS.c_item_id_null);
1056: x_result_type := po_validations.c_result_type_failure;
1057: ELSIF p_order_type_lookup_code_tbl(i) = 'AMOUNT' AND p_item_id_tbl(i) IS NOT NULL THEN
1058: x_results.add_result(p_entity_type => c_entity_type_line,
1059: p_entity_id => p_id_tbl(i),
1060: p_column_name => 'ITEM_ID',
1062: p_message_name => 'PO_PDOI_COLUMN_NULL',
1063: p_token1_name => 'COLUMN_NAME',
1064: p_token1_value => 'ITEM_ID',
1065: p_validation_id => PO_VAL_CONSTANTS.c_item_id_null);
1066: x_result_type := po_validations.c_result_type_failure;
1067: END IF;
1068: END LOOP;
1069:
1070: -- If order_type_lookup_code is Quantity and outside_operation flag is 'Y'
1079: column_name,
1080: column_val,
1081: validation_id)
1082: SELECT x_result_set_id,
1083: po_validations.c_result_type_failure,
1084: c_entity_type_line,
1085: p_id_tbl(i),
1086: 'PO_PDOI_ITEM_NOT_NULL',
1087: 'ITEM_ID',
1097: AND p_line_type_id_tbl(i) = plt.line_type_id
1098: AND plt.outside_operation_flag = 'Y');
1099:
1100: IF (SQL%ROWCOUNT > 0) THEN
1101: x_result_type := po_validations.c_result_type_failure;
1102: END IF;
1103:
1104: -- if item id is not null, it has to exist in mtl_system_items table
1105: FORALL i IN 1 .. p_id_tbl.COUNT
1114: token1_name,
1115: token1_value,
1116: validation_id)
1117: SELECT x_result_set_id,
1118: po_validations.c_result_type_failure,
1119: c_entity_type_line,
1120: p_id_tbl(i),
1121: DECODE(plt.outside_operation_flag, 'N', 'PO_PDOI_INVALID_ITEM_ID', 'PO_PDOI_INVALID_OP_ITEM_ID'),
1122: 'ITEM_ID',
1142: AND TRUNC(NVL(msi.start_date_active, SYSDATE)) <= TRUNC(SYSDATE)
1143: AND TRUNC(NVL(msi.end_date_active, SYSDATE)) >= TRUNC(SYSDATE));
1144:
1145: IF (SQL%ROWCOUNT > 0) THEN
1146: x_result_type := po_validations.c_result_type_failure;
1147: END IF;
1148:
1149: IF po_log.d_proc THEN
1150: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1146: x_result_type := po_validations.c_result_type_failure;
1147: END IF;
1148:
1149: IF po_log.d_proc THEN
1150: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1151: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1152: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1153: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1154: END IF;
1178: IS
1179: d_mod CONSTANT VARCHAR2(100) := d_item_description;
1180: BEGIN
1181: IF x_result_set_id IS NULL THEN
1182: x_result_set_id := po_validations.next_result_set_id();
1183: END IF;
1184:
1185: IF po_log.d_proc THEN
1186: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1192: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1193: END IF;
1194:
1195: /* Bug 5366732 Modified the inner query to select item description from mtl_system_items_tl instead of from mtl_system_items */
1196: x_result_type := po_validations.c_result_type_success;
1197: FORALL i IN 1 .. p_id_tbl.COUNT
1198: INSERT INTO po_validation_results_gt
1199: (result_set_id,
1200: result_type,
1206: token1_name,
1207: token1_value,
1208: validation_id)
1209: SELECT x_result_set_id,
1210: po_validations.c_result_type_failure,
1211: c_entity_type_line,
1212: p_id_tbl(i),
1213: DECODE(p_item_description_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_DIFF_ITEM_DESC'),
1214: 'ITEM_DESCRIPTION',
1237:
1238:
1239:
1240: IF (SQL%ROWCOUNT > 0) THEN
1241: x_result_type := po_validations.c_result_type_failure;
1242: END IF;
1243:
1244: IF po_log.d_proc THEN
1245: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1241: x_result_type := po_validations.c_result_type_failure;
1242: END IF;
1243:
1244: IF po_log.d_proc THEN
1245: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1246: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1247: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1248: END IF;
1249: EXCEPTION
1272: d_mod CONSTANT VARCHAR2(100) := d_unit_meas_lookup_code;
1273: l_service_uom_class VARCHAR2(2000);
1274: BEGIN
1275: IF x_result_set_id IS NULL THEN
1276: x_result_set_id := po_validations.next_result_set_id();
1277: END IF;
1278:
1279: IF (x_results IS NULL) THEN
1280: x_results := po_validation_results_type.new_instance();
1290: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1291: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1292: END IF;
1293:
1294: x_result_type := po_validations.c_result_type_success;
1295:
1296: -- If order_type_lookup_code is 'FIXED PRICE', unit_meas_lookup_code has
1297: -- to be null. Otherwise, it cannot be null.
1298: FOR i IN 1 .. p_id_tbl.COUNT LOOP
1302: p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1303: p_column_val => p_unit_meas_lookup_code_tbl(i),
1304: p_message_name => 'PO_PDOI_SVC_NO_UOM',
1305: p_validation_id => PO_VAL_CONSTANTS.c_unit_meas_lookup_null);
1306: x_result_type := po_validations.c_result_type_failure;
1307: ELSIF (p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_unit_meas_lookup_code_tbl(i) IS NULL) THEN
1308: x_results.add_result(p_entity_type => c_entity_type_line,
1309: p_entity_id => p_id_tbl(i),
1310: p_column_name => 'UNIT_MEAS_LOOKUP_CODE',
1312: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1313: p_token1_name => 'COLUMN_NAME',
1314: p_token1_value => 'UNIT_MEAS_LOOKUP_CODE',
1315: p_validation_id => PO_VAL_CONSTANTS.c_unit_meas_lookup_not_null);
1316: x_result_type := po_validations.c_result_type_failure;
1317: END IF;
1318: END LOOP;
1319:
1320: FORALL i IN 1 .. p_id_tbl.COUNT
1333: token2_value,
1334: token3_value,
1335: validation_id)
1336: SELECT x_result_set_id,
1337: po_validations.c_result_type_failure,
1338: c_entity_type_line,
1339: p_id_tbl(i),
1340: 'PO_PDOI_ITEM_RELATED_INFO',
1341: 'UNIT_MEAS_LOOKUP_CODE',
1358: AND miuv.organization_id = p_inventory_org_id
1359: AND miuv.unit_of_measure = p_unit_meas_lookup_code_tbl(i));
1360:
1361: IF (SQL%ROWCOUNT > 0) THEN
1362: x_result_type := po_validations.c_result_type_failure;
1363: END IF;
1364:
1365: -- check to see if x_uom_code is valid in po_units_of_measure_val_v
1366: FORALL i IN 1 .. p_id_tbl.COUNT
1375: token1_name,
1376: token1_value,
1377: validation_id)
1378: SELECT x_result_set_id,
1379: po_validations.c_result_type_failure,
1380: c_entity_type_line,
1381: p_id_tbl(i),
1382: 'PO_PDOI_INVALID_UOM_CODE',
1383: 'UNIT_MEAS_LOOKUP_CODE',
1393: FROM po_units_of_measure_val_v pumv
1394: WHERE pumv.unit_of_measure = p_unit_meas_lookup_code_tbl(i));
1395:
1396: IF (SQL%ROWCOUNT > 0) THEN
1397: x_result_type := po_validations.c_result_type_failure;
1398: END IF;
1399:
1400: -- validation for AMOUNT based line type
1401: FORALL i IN 1 .. p_id_tbl.COUNT
1414: token2_value,
1415: token3_value,
1416: validation_id)
1417: SELECT x_result_set_id,
1418: po_validations.c_result_type_failure,
1419: c_entity_type_line,
1420: p_id_tbl(i),
1421: 'PO_PDOI_INVALID_LINE_TYPE_INFO',
1422: 'UNIT_MEAS_LOOKUP_CODE',
1433: AND p_order_type_lookup_code_tbl(i) = 'AMOUNT'
1434: AND p_unit_meas_lookup_code_tbl(i) <> pltb.unit_of_measure;
1435:
1436: IF (SQL%ROWCOUNT > 0) THEN
1437: x_result_type := po_validations.c_result_type_failure;
1438: END IF;
1439:
1440: -- validation for 'RATE' based line
1441: l_service_uom_class := NVL(FND_PROFILE.value('PO_RATE_UOM_CLASS'), '999');
1453: token1_value,
1454: token2_value,
1455: validation_id)
1456: SELECT x_result_set_id,
1457: po_validations.c_result_type_failure,
1458: c_entity_type_line,
1459: p_id_tbl(i),
1460: 'PO_PDOI_SVC_INVALID_UOM',
1461: 'UNIT_MEAS_LOOKUP_CODE',
1473: AND muomv.unit_of_measure = p_unit_meas_lookup_code_tbl(i)
1474: AND TRUNC(sysdate) < NVL(muomv.disable_date, TRUNC(sysdate) + 1));
1475:
1476: IF (SQL%ROWCOUNT > 0) THEN
1477: x_result_type := po_validations.c_result_type_failure;
1478: END IF;
1479:
1480: IF po_log.d_proc THEN
1481: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1477: x_result_type := po_validations.c_result_type_failure;
1478: END IF;
1479:
1480: IF po_log.d_proc THEN
1481: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1482: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1483: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1484: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1485: END IF;
1507: IS
1508: d_mod CONSTANT VARCHAR2(100) := d_item_revision;
1509: BEGIN
1510: IF x_result_set_id IS NULL THEN
1511: x_result_set_id := po_validations.next_result_set_id();
1512: END IF;
1513:
1514: IF po_log.d_proc THEN
1515: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
1519: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1520: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1521: END IF;
1522:
1523: x_result_type := po_validations.c_result_type_success;
1524:
1525: -- if order_type_lookup_code is FIXED PRICE or RATE, or item id is null,
1526: -- then item revision has to be NULL
1527: FOR i IN 1 .. p_id_tbl.COUNT LOOP
1533: p_column_name => 'ITEM_REVISION',
1534: p_column_val => p_item_revision_tbl(i),
1535: p_message_name => 'PO_PDOI_COLUMN_NULL',
1536: p_validation_id => PO_VAL_CONSTANTS.c_item_revision_null);
1537: x_result_type := po_validations.c_result_type_failure;
1538: END IF;
1539: END LOOP;
1540:
1541: -- check to see if there are x_item_revision exists in mtl_item_revisions
1556: token2_value,
1557: token3_value,
1558: validation_id)
1559: SELECT x_result_set_id,
1560: po_validations.c_result_type_failure,
1561: c_entity_type_line,
1562: p_id_tbl(i),
1563: 'PO_PDOI_ITEM_RELATED_INFO',
1564: 'ITEM_REVISION',
1579: WHERE mir.inventory_item_id = p_item_id_tbl(i)
1580: AND mir.revision = p_item_revision_tbl(i));
1581:
1582: IF (SQL%ROWCOUNT > 0) THEN
1583: x_result_type := po_validations.c_result_type_failure;
1584: END IF;
1585:
1586: IF po_log.d_proc THEN
1587: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1583: x_result_type := po_validations.c_result_type_failure;
1584: END IF;
1585:
1586: IF po_log.d_proc THEN
1587: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1588: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1589: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1590: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1591: END IF;
1618: x_flag mtl_category_sets_v.validate_flag%TYPE;
1619: x_category_set_id mtl_category_sets_v.category_set_id%TYPE;
1620: BEGIN
1621: IF x_result_set_id IS NULL THEN
1622: x_result_set_id := po_validations.next_result_set_id();
1623: END IF;
1624:
1625: IF (x_results IS NULL) THEN
1626: x_results := po_validation_results_type.new_instance();
1637: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1638: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1639: END IF;
1640:
1641: x_result_type := po_validations.c_result_type_success;
1642:
1643: -- Find out the default category_set_id and flag for function_area
1644: -- of PURCHASING".
1645: SELECT validate_flag,
1668: token3_name,
1669: token3_value,
1670: validation_id)
1671: SELECT x_result_set_id,
1672: po_validations.c_result_type_failure,
1673: c_entity_type_line,
1674: p_id_tbl(i),
1675: 'PO_PDOI_ITEM_RELATED_INFO',
1676: 'CATEGORY_ID',
1697: AND SYSDATE < NVL(mcs.disable_date, SYSDATE + 1)
1698: AND mcs.enabled_flag = 'Y');
1699:
1700: IF (SQL%ROWCOUNT > 0) THEN
1701: x_result_type := po_validations.c_result_type_failure;
1702: END IF;
1703:
1704: IF x_flag = 'Y' THEN
1705: FORALL i IN 1 .. p_id_tbl.COUNT
1714: token1_name,
1715: token1_value,
1716: validation_id)
1717: SELECT x_result_set_id,
1718: po_validations.c_result_type_failure,
1719: c_entity_type_line,
1720: p_id_tbl(i),
1721: 'PO_PDOI_INVALID_CATEGORY_ID',
1722: 'CATEGORY_ID',
1739:
1740: -- bug5111418
1741: -- fail the record if this validation fails
1742: IF (SQL%ROWCOUNT > 0) THEN
1743: x_result_type := po_validations.c_result_type_failure;
1744: END IF;
1745: ELSE
1746: FORALL i IN 1 .. p_id_tbl.COUNT
1747: INSERT INTO po_validation_results_gt
1755: token1_name,
1756: token1_value,
1757: validation_id)
1758: SELECT x_result_set_id,
1759: po_validations.c_result_type_failure,
1760: c_entity_type_line,
1761: p_id_tbl(i),
1762: 'PO_PDOI_INVALID_CATEGORY_ID',
1763: 'CATEGORY_ID',
1776: AND SYSDATE < NVL(mcs.disable_date, SYSDATE + 1)
1777: AND mcs.enabled_flag = 'Y');
1778:
1779: IF (SQL%ROWCOUNT > 0) THEN
1780: x_result_type := po_validations.c_result_type_failure;
1781: END IF;
1782: END IF;
1783:
1784: IF po_log.d_proc THEN
1781: END IF;
1782: END IF;
1783:
1784: IF po_log.d_proc THEN
1785: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1786: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1787: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1788: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1789: END IF;
1809: IS
1810: d_mod CONSTANT VARCHAR2(100) := d_ip_category_id;
1811: BEGIN
1812: IF x_result_set_id IS NULL THEN
1813: x_result_set_id := po_validations.next_result_set_id();
1814: END IF;
1815:
1816: IF (x_results IS NULL) THEN
1817: x_results := po_validation_results_type.new_instance();
1823: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1824: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1825: END IF;
1826:
1827: x_result_type := po_validations.c_result_type_success;
1828: -- validate ip_category_id is not null
1829: po_validation_helper.not_null(p_calling_module => c_program_pdoi,
1830: p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(p_ip_category_id_tbl),
1831: p_entity_id_tbl => p_id_tbl,
1853: token1_name,
1854: token1_value,
1855: validation_id)
1856: SELECT x_result_set_id,
1857: po_validations.c_result_type_failure,
1858: c_entity_type_line,
1859: p_id_tbl(i),
1860: 'PO_PDOI_INVALID_IP_CATEGORY_ID',
1861: 'IP_CATEGORY_ID',
1871: FROM icx_cat_categories_v
1872: WHERE rt_category_id = p_ip_category_id_tbl(i));
1873:
1874: IF (SQL%ROWCOUNT > 0) THEN
1875: x_result_type := po_validations.c_result_type_failure;
1876: END IF;
1877:
1878: IF po_log.d_proc THEN
1879: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1875: x_result_type := po_validations.c_result_type_failure;
1876: END IF;
1877:
1878: IF po_log.d_proc THEN
1879: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
1880: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
1881: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
1882: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
1883: END IF;
1908: IS
1909: d_mod CONSTANT VARCHAR2(100) := d_unit_price;
1910: BEGIN
1911: IF x_result_set_id IS NULL THEN
1912: x_result_set_id := po_validations.next_result_set_id();
1913: END IF;
1914:
1915: IF (x_results IS NULL) THEN
1916: x_results := po_validation_results_type.new_instance();
1924: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
1925: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
1926: END IF;
1927:
1928: x_result_type := po_validations.c_result_type_success;
1929: -- If order_type_lookup_code is not 'FIXED PRICE', unit_price cannot be
1930: -- null and cannot be less than zero.
1931: FOR i IN 1 .. p_id_tbl.COUNT LOOP
1932: IF p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_unit_price_tbl(i) IS NULL THEN
1937: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
1938: p_token1_name => 'COLUMN_NAME',
1939: p_token1_value => 'UNIT_PRICE',
1940: p_validation_id => PO_VAL_CONSTANTS.c_unit_price_not_null);
1941: x_result_type := po_validations.c_result_type_failure;
1942: ELSIF p_order_type_lookup_code_tbl(i) <> 'FIXED PRICE' AND p_unit_price_tbl(i) < 0 THEN
1943: x_results.add_result(p_entity_type => c_entity_type_line,
1944: p_entity_id => p_id_tbl(i),
1945: p_column_name => 'UNIT_PRICE',
1949: p_token1_value => 'UNIT_PRICE',
1950: p_token2_name => 'VALUE',
1951: p_token2_value => p_unit_price_tbl(i),
1952: p_validation_id => PO_VAL_CONSTANTS.c_unit_price_ge_zero);
1953: x_result_type := po_validations.c_result_type_failure;
1954: END IF;
1955: END LOOP;
1956:
1957:
1974: token1_value,
1975: token2_value,
1976: validation_id)
1977: SELECT x_result_set_id,
1978: po_validations.c_result_type_failure,
1979: c_entity_type_line,
1980: p_id_tbl(i),
1981: 'PO_PDOI_INVALID_LINE_TYPE_INFO',
1982: 'UNIT_PRICE',
1994: WHERE p_line_type_id_tbl(i) = plt.line_type_id
1995: AND p_unit_price_tbl(i) = plt.unit_price);
1996:
1997: IF (SQL%ROWCOUNT > 0) THEN
1998: x_result_type := po_validations.c_result_type_failure;
1999: END IF;
2000:
2001: -- If order_type_lookup_code is 'FIXED PRICE', unit_price has to be null
2002: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2006: p_column_name => 'UNIT_PRICE',
2007: p_column_val => p_unit_price_tbl(i),
2008: p_message_name => 'PO_PDOI_SVC_NO_PRICE',
2009: p_validation_id => PO_VAL_CONSTANTS.c_unit_price_null);
2010: x_result_type := po_validations.c_result_type_failure;
2011: END IF;
2012: END LOOP;
2013:
2014: IF po_log.d_proc THEN
2011: END IF;
2012: END LOOP;
2013:
2014: IF po_log.d_proc THEN
2015: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2016: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2017: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2018: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
2019: END IF;
2049: po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2050: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2051: END IF;
2052:
2053: x_result_type := po_validations.c_result_type_success;
2054:
2055: -- If order_type_lookup_code is not 'FIXED PRICE' or 'RATE', quantity cannot
2056: -- be less than zero
2057: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2067: p_token1_value => 'QUANTITY',
2068: p_token2_name => 'VALUE',
2069: p_token2_value => p_quantity_tbl(i),
2070: p_validation_id => PO_VAL_CONSTANTS.c_quantity_ge_zero);
2071: x_result_type := po_validations.c_result_type_failure;
2072: -- If order_type_lookup_code is 'FIXED PRICE' or 'RATE', quantity
2073: -- must be null
2074: ELSIF (p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' OR p_order_type_lookup_code_tbl(i) = 'RATE') AND
2075: p_quantity_tbl(i) IS NOT NULL THEN
2078: p_column_name => 'QUANTITY',
2079: p_column_val => p_quantity_tbl(i),
2080: p_message_name => 'PO_SVC_NO_QTY',
2081: p_validation_id => PO_VAL_CONSTANTS.c_quantity_null);
2082: x_result_type := po_validations.c_result_type_failure;
2083: END IF;
2084: END LOOP;
2085:
2086: IF po_log.d_proc THEN
2118: po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2119: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2120: END IF;
2121:
2122: x_result_type := po_validations.c_result_type_success;
2123:
2124: -- If order_type_lookup_code is not 'FIXED PRICE' or 'RATE', amount has to
2125: -- be null
2126: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2131: p_column_name => 'AMOUNT',
2132: p_column_val => p_amount_tbl(i),
2133: p_message_name => 'PO_SVC_NO_AMT',
2134: p_validation_id => PO_VAL_CONSTANTS.c_amount_null);
2135: x_result_type := po_validations.c_result_type_failure;
2136: END IF;
2137: END LOOP;
2138:
2139: IF po_log.d_proc THEN
2171: po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2172: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2173: END IF;
2174:
2175: x_result_type := po_validations.c_result_type_success;
2176:
2177: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2178: IF (p_order_type_lookup_code_tbl(i) = 'RATE' AND
2179: p_rate_type_tbl(i) = 'User') THEN
2182: p_column_name => 'RATE_TYPE',
2183: p_column_val => p_rate_type_tbl(i),
2184: p_message_name => 'PO_PDOI_SVC_RATE_TYPE_NO_USR',
2185: p_validation_id => PO_VAL_CONSTANTS.c_rate_type_no_usr);
2186: x_result_type := po_validations.c_result_type_failure;
2187: END IF;
2188: END LOOP;
2189:
2190: IF po_log.d_proc THEN
2220: x_results := po_validation_results_type.new_instance();
2221: END IF;
2222:
2223: IF x_result_set_id IS NULL THEN
2224: x_result_set_id := po_validations.next_result_set_id();
2225: END IF;
2226:
2227: IF po_log.d_proc THEN
2228: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2232: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2233: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2234: END IF;
2235:
2236: x_result_type := po_validations.c_result_type_success;
2237:
2238: -- Line num must be populated and cannot be <= 0.
2239: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2240: IF (p_line_num_tbl(i) IS NULL) THEN
2245: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
2246: p_token1_name => 'COLUMN_NAME',
2247: p_token1_value => 'LINE_NUM',
2248: p_validation_id => PO_VAL_CONSTANTS.c_line_num_not_null);
2249: x_result_type := po_validations.c_result_type_failure;
2250: ELSIF p_line_num_tbl(i) <= 0 THEN
2251: x_results.add_result(p_entity_type => c_entity_type_line,
2252: p_entity_id => p_id_tbl(i),
2253: p_column_name => 'LINE_NUM',
2257: p_token1_value => 'LINE_NUM',
2258: p_token2_name => 'VALUE',
2259: p_token2_value => p_line_num_tbl(i),
2260: p_validation_id => PO_VAL_CONSTANTS.c_line_num_gt_zero);
2261: x_result_type := po_validations.c_result_type_failure;
2262: END IF;
2263: END LOOP;
2264:
2265:
2279: token1_name,
2280: token1_value,
2281: validation_id)
2282: SELECT x_result_set_id,
2283: po_validations.c_result_type_failure,
2284: c_entity_type_line,
2285: p_id_tbl(i),
2286: 'PO_PDOI_LINE_NUM_UNIQUE',
2287: 'LINE_NUM',
2308: AND PLD.line_num = p_line_num_tbl(i)
2309: AND NVL(PLD.delete_flag, 'N') <> 'Y'));
2310:
2311: IF (SQL%ROWCOUNT > 0) THEN
2312: x_result_type := po_validations.c_result_type_failure;
2313: END IF;
2314:
2315: IF po_log.d_proc THEN
2316: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2312: x_result_type := po_validations.c_result_type_failure;
2313: END IF;
2314:
2315: IF po_log.d_proc THEN
2316: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2317: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2318: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2319: END IF;
2320: EXCEPTION
2338: IS
2339: d_mod CONSTANT VARCHAR2(100) := d_po_line_id;
2340: BEGIN
2341: IF x_result_set_id IS NULL THEN
2342: x_result_set_id := po_validations.next_result_set_id();
2343: END IF;
2344:
2345: IF po_log.d_proc THEN
2346: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2348: po_log.proc_begin(d_mod, 'p_po_header_id_tbl', p_po_header_id_tbl);
2349: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2350: END IF;
2351:
2352: x_result_type := po_validations.c_result_type_success;
2353: FORALL i IN 1 .. p_id_tbl.COUNT
2354: INSERT INTO po_validation_results_gt
2355: (result_set_id,
2356: result_type,
2364: token2_name,
2365: token2_value,
2366: validation_id)
2367: SELECT x_result_set_id,
2368: po_validations.c_result_type_failure,
2369: c_entity_type_line,
2370: p_id_tbl(i),
2371: DECODE(p_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_LINE_ID_UNIQUE'),
2372: 'PO_LINE_ID',
2385: WHERE pln.po_header_id = p_po_header_id_tbl(i)
2386: AND pln.po_line_id = p_po_line_id_tbl(i)));
2387:
2388: IF (SQL%ROWCOUNT > 0) THEN
2389: x_result_type := po_validations.c_result_type_failure;
2390: END IF;
2391:
2392: IF po_log.d_proc THEN
2393: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2389: x_result_type := po_validations.c_result_type_failure;
2390: END IF;
2391:
2392: IF po_log.d_proc THEN
2393: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2394: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2395: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2396: END IF;
2397: EXCEPTION
2414: IS
2415: d_mod CONSTANT VARCHAR2(100) := d_line_type_id;
2416: BEGIN
2417: IF x_result_set_id IS NULL THEN
2418: x_result_set_id := po_validations.next_result_set_id();
2419: END IF;
2420:
2421: IF po_log.d_proc THEN
2422: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2423: po_log.proc_begin(d_mod, 'p_line_type_id_tbl', p_line_type_id_tbl);
2424: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2425: END IF;
2426:
2427: x_result_type := po_validations.c_result_type_success;
2428: FORALL i IN 1 .. p_id_tbl.COUNT
2429: INSERT INTO po_validation_results_gt
2430: (result_set_id,
2431: result_type,
2439: token2_name,
2440: token2_value,
2441: validation_id)
2442: SELECT x_result_set_id,
2443: po_validations.c_result_type_failure,
2444: c_entity_type_line,
2445: p_id_tbl(i),
2446: DECODE(p_line_type_id_tbl(i), NULL, 'PO_PDOI_COLUMN_NOT_NULL', 'PO_PDOI_INVALID_LINE_TYPE_ID'),
2447: 'LINE_TYPE_ID',
2458: FROM po_line_types_val_v pltv
2459: WHERE pltv.line_type_id = p_line_type_id_tbl(i));
2460:
2461: IF (SQL%ROWCOUNT > 0) THEN
2462: x_result_type := po_validations.c_result_type_failure;
2463: END IF;
2464:
2465: IF po_log.d_proc THEN
2466: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2462: x_result_type := po_validations.c_result_type_failure;
2463: END IF;
2464:
2465: IF po_log.d_proc THEN
2466: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2467: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2468: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2469: END IF;
2470: EXCEPTION
2489: IS
2490: d_mod CONSTANT VARCHAR2(100) := d_style_related_info;
2491: BEGIN
2492: IF x_result_set_id IS NULL THEN
2493: x_result_set_id := po_validations.next_result_set_id();
2494: END IF;
2495:
2496: IF po_log.d_proc THEN
2497: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2499: po_log.proc_begin(d_mod, 'p_line_type_id_tbl', p_line_type_id_tbl);
2500: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2501: END IF;
2502:
2503: x_result_type := po_validations.c_result_type_success;
2504:
2505: -- validate line_type_id is valid for the given style
2506: FORALL i IN 1 .. p_id_tbl.COUNT
2507: INSERT INTO po_validation_results_gt
2517: token2_name,
2518: token2_value,
2519: validation_id)
2520: SELECT x_result_set_id,
2521: po_validations.c_result_type_failure,
2522: c_entity_type_line,
2523: p_id_tbl(i),
2524: 'PO_PDOI_LINE_TYPE_ID_STYLE',
2525: 'LINE_TYPE_ID',
2540: AND pdv.style_allowed_value = to_char(p_line_type_id_tbl(i))
2541: AND nvl(pdv.enabled_flag, 'N') = 'Y');
2542:
2543: IF (SQL%ROWCOUNT > 0) THEN
2544: x_result_type := po_validations.c_result_type_failure;
2545: END IF;
2546:
2547: -- validate the purchase_basis is valid for the given style
2548: FORALL i IN 1 .. p_id_tbl.COUNT
2559: token2_name,
2560: token2_value,
2561: validation_id)
2562: SELECT x_result_set_id,
2563: po_validations.c_result_type_failure,
2564: c_entity_type_line,
2565: p_id_tbl(i),
2566: 'PO_PDOI_PURCHASE_BASIS_STYLE',
2567: 'PURCHASE_BASIS',
2579: AND pdsv.style_allowed_value = p_purchase_basis_tbl(i)
2580: AND nvl(pdsv.enabled_flag, 'N') = 'Y');
2581:
2582: IF (SQL%ROWCOUNT > 0) THEN
2583: x_result_type := po_validations.c_result_type_failure;
2584: END IF;
2585:
2586: IF po_log.d_proc THEN
2587: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2583: x_result_type := po_validations.c_result_type_failure;
2584: END IF;
2585:
2586: IF po_log.d_proc THEN
2587: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2588: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2589: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2590: END IF;
2591: EXCEPTION
2609: IS
2610: d_mod CONSTANT VARCHAR2(100) := d_price_type_lookup_code;
2611: BEGIN
2612: IF x_result_set_id IS NULL THEN
2613: x_result_set_id := po_validations.next_result_set_id();
2614: END IF;
2615:
2616: IF po_log.d_proc THEN
2617: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2618: po_log.proc_begin(d_mod, 'p_price_type_lookup_code_tbl', p_price_type_lookup_code_tbl);
2619: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2620: END IF;
2621:
2622: x_result_type := po_validations.c_result_type_success;
2623: FORALL i IN 1 .. p_id_tbl.COUNT
2624: INSERT INTO po_validation_results_gt
2625: (result_set_id,
2626: result_type,
2632: token1_name,
2633: token1_value,
2634: validation_id)
2635: SELECT x_result_set_id,
2636: po_validations.c_result_type_failure,
2637: c_entity_type_line,
2638: p_id_tbl(i),
2639: 'PO_PDOI_INVALID_PRICE_TYPE',
2640: 'PRICE_TYPE_LOOKUP_CODE',
2651: AND SYSDATE < NVL(plc.inactive_date, SYSDATE + 1)
2652: AND plc.lookup_code = p_price_type_lookup_code_tbl(i));
2653:
2654: IF (SQL%ROWCOUNT > 0) THEN
2655: x_result_type := po_validations.c_result_type_failure;
2656: END IF;
2657:
2658: IF po_log.d_proc THEN
2659: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2655: x_result_type := po_validations.c_result_type_failure;
2656: END IF;
2657:
2658: IF po_log.d_proc THEN
2659: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2660: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2661: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2662: END IF;
2663: EXCEPTION
2695: po_log.proc_begin(d_mod, 'p_purchase_basis_tbl', p_purchase_basis_tbl);
2696: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2697: END IF;
2698:
2699: x_result_type := po_validations.c_result_type_success;
2700:
2701: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2702: IF (p_purchase_basis_tbl(i) = 'TEMP LABOR') THEN
2703: IF (p_start_date_tbl(i) IS NULL) THEN
2706: p_entity_id => p_id_tbl(i),
2707: p_column_name => 'START_DATE',
2708: p_column_val => p_start_date_tbl(i),
2709: p_message_name => 'PO_PDOI_SVC_MUST_START_DATE');
2710: x_result_type := po_validations.c_result_type_failure;
2711: ELSIF(NVL(p_expiration_date_tbl(i), p_start_date_tbl(i)) < p_start_date_tbl(i)) THEN
2712: -- If expiration date provided, it must be later than the start date
2713: x_results.add_result(p_entity_type => c_entity_type_line,
2714: p_entity_id => p_id_tbl(i),
2714: p_entity_id => p_id_tbl(i),
2715: p_column_name => 'START_DATE',
2716: p_column_val => p_start_date_tbl(i),
2717: p_message_name => 'PO_SVC_NO_START_END_DATE');
2718: x_result_type := po_validations.c_result_type_failure;
2719: END IF;
2720: ELSE
2721: -- purchase basis is not 'TEMP LABOR'
2722: IF (p_start_date_tbl(i) IS NOT NULL) THEN
2725: p_entity_id => p_id_tbl(i),
2726: p_column_name => 'START_DATE',
2727: p_column_val => p_start_date_tbl(i),
2728: p_message_name => 'PO_SVC_NO_START_END_DATE');
2729: x_result_type := po_validations.c_result_type_failure;
2730: END IF;
2731:
2732: IF (p_start_date_tbl(i) IS NOT NULL) THEN
2733: -- expiration date must be null
2735: p_entity_id => p_id_tbl(i),
2736: p_column_name => 'EXPIRATION_DATE',
2737: p_column_val => p_expiration_date_tbl(i),
2738: p_message_name => 'PO_SVC_NO_START_END_DATE');
2739: x_result_type := po_validations.c_result_type_failure;
2740: END IF;
2741: END IF;
2742: END LOOP;
2743:
2768: IS
2769: d_mod CONSTANT VARCHAR2(100) := d_item_id_standard;
2770: BEGIN
2771: IF x_result_set_id IS NULL THEN
2772: x_result_set_id := po_validations.next_result_set_id();
2773: END IF;
2774:
2775: IF po_log.d_proc THEN
2776: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
2779: po_log.proc_begin(d_mod, 'p_inventory_org_id', p_inventory_org_id);
2780: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
2781: END IF;
2782:
2783: x_result_type := po_validations.c_result_type_success;
2784: FORALL i IN 1 .. p_id_tbl.COUNT
2785: INSERT INTO po_validation_results_gt
2786: (result_set_id,
2787: result_type,
2792: column_val,
2793: token1_name,
2794: token1_value)
2795: SELECT x_result_set_id,
2796: po_validations.c_result_type_failure,
2797: c_entity_type_line,
2798: p_id_tbl(i),
2799: 'PO_ATO_ITEM_NA',
2800: 'ITEM_ID',
2811: AND msi.organization_id = p_inventory_org_id
2812: AND msi.bom_item_type IN(1, 2));
2813:
2814: IF (SQL%ROWCOUNT > 0) THEN
2815: x_result_type := po_validations.c_result_type_failure;
2816: END IF;
2817:
2818: IF po_log.d_proc THEN
2819: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2815: x_result_type := po_validations.c_result_type_failure;
2816: END IF;
2817:
2818: IF po_log.d_proc THEN
2819: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
2820: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
2821: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
2822: END IF;
2823: EXCEPTION
2850: po_log.proc_begin(d_mod, 'p_quantity_tbl', p_quantity_tbl);
2851: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2852: END IF;
2853:
2854: x_result_type := po_validations.c_result_type_success;
2855:
2856: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2857: IF (p_quantity_tbl(i) IS NOT NULL AND p_quantity_tbl(i) = 0) THEN
2858: x_results.add_result(p_entity_type => c_entity_type_line,
2859: p_entity_id => p_id_tbl(i),
2860: p_column_name => 'QUANTITY',
2861: p_column_val => p_quantity_tbl(i),
2862: p_message_name => 'PO_PDOI_ZERO_QTY');
2863: x_result_type := po_validations.c_result_type_failure;
2864: ELSIF (p_quantity_tbl(i) IS NULL AND
2865: p_order_type_lookup_code_tbl(i) IN ('QUANTITY', 'AMOUNT')) THEN
2866: x_results.add_result(p_entity_type => c_entity_type_line,
2867: p_entity_id => p_id_tbl(i),
2867: p_entity_id => p_id_tbl(i),
2868: p_column_name => 'QUANTITY',
2869: p_column_val => p_quantity_tbl(i),
2870: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL');
2871: x_result_type := po_validations.c_result_type_failure;
2872: END IF;
2873: END LOOP;
2874:
2875: IF po_log.d_proc THEN
2909: po_log.proc_begin(d_mod, 'p_order_type_lookup_code_tbl', p_order_type_lookup_code_tbl);
2910: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
2911: END IF;
2912:
2913: x_result_type := po_validations.c_result_type_success;
2914:
2915: FOR i IN 1 .. p_id_tbl.COUNT LOOP
2916: IF (p_order_type_lookup_code_tbl(i) IN('FIXED PRICE', 'RATE') AND p_amount_tbl(i) IS NULL) THEN
2917: x_results.add_result(p_entity_type => c_entity_type_line,
2918: p_entity_id => p_id_tbl(i),
2919: p_column_name => 'AMOUNT',
2920: p_column_val => p_amount_tbl(i),
2921: p_message_name => 'PO_PDOI_SVC_MUST_AMT');
2922: x_result_type := po_validations.c_result_type_failure;
2923: ELSIF (p_order_type_lookup_code_tbl(i) IN ('FIXED PRICE', 'RATE')
2924: AND p_amount_tbl(i) IS NOT NULL
2925: AND p_amount_tbl(i) <= 0) THEN
2926: x_results.add_result(p_entity_type => c_entity_type_line,
2932: p_token1_value => 'AMOUNT',
2933: p_token2_name => 'VALUE',
2934: p_token2_value => p_amount_tbl(i),
2935: p_validation_id => PO_VAL_CONSTANTS.c_amount_gt_zero);
2936: x_result_type := po_validations.c_result_type_failure;
2937:
2938: END IF;
2939: END LOOP;
2940:
2967: IS
2968: d_mod CONSTANT VARCHAR2(100) := d_price_break_lookup_code;
2969: BEGIN
2970: IF x_result_set_id IS NULL THEN
2971: x_result_set_id := po_validations.next_result_set_id();
2972: END IF;
2973:
2974: IF (x_results IS NULL) THEN
2975: x_results := po_validation_results_type.new_instance();
2983: END IF;
2984:
2985: -- If price break lookup code is provided, it has to be a valid lookup
2986: -- code
2987: x_result_type := po_validations.c_result_type_success;
2988: FORALL i IN 1 .. p_id_tbl.COUNT
2989: INSERT INTO po_validation_results_gt
2990: (result_set_id,
2991: result_type,
2996: column_val,
2997: token1_name,
2998: token1_value)
2999: SELECT x_result_set_id,
3000: po_validations.c_result_type_failure,
3001: c_entity_type_line,
3002: p_id_tbl(i),
3003: 'PO_PDOI_INVALID_PRICE_BREAK',
3004: 'PRICE_BREAK_LOOKUP_CODE',
3012: FROM po_lookup_codes PLC
3013: WHERE PLC.lookup_type = 'PRICE BREAK TYPE');
3014:
3015: IF (SQL%ROWCOUNT > 0) THEN
3016: x_result_type := po_validations.c_result_type_failure;
3017: END IF;
3018:
3019: -- Cumulative Pricing is not allowed for GA
3020: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3027: p_column_val => p_price_break_lookup_code_tbl(i),
3028: p_message_name => 'PO_PDOI_GA_PRICE_BREAK_NA'
3029: );
3030:
3031: x_result_type := po_validations.c_result_type_failure;
3032: ELSIF ( p_order_type_lookup_code_tbl(i) = 'FIXED PRICE' AND
3033: p_purchase_basis_tbl(i) = 'SERVICES') THEN
3034:
3035: x_results.add_result(p_entity_type => c_entity_type_line,
3038: p_column_val => p_price_break_lookup_code_tbl(i),
3039: p_message_name => 'PO_PDOI_SVC_NO_CUMULATIVE_PB'
3040: );
3041:
3042: x_result_type := po_validations.c_result_type_failure;
3043: END IF;
3044: END IF;
3045: END LOOP;
3046:
3044: END IF;
3045: END LOOP;
3046:
3047: IF po_log.d_proc THEN
3048: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3049: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3050: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3051: END IF;
3052: EXCEPTION
3085: po_log.proc_begin(d_mod, 'p_unit_price_tbl', p_unit_price_tbl);
3086: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3087: END IF;
3088:
3089: x_result_type := po_validations.c_result_type_success;
3090:
3091: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3092: IF (p_allow_price_override_tbl(i) = 'N' AND p_not_to_exceed_price_tbl(i) IS NOT NULL) THEN
3093: x_results.add_result(p_entity_type => c_entity_type_line,
3095: p_column_name => 'NOT_TO_EXCEED_PRICE',
3096: p_column_val => p_not_to_exceed_price_tbl(i),
3097: p_message_name => 'PO_PDOI_EXCEED_PRICE_NULL',
3098: p_validation_id => PO_VAL_CONSTANTS.c_not_to_exceed_price_null);
3099: x_result_type := po_validations.c_result_type_failure;
3100: ELSIF p_not_to_exceed_price_tbl(i) IS NOT NULL
3101: AND p_not_to_exceed_price_tbl(i) < p_unit_price_tbl(i) THEN
3102: -- If not_to_exceed_price is not null, then it cannot be less than
3103: -- unit_price
3110: p_token1_value => p_not_to_exceed_price_tbl(i),
3111: p_token2_name => 'UNIT_PRICE',
3112: p_token2_value => p_unit_price_tbl(i),
3113: p_validation_id => PO_VAL_CONSTANTS.c_not_to_exceed_price_valid);
3114: x_result_type := po_validations.c_result_type_failure;
3115: END IF;
3116: END LOOP;
3117:
3118: IF po_log.d_proc THEN
3140: IS
3141: d_mod CONSTANT VARCHAR2(100) := d_ip_category_id_update;
3142: BEGIN
3143: IF x_result_set_id IS NULL THEN
3144: x_result_set_id := po_validations.next_result_set_id();
3145: END IF;
3146:
3147: IF (x_results IS NULL) THEN
3148: x_results := po_validation_results_type.new_instance();
3154: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3155: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3156: END IF;
3157:
3158: x_result_type := po_validations.c_result_type_success;
3159:
3160: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3161: IF p_ip_category_id_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_NUM THEN
3162: x_results.add_result(p_entity_type => c_entity_type_line,
3166: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3167: p_token1_name => 'COLUMN_NAME',
3168: p_token1_value => 'IP_CATEGORY_ID',
3169: p_validation_id => PO_VAL_CONSTANTS.c_ip_cat_id_update_not_null);
3170: x_result_type := po_validations.c_result_type_failure;
3171: END IF;
3172: END LOOP;
3173:
3174: -- Validate if x_category_id is valid if not empty
3184: token1_name,
3185: token1_value,
3186: validation_id)
3187: SELECT x_result_set_id,
3188: po_validations.c_result_type_failure,
3189: c_entity_type_line,
3190: p_id_tbl(i),
3191: 'PO_PDOI_INVALID_IP_CATEGORY_ID',
3192: 'IP_CATEGORY_ID',
3202: FROM icx_cat_categories_v
3203: WHERE rt_category_id = p_ip_category_id_tbl(i));
3204:
3205: IF (SQL%ROWCOUNT > 0) THEN
3206: x_result_type := po_validations.c_result_type_failure;
3207: END IF;
3208:
3209: IF po_log.d_proc THEN
3210: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3206: x_result_type := po_validations.c_result_type_failure;
3207: END IF;
3208:
3209: IF po_log.d_proc THEN
3210: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3211: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3212: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3213: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3214: END IF;
3235: IS
3236: d_mod CONSTANT VARCHAR2(100) := d_uom_update;
3237: BEGIN
3238: IF x_result_set_id IS NULL THEN
3239: x_result_set_id := po_validations.next_result_set_id();
3240: END IF;
3241:
3242: IF po_log.d_proc THEN
3243: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3247: po_log.proc_begin(d_mod, 'p_po_line_id_tbl', p_po_line_id_tbl);
3248: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3249: END IF;
3250:
3251: x_result_type := po_validations.c_result_type_success;
3252: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3253: IF p_unit_meas_lookup_code_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_VARCHAR THEN
3254: x_results.add_result(p_entity_type => c_entity_type_line,
3255: p_entity_id => p_id_tbl(i),
3258: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3259: p_token1_name => 'COLUMN_NAME',
3260: p_token1_value => 'UNIT_MEAS_LOOKUP_CODE',
3261: p_validation_id => PO_VAL_CONSTANTS.c_uom_update_not_null);
3262: x_result_type := po_validations.c_result_type_failure;
3263: END IF;
3264: END LOOP;
3265:
3266: FORALL i IN 1 .. p_id_tbl.COUNT
3275: token1_name,
3276: token1_value,
3277: validation_id)
3278: SELECT x_result_set_id,
3279: po_validations.c_result_type_failure,
3280: c_entity_type_line,
3281: p_id_tbl(i),
3282: 'PO_PDOI_INVALID_UOM_CODE',
3283: 'UNIT_MEAS_LOOKUP_CODE',
3306: pol.unit_meas_lookup_code IS NULL AND
3307: pumv.unit_of_measure <> p_unit_meas_lookup_code_tbl(i));
3308:
3309: IF (SQL%ROWCOUNT > 0) THEN
3310: x_result_type := po_validations.c_result_type_failure;
3311: END IF;
3312:
3313: IF po_log.d_proc THEN
3314: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3310: x_result_type := po_validations.c_result_type_failure;
3311: END IF;
3312:
3313: IF po_log.d_proc THEN
3314: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3315: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3316: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3317: END IF;
3318: EXCEPTION
3341: IS
3342: d_mod CONSTANT VARCHAR2(100) := d_item_desc_update;
3343: BEGIN
3344: IF x_result_set_id IS NULL THEN
3345: x_result_set_id := po_validations.next_result_set_id();
3346: END IF;
3347:
3348: IF po_log.d_proc THEN
3349: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3354: po_log.proc_begin(d_mod, 'p_po_line_id_tbl', p_po_line_id_tbl);
3355: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3356: END IF;
3357:
3358: x_result_type := po_validations.c_result_type_success;
3359: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3360: IF p_item_description_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_VARCHAR THEN
3361: x_results.add_result(p_entity_type => c_entity_type_line,
3362: p_entity_id => p_id_tbl(i),
3365: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3366: p_token1_name => 'COLUMN_NAME',
3367: p_token1_value => 'ITEM_DESCRIPTION',
3368: p_validation_id => PO_VAL_CONSTANTS.c_item_desc_update_not_null);
3369: x_result_type := po_validations.c_result_type_failure;
3370: END IF;
3371: END LOOP;
3372:
3373: /* Bug 5366732 Modified the inner query to select item description from mtl_system_items_tl instead of from mtl_system_items */
3385: token1_name,
3386: token1_value,
3387: validation_id)
3388: SELECT x_result_set_id,
3389: po_validations.c_result_type_failure,
3390: c_entity_type_line,
3391: p_id_tbl(i),
3392: 'PO_PDOI_DIFF_ITEM_DESC',
3393: 'ITEM_DESCRIPTION',
3427: mtl.language = USERENV('LANG') AND
3428: p_item_description_tbl(i) <> mtl.description);
3429:
3430: IF (SQL%ROWCOUNT > 0) THEN
3431: x_result_type := po_validations.c_result_type_failure;
3432: END IF;
3433:
3434: IF po_log.d_proc THEN
3435: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3431: x_result_type := po_validations.c_result_type_failure;
3432: END IF;
3433:
3434: IF po_log.d_proc THEN
3435: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3436: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3437: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3438: END IF;
3439: EXCEPTION
3462: po_log.proc_begin(d_mod, 'p_negotiated_by_preparer_tbl', p_negotiated_by_preparer_tbl);
3463: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3464: END IF;
3465:
3466: x_result_type := po_validations.c_result_type_success;
3467: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3468: IF p_negotiated_by_preparer_tbl(i) NOT IN ('Y', 'N') THEN
3469: x_results.add_result(p_entity_type => c_entity_type_line,
3470: p_entity_id => p_id_tbl(i),
3475: p_token1_value => 'NEGOTIATED_BY_PREPARER',
3476: p_token2_name => 'VALUE',
3477: p_token2_value => p_negotiated_by_preparer_tbl(i),
3478: p_validation_id => PO_VAL_CONSTANTS.c_negotiated_by_preparer);
3479: x_result_type := po_validations.c_result_type_failure;
3480: END IF;
3481: END LOOP;
3482:
3483: IF po_log.d_proc THEN
3511: po_log.proc_begin(d_mod, 'p_negotiated_by_preparer_tbl', p_negotiated_by_preparer_tbl);
3512: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3513: END IF;
3514:
3515: x_result_type := po_validations.c_result_type_success;
3516: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3517: IF p_negotiated_by_preparer_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_VARCHAR THEN
3518: x_results.add_result(p_entity_type => c_entity_type_line,
3519: p_entity_id => p_id_tbl(i),
3522: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3523: p_token1_name => 'COLUMN_NAME',
3524: p_token1_value => 'NEGOTIATED_BY_PREPARER',
3525: p_validation_id => PO_VAL_CONSTANTS.c_nego_by_prep_update_not_null);
3526: x_result_type := po_validations.c_result_type_failure;
3527: ELSIF p_negotiated_by_preparer_tbl(i) NOT IN (NULL, 'Y', 'N') THEN
3528: x_results.add_result(p_entity_type => c_entity_type_line,
3529: p_entity_id => p_id_tbl(i),
3530: p_column_name => 'NEGOTIATED_BY_PREPARER',
3534: p_token1_value => 'NEGOTIATED_BY_PREPARER',
3535: p_token2_name => 'VALUE',
3536: p_token2_value => p_negotiated_by_preparer_tbl(i),
3537: p_validation_id => PO_VAL_CONSTANTS.c_nego_by_prep_update_valid);
3538: x_result_type := po_validations.c_result_type_failure;
3539: END IF;
3540: END LOOP;
3541:
3542: IF po_log.d_proc THEN
3571: IS
3572: d_mod CONSTANT VARCHAR2(100) := d_category_id_update;
3573: BEGIN
3574: IF x_result_set_id IS NULL THEN
3575: x_result_set_id := po_validations.next_result_set_id();
3576: END IF;
3577:
3578: IF po_log.d_proc THEN
3579: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3586: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3587: po_log.proc_begin(d_mod, 'x_result_set_id', x_result_set_id);
3588: END IF;
3589:
3590: x_result_type := po_validations.c_result_type_success;
3591: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3592: IF p_category_id_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_NUM THEN
3593: x_results.add_result(p_entity_type => c_entity_type_line,
3594: p_entity_id => p_id_tbl(i),
3597: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3598: p_token1_name => 'COLUMN_NAME',
3599: p_token1_value => 'CATEGORY_ID',
3600: p_validation_id => PO_VAL_CONSTANTS.c_cat_id_update_not_null);
3601: x_result_type := po_validations.c_result_type_failure;
3602: END IF;
3603: END LOOP;
3604:
3605: FORALL i IN 1 .. p_id_tbl.COUNT
3612: column_name,
3613: column_val,
3614: validation_id)
3615: SELECT x_result_set_id,
3616: po_validations.c_result_type_failure,
3617: c_entity_type_line,
3618: p_id_tbl(i),
3619: 'PO_PDOI_NO_PO_CAT_UPDATE',
3620: 'CATEGORY_ID',
3632: WHERE p_po_line_id_tbl(i) = pld.po_line_id
3633: AND p_category_id_tbl(i) <> pld.category_id));
3634:
3635: IF (SQL%ROWCOUNT > 0) THEN
3636: x_result_type := po_validations.c_result_type_failure;
3637: END IF;
3638:
3639: PO_VAL_LINES2.category_id(p_id_tbl => p_id_tbl,
3640: p_category_id_tbl => p_category_id_tbl,
3645: x_results => x_results,
3646: x_result_type => x_result_type);
3647:
3648: IF po_log.d_proc THEN
3649: po_validations.log_validation_results_gt(d_mod, 9, x_result_set_id);
3650: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3651: po_log.LOG(po_log.c_proc_end, d_mod, NULL, 'x_results', x_results);
3652: po_log.proc_end(d_mod, 'x_result_set_id', x_result_set_id);
3653: END IF;
3680: x_results := po_validation_results_type.new_instance();
3681: END IF;
3682:
3683: IF x_result_set_id IS NULL THEN
3684: x_result_set_id := po_validations.next_result_set_id();
3685: END IF;
3686:
3687: IF po_log.d_proc THEN
3688: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3689: po_log.proc_begin(d_mod, 'p_unit_price_tbl', p_unit_price_tbl);
3690: po_log.LOG(po_log.c_proc_begin, d_mod, NULL, 'x_results', x_results);
3691: END IF;
3692:
3693: x_result_type := po_validations.c_result_type_success;
3694:
3695: FOR i IN 1 .. p_id_tbl.COUNT LOOP
3696:
3697: IF p_unit_price_tbl(i) = PO_PDOI_CONSTANTS.g_NULLIFY_NUM THEN
3702: p_message_name => 'PO_PDOI_COLUMN_NOT_NULL',
3703: p_token1_name => 'COLUMN_NAME',
3704: p_token1_value => 'UNIT_PRICE',
3705: p_validation_id => PO_VAL_CONSTANTS.c_unit_price_update_not_null);
3706: x_result_type := po_validations.c_result_type_failure;
3707: ELSIF p_unit_price_tbl(i) IS NOT NULL AND p_unit_price_tbl(i) < 0 THEN
3708: x_results.add_result(p_entity_type => c_entity_type_line,
3709: p_entity_id => p_id_tbl(i),
3710: p_column_name => 'UNIT_PRICE',
3714: p_token1_value => 'UNIT_PRICE',
3715: p_token2_name => 'VALUE',
3716: p_token2_value => p_unit_price_tbl(i),
3717: p_validation_id => PO_VAL_CONSTANTS.c_unit_price_update_ge_zero);
3718: x_result_type := po_validations.c_result_type_failure;
3719:
3720: END IF;
3721: END LOOP;
3722:
3732: column_name,
3733: column_val,
3734: validation_id)
3735: SELECT x_result_set_id,
3736: po_validations.c_result_type_failure,
3737: c_entity_type_line,
3738: p_id_tbl(i),
3739: 'PO_PDOI_SVC_NO_PRICE',
3740: 'UNIT_PRICE',
3746: AND p_unit_price_tbl(i) IS NOT NULL
3747: -- missin draft id
3748: UNION
3749: SELECT x_result_set_id,
3750: po_validations.c_result_type_failure,
3751: c_entity_type_line,
3752: p_id_tbl(i),
3753: 'PO_PDOI_SVC_NO_PRICE',
3754: 'UNIT_PRICE',
3760: AND POL.order_type_lookup_code = 'FIXED PRICE'
3761: AND p_unit_price_tbl(i) IS NOT NULL;
3762:
3763: IF (SQL%ROWCOUNT > 0) THEN
3764: x_result_type := po_validations.c_result_type_failure;
3765: END IF;
3766:
3767: -- bug5008026
3768: -- Make sure that the new price does not exceed price limit
3780: column_name,
3781: column_val,
3782: validation_id)
3783: SELECT x_result_set_id,
3784: po_validations.c_result_type_failure,
3785: c_entity_type_line,
3786: p_id_tbl(i),
3787: 'PO_PDOI_INVALID_PRICE',
3788: 'VALUE',
3796: WHERE POL.po_line_id = p_po_line_id_tbl(i)
3797: AND POL.not_to_exceed_price < p_unit_price_tbl(i);
3798:
3799: IF (SQL%ROWCOUNT > 0) THEN
3800: x_result_type := po_validations.c_result_type_failure;
3801: END IF;
3802:
3803: IF po_log.d_proc THEN
3804: po_log.proc_end(d_mod, 'x_result_type', x_result_type);
3834: x_results := po_validation_results_type.new_instance();
3835: END IF;
3836:
3837: IF x_result_set_id IS NULL THEN
3838: x_result_set_id := po_validations.next_result_set_id();
3839: END IF;
3840:
3841: IF po_log.d_proc THEN
3842: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3842: po_log.proc_begin(d_mod, 'p_id_tbl', p_id_tbl);
3843: po_log.proc_begin(d_mod, 'p_amount_tbl', p_amount_tbl);
3844: END IF;
3845:
3846: x_result_type := po_validations.c_result_type_success;
3847:
3848: -- bug5258790
3849: -- For fixed price line update, unit price has to be NULL
3850: FORALL i IN 1..p_id_tbl.COUNT
3857: column_name,
3858: column_val,
3859: validation_id)
3860: SELECT x_result_set_id,
3861: po_validations.c_result_type_failure,
3862: c_entity_type_line,
3863: p_id_tbl(i),
3864: 'PO_PDOI_SVC_BLKT_NO_AMT',
3865: 'AMOUNT',
3871: AND p_amount_tbl(i) IS NOT NULL
3872: -- missin draft id
3873: UNION
3874: SELECT x_result_set_id,
3875: po_validations.c_result_type_failure,
3876: c_entity_type_line,
3877: p_id_tbl(i),
3878: 'PO_PDOI_SVC_BLKT_NO_AMT',
3879: 'AMOUNT',
3886: AND p_amount_tbl(i) IS NOT NULL;
3887:
3888:
3889: IF (SQL%ROWCOUNT > 0) THEN
3890: x_result_type := po_validations.c_result_type_failure;
3891: END IF;
3892:
3893: IF po_log.d_proc THEN
3894: po_log.proc_end(d_mod, 'x_result_type', x_result_type);