[Home] [Help]
499: PROCEDURE validate_bids_placed
500: (
501: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
502: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
503: p_interface_type IN pon_interface_errors.interface_type%TYPE,
504: p_userid IN pon_interface_errors.created_by%TYPE,
505: p_rebid IN VARCHAR2,
506: p_bid_all_lines IN VARCHAR2,
507: p_auc_has_items IN VARCHAR2,
500: (
501: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
502: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
503: p_interface_type IN pon_interface_errors.interface_type%TYPE,
504: p_userid IN pon_interface_errors.created_by%TYPE,
505: p_rebid IN VARCHAR2,
506: p_bid_all_lines IN VARCHAR2,
507: p_auc_has_items IN VARCHAR2,
508: p_evaluation_flag IN VARCHAR2, -- Added for ER: Supplier Management: Supplier Evaluation
506: p_bid_all_lines IN VARCHAR2,
507: p_auc_has_items IN VARCHAR2,
508: p_evaluation_flag IN VARCHAR2, -- Added for ER: Supplier Management: Supplier Evaluation
509: p_suffix IN VARCHAR2,
510: p_batch_id IN pon_interface_errors.batch_id%TYPE,
511: p_request_id IN pon_interface_errors.request_id%TYPE
512: ) IS
513: l_header_modified VARCHAR2(1);
514:
507: p_auc_has_items IN VARCHAR2,
508: p_evaluation_flag IN VARCHAR2, -- Added for ER: Supplier Management: Supplier Evaluation
509: p_suffix IN VARCHAR2,
510: p_batch_id IN pon_interface_errors.batch_id%TYPE,
511: p_request_id IN pon_interface_errors.request_id%TYPE
512: ) IS
513: l_header_modified VARCHAR2(1);
514:
515: --Unsolicited Lines Project
580: if unsol line count is also zero. If so, throw error.
581: */
582: WHEN p_rebid = 'Y' AND l_header_modified = 'N' AND
583: (s_changed_line_count = 0 AND (l_allow_unsol_lines = 'Y' AND l_changed_unsol_line_count = 0))THEN
584: INTO pon_interface_errors
585: (INTERFACE_TYPE,
586: TABLE_NAME,
587: BATCH_ID,
588: ERROR_MESSAGE_NAME,
610:
611: -- All lines must have recieved a bid if that is a requirement
612: -- Added the p_evaluation_flag condition for ER: Supplier Management: Supplier Evaluation
613: WHEN p_bid_all_lines = 'Y' AND s_avail_bidded_lines <> s_bidded_lines AND p_evaluation_flag = 'N' THEN
614: INTO pon_interface_errors
615: (INTERFACE_TYPE,
616: TABLE_NAME,
617: BATCH_ID,
618: ERROR_MESSAGE_NAME,
646: /* 13565622 : Relax this validation, when priced unsolicited lines exist. */
647: WHEN p_auc_has_items = 'Y' AND s_has_bid_count = 0 AND p_evaluation_flag = 'N'
648: AND PON_UNSOL_UTIL_PKG.HAS_PRICED_UNSOL_RESP_LINES(p_bid_number) = 'N' THEN
649: /* modified for bug#12951763 */
650: INTO pon_interface_errors
651: (INTERFACE_TYPE,
652: TABLE_NAME,
653: BATCH_ID,
654: ERROR_MESSAGE_NAME,
728: PROCEDURE validate_lots_and_groups
729: (
730: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
731: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
732: p_interface_type IN pon_interface_errors.interface_type%TYPE,
733: p_userid IN pon_interface_errors.created_by%TYPE,
734: p_spreadsheet IN VARCHAR2,
735: p_batch_id IN pon_interface_errors.batch_id%TYPE,
736: p_request_id IN pon_interface_errors.request_id%TYPE
729: (
730: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
731: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
732: p_interface_type IN pon_interface_errors.interface_type%TYPE,
733: p_userid IN pon_interface_errors.created_by%TYPE,
734: p_spreadsheet IN VARCHAR2,
735: p_batch_id IN pon_interface_errors.batch_id%TYPE,
736: p_request_id IN pon_interface_errors.request_id%TYPE
737: ) IS
731: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
732: p_interface_type IN pon_interface_errors.interface_type%TYPE,
733: p_userid IN pon_interface_errors.created_by%TYPE,
734: p_spreadsheet IN VARCHAR2,
735: p_batch_id IN pon_interface_errors.batch_id%TYPE,
736: p_request_id IN pon_interface_errors.request_id%TYPE
737: ) IS
738: BEGIN
739:
732: p_interface_type IN pon_interface_errors.interface_type%TYPE,
733: p_userid IN pon_interface_errors.created_by%TYPE,
734: p_spreadsheet IN VARCHAR2,
735: p_batch_id IN pon_interface_errors.batch_id%TYPE,
736: p_request_id IN pon_interface_errors.request_id%TYPE
737: ) IS
738: BEGIN
739:
740: INSERT ALL
748: AND al.auction_header_id = bl.auction_header_id
749: AND al.line_number = bl.line_number
750: AND al.auction_header_id = p_auc_header_id
751: AND al.parent_line_number = s_line_number) = 'N' THEN
752: INTO pon_interface_errors
753: (INTERFACE_TYPE,
754: TABLE_NAME,
755: BATCH_ID,
756: INTERFACE_LINE_ID,
801: AND al.auction_header_id = p_auc_header_id
802: AND al.parent_line_number = s_line_number
803: AND bl.has_bid_flag = 'Y'
804: AND rownum = 1) THEN
805: INTO pon_interface_errors
806: (INTERFACE_TYPE,
807: TABLE_NAME,
808: BATCH_ID,
809: INTERFACE_LINE_ID,
874: PROCEDURE validate_lines
875: (
876: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
877: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
878: p_interface_type IN pon_interface_errors.interface_type%TYPE,
879: p_userid IN pon_interface_errors.created_by%TYPE,
880: p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
881: p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
882: p_spreadsheet IN VARCHAR2,
875: (
876: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
877: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
878: p_interface_type IN pon_interface_errors.interface_type%TYPE,
879: p_userid IN pon_interface_errors.created_by%TYPE,
880: p_tpid IN pon_bid_headers.trading_partner_id%TYPE,
881: p_vensid IN pon_bid_headers.vendor_site_id%TYPE,
882: p_spreadsheet IN VARCHAR2,
883: p_blanket IN VARCHAR2,
895: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
896: p_amt_precision IN fnd_currencies.precision%TYPE,
897: p_bid_curr_code IN pon_bid_headers.bid_currency_code%TYPE,
898: p_suffix IN VARCHAR2,
899: p_batch_id IN pon_interface_errors.batch_id%TYPE,
900: p_request_id IN pon_interface_errors.request_id%TYPE
901: --added by Allen Yang for Surrogate Bid 2008/09/03
902: --------------------------------------------------
903: , p_two_part_tech_surrogate_flag IN VARCHAR2
896: p_amt_precision IN fnd_currencies.precision%TYPE,
897: p_bid_curr_code IN pon_bid_headers.bid_currency_code%TYPE,
898: p_suffix IN VARCHAR2,
899: p_batch_id IN pon_interface_errors.batch_id%TYPE,
900: p_request_id IN pon_interface_errors.request_id%TYPE
901: --added by Allen Yang for Surrogate Bid 2008/09/03
902: --------------------------------------------------
903: , p_two_part_tech_surrogate_flag IN VARCHAR2
904: --------------------------------------------------
915: INSERT ALL
916:
917: -- Header level field min_bid_change is required if there is a proxy line
918: WHEN p_min_bid_change IS null AND s_proxy_bid_limit_price IS NOT null THEN
919: INTO pon_interface_errors
920: (INTERFACE_TYPE,
921: COLUMN_NAME,
922: TABLE_NAME,
923: BATCH_ID,
976: ---------------------------------------------------
977: AND p_two_part_tech_surrogate_flag = 'N'
978: ---------------------------------------------------
979: THEN
980: INTO pon_interface_errors
981: (INTERFACE_TYPE,
982: TABLE_NAME,
983: BATCH_ID,
984: INTERFACE_LINE_ID,
1031: ---------------------------------------------------
1032: AND p_two_part_tech_surrogate_flag = 'N'
1033: ---------------------------------------------------
1034: THEN
1035: INTO pon_interface_errors
1036: (INTERFACE_TYPE,
1037: TABLE_NAME,
1038: COLUMN_NAME,
1039: BATCH_ID,
1083: s_entity_message_code)
1084:
1085: -- Quantity is a required field if it is editable
1086: WHEN p_blanket = 'N' AND s_qty_editable = 'Y' AND s_bid_quantity IS null THEN
1087: INTO pon_interface_errors
1088: (INTERFACE_TYPE,
1089: TABLE_NAME,
1090: COLUMN_NAME,
1091: BATCH_ID,
1136:
1137: -- Bug 7460446 Bid cannot be zero or negative
1138: -- Doesn't apply for blanket agreements
1139: WHEN p_blanket = 'N' AND s_bid_quantity <= 0 THEN
1140: INTO pon_interface_errors
1141: (INTERFACE_TYPE,
1142: COLUMN_NAME,
1143: TABLE_NAME,
1144: BATCH_ID,
1191:
1192: -- Bid quantity should not exceed auction quantity
1193: -- Does not apply for blanket agreements
1194: WHEN p_blanket = 'N' AND s_bid_quantity > s_auc_quantity THEN
1195: INTO pon_interface_errors
1196: (INTERFACE_TYPE,
1197: COLUMN_NAME,
1198: TABLE_NAME,
1199: BATCH_ID,
1252: ---------------------------------------------------
1253: AND p_two_part_tech_surrogate_flag = 'N'
1254: ---------------------------------------------------
1255: THEN
1256: INTO pon_interface_errors
1257: (INTERFACE_TYPE,
1258: COLUMN_NAME,
1259: TABLE_NAME,
1260: BATCH_ID,
1310: ---------------------------------------------------
1311: AND p_two_part_tech_surrogate_flag = 'N'
1312: ---------------------------------------------------
1313: THEN
1314: INTO pon_interface_errors
1315: (INTERFACE_TYPE,
1316: COLUMN_NAME,
1317: TABLE_NAME,
1318: BATCH_ID,
1367: WHEN p_blanket = 'N'
1368: AND (s_promised_date is not null and (s_clm_pop_start_date is not null OR s_clm_pop_end_date is not null))
1369: AND p_two_part_tech_surrogate_flag = 'N'
1370: THEN
1371: INTO pon_interface_errors
1372: (INTERFACE_TYPE,
1373: COLUMN_NAME,
1374: TABLE_NAME,
1375: BATCH_ID,
1425: WHEN p_blanket = 'N'
1426: AND (s_promised_date is null and (s_clm_pop_start_date is not null AND s_clm_pop_end_date is null))
1427: AND p_two_part_tech_surrogate_flag = 'N'
1428: THEN
1429: INTO pon_interface_errors
1430: (INTERFACE_TYPE,
1431: COLUMN_NAME,
1432: TABLE_NAME,
1433: BATCH_ID,
1481: WHEN p_blanket = 'N'
1482: AND (s_promised_date is null and (s_clm_pop_start_date is null AND s_clm_pop_end_date is not null))
1483: AND p_two_part_tech_surrogate_flag = 'N'
1484: THEN
1485: INTO pon_interface_errors
1486: (INTERFACE_TYPE,
1487: COLUMN_NAME,
1488: TABLE_NAME,
1489: BATCH_ID,
1538: (s_clm_pop_start_date is not null AND s_clm_pop_end_date is not null AND (s_clm_pop_end_date <= s_clm_pop_start_date))
1539: )
1540: AND p_two_part_tech_surrogate_flag = 'N'
1541: THEN
1542: INTO pon_interface_errors
1543: (INTERFACE_TYPE,
1544: COLUMN_NAME,
1545: TABLE_NAME,
1546: BATCH_ID,
1596: (s_clm_pop_start_date is not null AND s_clm_pop_start_date < s_current_date)
1597: )
1598: AND p_two_part_tech_surrogate_flag = 'N'
1599: THEN
1600: INTO pon_interface_errors
1601: (INTERFACE_TYPE,
1602: COLUMN_NAME,
1603: TABLE_NAME,
1604: BATCH_ID,
1654: (s_clm_pop_end_date is not null AND s_clm_pop_end_date < s_current_date)
1655: )
1656: AND p_two_part_tech_surrogate_flag = 'N'
1657: THEN
1658: INTO pon_interface_errors
1659: (INTERFACE_TYPE,
1660: COLUMN_NAME,
1661: TABLE_NAME,
1662: BATCH_ID,
1713: AND s_clm_del_event is not null
1714: AND s_promised_period is not null
1715: AND s_promised_period <= 0
1716: THEN
1717: INTO pon_interface_errors
1718: (INTERFACE_TYPE,
1719: COLUMN_NAME,
1720: TABLE_NAME,
1721: BATCH_ID,
1771: AND s_clm_del_event is not null
1772: AND s_promised_pop_duration is not null
1773: AND s_promised_pop_duration <= 0
1774: THEN
1775: INTO pon_interface_errors
1776: (INTERFACE_TYPE,
1777: COLUMN_NAME,
1778: TABLE_NAME,
1779: BATCH_ID,
1825: s_entity_message_code)
1826:
1827: -- po_bid_min_rel_amount should not be negative
1828: WHEN p_blanket = 'Y' AND s_po_bid_min_rel_amount < 0 THEN
1829: INTO pon_interface_errors
1830: (INTERFACE_TYPE,
1831: COLUMN_NAME,
1832: TABLE_NAME,
1833: BATCH_ID,
1880:
1881: -- po_bid_min_rel_amount should not exceed currency precision
1882: WHEN p_blanket = 'Y' AND validate_currency_precision(
1883: s_po_bid_min_rel_amount, p_amt_precision) = 'F' THEN
1884: INTO pon_interface_errors
1885: (INTERFACE_TYPE,
1886: COLUMN_NAME,
1887: TABLE_NAME,
1888: BATCH_ID,
1942: ---------------------------------------------------
1943: AND p_two_part_tech_surrogate_flag = 'N'
1944: ---------------------------------------------------
1945: THEN
1946: INTO pon_interface_errors
1947: (INTERFACE_TYPE,
1948: COLUMN_NAME,
1949: TABLE_NAME,
1950: BATCH_ID,
2005: ---------------------------------------------------
2006: AND p_two_part_tech_surrogate_flag = 'N'
2007: ---------------------------------------------------
2008: THEN
2009: INTO pon_interface_errors
2010: (INTERFACE_TYPE,
2011: COLUMN_NAME,
2012: TABLE_NAME,
2013: BATCH_ID,
2065: ---------------------------------------------------
2066: AND p_two_part_tech_surrogate_flag = 'N'
2067: ---------------------------------------------------
2068: THEN
2069: INTO pon_interface_errors
2070: (INTERFACE_TYPE,
2071: COLUMN_NAME,
2072: TABLE_NAME,
2073: BATCH_ID,
2173: ---------------------------------------------------
2174: AND p_two_part_tech_surrogate_flag = 'N'
2175: ---------------------------------------------------
2176: THEN
2177: INTO pon_interface_errors
2178: (INTERFACE_TYPE,
2179: COLUMN_NAME,
2180: TABLE_NAME,
2181: BATCH_ID,
2238: /*For clm bug : 10136754 : For federal : Only goods line types should be validated against solicitation precision */
2239: ( (( nvl(g_is_fed,'N')='Y' AND s_order_type_lookup_code = 'QUANTITY') OR nvl(g_is_fed,'N')='N') AND
2240: validate_price_precision(s_bid_currency_price, p_price_precision) = 'F')
2241: THEN
2242: INTO pon_interface_errors
2243: (INTERFACE_TYPE,
2244: COLUMN_NAME,
2245: TABLE_NAME,
2246: BATCH_ID,
2302: */
2303: ( (nvl(g_is_fed,'N')='Y' AND s_order_type_lookup_code <> 'QUANTITY') AND
2304: validate_price_precision(s_bid_currency_price, p_amt_precision) = 'F')
2305: THEN
2306: INTO pon_interface_errors
2307: (INTERFACE_TYPE,
2308: COLUMN_NAME,
2309: TABLE_NAME,
2310: BATCH_ID,
2364: ---------------------------------------------------
2365: AND p_two_part_tech_surrogate_flag = 'N'
2366: ---------------------------------------------------
2367: THEN
2368: INTO pon_interface_errors
2369: (INTERFACE_TYPE,
2370: COLUMN_NAME,
2371: TABLE_NAME,
2372: BATCH_ID,
2423: ---------------------------------------------------
2424: AND p_two_part_tech_surrogate_flag = 'N'
2425: ---------------------------------------------------
2426: THEN
2427: INTO pon_interface_errors
2428: (INTERFACE_TYPE,
2429: COLUMN_NAME,
2430: TABLE_NAME,
2431: BATCH_ID,
2485: ---------------------------------------------------
2486: p_two_part_tech_surrogate_flag = 'N' AND
2487: ---------------------------------------------------
2488: validate_price_precision(s_bid_currency_limit_price, p_price_precision) = 'F' THEN
2489: INTO pon_interface_errors
2490: (INTERFACE_TYPE,
2491: COLUMN_NAME,
2492: TABLE_NAME,
2493: BATCH_ID,
2566: ---------------------------------------------------
2567: AND p_two_part_tech_surrogate_flag = 'N'
2568: ---------------------------------------------------
2569: THEN
2570: INTO pon_interface_errors
2571: (INTERFACE_TYPE,
2572: COLUMN_NAME,
2573: TABLE_NAME,
2574: BATCH_ID,
2648: ---------------------------------------------------
2649: AND p_two_part_tech_surrogate_flag = 'N'
2650: ---------------------------------------------------
2651: THEN
2652: INTO pon_interface_errors
2653: (INTERFACE_TYPE,
2654: COLUMN_NAME,
2655: TABLE_NAME,
2656: BATCH_ID,
2724: ---------------------------------------------------
2725: AND p_two_part_tech_surrogate_flag = 'N'
2726: ---------------------------------------------------
2727: THEN
2728: INTO pon_interface_errors
2729: (INTERFACE_TYPE,
2730: COLUMN_NAME,
2731: TABLE_NAME,
2732: BATCH_ID,
2795: ---------------------------------------------------
2796: AND p_two_part_tech_surrogate_flag = 'N'
2797: ---------------------------------------------------
2798: THEN
2799: INTO pon_interface_errors
2800: (INTERFACE_TYPE,
2801: COLUMN_NAME,
2802: TABLE_NAME,
2803: BATCH_ID,
2859: ---------------------------------------------------
2860: AND p_two_part_tech_surrogate_flag = 'N'
2861: ---------------------------------------------------
2862: THEN
2863: INTO pon_interface_errors
2864: (INTERFACE_TYPE,
2865: COLUMN_NAME,
2866: TABLE_NAME,
2867: BATCH_ID,
2918: -- There was a previous bid on this line which was deleted
2919: -- Not allowed to unbid when rebidding.
2920: WHEN p_rebid = 'Y' AND s_is_changed_line_flag = 'Y'
2921: AND s_has_bid_flag = 'N' THEN
2922: INTO pon_interface_errors
2923: (INTERFACE_TYPE,
2924: TABLE_NAME,
2925: BATCH_ID,
2926: INTERFACE_LINE_ID,
2970: ---------------------------------------------------
2971: AND p_two_part_tech_surrogate_flag = 'N'
2972: ---------------------------------------------------
2973: THEN
2974: INTO pon_interface_errors
2975: (INTERFACE_TYPE,
2976: TABLE_NAME,
2977: BATCH_ID,
2978: INTERFACE_LINE_ID,
3033: WHERE bpb.bid_number = p_bid_number
3034: AND bpb.line_number = s_line_number
3035: AND bpb.shipment_type = 'PRICE BREAK')
3036: THEN
3037: INTO pon_interface_errors
3038: (INTERFACE_TYPE,
3039: TABLE_NAME,
3040: BATCH_ID,
3041: INTERFACE_LINE_ID,
3146: ---------------------------------------------------
3147: p_two_part_tech_surrogate_flag = 'N' AND
3148: ---------------------------------------------------
3149: retainage_rate_percent IS NOT NULL AND (retainage_rate_percent < 0 OR retainage_rate_percent > 100) THEN
3150: INTO pon_interface_errors
3151: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3152: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3153: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3154: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3172: ---------------------------------------------------
3173: p_two_part_tech_surrogate_flag = 'N' AND
3174: ---------------------------------------------------
3175: bid_curr_max_retainage_amt IS NOT NULL AND bid_curr_max_retainage_amt < 0 THEN
3176: INTO pon_interface_errors
3177: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3178: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3179: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3180: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3198: ---------------------------------------------------
3199: p_two_part_tech_surrogate_flag = 'N' AND
3200: ---------------------------------------------------
3201: bid_curr_advance_amount IS NOT NULL AND bid_curr_advance_amount < 0 THEN
3202: INTO pon_interface_errors
3203: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3204: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3205: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3206: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3225: p_two_part_tech_surrogate_flag = 'N' AND
3226: ---------------------------------------------------
3227: progress_pymt_rate_percent IS NOT NULL AND
3228: (progress_pymt_rate_percent < 0 OR progress_pymt_rate_percent > 100) THEN
3229: INTO pon_interface_errors
3230: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3231: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3232: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3233: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3252: p_two_part_tech_surrogate_flag = 'N' AND
3253: ---------------------------------------------------
3254: recoupment_rate_percent IS NOT NULL AND
3255: (recoupment_rate_percent < 0 OR recoupment_rate_percent > 100) THEN
3256: INTO pon_interface_errors
3257: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3258: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3259: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3260: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3279: p_two_part_tech_surrogate_flag = 'N' AND
3280: ---------------------------------------------------
3281: progress_payment_type = 'FINANCE' AND contract_type='STANDARD'
3282: AND s_group_type NOT IN ('GROUP','LOT_LINE') AND s_clm_info_flag <> 'Y' AND progress_pymt_rate_percent IS NULL THEN
3283: INTO pon_interface_errors
3284: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3285: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3286: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3287: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3310: p_two_part_tech_surrogate_flag = 'N' AND
3311: ---------------------------------------------------
3312: retainage_negotiable_flag = 'Y'
3313: AND s_group_type NOT IN ('GROUP','LOT_LINE') AND s_clm_info_flag <> 'Y' AND retainage_rate_percent IS NULL THEN
3314: INTO pon_interface_errors
3315: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3316: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3317: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3318: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3339: p_two_part_tech_surrogate_flag = 'N' AND
3340: ---------------------------------------------------
3341: max_retainage_negotiable_flag = 'Y'
3342: AND s_group_type NOT IN ('GROUP','LOT_LINE') AND s_clm_info_flag <> 'Y' AND bid_curr_max_retainage_amt IS NULL THEN
3343: INTO pon_interface_errors
3344: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3345: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3346: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3347: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3373: ((recoupment_negotiable_flag = 'Y' AND recoupment_rate_percent IS NULL) OR
3374: ((progress_pymt_rate_percent IS NOT NULL OR bid_curr_advance_amount IS NOT NULL) AND
3375: recoupment_rate_percent IS NULL)))
3376: THEN
3377: INTO pon_interface_errors
3378: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3379: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3380: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3381: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3404: advance_negotiable_flag = 'Y'
3405: AND s_group_type NOT IN ('GROUP','LOT_LINE')
3406: AND s_clm_info_flag <> 'Y'
3407: AND bid_curr_advance_amount IS NULL THEN
3408: INTO pon_interface_errors
3409: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3410: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3411: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3412: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3473: p_two_part_tech_surrogate_flag = 'N' AND
3474: ---------------------------------------------------
3475: bid_curr_advance_amount IS NOT NULL AND s_bid_currency_unit_price IS NOT NULL AND
3476: (bid_curr_advance_amount > (NVL(s_bid_quantity,1) * s_bid_currency_unit_price)) THEN
3477: INTO pon_interface_errors
3478: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3479: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3480: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3481: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3506: WHERE p_bps.auction_header_id=p_auc_header_id
3507: AND p_bps.bid_line_number=s_line_number
3508: AND p_bps.bid_number=p_bid_number))
3509: + nvl(bid_curr_advance_amount,0)) * 100)/((nvl(s_bid_quantity, 1) * s_bid_currency_unit_price))) THEN
3510: INTO pon_interface_errors
3511: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3512: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3513: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3514: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3535: (p_spreadsheet = g_online_mode OR p_spreadsheet = g_xml_upload_mode) AND progress_payment_type = 'ACTUAL' AND
3536: recoupment_rate_percent IS NOT NULL AND bid_curr_advance_amount IS NOT NULL AND
3537: s_bid_currency_unit_price IS NOT NULL AND
3538: recoupment_rate_percent <((bid_curr_advance_amount * 100)/(nvl(s_bid_quantity,1) * s_bid_currency_unit_price)) THEN
3539: INTO pon_interface_errors
3540: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3541: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3542: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3543: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3565: has_bid_payments_flag = 'Y' AND
3566: s_bid_currency_unit_price IS NOT NULL AND
3567: round(nvl(s_bid_quantity,1)* s_bid_currency_unit_price,p_amt_precision) <> s_sum_pymt_amt --bug 14657112
3568: THEN
3569: INTO pon_interface_errors
3570: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3571: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3572: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3573: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3602: FROM pon_bid_payments_shipments
3603: WHERE auction_header_id = p_auc_header_id
3604: AND bid_number = p_bid_number
3605: AND bid_line_number = s_line_number) THEN
3606: INTO pon_interface_errors
3607: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3608: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3609: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3610: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3628: p_two_part_tech_surrogate_flag = 'N' AND
3629: ---------------------------------------------------
3630: bid_curr_advance_amount IS NOT NULL AND
3631: validate_currency_precision(bid_curr_advance_amount, p_amt_precision) = 'F' THEN
3632: INTO pon_interface_errors
3633: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3634: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3635: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3636: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3657: p_two_part_tech_surrogate_flag = 'N' AND
3658: ---------------------------------------------------
3659: bid_curr_max_retainage_amt IS NOT NULL AND
3660: validate_currency_precision(bid_curr_max_retainage_amt, p_amt_precision) = 'F' THEN
3661: INTO pon_interface_errors
3662: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3663: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3664: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3665: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3685: p_two_part_tech_surrogate_flag = 'N' AND
3686: ---------------------------------------------------
3687: progress_pymt_rate_percent IS NOT NULL AND
3688: validate_currency_precision(progress_pymt_rate_percent, 2) = 'F' THEN
3689: INTO pon_interface_errors
3690: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3691: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3692: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3693: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3713: p_two_part_tech_surrogate_flag = 'N' AND
3714: ---------------------------------------------------
3715: retainage_rate_percent IS NOT NULL AND
3716: validate_currency_precision(retainage_rate_percent, 2) = 'F' THEN
3717: INTO pon_interface_errors
3718: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3719: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3720: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3721: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3741: p_two_part_tech_surrogate_flag = 'N' AND
3742: ---------------------------------------------------
3743: recoupment_rate_percent IS NOT NULL AND
3744: validate_currency_precision(recoupment_rate_percent, 2) = 'F' THEN
3745: INTO pon_interface_errors
3746: (INTERFACE_TYPE, COLUMN_NAME, TABLE_NAME, --1
3747: BATCH_ID, INTERFACE_LINE_ID, ERROR_MESSAGE_NAME, --2
3748: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, --3
3749: LAST_UPDATE_DATE, REQUEST_ID, ENTITY_TYPE, --4
3808: PROCEDURE validate_requirements
3809: (
3810: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
3811: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
3812: p_interface_type IN pon_interface_errors.interface_type%TYPE,
3813: p_userid IN pon_interface_errors.created_by%TYPE,
3814: p_spreadsheet IN VARCHAR2,
3815: p_suffix IN VARCHAR2,
3816: p_batch_id IN pon_interface_errors.batch_id%TYPE,
3809: (
3810: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
3811: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
3812: p_interface_type IN pon_interface_errors.interface_type%TYPE,
3813: p_userid IN pon_interface_errors.created_by%TYPE,
3814: p_spreadsheet IN VARCHAR2,
3815: p_suffix IN VARCHAR2,
3816: p_batch_id IN pon_interface_errors.batch_id%TYPE,
3817: p_request_id IN pon_interface_errors.request_id%TYPE
3812: p_interface_type IN pon_interface_errors.interface_type%TYPE,
3813: p_userid IN pon_interface_errors.created_by%TYPE,
3814: p_spreadsheet IN VARCHAR2,
3815: p_suffix IN VARCHAR2,
3816: p_batch_id IN pon_interface_errors.batch_id%TYPE,
3817: p_request_id IN pon_interface_errors.request_id%TYPE
3818: -- added by Allen Yang for Surrogate Bid 2008/09/03
3819: ---------------------------------------------------
3820: , p_two_part_tech_surrogate_flag IN VARCHAR2
3813: p_userid IN pon_interface_errors.created_by%TYPE,
3814: p_spreadsheet IN VARCHAR2,
3815: p_suffix IN VARCHAR2,
3816: p_batch_id IN pon_interface_errors.batch_id%TYPE,
3817: p_request_id IN pon_interface_errors.request_id%TYPE
3818: -- added by Allen Yang for Surrogate Bid 2008/09/03
3819: ---------------------------------------------------
3820: , p_two_part_tech_surrogate_flag IN VARCHAR2
3821: ---------------------------------------------------
3833: AND s_value IS null AND p_two_part_tech_surrogate_flag = 'Y'
3834: AND s_two_part_section_type = 'TECHNICAL')
3835: -------------------------------------------------------------------
3836: THEN
3837: INTO pon_interface_errors
3838: (INTERFACE_TYPE,
3839: COLUMN_NAME,
3840: TABLE_NAME,
3841: BATCH_ID,
3890: WHERE bs.auction_header_id = p_auc_header_id
3891: AND bs.line_number = s_line_number
3892: AND bs.attribute_sequence_number = s_sequence_number
3893: AND bs.value = s_value) THEN
3894: INTO pon_interface_errors
3895: (INTERFACE_TYPE,
3896: COLUMN_NAME,
3897: TABLE_NAME,
3898: BATCH_ID,
3992: PROCEDURE validate_attributes
3993: (
3994: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
3995: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
3996: p_interface_type IN pon_interface_errors.interface_type%TYPE,
3997: p_userid IN pon_interface_errors.created_by%TYPE,
3998: p_spreadsheet IN VARCHAR2,
3999: p_suffix IN VARCHAR2,
4000: p_batch_id IN pon_interface_errors.batch_id%TYPE,
3993: (
3994: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
3995: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
3996: p_interface_type IN pon_interface_errors.interface_type%TYPE,
3997: p_userid IN pon_interface_errors.created_by%TYPE,
3998: p_spreadsheet IN VARCHAR2,
3999: p_suffix IN VARCHAR2,
4000: p_batch_id IN pon_interface_errors.batch_id%TYPE,
4001: p_request_id IN pon_interface_errors.request_id%TYPE
3996: p_interface_type IN pon_interface_errors.interface_type%TYPE,
3997: p_userid IN pon_interface_errors.created_by%TYPE,
3998: p_spreadsheet IN VARCHAR2,
3999: p_suffix IN VARCHAR2,
4000: p_batch_id IN pon_interface_errors.batch_id%TYPE,
4001: p_request_id IN pon_interface_errors.request_id%TYPE
4002: ) IS
4003: BEGIN
4004:
3997: p_userid IN pon_interface_errors.created_by%TYPE,
3998: p_spreadsheet IN VARCHAR2,
3999: p_suffix IN VARCHAR2,
4000: p_batch_id IN pon_interface_errors.batch_id%TYPE,
4001: p_request_id IN pon_interface_errors.request_id%TYPE
4002: ) IS
4003: BEGIN
4004:
4005: INSERT FIRST
4005: INSERT FIRST
4006:
4007: -- value must be entered if it is a required attribute
4008: WHEN s_mandatory_flag = 'Y' AND s_value IS null THEN
4009: INTO pon_interface_errors
4010: (INTERFACE_TYPE,
4011: COLUMN_NAME,
4012: TABLE_NAME,
4013: BATCH_ID,
4069: WHERE bs.auction_header_id = p_auc_header_id
4070: AND bs.line_number = s_line_number
4071: AND bs.attribute_sequence_number = s_sequence_number
4072: AND bs.value = s_value) THEN
4073: INTO pon_interface_errors
4074: (INTERFACE_TYPE,
4075: COLUMN_NAME,
4076: TABLE_NAME,
4077: BATCH_ID,
4157: PROCEDURE validate_cost_factors
4158: (
4159: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
4160: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
4161: p_interface_type IN pon_interface_errors.interface_type%TYPE,
4162: p_userid IN pon_interface_errors.created_by%TYPE,
4163: p_spreadsheet IN VARCHAR2,
4164: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
4165: p_amt_precision IN fnd_currencies.precision%TYPE,
4158: (
4159: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
4160: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
4161: p_interface_type IN pon_interface_errors.interface_type%TYPE,
4162: p_userid IN pon_interface_errors.created_by%TYPE,
4163: p_spreadsheet IN VARCHAR2,
4164: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
4165: p_amt_precision IN fnd_currencies.precision%TYPE,
4166: p_suffix IN VARCHAR2,
4163: p_spreadsheet IN VARCHAR2,
4164: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
4165: p_amt_precision IN fnd_currencies.precision%TYPE,
4166: p_suffix IN VARCHAR2,
4167: p_batch_id IN pon_interface_errors.batch_id%TYPE,
4168: p_request_id IN pon_interface_errors.request_id%TYPE
4169: ) IS
4170: BEGIN
4171:
4164: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
4165: p_amt_precision IN fnd_currencies.precision%TYPE,
4166: p_suffix IN VARCHAR2,
4167: p_batch_id IN pon_interface_errors.batch_id%TYPE,
4168: p_request_id IN pon_interface_errors.request_id%TYPE
4169: ) IS
4170: BEGIN
4171:
4172: -- The following validations are performed only for SUPPLIER cost factors
4173: INSERT FIRST
4174:
4175: -- bid_currency_value must recieve a bid
4176: WHEN s_bid_currency_value IS null THEN
4177: INTO pon_interface_errors
4178: (INTERFACE_TYPE,
4179: COLUMN_NAME,
4180: TABLE_NAME,
4181: BATCH_ID,
4231: s_entity_message_code)
4232:
4233: -- Line price cost factor must be positive
4234: WHEN s_sequence_number = -10 AND s_bid_currency_value <= 0 THEN
4235: INTO pon_interface_errors
4236: (INTERFACE_TYPE,
4237: COLUMN_NAME,
4238: TABLE_NAME,
4239: BATCH_ID,
4291: s_entity_message_code)
4292:
4293: -- Cost factor value must be postive or zero if not line price
4294: WHEN s_sequence_number <> -10 AND s_bid_currency_value < 0 THEN
4295: INTO pon_interface_errors
4296: (INTERFACE_TYPE,
4297: COLUMN_NAME,
4298: TABLE_NAME,
4299: BATCH_ID,
4353: -- bid_currency_precision must not exceed price precision for PER_UNIT cf
4354: WHEN s_pricing_basis = 'PER_UNIT'
4355: AND validate_price_precision(s_bid_currency_value,
4356: p_price_precision) = 'F' THEN
4357: INTO pon_interface_errors
4358: (INTERFACE_TYPE,
4359: COLUMN_NAME,
4360: TABLE_NAME,
4361: BATCH_ID,
4415: -- bid_currency_precision must not exceed currency precision for FIXED_AMOUNT cf
4416: WHEN s_pricing_basis = 'FIXED_AMOUNT'
4417: AND validate_currency_precision(s_bid_currency_value,
4418: p_amt_precision) = 'F' THEN
4419: INTO pon_interface_errors
4420: (INTERFACE_TYPE,
4421: COLUMN_NAME,
4422: TABLE_NAME,
4423: BATCH_ID,
4514: PROCEDURE validate_price_breaks
4515: (
4516: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
4517: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
4518: p_interface_type IN pon_interface_errors.interface_type%TYPE,
4519: p_userid IN pon_interface_errors.created_by%TYPE,
4520: p_spreadsheet IN VARCHAR2,
4521: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
4522: p_trans_view IN VARCHAR2,
4515: (
4516: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
4517: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
4518: p_interface_type IN pon_interface_errors.interface_type%TYPE,
4519: p_userid IN pon_interface_errors.created_by%TYPE,
4520: p_spreadsheet IN VARCHAR2,
4521: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
4522: p_trans_view IN VARCHAR2,
4523: p_blanket IN VARCHAR2,
4525: p_po_start_date IN pon_auction_headers_all.po_start_date%TYPE,
4526: p_po_end_date IN pon_auction_headers_all.po_end_date%TYPE,
4527: p_auc_close_date IN pon_auction_headers_all.close_bidding_date%TYPE,
4528: p_suffix IN VARCHAR2,
4529: p_batch_id IN pon_interface_errors.batch_id%TYPE,
4530: p_request_id IN pon_interface_errors.request_id%TYPE
4531: ) IS
4532: BEGIN
4533:
4526: p_po_end_date IN pon_auction_headers_all.po_end_date%TYPE,
4527: p_auc_close_date IN pon_auction_headers_all.close_bidding_date%TYPE,
4528: p_suffix IN VARCHAR2,
4529: p_batch_id IN pon_interface_errors.batch_id%TYPE,
4530: p_request_id IN pon_interface_errors.request_id%TYPE
4531: ) IS
4532: BEGIN
4533:
4534: INSERT ALL
4534: INSERT ALL
4535:
4536: -- quantity must be positive or zero
4537: WHEN s_quantity < 0 THEN
4538: INTO pon_interface_errors
4539: (INTERFACE_TYPE,
4540: TABLE_NAME,
4541: COLUMN_NAME,
4542: BATCH_ID,
4591:
4592: -- bid_currency_unit_price must be positive
4593: -- Only applier if price_type is PRICE
4594: WHEN s_price_type = 'PRICE' AND s_bid_currency_unit_price < 0 THEN
4595: INTO pon_interface_errors
4596: (INTERFACE_TYPE,
4597: TABLE_NAME,
4598: COLUMN_NAME,
4599: BATCH_ID,
4649: -- bid_currency_unit_price precision must not exceed price precision
4650: -- Only applier if price_type is PRICE
4651: WHEN s_price_type = 'PRICE' AND validate_price_precision(
4652: s_bid_currency_unit_price, p_price_precision) = 'F' THEN
4653: INTO pon_interface_errors
4654: (INTERFACE_TYPE,
4655: TABLE_NAME,
4656: COLUMN_NAME,
4657: BATCH_ID,
4709: -- in untransformed view, we can use s_price (which is the
4710: -- transformed price in auction currency)
4711: -- NOTE: rate conversion is unneccessary since we only check sign
4712: WHEN p_trans_view = 'N' AND s_price < 0 THEN
4713: INTO pon_interface_errors
4714: (INTERFACE_TYPE,
4715: TABLE_NAME,
4716: BATCH_ID,
4717: INTERFACE_LINE_ID,
4763: s_entity_name)
4764:
4765: -- bid_currency_price must be positive
4766: WHEN p_trans_view = 'Y' AND s_bid_currency_price < 0 THEN
4767: INTO pon_interface_errors
4768: (INTERFACE_TYPE,
4769: TABLE_NAME,
4770: BATCH_ID,
4771: INTERFACE_LINE_ID,
4820:
4821: -- bid_currency_price precision must not exceed price precision
4822: WHEN validate_price_precision(
4823: s_bid_currency_price, p_price_precision) = 'F' THEN
4824: INTO pon_interface_errors
4825: (INTERFACE_TYPE,
4826: TABLE_NAME,
4827: BATCH_ID,
4828: INTERFACE_LINE_ID,
4876: -- price_discount must be 0 to 100
4877: -- Only applies if price_type is PRICE DISCOUNT
4878: WHEN s_price_type = 'PRICE DISCOUNT'
4879: AND (s_price_discount < 0 OR s_price_discount > 100) THEN
4880: INTO pon_interface_errors
4881: (INTERFACE_TYPE,
4882: TABLE_NAME,
4883: COLUMN_NAME,
4884: BATCH_ID,
4932: s_entity_name)
4933:
4934: -- effective_start_date must be past the current date
4935: WHEN s_effective_start_date < s_current_date THEN
4936: INTO pon_interface_errors
4937: (INTERFACE_TYPE,
4938: TABLE_NAME,
4939: COLUMN_NAME,
4940: BATCH_ID,
4987: s_worksheet_seq_num,
4988: s_entity_name)
4989: -- effective_start_date must be after po start date
4990: WHEN s_effective_start_date < p_po_start_date THEN
4991: INTO pon_interface_errors
4992: (INTERFACE_TYPE,
4993: TABLE_NAME,
4994: COLUMN_NAME,
4995: BATCH_ID,
5043: s_entity_name)
5044:
5045: -- effective_start_date must be before po end date
5046: WHEN s_effective_start_date > p_po_end_date THEN
5047: INTO pon_interface_errors
5048: (INTERFACE_TYPE,
5049: TABLE_NAME,
5050: COLUMN_NAME,
5051: BATCH_ID,
5099: s_entity_name)
5100:
5101: -- effective start date must be after auction close date
5102: WHEN s_effective_start_date < p_auc_close_date THEN
5103: INTO pon_interface_errors
5104: (INTERFACE_TYPE,
5105: TABLE_NAME,
5106: COLUMN_NAME,
5107: BATCH_ID,
5154: s_worksheet_seq_num,
5155: s_entity_name)
5156: -- effective_end_date must be after current date
5157: WHEN s_effective_end_date < s_current_date THEN
5158: INTO pon_interface_errors
5159: (INTERFACE_TYPE,
5160: TABLE_NAME,
5161: COLUMN_NAME,
5162: BATCH_ID,
5210: s_entity_name)
5211:
5212: -- effective_end_date must be after po start date
5213: WHEN s_effective_end_date < p_po_start_date THEN
5214: INTO pon_interface_errors
5215: (INTERFACE_TYPE,
5216: TABLE_NAME,
5217: COLUMN_NAME,
5218: BATCH_ID,
5266: s_entity_name)
5267:
5268: -- effective_end_date must be before po end date
5269: WHEN s_effective_end_date > p_po_end_date THEN
5270: INTO pon_interface_errors
5271: (INTERFACE_TYPE,
5272: TABLE_NAME,
5273: COLUMN_NAME,
5274: BATCH_ID,
5322: s_entity_name)
5323:
5324: -- effective_end_date must be after auction close date
5325: WHEN s_effective_end_date < p_auc_close_date THEN
5326: INTO pon_interface_errors
5327: (INTERFACE_TYPE,
5328: TABLE_NAME,
5329: COLUMN_NAME,
5330: BATCH_ID,
5383: AND s_ship_to_location_id IS null
5384: AND s_quantity IS null
5385: AND s_effective_start_date IS null
5386: AND s_effective_end_date IS null THEN
5387: INTO pon_interface_errors
5388: (INTERFACE_TYPE,
5389: TABLE_NAME,
5390: BATCH_ID,
5391: INTERFACE_LINE_ID,
5432: s_worksheet_seq_num,
5433: s_entity_name)
5434: -- effective_start_date must be before effective_end_date
5435: WHEN s_effective_start_date > s_effective_end_date THEN
5436: INTO pon_interface_errors
5437: (INTERFACE_TYPE,
5438: TABLE_NAME,
5439: COLUMN_NAME,
5440: BATCH_ID,
5488: s_entity_name)
5489:
5490: -- Either bid_currency_unit_price or price_discount must have a value
5491: WHEN s_bid_currency_unit_price IS null AND s_price_discount IS null THEN
5492: INTO pon_interface_errors
5493: (INTERFACE_TYPE,
5494: TABLE_NAME,
5495: COLUMN_NAME,
5496: BATCH_ID,
5542: -- CUMULATIVE price breaks cannot have start and end dates
5543: WHEN s_price_break_type = 'CUMULATIVE'
5544: AND (s_effective_start_date IS NOT null
5545: OR s_effective_end_date IS NOT null) THEN
5546: INTO pon_interface_errors
5547: (INTERFACE_TYPE,
5548: TABLE_NAME,
5549: BATCH_ID,
5550: INTERFACE_LINE_ID,
5658: PROCEDURE VALIDATE_QTY_BASED_PRICE_TIERS
5659: (
5660: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
5661: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
5662: p_interface_type IN pon_interface_errors.interface_type%TYPE,
5663: p_userid IN pon_interface_errors.created_by%TYPE,
5664: p_spreadsheet IN VARCHAR2,
5665: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
5666: p_batch_id IN pon_interface_errors.batch_id%TYPE,
5659: (
5660: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
5661: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
5662: p_interface_type IN pon_interface_errors.interface_type%TYPE,
5663: p_userid IN pon_interface_errors.created_by%TYPE,
5664: p_spreadsheet IN VARCHAR2,
5665: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
5666: p_batch_id IN pon_interface_errors.batch_id%TYPE,
5667: p_request_id IN pon_interface_errors.request_id%TYPE,
5662: p_interface_type IN pon_interface_errors.interface_type%TYPE,
5663: p_userid IN pon_interface_errors.created_by%TYPE,
5664: p_spreadsheet IN VARCHAR2,
5665: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
5666: p_batch_id IN pon_interface_errors.batch_id%TYPE,
5667: p_request_id IN pon_interface_errors.request_id%TYPE,
5668: p_contract_type IN pon_bid_headers.contract_type%TYPE
5669: ) IS
5670:
5663: p_userid IN pon_interface_errors.created_by%TYPE,
5664: p_spreadsheet IN VARCHAR2,
5665: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
5666: p_batch_id IN pon_interface_errors.batch_id%TYPE,
5667: p_request_id IN pon_interface_errors.request_id%TYPE,
5668: p_contract_type IN pon_bid_headers.contract_type%TYPE
5669: ) IS
5670:
5671: BEGIN
5690: OR
5691: s_min_quantity = g_null_int
5692: )
5693: THEN
5694: INTO pon_interface_errors
5695: (INTERFACE_TYPE,
5696: TABLE_NAME,
5697: COLUMN_NAME,
5698: BATCH_ID,
5755: OR
5756: s_max_quantity = g_null_int
5757: )
5758: THEN
5759: INTO pon_interface_errors
5760: (INTERFACE_TYPE,
5761: TABLE_NAME,
5762: COLUMN_NAME,
5763: BATCH_ID,
5820: s_min_quantity <= 0 AND
5821: s_min_quantity <> g_null_int)
5822: )
5823: THEN
5824: INTO pon_interface_errors
5825: (INTERFACE_TYPE,
5826: TABLE_NAME,
5827: COLUMN_NAME,
5828: BATCH_ID,
5885: s_max_quantity <= 0 AND
5886: s_max_quantity <> g_null_int)
5887: )
5888: THEN
5889: INTO pon_interface_errors
5890: (INTERFACE_TYPE,
5891: TABLE_NAME,
5892: COLUMN_NAME,
5893: BATCH_ID,
5949: (
5950: s_min_quantity > s_max_quantity
5951: )
5952: THEN
5953: INTO pon_interface_errors
5954: (INTERFACE_TYPE,
5955: TABLE_NAME,
5956: COLUMN_NAME,
5957: BATCH_ID,
6019: )
6020: )
6021:
6022: THEN
6023: INTO pon_interface_errors
6024: (INTERFACE_TYPE,
6025: TABLE_NAME,
6026: COLUMN_NAME,
6027: BATCH_ID,
6094: )
6095: )
6096:
6097: THEN
6098: INTO pon_interface_errors
6099: (INTERFACE_TYPE,
6100: TABLE_NAME,
6101: COLUMN_NAME,
6102: BATCH_ID,
6153: WHEN (s_bid_currency_unit_price IS NULL
6154: OR
6155: s_bid_currency_unit_price = g_null_int) THEN
6156:
6157: INTO pon_interface_errors
6158: (INTERFACE_TYPE,
6159: TABLE_NAME,
6160: COLUMN_NAME,
6161: BATCH_ID,
6216: (s_bid_currency_unit_price IS NOT NULL AND
6217: s_bid_currency_unit_price <> g_null_int AND
6218: s_bid_currency_unit_price <= 0)
6219: THEN
6220: INTO pon_interface_errors
6221: (INTERFACE_TYPE,
6222: TABLE_NAME,
6223: COLUMN_NAME,
6224: BATCH_ID,
6275:
6276: -- bid_currency_unit_price precision must not exceed price precision
6277:
6278: WHEN validate_price_precision(s_bid_currency_unit_price, p_price_precision) = 'F' THEN
6279: INTO pon_interface_errors
6280: (INTERFACE_TYPE,
6281: TABLE_NAME,
6282: COLUMN_NAME,
6283: BATCH_ID,
6341: and s_max_ship_qty = s_max_quantity
6342: and s_max_ship_qty <> s_bid_quantity
6343: )
6344: THEN
6345: INTO pon_interface_errors
6346: (INTERFACE_TYPE,
6347: TABLE_NAME,
6348: COLUMN_NAME,
6349: BATCH_ID,
6404: WHEN (p_contract_type = 'STANDARD'
6405: and s_max_ship_qty = s_bid_quantity
6406: and s_max_ship_qty = s_max_quantity
6407: and s_bid_price <> s_shipments_price) THEN
6408: INTO pon_interface_errors
6409: (INTERFACE_TYPE,
6410: TABLE_NAME,
6411: COLUMN_NAME,
6412: BATCH_ID,
6509: message => 'Performing overlapping tiers validation for online bidding flow.');
6510: END IF; --}
6511:
6512:
6513: Insert INTO pon_interface_errors
6514: (INTERFACE_TYPE,
6515: TABLE_NAME,
6516: BATCH_ID,
6517: INTERFACE_LINE_ID,
6582: PROCEDURE validate_price_differentials
6583: (
6584: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
6585: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
6586: p_interface_type IN pon_interface_errors.interface_type%TYPE,
6587: p_userid IN pon_interface_errors.created_by%TYPE,
6588: p_spreadsheet IN VARCHAR2,
6589: p_suffix IN VARCHAR2,
6590: p_batch_id IN pon_interface_errors.batch_id%TYPE,
6583: (
6584: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
6585: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
6586: p_interface_type IN pon_interface_errors.interface_type%TYPE,
6587: p_userid IN pon_interface_errors.created_by%TYPE,
6588: p_spreadsheet IN VARCHAR2,
6589: p_suffix IN VARCHAR2,
6590: p_batch_id IN pon_interface_errors.batch_id%TYPE,
6591: p_request_id IN pon_interface_errors.request_id%TYPE
6586: p_interface_type IN pon_interface_errors.interface_type%TYPE,
6587: p_userid IN pon_interface_errors.created_by%TYPE,
6588: p_spreadsheet IN VARCHAR2,
6589: p_suffix IN VARCHAR2,
6590: p_batch_id IN pon_interface_errors.batch_id%TYPE,
6591: p_request_id IN pon_interface_errors.request_id%TYPE
6592: ) IS
6593: BEGIN
6594:
6587: p_userid IN pon_interface_errors.created_by%TYPE,
6588: p_spreadsheet IN VARCHAR2,
6589: p_suffix IN VARCHAR2,
6590: p_batch_id IN pon_interface_errors.batch_id%TYPE,
6591: p_request_id IN pon_interface_errors.request_id%TYPE
6592: ) IS
6593: BEGIN
6594:
6595: INSERT FIRST
6597: -- Line price differential validation:
6598: -- multiplier should be entered for REQUIRED price differentials
6599: WHEN s_shipment_number = -1 AND s_multiplier IS null
6600: AND s_differential_response_type = 'REQUIRED' THEN
6601: INTO pon_interface_errors
6602: (INTERFACE_TYPE,
6603: COLUMN_NAME,
6604: TABLE_NAME,
6605: BATCH_ID,
6659: -- Line price differential validation:
6660: -- multiplier should not be entered for DISPLAY_ONLY price differentials
6661: WHEN s_shipment_number = -1 AND s_multiplier IS NOT null
6662: AND s_differential_response_type = 'DISPLAY_ONLY' THEN
6663: INTO pon_interface_errors
6664: (INTERFACE_TYPE,
6665: COLUMN_NAME,
6666: TABLE_NAME,
6667: BATCH_ID,
6722:
6723: -- Line price differential validation:
6724: -- multiplier should be greater than the target multiplier
6725: WHEN s_shipment_number = -1 AND s_multiplier < s_target_multiplier THEN
6726: INTO pon_interface_errors
6727: (INTERFACE_TYPE,
6728: COLUMN_NAME,
6729: TABLE_NAME,
6730: BATCH_ID,
6788: -- multiplier should be entered for REQUIRED price differentials
6789: WHEN ( p_spreadsheet = g_online_mode or p_spreadsheet = g_xml_upload_mode )
6790: and s_shipment_number <> -1 AND s_multiplier IS null
6791: AND s_differential_response_type = 'REQUIRED' THEN
6792: INTO pon_interface_errors
6793: (INTERFACE_TYPE,
6794: COLUMN_NAME,
6795: TABLE_NAME,
6796: BATCH_ID,
6853: -- multiplier should not be entered for DISPLAY_ONLY price differentials
6854: WHEN ( p_spreadsheet = g_online_mode or p_spreadsheet = g_xml_upload_mode )
6855: and s_shipment_number <> -1 AND s_multiplier IS NOT null
6856: AND s_differential_response_type = 'DISPLAY_ONLY' THEN
6857: INTO pon_interface_errors
6858: (INTERFACE_TYPE,
6859: COLUMN_NAME,
6860: TABLE_NAME,
6861: BATCH_ID,
6919: -- multiplier should be greater than target multiplier
6920: WHEN ( p_spreadsheet = g_online_mode or p_spreadsheet = g_xml_upload_mode )
6921: and s_shipment_number <> -1
6922: AND s_multiplier < s_target_multiplier THEN
6923: INTO pon_interface_errors
6924: (INTERFACE_TYPE,
6925: COLUMN_NAME,
6926: TABLE_NAME,
6927: BATCH_ID,
7034: PROCEDURE validate_payments
7035: (
7036: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7037: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7038: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7039: p_userid IN pon_interface_errors.created_by%TYPE,
7040: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
7041: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7042: p_request_id IN pon_interface_errors.request_id%TYPE
7035: (
7036: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7037: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7038: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7039: p_userid IN pon_interface_errors.created_by%TYPE,
7040: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
7041: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7042: p_request_id IN pon_interface_errors.request_id%TYPE
7043: ) IS
7037: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7038: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7039: p_userid IN pon_interface_errors.created_by%TYPE,
7040: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
7041: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7042: p_request_id IN pon_interface_errors.request_id%TYPE
7043: ) IS
7044: l_loginid NUMBER;
7045: l_exp_date DATE;
7038: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7039: p_userid IN pon_interface_errors.created_by%TYPE,
7040: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
7041: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7042: p_request_id IN pon_interface_errors.request_id%TYPE
7043: ) IS
7044: l_loginid NUMBER;
7045: l_exp_date DATE;
7046: l_module CONSTANT VARCHAR2(32) := 'VALIDATE_PAYMENTS';
7056: */
7057:
7058: BEGIN
7059: --To check for duplicate pay item number
7060: INSERT INTO pon_interface_errors
7061: (INTERFACE_TYPE,
7062: COLUMN_NAME,
7063: TABLE_NAME,
7064: BATCH_ID,
7118:
7119:
7120: INSERT ALL
7121: WHEN payment_display_number < 1 OR payment_display_number<> ROUND(payment_display_number) THEN
7122: INTO pon_interface_errors
7123: (
7124: request_id, entity_attr_name, error_message_name, -- 1
7125: error_value_datatype, error_value_number, error_value_date, -- 2
7126: token1_name, token1_value, token2_name, -- 3
7141: l_exp_date, p_userid, SYSDATE, -- 7
7142: p_userid, SYSDATE, l_loginid -- 8
7143: )
7144: WHEN payment_type_code = 'RATE' AND quantity < 0 THEN
7145: INTO pon_interface_errors
7146: (
7147: request_id, entity_attr_name, error_message_name, -- 1
7148: error_value_datatype, error_value_number, error_value_date, -- 2
7149: token1_name, token1_value, token2_name, -- 3
7164: l_exp_date, p_userid, SYSDATE, -- 7
7165: p_userid, SYSDATE, l_loginid -- 8
7166: )
7167: WHEN bid_currency_price IS NOT NULL AND bid_currency_price < 0 THEN
7168: INTO pon_interface_errors
7169: (
7170: request_id, entity_attr_name, error_message_name, -- 1
7171: error_value_datatype, error_value_number, error_value_date, -- 2
7172: token1_name, token1_value, token2_name, -- 3
7187: l_exp_date, p_userid, SYSDATE, -- 7
7188: p_userid, SYSDATE, l_loginid -- 8
7189: )
7190: WHEN payment_display_number IS NULL THEN
7191: INTO pon_interface_errors
7192: (
7193: request_id, entity_attr_name, error_message_name, -- 1
7194: error_value_datatype, error_value, error_value_date, -- 2
7195: token1_name, token1_value, token2_name, -- 3
7210: l_exp_date, p_userid, SYSDATE, -- 7
7211: p_userid, SYSDATE, l_loginid -- 8
7212: )
7213: WHEN payment_type_code IS NULL THEN
7214: INTO pon_interface_errors
7215: (
7216: request_id, entity_attr_name, error_message_name, -- 1
7217: error_value_datatype, error_value_number, error_value, -- 2
7218: token1_name, token1_value, token2_name, -- 3
7233: l_exp_date, p_userid, SYSDATE, -- 7
7234: p_userid, SYSDATE, l_loginid -- 8
7235: )
7236: WHEN payment_description IS NULL THEN
7237: INTO pon_interface_errors
7238: (
7239: request_id, entity_attr_name, error_message_name, -- 1
7240: error_value_datatype, error_value_number, error_value, -- 2
7241: token1_name, token1_value, token2_name, -- 3
7256: l_exp_date, p_userid, SYSDATE, -- 7
7257: p_userid, SYSDATE, l_loginid -- 8
7258: )
7259: WHEN payment_type_code = 'RATE' AND quantity IS NULL THEN
7260: INTO pon_interface_errors
7261: (
7262: request_id, entity_attr_name, error_message_name, -- 1
7263: error_value_datatype, error_value, error_value_date, -- 2
7264: token1_name, token1_value, token2_name, -- 3
7279: l_exp_date, p_userid, SYSDATE, -- 7
7280: p_userid, SYSDATE, l_loginid -- 8
7281: )
7282: WHEN payment_type_code = 'RATE' AND uom_code IS NULL THEN
7283: INTO pon_interface_errors
7284: (
7285: request_id, entity_attr_name, error_message_name, -- 1
7286: error_value_datatype, error_value, error_value_date, -- 2
7287: token1_name, token1_value, token2_name, -- 3
7302: l_exp_date, p_userid, SYSDATE, -- 7
7303: p_userid, SYSDATE, l_loginid -- 8
7304: )
7305: WHEN bid_currency_price IS NULL THEN
7306: INTO pon_interface_errors
7307: (
7308: request_id, entity_attr_name, error_message_name, -- 1
7309: error_value_datatype, error_value, error_value_date, -- 2
7310: token1_name, token1_value, token2_name, -- 3
7325: l_exp_date, p_userid, SYSDATE, -- 7
7326: p_userid, SYSDATE, l_loginid -- 8
7327: )
7328: WHEN promised_date IS NOT NULL AND promised_date <= close_bidding_date THEN
7329: INTO pon_interface_errors
7330: (
7331: request_id, entity_attr_name, error_message_name, -- 1
7332: error_value_datatype, error_value_number, error_value_date, -- 2
7333: token1_name, token1_value, token2_name, -- 3
7349: p_userid, SYSDATE, l_loginid -- 8
7350: )
7351: WHEN bid_currency_price IS NOT NULL
7352: AND validate_price_precision(bid_currency_price, p_price_precision) = 'F' THEN
7353: INTO pon_interface_errors
7354: (
7355: request_id, entity_attr_name, error_message_name, -- 1
7356: error_value_datatype, error_value_number, error_value_date, -- 2
7357: token1_name, token1_value, token2_name, -- 3
7422: PROCEDURE validate_emd_status
7423: (
7424: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7425: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7426: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7427: p_userid IN pon_interface_errors.created_by%TYPE,
7428: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
7429: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7430: p_request_id IN pon_interface_errors.request_id%TYPE
7423: (
7424: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7425: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7426: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7427: p_userid IN pon_interface_errors.created_by%TYPE,
7428: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
7429: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7430: p_request_id IN pon_interface_errors.request_id%TYPE
7431: )
7425: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7426: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7427: p_userid IN pon_interface_errors.created_by%TYPE,
7428: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
7429: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7430: p_request_id IN pon_interface_errors.request_id%TYPE
7431: )
7432: IS
7433: l_emd_status varchar2(20) :=null;
7426: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7427: p_userid IN pon_interface_errors.created_by%TYPE,
7428: p_price_precision IN pon_bid_headers.number_price_decimals%TYPE,
7429: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7430: p_request_id IN pon_interface_errors.request_id%TYPE
7431: )
7432: IS
7433: l_emd_status varchar2(20) :=null;
7434: l_emd_enable_flag varchar2(2) :='N'; -- Added by Chaoqun on Jan-8-2009
7470: raise l_nopaid_exception;
7471: end if;
7472: END IF;--Added by Chaoqun
7473: exception when no_data_found or l_nopaid_exception then
7474: INSERT INTO pon_interface_errors
7475: (INTERFACE_TYPE,
7476: COLUMN_NAME,
7477: TABLE_NAME,
7478: BATCH_ID,
7510: PROCEDURE perform_all_validations
7511: (
7512: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7513: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7514: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7515: p_userid IN pon_interface_errors.created_by%TYPE,
7516: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7517: p_request_id IN pon_interface_errors.request_id%TYPE,
7518: p_spreadsheet IN VARCHAR2,
7511: (
7512: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7513: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7514: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7515: p_userid IN pon_interface_errors.created_by%TYPE,
7516: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7517: p_request_id IN pon_interface_errors.request_id%TYPE,
7518: p_spreadsheet IN VARCHAR2,
7519: x_return_status OUT NOCOPY NUMBER,
7512: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7513: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7514: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7515: p_userid IN pon_interface_errors.created_by%TYPE,
7516: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7517: p_request_id IN pon_interface_errors.request_id%TYPE,
7518: p_spreadsheet IN VARCHAR2,
7519: x_return_status OUT NOCOPY NUMBER,
7520: x_return_code OUT NOCOPY VARCHAR2
7513: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7514: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7515: p_userid IN pon_interface_errors.created_by%TYPE,
7516: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7517: p_request_id IN pon_interface_errors.request_id%TYPE,
7518: p_spreadsheet IN VARCHAR2,
7519: x_return_status OUT NOCOPY NUMBER,
7520: x_return_code OUT NOCOPY VARCHAR2
7521: ) IS
7696:
7697: -- Check if there were any errors
7698: SELECT decode(count(auction_header_id), 0, 'N', 'Y')
7699: INTO l_has_errors
7700: FROM pon_interface_errors
7701: WHERE (batch_id = p_batch_id OR request_id = p_request_id)
7702: AND rownum = 1;
7703:
7704: IF (l_has_errors = 'Y') THEN
7946:
7947: -- Check if any errors were present
7948: SELECT decode(count(auction_header_id), 0, 'N', 'Y')
7949: INTO l_has_errors
7950: FROM pon_interface_errors
7951: WHERE (batch_id = p_batch_id OR request_id = p_request_id)
7952: AND rownum = 1;
7953:
7954: IF (l_has_errors = 'Y') THEN
7964: PROCEDURE validate_bid
7965: (
7966: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7967: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7968: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7969: p_userid IN pon_interface_errors.created_by%TYPE,
7970: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7971: p_request_id IN pon_interface_errors.request_id%TYPE,
7972: x_return_status OUT NOCOPY NUMBER,
7965: (
7966: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7967: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7968: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7969: p_userid IN pon_interface_errors.created_by%TYPE,
7970: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7971: p_request_id IN pon_interface_errors.request_id%TYPE,
7972: x_return_status OUT NOCOPY NUMBER,
7973: x_return_code OUT NOCOPY VARCHAR2
7966: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7967: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7968: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7969: p_userid IN pon_interface_errors.created_by%TYPE,
7970: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7971: p_request_id IN pon_interface_errors.request_id%TYPE,
7972: x_return_status OUT NOCOPY NUMBER,
7973: x_return_code OUT NOCOPY VARCHAR2
7974: ) IS
7967: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7968: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7969: p_userid IN pon_interface_errors.created_by%TYPE,
7970: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7971: p_request_id IN pon_interface_errors.request_id%TYPE,
7972: x_return_status OUT NOCOPY NUMBER,
7973: x_return_code OUT NOCOPY VARCHAR2
7974: ) IS
7975: BEGIN
7982: PROCEDURE validate_spreadsheet_upload
7983: (
7984: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7985: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7986: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7987: p_spreadsheet_type IN VARCHAR2,
7988: p_userid IN pon_interface_errors.created_by%TYPE,
7989: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7990: p_request_id IN pon_interface_errors.request_id%TYPE,
7984: p_auc_header_id IN pon_bid_item_prices.auction_header_id%TYPE,
7985: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7986: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7987: p_spreadsheet_type IN VARCHAR2,
7988: p_userid IN pon_interface_errors.created_by%TYPE,
7989: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7990: p_request_id IN pon_interface_errors.request_id%TYPE,
7991: x_return_status OUT NOCOPY NUMBER,
7992: x_return_code OUT NOCOPY VARCHAR2
7985: p_bid_number IN pon_bid_item_prices.bid_number%TYPE,
7986: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7987: p_spreadsheet_type IN VARCHAR2,
7988: p_userid IN pon_interface_errors.created_by%TYPE,
7989: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7990: p_request_id IN pon_interface_errors.request_id%TYPE,
7991: x_return_status OUT NOCOPY NUMBER,
7992: x_return_code OUT NOCOPY VARCHAR2
7993: ) IS
7986: p_interface_type IN pon_interface_errors.interface_type%TYPE,
7987: p_spreadsheet_type IN VARCHAR2,
7988: p_userid IN pon_interface_errors.created_by%TYPE,
7989: p_batch_id IN pon_interface_errors.batch_id%TYPE,
7990: p_request_id IN pon_interface_errors.request_id%TYPE,
7991: x_return_status OUT NOCOPY NUMBER,
7992: x_return_code OUT NOCOPY VARCHAR2
7993: ) IS
7994: BEGIN