[Home] [Help]
203: || ' and rownum < 2';
204: FETCH curvar INTO val;
205: CONTINUE WHEN curvar%NOTFOUND ;
206: IF (val IS NULL ) THEN
207: INSERT INTO PON_INTERFACE_ERRORS
208: (
209: INTERFACE_TYPE,
210: ERROR_MESSAGE_NAME,
211: column_name,
423: l_sequence_number_attr PON_AUCTION_ATTRIBUTES.SEQUENCE_NUMBER%TYPE;
424: l_ATTR_GROUP_SEQ_NUMBER PON_AUCTION_ATTRIBUTES.ATTR_GROUP_SEQ_NUMBER%TYPE;
425: l_ATTR_DISP_SEQ_NUMBER PON_AUCTION_ATTRIBUTES.ATTR_DISP_SEQ_NUMBER%TYPE;
426: l_LAST_AMENDMENT_UPDATE PON_AUCTION_ATTRIBUTES.LAST_AMENDMENT_UPDATE%TYPE;
427: l_TABLE_NAME pon_interface_errors.TABLE_NAME%TYPE :='PON_AUC_ATTRIBUTES_INTERFACE';
428: l_sequence_number_scr pon_attribute_scores_interface.sequence_number%TYPE;
429: l_status VARCHAR2(1) :='Y';
430: l_count_acc NUMBER;
431: l_sum_wt_err VARCHAR(10);
440: SELECT 'N' status
441: INTO l_status
442: FROM dual
443: WHERE EXISTS
444: (SELECT * FROM pon_interface_errors WHERE BATCH_ID=batchId
445: );
446: EXCEPTION
447: WHEN No_Data_Found THEN
448: l_status:=NULL;
447: WHEN No_Data_Found THEN
448: l_status:=NULL;
449: END;
450: IF(l_status='N') THEN
451: print_log('An error occured while validating the requirements. please check the pon_interface_errors table for error information');
452: x_return_status := FND_API.G_RET_STS_ERROR;
453: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_HDR_REQ_ERR');
454: FND_MESSAGE.SET_TOKEN('BATCH_ID',batchId);
455: FND_MSG_PUB.ADD;
631: acceptance_values_insert(c_interface_attr_rec,l_sequence_number_attr ,l_status);
632:
633: print_log('l_status is '|| l_status);
634: IF (l_status='N')THEN
635: print_log('An error occured while validating the acceptable values. please check the pon_interface_errors table for error information');
636: x_return_status := FND_API.G_RET_STS_ERROR;
637: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_HDR_REQ_ERR');
638: FND_MESSAGE.SET_TOKEN('BATCH_ID',batchId);
639: FND_MSG_PUB.ADD;
661: c_interface_attr_rec.sequence_number:=l_sequence_number_attr;
662: print_log('BEGIN Validating the requirement before inserting');
663: pon_auc_interface_table_pkg.validate_requirement(NULL,batchId,-1,c_interface_attr_rec,l_status);
664: IF (l_status='N')THEN
665: print_log('An error occured while validating the requirements. please check the pon_interface_errors table for error information');
666: x_return_status := FND_API.G_RET_STS_ERROR;
667: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_HDR_REQ_ERR');
668: FND_MESSAGE.SET_TOKEN('BATCH_ID',batchId);
669: FND_MSG_PUB.ADD;
1626: SELECT 'E' status
1627: INTO x_return_status
1628: FROM dual
1629: WHERE EXISTS
1630: (SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
1631: );
1632: EXCEPTION
1633: WHEN No_Data_Found THEN
1634: x_return_status:=FND_API.G_RET_STS_SUCCESS;
1634: x_return_status:=FND_API.G_RET_STS_SUCCESS;
1635: END;
1636:
1637: IF(x_return_status=FND_API.G_RET_STS_ERROR) THEN
1638: print_Log('Error in PON_BID_PARTIES_INTERFACE table. Please check pon_interface_errors for more details' );
1639: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_SUPPLIER_VAL_ERR');
1640: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_batch_Id);
1641: FND_MSG_PUB.ADD;
1642: RAISE FND_API.G_EXC_ERROR;
1805: l_cp_login_id := fnd_global.login_id;
1806:
1807: INSERT ALL
1808: WHEN (sel_vendor_name IS NULL AND sel_vendor_id IS NULL )
1809: THEN INTO PON_INTERFACE_ERRORS
1810: (
1811: INTERFACE_TYPE ,
1812: ERROR_MESSAGE_NAME,
1813: COLUMN_NAME,
1832: l_cp_login_id)
1833: WHEN (sel_vendor_name IS NOT NULL AND sel_vendor_id IS NOT NULL
1834: AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIERS
1835: WHERE VENDOR_NAME=sel_vendor_name AND VENDOR_ID=sel_vendor_id))
1836: THEN INTO PON_INTERFACE_ERRORS
1837: (
1838: INTERFACE_TYPE ,
1839: ERROR_MESSAGE_NAME,
1840: TOKEN1_NAME,
1861: l_cp_login_id)
1862: WHEN (sel_vendor_id IS NOT NULL
1863: AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIERS
1864: WHERE VENDOR_ID=sel_vendor_id))
1865: THEN INTO PON_INTERFACE_ERRORS
1866: (
1867: INTERFACE_TYPE ,
1868: ERROR_MESSAGE_NAME,
1869: TOKEN1_NAME,
1890: l_cp_login_id)
1891: WHEN (sel_vendor_NAME IS NOT NULL
1892: AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIERS
1893: WHERE VENDOR_NAME=sel_vendor_NAME))
1894: THEN INTO PON_INTERFACE_ERRORS
1895: (
1896: INTERFACE_TYPE ,
1897: ERROR_MESSAGE_NAME,
1898: TOKEN1_NAME,
1920:
1921: WHEN (sel_vendor_site_code IS NOT NULL AND sel_vendor_site_id IS NOT NULL
1922: AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIER_SITES_ALL
1923: WHERE vendor_site_code=sel_vendor_site_code AND vendor_site_id=sel_vendor_site_id))
1924: THEN INTO PON_INTERFACE_ERRORS
1925: (
1926: INTERFACE_TYPE ,
1927: ERROR_MESSAGE_NAME,
1928: TOKEN1_NAME,
1949: l_cp_login_id)
1950: WHEN (sel_vendor_site_id IS NOT NULL
1951: AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIER_SITES_ALL
1952: WHERE vendor_site_id=sel_vendor_site_id))
1953: THEN INTO PON_INTERFACE_ERRORS
1954: (
1955: INTERFACE_TYPE ,
1956: ERROR_MESSAGE_NAME,
1957: TOKEN1_NAME,
1978: l_cp_login_id)
1979: WHEN (sel_vendor_site_code IS NOT NULL
1980: AND NOT EXISTS(SELECT 1 FROM AP_SUPPLIER_SITES_ALL
1981: WHERE vendor_site_code=sel_vendor_site_code))
1982: THEN INTO PON_INTERFACE_ERRORS
1983: (
1984: INTERFACE_TYPE ,
1985: ERROR_MESSAGE_NAME,
1986: TOKEN1_NAME,
2008:
2009: WHEN (sel_tpc_id IS NOT NULL
2010: AND NOT EXISTS(SELECT 1 FROM hz_parties
2011: WHERE party_id=sel_tpc_id))
2012: THEN INTO PON_INTERFACE_ERRORS
2013: (
2014: INTERFACE_TYPE ,
2015: ERROR_MESSAGE_NAME,
2016: TOKEN1_NAME,
2035: l_cp_user_id,
2036: SYSDATE,
2037: l_cp_login_id)
2038: WHEN (sel_tpc_id IS NULL AND sel_add_mail IS null)
2039: THEN INTO PON_INTERFACE_ERRORS
2040: (
2041: INTERFACE_TYPE ,
2042: ERROR_MESSAGE_NAME,
2043: COLUMN_NAME,
2142:
2143:
2144: EXCEPTION
2145: WHEN OTHERS THEN
2146: print_log('An error occured while validating the line. please check the pon_interface_errors table for error information');
2147: print_log('sqlcode ' || SQLCODE || 'sqlerror ' || SQLERRM);
2148: x_return_status := FND_API.G_RET_STS_ERROR;
2149: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_LINES_VAL_ERR');
2150: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_batch_id);
2316: SELECT 'N' status
2317: INTO l_status
2318: FROM dual
2319: WHERE EXISTS
2320: (SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
2321: );
2322: EXCEPTION
2323: WHEN No_Data_Found THEN
2324: l_status:=NULL;
2323: WHEN No_Data_Found THEN
2324: l_status:=NULL;
2325: END;
2326: IF(l_status='N') THEN
2327: print_log('An error occured while validating the line. please check the pon_interface_errors table for error information');
2328: x_return_status := FND_API.G_RET_STS_ERROR;
2329: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_LINES_VAL_ERR');
2330: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_batch_id);
2331: FND_MSG_PUB.ADD;
2478: SELECT 'N' status
2479: INTO l_status
2480: FROM dual
2481: WHERE EXISTS
2482: (SELECT * FROM pon_interface_errors WHERE BATCH_ID=p_batch_Id
2483: );
2484: EXCEPTION
2485: WHEN No_Data_Found THEN
2486: l_status:=NULL;
2486: l_status:=NULL;
2487: END;
2488: IF(l_status='N') THEN
2489: print_log('Errors found in validation of scores for line level attributes.' ||
2490: ' please check the pon_interface_errors table for error information');
2491: x_return_status := FND_API.G_RET_STS_ERROR;
2492: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_ATTRSCORES_VAL_ERR');
2493: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_batch_id);
2494: FND_MSG_PUB.ADD;
2573: SELECT 'N' status
2574: INTO l_status
2575: FROM dual
2576: WHERE EXISTS
2577: (SELECT * FROM pon_interface_errors WHERE BATCH_ID=p_batch_Id
2578: );
2579: EXCEPTION
2580: WHEN No_Data_Found THEN
2581: l_status:=NULL;
2580: WHEN No_Data_Found THEN
2581: l_status:=NULL;
2582: END;
2583: IF(l_status='N') THEN
2584: print_log('An error occured while validating the price breaks. please check the pon_interface_errors table for error information');
2585: x_return_status := FND_API.G_RET_STS_ERROR;
2586: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_PB_VAL_ERR');
2587: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_batch_id);
2588: FND_MSG_PUB.ADD;
2832: -- PRICE BREAK EFFECTIVE START DATE SHOULD BE BEFORE EFFECTIVE END DATE
2833: WHEN ( sel_effective_start_date IS NOT NULL
2834: AND sel_effective_end_date IS NOT NULL
2835: AND sel_effective_end_date < sel_effective_start_date ) THEN
2836: INTO PON_INTERFACE_ERRORS
2837: (
2838: INTERFACE_TYPE ,
2839: ERROR_MESSAGE_NAME,
2840: REQUEST_ID ,
2884: AND NVL(L.SHIP_TO_SITE_FLAG,'N') = 'Y'
2885: AND L.LOCATION_ID = sel_ship_to_location_id
2886: AND NVL (L.INVENTORY_ORGANIZATION_ID, sel_ship_to_organization_id) = sel_ship_to_organization_id
2887: ) ) THEN
2888: INTO PON_INTERFACE_ERRORS
2889: (
2890: INTERFACE_TYPE ,
2891: ERROR_MESSAGE_NAME,
2892: REQUEST_ID ,
2929: AND sel_ship_to_location_id IS NULL
2930: AND sel_effective_start_date IS NULL
2931: AND sel_effective_end_date IS NULL
2932: AND sel_quantity IS NULL ) THEN
2933: INTO PON_INTERFACE_ERRORS
2934: (
2935: INTERFACE_TYPE ,
2936: ERROR_MESSAGE_NAME,
2937: REQUEST_ID ,
2971: -- quantity should not be empty or negative
2972: WHEN ( sel_quantity IS NOT NULL
2973: AND sel_quantity < 0
2974: AND sel_quantity <> g_null_int ) THEN
2975: INTO PON_INTERFACE_ERRORS
2976: (
2977: INTERFACE_TYPE ,
2978: ERROR_MESSAGE_NAME,
2979: REQUEST_ID ,
3011: p_login_id -- LAST_UPDATE_LOGIN
3012: )
3013: -- the price break price should be positive
3014: WHEN ( sel_price < 0 ) THEN
3015: INTO PON_INTERFACE_ERRORS
3016: (
3017: INTERFACE_TYPE ,
3018: ERROR_MESSAGE_NAME,
3019: REQUEST_ID ,
3052: )
3053: -- EFFECTIVE START DATE AFTER SYSDATE OR CLOSE DATE
3054: WHEN ( sel_effective_start_date IS NOT NULL
3055: AND sel_effective_start_date <= NVL (p_close_bidding_date, SYSDATE) ) THEN
3056: INTO PON_INTERFACE_ERRORS
3057: (
3058: INTERFACE_TYPE ,
3059: ERROR_MESSAGE_NAME,
3060: REQUEST_ID ,
3093: )
3094: -- EFFECTIVE END DATE AFTER SYSDATE OR CLOSE DATE
3095: WHEN ( sel_effective_end_date IS NOT NULL
3096: AND sel_effective_end_date <= NVL (p_close_bidding_date, SYSDATE) ) THEN
3097: INTO PON_INTERFACE_ERRORS
3098: (
3099: INTERFACE_TYPE ,
3100: ERROR_MESSAGE_NAME,
3101: REQUEST_ID ,
3141: AND PPD.AUCTION_HEADER_ID = p_auction_header_id
3142: AND PPD.auction_LINE_NUMBER = sel_line_number
3143: AND PPD.auction_SHIPMENT_NUMBER = sel_shipment_number
3144: ) ) THEN
3145: INTO PON_INTERFACE_ERRORS
3146: (
3147: INTERFACE_TYPE ,
3148: ERROR_MESSAGE_NAME,
3149: REQUEST_ID ,
3182: )
3183: WHEN ( p_po_start_date IS NOT NULL
3184: AND sel_effective_start_date IS NOT NULL
3185: AND sel_effective_start_date < p_po_start_date ) THEN
3186: INTO PON_INTERFACE_ERRORS
3187: (
3188: INTERFACE_TYPE ,
3189: ERROR_MESSAGE_NAME,
3190: REQUEST_ID ,
3224: -- EFFECTIVE END DATE SHOULD BE AFTER PO START DATE
3225: WHEN ( p_po_start_date IS NOT NULL
3226: AND sel_effective_end_date IS NOT NULL
3227: AND sel_effective_end_date < p_po_start_date ) THEN
3228: INTO PON_INTERFACE_ERRORS
3229: (
3230: INTERFACE_TYPE ,
3231: ERROR_MESSAGE_NAME,
3232: REQUEST_ID ,
3266: -- the effective start date should be before po end date if both are entered
3267: WHEN ( p_po_end_date IS NOT NULL
3268: AND sel_effective_start_date IS NOT NULL
3269: AND sel_effective_start_date > p_po_end_date ) THEN
3270: INTO PON_INTERFACE_ERRORS
3271: (
3272: INTERFACE_TYPE ,
3273: ERROR_MESSAGE_NAME,
3274: REQUEST_ID ,
3308: -- effective end date should be before the po end date
3309: WHEN ( p_po_end_date IS NOT NULL
3310: AND sel_effective_end_date IS NOT NULL
3311: AND sel_effective_end_date > p_po_end_date ) THEN
3312: INTO PON_INTERFACE_ERRORS
3313: (
3314: INTERFACE_TYPE ,
3315: ERROR_MESSAGE_NAME,
3316: REQUEST_ID ,
3369: AND diff.price_type_name IS NOT null;
3370:
3371: INSERT ALL
3372: WHEN (sel_seq_num IS NULL )
3373: THEN INTO PON_INTERFACE_ERRORS
3374: (
3375: INTERFACE_TYPE ,
3376: ERROR_MESSAGE_NAME,
3377: COLUMN_NAME,
3405: p_login_id -- LAST_UPDATE_LOGIN
3406: )
3407: WHEN (sel_price_type IS NULL
3408: OR NOT EXISTS (SELECT 1 FROM po_price_diff_lookups_v WHERE price_differential_type = sel_price_type))
3409: THEN INTO PON_INTERFACE_ERRORS
3410: (
3411: INTERFACE_TYPE ,
3412: ERROR_MESSAGE_NAME,
3413: REQUEST_ID ,
3438: sysdate , -- LAST_UPDATE_DATE
3439: p_login_id -- LAST_UPDATE_LOGIN
3440: )
3441: WHEN (sel_multiplier IS NULL )
3442: THEN INTO PON_INTERFACE_ERRORS
3443: (
3444: INTERFACE_TYPE ,
3445: ERROR_MESSAGE_NAME,
3446: COLUMN_NAME,
3476: WHEN (sel_line_num IS NULL
3477: or NOT EXISTS(SELECT 1 FROM pon_auction_item_prices_all paip
3478: WHERE paip.auction_header_id = p_auction_header_id
3479: AND paip.line_number = sel_line_num ))
3480: THEN INTO PON_INTERFACE_ERRORS
3481: (
3482: INTERFACE_TYPE ,
3483: ERROR_MESSAGE_NAME,
3484: REQUEST_ID ,
3513: AND NOT EXISTS(SELECT 1 FROM pon_auc_price_breaks_interface pas
3514: WHERE pas.auction_header_id = p_auction_header_id
3515: AND pas.auction_line_number = sel_line_num
3516: AND pas.shipment_number = sel_ship_num ))
3517: THEN INTO PON_INTERFACE_ERRORS
3518: (
3519: INTERFACE_TYPE ,
3520: ERROR_MESSAGE_NAME,
3521: REQUEST_ID ,
3593: WHERE pas.auction_header_id = p_auction_header_id
3594: AND pas.line_number = line_num
3595: AND pas.attribute_sequence_number = seq_num
3596: )) THEN
3597: INTO PON_INTERFACE_ERRORS
3598: (
3599: INTERFACE_TYPE ,
3600: ERROR_MESSAGE_NAME,
3601: REQUEST_ID ,
3645: INSERT ALL
3646: -- Entered score value should be between 0 and 100
3647: WHEN ( SCORE IS NOT NULL
3648: AND sel_trunc_score NOT BETWEEN 0 AND 100 ) THEN
3649: INTO PON_INTERFACE_ERRORS
3650: (
3651: INTERFACE_TYPE ,
3652: ERROR_MESSAGE_NAME,
3653: REQUEST_ID ,
3680: )
3681: -- SCORE MUST BE A POSITIVE NUMBER
3682: WHEN ( SCORE IS NOT NULL
3683: AND (SCORE - sel_trunc_score <>0) ) THEN
3684: INTO PON_INTERFACE_ERRORS
3685: (
3686: INTERFACE_TYPE ,
3687: ERROR_MESSAGE_NAME,
3688: REQUEST_ID ,
3801: l_batch_id := rec.interface_header_id;
3802: UPDATE pon_auction_Headers_interface
3803: SET PROCESSING_STATUS_CODE = 'PROCESSING'
3804: WHERE interface_header_id = l_batch_id;
3805: DELETE FROM pon_interface_errors WHERE batch_id = l_batch_id;
3806:
3807: BEGIN
3808: create_negotiation(l_batch_id,
3809: l_auction_header_id,
4015: WHEN No_Data_Found THEN
4016: CONTINUE;
4017: END;
4018:
4019: insert into PON_INTERFACE_ERRORS
4020: (interface_type,
4021: error_value,
4022: error_message_name,
4023: table_name,
4041: SELECT 'E' status
4042: INTO x_return_status
4043: FROM dual
4044: WHERE EXISTS
4045: (SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
4046: );
4047: EXCEPTION
4048: WHEN No_Data_Found THEN
4049: x_return_status:=FND_API.G_RET_STS_SUCCESS;
4983: RAISE FND_API.G_EXC_ERROR;
4984: END IF;
4985: EXCEPTION
4986: WHEN OTHERS THEN
4987: INSERT INTO PON_INTERFACE_ERRORS
4988: (
4989: INTERFACE_TYPE ,
4990: ERROR_MESSAGE_NAME,
4991: BATCH_ID ,
5026: END IF;
5027:
5028: EXCEPTION
5029: WHEN OTHERS THEN
5030: INSERT INTO PON_INTERFACE_ERRORS
5031: (
5032: INTERFACE_TYPE ,
5033: ERROR_MESSAGE_NAME,
5034: BATCH_ID ,
5060: begin
5061: SELECT doctype_group_name INTO dummy1 FROM pon_auc_doctypes WHERE doctype_id = l_doctype_id;
5062: EXCEPTION
5063: WHEN OTHERS THEN
5064: INSERT INTO PON_INTERFACE_ERRORS
5065: (
5066: INTERFACE_TYPE ,
5067: ERROR_MESSAGE_NAME,
5068: BATCH_ID ,
5090:
5091: INSERT ALL
5092: WHEN (l_org_id IS NULL OR
5093: NOT EXISTS(SELECT 'Y' FROM hr_operating_units WHERE organization_id = l_org_id))
5094: THEN INTO PON_INTERFACE_ERRORS
5095: (
5096: INTERFACE_TYPE ,
5097: ERROR_MESSAGE_NAME,
5098: BATCH_ID ,
5123: AND ds.style_id = ts.style_id
5124: and ds.status = 'ACTIVE'
5125: and ts.doctype_id = l_doctype_id
5126: and ts.enabled_flag = 'Y'))
5127: THEN INTO PON_INTERFACE_ERRORS
5128: (
5129: INTERFACE_TYPE ,
5130: ERROR_MESSAGE_NAME,
5131: BATCH_ID ,
5155: OR NOT EXISTS(SELECT 'Y' FROM po_doc_style_headers WHERE STYLE_ID = l_po_style_id
5156: and Nvl(clm_flag,'N') = PON_CLM_UTIL_PKG.ISCLMRESP)))
5157: OR (l_doctype_id=rfi_doctype_id AND (l_po_style_id IS not NULL OR l_po_style_name IS NOT null))
5158: )
5159: THEN INTO PON_INTERFACE_ERRORS
5160: (
5161: INTERFACE_TYPE ,
5162: ERROR_MESSAGE_NAME,
5163: BATCH_ID ,
5190: SELECT 'E' status
5191: INTO x_return_status
5192: FROM dual
5193: WHERE EXISTS
5194: (SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
5195: );
5196: EXCEPTION
5197: WHEN No_Data_Found THEN
5198: x_return_status:=FND_API.G_RET_STS_SUCCESS;
5222: x_return_status
5223: );
5224:
5225: IF( x_return_status = 'E') THEN
5226: print_Log('process_negotiation_header: Error in pon_auction_headers_interface table. Please check pon_interface_errors for more details' );
5227: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_HEADER_VAL_ERR');
5228: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_batch_id);
5229: FND_MSG_PUB.ADD;
5230: RETURN;
5254: SELECT 'E' status
5255: INTO x_return_status
5256: FROM dual
5257: WHERE EXISTS
5258: (SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
5259: );
5260: EXCEPTION
5261: WHEN No_Data_Found THEN
5262: x_return_status:=FND_API.G_RET_STS_SUCCESS;
5262: x_return_status:=FND_API.G_RET_STS_SUCCESS;
5263: END;
5264:
5265: IF(x_return_status=FND_API.G_RET_STS_ERROR) THEN
5266: print_Log('Error in pon_auction_headers_interface table. Please check pon_interface_errors for more details' );
5267: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_HEADER_VAL_ERR');
5268: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_batch_id);
5269: FND_MSG_PUB.ADD;
5270: RETURN;
5400: null_check_status
5401: );
5402: IF( null_check_status = FND_API.G_RET_STS_ERROR) THEN
5403: print_log('Some mandatory fields in pon_auction_headers_interface table are having null values. Please check '
5404: || ' pon_interface_errors table for more details');
5405: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_HEADER_NULL_VALUES');
5406: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_batch_id);
5407: FND_MSG_PUB.ADD;
5408: RAISE FND_API.G_EXC_ERROR;
5443:
5444: IF(validate_res = 'N') THEN
5445: print_log('Invalid value in pon_auction_headers_interface ' || validate_col || ' ' || validate_val);
5446: invalid_val_exist_flag := 'Y';
5447: INSERT INTO PON_INTERFACE_ERRORS
5448: (
5449: INTERFACE_TYPE ,
5450: ERROR_MESSAGE_NAME,
5451: BATCH_ID ,
5477: END LOOP;
5478:
5479: IF (invalid_val_exist_flag = 'Y' ) THEN
5480: print_log('Some fields in pon_auction_headers_interface table are having invalid values. '
5481: || 'Please check pon_interface_errors table for more details');
5482: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_HDR_INV_VALUES');
5483: FND_MESSAGE.SET_TOKEN('BATCH_ID',p_batch_id);
5484: FND_MSG_PUB.ADD;
5485: RETURN;
5493: isFederal := PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(neg_header_record_data.doctype_id);
5494:
5495: INSERT ALL
5496: WHEN ( neg_header_record_data.auction_title IS NULL )
5497: THEN INTO PON_INTERFACE_ERRORS
5498: (
5499: INTERFACE_TYPE ,
5500: ERROR_MESSAGE_NAME,
5501: BATCH_ID ,
5521: l_cp_login_id
5522: )
5523: WHEN ( neg_header_record_data.ship_to_location_id IS NOT NULL AND neg_header_record_data.ship_to_location_id <> -1
5524: AND NOT EXISTS(SELECT 1 FROM po_ship_to_loc_org_v WHERE location_id = neg_header_record_data.ship_to_location_id))
5525: THEN INTO PON_INTERFACE_ERRORS
5526: (
5527: INTERFACE_TYPE ,
5528: ERROR_MESSAGE_NAME,
5529: BATCH_ID ,
5554: where L.LOCATION_ID = neg_header_record_data.bill_to_location_id
5555: AND NVL(L.BUSINESS_GROUP_ID, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99) ) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99)
5556: AND SYSDATE < NVL(L.INACTIVE_DATE, SYSDATE + 1) AND NVL(L.BILL_TO_SITE_FLAG,'N') = 'Y'))
5557: OR (p_is_amendment<>'Y' AND neg_header_record_data.bill_to_location_id IS NULL))
5558: THEN INTO PON_INTERFACE_ERRORS
5559: (
5560: INTERFACE_TYPE ,
5561: ERROR_MESSAGE_NAME,
5562: BATCH_ID ,
5589: where event_id = neg_header_record_data.event_id and
5590: trading_partner_id = p_tp_id
5591: and event_status <> 'CANCELLED'
5592: and NVL(open_date, SYSDATE - 100) >= SYSDATE)) THEN
5593: INTO PON_INTERFACE_ERRORS
5594: (
5595: INTERFACE_TYPE ,
5596: ERROR_MESSAGE_NAME,
5597: BATCH_ID ,
5616: SYSDATE,
5617: l_cp_login_id
5618: )
5619: WHEN (neg_header_record_data.bid_ranking='PRICE_ONLY' AND neg_header_record_data.show_bidder_scores='SCORE_WEIGHT') THEN
5620: INTO PON_INTERFACE_ERRORS
5621: (
5622: INTERFACE_TYPE ,
5623: ERROR_MESSAGE_NAME,
5624: BATCH_ID ,
5645: )
5646: /* ALLOW_MULTIPLE_ACTIVE_OFFERS */
5647: WHEN ( neg_header_record_data.ALLOW_MULTIPLE_ACTIVE_OFFERS = 'Y'
5648: AND neg_header_record_data.BID_FREQUENCY_CODE <> 'MULTIPLE_BIDS_ALLOWED')
5649: THEN INTO PON_INTERFACE_ERRORS
5650: (
5651: INTERFACE_TYPE ,
5652: ERROR_MESSAGE_NAME,
5653: BATCH_ID ,
5673: l_cp_login_id)
5674: --SIGNED DATE
5675: WHEN ( neg_header_record_data.SIGNED_DATE < neg_header_record_data.CREATION_DATE
5676: AND isFederal = 1)
5677: THEN INTO PON_INTERFACE_ERRORS
5678: (
5679: INTERFACE_TYPE ,
5680: ERROR_MESSAGE_NAME,
5681: BATCH_ID ,
5701: l_cp_login_id)
5702:
5703: --DOCUMENT_FORMAT
5704: WHEN ( neg_header_record_data.DOCUMENT_FORMAT IS NULL AND isFederal = 1)
5705: THEN INTO PON_INTERFACE_ERRORS
5706: (
5707: INTERFACE_TYPE ,
5708: ERROR_MESSAGE_NAME,
5709: BATCH_ID ,
5737: AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1)
5738: AND lookup_code IN (SELECT DOCUMENT_FORMAT FROM PO_PRINT_FORM_FORMATS WHERE DOCUMENT_TYPE = 'PO_SOL_STD_FORM'
5739: and STANDARD_FORM = neg_header_record_data.STANDARD_FORM
5740: AND SYSDATE < Nvl(inactive_date,SYSDATE + 1))))
5741: THEN INTO PON_INTERFACE_ERRORS
5742: (
5743: INTERFACE_TYPE ,
5744: ERROR_MESSAGE_NAME,
5745: BATCH_ID ,
5772: AND LANGUAGE = USERENV('LANG')
5773: AND ENABLED_FLAG = 'Y'
5774: AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1)
5775: AND NVL(END_DATE_ACTIVE, SYSDATE + 1)))
5776: THEN INTO PON_INTERFACE_ERRORS
5777: (
5778: INTERFACE_TYPE ,
5779: ERROR_MESSAGE_NAME,
5780: BATCH_ID ,
5800: l_cp_login_id)
5801:
5802: /* SOLICITATION_TYPE */
5803: WHEN (neg_header_record_data.SOLICITATION_TYPE IS NULL AND isFederal = 1)
5804: THEN INTO PON_INTERFACE_ERRORS
5805: (
5806: INTERFACE_TYPE ,
5807: ERROR_MESSAGE_NAME,
5808: BATCH_ID ,
5833: AND LANGUAGE = USERENV('LANG')
5834: AND ENABLED_FLAG = 'Y'
5835: AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1)
5836: ))
5837: THEN INTO PON_INTERFACE_ERRORS
5838: (
5839: INTERFACE_TYPE ,
5840: ERROR_MESSAGE_NAME,
5841: BATCH_ID ,
5861: l_cp_login_id)
5862:
5863: /* STANDARD_FORM */
5864: WHEN ( neg_header_record_data.STANDARD_FORM IS NULL AND isFederal = 1)
5865: THEN INTO PON_INTERFACE_ERRORS
5866: (
5867: INTERFACE_TYPE ,
5868: ERROR_MESSAGE_NAME,
5869: BATCH_ID ,
5899: AND LOOKUP_CODE IN(SELECT STANDARD_FORM
5900: FROM PO_PRINT_FORM_FORMATS
5901: WHERE DOCUMENT_TYPE = 'PO_SOL_STD_FORM')
5902: ))
5903: THEN INTO PON_INTERFACE_ERRORS
5904: (
5905: INTERFACE_TYPE ,
5906: ERROR_MESSAGE_NAME,
5907: BATCH_ID ,
5932:
5933: IF ( p_is_amendment <> 'Y' AND
5934: (neg_header_record_data.standard_form IS NULL OR neg_header_record_data.document_format IS NULL)) THEN
5935:
5936: INSERT INTO PON_INTERFACE_ERRORS
5937: (
5938: INTERFACE_TYPE ,
5939: ERROR_MESSAGE_NAME,
5940: BATCH_ID ,
5966:
5967: INSERT ALL
5968: WHEN(neg_header_record_data.progress_payment_flag = 'Y'
5969: AND (doctype_name NOT in ('REQUEST_FOR_QUOTE','SOLICITATION') OR neg_header_record_data.contract_type <> 'STANDARD'))
5970: THEN INTO PON_INTERFACE_ERRORS
5971: (
5972: INTERFACE_TYPE ,
5973: ERROR_MESSAGE_NAME,
5974: BATCH_ID ,
5993: l_cp_login_id)
5994:
5995: WHEN(neg_header_record_data.bid_visibility_code = 'OPEN_BIDDING'
5996: AND doctype_name IN ('REQUEST_FOR_QUOTE','SOLICITATION'))
5997: THEN INTO PON_INTERFACE_ERRORS
5998: (
5999: INTERFACE_TYPE ,
6000: ERROR_MESSAGE_NAME,
6001: BATCH_ID ,
6020: l_cp_login_id)
6021:
6022: WHEN(neg_header_record_data.project_id IS NOT NULL
6023: AND NOT EXISTS(SELECT 'Y' FROM pa_projects_expend_v WHERE project_Id = neg_header_record_data.project_Id))
6024: THEN INTO PON_INTERFACE_ERRORS
6025: (
6026: INTERFACE_TYPE ,
6027: ERROR_MESSAGE_NAME,
6028: BATCH_ID ,
6047: l_cp_login_id)
6048:
6049: WHEN(neg_header_record_data.fob_code IS NOT NULL
6050: AND neg_header_record_data.fob_code NOT IN (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'FOB' AND LANGUAGE=UserEnv('LANG')))
6051: THEN INTO PON_INTERFACE_ERRORS
6052: (
6053: INTERFACE_TYPE ,
6054: ERROR_MESSAGE_NAME,
6055: BATCH_ID ,
6073: SYSDATE,
6074: l_cp_login_id)
6075: WHEN(neg_header_record_data.freight_terms_code IS NOT NULL
6076: AND neg_header_record_data.freight_terms_code NOT IN (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'FREIGHT TERMS' AND LANGUAGE=UserEnv('LANG')))
6077: THEN INTO PON_INTERFACE_ERRORS
6078: (
6079: INTERFACE_TYPE ,
6080: ERROR_MESSAGE_NAME,
6081: BATCH_ID ,
6099: SYSDATE,
6100: l_cp_login_id)
6101: WHEN(neg_header_record_data.payment_terms_id IS NOT NULL
6102: AND NOT EXISTS (select 'Y' from ap_terms where term_id = neg_header_record_data.payment_terms_id and SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE-1) AND NVL(END_DATE_ACTIVE,SYSDATE+1)))
6103: THEN INTO PON_INTERFACE_ERRORS
6104: (
6105: INTERFACE_TYPE ,
6106: ERROR_MESSAGE_NAME,
6107: BATCH_ID ,
6131: WHERE org.organization_id = neg_header_record_data.org_id
6132: AND org.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID
6133: AND sob.currency_code = neg_header_record_data.currency_code
6134: AND neg_header_record_data.currency_code IS NOT NULL)))
6135: THEN INTO PON_INTERFACE_ERRORS
6136: (
6137: INTERFACE_TYPE ,
6138: ERROR_MESSAGE_NAME,
6139: BATCH_ID ,
6161: WHEN( p_is_amendment <> 'Y'
6162: AND ((neg_header_record_data.open_bidding_date IS NULL
6163: AND Nvl(neg_header_record_data.open_auction_now_flag,'N') = 'N')
6164: OR neg_header_record_data.open_bidding_date < sysdate))
6165: THEN INTO PON_INTERFACE_ERRORS
6166: (
6167: INTERFACE_TYPE ,
6168: ERROR_MESSAGE_NAME,
6169: BATCH_ID ,
6188: l_cp_login_id)
6189: WHEN( p_is_amendment <> 'Y'
6190: AND (neg_header_record_data.close_bidding_date IS NULL or
6191: neg_header_record_data.open_bidding_date > neg_header_record_data.close_bidding_date ))
6192: THEN INTO PON_INTERFACE_ERRORS
6193: (
6194: INTERFACE_TYPE ,
6195: ERROR_MESSAGE_NAME,
6196: BATCH_ID ,
6214: SYSDATE,
6215: l_cp_login_id)
6216: WHEN(neg_header_record_data.CONTRACT_TYPE = 'BLANKET' and
6217: neg_header_record_data.po_start_date > neg_header_record_data.po_end_date)
6218: THEN INTO PON_INTERFACE_ERRORS
6219: (
6220: INTERFACE_TYPE ,
6221: ERROR_MESSAGE_NAME,
6222: BATCH_ID ,
6240: SYSDATE,
6241: l_cp_login_id)
6242: WHEN(neg_header_record_data.CONTRACT_TYPE = 'BLANKET' and
6243: neg_header_record_data.po_min_rel_amount < 0 )
6244: THEN INTO PON_INTERFACE_ERRORS
6245: (
6246: INTERFACE_TYPE ,
6247: ERROR_MESSAGE_NAME,
6248: BATCH_ID ,
6918: print_Log('Completing init_rule_based_header_data procedure ');
6919:
6920: EXCEPTION
6921: WHEN OTHERS THEN
6922: print_Log('Error in pon_auction_headers_interface table. Please check pon_interface_errors for more details' );
6923: FND_MESSAGE.SET_NAME('PON','PON_IMPORT_HEADER_INIT_ERR');
6924: FND_MSG_PUB.ADD;
6925: RAISE FND_API.G_EXC_ERROR;
6926:
7240: SELECT 'E' status
7241: INTO x_return_status
7242: FROM dual
7243: WHERE EXISTS
7244: (SELECT * FROM pon_interface_errors WHERE BATCH_ID= p_batch_Id
7245: );
7246: EXCEPTION
7247: WHEN No_Data_Found THEN
7248: x_return_status:=FND_API.G_RET_STS_SUCCESS;
7248: x_return_status:=FND_API.G_RET_STS_SUCCESS;
7249: END;
7250:
7251: IF(x_return_status=FND_API.G_RET_STS_ERROR) THEN
7252: print_Log('Error in pon_auction_headers_interface table. Please check pon_interface_errors for more details' );
7253: FND_MESSAGE.SET_NAME('PON','PON_INT_HEADER_VAL_ERR');
7254: FND_MSG_PUB.ADD;
7255: RAISE FND_API.G_EXC_ERROR;
7256: END IF;
7571: p_user_login IN number)
7572: AS
7573:
7574: BEGIN
7575: INSERT INTO PON_INTERFACE_ERRORS
7576: (
7577: INTERFACE_TYPE ,
7578: ERROR_MESSAGE_NAME,
7579: BATCH_ID ,
7613: WHEN (sel_line_id > 1
7614: AND NOT EXISTS (SELECT interface_line_id FROM pon_item_prices_interface t2
7615: WHERE t2.batch_id = p_batch_id
7616: AND t2.interface_line_id = (sel_line_id - 1)) )
7617: THEN INTO PON_INTERFACE_ERRORS
7618: (
7619: INTERFACE_TYPE ,
7620: ERROR_MESSAGE_NAME,
7621: BATCH_ID ,
7639: SYSDATE,
7640: fnd_global.login_id)
7641: WHEN (sel_line_id < 1 OR
7642: ( sel_line_id = 1 AND sel_group_type IN ('LOT_LINE','GROUP_LINE')))
7643: THEN INTO PON_INTERFACE_ERRORS
7644: (
7645: INTERFACE_TYPE ,
7646: ERROR_MESSAGE_NAME,
7647: BATCH_ID ,
7675: OR
7676: (sel_group_type = 'GROUP_LINE'
7677: AND t2.group_type NOT IN ('GROUP','GROUP_LINE'))
7678: )) )
7679: THEN INTO PON_INTERFACE_ERRORS
7680: (
7681: INTERFACE_TYPE ,
7682: ERROR_MESSAGE_NAME,
7683: BATCH_ID ,
7711: SELECT 1 INTO dummy FROM dual
7712: WHERE NOT EXISTS (SELECT interface_line_id FROM pon_item_prices_interface
7713: WHERE batch_id = p_batch_id AND interface_line_id = 1);
7714:
7715: INSERT INTO PON_INTERFACE_ERRORS
7716: (
7717: INTERFACE_TYPE ,
7718: ERROR_MESSAGE_NAME,
7719: BATCH_ID ,
7743: IF ( PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(neg_header_record_data.doctype_id) = 1 ) THEN
7744:
7745: INSERT ALL
7746: WHEN (sel_group_type <> 'LINE')
7747: THEN INTO PON_INTERFACE_ERRORS
7748: (
7749: INTERFACE_TYPE ,
7750: ERROR_MESSAGE_NAME,
7751: BATCH_ID ,
7768: fnd_global.user_id,
7769: SYSDATE,
7770: fnd_global.login_id)
7771: WHEN (sel_info_flag IS NULL )
7772: THEN INTO PON_INTERFACE_ERRORS
7773: (
7774: INTERFACE_TYPE ,
7775: ERROR_MESSAGE_NAME,
7776: column_name,
7799: AND (sel_price IS NOT NULL
7800: OR sel_qty IS NOT NULL
7801: OR sel_category IS NOT NULL
7802: OR sel_line_type IS NOT NULL ) )
7803: THEN INTO PON_INTERFACE_ERRORS
7804: (
7805: INTERFACE_TYPE ,
7806: ERROR_MESSAGE_NAME,
7807: BATCH_ID ,
7830: WHERE t2.interface_line_id = sel_group_line_id
7831: AND Nvl(t2.clm_info_flag,'N') = 'N'
7832: AND Nvl(sel_info_flag,'N') = 'N' )
7833: OR sel_line_id = 1)) -- first line can't be slin
7834: THEN INTO PON_INTERFACE_ERRORS
7835: (
7836: INTERFACE_TYPE ,
7837: ERROR_MESSAGE_NAME,
7838: BATCH_ID ,