[Home] [Help]
34:
35: -- Check if any errors were present
36: SELECT decode(count(interface_type), 0, 'N', 'Y')
37: INTO l_has_errors
38: FROM pon_interface_errors
39: WHERE batch_id = p_batch_id
40: AND rownum = 1;
41:
42: IF (l_has_errors = 'Y') THEN
297: WHERE s_auction_header_id = bp.auction_header_id
298: AND s_auction_line_number = bp.line_number
299: AND s_bid_number = bp.bid_number)
300: THEN
301: INTO PON_INTERFACE_ERRORS
302: ( interface_type
303: , column_name
304: , error_message_name
305: , error_value
341: s_award_quantity > 0 OR
342: s_awardreject_reason is not null)
343: AND s_line_award_status = 'COMPLETED'
344: THEN
345: INTO PON_INTERFACE_ERRORS
346: ( interface_type
347: , column_name
348: , error_message_name
349: , table_name
383: s_award_quantity > 0 OR
384: s_awardreject_reason is not null)
385: AND s_shortlist_flag = 'N'
386: THEN
387: INTO PON_INTERFACE_ERRORS
388: ( interface_type
389: , column_name
390: , error_message_name
391: , error_value
427: s_award_quantity > 0 OR
428: s_awardreject_reason is not null)
429: AND s_bid_status <> 'ACTIVE'
430: THEN
431: INTO PON_INTERFACE_ERRORS
432: ( interface_type
433: , column_name
434: , error_message_name
435: , error_value
471: s_award_quantity > 0 OR
472: s_awardreject_reason is not null)
473: AND s_end_date_active is not null and s_end_date_active <= trunc(sysdate)
474: THEN
475: INTO PON_INTERFACE_ERRORS
476: ( interface_type
477: , column_name
478: , error_message_name
479: , error_value
513: -- Check if award quantity is > 0 when uploading an XML file
514: WHEN p_spreadsheet_type = PON_AWARD_PKG.g_xml_upload_mode
515: AND s_award_quantity < 0
516: THEN
517: INTO PON_INTERFACE_ERRORS
518: ( interface_type
519: , column_name
520: , error_message_name
521: , error_value_number
561: AND s_order_type_lookup_code = 'QUANTITY'
562: AND s_bid_line_award_status = 'Y'
563: AND s_award_quantity is null
564: THEN
565: INTO PON_INTERFACE_ERRORS
566: ( interface_type
567: , column_name
568: , error_message_name
569: , table_name
607: AND s_award_shipment_number = -1
608: AND p_spreadsheet_type = g_txt_upload_mode
609: AND nvl(s_award_quantity , 0) > 0
610: THEN
611: INTO PON_INTERFACE_ERRORS
612: ( interface_type
613: , column_name
614: , error_message_name
615: , table_name
680: AND bh.vendor_id = pv.vendor_id (+);
681:
682:
683: --
684: INSERT INTO PON_INTERFACE_ERRORS
685: ( interface_type
686: , column_name
687: , error_message_name
688: , error_value
789: line_type_id IS NOT NULL AND
790: NOT ((order_type_lookup_code = 'FIXED PRICE' AND purchase_basis = 'SERVICES') OR
791: (order_type_lookup_code = 'QUANTITY' AND purchase_basis = 'GOODS')))
792: OR po_outside_operation_flag = 'Y' THEN
793: INTO pon_interface_errors
794: (
795: interface_type, column_name, table_name, -- 1
796: error_value, error_message_name, batch_id, -- 2
797: interface_line_id, auction_header_id, line_number, -- 3
830:
831: INSERT ALL
832: WHEN S_CLM_INFO_FLAG = 'N' AND
833: retainage_rate_percent IS NOT NULL AND (retainage_rate_percent < 0 OR retainage_rate_percent > 100) THEN
834: INTO pon_interface_errors
835: (
836: interface_type, column_name, table_name, -- 1
837: error_value, error_message_name, batch_id, -- 2
838: interface_line_id, auction_header_id, line_number, -- 3
851: )
852:
853: WHEN S_CLM_INFO_FLAG = 'N' AND
854: max_retainage_amount IS NOT NULL AND max_retainage_amount < 0 THEN
855: INTO pon_interface_errors
856: (
857: interface_type, column_name, table_name, -- 1
858: error_value, error_message_name, batch_id, -- 2
859: interface_line_id, auction_header_id, line_number, -- 3
872: )
873:
874: WHEN S_CLM_INFO_FLAG = 'N' AND
875: advance_amount IS NOT NULL AND advance_amount < 0 THEN
876: INTO pon_interface_errors
877: (
878: interface_type, column_name, table_name, -- 1
879: error_value, error_message_name, batch_id, -- 2
880: interface_line_id, auction_header_id, line_number, -- 3
893: )
894:
895: WHEN S_CLM_INFO_FLAG = 'N' AND
896: progress_pymt_rate_percent IS NOT NULL AND (progress_pymt_rate_percent < 0 OR progress_pymt_rate_percent > 100) then
897: INTO pon_interface_errors
898: (
899: interface_type, column_name, table_name, -- 1
900: error_value, error_message_name, batch_id, -- 2
901: interface_line_id, auction_header_id, line_number, -- 3
913: SYSDATE, l_loginid -- 6
914: )
915: WHEN S_CLM_INFO_FLAG = 'N' AND
916: recoupment_rate_percent IS NOT NULL AND (recoupment_rate_percent < 0 OR recoupment_rate_percent > 100) THEN
917: INTO pon_interface_errors
918: (
919: interface_type, column_name, table_name, -- 1
920: error_value, error_message_name, batch_id, -- 2
921: interface_line_id, auction_header_id, line_number, -- 3
934: )
935:
936: WHEN S_CLM_INFO_FLAG = 'N' AND
937: p_progress_payment_type = 'FINANCE' AND progress_pymt_rate_percent IS NULL THEN
938: INTO pon_interface_errors
939: (
940: interface_type, column_name, table_name, -- 1
941: error_value, error_message_name, batch_id, -- 2
942: interface_line_id, auction_header_id, line_number, -- 3
958: WHEN S_CLM_INFO_FLAG = 'N' AND
959: progress_pymt_rate_percent IS NOT NULL AND
960: recoupment_rate_percent IS NULL AND
961: p_recoupment_negotiable_flag = 'N' THEN
962: INTO pon_interface_errors
963: (
964: interface_type, column_name, table_name, -- 1
965: error_value, error_message_name, batch_id, -- 2
966: interface_line_id, auction_header_id, line_number, -- 3
980:
981: WHEN S_CLM_INFO_FLAG = 'N' AND
982: ((advance_amount IS NOT NULL OR p_advance_negotiable_flag = 'Y') AND
983: (recoupment_rate_percent IS NULL AND p_recoupment_negotiable_flag = 'N')) THEN
984: INTO pon_interface_errors
985: (
986: interface_type, column_name, table_name, -- 1
987: error_value, error_message_name, batch_id, -- 2
988: interface_line_id, auction_header_id, line_number, -- 3
1002:
1003: WHEN S_CLM_INFO_FLAG = 'N' AND
1004: target_price IS NOT NULL AND advance_amount IS NOT NULL
1005: AND (advance_amount > nvl(s_quantity,1) * target_price) THEN
1006: INTO pon_interface_errors
1007: (
1008: interface_type, column_name, table_name, -- 1
1009: error_value, error_message_name, batch_id, -- 2
1010: interface_line_id, auction_header_id, line_number, -- 3
1025: WHEN S_CLM_INFO_FLAG = 'N' AND
1026: p_progress_payment_type <> 'NONE' AND recoupment_rate_percent IS NOT NULL
1027: AND advance_amount IS NOT NULL AND target_price IS NOT NULL
1028: AND (recoupment_rate_percent < (advance_amount * 100)/(nvl(s_quantity,1) * target_price)) THEN
1029: INTO pon_interface_errors
1030: (
1031: interface_type, column_name, table_name, -- 1
1032: error_value, error_message_name, batch_id, -- 2
1033: interface_line_id, auction_header_id, line_number, -- 3
1050: line_type_id IS NOT NULL AND
1051: NOT ((order_type_lookup_code = 'FIXED PRICE' AND purchase_basis = 'SERVICES') OR
1052: (order_type_lookup_code = 'QUANTITY' AND purchase_basis = 'GOODS')))
1053: OR po_outside_operation_flag = 'Y' THEN
1054: INTO pon_interface_errors
1055: (
1056: interface_type, column_name, table_name, -- 1
1057: error_value, error_message_name, batch_id, -- 2
1058: interface_line_id, auction_header_id, line_number, -- 3
1070: SYSDATE, l_loginid -- 6
1071: )
1072:
1073: WHEN S_CLM_INFO_FLAG = 'N' AND line_origination_code <> 'REQUISITION' AND project_number IS NOT NULL AND pro_project_id IS NULL THEN
1074: INTO pon_interface_errors
1075: (
1076: interface_type, column_name, table_name, -- 1
1077: error_value, error_message_name, batch_id, -- 2
1078: interface_line_id, auction_header_id, line_number, -- 3
1096: AND project_task_number IS NOT NULL
1097: AND NOT EXISTS (SELECT 1
1098: FROM PA_TASKS_EXPEND_V task
1099: WHERE task.project_id = pro_project_id AND task.task_number = project_task_number) THEN
1100: INTO pon_interface_errors
1101: (
1102: interface_type, column_name, table_name, -- 1
1103: error_value, error_message_name, batch_id, -- 2
1104: interface_line_id, auction_header_id, line_number, -- 3
1127: WHERE award.project_id = pro_project_id
1128: AND task.task_number = project_task_number
1129: AND award.task_id = task.task_id
1130: AND task.project_id = pro_project_id) THEN
1131: INTO pon_interface_errors
1132: (
1133: interface_type, column_name, table_name, -- 1
1134: error_value, error_message_name, batch_id, -- 2
1135: interface_line_id, auction_header_id, line_number, -- 3
1149:
1150: WHEN S_CLM_INFO_FLAG = 'N' AND
1151: line_origination_code <> 'REQUISITION' AND project_exp_organization_name IS NOT NULL
1152: AND porg_proj_exp_organization_id IS NULL THEN
1153: INTO pon_interface_errors
1154: (
1155: interface_type, column_name, table_name, -- 1
1156: error_value, error_message_name, batch_id, -- 2
1157: interface_line_id, auction_header_id, line_number, -- 3
1178: AND nvl(exptype.expnd_typ_end_date_Active, trunc(sysdate))
1179: AND trunc(sysdate) BETWEEN nvl(exptype.sys_link_start_date_active, trunc(sysdate))
1180: AND nvl(exptype.sys_link_end_date_Active, trunc(sysdate))) THEN
1181:
1182: INTO pon_interface_errors
1183: (
1184: interface_type, column_name, table_name, -- 1
1185: error_value, error_message_name, batch_id, -- 2
1186: interface_line_id, auction_header_id, line_number, -- 3
1201: WHEN S_CLM_INFO_FLAG = 'N' AND
1202: pro_project_id IS NOT NULL
1203: AND project_award_number IS NULL
1204: AND PON_NEGOTIATION_PUBLISH_PVT.IS_PROJECT_SPONSORED(pro_project_id) = 'Y' THEN
1205: INTO pon_interface_errors
1206: (
1207: interface_type, column_name, table_name, -- 1
1208: error_value, error_message_name, batch_id, -- 2
1209: interface_line_id, auction_header_id, line_number, -- 3
1225: ((project_number IS NULL OR project_task_number IS NULL OR s_project_exp_type IS NULL
1226: OR project_exp_organization_name IS NULL OR project_expenditure_item_date IS NULL) AND
1227: (project_number IS NOT NULL OR project_task_number IS NOT NULL OR s_project_exp_type IS NOT NULL
1228: OR project_exp_organization_name IS NOT NULL OR project_expenditure_item_date IS NOT NULL)) THEN
1229: INTO pon_interface_errors
1230: (
1231: interface_type, column_name, table_name, -- 1
1232: error_value, error_message_name, batch_id, -- 2
1233: interface_line_id, auction_header_id, line_number, -- 3
1254: AND fu.employee_id = peo.person_id
1255: AND SYSDATE >= nvl(fu.start_date, SYSDATE)
1256: AND SYSDATE <= nvl(fu.end_date, SYSDATE) )
1257: THEN
1258: INTO pon_interface_errors
1259: (
1260: interface_type, column_name, table_name, -- 1
1261: error_value, error_message_name, batch_id, -- 2
1262: interface_line_id, auction_header_id, line_number, -- 3
1624: WHERE batch_id = p_batch_id
1625: AND group_line_id IS NULL;
1626:
1627: IF( (l_sol_line_count+l_int_line_count) > 9999 ) THEN
1628: insert into PON_INTERFACE_ERRORS
1629: (interface_type,
1630: column_name,
1631: error_value,
1632: error_message_name,
1655: AND group_line_id IS NOT NULL
1656: GROUP BY clm_info_flag,group_line_id
1657: HAVING Count(*) > Decode(nvl(clm_info_flag,'N'),'Y',99,'N',576)) LOOP
1658:
1659: insert into PON_INTERFACE_ERRORS
1660: (interface_type,
1661: column_name,
1662: error_value,
1663: error_message_name,
1688: IF (is_valid_rule(p_doctype_Id, 'NO_PRICE_QUANTITY_ITEMS')) THEN
1689:
1690: IF (is_valid_rule(p_doctype_Id, 'QUANTITY')) THEN
1691: -- Quantity should be empty
1692: insert into PON_INTERFACE_ERRORS
1693: (interface_type,
1694: column_name,
1695: error_value,
1696: error_message_name,
1711: END IF;
1712:
1713: IF (is_valid_rule(p_doctype_Id, 'UNIT_OF_MEASURE')) THEN
1714: -- UOM should be empty
1715: insert into PON_INTERFACE_ERRORS
1716: (interface_type,
1717: column_name,
1718: error_value,
1719: error_message_name,
1734: END IF;
1735:
1736: IF (is_valid_rule(p_doctype_Id, 'TARGET_PRICE')) THEN
1737: -- Target Price should be empty
1738: insert into PON_INTERFACE_ERRORS
1739: (interface_type,
1740: column_name,
1741: error_value,
1742: error_message_name,
1757: END IF;
1758:
1759: IF (is_valid_rule(p_doctype_Id, 'CURRENT_PRICE')) THEN
1760: -- Current Price should be empty
1761: insert into PON_INTERFACE_ERRORS
1762: (interface_type,
1763: column_name,
1764: error_value,
1765: error_message_name,
1781:
1782: END IF;
1783:
1784: -- For Line Type Check.
1785: insert into PON_INTERFACE_ERRORS
1786: (interface_type,
1787: column_name,
1788: error_message_name,
1789: table_name,
1813: AND Nvl(clm_info_flag,'N') <> 'Y'
1814: and line_type is not null;
1815:
1816:
1817: insert into PON_INTERFACE_ERRORS
1818: (interface_type,
1819: column_name,
1820: error_value,
1821: error_message_name,
1837:
1838:
1839: -- perform the following check if po style has restricted line types
1840: if (l_line_type_restriction = 'SPECIFIED') then
1841: insert into PON_INTERFACE_ERRORS
1842: (interface_type,
1843: column_name,
1844: error_value,
1845: error_message_name,
1865: -- Bug 4722286.
1866: -- perform the following check if po style has restricted purchase basis
1867:
1868: if (l_line_type_restriction = 'ALL') then
1869: insert into PON_INTERFACE_ERRORS
1870: (interface_type,
1871: column_name,
1872: error_value,
1873: error_message_name,
1898: -- are on a standard outcome document
1899: --
1900: IF(p_contract_type = 'STANDARD') THEN
1901: --
1902: INSERT INTO pon_interface_errors
1903: (interface_type,
1904: column_name,
1905: error_value,
1906: error_message_name,
1921: END IF;
1922: --
1923: IF(p_contract_type = 'BLANKET' OR p_contract_type = 'CONTRACT') AND
1924: (p_global_flag = 'N') THEN
1925: INSERT INTO pon_interface_errors
1926: (interface_type,
1927: column_name,
1928: error_value,
1929: error_message_name,
1945: --
1946: -- Do not allow Outside Processing lines for Global Agreements
1947: --
1948: IF(p_contract_type = 'BLANKET' and p_global_flag = 'Y') THEN
1949: insert into pon_interface_errors
1950: (interface_type,
1951: column_name,
1952: error_value,
1953: error_message_name,
1968: END IF;
1969: --
1970: -- For Outside Processing lines, the Item is required
1971: --
1972: insert into pon_interface_errors
1973: (interface_type,
1974: column_name,
1975: error_value,
1976: error_message_name,
1991: AND outside_operation_flag = 'Y';
1992: --
1993: -- For temp labor lines, the job is required
1994: --
1995: INSERT INTO PON_INTERFACE_ERRORS
1996: (interface_type,
1997: column_name,
1998: error_value,
1999: error_message_name,
2049: job_id <> -1;
2050: --
2051: -- Validate the job
2052: --
2053: INSERT INTO PON_INTERFACE_ERRORS (
2054: interface_type,
2055: column_name,
2056: error_value,
2057: error_message_name,
2106: job_id <> -1;
2107: --
2108: -- Validate differential response type
2109: --
2110: insert into PON_INTERFACE_ERRORS
2111: (interface_type,
2112: column_name,
2113: error_value,
2114: error_message_name,
2147: -- ignore inventory item related fields for amount based lines
2148:
2149: l_item_number_delimiter := '.';
2150:
2151: insert into PON_INTERFACE_ERRORS
2152: (interface_type,
2153: column_name,
2154: error_value,
2155: error_message_name,
2279: (category_name = 'CAT_NONE_ENTERED' or category_name is null);
2280:
2281: -- validate description for inventory items where the modified flag is set to false
2282:
2283: insert into PON_INTERFACE_ERRORS
2284: (interface_type,
2285: column_name,
2286: error_value,
2287: error_message_name,
2310: msitl.language = p_language);
2311:
2312: -- validate revision for inventory items
2313:
2314: insert into PON_INTERFACE_ERRORS
2315: (interface_type,
2316: column_name,
2317: error_value,
2318: error_message_name,
2364: and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null))
2365: AND Nvl(clm_info_flag,'N') <> 'Y';
2366:
2367:
2368: INSERT INTO PON_INTERFACE_ERRORS
2369: ( interface_type
2370: , column_name
2371: , error_value
2372: , error_message_name
2388: and NOT((upper(UNIT_OF_MEASURE) = UPPER(l_amount_based_uom) ) or
2389: (upper(UNIT_OF_MEASURE) = UPPER(l_amount_based_unit_of_measure) ))
2390: AND Nvl(clm_info_flag,'N') <> 'Y';
2391:
2392: INSERT INTO PON_INTERFACE_ERRORS
2393: ( interface_type
2394: , column_name
2395: , error_value
2396: , error_message_name
2417:
2418: -- END Line Type Check
2419:
2420: IF (is_valid_rule(p_doctype_Id, 'CATEGORY')) THEN
2421: insert into PON_INTERFACE_ERRORS
2422: (interface_type,
2423: column_name,
2424: error_message_name,
2425: table_name,
2514: where batch_id = p_batch_id
2515: and category_name <> 'CAT_NON_ENTERED'
2516: AND category_id <> -1;
2517:
2518: insert into PON_INTERFACE_ERRORS
2519: (interface_type,
2520: column_name,
2521: error_value,
2522: error_message_name,
2536: AND category_name <> 'CAT_NON_ENTERED';
2537:
2538: -- validate category name for inventory items
2539:
2540: insert into PON_INTERFACE_ERRORS
2541: (interface_type,
2542: column_name,
2543: error_value,
2544: error_message_name,
2590: (p1.ip_category_name is null or p1.ip_category_name = 'IP_CAT_NONE_ENTERED');
2591:
2592: -- iP category needs to be valid
2593:
2594: insert into PON_INTERFACE_ERRORS
2595: (interface_type,
2596: column_name,
2597: error_message_name,
2598: table_name,
2640:
2641: --
2642: -- Item description can't be null
2643: --
2644: insert into PON_INTERFACE_ERRORS
2645: (interface_type,
2646: column_name,
2647: error_message_name,
2648: table_name,
2669: -- Unit of Measure
2670: -- AUCTION and BID --
2671: --
2672: IF (is_valid_rule(p_doctype_Id, 'UNIT_OF_MEASURE')) THEN
2673: insert into PON_INTERFACE_ERRORS
2674: (interface_type,
2675: column_name,
2676: error_message_name,
2677: table_name,
2763: where batch_id = p_batch_id
2764: and unit_of_measure <> 'UOM_NONE_ENTERED'
2765: AND uom_code <> 'XXX';
2766:
2767: insert into PON_INTERFACE_ERRORS
2768: (interface_type,
2769: column_name,
2770: error_value,
2771: error_message_name,
2786: AND nvl(price_and_quantity_apply, 'Y') = 'Y';
2787:
2788: -- validate unit of measure for inventory items
2789:
2790: insert into PON_INTERFACE_ERRORS
2791: (interface_type,
2792: column_name,
2793: error_value,
2794: error_message_name,
2829: -- check quantity can't be null under certain conditions.
2830: --
2831: --
2832: if (p_contract_type = 'STANDARD' or p_contract_type is null) then
2833: insert into PON_INTERFACE_ERRORS
2834: (interface_type,
2835: column_name,
2836: error_message_name,
2837: table_name,
2855: end if;
2856:
2857: -- quantity cannot also be null if line is neither fixed-price based nor amount-based and it has a fixed amount price factor
2858: IF (p_contract_type IN ('BLANKET', 'CONTRACT')) THEN
2859: INSERT INTO pon_interface_errors
2860: (interface_type,
2861: column_name,
2862: error_value,
2863: error_message_name,
2903: -- Quantity > 0 --
2904: -- AUCTION and BID --
2905: --
2906: IF (is_valid_rule(p_doctype_Id, 'QUANTITY')) THEN
2907: insert into PON_INTERFACE_ERRORS
2908: (interface_type,
2909: column_name,
2910: error_value,
2911: error_message_name,
2934: IF (is_valid_rule(p_doctype_Id, 'NEED_BY_DATE')) THEN
2935: /* CLM Period of Performance Dates : For federal document, for outcome doc style
2936: * as SPO, all the POP dates can not be null.
2937: */
2938: insert into PON_INTERFACE_ERRORS
2939: (interface_type,
2940: column_name,
2941: error_value,
2942: error_message_name,
2966:
2967: /* CLM Period of Performance Dates : For federal document, for outcome doc style
2968: * as SPO, need_by_start_date is required, when need_by_date is entered.
2969: */
2970: insert into PON_INTERFACE_ERRORS
2971: (interface_type,
2972: column_name,
2973: error_value,
2974: error_message_name,
2992:
2993: /* CLM Period of Performance Dates : For federal document, for outcome doc style
2994: * as SPO, need_by_date is required, when need_by_start_date is entered.
2995: */
2996: insert into PON_INTERFACE_ERRORS
2997: (interface_type,
2998: column_name,
2999: error_value,
3000: error_message_name,
3015: )
3016: and batch_id = p_batch_id
3017: and nvl(clm_info_flag,'N') = 'N';
3018: -- NEED_BY_FROM_DATE
3019: insert into PON_INTERFACE_ERRORS
3020: (interface_type,
3021: column_name,
3022: error_value,
3023: error_message_name,
3036: where need_by_start_date < sysdate
3037: and batch_id = p_batch_id;
3038: --
3039: -- NEED_BY_TO_DATE
3040: insert into PON_INTERFACE_ERRORS
3041: (interface_type,
3042: column_name,
3043: error_value,
3044: error_message_name,
3058: and batch_id = p_batch_id;
3059:
3060:
3061: -- CLM : Period Of Performance Date Range : CLM_NEED_BY_DATE Validations
3062: insert into PON_INTERFACE_ERRORS
3063: (interface_type,
3064: column_name,
3065: error_value,
3066: error_message_name,
3078: where clm_need_by_date < sysdate
3079: and batch_id = p_batch_id;
3080: --
3081: -- NEED_BY_TO_DATE vs NEED_BY_FROM_DATE
3082: insert into PON_INTERFACE_ERRORS
3083: (interface_type,
3084: column_name,
3085: error_value,
3086: error_message_name,
3105: -- need by required for planned inventory items for SPO
3106: /*Bug : 13700330 : We need this validation for clm also.
3107: For clm, clm_need_by_date field is mandatory for inventory item lines. */
3108: IF(p_contract_type = 'STANDARD') THEN --and nvl(l_is_fed,'N')<>'Y') THEN
3109: insert into PON_INTERFACE_ERRORS
3110: (interface_type,
3111: column_name,
3112: error_value,
3113: error_message_name,
3145: FROM fnd_lookup_values_vl
3146: WHERE lookup_type = 'CLM_DELIVERY_EVENT'
3147: AND meaning = p.CLM_DELIVERY_EVENT);
3148:
3149: insert into PON_INTERFACE_ERRORS
3150: (interface_type,
3151: column_name,
3152: error_value,
3153: error_message_name,
3170: FROM fnd_lookup_values_vl
3171: WHERE lookup_type = 'CLM_PERIOD'
3172: AND meaning = p.CLM_DELIVERY_PERIOD_UOM);
3173:
3174: insert into PON_INTERFACE_ERRORS
3175: (interface_type,
3176: column_name,
3177: error_value,
3178: error_message_name,
3195: FROM fnd_lookup_values_vl
3196: WHERE lookup_type = 'CLM_PERIOD'
3197: AND meaning = p.CLM_POP_DURATION_UOM);
3198:
3199: insert into PON_INTERFACE_ERRORS
3200: (interface_type,
3201: column_name,
3202: error_value,
3203: error_message_name,
3223: --
3224: IF (is_valid_rule(p_doctype_Id, 'SHIP_TO_LOCATION') and (p_contract_type is null or p_contract_type = 'STANDARD')) THEN
3225:
3226: IF (is_required_rule(p_doctype_Id, 'SHIP_TO_LOCATION')) THEN
3227: insert into PON_INTERFACE_ERRORS
3228: ( interface_type
3229: , column_name
3230: , error_message_name
3231: , table_name
3273: AND ship_to_location_id <> -1;
3274: --
3275: --
3276:
3277: insert into PON_INTERFACE_ERRORS
3278: (interface_type,
3279: column_name,
3280: error_value,
3281: error_message_name,
3299: -- Target price NOT <=0 --
3300: -- AUCTION only --
3301: --
3302: IF (is_valid_rule(p_doctype_Id, 'TARGET_PRICE')) THEN
3303: insert into PON_INTERFACE_ERRORS
3304: (interface_type,
3305: column_name,
3306: error_value,
3307: error_message_name,
3324: -- Bid start price NOT <=0 --
3325: -- AUCTION only --
3326: --
3327: IF (is_valid_rule(p_doctype_Id, 'START_PRICE')) THEN
3328: insert into PON_INTERFACE_ERRORS
3329: (interface_type,
3330: column_name,
3331: error_value,
3332: error_message_name,
3348: -- Current price NOT <=0 --
3349: -- AUCTION/RFQs/Offers --
3350: --
3351: IF (is_valid_rule(p_doctype_Id, 'CURRENT_PRICE')) THEN
3352: insert into PON_INTERFACE_ERRORS
3353: (interface_type,
3354: column_name,
3355: error_value,
3356: error_message_name,
3373: -- reserve price NOT <=0 --
3374: -- AUCTION only --
3375: --
3376: IF (is_valid_rule(p_doctype_Id, 'RESERVE_PRICE')) THEN
3377: insert into PON_INTERFACE_ERRORS
3378: (interface_type,
3379: column_name,
3380: error_value,
3381: error_message_name,
3408: -- Buyer AUCTION only --
3409: --
3410:
3411: IF (is_valid_rule(p_doctype_Id, 'TARGET_PRICE') AND is_valid_rule(p_doctype_Id, 'START_PRICE')) THEN
3412: insert into PON_INTERFACE_ERRORS
3413: (interface_type,
3414: column_name,
3415: error_value,
3416: error_message_name,
3433: --
3434: -- Bid start price < TARGET PRICE --
3435: -- Seller AUCTION only --
3436: --
3437: insert into PON_INTERFACE_ERRORS
3438: (interface_type,
3439: column_name,
3440: error_value,
3441: error_message_name,
3455: --
3456: -- bid start price < reserve price
3457: -- Seller AUCTION only --
3458: --
3459: insert into PON_INTERFACE_ERRORS
3460: (interface_type,
3461: column_name,
3462: error_value,
3463: error_message_name,
3477: --
3478: -- reserve price < target price
3479: -- Seller AUCTION only --
3480: --
3481: insert into PON_INTERFACE_ERRORS
3482: (interface_type,
3483: column_name,
3484: error_value,
3485: error_message_name,
3502:
3503: -- unit target price NOT < 0
3504: -- AUCTION only
3505:
3506: insert into PON_INTERFACE_ERRORS
3507: (interface_type,
3508: column_name,
3509: error_value,
3510: error_message_name,
3523: and nvl(price_and_quantity_apply, 'Y') = 'Y'
3524: and batch_id = p_batch_id;
3525:
3526: -- clm validations
3527: insert into PON_INTERFACE_ERRORS
3528: (interface_type,
3529: column_name,
3530: error_message_name,
3531: table_name,
3541: where CLM_BASE_LINE_NUM IS NOT NULL
3542: AND CLM_OPTION_FROM_DATE IS NULL
3543: and batch_id = p_batch_id;
3544:
3545: insert into PON_INTERFACE_ERRORS
3546: (interface_type,
3547: column_name,
3548: error_message_name,
3549: table_name,
3559: where CLM_BASE_LINE_NUM IS NOT NULL
3560: AND CLM_OPTION_TO_DATE IS NULL
3561: and batch_id = p_batch_id;
3562:
3563: insert into PON_INTERFACE_ERRORS
3564: (interface_type,
3565: column_name,
3566: error_value,
3567: error_message_name,
3579: WHERE CLM_BASE_LINE_NUM IS NOT NULL
3580: AND CLM_OPTION_FROM_DATE < sysdate
3581: and batch_id = p_batch_id;
3582: --
3583: insert into PON_INTERFACE_ERRORS
3584: (interface_type,
3585: column_name,
3586: error_value,
3587: error_message_name,
3609: END IF;
3610:
3611: IF (l_is_fed = 'Y') THEN
3612: /* CLM_AMOUNT*/
3613: insert into PON_INTERFACE_ERRORS
3614: (interface_type,
3615: column_name,
3616: error_value,
3617: error_message_name,
3631: AND P1.LINE_TYPE_ID NOT IN (SELECT LINE_TYPE_ID FROM po_line_types_b WHERE ORDER_TYPE_LOOKUP_CODE = 'AMOUNT')
3632: );
3633:
3634: /* CLM_UNIT_PRICE */
3635: insert into PON_INTERFACE_ERRORS
3636: (interface_type,
3637: column_name,
3638: error_value,
3639: error_message_name,
3654: );
3655:
3656:
3657: /* CLM_CONTRACT_TYPE*/
3658: insert into PON_INTERFACE_ERRORS
3659: (interface_type,
3660: column_name,
3661: error_value,
3662: error_message_name,
3685: ));
3686:
3687:
3688: /* CLM_COST_CONSTRAINT*/
3689: insert into PON_INTERFACE_ERRORS
3690: (interface_type,
3691: column_name,
3692: error_value,
3693: error_message_name,
3716: ));
3717:
3718:
3719: /* CLM_BASE_LINE_NUM*/
3720: insert into PON_INTERFACE_ERRORS
3721: (interface_type,
3722: column_name,
3723: error_value,
3724: error_message_name,
3747: );
3748:
3749:
3750: /* CLM_OPTION_INDICATOR */
3751: insert into PON_INTERFACE_ERRORS
3752: (interface_type,
3753: column_name,
3754: error_value,
3755: error_message_name,
3791: l_optional:=pon_auction_pkg.getmessage('PON_AUCTS_OPTIONAL');
3792:
3793:
3794: -- Should not set values for Fixed Price Line types
3795: /* insert into PON_INTERFACE_ERRORS
3796: (interface_type,
3797: column_name,
3798: error_value,
3799: error_message_name,
3853:
3854: SELECT price_tiers_indicator INTO l_price_tiers_indicator FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id;
3855:
3856: -- Validate price break seetings
3857: insert into PON_INTERFACE_ERRORS
3858: (interface_type,
3859: column_name,
3860: error_value,
3861: error_message_name,
3876: AND group_type <> 'GROUP'
3877: and batch_id = p_batch_id;
3878:
3879: -- Response type cannot be null if there are any shipments exists
3880: insert into PON_INTERFACE_ERRORS
3881: (interface_type,
3882: column_name,
3883: error_value,
3884: error_message_name,
3901: AND batch_id = p_batch_id;
3902:
3903:
3904: -- For Global agreements release quantity must be cumulative.
3905: INSERT INTO PON_INTERFACE_ERRORS
3906: (
3907: COLUMN_NAME ,
3908: INTERFACE_TYPE ,
3909: ERROR_MESSAGE_NAME,
3933: AND group_type <> 'GROUP'
3934: AND batch_id = p_batch_id;
3935:
3936: -- Temp based labor cannot have cumilative response
3937: insert into PON_INTERFACE_ERRORS
3938: (interface_type,
3939: column_name,
3940: error_value,
3941: error_message_name,