[Home] [Help]
389: * p_cust_account_rec Customer account record.
390: * p_rowid Rowid of the record (used only in update mode).
391: * IN/OUT:
392: * x_return_status Return status after the call. The status can
393: * be FND_API.G_RET_STS_SUCCESS (success),
394: * FND_API.G_RET_STS_ERROR (error),
395: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
396: *
397: * NOTES
390: * p_rowid Rowid of the record (used only in update mode).
391: * IN/OUT:
392: * x_return_status Return status after the call. The status can
393: * be FND_API.G_RET_STS_SUCCESS (success),
394: * FND_API.G_RET_STS_ERROR (error),
395: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
396: *
397: * NOTES
398: *
391: * IN/OUT:
392: * x_return_status Return status after the call. The status can
393: * be FND_API.G_RET_STS_SUCCESS (success),
394: * FND_API.G_RET_STS_ERROR (error),
395: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
396: *
397: * NOTES
398: *
399: * MODIFICATION HISTORY
516: -- If primary key value is not passed, it will be generated
517: -- from sequence by table handler.
518:
519: IF p_cust_account_rec.cust_account_id IS NOT NULL AND
520: p_cust_account_rec.cust_account_id <> FND_API.G_MISS_NUM
521: THEN
522: BEGIN
523: SELECT 'Y' INTO l_dummy
524: FROM HZ_CUST_ACCOUNTS
526:
527: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
528: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id' );
529: FND_MSG_PUB.ADD;
530: x_return_status := FND_API.G_RET_STS_ERROR;
531: EXCEPTION
532: WHEN NO_DATA_FOUND THEN
533: NULL;
534: END;
578: --END IF;
579:
580: EXCEPTION
581: WHEN NO_DATA_FOUND THEN
582: x_return_status := FND_API.G_RET_STS_ERROR;
583: END ;
584:
585: /* IF G_DEBUG THEN
586: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
600: -- value, error out;
601:
602: IF l_profile = 'Y' THEN
603: IF p_cust_account_rec.account_number IS NOT NULL AND
604: p_cust_account_rec.account_number <> FND_API.G_MISS_CHAR
605: THEN
606: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_ACCOUNT_NUMBER_AUTO_ON' );
607: FND_MSG_PUB.ADD;
608: x_return_status := FND_API.G_RET_STS_ERROR;
604: p_cust_account_rec.account_number <> FND_API.G_MISS_CHAR
605: THEN
606: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_ACCOUNT_NUMBER_AUTO_ON' );
607: FND_MSG_PUB.ADD;
608: x_return_status := FND_API.G_RET_STS_ERROR;
609: END IF;
610:
611: -- the account_number will be generated from sequence by table handler.
612:
625:
626: ELSIF l_profile = 'N' /* Bug 5118546 */
627: THEN
628: IF p_cust_account_rec.account_number IS NULL OR
629: p_cust_account_rec.account_number = FND_API.G_MISS_CHAR
630: THEN
631: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
632: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'account_number' );
633: FND_MSG_PUB.ADD;
630: THEN
631: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
632: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'account_number' );
633: FND_MSG_PUB.ADD;
634: x_return_status := FND_API.G_RET_STS_ERROR;
635: END IF;
636:
637: /*IF G_DEBUG THEN
638: hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
704: END IF;
705:
706: -- account_number is unique.
707: IF p_cust_account_rec.account_number IS NOT NULL AND
708: p_cust_account_rec.account_number <> FND_API.G_MISS_CHAR
709: THEN
710: BEGIN
711: SELECT ROWID INTO l_rowid
712: FROM HZ_CUST_ACCOUNTS
717: THEN
718: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
719: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'account_number' );
720: FND_MSG_PUB.ADD;
721: x_return_status := FND_API.G_RET_STS_ERROR;
722: END IF;
723: EXCEPTION
724: WHEN NO_DATA_FOUND THEN
725: NULL;
768: -- during creation.
769:
770: IF p_create_update_flag = 'C' AND
771: p_cust_account_rec.orig_system_reference IS NOT NULL AND
772: p_cust_account_rec.orig_system_reference <> FND_API.G_MISS_CHAR
773: THEN
774: BEGIN
775: SELECT 'Y' INTO l_dummy
776: FROM HZ_CUST_ACCOUNTS
778:
779: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
780: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'orig_system_reference' );
781: FND_MSG_PUB.ADD;
782: x_return_status := FND_API.G_RET_STS_ERROR;
783: EXCEPTION
784: WHEN NO_DATA_FOUND THEN
785: NULL;
786: END;
810: else
811: l_orig_system_reference1 := p_cust_account_rec.orig_system_reference;
812: end if;
813:
814: IF (p_cust_account_rec.orig_system is not null and p_cust_account_rec.orig_system <>fnd_api.g_miss_char)
815: and (p_cust_account_rec.orig_system_reference is not null and p_cust_account_rec.orig_system_reference <>fnd_api.g_miss_char)
816: and p_create_update_flag = 'U'
817: then
818: hz_orig_system_ref_pub.get_owner_table_id
811: l_orig_system_reference1 := p_cust_account_rec.orig_system_reference;
812: end if;
813:
814: IF (p_cust_account_rec.orig_system is not null and p_cust_account_rec.orig_system <>fnd_api.g_miss_char)
815: and (p_cust_account_rec.orig_system_reference is not null and p_cust_account_rec.orig_system_reference <>fnd_api.g_miss_char)
816: and p_create_update_flag = 'U'
817: then
818: hz_orig_system_ref_pub.get_owner_table_id
819: (p_orig_system => p_cust_account_rec.orig_system,
821: p_owner_table_name => 'HZ_CUST_ACCOUNTS',
822: x_owner_table_id => l_mosr_owner_table_id,
823: x_return_status => l_temp_return_status);
824:
825: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
826: l_mosr_owner_table_id= nvl(p_cust_account_rec.cust_account_id,l_mosr_owner_table_id))
827: THEN
828: l_validate_flag := 'N';
829: END IF;
831: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
832: -- x_return_status. Set x_return_status to error, ONLY if there is error.
833: -- In case of success, leave it to carry over previous value as before this call.
834: -- Fix for Bug 5498116 (29-AUG-2006)
835: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
836: x_return_status := l_temp_return_status;
837: END IF;
838:
839: END IF;
909: /****Logical APIs - validation not required****/
910: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
911: -- status is lookup code in lookup type CODE_STATUS
912: IF p_cust_account_rec.status IS NOT NULL AND
913: p_cust_account_rec.status <> FND_API.G_MISS_CHAR AND
914: ( p_create_update_flag = 'C' OR
915: ( p_create_update_flag = 'U' AND
916: p_cust_account_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
917: THEN
912: IF p_cust_account_rec.status IS NOT NULL AND
913: p_cust_account_rec.status <> FND_API.G_MISS_CHAR AND
914: ( p_create_update_flag = 'C' OR
915: ( p_create_update_flag = 'U' AND
916: p_cust_account_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
917: THEN
918: validate_lookup (
919: p_column => 'status',
920: p_lookup_type => 'CODE_STATUS',
956: --------------------------------------
957:
958: -- customer_type is lookup code in lookup type CUSTOMER_TYPE
959: IF p_cust_account_rec.customer_type IS NOT NULL AND
960: p_cust_account_rec.customer_type <> FND_API.G_MISS_CHAR AND
961: ( p_create_update_flag = 'C' OR
962: ( p_create_update_flag = 'U' AND
963: p_cust_account_rec.customer_type <> NVL( l_customer_type, FND_API.G_MISS_CHAR ) ) )
964: THEN
959: IF p_cust_account_rec.customer_type IS NOT NULL AND
960: p_cust_account_rec.customer_type <> FND_API.G_MISS_CHAR AND
961: ( p_create_update_flag = 'C' OR
962: ( p_create_update_flag = 'U' AND
963: p_cust_account_rec.customer_type <> NVL( l_customer_type, FND_API.G_MISS_CHAR ) ) )
964: THEN
965: validate_lookup (
966: p_column => 'customer_type',
967: p_lookup_type => 'CUSTOMER_TYPE',
1000: --------------------------------------
1001:
1002: -- sales_channel_code is lookup code in lookup type SALES_CHANNEL in so_lookups
1003: IF p_cust_account_rec.sales_channel_code IS NOT NULL AND
1004: p_cust_account_rec.sales_channel_code <> FND_API.G_MISS_CHAR AND
1005: ( p_create_update_flag = 'C' OR
1006: ( p_create_update_flag = 'U' AND
1007: p_cust_account_rec.sales_channel_code <> NVL( l_sales_channel_code, FND_API.G_MISS_CHAR ) ) )
1008: THEN
1003: IF p_cust_account_rec.sales_channel_code IS NOT NULL AND
1004: p_cust_account_rec.sales_channel_code <> FND_API.G_MISS_CHAR AND
1005: ( p_create_update_flag = 'C' OR
1006: ( p_create_update_flag = 'U' AND
1007: p_cust_account_rec.sales_channel_code <> NVL( l_sales_channel_code, FND_API.G_MISS_CHAR ) ) )
1008: THEN
1009: validate_lookup (
1010: p_column => 'sales_channel_code',
1011: p_lookup_table => 'SO_LOOKUPS',
1045: --------------------------------------
1046:
1047: -- freight_term is lookup code in lookup type FREIGHT_TERMS in so_lookups
1048: IF p_cust_account_rec.freight_term IS NOT NULL AND
1049: p_cust_account_rec.freight_term <> FND_API.G_MISS_CHAR AND
1050: ( p_create_update_flag = 'C' OR
1051: ( p_create_update_flag = 'U' AND
1052: p_cust_account_rec.freight_term <> NVL( l_freight_term, FND_API.G_MISS_CHAR ) ) )
1053: THEN
1048: IF p_cust_account_rec.freight_term IS NOT NULL AND
1049: p_cust_account_rec.freight_term <> FND_API.G_MISS_CHAR AND
1050: ( p_create_update_flag = 'C' OR
1051: ( p_create_update_flag = 'U' AND
1052: p_cust_account_rec.freight_term <> NVL( l_freight_term, FND_API.G_MISS_CHAR ) ) )
1053: THEN
1054: validate_lookup (
1055: p_column => 'freight_term',
1056: p_lookup_table => 'SO_LOOKUPS',
1092: /****Logical APIs - validation not required****/
1093: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1094: -- tax_header_level_flag is lookup code in lookup type YES/NO
1095: IF p_cust_account_rec.tax_header_level_flag IS NOT NULL AND
1096: p_cust_account_rec.tax_header_level_flag <> FND_API.G_MISS_CHAR
1097: THEN
1098: validate_lookup (
1099: p_column => 'tax_header_level_flag',
1100: p_lookup_type => 'YES/NO',
1135:
1136: -- Bug 4713150 : tax_code is now validated against the view zx_output_classifications_v
1137: /*
1138: IF p_cust_account_rec.tax_code IS NOT NULL AND
1139: p_cust_account_rec.tax_code <> FND_API.G_MISS_CHAR
1140: THEN
1141: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1142: FND_MESSAGE.SET_TOKEN('COLUMN','TAX_CODE');
1143: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1141: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1142: FND_MESSAGE.SET_TOKEN('COLUMN','TAX_CODE');
1143: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1144: FND_MSG_PUB.ADD;
1145: x_return_status := FND_API.G_RET_STS_ERROR;
1146: END IF;
1147: */
1148:
1149: IF p_cust_account_rec.tax_code is NOT NULL AND
1146: END IF;
1147: */
1148:
1149: IF p_cust_account_rec.tax_code is NOT NULL AND
1150: p_cust_account_rec.tax_code <> FND_API.G_MISS_CHAR AND
1151: ( p_create_update_flag = 'C' OR
1152: ( p_create_update_flag = 'U' AND
1153: p_cust_account_rec.tax_code <> NVL( l_tax_code, FND_API.G_MISS_CHAR ) ) )
1154: THEN
1149: IF p_cust_account_rec.tax_code is NOT NULL AND
1150: p_cust_account_rec.tax_code <> FND_API.G_MISS_CHAR AND
1151: ( p_create_update_flag = 'C' OR
1152: ( p_create_update_flag = 'U' AND
1153: p_cust_account_rec.tax_code <> NVL( l_tax_code, FND_API.G_MISS_CHAR ) ) )
1154: THEN
1155: check_tax_code(
1156: p_column => 'tax_code',
1157: p_column_value => p_cust_account_rec.tax_code,
1177: ----------------------------------------------------------
1178: /* 3456489. Order type is no longer set at the Customer Account Level. */
1179:
1180: IF p_cust_account_rec.order_type_id IS NOT NULL AND
1181: p_cust_account_rec.order_type_id <> FND_API.G_MISS_NUM
1182: THEN
1183: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1184: FND_MESSAGE.SET_TOKEN('COLUMN','ORDER_TYPE_ID');
1185: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1183: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1184: FND_MESSAGE.SET_TOKEN('COLUMN','ORDER_TYPE_ID');
1185: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1186: FND_MSG_PUB.ADD;
1187: x_return_status := FND_API.G_RET_STS_ERROR;
1188: END IF;
1189:
1190:
1191: /*
1191: /*
1192: | --order_type_id should be a valid value defined in OE_ORDER_TYPES_V
1193: |
1194: | IF p_cust_account_rec.order_type_id is NOT NULL AND
1195: | p_cust_account_rec.order_type_id <> FND_API.G_MISS_NUM AND
1196: | ( p_create_update_flag = 'C' OR
1197: | ( p_create_update_flag = 'U' AND
1198: | p_cust_account_rec.order_type_id <> NVL( l_order_type_id, FND_API.G_MISS_NUM ) ) )
1199: | THEN
1194: | IF p_cust_account_rec.order_type_id is NOT NULL AND
1195: | p_cust_account_rec.order_type_id <> FND_API.G_MISS_NUM AND
1196: | ( p_create_update_flag = 'C' OR
1197: | ( p_create_update_flag = 'U' AND
1198: | p_cust_account_rec.order_type_id <> NVL( l_order_type_id, FND_API.G_MISS_NUM ) ) )
1199: | THEN
1200: | check_ord_type(
1201: | p_column => 'order_type_id',
1202: | p_column_value => p_cust_account_rec.order_type_id,
1233: ----------------------------------------------------------
1234: /* 3456489. Sales Rep is no longer set at the Customer Account Level. */
1235:
1236: IF p_cust_account_rec.primary_salesrep_id IS NOT NULL AND
1237: p_cust_account_rec.primary_salesrep_id <> FND_API.G_MISS_NUM
1238: THEN
1239: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1240: FND_MESSAGE.SET_TOKEN('COLUMN','PRIMARY_SALESREP_ID');
1241: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1239: FND_MESSAGE.SET_NAME('AR','HZ_API_COLUMN_SHOULD_BE_NULL');
1240: FND_MESSAGE.SET_TOKEN('COLUMN','PRIMARY_SALESREP_ID');
1241: FND_MESSAGE.SET_TOKEN('TABLE','HZ_CUST_ACCOUNTS');
1242: FND_MSG_PUB.ADD;
1243: x_return_status := FND_API.G_RET_STS_ERROR;
1244: END IF;
1245:
1246: /*
1247: | --Primary_salesrep_id should be a valid value defined in RA_SALESREPS
1246: /*
1247: | --Primary_salesrep_id should be a valid value defined in RA_SALESREPS
1248: |
1249: | IF p_cust_account_rec.primary_salesrep_id is NOT NULL AND
1250: | p_cust_account_rec.primary_salesrep_id <> FND_API.G_MISS_NUM
1251: | AND ( p_create_update_flag = 'C' OR
1252: | ( p_create_update_flag = 'U' AND
1253: | p_cust_account_rec.primary_salesrep_id <> NVL( l_primary_salesrep_id, FND_API.G_MISS_NUM ) ) )
1254: | THEN
1249: | IF p_cust_account_rec.primary_salesrep_id is NOT NULL AND
1250: | p_cust_account_rec.primary_salesrep_id <> FND_API.G_MISS_NUM
1251: | AND ( p_create_update_flag = 'C' OR
1252: | ( p_create_update_flag = 'U' AND
1253: | p_cust_account_rec.primary_salesrep_id <> NVL( l_primary_salesrep_id, FND_API.G_MISS_NUM ) ) )
1254: | THEN
1255: | check_prim_salesrep(
1256: | p_column => 'primary_salesrep_id',
1257: | p_column_value => p_cust_account_rec.primary_salesrep_id,
1290: -------------------------------------------------------
1291: -- ship_partial is a lookup code in lookup type YES/NO
1292:
1293: IF p_cust_account_rec.ship_partial IS NOT NULL AND
1294: p_cust_account_rec.ship_partial <> FND_API.G_MISS_CHAR
1295: AND ( p_create_update_flag = 'C' OR
1296: ( p_create_update_flag = 'U' AND
1297: p_cust_account_rec.ship_partial <> NVL( l_ship_partial, FND_API.G_MISS_CHAR) ) )
1298: THEN
1293: IF p_cust_account_rec.ship_partial IS NOT NULL AND
1294: p_cust_account_rec.ship_partial <> FND_API.G_MISS_CHAR
1295: AND ( p_create_update_flag = 'C' OR
1296: ( p_create_update_flag = 'U' AND
1297: p_cust_account_rec.ship_partial <> NVL( l_ship_partial, FND_API.G_MISS_CHAR) ) )
1298: THEN
1299: validate_lookup (
1300: p_column => 'ship_partial',
1301: p_lookup_type => 'YES/NO',
1323: -----------------------------------------------------------
1324: --tax_rounding_rule is a lookup_code in lookup type TAX_ROUNDING_RULE
1325:
1326: IF p_cust_account_rec.tax_rounding_rule is NOT NULL AND
1327: p_cust_account_rec.tax_rounding_rule <> FND_API.G_MISS_CHAR
1328: AND ( p_create_update_flag = 'C' OR
1329: ( p_create_update_flag = 'U' AND
1330: p_cust_account_rec.tax_rounding_rule <> NVL( l_tax_rounding_rule, FND_API.G_MISS_CHAR) ) )
1331: THEN
1326: IF p_cust_account_rec.tax_rounding_rule is NOT NULL AND
1327: p_cust_account_rec.tax_rounding_rule <> FND_API.G_MISS_CHAR
1328: AND ( p_create_update_flag = 'C' OR
1329: ( p_create_update_flag = 'U' AND
1330: p_cust_account_rec.tax_rounding_rule <> NVL( l_tax_rounding_rule, FND_API.G_MISS_CHAR) ) )
1331: THEN
1332: validate_lookup(
1333: p_column =>'tax_rounding_rule',
1334: p_lookup_type =>'TAX_ROUNDING_RULE',
1367: -------------------------------------------------------
1368: --Customer_class_code is a lookup_code in lookup type CUSTOMER_CLASS
1369:
1370: IF p_cust_account_rec.customer_class_code is NOT NULL AND
1371: p_cust_account_rec.customer_class_code <> FND_API.G_MISS_CHAR
1372: AND ( p_create_update_flag = 'C' OR
1373: ( p_create_update_flag = 'U' AND
1374: p_cust_account_rec.customer_class_code <> NVL( l_customer_class_code, FND_API.G_MISS_CHAR) ) )
1375: THEN
1370: IF p_cust_account_rec.customer_class_code is NOT NULL AND
1371: p_cust_account_rec.customer_class_code <> FND_API.G_MISS_CHAR
1372: AND ( p_create_update_flag = 'C' OR
1373: ( p_create_update_flag = 'U' AND
1374: p_cust_account_rec.customer_class_code <> NVL( l_customer_class_code, FND_API.G_MISS_CHAR) ) )
1375: THEN
1376: validate_lookup(
1377: p_column =>'customer_class_code',
1378: p_lookup_type =>'CUSTOMER CLASS',
1409: ---------------------------------------------------
1410: --Invoice_quantity_rule is lookup_code in lookup type INVOICE_BASIS
1411:
1412: IF p_cust_account_rec.invoice_quantity_rule is NOT NULL AND
1413: p_cust_account_rec.invoice_quantity_rule <> FND_API.G_MISS_CHAR
1414: AND ( p_create_update_flag = 'C' OR
1415: ( p_create_update_flag = 'U' AND
1416: p_cust_account_rec.invoice_quantity_rule <> NVL( l_invoice_quantity_rule, FND_API.G_MISS_CHAR) ) )
1417: THEN
1412: IF p_cust_account_rec.invoice_quantity_rule is NOT NULL AND
1413: p_cust_account_rec.invoice_quantity_rule <> FND_API.G_MISS_CHAR
1414: AND ( p_create_update_flag = 'C' OR
1415: ( p_create_update_flag = 'U' AND
1416: p_cust_account_rec.invoice_quantity_rule <> NVL( l_invoice_quantity_rule, FND_API.G_MISS_CHAR) ) )
1417: THEN
1418: validate_lookup(
1419: p_column =>'invoice_quantity_rule',
1420: p_lookup_table =>'OE_LOOKUPS',
1454: ----------------------------------------------------------
1455: --price_list_id should be a valid value defined in SO_PRICE_LISTS
1456:
1457: IF p_cust_account_rec.price_list_id is NOT NULL AND
1458: p_cust_account_rec.price_list_id <> FND_API.G_MISS_NUM
1459: AND ( p_create_update_flag = 'C' OR
1460: ( p_create_update_flag = 'U' AND
1461: p_cust_account_rec.price_list_id <> NVL( l_price_list_id, FND_API.G_MISS_NUM) ) )
1462: THEN
1457: IF p_cust_account_rec.price_list_id is NOT NULL AND
1458: p_cust_account_rec.price_list_id <> FND_API.G_MISS_NUM
1459: AND ( p_create_update_flag = 'C' OR
1460: ( p_create_update_flag = 'U' AND
1461: p_cust_account_rec.price_list_id <> NVL( l_price_list_id, FND_API.G_MISS_NUM) ) )
1462: THEN
1463: check_price_list_fk(
1464: p_column => 'price_list_id',
1465: p_column_value => p_cust_account_rec.price_list_id,
1495: ----------------------------------------------------------
1496: --fob_point is lookup_code in lookup type FOB
1497:
1498: IF p_cust_account_rec.fob_point is NOT NULL AND
1499: p_cust_account_rec.fob_point <> FND_API.G_MISS_CHAR
1500: AND ( p_create_update_flag = 'C' OR
1501: ( p_create_update_flag = 'U' AND
1502: p_cust_account_rec.fob_point <> NVL( l_fob_point, FND_API.G_MISS_CHAR) ) )
1503: THEN
1498: IF p_cust_account_rec.fob_point is NOT NULL AND
1499: p_cust_account_rec.fob_point <> FND_API.G_MISS_CHAR
1500: AND ( p_create_update_flag = 'C' OR
1501: ( p_create_update_flag = 'U' AND
1502: p_cust_account_rec.fob_point <> NVL( l_fob_point, FND_API.G_MISS_CHAR) ) )
1503: THEN
1504: validate_lookup(
1505: p_column =>'fob_point',
1506: p_lookup_type =>'FOB',
1538: ----------------------------------------------------------
1539: --item_cross_ref_pref should be a value defined in MTL_CROSS_REFERENCE_TYPES or should have value 'INT' or 'CUST'
1540:
1541: IF p_cust_account_rec.item_cross_ref_pref is NOT NULL AND
1542: p_cust_account_rec.item_cross_ref_pref <> FND_API.G_MISS_CHAR
1543: AND ( p_create_update_flag = 'C' OR
1544: ( p_create_update_flag = 'U' AND
1545: p_cust_account_rec.item_cross_ref_pref <> NVL( l_item_cross_ref_pref, FND_API.G_MISS_CHAR) ) )
1546: THEN
1541: IF p_cust_account_rec.item_cross_ref_pref is NOT NULL AND
1542: p_cust_account_rec.item_cross_ref_pref <> FND_API.G_MISS_CHAR
1543: AND ( p_create_update_flag = 'C' OR
1544: ( p_create_update_flag = 'U' AND
1545: p_cust_account_rec.item_cross_ref_pref <> NVL( l_item_cross_ref_pref, FND_API.G_MISS_CHAR) ) )
1546: THEN
1547: IF p_cust_account_rec.item_cross_ref_pref NOT IN('INT','CUST')
1548: THEN
1549: check_item_cross_ref(
1583: ----------------------------------------------------------
1584: --warehouse_id should be a value defined in ORG_ORGANIZATION_DEFINITIONS
1585:
1586: IF p_cust_account_rec.warehouse_id is NOT NULL AND
1587: p_cust_account_rec.warehouse_id <> FND_API.G_MISS_NUM
1588: AND ( p_create_update_flag = 'C' OR
1589: ( p_create_update_flag = 'U' AND
1590: p_cust_account_rec.warehouse_id <> NVL( l_warehouse_id, FND_API.G_MISS_NUM) ) )
1591: THEN
1586: IF p_cust_account_rec.warehouse_id is NOT NULL AND
1587: p_cust_account_rec.warehouse_id <> FND_API.G_MISS_NUM
1588: AND ( p_create_update_flag = 'C' OR
1589: ( p_create_update_flag = 'U' AND
1590: p_cust_account_rec.warehouse_id <> NVL( l_warehouse_id, FND_API.G_MISS_NUM) ) )
1591: THEN
1592: check_warehouse(
1593: p_column => 'warehouse_id',
1594: p_column_value => p_cust_account_rec.warehouse_id,
1626: ----------------------------------------------------------
1627: --date_type_preference is a lookup_code in lookup_type REQUEST_DATE_TYPE in oe_lookups
1628:
1629: IF p_cust_account_rec.date_type_preference is NOT NULL AND
1630: p_cust_account_rec.date_type_preference <> FND_API.G_MISS_CHAR
1631: AND ( p_create_update_flag = 'C' OR
1632: ( p_create_update_flag = 'U' AND
1633: p_cust_account_rec.date_type_preference <> NVL( l_date_type_preference, FND_API.G_MISS_CHAR) ) )
1634: THEN
1629: IF p_cust_account_rec.date_type_preference is NOT NULL AND
1630: p_cust_account_rec.date_type_preference <> FND_API.G_MISS_CHAR
1631: AND ( p_create_update_flag = 'C' OR
1632: ( p_create_update_flag = 'U' AND
1633: p_cust_account_rec.date_type_preference <> NVL( l_date_type_preference, FND_API.G_MISS_CHAR) ) )
1634: THEN
1635: validate_lookup(
1636: p_column =>'date_type_preference',
1637: p_lookup_table =>'OE_LOOKUPS',
1669: --------------------------------------
1670:
1671: -- primary_specialist_id is foreign key to per_all_people_f
1672: IF p_cust_account_rec.primary_specialist_id IS NOT NULL AND
1673: p_cust_account_rec.primary_specialist_id <> FND_API.G_MISS_NUM
1674: THEN
1675: check_per_all_people_f_fk (
1676: p_column => 'primary_specialist_id',
1677: p_column_value => p_cust_account_rec.primary_specialist_id,
1710: --------------------------------------
1711:
1712: -- secondary_specialist_id is foreign key to per_all_people_f
1713: IF p_cust_account_rec.secondary_specialist_id IS NOT NULL AND
1714: p_cust_account_rec.secondary_specialist_id <> FND_API.G_MISS_NUM
1715: THEN
1716: check_per_all_people_f_fk (
1717: p_column => 'secondary_specialist_id',
1718: p_column_value => p_cust_account_rec.secondary_specialist_id,
1751: --------------------------------------
1752: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1753: -- account_liable_flag is lookup code in lookup type YES/NO
1754: IF p_cust_account_rec.account_liable_flag IS NOT NULL AND
1755: p_cust_account_rec.account_liable_flag <> FND_API.G_MISS_CHAR
1756: THEN
1757: validate_lookup (
1758: p_column => 'account_liable_flag',
1759: p_lookup_type => 'YES/NO',
1782: /****Logical APIs - validation not required****/
1783: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1784: -- hold_bill_flag is lookup code in lookup type YES/NO
1785: IF p_cust_account_rec.hold_bill_flag IS NOT NULL AND
1786: p_cust_account_rec.hold_bill_flag <> FND_API.G_MISS_CHAR
1787: THEN
1788: validate_lookup (
1789: p_column => 'hold_bill_flag',
1790: p_lookup_type => 'YES/NO',
1815: --------------------------------------
1816: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1817: -- dormant_account_flag is lookup code in lookup type YES/NO
1818: IF p_cust_account_rec.dormant_account_flag IS NOT NULL AND
1819: p_cust_account_rec.dormant_account_flag <> FND_API.G_MISS_CHAR
1820: THEN
1821: validate_lookup (
1822: p_column => 'dormant_account_flag',
1823: p_lookup_type => 'YES/NO',
1846: /****Logical APIs - validation not required****/
1847: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1848: -- ship_sets_include_lines_flag is lookup code in lookup type YES/NO
1849: IF p_cust_account_rec.ship_sets_include_lines_flag IS NOT NULL AND
1850: p_cust_account_rec.ship_sets_include_lines_flag <> FND_API.G_MISS_CHAR
1851: AND ( p_create_update_flag = 'C' OR
1852: ( p_create_update_flag = 'U' AND
1853: p_cust_account_rec.ship_sets_include_lines_flag <> NVL( l_ship_sets_inc_lines_f, FND_API.G_MISS_CHAR) ) )
1854: THEN
1849: IF p_cust_account_rec.ship_sets_include_lines_flag IS NOT NULL AND
1850: p_cust_account_rec.ship_sets_include_lines_flag <> FND_API.G_MISS_CHAR
1851: AND ( p_create_update_flag = 'C' OR
1852: ( p_create_update_flag = 'U' AND
1853: p_cust_account_rec.ship_sets_include_lines_flag <> NVL( l_ship_sets_inc_lines_f, FND_API.G_MISS_CHAR) ) )
1854: THEN
1855: validate_lookup (
1856: p_column => 'ship_sets_include_lines_flag',
1857: p_lookup_type => 'YES/NO',
1877: --is always set to N.
1878:
1879: IF (p_create_update_flag = 'C' or
1880: (p_create_update_flag ='U' and
1881: p_cust_account_rec.ship_sets_include_lines_flag <> NVL(l_ship_sets_inc_lines_f,FND_API.G_MISS_CHAR)))
1882: THEN
1883: IF p_cust_account_rec.ship_sets_include_lines_flag = 'Y'
1884: THEN
1885: BEGIN
1895: FND_MESSAGE.SET_TOKEN('VALUE1','Y');
1896: FND_MESSAGE.SET_TOKEN('COLUMN2','arrivalsets_include_lines_flag');
1897: FND_MESSAGE.SET_TOKEN('VALUE2','N');
1898: FND_MSG_PUB.ADD;
1899: x_return_status := FND_API.G_RET_STS_ERROR;
1900: END IF;
1901: END ;
1902: END IF;
1903: END IF;
1934: /****Logical APIs - validation not required****/
1935: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1936: -- arrivalsets_include_lines_flag is lookup code in lookup type YES/NO
1937: IF p_cust_account_rec.arrivalsets_include_lines_flag IS NOT NULL AND
1938: p_cust_account_rec.arrivalsets_include_lines_flag <> FND_API.G_MISS_CHAR
1939: AND ( p_create_update_flag = 'C' OR
1940: ( p_create_update_flag = 'U' AND
1941: p_cust_account_rec.arrivalsets_include_lines_flag <> NVL( l_arrivalsets_inc_lines_f, FND_API.G_MISS_CHAR) ) )
1942: THEN
1937: IF p_cust_account_rec.arrivalsets_include_lines_flag IS NOT NULL AND
1938: p_cust_account_rec.arrivalsets_include_lines_flag <> FND_API.G_MISS_CHAR
1939: AND ( p_create_update_flag = 'C' OR
1940: ( p_create_update_flag = 'U' AND
1941: p_cust_account_rec.arrivalsets_include_lines_flag <> NVL( l_arrivalsets_inc_lines_f, FND_API.G_MISS_CHAR) ) )
1942: THEN
1943: validate_lookup (
1944: p_column => 'arrivalsets_include_lines_flag',
1945: p_lookup_type => 'YES/NO',
1965: --is always set to N.
1966:
1967: IF (p_create_update_flag ='C' or
1968: (p_create_update_flag ='U' and
1969: p_cust_account_rec.arrivalsets_include_lines_flag <> NVL(l_arrivalsets_inc_lines_f,FND_API.G_MISS_CHAR)))
1970: THEN
1971: IF p_cust_account_rec.arrivalsets_include_lines_flag = 'Y'
1972: THEN
1973: BEGIN
1984: FND_MESSAGE.SET_TOKEN('VALUE1','Y');
1985: FND_MESSAGE.SET_TOKEN('COLUMN2','ship_sets_include_lines_flag');
1986: FND_MESSAGE.SET_TOKEN('VALUE2','N');
1987: FND_MSG_PUB.ADD;
1988: x_return_status := FND_API.G_RET_STS_ERROR;
1989: END IF;
1990: END ;
1991: END IF;
1992: END IF;
2022: /****Logical APIs - validation not required****/
2023: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
2024: -- sched_date_push_flag is lookup code in lookup type YES/NO
2025: IF p_cust_account_rec.sched_date_push_flag IS NOT NULL AND
2026: p_cust_account_rec.sched_date_push_flag <> FND_API.G_MISS_CHAR
2027: THEN
2028: validate_lookup (
2029: p_column => 'sched_date_push_flag',
2030: p_lookup_type => 'YES/NO',
2133: /****Logical APIs - validation not required****/
2134: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
2135: -- autopay_flag is lookup code in lookup type YES/NO
2136: IF p_cust_account_rec.autopay_flag IS NOT NULL AND
2137: p_cust_account_rec.autopay_flag <> FND_API.G_MISS_CHAR
2138: THEN
2139: validate_lookup (
2140: p_column => 'autopay_flag',
2141: p_lookup_type => 'YES/NO',
2176: --------------------------------------
2177: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
2178: -- notify_flag is lookup code in lookup type YES/NO
2179: IF p_cust_account_rec.notify_flag IS NOT NULL AND
2180: p_cust_account_rec.notify_flag <> FND_API.G_MISS_CHAR
2181: THEN
2182: validate_lookup (
2183: p_column => 'notify_flag',
2184: p_lookup_type => 'YES/NO',
2207:
2208: -- ship_via is foreign key to oe_ship_methods_v
2209: -- can be used only in single org case.
2210: IF p_cust_account_rec.ship_via IS NOT NULL AND
2211: p_cust_account_rec.ship_via <> FND_API.G_MISS_CHAR AND
2212: ( p_create_update_flag = 'C' OR
2213: ( p_create_update_flag = 'U' AND
2214: p_cust_account_rec.ship_via <> NVL( l_ship_via, FND_API.G_MISS_CHAR ) ) )
2215: THEN
2210: IF p_cust_account_rec.ship_via IS NOT NULL AND
2211: p_cust_account_rec.ship_via <> FND_API.G_MISS_CHAR AND
2212: ( p_create_update_flag = 'C' OR
2213: ( p_create_update_flag = 'U' AND
2214: p_cust_account_rec.ship_via <> NVL( l_ship_via, FND_API.G_MISS_CHAR ) ) )
2215: THEN
2216: check_oe_ship_methods_v_fk (
2217: p_entity => 'ACCOUNT',
2218: p_column => 'ship_via',
2246: --------------------------------------
2247:
2248: -- selling_party_id is foreign key of hz_parties
2249: IF p_cust_account_rec.selling_party_id IS NOT NULL AND
2250: p_cust_account_rec.selling_party_id <> FND_API.G_MISS_NUM
2251: THEN
2252: check_party_fk (
2253: p_column => 'selling_party_id',
2254: p_column_value => p_cust_account_rec.selling_party_id,
2328: * p_cust_account_rec Customer account relate record.
2329: * p_rowid Rowid of the record (used only in update mode).
2330: * IN/OUT:
2331: * x_return_status Return status after the call. The status can
2332: * be FND_API.G_RET_STS_SUCCESS (success),
2333: * FND_API.G_RET_STS_ERROR (error),
2334: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2335: *
2336: * NOTES
2329: * p_rowid Rowid of the record (used only in update mode).
2330: * IN/OUT:
2331: * x_return_status Return status after the call. The status can
2332: * be FND_API.G_RET_STS_SUCCESS (success),
2333: * FND_API.G_RET_STS_ERROR (error),
2334: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2335: *
2336: * NOTES
2337: *
2330: * IN/OUT:
2331: * x_return_status Return status after the call. The status can
2332: * be FND_API.G_RET_STS_SUCCESS (success),
2333: * FND_API.G_RET_STS_ERROR (error),
2334: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2335: *
2336: * NOTES
2337: *
2338: * MODIFICATION HISTORY
2411:
2412: -- cust_account_id is foreign key of hz_cust_accounts
2413: IF p_create_update_flag = 'C' AND
2414: p_cust_acct_relate_rec.cust_account_id IS NOT NULL AND
2415: p_cust_acct_relate_rec.cust_account_id <> FND_API.G_MISS_NUM
2416: THEN
2417: check_cust_account_fk (
2418: p_column => 'cust_account_id',
2419: p_column_value => p_cust_acct_relate_rec.cust_account_id,
2462:
2463: -- related_cust_account_id is foreign key of hz_cust_accounts
2464: IF p_create_update_flag = 'C' AND
2465: p_cust_acct_relate_rec.related_cust_account_id IS NOT NULL AND
2466: p_cust_acct_relate_rec.related_cust_account_id <> FND_API.G_MISS_NUM
2467: THEN
2468: check_cust_account_fk (
2469: p_column => 'related_cust_account_id',
2470: p_column_value => p_cust_acct_relate_rec.related_cust_account_id,
2493: AND ORG_ID = p_cust_acct_relate_rec.org_id; -- Bug 3456489
2494:
2495: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_REL_ALREADY_EXISTS' );
2496: FND_MSG_PUB.ADD;
2497: x_return_status := FND_API.G_RET_STS_ERROR;
2498:
2499: EXCEPTION
2500: WHEN NO_DATA_FOUND THEN
2501: NULL;
2550: -- checking during create.
2551:
2552: IF p_create_update_flag = 'C' AND
2553: p_cust_acct_relate_rec.customer_reciprocal_flag IS NOT NULL AND
2554: p_cust_acct_relate_rec.customer_reciprocal_flag <> FND_API.G_MISS_CHAR
2555: THEN
2556: validate_lookup (
2557: p_column => 'customer_reciprocal_flag',
2558: p_lookup_type => 'YES/NO',
2581: --------------------------------------
2582:
2583: -- relationship_type is lookup code in lookup type RELATIONSHIP_TYPE
2584: IF p_cust_acct_relate_rec.relationship_type IS NOT NULL AND
2585: p_cust_acct_relate_rec.relationship_type <> FND_API.G_MISS_CHAR AND
2586: ( p_create_update_flag = 'C' OR
2587: ( p_create_update_flag = 'U' AND
2588: p_cust_acct_relate_rec.relationship_type <> NVL( l_relationship_type, FND_API.G_MISS_CHAR ) ) )
2589: THEN
2584: IF p_cust_acct_relate_rec.relationship_type IS NOT NULL AND
2585: p_cust_acct_relate_rec.relationship_type <> FND_API.G_MISS_CHAR AND
2586: ( p_create_update_flag = 'C' OR
2587: ( p_create_update_flag = 'U' AND
2588: p_cust_acct_relate_rec.relationship_type <> NVL( l_relationship_type, FND_API.G_MISS_CHAR ) ) )
2589: THEN
2590: validate_lookup (
2591: p_column => 'relationship_type',
2592: p_lookup_type => 'RELATIONSHIP_TYPE',
2633: /****Logical APIs - validation not required****/
2634: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
2635: -- status is lookup code in lookup type CODE_STATUS
2636: IF p_cust_acct_relate_rec.status IS NOT NULL AND
2637: p_cust_acct_relate_rec.status <> FND_API.G_MISS_CHAR AND
2638: ( p_create_update_flag = 'C' OR
2639: ( p_create_update_flag = 'U' AND
2640: p_cust_acct_relate_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
2641: THEN
2636: IF p_cust_acct_relate_rec.status IS NOT NULL AND
2637: p_cust_acct_relate_rec.status <> FND_API.G_MISS_CHAR AND
2638: ( p_create_update_flag = 'C' OR
2639: ( p_create_update_flag = 'U' AND
2640: p_cust_acct_relate_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
2641: THEN
2642: validate_lookup (
2643: p_column => 'status',
2644: p_lookup_type => 'CODE_STATUS',
2711: * p_customer_profile_rec Customer profile record.
2712: * p_rowid Rowid of the record (used only in update mode).
2713: * IN/OUT:
2714: * x_return_status Return status after the call. The status can
2715: * be FND_API.G_RET_STS_SUCCESS (success),
2716: * FND_API.G_RET_STS_ERROR (error),
2717: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2718: *
2719: * NOTES
2712: * p_rowid Rowid of the record (used only in update mode).
2713: * IN/OUT:
2714: * x_return_status Return status after the call. The status can
2715: * be FND_API.G_RET_STS_SUCCESS (success),
2716: * FND_API.G_RET_STS_ERROR (error),
2717: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2718: *
2719: * NOTES
2720: *
2713: * IN/OUT:
2714: * x_return_status Return status after the call. The status can
2715: * be FND_API.G_RET_STS_SUCCESS (success),
2716: * FND_API.G_RET_STS_ERROR (error),
2717: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2718: *
2719: * NOTES
2720: *
2721: * MODIFICATION HISTORY
2873: -- If primary key value is not passed, it will be generated
2874: -- from sequence by table handler.
2875:
2876: IF p_customer_profile_rec.cust_account_profile_id IS NOT NULL AND
2877: p_customer_profile_rec.cust_account_profile_id <> FND_API.G_MISS_NUM
2878: THEN
2879: BEGIN
2880: SELECT 'Y' INTO l_dummy
2881: FROM HZ_CUSTOMER_PROFILES
2883:
2884: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
2885: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_profile_id' );
2886: FND_MSG_PUB.ADD;
2887: x_return_status := FND_API.G_RET_STS_ERROR;
2888: EXCEPTION
2889: WHEN NO_DATA_FOUND THEN
2890: NULL;
2891: END;
3010: -- check FK during creation.
3011:
3012: IF p_create_update_flag = 'C' AND
3013: p_customer_profile_rec.cust_account_id IS NOT NULL AND
3014: p_customer_profile_rec.cust_account_id <> FND_API.G_MISS_NUM
3015: THEN
3016: check_cust_account_fk (
3017: p_column => 'cust_account_id',
3018: p_column_value => p_customer_profile_rec.cust_account_id,
3038: -------------------------------------
3039:
3040: IF p_create_update_flag = 'C' AND
3041: ( p_customer_profile_rec.site_use_id IS NULL OR
3042: p_customer_profile_rec.site_use_id = FND_API.G_MISS_NUM ) AND
3043: p_customer_profile_rec.cust_account_id <> -1
3044: THEN
3045: BEGIN
3046: SELECT 'Y' INTO l_dummy
3051:
3052: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
3053: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id' );
3054: FND_MSG_PUB.ADD;
3055: x_return_status := FND_API.G_RET_STS_ERROR;
3056: EXCEPTION
3057: WHEN NO_DATA_FOUND THEN
3058: NULL;
3059: END;
3068:
3069:
3070: ELSIF p_create_update_flag = 'C' AND
3071: ( p_customer_profile_rec.site_use_id IS NULL OR
3072: p_customer_profile_rec.site_use_id = FND_API.G_MISS_NUM ) AND
3073: p_customer_profile_rec.cust_account_id = -1
3074: THEN
3075: -- 2310474 in this case we are creating a customer profile for party
3076: -- One party can only have 1 and only 1 profile directly attachment to the party
3083:
3084: FND_MESSAGE.SET_NAME( 'AR', 'HZ_ONLY_ONE_PROF_AT_PARTY_LEV' );
3085: FND_MESSAGE.SET_TOKEN( 'PARTY_ID', p_customer_profile_rec.party_id );
3086: FND_MSG_PUB.ADD;
3087: x_return_status := FND_API.G_RET_STS_ERROR;
3088: EXCEPTION
3089: WHEN NO_DATA_FOUND THEN
3090: NULL;
3091: END;
3117: ---------------------------------
3118:
3119: -- Validate lookup_code of the REVIEW_CYCLE
3120: IF p_customer_profile_rec.review_cycle IS NOT NULL AND
3121: p_customer_profile_rec.review_cycle <> FND_API.G_MISS_CHAR AND
3122: ( p_create_update_flag = 'C' OR
3123: ( p_create_update_flag = 'U' AND
3124: p_customer_profile_rec.review_cycle <> NVL( l_review_cycle, FND_API.G_MISS_CHAR ) ) )
3125: THEN
3120: IF p_customer_profile_rec.review_cycle IS NOT NULL AND
3121: p_customer_profile_rec.review_cycle <> FND_API.G_MISS_CHAR AND
3122: ( p_create_update_flag = 'C' OR
3123: ( p_create_update_flag = 'U' AND
3124: p_customer_profile_rec.review_cycle <> NVL( l_review_cycle, FND_API.G_MISS_CHAR ) ) )
3125: THEN
3126: validate_lookup (
3127: p_column => 'review_cycle',
3128: p_lookup_table => 'AR_LOOKUPS',
3166: END IF;
3167:
3168: -- collector_id is foreign key to ar_collectors
3169: IF p_customer_profile_rec.collector_id IS NOT NULL AND
3170: p_customer_profile_rec.collector_id <> FND_API.G_MISS_NUM
3171: THEN
3172: check_collector_fk (
3173: p_column => 'collector_id',
3174: p_column_value => p_customer_profile_rec.collector_id,
3217:
3218:
3219: -- credit_checking is lookup code in lookup type YES/NO
3220: IF p_customer_profile_rec.credit_checking IS NOT NULL AND
3221: p_customer_profile_rec.credit_checking <> FND_API.G_MISS_CHAR
3222: THEN
3223: validate_lookup (
3224: p_column => 'credit_checking',
3225: p_lookup_type => 'YES/NO',
3270: /****Logical APIs - validation not required****/
3271: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3272: -- discount_terms is lookup code in lookup type YES/NO
3273: IF p_customer_profile_rec.discount_terms IS NOT NULL AND
3274: p_customer_profile_rec.discount_terms <> FND_API.G_MISS_CHAR
3275: AND ( p_create_update_flag = 'C' OR
3276: ( p_create_update_flag = 'U' AND
3277: p_customer_profile_rec.discount_terms <> NVL( l_discount_terms, FND_API.G_MISS_CHAR ) ) )
3278: THEN
3273: IF p_customer_profile_rec.discount_terms IS NOT NULL AND
3274: p_customer_profile_rec.discount_terms <> FND_API.G_MISS_CHAR
3275: AND ( p_create_update_flag = 'C' OR
3276: ( p_create_update_flag = 'U' AND
3277: p_customer_profile_rec.discount_terms <> NVL( l_discount_terms, FND_API.G_MISS_CHAR ) ) )
3278: THEN
3279: validate_lookup (
3280: p_column => 'discount_terms',
3281: p_lookup_type => 'YES/NO',
3330: ----------------------------------------------
3331:
3332: -- profile_class_id is foreign key to hz_cust_profile_classes
3333: IF p_customer_profile_rec.profile_class_id IS NOT NULL AND
3334: p_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM
3335: THEN
3336: IF p_customer_profile_rec.profile_class_id < 0 THEN
3337: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NEGATIVE_PROFILE_CLASS' );
3338: FND_MSG_PUB.ADD;
3335: THEN
3336: IF p_customer_profile_rec.profile_class_id < 0 THEN
3337: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NEGATIVE_PROFILE_CLASS' );
3338: FND_MSG_PUB.ADD;
3339: x_return_status := FND_API.G_RET_STS_ERROR;
3340: ELSE
3341: BEGIN
3342: SELECT STATUS, NAME INTO l_class_status, l_profile_class_name
3343: FROM HZ_CUST_PROFILE_CLASSES
3346: IF l_class_status <> 'A' THEN
3347: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INACTIVE_PROFILE_CLASS' );
3348: FND_MESSAGE.SET_TOKEN( 'NAME', l_profile_class_name );
3349: FND_MSG_PUB.ADD;
3350: x_return_status := FND_API.G_RET_STS_ERROR;
3351: END IF;
3352: EXCEPTION
3353: WHEN NO_DATA_FOUND THEN
3354: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
3355: FND_MESSAGE.SET_TOKEN( 'FK', 'profile_class_id' );
3356: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'profile_class_id' );
3357: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_profile_classes');
3358: FND_MSG_PUB.ADD;
3359: x_return_status := FND_API.G_RET_STS_ERROR;
3360: END;
3361: END IF;
3362:
3363: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3433:
3434:
3435: IF p_create_update_flag = 'C' AND
3436: p_customer_profile_rec.site_use_id IS NOT NULL AND
3437: p_customer_profile_rec.site_use_id <> FND_API.G_MISS_NUM AND
3438: p_customer_profile_rec.cust_account_id <> -1
3439: THEN
3440: BEGIN
3441:
3449: MO_GLOBAL.validate_orgid_pub_api(l_org_id,'N',l_return_status);
3450: EXCEPTION
3451: WHEN OTHERS
3452: THEN
3453: x_return_status := FND_API.G_RET_STS_ERROR;
3454: END;
3455:
3456: -- cust_account_id should be same as cust_account_id site_use_id belongs to.
3457: SELECT CUST_ACCOUNT_ID INTO l_cust_account_id
3460:
3461: IF l_cust_account_id <> p_customer_profile_rec.cust_account_id THEN
3462: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_ACCT_SITEUSE_MISMATCH' );
3463: FND_MSG_PUB.ADD;
3464: x_return_status := FND_API.G_RET_STS_ERROR;
3465: END IF;
3466:
3467: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3468: hz_utility_v2pub.debug(p_message=>'cust_account_id should be same as cust_account_id site_use_id belongs to. ' ||
3477: FND_MESSAGE.SET_TOKEN( 'FK', 'site_use_id' );
3478: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'site_use_id' );
3479: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_site_uses' );
3480: FND_MSG_PUB.ADD;
3481: x_return_status := FND_API.G_RET_STS_ERROR;
3482: END;
3483:
3484: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3485: hz_utility_v2pub.debug(p_message=>'site_use_id is foreign key to hz_cust_site_uses. ' ||
3498:
3499: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
3500: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id' );
3501: FND_MSG_PUB.ADD;
3502: x_return_status := FND_API.G_RET_STS_ERROR;
3503: EXCEPTION
3504: WHEN NO_DATA_FOUND THEN
3505: NULL;
3506: END;
3516:
3517: -- 2310474: Profile can not be at account site level if it is created at party level
3518: ELSIF p_create_update_flag = 'C' AND
3519: p_customer_profile_rec.site_use_id IS NOT NULL AND
3520: p_customer_profile_rec.site_use_id <> FND_API.G_MISS_NUM AND
3521: p_customer_profile_rec.cust_account_id = -1
3522: THEN
3523: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_SITE_PROF_AT_PTY_LEV' );
3524: FND_MESSAGE.SET_TOKEN( 'PARTY_ID', p_customer_profile_rec.party_id );
3523: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_SITE_PROF_AT_PTY_LEV' );
3524: FND_MESSAGE.SET_TOKEN( 'PARTY_ID', p_customer_profile_rec.party_id );
3525: FND_MESSAGE.SET_TOKEN( 'SITE_USE_ID', p_customer_profile_rec.site_use_id );
3526: FND_MSG_PUB.ADD;
3527: x_return_status := FND_API.G_RET_STS_ERROR;
3528:
3529:
3530: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3531: hz_utility_v2pub.debug(p_message=>'profile at party level cannot be assign to a site. ' ||
3558: ----------------------------------------------
3559:
3560: -- credit_rating is lookup code in lookup type CREDIT_RATING
3561: IF p_customer_profile_rec.credit_rating IS NOT NULL AND
3562: p_customer_profile_rec.credit_rating <> FND_API.G_MISS_CHAR AND
3563: ( p_create_update_flag = 'C' OR
3564: ( p_create_update_flag = 'U' AND
3565: p_customer_profile_rec.credit_rating <> NVL( l_credit_rating, FND_API.G_MISS_CHAR ) ) )
3566: THEN
3561: IF p_customer_profile_rec.credit_rating IS NOT NULL AND
3562: p_customer_profile_rec.credit_rating <> FND_API.G_MISS_CHAR AND
3563: ( p_create_update_flag = 'C' OR
3564: ( p_create_update_flag = 'U' AND
3565: p_customer_profile_rec.credit_rating <> NVL( l_credit_rating, FND_API.G_MISS_CHAR ) ) )
3566: THEN
3567: validate_lookup (
3568: p_column => 'credit_rating',
3569: p_lookup_type => 'CREDIT_RATING',
3593: ----------------------------------------------
3594:
3595: -- risk_code is lookup code in lookup type RISK_CODE
3596: IF p_customer_profile_rec.risk_code IS NOT NULL AND
3597: p_customer_profile_rec.risk_code <> FND_API.G_MISS_CHAR AND
3598: ( p_create_update_flag = 'C' OR
3599: ( p_create_update_flag = 'U' AND
3600: p_customer_profile_rec.risk_code <> NVL( l_risk_code, FND_API.G_MISS_CHAR ) ) )
3601: THEN
3596: IF p_customer_profile_rec.risk_code IS NOT NULL AND
3597: p_customer_profile_rec.risk_code <> FND_API.G_MISS_CHAR AND
3598: ( p_create_update_flag = 'C' OR
3599: ( p_create_update_flag = 'U' AND
3600: p_customer_profile_rec.risk_code <> NVL( l_risk_code, FND_API.G_MISS_CHAR ) ) )
3601: THEN
3602: validate_lookup (
3603: p_column => 'risk_code',
3604: p_lookup_type => 'RISK_CODE',
3647:
3648:
3649: -- auto_rec_incl_disputed_flag is lookup code in lookup type YES/NO
3650: IF p_customer_profile_rec.auto_rec_incl_disputed_flag IS NOT NULL AND
3651: p_customer_profile_rec.auto_rec_incl_disputed_flag <> FND_API.G_MISS_CHAR
3652: THEN
3653: validate_lookup (
3654: p_column => 'auto_rec_incl_disputed_flag',
3655: p_lookup_type => 'YES/NO',
3682: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3683: -- charge_on_finance_charge_flag is lookup code in lookup type YES/NO
3684: -- As a part of late charge policy project changing the lookup type to AR_FORMULAE (bug # 5065436)
3685: IF p_customer_profile_rec.charge_on_finance_charge_flag IS NOT NULL AND
3686: p_customer_profile_rec.charge_on_finance_charge_flag <> FND_API.G_MISS_CHAR
3687: THEN
3688: validate_lookup (
3689: p_column => 'charge_on_finance_charge_flag',
3690: p_lookup_type => 'AR_FORMULAE',
3714: /****Logical APIs - validation not required****/
3715: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3716: -- cons_inv_flag is lookup code in lookup type YES/NO
3717: IF p_customer_profile_rec.cons_inv_flag IS NOT NULL AND
3718: p_customer_profile_rec.cons_inv_flag <> FND_API.G_MISS_CHAR
3719: THEN
3720: validate_lookup (
3721: p_column => 'cons_inv_flag',
3722: p_lookup_type => 'YES/NO',
3786: /****Logical APIs - validation not required****/
3787: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3788: -- status is lookup code in lookup type CODE_STATUS
3789: IF p_customer_profile_rec.status IS NOT NULL AND
3790: p_customer_profile_rec.status <> FND_API.G_MISS_CHAR AND
3791: ( p_create_update_flag = 'C' OR
3792: ( p_create_update_flag = 'U' AND
3793: p_customer_profile_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
3794: THEN
3789: IF p_customer_profile_rec.status IS NOT NULL AND
3790: p_customer_profile_rec.status <> FND_API.G_MISS_CHAR AND
3791: ( p_create_update_flag = 'C' OR
3792: ( p_create_update_flag = 'U' AND
3793: p_customer_profile_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
3794: THEN
3795: validate_lookup (
3796: p_column => 'status',
3797: p_lookup_type => 'CODE_STATUS',
3841:
3842:
3843: -- dunning_letters is lookup code in lookup type YES/NO
3844: IF p_customer_profile_rec.dunning_letters IS NOT NULL AND
3845: p_customer_profile_rec.dunning_letters <> FND_API.G_MISS_CHAR
3846: AND ( p_create_update_flag = 'C' OR
3847: ( p_create_update_flag = 'U' AND
3848: p_customer_profile_rec.dunning_letters <> NVL( l_dunning_letters, FND_API.G_MISS_CHAR ) ) )
3849: THEN
3844: IF p_customer_profile_rec.dunning_letters IS NOT NULL AND
3845: p_customer_profile_rec.dunning_letters <> FND_API.G_MISS_CHAR
3846: AND ( p_create_update_flag = 'C' OR
3847: ( p_create_update_flag = 'U' AND
3848: p_customer_profile_rec.dunning_letters <> NVL( l_dunning_letters, FND_API.G_MISS_CHAR ) ) )
3849: THEN
3850: validate_lookup (
3851: p_column => 'dunning_letters',
3852: p_lookup_type => 'YES/NO',
3896: /****Logical APIs - validation not required****/
3897: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3898: -- interest_charges is lookup code in lookup type YES/NO
3899: IF p_customer_profile_rec.interest_charges IS NOT NULL AND
3900: p_customer_profile_rec.interest_charges <> FND_API.G_MISS_CHAR
3901: AND ( p_create_update_flag = 'C' OR
3902: ( p_create_update_flag = 'U' AND
3903: p_customer_profile_rec.interest_charges <> NVL( l_interest_charges, FND_API.G_MISS_CHAR ) ) )
3904: THEN
3899: IF p_customer_profile_rec.interest_charges IS NOT NULL AND
3900: p_customer_profile_rec.interest_charges <> FND_API.G_MISS_CHAR
3901: AND ( p_create_update_flag = 'C' OR
3902: ( p_create_update_flag = 'U' AND
3903: p_customer_profile_rec.interest_charges <> NVL( l_interest_charges, FND_API.G_MISS_CHAR ) ) )
3904: THEN
3905: validate_lookup (
3906: p_column => 'interest_charges',
3907: p_lookup_type => 'YES/NO',
3953: /****Logical APIs - validation not required****/
3954: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
3955: -- send_statements is lookup code in lookup type YES/NO
3956: IF p_customer_profile_rec.send_statements IS NOT NULL AND
3957: p_customer_profile_rec.send_statements <> FND_API.G_MISS_CHAR
3958: AND ( p_create_update_flag = 'C' OR
3959: ( p_create_update_flag = 'U' AND
3960: p_customer_profile_rec.send_statements <> NVL( l_send_statements, FND_API.G_MISS_CHAR ) ) )
3961: THEN
3956: IF p_customer_profile_rec.send_statements IS NOT NULL AND
3957: p_customer_profile_rec.send_statements <> FND_API.G_MISS_CHAR
3958: AND ( p_create_update_flag = 'C' OR
3959: ( p_create_update_flag = 'U' AND
3960: p_customer_profile_rec.send_statements <> NVL( l_send_statements, FND_API.G_MISS_CHAR ) ) )
3961: THEN
3962: validate_lookup (
3963: p_column => 'send_statements',
3964: p_lookup_type => 'YES/NO',
4011: /****Logical APIs - validation not required****/
4012: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
4013: -- credit_balance_statements is lookup code in lookup type YES/NO
4014: IF p_customer_profile_rec.credit_balance_statements IS NOT NULL AND
4015: p_customer_profile_rec.credit_balance_statements <> FND_API.G_MISS_CHAR
4016: THEN
4017: validate_lookup (
4018: p_column => 'credit_balance_statements',
4019: p_lookup_type => 'YES/NO',
4065: /****Logical APIs - validation not required****/
4066: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
4067: -- credit_hold is lookup code in lookup type YES/NO
4068: IF p_customer_profile_rec.credit_hold IS NOT NULL AND
4069: p_customer_profile_rec.credit_hold <> FND_API.G_MISS_CHAR
4070: THEN
4071: validate_lookup (
4072: p_column => 'credit_hold',
4073: p_lookup_type => 'YES/NO',
4115: ------------------------------------------
4116: ----*** Select fields to validate ***----
4117: ------------------------------------------
4118: IF p_customer_profile_rec.profile_class_id IS NOT NULL AND
4119: p_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM
4120: THEN
4121:
4122: SELECT * INTO l_profile_class_rec
4123: FROM HZ_CUST_PROFILE_CLASSES
4128: ------------------------------------------
4129: ---*** Account Status Validation ***---
4130: ------------------------------------------
4131: IF p_customer_profile_rec.account_status IS NOT NULL AND
4132: p_customer_profile_rec.account_status <> FND_API.G_MISS_CHAR
4133: AND ( p_create_update_flag = 'C' OR
4134: ( p_create_update_flag = 'U' AND
4135: p_customer_profile_rec.account_status <> NVL( l_account_status, FND_API.G_MISS_CHAR ) ) )
4136: THEN
4131: IF p_customer_profile_rec.account_status IS NOT NULL AND
4132: p_customer_profile_rec.account_status <> FND_API.G_MISS_CHAR
4133: AND ( p_create_update_flag = 'C' OR
4134: ( p_create_update_flag = 'U' AND
4135: p_customer_profile_rec.account_status <> NVL( l_account_status, FND_API.G_MISS_CHAR ) ) )
4136: THEN
4137: validate_lookup (
4138: p_column => 'account_status',
4139: p_lookup_type => 'ACCOUNT_STATUS',
4160: ---------------------------------------
4161: ------***Tolerance Validation ***------
4162: ---------------------------------------
4163: IF p_customer_profile_rec.tolerance IS NOT NULL AND
4164: p_customer_profile_rec.tolerance <> FND_API.G_MISS_NUM
4165: AND ( p_create_update_flag = 'C' OR
4166: ( p_create_update_flag = 'U' AND
4167: p_customer_profile_rec.tolerance <> NVL( l_tolerance, FND_API.G_MISS_NUM ) ) )
4168: THEN
4163: IF p_customer_profile_rec.tolerance IS NOT NULL AND
4164: p_customer_profile_rec.tolerance <> FND_API.G_MISS_NUM
4165: AND ( p_create_update_flag = 'C' OR
4166: ( p_create_update_flag = 'U' AND
4167: p_customer_profile_rec.tolerance <> NVL( l_tolerance, FND_API.G_MISS_NUM ) ) )
4168: THEN
4169: IF p_customer_profile_rec.tolerance > 100 OR
4170: p_customer_profile_rec.tolerance < -100
4171: THEN
4173: FND_MESSAGE.SET_TOKEN( 'COLUMN','tolerance');
4174: FND_MESSAGE.SET_TOKEN( 'VALUE1', '-100' );
4175: FND_MESSAGE.SET_TOKEN( 'VALUE2', '100' );
4176: FND_MSG_PUB.ADD;
4177: x_return_status := FND_API.G_RET_STS_ERROR;
4178: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4179: hz_utility_v2pub.debug(p_message=>'Tolerance should be between -100 and 100 .' ||
4180: 'x_return_status = ' || x_return_status,
4181: p_prefix =>l_debug_prefix,
4196: -------------------------------------------------
4197: ---*** Validating Percent_Collectable ***---
4198: -------------------------------------------------
4199: IF p_customer_profile_rec.percent_collectable IS NOT NULL AND
4200: p_customer_profile_rec.percent_collectable <> FND_API.G_MISS_NUM
4201: AND ( p_create_update_flag = 'C' OR
4202: ( p_create_update_flag = 'U' AND
4203: p_customer_profile_rec.percent_collectable <> NVL( l_percent_collectable, FND_API.G_MISS_NUM ) ) )
4204: THEN
4199: IF p_customer_profile_rec.percent_collectable IS NOT NULL AND
4200: p_customer_profile_rec.percent_collectable <> FND_API.G_MISS_NUM
4201: AND ( p_create_update_flag = 'C' OR
4202: ( p_create_update_flag = 'U' AND
4203: p_customer_profile_rec.percent_collectable <> NVL( l_percent_collectable, FND_API.G_MISS_NUM ) ) )
4204: THEN
4205: IF p_customer_profile_rec.percent_collectable > 100 OR
4206: p_customer_profile_rec.percent_collectable < 0
4207: THEN
4209: FND_MESSAGE.SET_TOKEN( 'COLUMN','percent_collectable');
4210: FND_MESSAGE.SET_TOKEN( 'VALUE1', '0' );
4211: FND_MESSAGE.SET_TOKEN( 'VALUE2', '100' );
4212: FND_MSG_PUB.ADD;
4213: x_return_status := FND_API.G_RET_STS_ERROR;
4214:
4215: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4216: hz_utility_v2pub.debug(p_message=>'Percent_Collectable should be between 0 and 100 .' ||
4217: 'x_return_status = ' || x_return_status,
4234: ---*** Validating Override_Terms ***---
4235: -----------------------------------------
4236: -- override_terms is lookup code in lookup type YES/NO
4237: IF p_customer_profile_rec.override_terms IS NOT NULL AND
4238: p_customer_profile_rec.override_terms <> FND_API.G_MISS_CHAR
4239: AND ( p_create_update_flag = 'C' OR
4240: ( p_create_update_flag = 'U' AND
4241: p_customer_profile_rec.override_terms <> NVL( l_override_terms, FND_API.G_MISS_CHAR) ) )
4242: THEN
4237: IF p_customer_profile_rec.override_terms IS NOT NULL AND
4238: p_customer_profile_rec.override_terms <> FND_API.G_MISS_CHAR
4239: AND ( p_create_update_flag = 'C' OR
4240: ( p_create_update_flag = 'U' AND
4241: p_customer_profile_rec.override_terms <> NVL( l_override_terms, FND_API.G_MISS_CHAR) ) )
4242: THEN
4243: validate_lookup (
4244: p_column => 'override_terms',
4245: p_lookup_type => 'YES/NO',
4267: ----------------------------------------------
4268: ---***Validating lockbox_matching_option***---
4269: ----------------------------------------------
4270: IF p_customer_profile_rec.lockbox_matching_option IS NOT NULL AND
4271: p_customer_profile_rec.lockbox_matching_option <> FND_API.G_MISS_CHAR
4272: AND ( p_create_update_flag = 'C' OR
4273: ( p_create_update_flag = 'U' AND
4274: p_customer_profile_rec.lockbox_matching_option <> NVL( l_lockbox_matching_option, FND_API.G_MISS_CHAR) ) )
4275: THEN
4270: IF p_customer_profile_rec.lockbox_matching_option IS NOT NULL AND
4271: p_customer_profile_rec.lockbox_matching_option <> FND_API.G_MISS_CHAR
4272: AND ( p_create_update_flag = 'C' OR
4273: ( p_create_update_flag = 'U' AND
4274: p_customer_profile_rec.lockbox_matching_option <> NVL( l_lockbox_matching_option, FND_API.G_MISS_CHAR) ) )
4275: THEN
4276: validate_lookup (
4277: p_column => 'lockbox_matching_option',
4278: p_lookup_type => 'ARLPLB_MATCHING_OPTION',
4300: --------------------------------------------
4301: --*** Validating autocash_hierarchy_id***---
4302: --------------------------------------------
4303: IF p_customer_profile_rec.autocash_hierarchy_id IS NOT NULL AND
4304: p_customer_profile_rec.autocash_hierarchy_id <> FND_API.G_MISS_NUM
4305: AND ( p_create_update_flag = 'C' OR
4306: ( p_create_update_flag = 'U' AND
4307: p_customer_profile_rec.autocash_hierarchy_id <> NVL( l_autocash_hierarchy_id, FND_API.G_MISS_NUM) ) )
4308: THEN
4303: IF p_customer_profile_rec.autocash_hierarchy_id IS NOT NULL AND
4304: p_customer_profile_rec.autocash_hierarchy_id <> FND_API.G_MISS_NUM
4305: AND ( p_create_update_flag = 'C' OR
4306: ( p_create_update_flag = 'U' AND
4307: p_customer_profile_rec.autocash_hierarchy_id <> NVL( l_autocash_hierarchy_id, FND_API.G_MISS_NUM) ) )
4308: THEN
4309: check_auto_hierid_fk (
4310: p_column => 'autocash_hierarchy_id',
4311: p_column_value => p_customer_profile_rec.autocash_hierarchy_id,
4332: ---------------------------------------------------------
4333: -----*** Validating autocash_hierarchy_id_for_adr***-----
4334: ---------------------------------------------------------
4335: IF p_customer_profile_rec.autocash_hierarchy_id_for_adr IS NOT NULL AND
4336: p_customer_profile_rec.autocash_hierarchy_id_for_adr <> FND_API.G_MISS_NUM
4337: AND ( p_create_update_flag = 'C' OR
4338: ( p_create_update_flag = 'U' AND
4339: p_customer_profile_rec.autocash_hierarchy_id_for_adr<> NVL( l_autocash_hierarchy_id_for_ad, FND_API.G_MISS_NUM) ) )
4340: THEN
4335: IF p_customer_profile_rec.autocash_hierarchy_id_for_adr IS NOT NULL AND
4336: p_customer_profile_rec.autocash_hierarchy_id_for_adr <> FND_API.G_MISS_NUM
4337: AND ( p_create_update_flag = 'C' OR
4338: ( p_create_update_flag = 'U' AND
4339: p_customer_profile_rec.autocash_hierarchy_id_for_adr<> NVL( l_autocash_hierarchy_id_for_ad, FND_API.G_MISS_NUM) ) )
4340: THEN
4341: check_auto_hierid_fk (
4342: p_column => 'autocash_hierarchy_id_for_adr',
4343: p_column_value => p_customer_profile_rec.autocash_hierarchy_id_for_adr,
4363: --------------------------------------------
4364: ---*** Validating statement_cycle_id ***---
4365: --------------------------------------------
4366: IF p_customer_profile_rec.statement_cycle_id IS NOT NULL AND
4367: p_customer_profile_rec.statement_cycle_id <> FND_API.G_MISS_NUM
4368: AND ( p_create_update_flag = 'C' OR
4369: ( p_create_update_flag = 'U' AND
4370: p_customer_profile_rec.statement_cycle_id <> NVL( l_statement_cycle_id, FND_API.G_MISS_NUM) ) )
4371: THEN
4366: IF p_customer_profile_rec.statement_cycle_id IS NOT NULL AND
4367: p_customer_profile_rec.statement_cycle_id <> FND_API.G_MISS_NUM
4368: AND ( p_create_update_flag = 'C' OR
4369: ( p_create_update_flag = 'U' AND
4370: p_customer_profile_rec.statement_cycle_id <> NVL( l_statement_cycle_id, FND_API.G_MISS_NUM) ) )
4371: THEN
4372: check_stat_cycid_fk (
4373: p_column => 'statement_cycle_id',
4374: p_column_value => p_customer_profile_rec.statement_cycle_id,
4395: ---------------------------------------------
4396: --------------*** Clearing Days***-----------
4397: ---------------------------------------------
4398: IF p_customer_profile_rec.clearing_days IS NOT NULL AND
4399: p_customer_profile_rec.clearing_days <> FND_API.G_MISS_NUM
4400: AND ( p_create_update_flag = 'C' OR
4401: ( p_create_update_flag = 'U' AND
4402: p_customer_profile_rec.clearing_days <> NVL( l_clearing_days, FND_API.G_MISS_NUM) ) )
4403: THEN
4398: IF p_customer_profile_rec.clearing_days IS NOT NULL AND
4399: p_customer_profile_rec.clearing_days <> FND_API.G_MISS_NUM
4400: AND ( p_create_update_flag = 'C' OR
4401: ( p_create_update_flag = 'U' AND
4402: p_customer_profile_rec.clearing_days <> NVL( l_clearing_days, FND_API.G_MISS_NUM) ) )
4403: THEN
4404: -- Error
4405: check_positive_value (
4406: p_column => 'clearing_days',
4426: ---------------------------------------------
4427: ------------*** Payment_grace_days***--------
4428: ---------------------------------------------
4429: IF p_customer_profile_rec.payment_grace_days IS NOT NULL AND
4430: p_customer_profile_rec.payment_grace_days <> FND_API.G_MISS_NUM
4431: AND ( p_create_update_flag = 'C' OR
4432: ( p_create_update_flag = 'U' AND
4433: p_customer_profile_rec.payment_grace_days <> NVL( l_payment_grace_days, FND_API.G_MISS_NUM) ) )
4434: THEN
4429: IF p_customer_profile_rec.payment_grace_days IS NOT NULL AND
4430: p_customer_profile_rec.payment_grace_days <> FND_API.G_MISS_NUM
4431: AND ( p_create_update_flag = 'C' OR
4432: ( p_create_update_flag = 'U' AND
4433: p_customer_profile_rec.payment_grace_days <> NVL( l_payment_grace_days, FND_API.G_MISS_NUM) ) )
4434: THEN
4435: check_positive_value (
4436: p_column => 'payment_grace_days',
4437: p_column_value => p_customer_profile_rec.payment_grace_days,
4457: ---------------------------------------------
4458: -----------***Interest_period_day***---------
4459: ---------------------------------------------
4460: IF p_customer_profile_rec.interest_period_days IS NOT NULL AND
4461: p_customer_profile_rec.interest_period_days <> FND_API.G_MISS_NUM
4462: THEN
4463: check_greater_than_zero (
4464: p_column => 'interest_period_days',
4465: p_column_value => p_customer_profile_rec.interest_period_days,
4485: --------------------------------------------
4486: ---***Validating dunning_letter_set_id***---
4487: --------------------------------------------
4488: IF p_customer_profile_rec.dunning_letter_set_id IS NOT NULL AND
4489: p_customer_profile_rec.dunning_letter_set_id <> FND_API.G_MISS_NUM
4490: THEN
4491: /* Bug 5095478. DUNNING_LETTER_SET_ID is obsoleted.
4492: This validation is changed to throw an error if user passes the
4493: DUNNING_LETTER_SET_ID
4508: THEN
4509: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OBSOLETE_COLUMN');
4510: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dunning_letter_set_id' );
4511: FND_MSG_PUB.ADD;
4512: x_return_status := FND_API.G_RET_STS_ERROR;
4513: END IF;
4514:
4515: END IF;
4516:
4524: --------------------------------------------
4525: ---*** Validating tax_printing_option ***---
4526: --------------------------------------------
4527: IF p_customer_profile_rec.tax_printing_option IS NOT NULL AND
4528: p_customer_profile_rec.tax_printing_option <> FND_API.G_MISS_CHAR
4529: AND ( p_create_update_flag = 'C' OR
4530: ( p_create_update_flag = 'U' AND
4531: p_customer_profile_rec.tax_printing_option <> NVL( l_tax_printing_option, FND_API.G_MISS_CHAR ) ))
4532: THEN
4527: IF p_customer_profile_rec.tax_printing_option IS NOT NULL AND
4528: p_customer_profile_rec.tax_printing_option <> FND_API.G_MISS_CHAR
4529: AND ( p_create_update_flag = 'C' OR
4530: ( p_create_update_flag = 'U' AND
4531: p_customer_profile_rec.tax_printing_option <> NVL( l_tax_printing_option, FND_API.G_MISS_CHAR ) ))
4532: THEN
4533: validate_lookup (
4534: p_column => 'tax_printing_option',
4535: p_lookup_type => 'TAX_PRINTING_OPTION',
4557: ----------------------------------------
4558: ----*** Validating standard_terms***---
4559: ----------------------------------------
4560: IF p_customer_profile_rec.standard_terms IS NOT NULL AND
4561: p_customer_profile_rec.standard_terms <> FND_API.G_MISS_NUM
4562: AND ( p_create_update_flag = 'C' OR
4563: ( p_create_update_flag = 'U' AND
4564: p_customer_profile_rec.standard_terms <> NVL( l_standard_terms, FND_API.G_MISS_NUM ) ))
4565: THEN
4560: IF p_customer_profile_rec.standard_terms IS NOT NULL AND
4561: p_customer_profile_rec.standard_terms <> FND_API.G_MISS_NUM
4562: AND ( p_create_update_flag = 'C' OR
4563: ( p_create_update_flag = 'U' AND
4564: p_customer_profile_rec.standard_terms <> NVL( l_standard_terms, FND_API.G_MISS_NUM ) ))
4565: THEN
4566: l_ckeck_acc_bfb_enabled := 'N';
4567: /* Commented the below code and added new set of validations below
4568: for enhancement # 5529044
4569: -- When Updating a BFB enabled site use profile which has a null payment term id
4570: -- with a new payment term id check that the payment term id is a BFB payment term
4571: -- if BFB is enabled for the account profile and check that the payment term is
4572: -- a non BFB payment term if BFB is disabled for the Account profile.
4573: if (p_customer_profile_rec.site_use_id is NOT NULL and p_customer_profile_rec.site_use_id <> FND_API.G_MISS_NUM
4574: and p_create_update_flag = 'U' )then
4575: l_ckeck_acc_bfb_enabled := 'Y';
4576: ll_cons_inv_flag := 'N';
4577: begin
4642: -------------------------------------------
4643: ---*** Validating grouping_rule_id ***---
4644: -------------------------------------------
4645: IF p_customer_profile_rec.grouping_rule_id IS NOT NULL AND
4646: p_customer_profile_rec.grouping_rule_id <> FND_API.G_MISS_NUM
4647: AND ( p_create_update_flag = 'C' OR
4648: ( p_create_update_flag = 'U' AND
4649: p_customer_profile_rec.grouping_rule_id <> NVL( l_grouping_rule_id, FND_API.G_MISS_NUM ) ))
4650:
4645: IF p_customer_profile_rec.grouping_rule_id IS NOT NULL AND
4646: p_customer_profile_rec.grouping_rule_id <> FND_API.G_MISS_NUM
4647: AND ( p_create_update_flag = 'C' OR
4648: ( p_create_update_flag = 'U' AND
4649: p_customer_profile_rec.grouping_rule_id <> NVL( l_grouping_rule_id, FND_API.G_MISS_NUM ) ))
4650:
4651: THEN
4652: check_grouping_ruleid_fk (
4653: p_column => 'grouping_rule_id',
4673: -------------------------------------------------
4674: ------------*** Discount_grace_days **---------------
4675: -------------------------------------------------
4676: IF v_customer_profile_rec.discount_grace_days IS NOT NULL AND
4677: v_customer_profile_rec.discount_grace_days <> FND_API.G_MISS_NUM
4678: AND ( p_create_update_flag = 'C' OR
4679: ( p_create_update_flag = 'U' AND
4680: p_customer_profile_rec.discount_grace_days <> NVL( l_discount_grace_days, FND_API.G_MISS_NUM ) ))
4681: THEN
4676: IF v_customer_profile_rec.discount_grace_days IS NOT NULL AND
4677: v_customer_profile_rec.discount_grace_days <> FND_API.G_MISS_NUM
4678: AND ( p_create_update_flag = 'C' OR
4679: ( p_create_update_flag = 'U' AND
4680: p_customer_profile_rec.discount_grace_days <> NVL( l_discount_grace_days, FND_API.G_MISS_NUM ) ))
4681: THEN
4682: check_positive_value (
4683: p_column => 'discount_grace_days',
4684: p_column_value => v_customer_profile_rec.discount_grace_days,
4695:
4696: IF p_create_update_flag = 'C' OR
4697: (p_create_update_flag = 'U' AND
4698: v_customer_profile_rec.profile_class_id IS NOT NULL AND
4699: v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM)
4700: THEN
4701:
4702: IF v_customer_profile_rec.discount_terms IS NULL THEN
4703: v_customer_profile_rec.discount_terms :=
4728: IF v_customer_profile_rec.discount_terms = 'N' OR
4729: v_customer_profile_rec.discount_terms is NULL
4730: THEN
4731: IF v_customer_profile_rec.discount_grace_days >= 0 AND
4732: v_customer_profile_rec.discount_grace_days <> FND_API.G_MISS_NUM
4733: THEN
4734: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_DEP_FIELDS' );
4735: FND_MESSAGE.SET_TOKEN( 'COLUMN1', 'discount_terms' );
4736: FND_MESSAGE.SET_TOKEN( 'VALUE1', 'N' );
4736: FND_MESSAGE.SET_TOKEN( 'VALUE1', 'N' );
4737: FND_MESSAGE.SET_TOKEN( 'COLUMN2', 'discount_grace_days');
4738: FND_MESSAGE.SET_TOKEN( 'VALUE2', 'NULL' );
4739: FND_MSG_PUB.ADD;
4740: x_return_status := FND_API.G_RET_STS_ERROR;
4741:
4742: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4743: hz_utility_v2pub.debug(p_message=>'discount_grace_days should be null when discount_terms is N.' ||
4744: 'x_return_status = ' || x_return_status,
4763:
4764: IF p_create_update_flag = 'C' OR
4765: (p_create_update_flag = 'U' AND
4766: v_customer_profile_rec.profile_class_id IS NOT NULL AND
4767: v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM)
4768: THEN
4769: IF v_customer_profile_rec.dunning_letters IS NULL THEN
4770: v_customer_profile_rec.dunning_letters :=
4771: l_profile_class_rec.dunning_letters;
4795:
4796: /*Bug 5095478 the column dunning_letter_set_id is obsolete in R12
4797: IF v_customer_profile_rec.dunning_letters = 'Y'
4798: THEN
4799: IF v_customer_profile_rec.dunning_letter_set_id = FND_API.G_MISS_NUM OR
4800: v_customer_profile_rec.dunning_letter_set_id IS NULL
4801: THEN
4802: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MAND_DEP_FIELDS' );
4803: FND_MESSAGE.SET_TOKEN('COLUMN1', 'dunning_letters');
4803: FND_MESSAGE.SET_TOKEN('COLUMN1', 'dunning_letters');
4804: FND_MESSAGE.SET_TOKEN('VALUE1', 'Y');
4805: FND_MESSAGE.SET_TOKEN('COLUMN2','dunning_letter_set_id');
4806: FND_MSG_PUB.ADD;
4807: x_return_status := FND_API.G_RET_STS_ERROR;
4808:
4809: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4810: hz_utility_v2pub.debug(p_message=>'dunning_letter_set_id cannot be NULL when dunning_letters is Y.' ||
4811: 'x_return_status = ' || x_return_status,
4819: IF v_customer_profile_rec.dunning_letters = 'N' OR
4820: v_customer_profile_rec.dunning_letters IS NULL
4821: THEN
4822: IF v_customer_profile_rec.dunning_letter_set_id >= 0 AND
4823: v_customer_profile_rec.dunning_letter_set_id <> FND_API.G_MISS_NUM
4824: THEN
4825: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_DEP_FIELDS' );
4826: FND_MESSAGE.SET_TOKEN('COLUMN1','dunning_letters');
4827: FND_MESSAGE.SET_TOKEN('VALUE1','N');
4827: FND_MESSAGE.SET_TOKEN('VALUE1','N');
4828: FND_MESSAGE.SET_TOKEN('COLUMN2','dunning_letter_set_id');
4829: FND_MESSAGE.SET_TOKEN('VALUE2','NULL');
4830: FND_MSG_PUB.ADD;
4831: x_return_status := FND_API.G_RET_STS_ERROR;
4832:
4833: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4834: hz_utility_v2pub.debug(p_message=>'dunning_letter_set_id should be NULL when dunning_letters is N.' ||
4835: 'x_return_status = ' || x_return_status,
4853:
4854: IF p_create_update_flag = 'C' OR
4855: ( p_create_update_flag = 'U' AND
4856: v_customer_profile_rec.profile_class_id is not null AND
4857: v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM )
4858: THEN
4859:
4860: IF v_customer_profile_rec.send_statements IS NULL THEN
4861: v_customer_profile_rec.send_statements :=
4896: END IF;
4897: END IF;
4898:
4899: IF v_customer_profile_rec.send_statements = 'Y' THEN
4900: IF v_customer_profile_rec.statement_cycle_id = FND_API.G_MISS_NUM OR
4901: v_customer_profile_rec.statement_cycle_id IS NULL
4902: THEN
4903: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MAND_DEP_FIELDS' );
4904: FND_MESSAGE.SET_TOKEN( 'COLUMN1','send_statements');
4904: FND_MESSAGE.SET_TOKEN( 'COLUMN1','send_statements');
4905: FND_MESSAGE.SET_TOKEN( 'VALUE1','Y');
4906: FND_MESSAGE.SET_TOKEN( 'COLUMN2','statement_cycle_id');
4907: FND_MSG_PUB.ADD;
4908: x_return_status := FND_API.G_RET_STS_ERROR;
4909:
4910: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4911: hz_utility_v2pub.debug(p_message=>'statement_cycle_id cannot be NULL when send_statements
4912: is Y.' || 'x_return_status = ' || x_return_status,
4920: FND_MESSAGE.SET_TOKEN( 'COLUMN1','send_statements');
4921: FND_MESSAGE.SET_TOKEN( 'VALUE1','Y');
4922: FND_MESSAGE.SET_TOKEN( 'COLUMN2','credit_balance_statements');
4923: FND_MSG_PUB.ADD;
4924: x_return_status := FND_API.G_RET_STS_ERROR;
4925:
4926: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4927: hz_utility_v2pub.debug(p_message=>'credit_balance_statements cannot be NULL when
4928: send_statements is Y .' || 'x_return_status = ' ||
4937: IF v_customer_profile_rec.send_statements = 'N' OR
4938: v_customer_profile_rec.send_statements IS NULL
4939: THEN
4940: IF v_customer_profile_rec.statement_cycle_id >= 0 AND
4941: v_customer_profile_rec.statement_cycle_id <> FND_API.G_MISS_NUM
4942: THEN
4943: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_DEP_FIELDS' );
4944: FND_MESSAGE.SET_TOKEN('COLUMN1', 'send_statements');
4945: FND_MESSAGE.SET_TOKEN('VALUE1', 'N');
4945: FND_MESSAGE.SET_TOKEN('VALUE1', 'N');
4946: FND_MESSAGE.SET_TOKEN('COLUMN2','statement_cycle_id');
4947: FND_MESSAGE.SET_TOKEN('VALUE2', 'NULL');
4948: FND_MSG_PUB.ADD;
4949: x_return_status := FND_API.G_RET_STS_ERROR;
4950:
4951: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4952: hz_utility_v2pub.debug(p_message=>'statement_cycle_id should be NULL when send_statements
4953: is N .' || 'x_return_status = ' || x_return_status,
4964: FND_MESSAGE.SET_TOKEN('VALUE1','N');
4965: FND_MESSAGE.SET_TOKEN('COLUMN2','credit_balance_statements');
4966: FND_MESSAGE.SET_TOKEN('VALUE2','N');
4967: FND_MSG_PUB.ADD;
4968: x_return_status := FND_API.G_RET_STS_ERROR;
4969:
4970: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4971: hz_utility_v2pub.debug(p_message=>'credit_balance_statements should be N when send_statements
4972: is N .' || 'x_return_status = ' || x_return_status,
4992:
4993: IF p_create_update_flag = 'C' OR
4994: ( p_create_update_flag = 'U' AND
4995: v_customer_profile_rec.profile_class_id is not null AND
4996: v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM )
4997: THEN
4998:
4999: IF v_customer_profile_rec.interest_charges IS NULL THEN
5000: v_customer_profile_rec.interest_charges :=
5045: IF v_customer_profile_rec.interest_charges = 'Y' THEN
5046: -- Bug 5400422 : Validate only for few late_charge_calculation_trx
5047: IF v_customer_profile_rec.late_charge_calculation_trx IN
5048: ('LATE', 'OVERDUE_LATE', 'OVERDUE') THEN
5049: IF v_customer_profile_rec.interest_period_days = FND_API.G_MISS_NUM OR
5050: v_customer_profile_rec.interest_period_days IS NULL
5051: THEN
5052: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_INT_CHARGES_Y' );
5053: FND_MSG_PUB.ADD;
5050: v_customer_profile_rec.interest_period_days IS NULL
5051: THEN
5052: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_INT_CHARGES_Y' );
5053: FND_MSG_PUB.ADD;
5054: x_return_status := FND_API.G_RET_STS_ERROR;
5055: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5056: hz_utility_v2pub.debug(p_message=>'interest_period_days cannot be NULL when interest_charges is Y.' || 'x_return_status = ' || x_return_status,
5057: p_prefix =>l_debug_prefix,
5058: p_msg_level=>fnd_log.level_statement);
5082: IF v_customer_profile_rec.interest_charges = 'N' OR
5083: v_customer_profile_rec.interest_charges IS NULL
5084: THEN
5085: IF v_customer_profile_rec.interest_period_days >= 0 AND
5086: v_customer_profile_rec.interest_period_days <> FND_API.G_MISS_NUM
5087: THEN
5088: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_DEP_FIELDS' );
5089: FND_MESSAGE.SET_TOKEN( 'COLUMN1','interest_charges');
5090: FND_MESSAGE.SET_TOKEN( 'VALUE1','N');
5090: FND_MESSAGE.SET_TOKEN( 'VALUE1','N');
5091: FND_MESSAGE.SET_TOKEN( 'COLUMN2','interest_period_days');
5092: FND_MESSAGE.SET_TOKEN( 'VALUE2','NULL');
5093: FND_MSG_PUB.ADD;
5094: x_return_status := FND_API.G_RET_STS_ERROR;
5095:
5096: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5097: hz_utility_v2pub.debug(p_message=>'Interest_period_days should be NULL when interest_charges
5098: is N .' || 'x_return_status = ' || x_return_status,
5108: FND_MESSAGE.SET_TOKEN( 'VALUE1','N');
5109: FND_MESSAGE.SET_TOKEN( 'COLUMN2','charge_on_finance_charge_flag');
5110: FND_MESSAGE.SET_TOKEN( 'VALUE2','N');
5111: FND_MSG_PUB.ADD;
5112: x_return_status := FND_API.G_RET_STS_ERROR;
5113:
5114: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5115: hz_utility_v2pub.debug(p_message=>'charge_on_finance_charge_flag cannot be Y when
5116: interest_charges is N .' || 'x_return_status ='
5133: -- Credit_classification
5134: -----------------------------------------------------------------------
5135: -- Validate lookup_code of the CREDIT_CLASSIFICATION
5136: IF p_customer_profile_rec.credit_classification IS NOT NULL AND
5137: p_customer_profile_rec.credit_classification <> FND_API.G_MISS_CHAR AND
5138: ( p_create_update_flag = 'C' OR
5139: ( p_create_update_flag = 'U' AND
5140: p_customer_profile_rec.credit_classification <> NVL( l_credit_classification, FND_API.G_MISS_CHAR ) ) )
5141: THEN
5136: IF p_customer_profile_rec.credit_classification IS NOT NULL AND
5137: p_customer_profile_rec.credit_classification <> FND_API.G_MISS_CHAR AND
5138: ( p_create_update_flag = 'C' OR
5139: ( p_create_update_flag = 'U' AND
5140: p_customer_profile_rec.credit_classification <> NVL( l_credit_classification, FND_API.G_MISS_CHAR ) ) )
5141: THEN
5142: validate_lookup (
5143: p_column => 'credit_classification',
5144: p_lookup_table => 'AR_LOOKUPS',
5171: ----------------------------------------------
5172:
5173: -- LATE_CHARGE_CALCULATION_TRX is lookup code in lookup type AR_MANDATORY_LATE_CHARGES
5174: IF p_customer_profile_rec.late_charge_calculation_trx IS NOT NULL AND
5175: p_customer_profile_rec.late_charge_calculation_trx <> FND_API.G_MISS_CHAR AND
5176: ( p_create_update_flag = 'C' OR
5177: ( p_create_update_flag = 'U' AND
5178: p_customer_profile_rec.late_charge_calculation_trx <> NVL( l_late_charge_calculation_trx, FND_API.G_MISS_CHAR ) ) )
5179: THEN
5174: IF p_customer_profile_rec.late_charge_calculation_trx IS NOT NULL AND
5175: p_customer_profile_rec.late_charge_calculation_trx <> FND_API.G_MISS_CHAR AND
5176: ( p_create_update_flag = 'C' OR
5177: ( p_create_update_flag = 'U' AND
5178: p_customer_profile_rec.late_charge_calculation_trx <> NVL( l_late_charge_calculation_trx, FND_API.G_MISS_CHAR ) ) )
5179: THEN
5180: validate_lookup (
5181: p_column => 'late_charge_calculation_trx',
5182: p_lookup_type => 'AR_MANDATORY_LATE_CHARGES',
5204: ----------------------------------------------
5205:
5206: -- CREDIT_ITEMS_FLAG is lookup code in lookup type YES/NO
5207: IF p_customer_profile_rec.credit_items_flag IS NOT NULL AND
5208: p_customer_profile_rec.credit_items_flag <> FND_API.G_MISS_CHAR
5209: AND ( p_create_update_flag = 'C' OR
5210: ( p_create_update_flag = 'U' AND
5211: p_customer_profile_rec.credit_items_flag <> NVL( l_credit_items_flag, FND_API.G_MISS_CHAR ) ) )
5212: THEN
5207: IF p_customer_profile_rec.credit_items_flag IS NOT NULL AND
5208: p_customer_profile_rec.credit_items_flag <> FND_API.G_MISS_CHAR
5209: AND ( p_create_update_flag = 'C' OR
5210: ( p_create_update_flag = 'U' AND
5211: p_customer_profile_rec.credit_items_flag <> NVL( l_credit_items_flag, FND_API.G_MISS_CHAR ) ) )
5212: THEN
5213: validate_lookup (
5214: p_column => 'credit_items_flag',
5215: p_lookup_type => 'YES/NO',
5238: ----------------------------------------------
5239:
5240: -- DISPUTED_TRANSACTIONS_FLAG is lookup code in lookup type YES/NO
5241: IF p_customer_profile_rec.disputed_transactions_flag IS NOT NULL AND
5242: p_customer_profile_rec.disputed_transactions_flag <> FND_API.G_MISS_CHAR
5243: AND ( p_create_update_flag = 'C' OR
5244: ( p_create_update_flag = 'U' AND
5245: p_customer_profile_rec.disputed_transactions_flag <> NVL( l_disputed_transactions_flag, FND_API.G_MISS_CHAR ) ) )
5246: THEN
5241: IF p_customer_profile_rec.disputed_transactions_flag IS NOT NULL AND
5242: p_customer_profile_rec.disputed_transactions_flag <> FND_API.G_MISS_CHAR
5243: AND ( p_create_update_flag = 'C' OR
5244: ( p_create_update_flag = 'U' AND
5245: p_customer_profile_rec.disputed_transactions_flag <> NVL( l_disputed_transactions_flag, FND_API.G_MISS_CHAR ) ) )
5246: THEN
5247: validate_lookup (
5248: p_column => 'disputed_transactions_flag',
5249: p_lookup_type => 'YES/NO',
5272: ----------------------------------------------
5273:
5274: -- LATE_CHARGE_TYPE is lookup code in lookup type AR_LATE_CHARGE_TYPE
5275: IF p_customer_profile_rec.late_charge_type IS NOT NULL AND
5276: p_customer_profile_rec.late_charge_type <> FND_API.G_MISS_CHAR AND
5277: ( p_create_update_flag = 'C' OR
5278: ( p_create_update_flag = 'U' AND
5279: p_customer_profile_rec.late_charge_type <> NVL( l_late_charge_type, FND_API.G_MISS_CHAR ) ) )
5280: THEN
5275: IF p_customer_profile_rec.late_charge_type IS NOT NULL AND
5276: p_customer_profile_rec.late_charge_type <> FND_API.G_MISS_CHAR AND
5277: ( p_create_update_flag = 'C' OR
5278: ( p_create_update_flag = 'U' AND
5279: p_customer_profile_rec.late_charge_type <> NVL( l_late_charge_type, FND_API.G_MISS_CHAR ) ) )
5280: THEN
5281: validate_lookup (
5282: p_column => 'late_charge_type',
5283: p_lookup_type => 'AR_LATE_CHARGE_TYPE',
5305: ----------------------------------------------
5306:
5307: -- INTEREST_CALCULATION_PERIOD is lookup code in lookup type AR_CALCULATION_PERIOD
5308: IF p_customer_profile_rec.interest_calculation_period IS NOT NULL AND
5309: p_customer_profile_rec.interest_calculation_period <> FND_API.G_MISS_CHAR AND
5310: ( p_create_update_flag = 'C' OR
5311: ( p_create_update_flag = 'U' AND
5312: p_customer_profile_rec.interest_calculation_period <> NVL( l_interest_calculation_period, FND_API.G_MISS_CHAR ) ) )
5313: THEN
5308: IF p_customer_profile_rec.interest_calculation_period IS NOT NULL AND
5309: p_customer_profile_rec.interest_calculation_period <> FND_API.G_MISS_CHAR AND
5310: ( p_create_update_flag = 'C' OR
5311: ( p_create_update_flag = 'U' AND
5312: p_customer_profile_rec.interest_calculation_period <> NVL( l_interest_calculation_period, FND_API.G_MISS_CHAR ) ) )
5313: THEN
5314: validate_lookup (
5315: p_column => 'interest_calculation_period',
5316: p_lookup_type => 'AR_CALCULATION_PERIOD',
5338: ----------------------------------------------
5339:
5340: -- HOLD_CHARGED_INVOICES_FLAG is lookup code in lookup type YES/NO
5341: IF p_customer_profile_rec.hold_charged_invoices_flag IS NOT NULL AND
5342: p_customer_profile_rec.hold_charged_invoices_flag <> FND_API.G_MISS_CHAR
5343: AND ( p_create_update_flag = 'C' OR
5344: ( p_create_update_flag = 'U' AND
5345: p_customer_profile_rec.hold_charged_invoices_flag <> NVL( l_hold_charged_invoices_flag, FND_API.G_MISS_CHAR ) ) )
5346: THEN
5341: IF p_customer_profile_rec.hold_charged_invoices_flag IS NOT NULL AND
5342: p_customer_profile_rec.hold_charged_invoices_flag <> FND_API.G_MISS_CHAR
5343: AND ( p_create_update_flag = 'C' OR
5344: ( p_create_update_flag = 'U' AND
5345: p_customer_profile_rec.hold_charged_invoices_flag <> NVL( l_hold_charged_invoices_flag, FND_API.G_MISS_CHAR ) ) )
5346: THEN
5347: validate_lookup (
5348: p_column => 'hold_charged_invoices_flag',
5349: p_lookup_type => 'YES/NO',
5372: ----------------------------------------------
5373:
5374: -- MULTIPLE_INTEREST_RATES_FLAG is lookup code in lookup type YES/NO
5375: IF p_customer_profile_rec.multiple_interest_rates_flag IS NOT NULL AND
5376: p_customer_profile_rec.multiple_interest_rates_flag <> FND_API.G_MISS_CHAR
5377: AND ( p_create_update_flag = 'C' OR
5378: ( p_create_update_flag = 'U' AND
5379: p_customer_profile_rec.multiple_interest_rates_flag <> NVL( l_multiple_interest_rates_flag, FND_API.G_MISS_CHAR ) ) )
5380: THEN
5375: IF p_customer_profile_rec.multiple_interest_rates_flag IS NOT NULL AND
5376: p_customer_profile_rec.multiple_interest_rates_flag <> FND_API.G_MISS_CHAR
5377: AND ( p_create_update_flag = 'C' OR
5378: ( p_create_update_flag = 'U' AND
5379: p_customer_profile_rec.multiple_interest_rates_flag <> NVL( l_multiple_interest_rates_flag, FND_API.G_MISS_CHAR ) ) )
5380: THEN
5381: validate_lookup (
5382: p_column => 'multiple_interest_rates_flag',
5383: p_lookup_type => 'YES/NO',
5406: ------------------------------------
5407: -- 1. late_charge_term_id should be a valid value defined in RA_TERMS
5408: -- 2. Late charge payment term is required when late charge type is debit memo or interest invoice
5409:
5410: IF (((p_customer_profile_rec.late_charge_term_id IS NULL OR p_customer_profile_rec.late_charge_term_id = FND_API.G_MISS_NUM) AND
5411: (p_customer_profile_rec.late_charge_type = 'DM' OR
5412: p_customer_profile_rec.late_charge_type = 'INV')) OR
5413: ((p_customer_profile_rec.late_charge_type = 'ADJ') AND
5414: (p_customer_profile_rec.late_charge_term_id IS NOT NULL AND p_customer_profile_rec.late_charge_term_id <> FND_API.G_MISS_NUM))) THEN
5410: IF (((p_customer_profile_rec.late_charge_term_id IS NULL OR p_customer_profile_rec.late_charge_term_id = FND_API.G_MISS_NUM) AND
5411: (p_customer_profile_rec.late_charge_type = 'DM' OR
5412: p_customer_profile_rec.late_charge_type = 'INV')) OR
5413: ((p_customer_profile_rec.late_charge_type = 'ADJ') AND
5414: (p_customer_profile_rec.late_charge_term_id IS NOT NULL AND p_customer_profile_rec.late_charge_term_id <> FND_API.G_MISS_NUM))) THEN
5415: -- Late charge payment term is required when
5416: -- late charge type is debit memo or interest invoice
5417: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LC_PAY_TERM' );
5418: FND_MSG_PUB.ADD;
5415: -- Late charge payment term is required when
5416: -- late charge type is debit memo or interest invoice
5417: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LC_PAY_TERM' );
5418: FND_MSG_PUB.ADD;
5419: x_return_status := FND_API.G_RET_STS_ERROR;
5420: END IF;
5421:
5422: IF p_customer_profile_rec.late_charge_term_id is NOT NULL AND
5423: p_customer_profile_rec.late_charge_term_id <> FND_API.G_MISS_NUM
5419: x_return_status := FND_API.G_RET_STS_ERROR;
5420: END IF;
5421:
5422: IF p_customer_profile_rec.late_charge_term_id is NOT NULL AND
5423: p_customer_profile_rec.late_charge_term_id <> FND_API.G_MISS_NUM
5424: AND ( p_create_update_flag = 'C' OR
5425: ( p_create_update_flag = 'U' AND
5426: p_customer_profile_rec.late_charge_term_id <> NVL( l_late_charge_term_id, FND_API.G_MISS_NUM ) ) )
5427: THEN
5422: IF p_customer_profile_rec.late_charge_term_id is NOT NULL AND
5423: p_customer_profile_rec.late_charge_term_id <> FND_API.G_MISS_NUM
5424: AND ( p_create_update_flag = 'C' OR
5425: ( p_create_update_flag = 'U' AND
5426: p_customer_profile_rec.late_charge_term_id <> NVL( l_late_charge_term_id, FND_API.G_MISS_NUM ) ) )
5427: THEN
5428: check_late_charge_term_id_fk(
5429: p_column => 'payment_term_id',
5430: p_column_value => p_customer_profile_rec.late_charge_term_id,
5453: -- 1. message_text_id should be a valid value defined in AR_STANDARD_TEXT
5454: -- 2. Message text can be entered only when late charge type is debit memo or interest invoice
5455:
5456: IF ((p_customer_profile_rec.late_charge_type = 'ADJ') AND
5457: (p_customer_profile_rec.message_text_id IS NOT NULL AND p_customer_profile_rec.message_text_id <> FND_API.G_MISS_NUM)) THEN
5458: -- Message text can be entered only when
5459: -- late charge type is debit memo or interest invoice
5460: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_MSG_FOR_LC_TYPE' );
5461: FND_MSG_PUB.ADD;
5458: -- Message text can be entered only when
5459: -- late charge type is debit memo or interest invoice
5460: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_MSG_FOR_LC_TYPE' );
5461: FND_MSG_PUB.ADD;
5462: x_return_status := FND_API.G_RET_STS_ERROR;
5463: END IF;
5464:
5465: IF p_customer_profile_rec.message_text_id is NOT NULL AND
5466: p_customer_profile_rec.message_text_id <> FND_API.G_MISS_NUM
5462: x_return_status := FND_API.G_RET_STS_ERROR;
5463: END IF;
5464:
5465: IF p_customer_profile_rec.message_text_id is NOT NULL AND
5466: p_customer_profile_rec.message_text_id <> FND_API.G_MISS_NUM
5467: AND ( p_create_update_flag = 'C' OR
5468: ( p_create_update_flag = 'U' AND
5469: p_customer_profile_rec.message_text_id <> NVL( l_message_text_id, FND_API.G_MISS_NUM ) ) )
5470: THEN
5465: IF p_customer_profile_rec.message_text_id is NOT NULL AND
5466: p_customer_profile_rec.message_text_id <> FND_API.G_MISS_NUM
5467: AND ( p_create_update_flag = 'C' OR
5468: ( p_create_update_flag = 'U' AND
5469: p_customer_profile_rec.message_text_id <> NVL( l_message_text_id, FND_API.G_MISS_NUM ) ) )
5470: THEN
5471:
5472: check_message_text_id_fk(
5473: p_column => 'message_text_id',
5517: * p_cust_profile_amt_rec Customer profile amount record.
5518: * p_rowid Rowid of the record (used only in update mode).
5519: * IN/OUT:
5520: * x_return_status Return status after the call. The status can
5521: * be FND_API.G_RET_STS_SUCCESS (success),
5522: * FND_API.G_RET_STS_ERROR (error),
5523: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5524: *
5525: * NOTES
5518: * p_rowid Rowid of the record (used only in update mode).
5519: * IN/OUT:
5520: * x_return_status Return status after the call. The status can
5521: * be FND_API.G_RET_STS_SUCCESS (success),
5522: * FND_API.G_RET_STS_ERROR (error),
5523: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5524: *
5525: * NOTES
5526: *
5519: * IN/OUT:
5520: * x_return_status Return status after the call. The status can
5521: * be FND_API.G_RET_STS_SUCCESS (success),
5522: * FND_API.G_RET_STS_ERROR (error),
5523: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
5524: *
5525: * NOTES
5526: *
5527: * MODIFICATION HISTORY
5625: -- If primary key value is not passed, it will be generated
5626: -- from sequence by table handler.
5627:
5628: IF p_cust_profile_amt_rec.cust_acct_profile_amt_id IS NOT NULL AND
5629: p_cust_profile_amt_rec.cust_acct_profile_amt_id <> FND_API.G_MISS_NUM
5630: THEN
5631: BEGIN
5632: SELECT 'Y' INTO l_dummy
5633: FROM HZ_CUST_PROFILE_AMTS
5635:
5636: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
5637: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_profile_amt_id' );
5638: FND_MSG_PUB.ADD;
5639: x_return_status := FND_API.G_RET_STS_ERROR;
5640: EXCEPTION
5641: WHEN NO_DATA_FOUND THEN
5642: NULL;
5643: END;
5713: p_msg_level=>fnd_log.level_statement);
5714: END IF;
5715:
5716: IF p_create_update_flag = 'C' AND
5717: p_check_foreign_key = FND_API.G_TRUE AND
5718: p_cust_profile_amt_rec.cust_account_profile_id IS NOT NULL AND
5719: p_cust_profile_amt_rec.cust_account_profile_id <> FND_API.G_MISS_NUM
5720: THEN
5721: BEGIN
5715:
5716: IF p_create_update_flag = 'C' AND
5717: p_check_foreign_key = FND_API.G_TRUE AND
5718: p_cust_profile_amt_rec.cust_account_profile_id IS NOT NULL AND
5719: p_cust_profile_amt_rec.cust_account_profile_id <> FND_API.G_MISS_NUM
5720: THEN
5721: BEGIN
5722: -- select cust_account_id, site_use_id for later cross reference checking
5723: -- Bug 4770111 : select created_by_module also
5733: FND_MESSAGE.SET_TOKEN( 'FK', 'cust_account_profile_id' );
5734: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_profile_id' );
5735: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_customer_profiles' );
5736: FND_MSG_PUB.ADD;
5737: x_return_status := FND_API.G_RET_STS_ERROR;
5738: END;
5739:
5740: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5741: hz_utility_v2pub.debug(p_message=>'cust_account_profile_id is foreign key to hz_customer_profiles. ' ||
5807: p_msg_level=>fnd_log.level_statement);
5808: END IF;
5809:
5810: IF p_create_update_flag = 'C' AND
5811: p_check_foreign_key = FND_API.G_TRUE AND
5812: p_cust_profile_amt_rec.cust_account_id IS NOT NULL AND
5813: p_cust_profile_amt_rec.cust_account_id <> FND_API.G_MISS_NUM
5814: THEN
5815: check_cust_account_fk (
5809:
5810: IF p_create_update_flag = 'C' AND
5811: p_check_foreign_key = FND_API.G_TRUE AND
5812: p_cust_profile_amt_rec.cust_account_id IS NOT NULL AND
5813: p_cust_profile_amt_rec.cust_account_id <> FND_API.G_MISS_NUM
5814: THEN
5815: check_cust_account_fk (
5816: p_column => 'cust_account_id',
5817: p_column_value => p_cust_profile_amt_rec.cust_account_id,
5829: IF p_cust_profile_amt_rec.cust_account_id <> l_cust_account_id THEN
5830: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_PROF_AMT_IDS_MISMATCH' );
5831: FND_MESSAGE.SET_TOKEN( 'ENTITY', 'customer' );
5832: FND_MSG_PUB.ADD;
5833: x_return_status := FND_API.G_RET_STS_ERROR;
5834: END IF;
5835:
5836: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5837: hz_utility_v2pub.debug(p_message=>'cust_account_id should be the same as cust_account_id in corresponding customer profile. ' ||
5896: -- we only need to check FK during creation.
5897:
5898: IF p_create_update_flag = 'C' AND
5899: p_cust_profile_amt_rec.currency_code IS NOT NULL AND
5900: p_cust_profile_amt_rec.currency_code <> FND_API.G_MISS_CHAR
5901: THEN
5902: check_currency_fk (
5903: p_column => 'currency_code',
5904: p_column_value => p_cust_profile_amt_rec.currency_code,
5928:
5929: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
5930: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_profile_id - currency_code' );
5931: FND_MSG_PUB.ADD;
5932: x_return_status := FND_API.G_RET_STS_ERROR;
5933: EXCEPTION
5934: WHEN NO_DATA_FOUND THEN
5935: NULL;
5936: END;
5977: -- Since site_use_id is non-updateable, we only need to
5978: -- check FK during creation.
5979:
5980: IF p_create_update_flag = 'C' AND
5981: p_check_foreign_key = FND_API.G_TRUE
5982: THEN
5983: IF p_cust_profile_amt_rec.site_use_id IS NOT NULL AND
5984: p_cust_profile_amt_rec.site_use_id <> FND_API.G_MISS_NUM
5985: THEN
5980: IF p_create_update_flag = 'C' AND
5981: p_check_foreign_key = FND_API.G_TRUE
5982: THEN
5983: IF p_cust_profile_amt_rec.site_use_id IS NOT NULL AND
5984: p_cust_profile_amt_rec.site_use_id <> FND_API.G_MISS_NUM
5985: THEN
5986: check_cust_site_use_fk (
5987: p_column => 'site_use_id',
5988: p_column_value => p_cust_profile_amt_rec.site_use_id,
5998:
5999: -- cross reference checking
6000:
6001: IF ( p_cust_profile_amt_rec.site_use_id IS NOT NULL
6002: AND p_cust_profile_amt_rec.site_use_id <> FND_API.G_MISS_NUM AND
6003: ( l_site_use_id IS NULL OR
6004: l_site_use_id <> p_cust_profile_amt_rec.site_use_id ) ) OR
6005: ( ( p_cust_profile_amt_rec.site_use_id IS NULL OR
6006: p_cust_profile_amt_rec.site_use_id = FND_API.G_MISS_NUM ) AND
6002: AND p_cust_profile_amt_rec.site_use_id <> FND_API.G_MISS_NUM AND
6003: ( l_site_use_id IS NULL OR
6004: l_site_use_id <> p_cust_profile_amt_rec.site_use_id ) ) OR
6005: ( ( p_cust_profile_amt_rec.site_use_id IS NULL OR
6006: p_cust_profile_amt_rec.site_use_id = FND_API.G_MISS_NUM ) AND
6007: l_site_use_id IS NOT NULL )
6008: THEN
6009: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_PROF_AMT_IDS_MISMATCH' );
6010: FND_MESSAGE.SET_TOKEN( 'ENTITY', 'site use' );
6008: THEN
6009: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_PROF_AMT_IDS_MISMATCH' );
6010: FND_MESSAGE.SET_TOKEN( 'ENTITY', 'site use' );
6011: FND_MSG_PUB.ADD;
6012: x_return_status := FND_API.G_RET_STS_ERROR;
6013: END IF;
6014:
6015: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6016: hz_utility_v2pub.debug(p_message=>'site_use_id should be the same as site_use_id site_use_id in corresponding customer profile. ' ||
6062: ------------------------------------
6063: --*** Credit Limit Validations ***--
6064: ------------------------------------
6065:
6066: IF p_cust_profile_amt_rec.overall_credit_limit <> FND_API.G_MISS_NUM AND
6067: p_cust_profile_amt_rec.overall_credit_limit IS NOT NULL
6068: THEN
6069: IF p_cust_profile_amt_rec.trx_credit_limit <> FND_API.G_MISS_NUM AND
6070: p_cust_profile_amt_rec.trx_credit_limit IS NOT NULL
6065:
6066: IF p_cust_profile_amt_rec.overall_credit_limit <> FND_API.G_MISS_NUM AND
6067: p_cust_profile_amt_rec.overall_credit_limit IS NOT NULL
6068: THEN
6069: IF p_cust_profile_amt_rec.trx_credit_limit <> FND_API.G_MISS_NUM AND
6070: p_cust_profile_amt_rec.trx_credit_limit IS NOT NULL
6071: THEN
6072:
6073: IF ( p_create_update_flag = 'C' OR
6072:
6073: IF ( p_create_update_flag = 'C' OR
6074: ( p_create_update_flag = 'U' AND
6075: (
6076: (p_cust_profile_amt_rec.overall_credit_limit <> NVL( l_overall_credit_limit, FND_API.G_MISS_NUM ))
6077: OR
6078: (p_cust_profile_amt_rec.trx_credit_limit <> NVL(l_trx_credit_limit,FND_API.G_MISS_NUM))
6079: )
6080: )
6074: ( p_create_update_flag = 'U' AND
6075: (
6076: (p_cust_profile_amt_rec.overall_credit_limit <> NVL( l_overall_credit_limit, FND_API.G_MISS_NUM ))
6077: OR
6078: (p_cust_profile_amt_rec.trx_credit_limit <> NVL(l_trx_credit_limit,FND_API.G_MISS_NUM))
6079: )
6080: )
6081: )
6082: THEN
6083: IF p_cust_profile_amt_rec.overall_credit_limit < p_cust_profile_amt_rec.trx_credit_limit
6084: THEN
6085: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_OVERALL_CREDIT' );
6086: FND_MSG_PUB.ADD;
6087: x_return_status := FND_API.G_RET_STS_ERROR;
6088:
6089: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6090: hz_utility_v2pub.debug(p_message=>'overall_credit_limit should be greater than the trx_credit_limit. ' ||
6091: 'x_return_status = ' || x_return_status,
6108: ----------------------------------------------
6109:
6110: -- MIN_FC_INVOICE_OVERDUE_TYPE is lookup code in lookup type AR_AMOUNT_PERCENT
6111: IF p_cust_profile_amt_rec.min_fc_invoice_overdue_type IS NOT NULL AND
6112: p_cust_profile_amt_rec.min_fc_invoice_overdue_type <> FND_API.G_MISS_CHAR AND
6113: ( p_create_update_flag = 'C' OR
6114: ( p_create_update_flag = 'U' AND
6115: p_cust_profile_amt_rec.min_fc_invoice_overdue_type <> NVL( l_min_fc_invoice_overdue_type, FND_API.G_MISS_CHAR ) ) )
6116: THEN
6111: IF p_cust_profile_amt_rec.min_fc_invoice_overdue_type IS NOT NULL AND
6112: p_cust_profile_amt_rec.min_fc_invoice_overdue_type <> FND_API.G_MISS_CHAR AND
6113: ( p_create_update_flag = 'C' OR
6114: ( p_create_update_flag = 'U' AND
6115: p_cust_profile_amt_rec.min_fc_invoice_overdue_type <> NVL( l_min_fc_invoice_overdue_type, FND_API.G_MISS_CHAR ) ) )
6116: THEN
6117: validate_lookup (
6118: p_column => 'min_fc_invoice_overdue_type',
6119: p_lookup_table => 'AR_LOOKUPS',
6142: ----------------------------------------------
6143:
6144: -- MIN_FC_BALANCE_OVERDUE_TYPE is lookup code in lookup type AR_AMOUNT_PERCENT
6145: IF p_cust_profile_amt_rec.min_fc_balance_overdue_type IS NOT NULL AND
6146: p_cust_profile_amt_rec.min_fc_balance_overdue_type <> FND_API.G_MISS_CHAR AND
6147: ( p_create_update_flag = 'C' OR
6148: ( p_create_update_flag = 'U' AND
6149: p_cust_profile_amt_rec.min_fc_balance_overdue_type <> NVL( l_min_fc_balance_overdue_type, FND_API.G_MISS_CHAR ) ) )
6150: THEN
6145: IF p_cust_profile_amt_rec.min_fc_balance_overdue_type IS NOT NULL AND
6146: p_cust_profile_amt_rec.min_fc_balance_overdue_type <> FND_API.G_MISS_CHAR AND
6147: ( p_create_update_flag = 'C' OR
6148: ( p_create_update_flag = 'U' AND
6149: p_cust_profile_amt_rec.min_fc_balance_overdue_type <> NVL( l_min_fc_balance_overdue_type, FND_API.G_MISS_CHAR ) ) )
6150: THEN
6151: validate_lookup (
6152: p_column => 'min_fc_balance_overdue_type',
6153: p_lookup_table => 'AR_LOOKUPS',
6176: ----------------------------------------------
6177:
6178: -- INTEREST_TYPE is lookup code in lookup type AR_INTEREST_PENALTY_TYPE
6179: IF p_cust_profile_amt_rec.interest_type IS NOT NULL AND
6180: p_cust_profile_amt_rec.interest_type <> FND_API.G_MISS_CHAR AND
6181: ( p_create_update_flag = 'C' OR
6182: ( p_create_update_flag = 'U' AND
6183: p_cust_profile_amt_rec.interest_type <> NVL( l_interest_type, FND_API.G_MISS_CHAR ) ) )
6184: THEN
6179: IF p_cust_profile_amt_rec.interest_type IS NOT NULL AND
6180: p_cust_profile_amt_rec.interest_type <> FND_API.G_MISS_CHAR AND
6181: ( p_create_update_flag = 'C' OR
6182: ( p_create_update_flag = 'U' AND
6183: p_cust_profile_amt_rec.interest_type <> NVL( l_interest_type, FND_API.G_MISS_CHAR ) ) )
6184: THEN
6185: validate_lookup (
6186: p_column => 'interest_type',
6187: p_lookup_table => 'AR_LOOKUPS',
6210: ----------------------------------------------
6211:
6212: -- PENALTY_TYPE is lookup code in lookup type AR_INTEREST_PENALTY_TYPE
6213: IF p_cust_profile_amt_rec.penalty_type IS NOT NULL AND
6214: p_cust_profile_amt_rec.penalty_type <> FND_API.G_MISS_CHAR AND
6215: ( p_create_update_flag = 'C' OR
6216: ( p_create_update_flag = 'U' AND
6217: p_cust_profile_amt_rec.penalty_type <> NVL( l_penalty_type, FND_API.G_MISS_CHAR ) ) )
6218: THEN
6213: IF p_cust_profile_amt_rec.penalty_type IS NOT NULL AND
6214: p_cust_profile_amt_rec.penalty_type <> FND_API.G_MISS_CHAR AND
6215: ( p_create_update_flag = 'C' OR
6216: ( p_create_update_flag = 'U' AND
6217: p_cust_profile_amt_rec.penalty_type <> NVL( l_penalty_type, FND_API.G_MISS_CHAR ) ) )
6218: THEN
6219: validate_lookup (
6220: p_column => 'penalty_type',
6221: p_lookup_table => 'AR_LOOKUPS',
6253: -- exchange_rate_type should be a valid value defined in GL_DAILY_CONVERSION_TYPES
6254: -- The type USER is excluded.
6255:
6256: IF p_cust_profile_amt_rec.exchange_rate_type is NOT NULL AND
6257: p_cust_profile_amt_rec.exchange_rate_type <> FND_API.G_MISS_CHAR
6258: AND ( p_create_update_flag = 'C' OR
6259: ( p_create_update_flag = 'U' AND
6260: p_cust_profile_amt_rec.exchange_rate_type <> NVL( l_exchange_rate_type, FND_API.G_MISS_CHAR ) ) )
6261: THEN
6256: IF p_cust_profile_amt_rec.exchange_rate_type is NOT NULL AND
6257: p_cust_profile_amt_rec.exchange_rate_type <> FND_API.G_MISS_CHAR
6258: AND ( p_create_update_flag = 'C' OR
6259: ( p_create_update_flag = 'U' AND
6260: p_cust_profile_amt_rec.exchange_rate_type <> NVL( l_exchange_rate_type, FND_API.G_MISS_CHAR ) ) )
6261: THEN
6262:
6263: check_exchange_rate_type_fk(
6264: p_column => 'exchange_rate_type',
6297: v_cust_profile_amt_rec.min_fc_invoice_overdue_type := l_min_fc_invoice_overdue_type;
6298: END IF;
6299: IF p_cust_profile_amt_rec.min_fc_invoice_amount is NULL THEN
6300: v_cust_profile_amt_rec.min_fc_invoice_amount := l_min_fc_invoice_amount;
6301: ELSIF p_cust_profile_amt_rec.min_fc_invoice_amount = FND_API.G_MISS_NUM THEN
6302: v_cust_profile_amt_rec.min_fc_invoice_amount := NULL;
6303: END IF;
6304: IF p_cust_profile_amt_rec.min_fc_invoice_percent is NULL THEN
6305: v_cust_profile_amt_rec.min_fc_invoice_percent := l_min_fc_invoice_percent;
6302: v_cust_profile_amt_rec.min_fc_invoice_amount := NULL;
6303: END IF;
6304: IF p_cust_profile_amt_rec.min_fc_invoice_percent is NULL THEN
6305: v_cust_profile_amt_rec.min_fc_invoice_percent := l_min_fc_invoice_percent;
6306: ELSIF p_cust_profile_amt_rec.min_fc_invoice_percent = FND_API.G_MISS_NUM THEN
6307: v_cust_profile_amt_rec.min_fc_invoice_percent := NULL;
6308: END IF;
6309: END IF;
6310:
6308: END IF;
6309: END IF;
6310:
6311: IF p_create_update_flag = 'C' THEN
6312: IF p_cust_profile_amt_rec.min_fc_invoice_overdue_type = FND_API.G_MISS_CHAR THEN
6313: v_cust_profile_amt_rec.min_fc_invoice_overdue_type := NULL;
6314: END IF;
6315: IF p_cust_profile_amt_rec.min_fc_invoice_amount = FND_API.G_MISS_NUM THEN
6316: v_cust_profile_amt_rec.min_fc_invoice_amount := NULL;
6311: IF p_create_update_flag = 'C' THEN
6312: IF p_cust_profile_amt_rec.min_fc_invoice_overdue_type = FND_API.G_MISS_CHAR THEN
6313: v_cust_profile_amt_rec.min_fc_invoice_overdue_type := NULL;
6314: END IF;
6315: IF p_cust_profile_amt_rec.min_fc_invoice_amount = FND_API.G_MISS_NUM THEN
6316: v_cust_profile_amt_rec.min_fc_invoice_amount := NULL;
6317: END IF;
6318: IF p_cust_profile_amt_rec.min_fc_invoice_percent = FND_API.G_MISS_NUM THEN
6319: v_cust_profile_amt_rec.min_fc_invoice_percent := NULL;
6314: END IF;
6315: IF p_cust_profile_amt_rec.min_fc_invoice_amount = FND_API.G_MISS_NUM THEN
6316: v_cust_profile_amt_rec.min_fc_invoice_amount := NULL;
6317: END IF;
6318: IF p_cust_profile_amt_rec.min_fc_invoice_percent = FND_API.G_MISS_NUM THEN
6319: v_cust_profile_amt_rec.min_fc_invoice_percent := NULL;
6320: END IF;
6321: END IF;
6322:
6324: IF (v_cust_profile_amt_rec.min_fc_invoice_amount IS NULL OR
6325: v_cust_profile_amt_rec.min_fc_invoice_percent IS NOT NULL) THEN
6326: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PERCENT' );
6327: FND_MSG_PUB.ADD;
6328: x_return_status := FND_API.G_RET_STS_ERROR;
6329: END IF;
6330: ELSIF v_cust_profile_amt_rec.min_fc_invoice_overdue_type = 'PERCENT' THEN
6331: IF (v_cust_profile_amt_rec.min_fc_invoice_amount IS NOT NULL OR
6332: v_cust_profile_amt_rec.min_fc_invoice_percent IS NULL) THEN
6331: IF (v_cust_profile_amt_rec.min_fc_invoice_amount IS NOT NULL OR
6332: v_cust_profile_amt_rec.min_fc_invoice_percent IS NULL) THEN
6333: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PERCENT' );
6334: FND_MSG_PUB.ADD;
6335: x_return_status := FND_API.G_RET_STS_ERROR;
6336: ELSE
6337: check_less_than_100 (
6338: p_column => 'min_fc_invoice_percent',
6339: p_column_value => v_cust_profile_amt_rec.min_fc_invoice_percent,
6350: IF (v_cust_profile_amt_rec.min_fc_invoice_amount IS NOT NULL OR
6351: v_cust_profile_amt_rec.min_fc_invoice_percent IS NOT NULL) THEN
6352: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PERCENT' );
6353: FND_MSG_PUB.ADD;
6354: x_return_status := FND_API.G_RET_STS_ERROR;
6355: END IF;
6356: END IF;
6357:
6358: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6378: v_cust_profile_amt_rec.min_fc_balance_overdue_type := l_min_fc_balance_overdue_type;
6379: END IF;
6380: IF p_cust_profile_amt_rec.min_fc_balance_amount is NULL THEN
6381: v_cust_profile_amt_rec.min_fc_balance_amount := l_min_fc_balance_amount;
6382: ELSIF p_cust_profile_amt_rec.min_fc_balance_amount = FND_API.G_MISS_NUM THEN
6383: v_cust_profile_amt_rec.min_fc_balance_amount := NULL;
6384: END IF;
6385: IF p_cust_profile_amt_rec.min_fc_balance_percent is NULL THEN
6386: v_cust_profile_amt_rec.min_fc_balance_percent := l_min_fc_balance_percent;
6383: v_cust_profile_amt_rec.min_fc_balance_amount := NULL;
6384: END IF;
6385: IF p_cust_profile_amt_rec.min_fc_balance_percent is NULL THEN
6386: v_cust_profile_amt_rec.min_fc_balance_percent := l_min_fc_balance_percent;
6387: ELSIF p_cust_profile_amt_rec.min_fc_balance_percent = FND_API.G_MISS_NUM THEN
6388: v_cust_profile_amt_rec.min_fc_balance_percent := NULL;
6389: END IF;
6390: END IF;
6391:
6389: END IF;
6390: END IF;
6391:
6392: IF p_create_update_flag = 'C' THEN
6393: IF p_cust_profile_amt_rec.min_fc_balance_overdue_type = FND_API.G_MISS_CHAR THEN
6394: v_cust_profile_amt_rec.min_fc_balance_overdue_type := NULL;
6395: END IF;
6396: IF p_cust_profile_amt_rec.min_fc_balance_amount = FND_API.G_MISS_NUM THEN
6397: v_cust_profile_amt_rec.min_fc_balance_amount := NULL;
6392: IF p_create_update_flag = 'C' THEN
6393: IF p_cust_profile_amt_rec.min_fc_balance_overdue_type = FND_API.G_MISS_CHAR THEN
6394: v_cust_profile_amt_rec.min_fc_balance_overdue_type := NULL;
6395: END IF;
6396: IF p_cust_profile_amt_rec.min_fc_balance_amount = FND_API.G_MISS_NUM THEN
6397: v_cust_profile_amt_rec.min_fc_balance_amount := NULL;
6398: END IF;
6399: IF p_cust_profile_amt_rec.min_fc_balance_percent = FND_API.G_MISS_NUM THEN
6400: v_cust_profile_amt_rec.min_fc_balance_percent := NULL;
6395: END IF;
6396: IF p_cust_profile_amt_rec.min_fc_balance_amount = FND_API.G_MISS_NUM THEN
6397: v_cust_profile_amt_rec.min_fc_balance_amount := NULL;
6398: END IF;
6399: IF p_cust_profile_amt_rec.min_fc_balance_percent = FND_API.G_MISS_NUM THEN
6400: v_cust_profile_amt_rec.min_fc_balance_percent := NULL;
6401: END IF;
6402: END IF;
6403:
6405: IF (v_cust_profile_amt_rec.min_fc_balance_amount IS NULL OR
6406: v_cust_profile_amt_rec.min_fc_balance_percent IS NOT NULL) THEN
6407: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_BAL_AMT' );
6408: FND_MSG_PUB.ADD;
6409: x_return_status := FND_API.G_RET_STS_ERROR;
6410: END IF;
6411: ELSIF v_cust_profile_amt_rec.min_fc_balance_overdue_type = 'PERCENT' THEN
6412: IF (v_cust_profile_amt_rec.min_fc_balance_amount IS NOT NULL OR
6413: v_cust_profile_amt_rec.min_fc_balance_percent IS NULL) THEN
6412: IF (v_cust_profile_amt_rec.min_fc_balance_amount IS NOT NULL OR
6413: v_cust_profile_amt_rec.min_fc_balance_percent IS NULL) THEN
6414: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_BAL_AMT' );
6415: FND_MSG_PUB.ADD;
6416: x_return_status := FND_API.G_RET_STS_ERROR;
6417: ELSE
6418: check_less_than_100 (
6419: p_column => 'min_fc_balance_percent',
6420: p_column_value => v_cust_profile_amt_rec.min_fc_balance_percent,
6431: IF (v_cust_profile_amt_rec.min_fc_balance_amount IS NOT NULL OR
6432: v_cust_profile_amt_rec.min_fc_balance_percent IS NOT NULL) THEN
6433: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_BAL_AMT' );
6434: FND_MSG_PUB.ADD;
6435: x_return_status := FND_API.G_RET_STS_ERROR;
6436: END IF;
6437: END IF;
6438:
6439: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6458: v_cust_profile_amt_rec.interest_type := l_interest_type;
6459: END IF;
6460: IF p_cust_profile_amt_rec.interest_rate is NULL THEN
6461: v_cust_profile_amt_rec.interest_rate := l_interest_rate;
6462: ELSIF p_cust_profile_amt_rec.interest_rate = FND_API.G_MISS_NUM THEN
6463: v_cust_profile_amt_rec.interest_rate := NULL;
6464: END IF;
6465: IF p_cust_profile_amt_rec.interest_fixed_amount is NULL THEN
6466: v_cust_profile_amt_rec.interest_fixed_amount := l_interest_fixed_amount;
6463: v_cust_profile_amt_rec.interest_rate := NULL;
6464: END IF;
6465: IF p_cust_profile_amt_rec.interest_fixed_amount is NULL THEN
6466: v_cust_profile_amt_rec.interest_fixed_amount := l_interest_fixed_amount;
6467: ELSIF p_cust_profile_amt_rec.interest_fixed_amount = FND_API.G_MISS_NUM THEN
6468: v_cust_profile_amt_rec.interest_fixed_amount := NULL;
6469: END IF;
6470: IF p_cust_profile_amt_rec.interest_schedule_id is NULL THEN
6471: v_cust_profile_amt_rec.interest_schedule_id := l_interest_schedule_id;
6468: v_cust_profile_amt_rec.interest_fixed_amount := NULL;
6469: END IF;
6470: IF p_cust_profile_amt_rec.interest_schedule_id is NULL THEN
6471: v_cust_profile_amt_rec.interest_schedule_id := l_interest_schedule_id;
6472: ELSIF p_cust_profile_amt_rec.interest_schedule_id = FND_API.G_MISS_NUM THEN
6473: v_cust_profile_amt_rec.interest_schedule_id := NULL;
6474: END IF;
6475: END IF;
6476:
6474: END IF;
6475: END IF;
6476:
6477: IF p_create_update_flag = 'C' THEN
6478: IF p_cust_profile_amt_rec.interest_type = FND_API.G_MISS_CHAR THEN
6479: v_cust_profile_amt_rec.interest_type := NULL;
6480: END IF;
6481: IF p_cust_profile_amt_rec.interest_rate = FND_API.G_MISS_NUM THEN
6482: v_cust_profile_amt_rec.interest_rate := NULL;
6477: IF p_create_update_flag = 'C' THEN
6478: IF p_cust_profile_amt_rec.interest_type = FND_API.G_MISS_CHAR THEN
6479: v_cust_profile_amt_rec.interest_type := NULL;
6480: END IF;
6481: IF p_cust_profile_amt_rec.interest_rate = FND_API.G_MISS_NUM THEN
6482: v_cust_profile_amt_rec.interest_rate := NULL;
6483: END IF;
6484: IF p_cust_profile_amt_rec.interest_fixed_amount = FND_API.G_MISS_NUM THEN
6485: v_cust_profile_amt_rec.interest_fixed_amount := NULL;
6480: END IF;
6481: IF p_cust_profile_amt_rec.interest_rate = FND_API.G_MISS_NUM THEN
6482: v_cust_profile_amt_rec.interest_rate := NULL;
6483: END IF;
6484: IF p_cust_profile_amt_rec.interest_fixed_amount = FND_API.G_MISS_NUM THEN
6485: v_cust_profile_amt_rec.interest_fixed_amount := NULL;
6486: END IF;
6487: IF p_cust_profile_amt_rec.interest_schedule_id = FND_API.G_MISS_NUM THEN
6488: v_cust_profile_amt_rec.interest_schedule_id := NULL;
6483: END IF;
6484: IF p_cust_profile_amt_rec.interest_fixed_amount = FND_API.G_MISS_NUM THEN
6485: v_cust_profile_amt_rec.interest_fixed_amount := NULL;
6486: END IF;
6487: IF p_cust_profile_amt_rec.interest_schedule_id = FND_API.G_MISS_NUM THEN
6488: v_cust_profile_amt_rec.interest_schedule_id := NULL;
6489: END IF;
6490: END IF;
6491:
6494: v_cust_profile_amt_rec.interest_fixed_amount IS NOT NULL OR
6495: v_cust_profile_amt_rec.interest_schedule_id IS NOT NULL) THEN
6496: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_INT_RATE' );
6497: FND_MSG_PUB.ADD;
6498: x_return_status := FND_API.G_RET_STS_ERROR;
6499: ELSE
6500: check_less_than_100 (
6501: p_column => 'interest_rate',
6502: p_column_value => v_cust_profile_amt_rec.interest_rate,
6514: v_cust_profile_amt_rec.interest_fixed_amount IS NULL OR
6515: v_cust_profile_amt_rec.interest_schedule_id IS NOT NULL) THEN
6516: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_INT_RATE' );
6517: FND_MSG_PUB.ADD;
6518: x_return_status := FND_API.G_RET_STS_ERROR;
6519: END IF;
6520: /*6469663 Late charge enhacement handling case oftier basis calculation*/
6521: ELSIF v_cust_profile_amt_rec.interest_type IN ( 'CHARGES_SCHEDULE','CHARGE_PER_TIER') THEN
6522: IF (v_cust_profile_amt_rec.interest_rate IS NOT NULL OR
6523: v_cust_profile_amt_rec.interest_fixed_amount IS NOT NULL OR
6524: v_cust_profile_amt_rec.interest_schedule_id IS NULL) THEN
6525: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_INT_RATE' );
6526: FND_MSG_PUB.ADD;
6527: x_return_status := FND_API.G_RET_STS_ERROR;
6528: ELSE
6529: check_charge_schedule_id_fk (
6530: p_column => 'interest_schedule_id',
6531: p_column_value => v_cust_profile_amt_rec.interest_schedule_id,
6543: v_cust_profile_amt_rec.interest_fixed_amount IS NOT NULL OR
6544: v_cust_profile_amt_rec.interest_schedule_id IS NOT NULL) THEN
6545: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_INT_RATE' );
6546: FND_MSG_PUB.ADD;
6547: x_return_status := FND_API.G_RET_STS_ERROR;
6548: END IF;
6549: END IF;
6550:
6551: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6570: v_cust_profile_amt_rec.penalty_type := l_penalty_type;
6571: END IF;
6572: IF p_cust_profile_amt_rec.penalty_rate is NULL THEN
6573: v_cust_profile_amt_rec.penalty_rate := l_penalty_rate;
6574: ELSIF p_cust_profile_amt_rec.penalty_rate = FND_API.G_MISS_NUM THEN
6575: v_cust_profile_amt_rec.penalty_rate := NULL;
6576: END IF;
6577: IF p_cust_profile_amt_rec.penalty_fixed_amount is NULL THEN
6578: v_cust_profile_amt_rec.penalty_fixed_amount := l_penalty_fixed_amount;
6575: v_cust_profile_amt_rec.penalty_rate := NULL;
6576: END IF;
6577: IF p_cust_profile_amt_rec.penalty_fixed_amount is NULL THEN
6578: v_cust_profile_amt_rec.penalty_fixed_amount := l_penalty_fixed_amount;
6579: ELSIF p_cust_profile_amt_rec.penalty_fixed_amount = FND_API.G_MISS_NUM THEN
6580: v_cust_profile_amt_rec.penalty_fixed_amount := NULL;
6581: END IF;
6582: IF p_cust_profile_amt_rec.penalty_schedule_id is NULL THEN
6583: v_cust_profile_amt_rec.penalty_schedule_id := l_penalty_schedule_id;
6580: v_cust_profile_amt_rec.penalty_fixed_amount := NULL;
6581: END IF;
6582: IF p_cust_profile_amt_rec.penalty_schedule_id is NULL THEN
6583: v_cust_profile_amt_rec.penalty_schedule_id := l_penalty_schedule_id;
6584: ELSIF p_cust_profile_amt_rec.penalty_schedule_id = FND_API.G_MISS_NUM THEN
6585: v_cust_profile_amt_rec.penalty_schedule_id := NULL;
6586: END IF;
6587: END IF;
6588:
6586: END IF;
6587: END IF;
6588:
6589: IF p_create_update_flag = 'C' THEN
6590: IF p_cust_profile_amt_rec.penalty_type = FND_API.G_MISS_CHAR THEN
6591: v_cust_profile_amt_rec.penalty_type := NULL;
6592: END IF;
6593: IF p_cust_profile_amt_rec.penalty_rate = FND_API.G_MISS_NUM THEN
6594: v_cust_profile_amt_rec.penalty_rate := NULL;
6589: IF p_create_update_flag = 'C' THEN
6590: IF p_cust_profile_amt_rec.penalty_type = FND_API.G_MISS_CHAR THEN
6591: v_cust_profile_amt_rec.penalty_type := NULL;
6592: END IF;
6593: IF p_cust_profile_amt_rec.penalty_rate = FND_API.G_MISS_NUM THEN
6594: v_cust_profile_amt_rec.penalty_rate := NULL;
6595: END IF;
6596: IF p_cust_profile_amt_rec.penalty_fixed_amount = FND_API.G_MISS_NUM THEN
6597: v_cust_profile_amt_rec.penalty_fixed_amount := NULL;
6592: END IF;
6593: IF p_cust_profile_amt_rec.penalty_rate = FND_API.G_MISS_NUM THEN
6594: v_cust_profile_amt_rec.penalty_rate := NULL;
6595: END IF;
6596: IF p_cust_profile_amt_rec.penalty_fixed_amount = FND_API.G_MISS_NUM THEN
6597: v_cust_profile_amt_rec.penalty_fixed_amount := NULL;
6598: END IF;
6599: IF p_cust_profile_amt_rec.penalty_schedule_id = FND_API.G_MISS_NUM THEN
6600: v_cust_profile_amt_rec.penalty_schedule_id := NULL;
6595: END IF;
6596: IF p_cust_profile_amt_rec.penalty_fixed_amount = FND_API.G_MISS_NUM THEN
6597: v_cust_profile_amt_rec.penalty_fixed_amount := NULL;
6598: END IF;
6599: IF p_cust_profile_amt_rec.penalty_schedule_id = FND_API.G_MISS_NUM THEN
6600: v_cust_profile_amt_rec.penalty_schedule_id := NULL;
6601: END IF;
6602: END IF;
6603:
6606: v_cust_profile_amt_rec.penalty_fixed_amount IS NOT NULL OR
6607: v_cust_profile_amt_rec.penalty_schedule_id IS NOT NULL) THEN
6608: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PEN_RATE' );
6609: FND_MSG_PUB.ADD;
6610: x_return_status := FND_API.G_RET_STS_ERROR;
6611: ELSE
6612: check_less_than_100 (
6613: p_column => 'penalty_rate',
6614: p_column_value => v_cust_profile_amt_rec.penalty_rate,
6626: v_cust_profile_amt_rec.penalty_fixed_amount IS NULL OR
6627: v_cust_profile_amt_rec.penalty_schedule_id IS NOT NULL) THEN
6628: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PEN_RATE' );
6629: FND_MSG_PUB.ADD;
6630: x_return_status := FND_API.G_RET_STS_ERROR;
6631: END IF;
6632: /*6469663 Late charge enhacement handling case oftier basis calculation*/
6633: ELSIF v_cust_profile_amt_rec.penalty_type IN ('CHARGES_SCHEDULE','CHARGE_PER_TIER') THEN
6634: IF (v_cust_profile_amt_rec.penalty_rate IS NOT NULL OR
6635: v_cust_profile_amt_rec.penalty_fixed_amount IS NOT NULL OR
6636: v_cust_profile_amt_rec.penalty_schedule_id IS NULL) THEN
6637: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PEN_RATE' );
6638: FND_MSG_PUB.ADD;
6639: x_return_status := FND_API.G_RET_STS_ERROR;
6640: ELSE
6641: check_charge_schedule_id_fk (
6642: p_column => 'penalty_schedule_id',
6643: p_column_value => v_cust_profile_amt_rec.penalty_schedule_id,
6655: v_cust_profile_amt_rec.penalty_fixed_amount IS NOT NULL OR
6656: v_cust_profile_amt_rec.penalty_schedule_id IS NOT NULL) THEN
6657: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_INVALID_PEN_RATE' );
6658: FND_MSG_PUB.ADD;
6659: x_return_status := FND_API.G_RET_STS_ERROR;
6660: END IF;
6661: END IF;
6662:
6663: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
6689: * p_cust_acct_site_rec Customer account site record.
6690: * p_rowid Rowid of the record (used only in update mode).
6691: * IN/OUT:
6692: * x_return_status Return status after the call. The status can
6693: * be FND_API.G_RET_STS_SUCCESS (success),
6694: * FND_API.G_RET_STS_ERROR (error),
6695: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6696: *
6697: * NOTES
6690: * p_rowid Rowid of the record (used only in update mode).
6691: * IN/OUT:
6692: * x_return_status Return status after the call. The status can
6693: * be FND_API.G_RET_STS_SUCCESS (success),
6694: * FND_API.G_RET_STS_ERROR (error),
6695: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6696: *
6697: * NOTES
6698: *
6691: * IN/OUT:
6692: * x_return_status Return status after the call. The status can
6693: * be FND_API.G_RET_STS_SUCCESS (success),
6694: * FND_API.G_RET_STS_ERROR (error),
6695: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
6696: *
6697: * NOTES
6698: *
6699: * MODIFICATION HISTORY
6767: -- If primary key value is not passed, it will be generated
6768: -- from sequence by table handler.
6769:
6770: IF p_cust_acct_site_rec.cust_acct_site_id IS NOT NULL AND
6771: p_cust_acct_site_rec.cust_acct_site_id <> FND_API.G_MISS_NUM
6772: THEN
6773: BEGIN
6774: SELECT 'Y' INTO l_dummy
6775: FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
6777:
6778: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
6779: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id' );
6780: FND_MSG_PUB.ADD;
6781: x_return_status := FND_API.G_RET_STS_ERROR;
6782: EXCEPTION
6783: WHEN NO_DATA_FOUND THEN
6784: NULL;
6785: END;
6854: -- non-updateable.
6855: IF p_create_update_flag = 'C' THEN
6856: IF p_cust_acct_site_rec.cust_account_id <> -1 AND
6857: p_cust_acct_site_rec.cust_account_id IS NOT NULL AND
6858: p_cust_acct_site_rec.cust_account_id <> FND_API.G_MISS_NUM
6859: THEN
6860: check_cust_account_fk (
6861: p_column => 'cust_account_id',
6862: p_column_value => p_cust_acct_site_rec.cust_account_id,
6931: -- Do not need to check during update because party_site_id is
6932: -- non-updateable.
6933: IF p_create_update_flag = 'C' AND
6934: p_cust_acct_site_rec.party_site_id IS NOT NULL AND
6935: p_cust_acct_site_rec.party_site_id <> FND_API.G_MISS_NUM
6936: THEN
6937: check_party_site_fk (
6938: p_column => 'party_site_id',
6939: p_column_value => p_cust_acct_site_rec.party_site_id,
6969: EXCEPTION
6970: WHEN NO_DATA_FOUND THEN
6971: FND_MESSAGE.SET_NAME( 'AR', 'HZ_LOC_CONTENT_INVALID' );
6972: FND_MSG_PUB.ADD;
6973: x_return_status := FND_API.G_RET_STS_ERROR;
6974: END;
6975:
6976: -- Debug info.
6977: IF G_DEBUG THEN
6995:
6996: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
6997: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id - party_site_id' );
6998: FND_MSG_PUB.ADD;
6999: x_return_status := FND_API.G_RET_STS_ERROR;
7000: EXCEPTION
7001: WHEN NO_DATA_FOUND THEN
7002: NULL;
7003: END;
7033:
7034: -- Bug 3456489. Post MOAC, this uniqeness should be within orgs.
7035: IF p_create_update_flag = 'C' AND
7036: p_cust_acct_site_rec.orig_system_reference IS NOT NULL AND
7037: p_cust_acct_site_rec.orig_system_reference <> FND_API.G_MISS_CHAR
7038: THEN
7039: BEGIN
7040: SELECT 'Y' INTO l_dummy
7041: FROM HZ_CUST_ACCT_SITES_ALL
7044:
7045: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
7046: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'orig_system_reference' );
7047: FND_MSG_PUB.ADD;
7048: x_return_status := FND_API.G_RET_STS_ERROR;
7049: EXCEPTION
7050: WHEN NO_DATA_FOUND THEN
7051: NULL;
7052: END;
7074: else
7075: l_orig_system_reference1 := p_cust_acct_site_rec.orig_system_reference;
7076: end if;
7077:
7078: IF (p_cust_acct_site_rec.orig_system is not null and p_cust_acct_site_rec.orig_system <>fnd_api.g_miss_char)
7079: and (p_cust_acct_site_rec.orig_system_reference is not null and p_cust_acct_site_rec.orig_system_reference <>fnd_api.g_miss_char)
7080: and p_create_update_flag = 'U'
7081: then
7082: hz_orig_system_ref_pub.get_owner_table_id
7075: l_orig_system_reference1 := p_cust_acct_site_rec.orig_system_reference;
7076: end if;
7077:
7078: IF (p_cust_acct_site_rec.orig_system is not null and p_cust_acct_site_rec.orig_system <>fnd_api.g_miss_char)
7079: and (p_cust_acct_site_rec.orig_system_reference is not null and p_cust_acct_site_rec.orig_system_reference <>fnd_api.g_miss_char)
7080: and p_create_update_flag = 'U'
7081: then
7082: hz_orig_system_ref_pub.get_owner_table_id
7083: (p_orig_system => p_cust_acct_site_rec.orig_system,
7085: p_owner_table_name => 'HZ_CUST_ACCT_SITES_ALL',
7086: x_owner_table_id => l_mosr_owner_table_id,
7087: x_return_status => l_temp_return_status);
7088:
7089: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
7090: l_mosr_owner_table_id= nvl(p_cust_acct_site_rec.cust_acct_site_id,l_mosr_owner_table_id))
7091: THEN
7092: l_validate_flag := 'N';
7093: END IF;
7095: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
7096: -- x_return_status. Set x_return_status to error, ONLY if there is error.
7097: -- In case of success, leave it to carry over previous value as before this call.
7098: -- Fix for Bug 5498116 (29-AUG-2006)
7099: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
7100: x_return_status := l_temp_return_status;
7101: END IF;
7102:
7103: end if;
7136:
7137: -- We have unique index on tp_header_id
7138:
7139: IF p_cust_acct_site_rec.tp_header_id IS NOT NULL AND
7140: p_cust_acct_site_rec.tp_header_id <> FND_API.G_MISS_NUM
7141: THEN
7142: BEGIN
7143: SELECT ROWID INTO l_rowid
7144: FROM HZ_CUST_ACCT_SITES_ALL
7150: THEN
7151: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
7152: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'tp_header_id' );
7153: FND_MSG_PUB.ADD;
7154: x_return_status := FND_API.G_RET_STS_ERROR;
7155: END IF;
7156: EXCEPTION
7157: WHEN NO_DATA_FOUND THEN
7158: NULL;
7198: /****Logical APIs - validation not required****/
7199: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
7200: -- status is lookup code in lookup type CODE_STATUS
7201: IF p_cust_acct_site_rec.status IS NOT NULL AND
7202: p_cust_acct_site_rec.status <> FND_API.G_MISS_CHAR AND
7203: ( p_create_update_flag = 'C' OR
7204: ( p_create_update_flag = 'U' AND
7205: p_cust_acct_site_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
7206: THEN
7201: IF p_cust_acct_site_rec.status IS NOT NULL AND
7202: p_cust_acct_site_rec.status <> FND_API.G_MISS_CHAR AND
7203: ( p_create_update_flag = 'C' OR
7204: ( p_create_update_flag = 'U' AND
7205: p_cust_acct_site_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
7206: THEN
7207: validate_lookup (
7208: p_column => 'status',
7209: p_lookup_type => 'CODE_STATUS',
7232: ----------------------------------------------
7233:
7234: -- customer_category_code is lookup code in lookup type ADDRESS_CATEGORY
7235: IF p_cust_acct_site_rec.customer_category_code IS NOT NULL AND
7236: p_cust_acct_site_rec.customer_category_code <> FND_API.G_MISS_CHAR AND
7237: ( p_create_update_flag = 'C' OR
7238: ( p_create_update_flag = 'U' AND
7239: p_cust_acct_site_rec.customer_category_code <> NVL( l_customer_category_code, FND_API.G_MISS_CHAR ) ) )
7240: THEN
7235: IF p_cust_acct_site_rec.customer_category_code IS NOT NULL AND
7236: p_cust_acct_site_rec.customer_category_code <> FND_API.G_MISS_CHAR AND
7237: ( p_create_update_flag = 'C' OR
7238: ( p_create_update_flag = 'U' AND
7239: p_cust_acct_site_rec.customer_category_code <> NVL( l_customer_category_code, FND_API.G_MISS_CHAR ) ) )
7240: THEN
7241: validate_lookup (
7242: p_column => 'customer_category_code',
7243: p_lookup_type => 'ADDRESS_CATEGORY',
7265: ----------------------------------------------
7266:
7267: -- language is foreign key of fnd installed languages
7268: IF p_cust_acct_site_rec.language IS NOT NULL AND
7269: p_cust_acct_site_rec.language <> FND_API.G_MISS_CHAR
7270: THEN
7271: BEGIN
7272: SELECT 'Y' INTO l_dummy
7273: FROM FND_LANGUAGES
7280: FND_MESSAGE.SET_TOKEN( 'FK', 'language' );
7281: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'language_code' );
7282: FND_MESSAGE.SET_TOKEN( 'TABLE', 'fnd_languages(installed)' );
7283: FND_MSG_PUB.ADD;
7284: x_return_status := FND_API.G_RET_STS_ERROR;
7285: END;
7286:
7287: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
7288: hz_utility_v2pub.debug(p_message=>'language is foreign key of fnd installed languages. ' ||
7306: --------------------------------------
7307:
7308: -- primary_specialist_id is foreign key to per_all_people_f
7309: IF p_cust_acct_site_rec.primary_specialist_id IS NOT NULL AND
7310: p_cust_acct_site_rec.primary_specialist_id <> FND_API.G_MISS_NUM
7311: THEN
7312: check_per_all_people_f_fk (
7313: p_column => 'primary_specialist_id',
7314: p_column_value => p_cust_acct_site_rec.primary_specialist_id,
7334: --------------------------------------
7335:
7336: -- secondary_specialist_id is foreign key to per_all_people_f
7337: IF p_cust_acct_site_rec.secondary_specialist_id IS NOT NULL AND
7338: p_cust_acct_site_rec.secondary_specialist_id <> FND_API.G_MISS_NUM
7339: THEN
7340: /* Bug 3591694 Changed foreign key validation from check_cust_account_fk to
7341: check_per_all_people_f_fk.
7342: */
7386: --------------------------------------
7387: IF p_create_update_flag = 'C' THEN
7388: IF p_cust_acct_site_rec.cust_account_id <> -1 AND
7389: p_cust_acct_site_rec.cust_account_id IS NOT NULL AND
7390: p_cust_acct_site_rec.cust_account_id <> FND_API.G_MISS_NUM
7391: THEN
7392: IF p_cust_acct_site_rec.ece_tp_location_code IS NOT NULL AND
7393: p_cust_acct_site_rec.ece_tp_location_code <> FND_API.G_MISS_CHAR
7394: THEN
7389: p_cust_acct_site_rec.cust_account_id IS NOT NULL AND
7390: p_cust_acct_site_rec.cust_account_id <> FND_API.G_MISS_NUM
7391: THEN
7392: IF p_cust_acct_site_rec.ece_tp_location_code IS NOT NULL AND
7393: p_cust_acct_site_rec.ece_tp_location_code <> FND_API.G_MISS_CHAR
7394: THEN
7395: select count(1)
7396: into l_count
7397: from hz_cust_acct_sites_all addr -- Bug 3456489
7401:
7402: IF l_count > 0 THEN
7403: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_ADDR_EDI_LOC_EXISTS' );
7404: FND_MSG_PUB.ADD;
7405: x_return_status := FND_API.G_RET_STS_ERROR;
7406: END IF;
7407: END IF;
7408: END IF;
7409: ELSIF p_create_update_flag = 'U' THEN
7407: END IF;
7408: END IF;
7409: ELSIF p_create_update_flag = 'U' THEN
7410: IF p_cust_acct_site_rec.ece_tp_location_code IS NOT NULL AND
7411: p_cust_acct_site_rec.ece_tp_location_code <> FND_API.G_MISS_CHAR
7412: THEN
7413: select count(1)
7414: into l_count
7415: from hz_cust_acct_sites_all addr -- Bug 3456489
7420:
7421: IF l_count > 0 THEN
7422: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_ADDR_EDI_LOC_EXISTS' );
7423: FND_MSG_PUB.ADD;
7424: x_return_status := FND_API.G_RET_STS_ERROR;
7425: END IF;
7426: END IF;
7427: END IF;
7428:
7466: * p_cust_site_use_rec Customer account site use record.
7467: * p_rowid Rowid of the record (used only in update mode).
7468: * IN/OUT:
7469: * x_return_status Return status after the call. The status can
7470: * be FND_API.G_RET_STS_SUCCESS (success),
7471: * FND_API.G_RET_STS_ERROR (error),
7472: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7473: *
7474: * NOTES
7467: * p_rowid Rowid of the record (used only in update mode).
7468: * IN/OUT:
7469: * x_return_status Return status after the call. The status can
7470: * be FND_API.G_RET_STS_SUCCESS (success),
7471: * FND_API.G_RET_STS_ERROR (error),
7472: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7473: *
7474: * NOTES
7475: *
7468: * IN/OUT:
7469: * x_return_status Return status after the call. The status can
7470: * be FND_API.G_RET_STS_SUCCESS (success),
7471: * FND_API.G_RET_STS_ERROR (error),
7472: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
7473: *
7474: * NOTES
7475: *
7476: * MODIFICATION HISTORY
7592:
7593: --Bug Fix 2399491, Checking for duplicates in the _all table.
7594:
7595: IF p_cust_site_use_rec.site_use_id IS NOT NULL AND
7596: p_cust_site_use_rec.site_use_id <> FND_API.G_MISS_NUM
7597: THEN
7598: BEGIN
7599: SELECT 'Y' INTO l_dummy
7600: FROM HZ_CUST_SITE_USES_ALL
7602:
7603: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
7604: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_site_use_id' );
7605: FND_MSG_PUB.ADD;
7606: x_return_status := FND_API.G_RET_STS_ERROR;
7607: EXCEPTION
7608: WHEN NO_DATA_FOUND THEN
7609: NULL;
7610: END;
7679:
7680: -- cust_acct_site_id is foreign key to hz_cust_acct_sites
7681: IF p_create_update_flag = 'C' AND
7682: p_cust_site_use_rec.cust_acct_site_id IS NOT NULL AND
7683: p_cust_site_use_rec.cust_acct_site_id <> FND_API.G_MISS_NUM
7684: THEN
7685: check_cust_acct_site_fk (
7686: p_column => 'cust_acct_site_id',
7687: p_column_value => p_cust_site_use_rec.cust_acct_site_id,
7735: /****Logical APIs - validation not required****/
7736: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
7737: -- status is lookup code in lookup type CODE_STATUS
7738: IF p_cust_site_use_rec.status IS NOT NULL AND
7739: p_cust_site_use_rec.status <> FND_API.G_MISS_CHAR AND
7740: ( p_create_update_flag = 'C' OR
7741: ( p_create_update_flag = 'U' AND
7742: p_cust_site_use_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
7743: THEN
7738: IF p_cust_site_use_rec.status IS NOT NULL AND
7739: p_cust_site_use_rec.status <> FND_API.G_MISS_CHAR AND
7740: ( p_create_update_flag = 'C' OR
7741: ( p_create_update_flag = 'U' AND
7742: p_cust_site_use_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
7743: THEN
7744: validate_lookup (
7745: p_column => 'status',
7746: p_lookup_type => 'CODE_STATUS',
7812: -- during creation.
7813:
7814: IF p_create_update_flag = 'C' AND
7815: p_cust_site_use_rec.site_use_code IS NOT NULL AND
7816: p_cust_site_use_rec.site_use_code <> FND_API.G_MISS_CHAR
7817: THEN
7818: validate_lookup (
7819: p_column => 'site_use_code',
7820: p_lookup_type => 'SITE_USE_CODE',
7833:
7834: IF p_cust_site_use_rec.status = 'A' OR
7835: ( p_create_update_flag = 'C' AND
7836: ( p_cust_site_use_rec.status IS NULL OR
7837: p_cust_site_use_rec.status = FND_API.G_MISS_CHAR ) ) OR
7838: ( p_create_update_flag = 'U' AND
7839: p_cust_site_use_rec.status IS NULL AND
7840: l_status = 'A' )
7841: THEN
7865: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_ONE_ACTIVE_STMTS_SITE' );
7866: END IF;
7867:
7868: FND_MSG_PUB.ADD;
7869: x_return_status := FND_API.G_RET_STS_ERROR;
7870:
7871: EXCEPTION
7872: WHEN NO_DATA_FOUND THEN
7873: NULL;
7889: SELECT 'Y' INTO l_dummy
7890: FROM HZ_CUST_SITE_USES_ALL -- Bug 3456489
7891: WHERE CUST_ACCT_SITE_ID = nvl(p_cust_site_use_rec.cust_acct_site_id,l_cust_acct_site_id)
7892: AND SITE_USE_CODE = nvl(p_cust_site_use_rec.site_use_code,l_site_use_code)
7893: AND SITE_USE_ID <> nvl(p_cust_site_use_rec.site_use_id,fnd_api.g_miss_num)
7894: AND STATUS = 'A'
7895: AND ORG_ID = p_cust_site_use_rec.org_id -- Bug 3456489
7896: AND ROWNUM = 1;
7897:
7897:
7898: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
7899: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id - site_use_code' );
7900: FND_MSG_PUB.ADD;
7901: x_return_status := FND_API.G_RET_STS_ERROR;
7902: EXCEPTION
7903: WHEN NO_DATA_FOUND THEN
7904: NULL;
7905: END;
7936: p_cust_site_use_rec.org_id);
7937: END IF;
7938: EXCEPTION
7939: WHEN NO_DATA_FOUND THEN
7940: x_return_status := FND_API.G_RET_STS_ERROR;
7941: END ;
7942:
7943:
7944: IF p_create_update_flag = 'C' THEN
7942:
7943:
7944: IF p_create_update_flag = 'C' THEN
7945: IF p_cust_site_use_rec.location IS NULL OR
7946: p_cust_site_use_rec.location = FND_API.G_MISS_CHAR
7947: THEN
7948: IF l_profile = 'N' THEN
7949: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
7950: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'location' );
7948: IF l_profile = 'N' THEN
7949: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
7950: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'location' );
7951: FND_MSG_PUB.ADD;
7952: x_return_status := FND_API.G_RET_STS_ERROR;
7953: END IF;
7954:
7955: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
7956: hz_utility_v2pub.debug(p_message=>'location is mandatory if autonumbering is set to N. ' ||
7980:
7981: -- location must be unique within a customer account/site_use_type
7982:
7983: IF p_cust_site_use_rec.location IS NOT NULL AND
7984: p_cust_site_use_rec.location <> FND_API.G_MISS_CHAR
7985: THEN
7986: BEGIN
7987:
7988: --Bug No : 2998504. Status check added to the where clause.
8003:
8004:
8005: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_DUP_CODE_LOCATION' );
8006: FND_MSG_PUB.ADD;
8007: x_return_status := FND_API.G_RET_STS_ERROR;
8008:
8009: EXCEPTION
8010: WHEN NO_DATA_FOUND THEN
8011: NULL;
8030: ----------------------------------------------
8031: -- validate contact_id
8032: ----------------------------------------------
8033: IF p_cust_site_use_rec.contact_id IS NOT NULL AND
8034: p_cust_site_use_rec.contact_id <> FND_API.G_MISS_NUM
8035: THEN
8036: check_cust_site_use_cont_fk (
8037: p_column => 'cust_account_role_id',
8038: p_column_value => p_cust_site_use_rec.contact_id,
8055: -- BILL_TO site use of this account and its related account.
8056: -- For other type of site uses, this column should be NULL.
8057: -- Bug No : 2266165. Added the check for bill_to_flag.
8058: IF p_cust_site_use_rec.bill_to_site_use_id IS NOT NULL AND
8059: p_cust_site_use_rec.bill_to_site_use_id <> FND_API.G_MISS_NUM
8060: THEN
8061: IF l_site_use_code = 'SHIP_TO' THEN
8062: BEGIN
8063: SELECT 'Y' INTO l_dummy
8081: EXCEPTION
8082: WHEN NO_DATA_FOUND THEN
8083: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_BILL_TO_SITE_USE_F' );
8084: FND_MSG_PUB.ADD;
8085: x_return_status := FND_API.G_RET_STS_ERROR;
8086: END;
8087:
8088: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
8089: hz_utility_v2pub.debug(p_message=>'For SHIP_TO site use, bill_to_site_use_id should be a valid BILL_TO site use of this account and its related account. ' ||
8094:
8095: ELSE
8096: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_BILL_TO_SITE_USE_S' );
8097: FND_MSG_PUB.ADD;
8098: x_return_status := FND_API.G_RET_STS_ERROR;
8099: END IF;
8100: END IF;
8101:
8102:
8120: AND rownum = 1;
8121:
8122: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_SHIP_TO_SITES_EXIST' );
8123: FND_MSG_PUB.ADD;
8124: x_return_status := FND_API.G_RET_STS_ERROR;
8125:
8126: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
8127: hz_utility_v2pub.debug(p_message=>'For BILL_TO site use, inactivation not allowed if a SHIP_TO address uses this BILL_TO address ' ||
8128: 'x_return_status = ' || x_return_status,
8149: -- validate orig_system_reference
8150: ----------------------------------------------
8151: /****Logical APIs - validation not required****/
8152: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8153: IF (p_cust_site_use_rec.orig_system is not null and p_cust_site_use_rec.orig_system <>fnd_api.g_miss_char)
8154: and (p_cust_site_use_rec.orig_system_reference is not null and p_cust_site_use_rec.orig_system_reference <>fnd_api.g_miss_char)
8155: and p_create_update_flag = 'U'
8156: THEN
8157:
8150: ----------------------------------------------
8151: /****Logical APIs - validation not required****/
8152: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8153: IF (p_cust_site_use_rec.orig_system is not null and p_cust_site_use_rec.orig_system <>fnd_api.g_miss_char)
8154: and (p_cust_site_use_rec.orig_system_reference is not null and p_cust_site_use_rec.orig_system_reference <>fnd_api.g_miss_char)
8155: and p_create_update_flag = 'U'
8156: THEN
8157:
8158: hz_orig_system_ref_pub.get_owner_table_id
8161: p_owner_table_name =>'HZ_CUST_SITE_USES_ALL',
8162: x_owner_table_id => l_mosr_owner_table_id,
8163: x_return_status => l_temp_return_status);
8164:
8165: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
8166: l_mosr_owner_table_id= nvl(p_cust_site_use_rec.site_use_id,l_mosr_owner_table_id))
8167: THEN
8168: l_validate_flag := 'N';
8169: END IF;
8171: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
8172: -- x_return_status. Set x_return_status to error, ONLY if there is error.
8173: -- In case of success, leave it to carry over previous value as before this call.
8174: -- Fix for Bug 5498116 (29-AUG-2006)
8175: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
8176: x_return_status := l_temp_return_status;
8177: END IF;
8178:
8179: end if;
8209: ----------------------------------------------
8210:
8211: -- freight_term is lookup code in lookup type FREIGHT_TERMS in so_lookups
8212: IF p_cust_site_use_rec.freight_term IS NOT NULL AND
8213: p_cust_site_use_rec.freight_term <> FND_API.G_MISS_CHAR AND
8214: ( p_create_update_flag = 'C' OR
8215: ( p_create_update_flag = 'U' AND
8216: p_cust_site_use_rec.freight_term <> NVL( l_freight_term, FND_API.G_MISS_CHAR ) ) )
8217: THEN
8212: IF p_cust_site_use_rec.freight_term IS NOT NULL AND
8213: p_cust_site_use_rec.freight_term <> FND_API.G_MISS_CHAR AND
8214: ( p_create_update_flag = 'C' OR
8215: ( p_create_update_flag = 'U' AND
8216: p_cust_site_use_rec.freight_term <> NVL( l_freight_term, FND_API.G_MISS_CHAR ) ) )
8217: THEN
8218: validate_lookup (
8219: p_column => 'freight_term',
8220: p_lookup_table => 'SO_LOOKUPS',
8262: /****Logical APIs - validation not required****/
8263: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8264: -- primary_flag is lookup code in lookup type YES/NO
8265: IF p_cust_site_use_rec.primary_flag IS NOT NULL AND
8266: p_cust_site_use_rec.primary_flag <> FND_API.G_MISS_CHAR
8267: THEN
8268: validate_lookup (
8269: p_column => 'primary_flag',
8270: p_lookup_type => 'YES/NO',
8305:
8306: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_ONE_PRIMARY_SU' );
8307: FND_MESSAGE.SET_TOKEN( 'SITE_CODE', l_site_use_code );
8308: FND_MSG_PUB.ADD;
8309: x_return_status := FND_API.G_RET_STS_ERROR;
8310:
8311: EXCEPTION
8312: WHEN NO_DATA_FOUND THEN
8313: NULL;
8326: -- check to ensure that an inactive site use is never marked
8327: -- as primary.
8328: IF p_create_update_flag = 'C' THEN
8329: IF p_cust_site_use_rec.primary_flag IS NULL OR
8330: p_cust_site_use_rec.primary_flag = fnd_api.g_miss_char
8331: THEN
8332: l_primary_flag := 'N';
8333: ELSE
8334: l_primary_flag := p_cust_site_use_rec.primary_flag;
8334: l_primary_flag := p_cust_site_use_rec.primary_flag;
8335: END IF;
8336:
8337: IF p_cust_site_use_rec.status IS NULL OR
8338: p_cust_site_use_rec.status = fnd_api.g_miss_char
8339: THEN
8340: l_status := 'A';
8341: ELSE
8342: l_status := p_cust_site_use_rec.status;
8361: fnd_message.set_token('ENTITY', 'Site Use');
8362: -- fnd_message.set_token('COLUMN1', 'primary_flag');
8363: -- fnd_message.set_token('COLUMN2', 'status');
8364: fnd_msg_pub.add;
8365: x_return_status := FND_API.G_RET_STS_ERROR;
8366: -- reset l_error for later use.
8367: l_error := FALSE;
8368: END IF;
8369:
8390: ----------------------------------------------
8391:
8392: -- ship_partial is lookup code in lookup type YES/NO
8393: IF p_cust_site_use_rec.ship_partial IS NOT NULL AND
8394: p_cust_site_use_rec.ship_partial <> FND_API.G_MISS_CHAR
8395: THEN
8396: validate_lookup (
8397: p_column => 'ship_partial',
8398: p_lookup_type => 'YES/NO',
8422: /****Logical APIs - validation not required****/
8423: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8424: -- ship_sets_include_lines_flag is lookup code in lookup type YES/NO
8425: IF p_cust_site_use_rec.ship_sets_include_lines_flag IS NOT NULL AND
8426: p_cust_site_use_rec.ship_sets_include_lines_flag <> FND_API.G_MISS_CHAR
8427: THEN
8428: validate_lookup (
8429: p_column => 'ship_sets_include_lines_flag',
8430: p_lookup_type => 'YES/NO',
8444: --If ship_sets_include_lines_flag is set to Y then arrivalsets_include_lines_flag
8445: --is always set to N.
8446: IF (p_create_update_flag = 'C' or
8447: (p_create_update_flag ='U' and
8448: p_cust_site_use_rec.ship_sets_include_lines_flag <> NVL(l_ship_sets_inc_lines_f,FND_API.G_MISS_CHAR)))
8449: THEN
8450: IF p_cust_site_use_rec.ship_sets_include_lines_flag = 'Y'
8451: THEN
8452: BEGIN
8463: FND_MESSAGE.SET_TOKEN('VALUE1','Y');
8464: FND_MESSAGE.SET_TOKEN('COLUMN2','arrivalsets_include_lines_flag');
8465: FND_MESSAGE.SET_TOKEN('VALUE2','N');
8466: FND_MSG_PUB.ADD;
8467: x_return_status := FND_API.G_RET_STS_ERROR;
8468: END IF;
8469: END ;
8470: END IF;
8471: END IF;
8490: /****Logical APIs - validation not required****/
8491: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8492: -- arrivalsets_include_lines_flag is lookup code in lookup type YES/NO
8493: IF p_cust_site_use_rec.arrivalsets_include_lines_flag IS NOT NULL AND
8494: p_cust_site_use_rec.arrivalsets_include_lines_flag <> FND_API.G_MISS_CHAR
8495: THEN
8496: validate_lookup (
8497: p_column => 'arrivalsets_include_lines_flag',
8498: p_lookup_type => 'YES/NO',
8513: --is always set to N.
8514:
8515: IF (p_create_update_flag ='C' or
8516: (p_create_update_flag ='U' and
8517: p_cust_site_use_rec.arrivalsets_include_lines_flag <> NVL(l_arrivalsets_inc_lines_f,FND_API.G_MISS_CHAR)))
8518: THEN
8519: IF p_cust_site_use_rec.arrivalsets_include_lines_flag = 'Y'
8520: THEN
8521: BEGIN
8532: FND_MESSAGE.SET_TOKEN('VALUE1','Y');
8533: FND_MESSAGE.SET_TOKEN('COLUMN2','ship_sets_include_lines_flag');
8534: FND_MESSAGE.SET_TOKEN('VALUE2','N');
8535: FND_MSG_PUB.ADD;
8536: x_return_status := FND_API.G_RET_STS_ERROR;
8537: END IF;
8538: END ;
8539: END IF;
8540: END IF;
8558: ------------------------------------------
8559: --Tax_code should be a valid value defined in zx_output_classifications_v
8560:
8561: IF p_cust_site_use_rec.tax_code is NOT NULL AND
8562: p_cust_site_use_rec.tax_code <> FND_API.G_MISS_CHAR
8563: AND ( p_create_update_flag = 'C' OR
8564: ( p_create_update_flag = 'U' AND
8565: p_cust_site_use_rec.tax_code <> NVL( l_tax_code, FND_API.G_MISS_CHAR ) ) )
8566: THEN
8561: IF p_cust_site_use_rec.tax_code is NOT NULL AND
8562: p_cust_site_use_rec.tax_code <> FND_API.G_MISS_CHAR
8563: AND ( p_create_update_flag = 'C' OR
8564: ( p_create_update_flag = 'U' AND
8565: p_cust_site_use_rec.tax_code <> NVL( l_tax_code, FND_API.G_MISS_CHAR ) ) )
8566: THEN
8567: check_tax_code(
8568: p_column => 'tax_code',
8569: p_column_value => p_cust_site_use_rec.tax_code,
8592: ------------------------------------
8593: --Payment_term_id should be a valid value defined in RA_TERMS
8594:
8595: IF p_cust_site_use_rec.payment_term_id is NOT NULL AND
8596: p_cust_site_use_rec.payment_term_id <> FND_API.G_MISS_NUM
8597: AND ( p_create_update_flag = 'C' OR
8598: ( p_create_update_flag = 'U' AND
8599: p_cust_site_use_rec.payment_term_id <> NVL( l_payment_term_id, FND_API.G_MISS_NUM ) ) )
8600: THEN
8595: IF p_cust_site_use_rec.payment_term_id is NOT NULL AND
8596: p_cust_site_use_rec.payment_term_id <> FND_API.G_MISS_NUM
8597: AND ( p_create_update_flag = 'C' OR
8598: ( p_create_update_flag = 'U' AND
8599: p_cust_site_use_rec.payment_term_id <> NVL( l_payment_term_id, FND_API.G_MISS_NUM ) ) )
8600: THEN
8601: check_payterm_id_fk(
8602: p_column => 'payment_term_id',
8603: p_column_value => p_cust_site_use_rec.payment_term_id,
8623: ---------------------------------------------
8624: --Demand_class_code is lookup_code in lookup_type DEMAND_CLASS in fnd_common_lookups
8625:
8626: IF p_cust_site_use_rec.demand_class_code is NOT NULL AND
8627: p_cust_site_use_rec.demand_class_code <> FND_API.G_MISS_CHAR
8628: AND ( p_create_update_flag = 'C' OR
8629: ( p_create_update_flag = 'U' AND
8630: p_cust_site_use_rec.demand_class_code <> NVL(l_demand_class_code,FND_API.G_MISS_CHAR) ) )
8631: THEN
8626: IF p_cust_site_use_rec.demand_class_code is NOT NULL AND
8627: p_cust_site_use_rec.demand_class_code <> FND_API.G_MISS_CHAR
8628: AND ( p_create_update_flag = 'C' OR
8629: ( p_create_update_flag = 'U' AND
8630: p_cust_site_use_rec.demand_class_code <> NVL(l_demand_class_code,FND_API.G_MISS_CHAR) ) )
8631: THEN
8632: validate_lookup(
8633: p_column =>'demand_class_code',
8634: p_lookup_table =>'FND_COMMON_LOOKUPS',
8655: -----------------------------------------------
8656: --Primary_salesrep_id should be a valid value defined in RA_SALESREPS
8657:
8658: IF p_cust_site_use_rec.primary_salesrep_id is NOT NULL AND
8659: p_cust_site_use_rec.primary_salesrep_id <> FND_API.G_MISS_NUM
8660: AND ( p_create_update_flag = 'C' OR
8661: ( p_create_update_flag = 'U' AND
8662: p_cust_site_use_rec.primary_salesrep_id <> NVL(l_primary_salesrep_id,FND_API.G_MISS_NUM) ) )
8663: THEN
8658: IF p_cust_site_use_rec.primary_salesrep_id is NOT NULL AND
8659: p_cust_site_use_rec.primary_salesrep_id <> FND_API.G_MISS_NUM
8660: AND ( p_create_update_flag = 'C' OR
8661: ( p_create_update_flag = 'U' AND
8662: p_cust_site_use_rec.primary_salesrep_id <> NVL(l_primary_salesrep_id,FND_API.G_MISS_NUM) ) )
8663: THEN
8664: check_prim_salesrep(
8665: p_column => 'primary_salesrep_id',
8666: p_column_value => p_cust_site_use_rec.primary_salesrep_id,
8685: ----------------------------------------------------
8686: --validate finchrg_receivables_trx_id
8687: ----------------------------------------------------
8688: IF p_cust_site_use_rec.finchrg_receivables_trx_id is NOT NULL AND
8689: p_cust_site_use_rec.finchrg_receivables_trx_id <> FND_API.G_MISS_NUM
8690: AND ( p_create_update_flag = 'C' OR
8691: ( p_create_update_flag = 'U' AND
8692: p_cust_site_use_rec.finchrg_receivables_trx_id <> NVL(l_finchrg_receivables_trx_id,FND_API.G_MISS_NUM) ) )
8693: THEN
8688: IF p_cust_site_use_rec.finchrg_receivables_trx_id is NOT NULL AND
8689: p_cust_site_use_rec.finchrg_receivables_trx_id <> FND_API.G_MISS_NUM
8690: AND ( p_create_update_flag = 'C' OR
8691: ( p_create_update_flag = 'U' AND
8692: p_cust_site_use_rec.finchrg_receivables_trx_id <> NVL(l_finchrg_receivables_trx_id,FND_API.G_MISS_NUM) ) )
8693: THEN
8694: check_finchrg_trx_fk(
8695: p_column => 'finchrg_receivables_trx_id',
8696: p_column_value => p_cust_site_use_rec.finchrg_receivables_trx_id,
8721: -------------------------------------------
8722: --order_type_id should be a valid value defined in OE_ORDER_TYPES_V
8723:
8724: IF p_cust_site_use_rec.order_type_id is NOT NULL AND
8725: p_cust_site_use_rec.order_type_id <> FND_API.G_MISS_NUM
8726: AND ( p_create_update_flag = 'C' OR
8727: ( p_create_update_flag = 'U' AND
8728: p_cust_site_use_rec.order_type_id <> NVL(l_order_type_id,FND_API.G_MISS_NUM) ) )
8729: THEN
8724: IF p_cust_site_use_rec.order_type_id is NOT NULL AND
8725: p_cust_site_use_rec.order_type_id <> FND_API.G_MISS_NUM
8726: AND ( p_create_update_flag = 'C' OR
8727: ( p_create_update_flag = 'U' AND
8728: p_cust_site_use_rec.order_type_id <> NVL(l_order_type_id,FND_API.G_MISS_NUM) ) )
8729: THEN
8730: check_ord_type(
8731: p_column => 'order_type_id',
8732: p_column_value => p_cust_site_use_rec.order_type_id,
8755: ----------------------------------------------------------
8756: --price_list_id should be a valid value defined in SO_PRICE_LISTS
8757:
8758: IF p_cust_site_use_rec.price_list_id is NOT NULL AND
8759: p_cust_site_use_rec.price_list_id <> FND_API.G_MISS_NUM
8760: AND ( p_create_update_flag = 'C' OR
8761: ( p_create_update_flag = 'U' AND
8762: p_cust_site_use_rec.price_list_id <> NVL(l_price_list_id,FND_API.G_MISS_NUM) ) )
8763: THEN
8758: IF p_cust_site_use_rec.price_list_id is NOT NULL AND
8759: p_cust_site_use_rec.price_list_id <> FND_API.G_MISS_NUM
8760: AND ( p_create_update_flag = 'C' OR
8761: ( p_create_update_flag = 'U' AND
8762: p_cust_site_use_rec.price_list_id <> NVL(l_price_list_id,FND_API.G_MISS_NUM) ) )
8763: THEN
8764: check_price_list_fk(
8765: p_column => 'price_list_id',
8766: p_column_value => p_cust_site_use_rec.price_list_id,
8788: ----------------------------------------------
8789: --item_cross_ref_pref should be a value defined in MTL_CROSS_REFERENCE_TYPES or should have value 'INT' or 'CUST'
8790:
8791: IF p_cust_site_use_rec.item_cross_ref_pref IS NOT NULL AND
8792: p_cust_site_use_rec.item_cross_ref_pref <> FND_API.G_MISS_CHAR
8793: AND ( p_create_update_flag = 'C' OR
8794: ( p_create_update_flag = 'U' AND
8795: p_cust_site_use_rec.item_cross_ref_pref <> NVL(l_item_cross_ref_pref,FND_API.G_MISS_CHAR) ) )
8796: THEN
8791: IF p_cust_site_use_rec.item_cross_ref_pref IS NOT NULL AND
8792: p_cust_site_use_rec.item_cross_ref_pref <> FND_API.G_MISS_CHAR
8793: AND ( p_create_update_flag = 'C' OR
8794: ( p_create_update_flag = 'U' AND
8795: p_cust_site_use_rec.item_cross_ref_pref <> NVL(l_item_cross_ref_pref,FND_API.G_MISS_CHAR) ) )
8796: THEN
8797: IF p_cust_site_use_rec.item_cross_ref_pref NOT IN('INT','CUST')
8798: THEN
8799: check_item_cross_ref(
8823: --------------------------------------------------
8824: --warehouse_id should be a value defined in ORG_ORGANIZATION_DEFINITIONS
8825:
8826: IF p_cust_site_use_rec.warehouse_id is NOT NULL AND
8827: p_cust_site_use_rec.warehouse_id <> FND_API.G_MISS_NUM
8828: AND ( p_create_update_flag = 'C' OR
8829: ( p_create_update_flag = 'U' AND
8830: p_cust_site_use_rec.warehouse_id <> NVL(l_warehouse_id,FND_API.G_MISS_NUM) ) )
8831: THEN
8826: IF p_cust_site_use_rec.warehouse_id is NOT NULL AND
8827: p_cust_site_use_rec.warehouse_id <> FND_API.G_MISS_NUM
8828: AND ( p_create_update_flag = 'C' OR
8829: ( p_create_update_flag = 'U' AND
8830: p_cust_site_use_rec.warehouse_id <> NVL(l_warehouse_id,FND_API.G_MISS_NUM) ) )
8831: THEN
8832: check_warehouse(
8833: p_column => 'warehouse_id',
8834: p_column_value => p_cust_site_use_rec.warehouse_id,
8857: ----------------------------------------------
8858: --fob_point is lookup_code in lookup type FOB
8859:
8860: IF p_cust_site_use_rec.fob_point is NOT NULL AND
8861: p_cust_site_use_rec.fob_point <> FND_API.G_MISS_CHAR
8862: AND ( p_create_update_flag = 'C' OR
8863: ( p_create_update_flag = 'U' AND
8864: p_cust_site_use_rec.fob_point <> NVL(l_fob_point,FND_API.G_MISS_CHAR) ) )
8865: THEN
8860: IF p_cust_site_use_rec.fob_point is NOT NULL AND
8861: p_cust_site_use_rec.fob_point <> FND_API.G_MISS_CHAR
8862: AND ( p_create_update_flag = 'C' OR
8863: ( p_create_update_flag = 'U' AND
8864: p_cust_site_use_rec.fob_point <> NVL(l_fob_point,FND_API.G_MISS_CHAR) ) )
8865: THEN
8866: validate_lookup(
8867: p_column =>'fob_point',
8868: p_lookup_type =>'FOB',
8892: ----------------------------------------------------------
8893: --date_type_preference is a lookup_code in lookup_type REQUEST_DATE_TYPE in oe_lookups
8894:
8895: IF p_cust_site_use_rec.date_type_preference IS NOT NULL AND
8896: p_cust_site_use_rec.date_type_preference <> FND_API.G_MISS_CHAR
8897: AND ( p_create_update_flag = 'C' OR
8898: ( p_create_update_flag = 'U' AND
8899: p_cust_site_use_rec.date_type_preference <> NVL(l_date_type_preference,FND_API.G_MISS_CHAR) ) )
8900: THEN
8895: IF p_cust_site_use_rec.date_type_preference IS NOT NULL AND
8896: p_cust_site_use_rec.date_type_preference <> FND_API.G_MISS_CHAR
8897: AND ( p_create_update_flag = 'C' OR
8898: ( p_create_update_flag = 'U' AND
8899: p_cust_site_use_rec.date_type_preference <> NVL(l_date_type_preference,FND_API.G_MISS_CHAR) ) )
8900: THEN
8901: validate_lookup(
8902: p_column =>'date_type_preference',
8903: p_lookup_table =>'OE_LOOKUPS',
8928: ----------------------------------------------
8929:
8930: -- ship_partial is lookup code in lookup type YES/NO
8931: IF p_cust_site_use_rec.ship_partial IS NOT NULL AND
8932: p_cust_site_use_rec.ship_partial <> FND_API.G_MISS_CHAR
8933: AND ( p_create_update_flag = 'C' OR
8934: ( p_create_update_flag = 'U' AND
8935: p_cust_site_use_rec.ship_partial <> NVL(l_ship_partial,FND_API.G_MISS_CHAR) ) )
8936: THEN
8931: IF p_cust_site_use_rec.ship_partial IS NOT NULL AND
8932: p_cust_site_use_rec.ship_partial <> FND_API.G_MISS_CHAR
8933: AND ( p_create_update_flag = 'C' OR
8934: ( p_create_update_flag = 'U' AND
8935: p_cust_site_use_rec.ship_partial <> NVL(l_ship_partial,FND_API.G_MISS_CHAR) ) )
8936: THEN
8937: validate_lookup (
8938: p_column => 'ship_partial',
8939: p_lookup_type => 'YES/NO',
8963: /****Logical APIs - validation not required****/
8964: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8965: -- sched_date_push_flag is lookup code in lookup type YES/NO
8966: IF p_cust_site_use_rec.sched_date_push_flag IS NOT NULL AND
8967: p_cust_site_use_rec.sched_date_push_flag <> FND_API.G_MISS_CHAR
8968: THEN
8969: validate_lookup (
8970: p_column => 'sched_date_push_flag',
8971: p_lookup_type => 'YES/NO',
8995: /****Logical APIs - validation not required****/
8996: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
8997: -- gsa_indicator is lookup code in lookup type YES/NO
8998: IF p_cust_site_use_rec.gsa_indicator IS NOT NULL AND
8999: p_cust_site_use_rec.gsa_indicator <> FND_API.G_MISS_CHAR
9000: THEN
9001: validate_lookup (
9002: p_column => 'gsa_indicator',
9003: p_lookup_type => 'YES/NO',
9031: IF p_cust_site_use_rec.site_use_code <> 'BILL_TO' OR
9032: ( p_cust_site_use_rec.site_use_code IS NULL AND l_site_use_code <> 'BILL_TO' )
9033: THEN
9034: IF ( p_cust_site_use_rec.gl_id_rev IS NOT NULL AND
9035: p_cust_site_use_rec.gl_id_rev <> FND_API.G_MISS_NUM ) OR
9036: ( p_cust_site_use_rec.gl_id_tax IS NOT NULL AND
9037: p_cust_site_use_rec.gl_id_tax <> FND_API.G_MISS_NUM ) OR
9038: ( p_cust_site_use_rec.gl_id_freight IS NOT NULL AND
9039: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM ) OR
9033: THEN
9034: IF ( p_cust_site_use_rec.gl_id_rev IS NOT NULL AND
9035: p_cust_site_use_rec.gl_id_rev <> FND_API.G_MISS_NUM ) OR
9036: ( p_cust_site_use_rec.gl_id_tax IS NOT NULL AND
9037: p_cust_site_use_rec.gl_id_tax <> FND_API.G_MISS_NUM ) OR
9038: ( p_cust_site_use_rec.gl_id_freight IS NOT NULL AND
9039: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM ) OR
9040: ( p_cust_site_use_rec.gl_id_clearing IS NOT NULL AND
9041: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM ) OR
9035: p_cust_site_use_rec.gl_id_rev <> FND_API.G_MISS_NUM ) OR
9036: ( p_cust_site_use_rec.gl_id_tax IS NOT NULL AND
9037: p_cust_site_use_rec.gl_id_tax <> FND_API.G_MISS_NUM ) OR
9038: ( p_cust_site_use_rec.gl_id_freight IS NOT NULL AND
9039: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM ) OR
9040: ( p_cust_site_use_rec.gl_id_clearing IS NOT NULL AND
9041: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM ) OR
9042: ( p_cust_site_use_rec.gl_id_unbilled IS NOT NULL AND
9043: p_cust_site_use_rec.gl_id_unbilled <> FND_API.G_MISS_NUM ) OR
9037: p_cust_site_use_rec.gl_id_tax <> FND_API.G_MISS_NUM ) OR
9038: ( p_cust_site_use_rec.gl_id_freight IS NOT NULL AND
9039: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM ) OR
9040: ( p_cust_site_use_rec.gl_id_clearing IS NOT NULL AND
9041: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM ) OR
9042: ( p_cust_site_use_rec.gl_id_unbilled IS NOT NULL AND
9043: p_cust_site_use_rec.gl_id_unbilled <> FND_API.G_MISS_NUM ) OR
9044: ( p_cust_site_use_rec.gl_id_unearned IS NOT NULL AND
9045: p_cust_site_use_rec.gl_id_unearned <> FND_API.G_MISS_NUM )
9039: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM ) OR
9040: ( p_cust_site_use_rec.gl_id_clearing IS NOT NULL AND
9041: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM ) OR
9042: ( p_cust_site_use_rec.gl_id_unbilled IS NOT NULL AND
9043: p_cust_site_use_rec.gl_id_unbilled <> FND_API.G_MISS_NUM ) OR
9044: ( p_cust_site_use_rec.gl_id_unearned IS NOT NULL AND
9045: p_cust_site_use_rec.gl_id_unearned <> FND_API.G_MISS_NUM )
9046: THEN
9047: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9041: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM ) OR
9042: ( p_cust_site_use_rec.gl_id_unbilled IS NOT NULL AND
9043: p_cust_site_use_rec.gl_id_unbilled <> FND_API.G_MISS_NUM ) OR
9044: ( p_cust_site_use_rec.gl_id_unearned IS NOT NULL AND
9045: p_cust_site_use_rec.gl_id_unearned <> FND_API.G_MISS_NUM )
9046: THEN
9047: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9048: FND_MSG_PUB.ADD;
9049: x_return_status := FND_API.G_RET_STS_ERROR;
9045: p_cust_site_use_rec.gl_id_unearned <> FND_API.G_MISS_NUM )
9046: THEN
9047: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9048: FND_MSG_PUB.ADD;
9049: x_return_status := FND_API.G_RET_STS_ERROR;
9050: END IF;
9051:
9052: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
9053: hz_utility_v2pub.debug(p_message=>'If site_use_code is not BILL_TO, then Revenue, Tax, Freight, Clearing, ' ||
9065: IF p_cust_site_use_rec.site_use_code <> 'DRAWEE' OR
9066: ( p_cust_site_use_rec.site_use_code IS NULL AND l_site_use_code <> 'DRAWEE' )
9067: THEN
9068: IF ( p_cust_site_use_rec.gl_id_unpaid_rec IS NOT NULL AND
9069: p_cust_site_use_rec.gl_id_unpaid_rec <> FND_API.G_MISS_NUM ) OR
9070: ( p_cust_site_use_rec.gl_id_remittance IS NOT NULL AND
9071: p_cust_site_use_rec.gl_id_remittance <> FND_API.G_MISS_NUM ) OR
9072: ( p_cust_site_use_rec.gl_id_factor IS NOT NULL AND
9073: p_cust_site_use_rec.gl_id_factor <> FND_API.G_MISS_NUM )
9067: THEN
9068: IF ( p_cust_site_use_rec.gl_id_unpaid_rec IS NOT NULL AND
9069: p_cust_site_use_rec.gl_id_unpaid_rec <> FND_API.G_MISS_NUM ) OR
9070: ( p_cust_site_use_rec.gl_id_remittance IS NOT NULL AND
9071: p_cust_site_use_rec.gl_id_remittance <> FND_API.G_MISS_NUM ) OR
9072: ( p_cust_site_use_rec.gl_id_factor IS NOT NULL AND
9073: p_cust_site_use_rec.gl_id_factor <> FND_API.G_MISS_NUM )
9074: THEN
9075: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9069: p_cust_site_use_rec.gl_id_unpaid_rec <> FND_API.G_MISS_NUM ) OR
9070: ( p_cust_site_use_rec.gl_id_remittance IS NOT NULL AND
9071: p_cust_site_use_rec.gl_id_remittance <> FND_API.G_MISS_NUM ) OR
9072: ( p_cust_site_use_rec.gl_id_factor IS NOT NULL AND
9073: p_cust_site_use_rec.gl_id_factor <> FND_API.G_MISS_NUM )
9074: THEN
9075: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9076: FND_MSG_PUB.ADD;
9077: x_return_status := FND_API.G_RET_STS_ERROR;
9073: p_cust_site_use_rec.gl_id_factor <> FND_API.G_MISS_NUM )
9074: THEN
9075: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9076: FND_MSG_PUB.ADD;
9077: x_return_status := FND_API.G_RET_STS_ERROR;
9078: END IF;
9079:
9080: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
9081: hz_utility_v2pub.debug(p_message=>'If site_use_code is not DRAWEE, then Unpaid Bills Receivables, ' ||
9092: IF p_cust_site_use_rec.site_use_code NOT IN ('BILL_TO', 'DRAWEE') OR
9093: ( p_cust_site_use_rec.site_use_code IS NULL AND l_site_use_code NOT IN ('BILL_TO', 'DRAWEE') )
9094: THEN
9095: IF ( p_cust_site_use_rec.gl_id_rec IS NOT NULL AND
9096: p_cust_site_use_rec.gl_id_rec <> FND_API.G_MISS_NUM )
9097: THEN
9098: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9099: FND_MSG_PUB.ADD;
9100: x_return_status := FND_API.G_RET_STS_ERROR;
9096: p_cust_site_use_rec.gl_id_rec <> FND_API.G_MISS_NUM )
9097: THEN
9098: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_CCID_INVALID' );
9099: FND_MSG_PUB.ADD;
9100: x_return_status := FND_API.G_RET_STS_ERROR;
9101: END IF;
9102:
9103: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
9104: hz_utility_v2pub.debug(p_message=>'If site_use_code is not BILL_TO OR DRAWEE, then Bills Receivables field should not be populated. ' ||
9115: l_coa_id := HZ_MO_GLOBAL_CACHE.get_chart_of_accounts_id(
9116: p_cust_site_use_rec.org_id);
9117: EXCEPTION
9118: WHEN NO_DATA_FOUND THEN
9119: x_return_status := FND_API.G_RET_STS_ERROR;
9120: END;
9121:
9122: /* 3456489. Validate accounts only if Chart of Accounts ID is not null. */
9123: IF (l_coa_id IS NOT NULL) THEN
9128:
9129:
9130: -- gl_id_rec is validate gl field.
9131: IF p_cust_site_use_rec.gl_id_rec IS NOT NULL AND
9132: p_cust_site_use_rec.gl_id_rec <> FND_API.G_MISS_NUM
9133: THEN
9134: validate_gl_id (
9135: p_gl_name => 'REC',
9136: p_column_value => p_cust_site_use_rec.gl_id_rec,
9151: ----------------------------------------------
9152:
9153: -- gl_id_rev is validate gl field.
9154: IF p_cust_site_use_rec.gl_id_rev IS NOT NULL AND
9155: p_cust_site_use_rec.gl_id_rev <> FND_API.G_MISS_NUM
9156: THEN
9157: validate_gl_id (
9158: p_gl_name => 'REV',
9159: p_column_value => p_cust_site_use_rec.gl_id_rev,
9174: ----------------------------------------------
9175:
9176: -- gl_id_tax is validate gl field.
9177: IF p_cust_site_use_rec.gl_id_tax IS NOT NULL AND
9178: p_cust_site_use_rec.gl_id_tax <> FND_API.G_MISS_NUM
9179: THEN
9180: validate_gl_id (
9181: p_gl_name => 'TAX',
9182: p_column_value => p_cust_site_use_rec.gl_id_tax,
9197: ----------------------------------------------
9198:
9199: -- gl_id_freight is validate gl field.
9200: IF p_cust_site_use_rec.gl_id_freight IS NOT NULL AND
9201: p_cust_site_use_rec.gl_id_freight <> FND_API.G_MISS_NUM
9202: THEN
9203: validate_gl_id (
9204: p_gl_name => 'FREIGHT',
9205: p_column_value => p_cust_site_use_rec.gl_id_freight,
9220: ----------------------------------------------
9221:
9222: -- gl_id_clearing is validate gl field.
9223: IF p_cust_site_use_rec.gl_id_clearing IS NOT NULL AND
9224: p_cust_site_use_rec.gl_id_clearing <> FND_API.G_MISS_NUM
9225: THEN
9226: validate_gl_id (
9227: p_gl_name => 'CLEARING',
9228: p_column_value => p_cust_site_use_rec.gl_id_clearing,
9243: ----------------------------------------------
9244:
9245: -- gl_id_unbilled is validate gl field.
9246: IF p_cust_site_use_rec.gl_id_unbilled IS NOT NULL AND
9247: p_cust_site_use_rec.gl_id_unbilled <> FND_API.G_MISS_NUM
9248: THEN
9249: validate_gl_id (
9250: p_gl_name => 'UNBILLED',
9251: p_column_value => p_cust_site_use_rec.gl_id_unbilled,
9266: ----------------------------------------------
9267:
9268: -- gl_id_unearned is validate gl field.
9269: IF p_cust_site_use_rec.gl_id_unearned IS NOT NULL AND
9270: p_cust_site_use_rec.gl_id_unearned <> FND_API.G_MISS_NUM
9271: THEN
9272: validate_gl_id (
9273: p_gl_name => 'UNEARNED',
9274: p_column_value => p_cust_site_use_rec.gl_id_unearned,
9289: ----------------------------------------------
9290:
9291: -- gl_id_unpaid_rec is validate gl field.
9292: IF p_cust_site_use_rec.gl_id_unpaid_rec IS NOT NULL AND
9293: p_cust_site_use_rec.gl_id_unpaid_rec <> FND_API.G_MISS_NUM
9294: THEN
9295: validate_gl_id (
9296: p_gl_name => 'UNPAID_REC',
9297: p_column_value => p_cust_site_use_rec.gl_id_unpaid_rec,
9312: ----------------------------------------------
9313:
9314: -- gl_id_remittance is validate gl field.
9315: IF p_cust_site_use_rec.gl_id_remittance IS NOT NULL AND
9316: p_cust_site_use_rec.gl_id_remittance <> FND_API.G_MISS_NUM
9317: THEN
9318: validate_gl_id (
9319: p_gl_name => 'REMITTANCE',
9320: p_column_value => p_cust_site_use_rec.gl_id_remittance,
9335: ----------------------------------------------
9336:
9337: -- gl_id_factor is validate gl field.
9338: IF p_cust_site_use_rec.gl_id_factor IS NOT NULL AND
9339: p_cust_site_use_rec.gl_id_factor <> FND_API.G_MISS_NUM
9340: THEN
9341: validate_gl_id (
9342: p_gl_name => 'FACTOR',
9343: p_column_value => p_cust_site_use_rec.gl_id_factor,
9367:
9368: -- ship_via is foreign key to oe_ship_methods_v
9369: -- can be used in both single and multi org case.
9370: IF p_cust_site_use_rec.ship_via IS NOT NULL AND
9371: p_cust_site_use_rec.ship_via <> FND_API.G_MISS_CHAR AND
9372: ( p_create_update_flag = 'C' OR
9373: ( p_create_update_flag = 'U' AND
9374: p_cust_site_use_rec.ship_via <> NVL( l_ship_via, FND_API.G_MISS_CHAR ) ) )
9375: THEN
9370: IF p_cust_site_use_rec.ship_via IS NOT NULL AND
9371: p_cust_site_use_rec.ship_via <> FND_API.G_MISS_CHAR AND
9372: ( p_create_update_flag = 'C' OR
9373: ( p_create_update_flag = 'U' AND
9374: p_cust_site_use_rec.ship_via <> NVL( l_ship_via, FND_API.G_MISS_CHAR ) ) )
9375: THEN
9376: check_oe_ship_methods_v_fk (
9377: p_entity => 'SITE_USE',
9378: p_column => 'ship_via',
9446: * p_cust_account_role_rec Customer account role record.
9447: * p_rowid Rowid of the record (used only in update mode).
9448: * IN/OUT:
9449: * x_return_status Return status after the call. The status can
9450: * be FND_API.G_RET_STS_SUCCESS (success),
9451: * FND_API.G_RET_STS_ERROR (error),
9452: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9453: *
9454: * NOTES
9447: * p_rowid Rowid of the record (used only in update mode).
9448: * IN/OUT:
9449: * x_return_status Return status after the call. The status can
9450: * be FND_API.G_RET_STS_SUCCESS (success),
9451: * FND_API.G_RET_STS_ERROR (error),
9452: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9453: *
9454: * NOTES
9455: *
9448: * IN/OUT:
9449: * x_return_status Return status after the call. The status can
9450: * be FND_API.G_RET_STS_SUCCESS (success),
9451: * FND_API.G_RET_STS_ERROR (error),
9452: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
9453: *
9454: * NOTES
9455: *
9456: * MODIFICATION HISTORY
9522: -- If primary key value is not passed, it will be generated
9523: -- from sequence by table handler.
9524:
9525: IF p_cust_account_role_rec.cust_account_role_id IS NOT NULL AND
9526: p_cust_account_role_rec.cust_account_role_id <> FND_API.G_MISS_NUM
9527: THEN
9528: BEGIN
9529: SELECT 'Y' INTO l_dummy
9530: FROM HZ_CUST_ACCOUNT_ROLES
9532:
9533: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
9534: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_role_id' );
9535: FND_MSG_PUB.ADD;
9536: x_return_status := FND_API.G_RET_STS_ERROR;
9537: EXCEPTION
9538: WHEN NO_DATA_FOUND THEN
9539: NULL;
9540: END;
9613: -- check FK during creation.
9614:
9615: IF p_create_update_flag = 'C' AND
9616: p_cust_account_role_rec.party_id IS NOT NULL AND
9617: p_cust_account_role_rec.party_id <> FND_API.G_MISS_NUM
9618: THEN
9619: check_party_fk (
9620: p_column => 'party_id',
9621: p_column_value => p_cust_account_role_rec.party_id,
9687: -- we only need to check FK during creation.
9688:
9689: IF p_create_update_flag = 'C' AND
9690: p_cust_account_role_rec.cust_account_id IS NOT NULL AND
9691: p_cust_account_role_rec.cust_account_id <> FND_API.G_MISS_NUM
9692: THEN
9693: check_cust_account_fk (
9694: p_column => 'cust_account_id',
9695: p_column_value => p_cust_account_role_rec.cust_account_id,
9743: -- check FK during creation.
9744:
9745: IF p_create_update_flag = 'C' AND
9746: p_cust_account_role_rec.cust_acct_site_id IS NOT NULL AND
9747: p_cust_account_role_rec.cust_acct_site_id <> FND_API.G_MISS_NUM
9748: THEN
9749: BEGIN
9750:
9751: SELECT CUST_ACCOUNT_ID,ORG_ID
9758: MO_GLOBAL.validate_orgid_pub_api(l_org_id,'N',l_return_status);
9759: EXCEPTION
9760: WHEN OTHERS
9761: THEN
9762: x_return_status := FND_API.G_RET_STS_ERROR;
9763: END;
9764:
9765: IF l_cust_account_id <> p_cust_account_role_rec.cust_account_id THEN
9766: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_ACCT_SITE_MISSMATCH' );
9764:
9765: IF l_cust_account_id <> p_cust_account_role_rec.cust_account_id THEN
9766: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_ACCT_SITE_MISSMATCH' );
9767: FND_MSG_PUB.ADD;
9768: x_return_status := FND_API.G_RET_STS_ERROR;
9769: END IF;
9770:
9771: EXCEPTION
9772: WHEN NO_DATA_FOUND THEN
9774: FND_MESSAGE.SET_TOKEN( 'FK', 'cust_acct_site_id' );
9775: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id' );
9776: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_acct_sites' );
9777: FND_MSG_PUB.ADD;
9778: x_return_status := FND_API.G_RET_STS_ERROR;
9779: END;
9780:
9781: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
9782: hz_utility_v2pub.debug(p_message=>'cust_acct_site_id is foreign key to hz_cust_acct_sites and cust_account_id in hz_cust_acct_sites should be same as we put in cust_account_id in hz_cust_account_roles.' ||
9843: -- during creation.
9844:
9845: IF p_create_update_flag = 'C' AND
9846: p_cust_account_role_rec.role_type IS NOT NULL AND
9847: p_cust_account_role_rec.role_type <> FND_API.G_MISS_CHAR
9848: THEN
9849: validate_lookup (
9850: p_column => 'role_type',
9851: p_lookup_type => 'ACCT_ROLE_TYPE',
9867: -- three columns are non-updateable.
9868:
9869: IF p_create_update_flag = 'C' THEN
9870: IF p_cust_account_role_rec.cust_acct_site_id IS NULL OR
9871: p_cust_account_role_rec.cust_acct_site_id = FND_API.G_MISS_NUM
9872: THEN
9873: BEGIN
9874: SELECT 'Y' INTO l_dummy
9875: FROM HZ_CUST_ACCOUNT_ROLES
9882:
9883: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
9884: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id - party_id - role_type' );
9885: FND_MSG_PUB.ADD;
9886: x_return_status := FND_API.G_RET_STS_ERROR;
9887: EXCEPTION
9888: WHEN NO_DATA_FOUND THEN
9889: NULL;
9890: END;
9908:
9909: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
9910: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id - party_id - role_type' );
9911: FND_MSG_PUB.ADD;
9912: x_return_status := FND_API.G_RET_STS_ERROR;
9913: EXCEPTION
9914: WHEN NO_DATA_FOUND THEN
9915: NULL;
9916: END;
9943:
9944: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
9945: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id - party_id - role_type' );
9946: FND_MSG_PUB.ADD;
9947: x_return_status := FND_API.G_RET_STS_ERROR;
9948: EXCEPTION
9949: WHEN NO_DATA_FOUND THEN
9950: NULL;
9951: END;
9972:
9973: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
9974: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_id - party_id - role_type' );
9975: FND_MSG_PUB.ADD;
9976: x_return_status := FND_API.G_RET_STS_ERROR;
9977: EXCEPTION
9978: WHEN NO_DATA_FOUND THEN
9979: NULL;
9980: END;
10002: -- validate orig_system_reference
10003: ----------------------------------------------
10004: /****Logical APIs - validation not required****/
10005: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10006: IF (p_cust_account_role_rec.orig_system is not null and p_cust_account_role_rec.orig_system <>fnd_api.g_miss_char)
10007: and (p_cust_account_role_rec.orig_system_reference is not null and p_cust_account_role_rec.orig_system_reference <>fnd_api.g_miss_char)
10008: and p_create_update_flag = 'U'
10009: then
10010: hz_orig_system_ref_pub.get_owner_table_id
10003: ----------------------------------------------
10004: /****Logical APIs - validation not required****/
10005: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10006: IF (p_cust_account_role_rec.orig_system is not null and p_cust_account_role_rec.orig_system <>fnd_api.g_miss_char)
10007: and (p_cust_account_role_rec.orig_system_reference is not null and p_cust_account_role_rec.orig_system_reference <>fnd_api.g_miss_char)
10008: and p_create_update_flag = 'U'
10009: then
10010: hz_orig_system_ref_pub.get_owner_table_id
10011: (p_orig_system => p_cust_account_role_rec.orig_system,
10013: p_owner_table_name => 'HZ_CUST_ACCOUNT_ROLES',
10014: x_owner_table_id => l_mosr_owner_table_id,
10015: x_return_status => l_temp_return_status);
10016:
10017: IF (l_temp_return_status = fnd_api.g_ret_sts_success AND
10018: l_mosr_owner_table_id= nvl(p_cust_account_role_rec.cust_account_role_id,l_mosr_owner_table_id))
10019: THEN
10020: l_validate_flag := 'N';
10021: END IF;
10023: -- Call to hz_orig_system_ref_pub.get_owner_table_id API was resetting the
10024: -- x_return_status. Set x_return_status to error, ONLY if there is error.
10025: -- In case of success, leave it to carry over previous value as before this call.
10026: -- Fix for Bug 5498116 (29-AUG-2006)
10027: IF (l_temp_return_status = FND_API.G_RET_STS_ERROR) THEN
10028: x_return_status := l_temp_return_status;
10029: END IF;
10030:
10031: end if;
10064: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10065:
10066: -- primary_flag is lookup code in lookup type YES/NO
10067: IF p_cust_account_role_rec.primary_flag IS NOT NULL AND
10068: p_cust_account_role_rec.primary_flag <> FND_API.G_MISS_CHAR
10069: THEN
10070: validate_lookup (
10071: p_column => 'primary_flag',
10072: p_lookup_type => 'YES/NO',
10091: l_cust_acct_site_id := p_cust_account_role_rec.cust_acct_site_id;
10092: END IF;
10093:
10094: IF l_cust_acct_site_id IS NULL OR
10095: l_cust_acct_site_id = FND_API.G_MISS_NUM
10096: THEN
10097: BEGIN
10098: SELECT 'Y' INTO l_dummy
10099: FROM HZ_CUST_ACCOUNT_ROLES
10106:
10107: FND_MESSAGE.SET_NAME( 'AR', 'HZ_CUST_ACCT_ROLE_PRIMARY' );
10108: FND_MESSAGE.SET_TOKEN( 'ENTITY', 'account' );
10109: FND_MSG_PUB.ADD;
10110: x_return_status := FND_API.G_RET_STS_ERROR;
10111: EXCEPTION
10112: WHEN NO_DATA_FOUND THEN
10113: NULL;
10114: END;
10133:
10134: FND_MESSAGE.SET_NAME( 'AR', 'HZ_CUST_ACCT_ROLE_PRIMARY' );
10135: FND_MESSAGE.SET_TOKEN( 'ENTITY', 'account site' );
10136: FND_MSG_PUB.ADD;
10137: x_return_status := FND_API.G_RET_STS_ERROR;
10138: EXCEPTION
10139: WHEN NO_DATA_FOUND THEN
10140: NULL;
10141: END;
10184: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10185: -- status is lookup code in lookup type REGISTRY_STATUS because
10186: -- the status is used in party merge.
10187: IF p_cust_account_role_rec.status IS NOT NULL AND
10188: p_cust_account_role_rec.status <> FND_API.G_MISS_CHAR AND
10189: ( p_create_update_flag = 'C' OR
10190: ( p_create_update_flag = 'U' AND
10191: p_cust_account_role_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
10192: THEN
10187: IF p_cust_account_role_rec.status IS NOT NULL AND
10188: p_cust_account_role_rec.status <> FND_API.G_MISS_CHAR AND
10189: ( p_create_update_flag = 'C' OR
10190: ( p_create_update_flag = 'U' AND
10191: p_cust_account_role_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
10192: THEN
10193: validate_lookup (
10194: p_column => 'status',
10195: p_lookup_type => 'REGISTRY_STATUS',
10263: * p_role_responsibility_rec Customer account role responsibility record.
10264: * p_rowid Rowid of the record (used only in update mode).
10265: * IN/OUT:
10266: * x_return_status Return status after the call. The status can
10267: * be FND_API.G_RET_STS_SUCCESS (success),
10268: * FND_API.G_RET_STS_ERROR (error),
10269: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10270: *
10271: * NOTES
10264: * p_rowid Rowid of the record (used only in update mode).
10265: * IN/OUT:
10266: * x_return_status Return status after the call. The status can
10267: * be FND_API.G_RET_STS_SUCCESS (success),
10268: * FND_API.G_RET_STS_ERROR (error),
10269: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10270: *
10271: * NOTES
10272: *
10265: * IN/OUT:
10266: * x_return_status Return status after the call. The status can
10267: * be FND_API.G_RET_STS_SUCCESS (success),
10268: * FND_API.G_RET_STS_ERROR (error),
10269: * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
10270: *
10271: * NOTES
10272: *
10273: * MODIFICATION HISTORY
10326: -- If primary key value is not passed, it will be generated
10327: -- from sequence by table handler.
10328:
10329: IF p_role_responsibility_rec.responsibility_id IS NOT NULL AND
10330: p_role_responsibility_rec.responsibility_id <> FND_API.G_MISS_NUM
10331: THEN
10332: BEGIN
10333: SELECT 'Y' INTO l_dummy
10334: FROM HZ_ROLE_RESPONSIBILITY
10336:
10337: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
10338: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'responsibility_id' );
10339: FND_MSG_PUB.ADD;
10340: x_return_status := FND_API.G_RET_STS_ERROR;
10341: EXCEPTION
10342: WHEN NO_DATA_FOUND THEN
10343: NULL;
10344: END;
10384:
10385: -- responsibility_type is lookup code in lookup type SITE_USE_CODE
10386:
10387: IF p_role_responsibility_rec.responsibility_type IS NOT NULL AND
10388: p_role_responsibility_rec.responsibility_type <> FND_API.G_MISS_CHAR AND
10389: ( p_create_update_flag = 'C' OR
10390: ( p_create_update_flag = 'U' AND
10391: p_role_responsibility_rec.responsibility_type <> NVL( l_responsibility_type, FND_API.G_MISS_CHAR ) ) )
10392: THEN
10387: IF p_role_responsibility_rec.responsibility_type IS NOT NULL AND
10388: p_role_responsibility_rec.responsibility_type <> FND_API.G_MISS_CHAR AND
10389: ( p_create_update_flag = 'C' OR
10390: ( p_create_update_flag = 'U' AND
10391: p_role_responsibility_rec.responsibility_type <> NVL( l_responsibility_type, FND_API.G_MISS_CHAR ) ) )
10392: THEN
10393: validate_lookup (
10394: p_column => 'responsibility_type',
10395: p_lookup_type => 'SITE_USE_CODE',
10465: -- we only need to check FK during creation.
10466:
10467: IF p_create_update_flag = 'C' AND
10468: p_role_responsibility_rec.cust_account_role_id IS NOT NULL AND
10469: p_role_responsibility_rec.cust_account_role_id <> FND_API.G_MISS_NUM
10470: THEN
10471: check_cust_account_role_fk (
10472: p_column => 'cust_account_role_id',
10473: p_column_value => p_role_responsibility_rec.cust_account_role_id,
10507:
10508: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
10509: FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_role_id - responsibility_type' );
10510: FND_MSG_PUB.ADD;
10511: x_return_status := FND_API.G_RET_STS_ERROR;
10512: EXCEPTION
10513: WHEN NO_DATA_FOUND THEN
10514: NULL;
10515: END;
10568: /****Logical APIs - validation not required****/
10569: IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
10570: -- primary_flag is lookup code in lookup type YES/NO
10571: IF p_role_responsibility_rec.primary_flag IS NOT NULL AND
10572: p_role_responsibility_rec.primary_flag <> FND_API.G_MISS_CHAR
10573: THEN
10574: validate_lookup (
10575: p_column => 'primary_flag',
10576: p_lookup_type => 'YES/NO',
10603: AND ROWNUM = 1;
10604:
10605: FND_MESSAGE.SET_NAME( 'AR', 'AR_CUST_ROLE_PRIMARY' );
10606: FND_MSG_PUB.ADD;
10607: x_return_status := FND_API.G_RET_STS_ERROR;
10608: EXCEPTION
10609: WHEN NO_DATA_FOUND THEN
10610: NULL;
10611: END;
10687: FND_MESSAGE.SET_TOKEN( 'FK', 'cust_account_id' );
10688: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10689: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_accounts' );
10690: FND_MSG_PUB.ADD;
10691: x_return_status := FND_API.G_RET_STS_ERROR;
10692:
10693: END check_cust_account_fk;
10694:
10695: PROCEDURE check_cust_acct_site_fk (
10714: FND_MESSAGE.SET_TOKEN( 'FK', 'cust_acct_site_id' );
10715: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10716: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_acct_sites' );
10717: FND_MSG_PUB.ADD;
10718: x_return_status := FND_API.G_RET_STS_ERROR;
10719:
10720: END check_cust_acct_site_fk;
10721:
10722: PROCEDURE check_cust_site_use_fk (
10738: MO_GLOBAL.validate_orgid_pub_api(l_org_id,'N',l_return_status);
10739: EXCEPTION
10740: WHEN OTHERS
10741: THEN
10742: x_return_status := FND_API.G_RET_STS_ERROR;
10743: END;
10744:
10745: EXCEPTION
10746: WHEN NO_DATA_FOUND THEN
10748: FND_MESSAGE.SET_TOKEN( 'FK', 'site_use_id' );
10749: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10750: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_site_uses' );
10751: FND_MSG_PUB.ADD;
10752: x_return_status := FND_API.G_RET_STS_ERROR;
10753:
10754: END check_cust_site_use_fk;
10755: PROCEDURE check_cust_site_use_cont_fk(
10756: p_column IN VARCHAR2,
10772: FND_MESSAGE.SET_TOKEN( 'FK', 'contact_id' );
10773: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10774: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_account_roles' );
10775: FND_MSG_PUB.ADD;
10776: x_return_status := FND_API.G_RET_STS_ERROR;
10777:
10778: END check_cust_site_use_cont_fk;
10779:
10780: PROCEDURE check_cust_account_role_fk (
10796: FND_MESSAGE.SET_TOKEN( 'FK', 'cust_account_role_id' );
10797: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10798: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_account_roles' );
10799: FND_MSG_PUB.ADD;
10800: x_return_status := FND_API.G_RET_STS_ERROR;
10801:
10802: END check_cust_account_role_fk;
10803:
10804: PROCEDURE check_per_all_people_f_fk (
10829: FND_MESSAGE.SET_TOKEN( 'FK', 'person_id' );
10830: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10831: FND_MESSAGE.SET_TOKEN( 'TABLE', 'per_all_people_f' );
10832: FND_MSG_PUB.ADD;
10833: x_return_status := FND_API.G_RET_STS_ERROR;
10834:
10835: END check_per_all_people_f_fk;
10836:
10837: PROCEDURE check_collector_fk (
10854: FND_MESSAGE.SET_TOKEN( 'FK', 'collector_id' );
10855: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10856: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_collectors');
10857: FND_MSG_PUB.ADD;
10858: x_return_status := FND_API.G_RET_STS_ERROR;
10859:
10860: END check_collector_fk;
10861:
10862: PROCEDURE check_party_fk (
10879: FND_MESSAGE.SET_TOKEN( 'FK', 'party_id' );
10880: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10881: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_parties' );
10882: FND_MSG_PUB.ADD;
10883: x_return_status := FND_API.G_RET_STS_ERROR;
10884:
10885: END check_party_fk;
10886:
10887: PROCEDURE check_party_site_fk (
10904: FND_MESSAGE.SET_TOKEN( 'FK', 'party_site_id' );
10905: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10906: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_party_sites' );
10907: FND_MSG_PUB.ADD;
10908: x_return_status := FND_API.G_RET_STS_ERROR;
10909: END;
10910:
10911: END check_party_site_fk;
10912:
10934: FND_MESSAGE.SET_TOKEN( 'FK', 'currency_code' );
10935: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10936: FND_MESSAGE.SET_TOKEN( 'TABLE', 'fnd_currencies' );
10937: FND_MSG_PUB.ADD;
10938: x_return_status := FND_API.G_RET_STS_ERROR;
10939:
10940: END check_currency_fk;
10941:
10942: PROCEDURE check_oe_ship_methods_v_fk (
10962: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_COLUMN_SHOULD_BE_NULL' );
10963: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
10964: FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_accounts' );
10965: FND_MSG_PUB.ADD;
10966: x_return_status := FND_API.G_RET_STS_ERROR;
10967:
10968: RETURN;
10969: END IF;
10970:
10972: EXCEPTION
10973: WHEN NO_DATA_FOUND THEN
10974: FND_MESSAGE.SET_NAME( 'AR', 'AR_NO_ROW_IN_SYSTEM_PARAMETERS');
10975: FND_MSG_PUB.ADD;
10976: x_return_status := FND_API.G_RET_STS_ERROR;
10977: END ;
10978: END IF;
10979: **************************************************************************/
10980: validate_lookup (
11017: FND_MESSAGE.SET_TOKEN( 'FK', 'payment_term_id' );
11018: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11019: FND_MESSAGE.SET_TOKEN( 'TABLE', 'RA_TERMS' );
11020: FND_MSG_PUB.ADD;
11021: x_return_status := FND_API.G_RET_STS_ERROR;
11022: END check_payterm_id_fk;
11023:
11024: PROCEDURE check_late_charge_term_id_fk (
11025: p_column IN VARCHAR2,
11048: -- Balance forward billing has not been enabled.
11049: -- Pass a late charge payment term that does not have a balance forward billing cycle.
11050: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_PAY_TERM_DISABLED' );
11051: FND_MSG_PUB.ADD;
11052: x_return_status := FND_API.G_RET_STS_ERROR;
11053: end;
11054: elsif p_cons_inv_flag = 'Y' then
11055: begin
11056: SELECT 'Y'
11066: -- Balance forward billing has been enabled.
11067: -- Pass a late charge payment term that has a balance forward billing cycle.
11068: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LC_PAY_TERM_ENABLED' );
11069: FND_MSG_PUB.ADD;
11070: x_return_status := FND_API.G_RET_STS_ERROR;
11071: end;
11072: end if;
11073:
11074: END check_late_charge_term_id_fk;
11095: FND_MESSAGE.SET_TOKEN( 'FK', 'standard_text_id' );
11096: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11097: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_standard_text' );
11098: FND_MSG_PUB.ADD;
11099: x_return_status := FND_API.G_RET_STS_ERROR;
11100: END check_message_text_id_fk;
11101:
11102: PROCEDURE check_exchange_rate_type_fk (
11103: p_column IN VARCHAR2,
11121: FND_MESSAGE.SET_TOKEN( 'FK', 'conversion_type' );
11122: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11123: FND_MESSAGE.SET_TOKEN( 'TABLE', 'gl_daily_conversion_types' );
11124: FND_MSG_PUB.ADD;
11125: x_return_status := FND_API.G_RET_STS_ERROR;
11126: END check_exchange_rate_type_fk;
11127:
11128: PROCEDURE check_charge_schedule_id_fk (
11129: p_column IN VARCHAR2,
11146: FND_MESSAGE.SET_TOKEN( 'FK', 'schedule_id' );
11147: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11148: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_charge_schedules' );
11149: FND_MSG_PUB.ADD;
11150: x_return_status := FND_API.G_RET_STS_ERROR;
11151: END check_charge_schedule_id_fk;
11152:
11153: PROCEDURE check_finchrg_trx_fk (
11154: p_column IN VARCHAR2,
11169: FND_MESSAGE.SET_TOKEN( 'FK', 'finchrg_receivables_trx_id' );
11170: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11171: FND_MESSAGE.SET_TOKEN( 'TABLE', 'AR_RECEIVABLES_TRX' );
11172: FND_MSG_PUB.ADD;
11173: x_return_status := FND_API.G_RET_STS_ERROR;
11174: END check_finchrg_trx_fk;
11175:
11176: PROCEDURE check_price_list_fk (
11177: p_column IN VARCHAR2,
11203: -- Bug 3591694 FND_MESSAGE.SET_TOKEN( 'TABLE', 'SO_PRICE_LISTS' );
11204: FND_MESSAGE.SET_TOKEN( 'TABLE', 'QP_LIST_HEADERS_B' );
11205:
11206: FND_MSG_PUB.ADD;
11207: x_return_status := FND_API.G_RET_STS_ERROR;
11208: END check_price_list_fk;
11209:
11210: PROCEDURE check_item_cross_ref(
11211: p_column IN VARCHAR2,
11223: FND_MESSAGE.SET_TOKEN( 'FK', 'item_cross_ref_pref' );
11224: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11225: FND_MESSAGE.SET_TOKEN( 'TABLE', 'mtl_cross_reference_types' );
11226: FND_MSG_PUB.ADD;
11227: x_return_status := FND_API.G_RET_STS_ERROR;
11228: END check_item_cross_ref;
11229:
11230: PROCEDURE check_tax_code(
11231: p_column IN VARCHAR2,
11280: FND_MESSAGE.SET_TOKEN( 'FK', 'tax_code' );
11281: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11282: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ZX_OUTPUT_CLASSIFICATIONS_V' );
11283: FND_MSG_PUB.ADD;
11284: x_return_status := FND_API.G_RET_STS_ERROR;
11285: END check_tax_code;
11286:
11287: PROCEDURE check_warehouse(
11288: p_column IN VARCHAR2,
11301: FND_MESSAGE.SET_TOKEN( 'FK', 'warehouse_id' );
11302: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11303: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ORG_ORGANIZATION_DEFINITIONS' );
11304: FND_MSG_PUB.ADD;
11305: x_return_status := FND_API.G_RET_STS_ERROR;
11306: END check_warehouse;
11307:
11308: PROCEDURE check_prim_salesrep(
11309: p_column IN VARCHAR2,
11331: FND_MESSAGE.SET_TOKEN( 'FK', 'primary_salesrep_id' );
11332: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11333: FND_MESSAGE.SET_TOKEN( 'TABLE', 'JTF_RS_SALESREPS' );
11334: FND_MSG_PUB.ADD;
11335: x_return_status := FND_API.G_RET_STS_ERROR;
11336: */
11337: x_return_status := FND_API.G_RET_STS_SUCCESS;
11338: END check_prim_salesrep;
11339:
11333: FND_MESSAGE.SET_TOKEN( 'TABLE', 'JTF_RS_SALESREPS' );
11334: FND_MSG_PUB.ADD;
11335: x_return_status := FND_API.G_RET_STS_ERROR;
11336: */
11337: x_return_status := FND_API.G_RET_STS_SUCCESS;
11338: END check_prim_salesrep;
11339:
11340: PROCEDURE check_ord_type(
11341: p_column IN VARCHAR2,
11370: FND_MESSAGE.SET_TOKEN( 'FK', 'order_type_id' );
11371: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11372: FND_MESSAGE.SET_TOKEN( 'TABLE', 'OE_TRANSACTIONS_TYPES_VL' );
11373: FND_MSG_PUB.ADD;
11374: x_return_status := FND_API.G_RET_STS_ERROR;
11375: */
11376: x_return_status := FND_API.G_RET_STS_SUCCESS;
11377: END check_ord_type;
11378:
11372: FND_MESSAGE.SET_TOKEN( 'TABLE', 'OE_TRANSACTIONS_TYPES_VL' );
11373: FND_MSG_PUB.ADD;
11374: x_return_status := FND_API.G_RET_STS_ERROR;
11375: */
11376: x_return_status := FND_API.G_RET_STS_SUCCESS;
11377: END check_ord_type;
11378:
11379: PROCEDURE check_partial_mandatory_column (
11380: p_column IN VARCHAR2,
11383: ) IS
11384:
11385: BEGIN
11386:
11387: IF p_column_value = FND_API.G_MISS_NUM THEN
11388: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
11389: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11390: FND_MSG_PUB.ADD;
11391: x_return_status := FND_API.G_RET_STS_ERROR;
11387: IF p_column_value = FND_API.G_MISS_NUM THEN
11388: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
11389: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11390: FND_MSG_PUB.ADD;
11391: x_return_status := FND_API.G_RET_STS_ERROR;
11392: END IF;
11393:
11394: END check_partial_mandatory_column;
11395:
11400: ) IS
11401:
11402: BEGIN
11403:
11404: IF p_column_value = FND_API.G_MISS_CHAR THEN
11405: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
11406: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11407: FND_MSG_PUB.ADD;
11408: x_return_status := FND_API.G_RET_STS_ERROR;
11404: IF p_column_value = FND_API.G_MISS_CHAR THEN
11405: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
11406: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11407: FND_MSG_PUB.ADD;
11408: x_return_status := FND_API.G_RET_STS_ERROR;
11409: END IF;
11410:
11411: END check_partial_mandatory_column;
11412:
11419:
11420: BEGIN
11421:
11422: IF p_column_value IS NOT NULL AND
11423: p_column_value <> FND_API.G_MISS_NUM
11424: THEN
11425: IF NOT FND_FLEX_KEYVAL.validate_ccid (
11426: appl_short_name => 'SQLGL',
11427: key_flex_code => 'GL#',
11432: THEN
11433: FND_MESSAGE.SET_NAME( 'AR', 'AR_AUTO_' ||
11434: UPPER( p_gl_name ) || '_CCID_INVALID' );
11435: FND_MSG_PUB.ADD;
11436: x_return_status := FND_API.G_RET_STS_ERROR;
11437: END IF;
11438: END IF;
11439:
11440: END validate_gl_id;
11460: FND_MESSAGE.SET_TOKEN( 'FK', p_column );
11461: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11462: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_autocash_hierarchies');
11463: FND_MSG_PUB.ADD;
11464: x_return_status := FND_API.G_RET_STS_ERROR;
11465:
11466: END check_auto_hierid_fk;
11467:
11468: PROCEDURE check_stat_cycid_fk (
11485: FND_MESSAGE.SET_TOKEN( 'FK', 'statement_cycle_id' );
11486: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11487: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_statement_cycles');
11488: FND_MSG_PUB.ADD;
11489: x_return_status := FND_API.G_RET_STS_ERROR;
11490:
11491: END check_stat_cycid_fk;
11492:
11493: PROCEDURE check_dunning_letid_fk (
11510: FND_MESSAGE.SET_TOKEN( 'FK', 'dunning_letter_set_id' );
11511: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11512: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ar_dunning_letter_sets');
11513: FND_MSG_PUB.ADD;
11514: x_return_status := FND_API.G_RET_STS_ERROR;
11515:
11516: END check_dunning_letid_fk;
11517:
11518: PROCEDURE check_standard_terms_fk (
11559: FND_MESSAGE.SET_TOKEN( 'FK', 'standard_terms' );
11560: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11561: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ra_terms_b');
11562: FND_MSG_PUB.ADD;
11563: x_return_status := FND_API.G_RET_STS_ERROR;
11564: end;
11565: elsif p_cons_inv_flag = 'Y' then
11566: begin
11567: SELECT 'Y'
11577: -- Balance forward billing has been enabled. Pass a payment term
11578: -- with a balance forward billing cycle associated.
11579: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_BAL_FWD_BILL_ENABLED' );
11580: FND_MSG_PUB.ADD;
11581: x_return_status := FND_API.G_RET_STS_ERROR;
11582: end;
11583: end if;
11584:
11585: /*
11596: FND_MESSAGE.SET_TOKEN( 'FK', 'standard_terms' );
11597: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11598: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ra_terms');
11599: FND_MSG_PUB.ADD;
11600: x_return_status := FND_API.G_RET_STS_ERROR;
11601: */
11602: END check_standard_terms_fk;
11603:
11604: PROCEDURE check_grouping_ruleid_fk (
11623: FND_MESSAGE.SET_TOKEN( 'FK', 'grouping_rule_id' );
11624: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11625: FND_MESSAGE.SET_TOKEN( 'TABLE', 'ra_grouping_rules');
11626: FND_MSG_PUB.ADD;
11627: x_return_status := FND_API.G_RET_STS_ERROR;
11628:
11629: END check_grouping_ruleid_fk;
11630:
11631: PROCEDURE check_positive_value (
11638: IF p_column_value < 0 THEN
11639: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_LESS_THAN_ZERO' );
11640: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11641: FND_MSG_PUB.ADD;
11642: x_return_status := FND_API.G_RET_STS_ERROR;
11643: END IF;
11644:
11645: END check_positive_value;
11646:
11654: IF p_column_value <= 0 THEN
11655: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_GREATER_THAN_ZERO' );
11656: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11657: FND_MSG_PUB.ADD;
11658: x_return_status := FND_API.G_RET_STS_ERROR;
11659: END IF;
11660:
11661: END check_greater_than_zero;
11662:
11670: IF p_column_value > 100 THEN
11671: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_GREATER_THAN_100' );
11672: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
11673: FND_MSG_PUB.ADD;
11674: x_return_status := FND_API.G_RET_STS_ERROR;
11675: END IF;
11676:
11677: END check_less_than_100;
11678:
12023:
12024: IF p_restricted = 'N' THEN
12025: IF ( p_create_update_flag = 'C' AND
12026: ( p_column_value IS NULL OR
12027: p_column_value = FND_API.G_MISS_CHAR ) ) OR
12028: ( p_create_update_flag = 'U' AND
12029: p_column_value = FND_API.G_MISS_CHAR )
12030: THEN
12031: l_error := TRUE;
12025: IF ( p_create_update_flag = 'C' AND
12026: ( p_column_value IS NULL OR
12027: p_column_value = FND_API.G_MISS_CHAR ) ) OR
12028: ( p_create_update_flag = 'U' AND
12029: p_column_value = FND_API.G_MISS_CHAR )
12030: THEN
12031: l_error := TRUE;
12032: END IF;
12033: ELSE
12031: l_error := TRUE;
12032: END IF;
12033: ELSE
12034: IF ( p_column_value IS NULL OR
12035: p_column_value = FND_API.G_MISS_CHAR )
12036: THEN
12037: l_error := TRUE;
12038: END IF;
12039: END IF;
12041: IF l_error THEN
12042: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
12043: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12044: FND_MSG_PUB.ADD;
12045: x_return_status := FND_API.G_RET_STS_ERROR;
12046: END IF;
12047:
12048: END validate_mandatory;
12049:
12061:
12062: IF p_restricted = 'N' THEN
12063: IF ( p_create_update_flag = 'C' AND
12064: ( p_column_value IS NULL OR
12065: p_column_value = FND_API.G_MISS_NUM ) ) OR
12066: ( p_create_update_flag = 'U' AND
12067: p_column_value = FND_API.G_MISS_NUM )
12068: THEN
12069: l_error := TRUE;
12063: IF ( p_create_update_flag = 'C' AND
12064: ( p_column_value IS NULL OR
12065: p_column_value = FND_API.G_MISS_NUM ) ) OR
12066: ( p_create_update_flag = 'U' AND
12067: p_column_value = FND_API.G_MISS_NUM )
12068: THEN
12069: l_error := TRUE;
12070: END IF;
12071: ELSE
12069: l_error := TRUE;
12070: END IF;
12071: ELSE
12072: IF ( p_column_value IS NULL OR
12073: p_column_value = FND_API.G_MISS_NUM )
12074: THEN
12075: l_error := TRUE;
12076: END IF;
12077: END IF;
12079: IF l_error THEN
12080: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
12081: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12082: FND_MSG_PUB.ADD;
12083: x_return_status := FND_API.G_RET_STS_ERROR;
12084: END IF;
12085:
12086: END validate_mandatory;
12087:
12099:
12100: IF p_restricted = 'N' THEN
12101: IF ( p_create_update_flag = 'C' AND
12102: ( p_column_value IS NULL OR
12103: p_column_value = FND_API.G_MISS_DATE ) ) OR
12104: ( p_create_update_flag = 'U' AND
12105: p_column_value = FND_API.G_MISS_DATE )
12106: THEN
12107: l_error := TRUE;
12101: IF ( p_create_update_flag = 'C' AND
12102: ( p_column_value IS NULL OR
12103: p_column_value = FND_API.G_MISS_DATE ) ) OR
12104: ( p_create_update_flag = 'U' AND
12105: p_column_value = FND_API.G_MISS_DATE )
12106: THEN
12107: l_error := TRUE;
12108: END IF;
12109: ELSE
12107: l_error := TRUE;
12108: END IF;
12109: ELSE
12110: IF ( p_column_value IS NULL OR
12111: p_column_value = FND_API.G_MISS_DATE )
12112: THEN
12113: l_error := TRUE;
12114: END IF;
12115: END IF;
12117: IF l_error THEN
12118: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
12119: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12120: FND_MSG_PUB.ADD;
12121: x_return_status := FND_API.G_RET_STS_ERROR;
12122: END IF;
12123:
12124: END validate_mandatory;
12125:
12136: BEGIN
12137:
12138: IF p_column_value IS NOT NULL THEN
12139: IF p_restricted = 'Y' THEN
12140: IF ( p_column_value <> FND_API.G_MISS_CHAR OR
12141: p_old_column_value IS NOT NULL ) AND
12142: ( p_old_column_value IS NULL OR
12143: p_column_value <> p_old_column_value )
12144: THEN
12145: l_error := TRUE;
12146: END IF;
12147: ELSE
12148: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053.
12149: p_old_column_value <> FND_API.G_MISS_CHAR)
12150: AND
12151: ( p_column_value = FND_API.G_MISS_CHAR OR
12152: p_column_value <> p_old_column_value )
12153: THEN
12147: ELSE
12148: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053.
12149: p_old_column_value <> FND_API.G_MISS_CHAR)
12150: AND
12151: ( p_column_value = FND_API.G_MISS_CHAR OR
12152: p_column_value <> p_old_column_value )
12153: THEN
12154: l_error := TRUE;
12155: END IF;
12159: IF l_error THEN
12160: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
12161: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12162: FND_MSG_PUB.ADD;
12163: x_return_status := FND_API.G_RET_STS_ERROR;
12164: END IF;
12165:
12166: END validate_nonupdateable;
12167:
12178: BEGIN
12179:
12180: IF p_column_value IS NOT NULL THEN
12181: IF p_restricted = 'Y' THEN
12182: IF ( p_column_value <> FND_API.G_MISS_NUM OR
12183: p_old_column_value IS NOT NULL ) AND
12184: ( p_old_column_value IS NULL OR
12185: p_column_value <> p_old_column_value )
12186: THEN
12187: l_error := TRUE;
12188: END IF;
12189: ELSE
12190: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053
12191: p_old_column_value <> FND_API.G_MISS_NUM)
12192: AND
12193: ( p_column_value = FND_API.G_MISS_NUM OR
12194: p_column_value <> p_old_column_value )
12195: THEN
12189: ELSE
12190: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053
12191: p_old_column_value <> FND_API.G_MISS_NUM)
12192: AND
12193: ( p_column_value = FND_API.G_MISS_NUM OR
12194: p_column_value <> p_old_column_value )
12195: THEN
12196: l_error := TRUE;
12197: END IF;
12201: IF l_error THEN
12202: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
12203: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12204: FND_MSG_PUB.ADD;
12205: x_return_status := FND_API.G_RET_STS_ERROR;
12206: END IF;
12207:
12208: END validate_nonupdateable;
12209:
12220: BEGIN
12221:
12222: IF p_column_value IS NOT NULL THEN
12223: IF p_restricted = 'Y' THEN
12224: IF ( p_column_value <> FND_API.G_MISS_DATE OR
12225: p_old_column_value IS NOT NULL ) AND
12226: ( p_old_column_value IS NULL OR
12227: p_column_value <> p_old_column_value )
12228: THEN
12229: l_error := TRUE;
12230: END IF;
12231: ELSE
12232: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053
12233: p_old_column_value <> FND_API.G_MISS_DATE)
12234: AND
12235: ( p_column_value = FND_API.G_MISS_DATE OR
12236: p_column_value <> p_old_column_value )
12237: THEN
12231: ELSE
12232: IF (p_old_column_value IS NOT NULL AND -- Bug 3439053
12233: p_old_column_value <> FND_API.G_MISS_DATE)
12234: AND
12235: ( p_column_value = FND_API.G_MISS_DATE OR
12236: p_column_value <> p_old_column_value )
12237: THEN
12238: l_error := TRUE;
12239: END IF;
12243: IF l_error THEN
12244: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
12245: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12246: FND_MSG_PUB.ADD;
12247: x_return_status := FND_API.G_RET_STS_ERROR;
12248: END IF;
12249:
12250: END validate_nonupdateable;
12251:
12270: l_end_date := p_end_date;
12271: ELSIF p_create_update_flag = 'U' THEN
12272: IF p_start_date IS NOT NULL
12273: THEN
12274: IF p_start_date = FND_API.G_MISS_DATE THEN
12275: l_start_date := NULL;
12276: ELSE
12277: l_start_date := p_start_date;
12278: END IF;
12279: END IF;
12280:
12281: IF p_end_date IS NOT NULL
12282: THEN
12283: IF p_end_date = FND_API.G_MISS_DATE THEN
12284: l_end_date := NULL;
12285: ELSE
12286: l_end_date := p_end_date;
12287: END IF;
12288: END IF;
12289: END IF;
12290:
12291: IF l_end_date IS NOT NULL AND
12292: l_end_date <> FND_API.G_MISS_DATE AND
12293: ( l_start_date IS NULL OR
12294: l_start_date = FND_API.G_MISS_DATE OR
12295: l_start_date > l_end_date )
12296: THEN
12290:
12291: IF l_end_date IS NOT NULL AND
12292: l_end_date <> FND_API.G_MISS_DATE AND
12293: ( l_start_date IS NULL OR
12294: l_start_date = FND_API.G_MISS_DATE OR
12295: l_start_date > l_end_date )
12296: THEN
12297: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DATE_GREATER' );
12298: FND_MESSAGE.SET_TOKEN( 'DATE2', p_end_date_column_name );
12297: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DATE_GREATER' );
12298: FND_MESSAGE.SET_TOKEN( 'DATE2', p_end_date_column_name );
12299: FND_MESSAGE.SET_TOKEN( 'DATE1', p_start_date_column_name );
12300: FND_MSG_PUB.ADD;
12301: x_return_status := FND_API.G_RET_STS_ERROR;
12302: END IF;
12303:
12304: END validate_start_end_date;
12305:
12310: ) IS
12311:
12312: BEGIN
12313:
12314: IF p_column_value = FND_API.G_MISS_CHAR THEN
12315: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12316: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12317: FND_MSG_PUB.ADD;
12318: x_return_status := FND_API.G_RET_STS_ERROR;
12314: IF p_column_value = FND_API.G_MISS_CHAR THEN
12315: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12316: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12317: FND_MSG_PUB.ADD;
12318: x_return_status := FND_API.G_RET_STS_ERROR;
12319: END IF;
12320:
12321: END validate_cannot_update_to_null;
12322:
12327: ) IS
12328:
12329: BEGIN
12330:
12331: IF p_column_value = FND_API.G_MISS_NUM THEN
12332: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12333: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12334: FND_MSG_PUB.ADD;
12335: x_return_status := FND_API.G_RET_STS_ERROR;
12331: IF p_column_value = FND_API.G_MISS_NUM THEN
12332: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12333: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12334: FND_MSG_PUB.ADD;
12335: x_return_status := FND_API.G_RET_STS_ERROR;
12336: END IF;
12337:
12338: END validate_cannot_update_to_null;
12339:
12344: ) IS
12345:
12346: BEGIN
12347:
12348: IF p_column_value = FND_API.G_MISS_DATE THEN
12349: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12350: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12351: FND_MSG_PUB.ADD;
12352: x_return_status := FND_API.G_RET_STS_ERROR;
12348: IF p_column_value = FND_API.G_MISS_DATE THEN
12349: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
12350: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12351: FND_MSG_PUB.ADD;
12352: x_return_status := FND_API.G_RET_STS_ERROR;
12353: END IF;
12354:
12355: END validate_cannot_update_to_null;
12356:
12366:
12367: BEGIN
12368:
12369: IF p_column_value IS NOT NULL AND
12370: p_column_value <> FND_API.G_MISS_CHAR THEN
12371:
12372: IF p_lookup_type = 'YES/NO' THEN
12373: IF p_column_value NOT IN ('Y', 'N') THEN
12374: l_error := TRUE;
12386: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
12387: FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
12388: FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', p_lookup_type );
12389: FND_MSG_PUB.ADD;
12390: x_return_status := FND_API.G_RET_STS_ERROR;
12391: END IF;
12392: END IF;
12393:
12394: END validate_lookup;
12464: END IF;
12465:
12466: -- created_by_module is lookup code in lookup type HZ_CREATED_BY_MODULES
12467: IF p_created_by_module IS NOT NULL AND
12468: p_created_by_module <> fnd_api.g_miss_char AND
12469: (p_create_update_flag = 'C' OR
12470: (p_create_update_flag = 'U' AND
12471: (p_old_created_by_module IS NULL OR
12472: p_created_by_module <> p_old_created_by_module)))
12577: if (p_customer_profile_rec.cons_bill_level IS NULL
12578: OR p_customer_profile_rec.cons_inv_type IS NULL) then
12579: FND_MESSAGE.SET_NAME('AR', 'HZ_API_BILL_LVL_TYPE_INVALID');
12580: FND_MSG_PUB.ADD;
12581: x_return_status := FND_API.G_RET_STS_ERROR;
12582: else
12583: if p_customer_profile_rec.cons_bill_level IS NOT NULL then
12584: SELECT COUNT(*)
12585: INTO l_bill_level_count
12591:
12592: IF (l_bill_level_count = 0) THEN
12593: FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_BILL_LEVEL');
12594: FND_MSG_PUB.ADD;
12595: x_return_status := FND_API.G_RET_STS_ERROR;
12596: END IF;
12597: end if;
12598: end if;
12599:
12610:
12611: IF (l_bill_TYPE_count = 0) THEN
12612: FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ACCT_BILL_TYPE');
12613: FND_MSG_PUB.ADD;
12614: x_return_status := FND_API.G_RET_STS_ERROR;
12615: END IF;
12616: -- For Site use bill level, use Summary, Detail, or Imported as bill type.
12617: else
12618: SELECT COUNT(*)
12625:
12626: IF (l_bill_TYPE_count = 0) THEN
12627: FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ACCT_BILL_TYPE');
12628: FND_MSG_PUB.ADD;
12629: x_return_status := FND_API.G_RET_STS_ERROR;
12630: END IF;
12631: end if;
12632: end if;
12633: END validate_bill_level;