[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
p_restricted IN VARCHAR2 DEFAULT 'N',
x_return_status IN OUT NOCOPY VARCHAR2
);
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN NUMBER,
p_restricted IN VARCHAR2 DEFAULT 'N',
x_return_status IN OUT NOCOPY VARCHAR2
);
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN DATE,
p_restricted IN VARCHAR2 DEFAULT 'N',
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE validate_nonupdateable (
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
p_old_column_value IN VARCHAR2,
p_restricted IN VARCHAR2 DEFAULT 'Y',
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE validate_nonupdateable (
p_column IN VARCHAR2,
p_column_value IN NUMBER,
p_old_column_value IN NUMBER,
p_restricted IN VARCHAR2 DEFAULT 'Y',
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE validate_nonupdateable (
p_column IN VARCHAR2,
p_column_value IN DATE,
p_old_column_value IN DATE,
p_restricted IN VARCHAR2 DEFAULT 'Y',
x_return_status IN OUT NOCOPY VARCHAR2
);
p_create_update_flag IN VARCHAR2,
p_start_date_column_name IN VARCHAR2,
p_start_date IN DATE,
p_old_start_date IN DATE,
p_end_date_column_name IN VARCHAR2,
p_end_date IN DATE,
p_old_end_date IN DATE,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE validate_cannot_update_to_null (
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE validate_cannot_update_to_null (
p_column IN VARCHAR2,
p_column_value IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE validate_cannot_update_to_null (
p_column IN VARCHAR2,
p_column_value IN DATE,
x_return_status IN OUT NOCOPY VARCHAR2
);
p_create_update_flag IN VARCHAR2,
p_created_by_module IN VARCHAR2,
p_old_created_by_module IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
);
p_create_update_flag IN VARCHAR2,
p_application_id IN NUMBER,
p_old_application_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
p_create_update_flag IN VARCHAR2,
p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_cust_account_rec Customer account record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
* 11-08-2001 Rajeshwari P Included the validation for fields, tax_code,
* invoice_quantity_rule,customer_class_code,
* tax_rounding_rule,primary_salesrep_id,
* order_type_id,price_list_id,ship_partial,fob_point ,
* item_cross_ref_pref,warehouse_id,date_type_preference,
* ship_sets_include_lines_flag and
* arrivalsets_include_lines_flag in procedure
* VALIDATE_CUST_ACCOUNT.
* 21-05-2002 Rajeshwari P Bug fix 2311760.Commented the code which validates the
* Obsolete column ship_partial.
*
* 05-26-2003 Ramesh Ch Bug No:2441276. Added Validation to make
* account_number as non updateable.
* 12-MAY-2005 Rajib Ranjan Borah o TCA SSA Uptake (Bug 3456489)
* ~ HZ Cache will be used instead of querying from
* AR System Parameters.
* ~ Tax Code, Order Type and Sales Rep are no longer set
* at the Cust Account level.
*/
PROCEDURE validate_cust_account (
p_create_update_flag IN VARCHAR2,
p_cust_account_rec IN HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := ''; -- 'validate_cust_account'
IF p_create_update_flag = 'U' THEN
SELECT ACCOUNT_NUMBER,ORIG_SYSTEM_REFERENCE, ACCOUNT_ESTABLISHED_DATE,
-- ACCOUNT_TERMINATION_DATE, --ACCOUNT_ACTIVATION_DATE,
CREATED_BY_MODULE, APPLICATION_ID,
STATUS, CUSTOMER_TYPE, SALES_CHANNEL_CODE, FREIGHT_TERM,
SHIP_VIA,SHIP_SETS_INCLUDE_LINES_FLAG,ARRIVALSETS_INCLUDE_LINES_FLAG,
TAX_CODE,INVOICE_QUANTITY_RULE,--PRIMARY_SALESREP_ID,
ORDER_TYPE_ID,PRICE_LIST_ID,FOB_POINT,
ITEM_CROSS_REF_PREF,WAREHOUSE_ID,DATE_TYPE_PREFERENCE,
CUSTOMER_CLASS_CODE,TAX_ROUNDING_RULE
INTO l_account_number,l_orig_system_reference, l_account_established_date,
-- l_account_termination_date, --l_account_activation_date,
l_created_by_module, l_application_id,
l_status, l_customer_type, l_sales_channel_code, l_freight_term,
l_ship_via,l_ship_sets_inc_lines_f,l_arrivalsets_inc_lines_f,
l_tax_code,l_invoice_quantity_rule,--l_primary_salesrep_id,
l_order_type_id,l_price_list_id,l_fob_point,
l_item_cross_ref_pref,l_warehouse_id,l_date_type_preference,
l_customer_class_code,l_tax_rounding_rule
FROM HZ_CUST_ACCOUNTS
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
-- If primary key value is passed, check for uniqueness.
-- If primary key value is not passed, it will be generated
-- from sequence by table handler.
IF p_cust_account_rec.cust_account_id IS NOT NULL AND
p_cust_account_rec.cust_account_id <> FND_API.G_MISS_NUM
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNTS
WHERE CUST_ACCOUNT_ID = p_cust_account_rec.cust_account_id;
IF p_create_update_flag = 'C' THEN
-- If autonumbering is on, if user has passed in an account_number,
-- error out. If autonumbering is off, if user has not passed in
-- value, error out;
ELSE -- in update
IF p_cust_account_rec.account_number IS NOT NULL THEN
validate_cannot_update_to_null (
p_column => 'account_number',
p_column_value => p_cust_account_rec.account_number,
x_return_status => x_return_status );
'account_number cannot be updated to null. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix );
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'account_number cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
validate_nonupdateable (
p_column => 'account_number',
p_column_value => p_cust_account_rec.account_number,
p_old_column_value => l_account_number,
x_return_status => x_return_status );
'account_number is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix );
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'account_number is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
SELECT ROWID INTO l_rowid
FROM HZ_CUST_ACCOUNTS
WHERE ACCOUNT_NUMBER = p_cust_account_rec.account_number;
IF p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND l_rowid <> p_rowid )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
IF p_create_update_flag = 'C' AND
p_cust_account_rec.orig_system_reference IS NOT NULL AND
p_cust_account_rec.orig_system_reference <> FND_API.G_MISS_CHAR
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNTS
WHERE ORIG_SYSTEM_REFERENCE = p_cust_account_rec.orig_system_reference;
and p_create_update_flag = 'U'
then
hz_orig_system_ref_pub.get_owner_table_id
(p_orig_system => p_cust_account_rec.orig_system,
p_orig_system_reference => p_cust_account_rec.orig_system_reference,
p_owner_table_name => 'HZ_CUST_ACCOUNTS',
x_owner_table_id => l_mosr_owner_table_id,
x_return_status => l_temp_return_status);
IF p_create_update_flag = 'U' AND
p_cust_account_rec.orig_system_reference IS NOT NULL
and l_validate_flag = 'Y'
THEN
validate_nonupdateable (
p_column => 'orig_system_reference',
p_column_value => l_orig_system_reference1,
p_old_column_value => l_orig_system_reference,
x_return_status => x_return_status );
'orig_system_reference is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix );
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'orig_system_reference is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' AND
p_cust_account_rec.status IS NOT NULL
THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_cust_account_rec.status,
x_return_status => x_return_status );
'status cannot be updated to null. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix );
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'status cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'CODE_STATUS',
p_column_value => p_cust_account_rec.status,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.customer_type <> NVL( l_customer_type, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'customer_type',
p_lookup_type => 'CUSTOMER_TYPE',
p_column_value => p_cust_account_rec.customer_type,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.sales_channel_code <> NVL( l_sales_channel_code, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'sales_channel_code',
p_lookup_table => 'SO_LOOKUPS',
p_lookup_type => 'SALES_CHANNEL',
p_column_value => p_cust_account_rec.sales_channel_code,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.freight_term <> NVL( l_freight_term, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'freight_term',
p_lookup_table => 'SO_LOOKUPS',
p_lookup_type => 'FREIGHT_TERMS',
p_column_value => p_cust_account_rec.freight_term,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.tax_code <> NVL( l_tax_code, FND_API.G_MISS_CHAR ) ) )
THEN
check_tax_code(
p_column => 'tax_code',
p_column_value => p_cust_account_rec.tax_code,
p_called_from => 'validate_cust_account',
x_return_status => x_return_status );
| ( p_create_update_flag = 'C' OR
| ( p_create_update_flag = 'U' AND
| p_cust_account_rec.order_type_id <> NVL( l_order_type_id, FND_API.G_MISS_NUM ) ) )
| THEN
| check_ord_type(
| p_column => 'order_type_id',
| p_column_value => p_cust_account_rec.order_type_id,
| x_return_status => x_return_status );
| AND ( p_create_update_flag = 'C' OR
| ( p_create_update_flag = 'U' AND
| p_cust_account_rec.primary_salesrep_id <> NVL( l_primary_salesrep_id, FND_API.G_MISS_NUM ) ) )
| THEN
| check_prim_salesrep(
| p_column => 'primary_salesrep_id',
| p_column_value => p_cust_account_rec.primary_salesrep_id,
| x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.ship_partial <> NVL( l_ship_partial, FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup (
p_column => 'ship_partial',
p_lookup_type => 'YES/NO',
p_column_value => p_cust_account_rec.ship_partial,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.tax_rounding_rule <> NVL( l_tax_rounding_rule, FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup(
p_column =>'tax_rounding_rule',
p_lookup_type =>'TAX_ROUNDING_RULE',
p_column_value =>p_cust_account_rec.tax_rounding_rule,
x_return_status =>x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.customer_class_code <> NVL( l_customer_class_code, FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup(
p_column =>'customer_class_code',
p_lookup_type =>'CUSTOMER CLASS',
p_column_value =>p_cust_account_rec.customer_class_code,
x_return_status =>x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.invoice_quantity_rule <> NVL( l_invoice_quantity_rule, FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup(
p_column =>'invoice_quantity_rule',
p_lookup_table =>'OE_LOOKUPS',
p_lookup_type =>'INVOICE_BASIS',
p_column_value =>p_cust_account_rec.invoice_quantity_rule,
x_return_status =>x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.price_list_id <> NVL( l_price_list_id, FND_API.G_MISS_NUM) ) )
THEN
check_price_list_fk(
p_column => 'price_list_id',
p_column_value => p_cust_account_rec.price_list_id,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.fob_point <> NVL( l_fob_point, FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup(
p_column =>'fob_point',
p_lookup_type =>'FOB',
p_column_value =>p_cust_account_rec.fob_point,
x_return_status =>x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.item_cross_ref_pref <> NVL( l_item_cross_ref_pref, FND_API.G_MISS_CHAR) ) )
THEN
IF p_cust_account_rec.item_cross_ref_pref NOT IN('INT','CUST')
THEN
check_item_cross_ref(
p_column => 'item_cross_ref_pref',
p_column_value => p_cust_account_rec.item_cross_ref_pref,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.warehouse_id <> NVL( l_warehouse_id, FND_API.G_MISS_NUM) ) )
THEN
check_warehouse(
p_column => 'warehouse_id',
p_column_value => p_cust_account_rec.warehouse_id,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.date_type_preference <> NVL( l_date_type_preference, FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup(
p_column =>'date_type_preference',
p_lookup_table =>'OE_LOOKUPS',
p_lookup_type =>'REQUEST_DATE_TYPE',
p_column_value =>p_cust_account_rec.date_type_preference,
x_return_status =>x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.ship_sets_include_lines_flag <> NVL( l_ship_sets_inc_lines_f, FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup (
p_column => 'ship_sets_include_lines_flag',
p_lookup_type => 'YES/NO',
p_column_value => p_cust_account_rec.ship_sets_include_lines_flag,
x_return_status => x_return_status );
IF (p_create_update_flag = 'C' or
(p_create_update_flag ='U' and
p_cust_account_rec.ship_sets_include_lines_flag <> NVL(l_ship_sets_inc_lines_f,FND_API.G_MISS_CHAR)))
THEN
IF p_cust_account_rec.ship_sets_include_lines_flag = 'Y'
THEN
BEGIN
SELECT decode(p_cust_account_rec.ship_sets_include_lines_flag,p_cust_account_rec.arrivalsets_include_lines_flag,
'N',l_arrivalsets_inc_lines_f,
decode(p_cust_account_rec.arrivalsets_include_lines_flag,l_ship_sets_inc_lines_f, 'Y','N'),'Y')
INTO l_dummy
FROM DUAL;
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.arrivalsets_include_lines_flag <> NVL( l_arrivalsets_inc_lines_f, FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup (
p_column => 'arrivalsets_include_lines_flag',
p_lookup_type => 'YES/NO',
p_column_value => p_cust_account_rec.arrivalsets_include_lines_flag,
x_return_status => x_return_status );
IF (p_create_update_flag ='C' or
(p_create_update_flag ='U' and
p_cust_account_rec.arrivalsets_include_lines_flag <> NVL(l_arrivalsets_inc_lines_f,FND_API.G_MISS_CHAR)))
THEN
IF p_cust_account_rec.arrivalsets_include_lines_flag = 'Y'
THEN
BEGIN
SELECT decode(p_cust_account_rec.arrivalsets_include_lines_flag,p_cust_account_rec.ship_sets_include_lines_flag,
'N',l_ship_sets_inc_lines_f,
decode(p_cust_account_rec.ship_sets_include_lines_flag,l_arrivalsets_inc_lines_f,
'Y','N'),'Y')
INTO l_dummy
FROM DUAL;
p_create_update_flag => p_create_update_flag,
p_start_date_column_name => 'account_established_date',
p_start_date => p_cust_account_rec.account_established_date,
p_old_start_date => l_account_established_date,
p_end_date_column_name => 'account_termination_date',
p_end_date => p_cust_account_rec.account_termination_date,
p_old_end_date => l_account_termination_date,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_start_date_column_name => 'account_established_date',
p_start_date => p_cust_account_rec.account_established_date,
p_old_start_date => l_account_established_date,
p_end_date_column_name => 'account_activation_date',
p_end_date => p_cust_account_rec.account_activation_date,
p_old_end_date => l_account_activation_date,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_start_date_column_name => 'account_activation_date',
p_start_date => p_cust_account_rec.account_activation_date,
p_old_start_date => l_account_activation_date,
p_end_date_column_name => 'account_termination_date',
p_end_date => p_cust_account_rec.account_termination_date,
p_old_end_date => l_account_termination_date,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_rec.ship_via <> NVL( l_ship_via, FND_API.G_MISS_CHAR ) ) )
THEN
check_oe_ship_methods_v_fk (
p_entity => 'ACCOUNT',
p_column => 'ship_via',
p_column_value => p_cust_account_rec.ship_via,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_cust_account_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_cust_account_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_cust_account_rec Customer account relate record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
* 10-04-2003 Rajib Ranjan Borah o Bug 2985448.Only active relationships will be considered
* while checking for duplicates.
* 12-MAY-2005 Rajib Ranjan Borah o TCA SSA Uptake (Bug 3456489)
* 12-AUG-2205 Idris Ali o Bug 4529413:Replaced p_rowid with p_cust_acct_relate_id.
*/
PROCEDURE validate_cust_acct_relate (
p_create_update_flag IN VARCHAR2,
p_cust_acct_relate_rec IN HZ_CUST_ACCOUNT_V2PUB.CUST_ACCT_RELATE_REC_TYPE,
p_cust_acct_relate_id IN NUMBER, -- Bug 4529413
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
IF p_create_update_flag = 'U' THEN
SELECT CUSTOMER_RECIPROCAL_FLAG, CREATED_BY_MODULE, APPLICATION_ID,
RELATIONSHIP_TYPE, STATUS
INTO l_customer_reciprocal_flag, l_created_by_module, l_application_id,
l_relationship_type, l_status
FROM HZ_CUST_ACCT_RELATE_ALL -- Bug 3456489
WHERE CUST_ACCT_RELATE_ID = p_cust_acct_relate_id;
IF (p_create_update_flag <> 'U') OR
(p_cust_acct_relate_rec.cust_acct_relate_id is NULL) -- Bug 4529413
THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'cust_account_id',
p_column_value => p_cust_acct_relate_rec.cust_account_id,
p_restricted => 'Y',
x_return_status => x_return_status );
IF p_create_update_flag = 'C' AND
p_cust_acct_relate_rec.cust_account_id IS NOT NULL AND
p_cust_acct_relate_rec.cust_account_id <> FND_API.G_MISS_NUM
THEN
check_cust_account_fk (
p_column => 'cust_account_id',
p_column_value => p_cust_acct_relate_rec.cust_account_id,
x_return_status => x_return_status );
IF (p_create_update_flag <> 'U') OR
(p_cust_acct_relate_rec.cust_acct_relate_id is NULL) -- Bug 4529413
THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'related_cust_account_id',
p_column_value => p_cust_acct_relate_rec.related_cust_account_id,
p_restricted => 'Y',
x_return_status => x_return_status );
IF p_create_update_flag = 'C' AND
p_cust_acct_relate_rec.related_cust_account_id IS NOT NULL AND
p_cust_acct_relate_rec.related_cust_account_id <> FND_API.G_MISS_NUM
THEN
check_cust_account_fk (
p_column => 'related_cust_account_id',
p_column_value => p_cust_acct_relate_rec.related_cust_account_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCT_RELATE_ALL -- Bug 3456489
WHERE CUST_ACCOUNT_ID = p_cust_acct_relate_rec.cust_account_id
AND RELATED_CUST_ACCOUNT_ID = p_cust_acct_relate_rec.related_cust_account_id
--Bug 2985448
AND STATUS='A'
AND ORG_ID = p_cust_acct_relate_rec.org_id; -- Bug 3456489
IF p_create_update_flag = 'U' AND
p_cust_acct_relate_rec.customer_reciprocal_flag IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'customer_reciprocal_flag',
p_column_value => p_cust_acct_relate_rec.customer_reciprocal_flag,
p_old_column_value => l_customer_reciprocal_flag,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'customer_reciprocal_flag is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_cust_acct_relate_rec.customer_reciprocal_flag IS NOT NULL AND
p_cust_acct_relate_rec.customer_reciprocal_flag <> FND_API.G_MISS_CHAR
THEN
validate_lookup (
p_column => 'customer_reciprocal_flag',
p_lookup_type => 'YES/NO',
p_column_value => p_cust_acct_relate_rec.customer_reciprocal_flag,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_acct_relate_rec.relationship_type <> NVL( l_relationship_type, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'relationship_type',
p_lookup_type => 'RELATIONSHIP_TYPE',
p_column_value => p_cust_acct_relate_rec.relationship_type,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_acct_relate_rec.status IS NOT NULL
THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_cust_acct_relate_rec.status,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'status cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_acct_relate_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'CODE_STATUS',
p_column_value => p_cust_acct_relate_rec.status,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_cust_acct_relate_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_cust_acct_relate_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_customer_profile_rec Customer profile record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
* 11-08-2001 P.Suresh * Bug No : 1999532. Added validations as present in
* customer standard form and interface.
* 12-05-2001 P.Suresh * Bug No : 2125994. Commented the mandatory check
* on charge_on_finance_charge_flag when interest
* charge is 'Y'.
* 16-09-2002 P.Suresh * Bug No : 2441092. Added a condition that the
* discount_terms should be 'Y' when defaulting the
* discount_grace_days from the hz_cust_profile_classes.
* 05-30-2003 Ramesh Ch * Bug No : 2884220. Added a condition that the
* interest_charges should be 'Y' when initializing
* the interest_period_days from the hz_cust_profile_classes.
* 06-23-2003 Ramesh Ch Bug No : 2884220. Added a condition that the dunning_letters,send_statements
* should be 'Y' when initializing the dunning_letter_set_id,statement_cycle_id resp
* from the hz_cust_profile_classes.
*
*/
PROCEDURE validate_customer_profile (
p_create_update_flag IN VARCHAR2,
p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := ''; -- 'validate_customer_profile'
IF p_create_update_flag = 'U'
THEN
SELECT CUST_ACCOUNT_ID, SITE_USE_ID, CREATED_BY_MODULE, APPLICATION_ID,
CREDIT_RATING, RISK_CODE, STATUS,DISCOUNT_TERMS,
DISCOUNT_GRACE_DAYS,DUNNING_LETTERS,DUNNING_LETTER_SET_ID,
SEND_STATEMENTS,STATEMENT_CYCLE_ID,CREDIT_BALANCE_STATEMENTS,
INTEREST_CHARGES,CHARGE_ON_FINANCE_CHARGE_FLAG,
INTEREST_PERIOD_DAYS,ACCOUNT_STATUS,TOLERANCE,PERCENT_COLLECTABLE,
STANDARD_TERMS,OVERRIDE_TERMS,LOCKBOX_MATCHING_OPTION,
AUTOCASH_HIERARCHY_ID,AUTOCASH_HIERARCHY_ID_FOR_ADR,
CLEARING_DAYS,PAYMENT_GRACE_DAYS,GROUPING_RULE_ID,
TAX_PRINTING_OPTION,
REVIEW_CYCLE,LAST_CREDIT_REVIEW_DATE,NEXT_CREDIT_REVIEW_DATE,PARTY_ID,
CREDIT_CLASSIFICATION, LATE_CHARGE_CALCULATION_TRX, CREDIT_ITEMS_FLAG,
DISPUTED_TRANSACTIONS_FLAG, LATE_CHARGE_TYPE, LATE_CHARGE_TERM_ID,
INTEREST_CALCULATION_PERIOD, HOLD_CHARGED_INVOICES_FLAG, MESSAGE_TEXT_ID,
MULTIPLE_INTEREST_RATES_FLAG, CHARGE_BEGIN_DATE
INTO l_cust_account_id, l_site_use_id, l_created_by_module, l_application_id,
l_credit_rating, l_risk_code, l_status,l_discount_terms,
l_discount_grace_days,l_dunning_letters,l_dunning_letter_set_id,
l_send_statements,l_statement_cycle_id,l_credit_balance_statements,
l_interest_charges,l_finance_charge_flag,
l_interest_period_days,l_account_status,l_tolerance,l_percent_collectable,
l_standard_terms,l_override_terms,l_lockbox_matching_option,
l_autocash_hierarchy_id,l_autocash_hierarchy_id_for_ad,
l_clearing_days,l_payment_grace_days,l_grouping_rule_id,
l_tax_printing_option,
l_review_cycle,l_last_credit_review_date,l_next_credit_review_date,l_party_id,
l_credit_classification, l_late_charge_calculation_trx, l_credit_items_flag,
l_disputed_transactions_flag, l_late_charge_type, l_late_charge_term_id,
l_interest_calculation_period, l_hold_charged_invoices_flag, l_message_text_id,
l_multiple_interest_rates_flag, l_charge_begin_date
FROM HZ_CUSTOMER_PROFILES
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
-- If primary key value is passed, check for uniqueness.
-- If primary key value is not passed, it will be generated
-- from sequence by table handler.
IF p_customer_profile_rec.cust_account_profile_id IS NOT NULL AND
p_customer_profile_rec.cust_account_profile_id <> FND_API.G_MISS_NUM
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUSTOMER_PROFILES
WHERE CUST_ACCOUNT_PROFILE_ID = p_customer_profile_rec.cust_account_profile_id;
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'cust_account_id',
p_column_value => p_customer_profile_rec.cust_account_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'C'
THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'party_id',
p_column_value => p_customer_profile_rec.party_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_customer_profile_rec.cust_account_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'cust_account_id',
p_column_value => p_customer_profile_rec.cust_account_id,
p_old_column_value => l_cust_account_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'cust_account_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' AND
p_customer_profile_rec.party_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_customer_profile_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'cust_account_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_customer_profile_rec.cust_account_id IS NOT NULL AND
p_customer_profile_rec.cust_account_id <> FND_API.G_MISS_NUM
THEN
check_cust_account_fk (
p_column => 'cust_account_id',
p_column_value => p_customer_profile_rec.cust_account_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' AND
( p_customer_profile_rec.site_use_id IS NULL OR
p_customer_profile_rec.site_use_id = FND_API.G_MISS_NUM ) AND
p_customer_profile_rec.cust_account_id <> -1
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUSTOMER_PROFILES
WHERE CUST_ACCOUNT_ID = p_customer_profile_rec.cust_account_id
AND SITE_USE_ID IS NULL
AND ROWNUM = 1;
ELSIF p_create_update_flag = 'C' AND
( p_customer_profile_rec.site_use_id IS NULL OR
p_customer_profile_rec.site_use_id = FND_API.G_MISS_NUM ) AND
p_customer_profile_rec.cust_account_id = -1
THEN
-- 2310474 in this case we are creating a customer profile for party
-- One party can only have 1 and only 1 profile directly attachment to the party
-- At party level
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUSTOMER_PROFILES
WHERE PARTY_ID = p_customer_profile_rec.party_id
AND CUST_ACCOUNT_ID = -1;
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.review_cycle <> NVL( l_review_cycle, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'review_cycle',
p_lookup_table => 'AR_LOOKUPS',
p_lookup_type => 'PERIODIC_REVIEW_CYCLE',
p_column_value => p_customer_profile_rec.review_cycle,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.discount_terms <> NVL( l_discount_terms, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'discount_terms',
p_lookup_type => 'YES/NO',
p_column_value => p_customer_profile_rec.discount_terms,
x_return_status => x_return_status );
SELECT STATUS, NAME INTO l_class_status, l_profile_class_name
FROM HZ_CUST_PROFILE_CLASSES
WHERE PROFILE_CLASS_ID = p_customer_profile_rec.profile_class_id;
IF p_create_update_flag = 'U' AND
p_customer_profile_rec.profile_class_id IS NOT NULL
THEN
validate_cannot_update_to_null (
p_column => 'profile_class_id',
p_column_value => p_customer_profile_rec.profile_class_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'profile_class_id cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' AND
p_customer_profile_rec.site_use_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'site_use_id',
p_column_value => p_customer_profile_rec.site_use_id,
p_old_column_value => l_site_use_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'site_use_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_customer_profile_rec.site_use_id IS NOT NULL AND
p_customer_profile_rec.site_use_id <> FND_API.G_MISS_NUM AND
p_customer_profile_rec.cust_account_id <> -1
THEN
BEGIN
SELECT CUST_ACCT_SITE_ID,SITE_USE_CODE,ORG_ID
INTO l_cust_acct_site_id,l_cust_acct_site_use_code,l_org_id
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID = p_customer_profile_rec.site_use_id;
SELECT CUST_ACCOUNT_ID INTO l_cust_account_id
FROM HZ_CUST_ACCT_SITES_ALL
WHERE CUST_ACCT_SITE_ID = l_cust_acct_site_id;
SELECT 'Y' INTO l_dummy
FROM HZ_CUSTOMER_PROFILES
WHERE CUST_ACCOUNT_ID = p_customer_profile_rec.cust_account_id
AND SITE_USE_ID = p_customer_profile_rec.site_use_id
AND ROWNUM = 1;
ELSIF p_create_update_flag = 'C' AND
p_customer_profile_rec.site_use_id IS NOT NULL AND
p_customer_profile_rec.site_use_id <> FND_API.G_MISS_NUM AND
p_customer_profile_rec.cust_account_id = -1
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_SITE_PROF_AT_PTY_LEV' );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.credit_rating <> NVL( l_credit_rating, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'credit_rating',
p_lookup_type => 'CREDIT_RATING',
p_column_value => p_customer_profile_rec.credit_rating,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.risk_code <> NVL( l_risk_code, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'risk_code',
p_lookup_type => 'RISK_CODE',
p_column_value => p_customer_profile_rec.risk_code,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_customer_profile_rec => p_customer_profile_rec,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_customer_profile_rec.status IS NOT NULL
THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_customer_profile_rec.status,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'status cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'CODE_STATUS',
p_column_value => p_customer_profile_rec.status,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.dunning_letters <> NVL( l_dunning_letters, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'dunning_letters',
p_lookup_type => 'YES/NO',
p_column_value => p_customer_profile_rec.dunning_letters,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.interest_charges <> NVL( l_interest_charges, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'interest_charges',
p_lookup_type => 'YES/NO',
p_column_value => p_customer_profile_rec.interest_charges,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.send_statements <> NVL( l_send_statements, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'send_statements',
p_lookup_type => 'YES/NO',
p_column_value => p_customer_profile_rec.send_statements,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_customer_profile_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_customer_profile_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
SELECT * INTO l_profile_class_rec
FROM HZ_CUST_PROFILE_CLASSES
WHERE PROFILE_CLASS_ID = p_customer_profile_rec.profile_class_id;
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.account_status <> NVL( l_account_status, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'account_status',
p_lookup_type => 'ACCOUNT_STATUS',
p_column_value => p_customer_profile_rec.account_status,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.tolerance <> NVL( l_tolerance, FND_API.G_MISS_NUM ) ) )
THEN
IF p_customer_profile_rec.tolerance > 100 OR
p_customer_profile_rec.tolerance < -100
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VALUE_BETWEEN' );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.percent_collectable <> NVL( l_percent_collectable, FND_API.G_MISS_NUM ) ) )
THEN
IF p_customer_profile_rec.percent_collectable > 100 OR
p_customer_profile_rec.percent_collectable < 0
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VALUE_BETWEEN' );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.override_terms <> NVL( l_override_terms, FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup (
p_column => 'override_terms',
p_lookup_type => 'YES/NO',
p_column_value => p_customer_profile_rec.override_terms,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.lockbox_matching_option <> NVL( l_lockbox_matching_option, FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup (
p_column => 'lockbox_matching_option',
p_lookup_type => 'ARLPLB_MATCHING_OPTION',
p_column_value => p_customer_profile_rec.lockbox_matching_option,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.autocash_hierarchy_id <> NVL( l_autocash_hierarchy_id, FND_API.G_MISS_NUM) ) )
THEN
check_auto_hierid_fk (
p_column => 'autocash_hierarchy_id',
p_column_value => p_customer_profile_rec.autocash_hierarchy_id,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.autocash_hierarchy_id_for_adr<> NVL( l_autocash_hierarchy_id_for_ad, FND_API.G_MISS_NUM) ) )
THEN
check_auto_hierid_fk (
p_column => 'autocash_hierarchy_id_for_adr',
p_column_value => p_customer_profile_rec.autocash_hierarchy_id_for_adr,
x_return_status=> x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.statement_cycle_id <> NVL( l_statement_cycle_id, FND_API.G_MISS_NUM) ) )
THEN
check_stat_cycid_fk (
p_column => 'statement_cycle_id',
p_column_value => p_customer_profile_rec.statement_cycle_id,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.clearing_days <> NVL( l_clearing_days, FND_API.G_MISS_NUM) ) )
THEN
-- Error
check_positive_value (
p_column => 'clearing_days',
p_column_value => p_customer_profile_rec.clearing_days,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.payment_grace_days <> NVL( l_payment_grace_days, FND_API.G_MISS_NUM) ) )
THEN
check_positive_value (
p_column => 'payment_grace_days',
p_column_value => p_customer_profile_rec.payment_grace_days,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.tax_printing_option <> NVL( l_tax_printing_option, FND_API.G_MISS_CHAR ) ))
THEN
validate_lookup (
p_column => 'tax_printing_option',
p_lookup_type => 'TAX_PRINTING_OPTION',
p_column_value => p_customer_profile_rec.tax_printing_option,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.standard_terms <> NVL( l_standard_terms, FND_API.G_MISS_NUM ) ))
THEN
l_ckeck_acc_bfb_enabled := 'N';
and p_create_update_flag = 'U' )then
l_ckeck_acc_bfb_enabled := 'Y';
select cons_inv_flag
into ll_cons_inv_flag
from hz_customer_profiles
where cust_account_id = p_customer_profile_rec.cust_account_id
and site_use_id is NULL;
select cons_inv_flag
into ll_cons_inv_flag
from hz_customer_profiles
where cust_account_id = p_customer_profile_rec.cust_account_id
and site_use_id is NULL;
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.grouping_rule_id <> NVL( l_grouping_rule_id, FND_API.G_MISS_NUM ) ))
THEN
check_grouping_ruleid_fk (
p_column => 'grouping_rule_id',
p_column_value => p_customer_profile_rec.grouping_rule_id,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.discount_grace_days <> NVL( l_discount_grace_days, FND_API.G_MISS_NUM ) ))
THEN
check_positive_value (
p_column => 'discount_grace_days',
p_column_value => v_customer_profile_rec.discount_grace_days,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
v_customer_profile_rec.profile_class_id IS NOT NULL AND
v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM)
THEN
IF v_customer_profile_rec.discount_terms IS NULL THEN
v_customer_profile_rec.discount_terms :=
l_profile_class_rec.discount_terms;
IF p_create_update_flag = 'U' AND
v_customer_profile_rec.profile_class_id IS NULL THEN
IF v_customer_profile_rec.discount_terms IS NULL
THEN
v_customer_profile_rec.discount_terms := l_discount_terms;
IF p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
v_customer_profile_rec.profile_class_id IS NOT NULL AND
v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM)
THEN
IF v_customer_profile_rec.dunning_letters IS NULL THEN
v_customer_profile_rec.dunning_letters :=
l_profile_class_rec.dunning_letters;
IF p_create_update_flag = 'U' AND
v_customer_profile_rec.profile_class_id IS NULL
THEN
IF v_customer_profile_rec.dunning_letters IS NULL THEN
v_customer_profile_rec.dunning_letters := l_dunning_letters;
IF p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
v_customer_profile_rec.profile_class_id is not null AND
v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM )
THEN
IF v_customer_profile_rec.send_statements IS NULL THEN
v_customer_profile_rec.send_statements :=
l_profile_class_rec.statements;
IF p_create_update_flag = 'U' AND
v_customer_profile_rec.profile_class_id is null
THEN
IF v_customer_profile_rec.send_statements IS NULL THEN
v_customer_profile_rec.send_statements := l_send_statements;
IF p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
v_customer_profile_rec.profile_class_id is not null AND
v_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM )
THEN
IF v_customer_profile_rec.interest_charges IS NULL THEN
v_customer_profile_rec.interest_charges :=
l_profile_class_rec.interest_charges;
IF p_create_update_flag = 'U' AND
v_customer_profile_rec.profile_class_id is null
THEN
IF v_customer_profile_rec.interest_charges IS NULL THEN
v_customer_profile_rec.interest_charges := l_interest_charges;
p_create_update_flag => p_create_update_flag,
p_column => 'charge_on_finance_charge_flag',
p_column_value => v_customer_profile_rec.charge_on_finance_charge_flag,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.credit_classification <> NVL( l_credit_classification, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'credit_classification',
p_lookup_table => 'AR_LOOKUPS',
p_lookup_type => 'AR_CMGT_CREDIT_CLASSIFICATION',
p_column_value => p_customer_profile_rec.credit_classification,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.late_charge_calculation_trx <> NVL( l_late_charge_calculation_trx, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'late_charge_calculation_trx',
p_lookup_type => 'AR_MANDATORY_LATE_CHARGES',
p_column_value => p_customer_profile_rec.late_charge_calculation_trx,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.credit_items_flag <> NVL( l_credit_items_flag, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'credit_items_flag',
p_lookup_type => 'YES/NO',
p_column_value => p_customer_profile_rec.credit_items_flag,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.disputed_transactions_flag <> NVL( l_disputed_transactions_flag, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'disputed_transactions_flag',
p_lookup_type => 'YES/NO',
p_column_value => p_customer_profile_rec.disputed_transactions_flag,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.late_charge_type <> NVL( l_late_charge_type, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'late_charge_type',
p_lookup_type => 'AR_LATE_CHARGE_TYPE',
p_column_value => p_customer_profile_rec.late_charge_type,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.interest_calculation_period <> NVL( l_interest_calculation_period, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'interest_calculation_period',
p_lookup_type => 'AR_CALCULATION_PERIOD',
p_column_value => p_customer_profile_rec.interest_calculation_period,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.hold_charged_invoices_flag <> NVL( l_hold_charged_invoices_flag, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'hold_charged_invoices_flag',
p_lookup_type => 'YES/NO',
p_column_value => p_customer_profile_rec.hold_charged_invoices_flag,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.multiple_interest_rates_flag <> NVL( l_multiple_interest_rates_flag, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'multiple_interest_rates_flag',
p_lookup_type => 'YES/NO',
p_column_value => p_customer_profile_rec.multiple_interest_rates_flag,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.late_charge_term_id <> NVL( l_late_charge_term_id, FND_API.G_MISS_NUM ) ) )
THEN
check_late_charge_term_id_fk(
p_column => 'payment_term_id',
p_column_value => p_customer_profile_rec.late_charge_term_id,
p_cons_inv_flag => p_customer_profile_rec.cons_inv_flag,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_customer_profile_rec.message_text_id <> NVL( l_message_text_id, FND_API.G_MISS_NUM ) ) )
THEN
check_message_text_id_fk(
p_column => 'message_text_id',
p_column_value => p_customer_profile_rec.message_text_id,
x_return_status => x_return_status );
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_check_foreign_key If do foreign key checking on cust_account_id
* and cust_account_profile_id or not.
* p_cust_profile_amt_rec Customer profile amount record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
* 11-08-2001 P.Suresh * Bug Fix : 2001573. Validated overall_credit_limit.
* It should be greater or equal to trx_credit_limit.
*
*/
PROCEDURE validate_cust_profile_amt (
p_create_update_flag IN VARCHAR2,
p_check_foreign_key IN VARCHAR2,
p_cust_profile_amt_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := ''; -- 'validate_cust_profile_amt'
IF p_create_update_flag = 'U' THEN
SELECT CUST_ACCOUNT_ID, SITE_USE_ID,
CUST_ACCOUNT_PROFILE_ID, CURRENCY_CODE,
CREATED_BY_MODULE, APPLICATION_ID,
OVERALL_CREDIT_LIMIT,TRX_CREDIT_LIMIT,
MIN_FC_INVOICE_OVERDUE_TYPE, MIN_FC_BALANCE_OVERDUE_TYPE,
INTEREST_TYPE, PENALTY_TYPE, EXCHANGE_RATE_TYPE,
MIN_FC_INVOICE_AMOUNT, MIN_FC_INVOICE_PERCENT,
MIN_FC_BALANCE_AMOUNT, MIN_FC_BALANCE_PERCENT,
INTEREST_RATE, INTEREST_FIXED_AMOUNT, INTEREST_SCHEDULE_ID,
PENALTY_RATE, PENALTY_FIXED_AMOUNT, PENALTY_SCHEDULE_ID
INTO l_cust_account_id, l_site_use_id,
l_cust_account_profile_id, l_currency_code,
l_created_by_module, l_application_id,
l_overall_credit_limit,l_trx_credit_limit,
l_min_fc_invoice_overdue_type, l_min_fc_balance_overdue_type,
l_interest_type, l_penalty_type, l_exchange_rate_type,
l_min_fc_invoice_amount, l_min_fc_invoice_percent,
l_min_fc_balance_amount, l_min_fc_balance_percent,
l_interest_rate, l_interest_fixed_amount, l_interest_schedule_id,
l_penalty_rate, l_penalty_fixed_amount, l_penalty_schedule_id
FROM HZ_CUST_PROFILE_AMTS
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
-- If primary key value is passed, check for uniqueness.
-- If primary key value is not passed, it will be generated
-- from sequence by table handler.
IF p_cust_profile_amt_rec.cust_acct_profile_amt_id IS NOT NULL AND
p_cust_profile_amt_rec.cust_acct_profile_amt_id <> FND_API.G_MISS_NUM
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_PROFILE_AMTS
WHERE CUST_ACCT_PROFILE_AMT_ID = p_cust_profile_amt_rec.cust_acct_profile_amt_id;
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'cust_account_profile_id',
p_column_value => p_cust_profile_amt_rec.cust_account_profile_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_profile_amt_rec.cust_account_profile_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'cust_account_profile_id',
p_column_value => p_cust_profile_amt_rec.cust_account_profile_id,
p_old_column_value => l_cust_account_profile_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'cust_account_profile_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_check_foreign_key = FND_API.G_TRUE AND
p_cust_profile_amt_rec.cust_account_profile_id IS NOT NULL AND
p_cust_profile_amt_rec.cust_account_profile_id <> FND_API.G_MISS_NUM
THEN
BEGIN
-- select cust_account_id, site_use_id for later cross reference checking
-- Bug 4770111 : select created_by_module also
SELECT CUST_ACCOUNT_ID, SITE_USE_ID, CREATED_BY_MODULE
INTO l_cust_account_id, l_site_use_id, l_prof_cbm
FROM HZ_CUSTOMER_PROFILES
WHERE CUST_ACCOUNT_PROFILE_ID = p_cust_profile_amt_rec.cust_account_profile_id;
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'cust_account_id',
p_column_value => p_cust_profile_amt_rec.cust_account_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_profile_amt_rec.cust_account_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'cust_account_id',
p_column_value => p_cust_profile_amt_rec.cust_account_id,
p_old_column_value => l_cust_account_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'cust_account_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_check_foreign_key = FND_API.G_TRUE AND
p_cust_profile_amt_rec.cust_account_id IS NOT NULL AND
p_cust_profile_amt_rec.cust_account_id <> FND_API.G_MISS_NUM
THEN
check_cust_account_fk (
p_column => 'cust_account_id',
p_column_value => p_cust_profile_amt_rec.cust_account_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'currency_code',
p_column_value => p_cust_profile_amt_rec.currency_code,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_profile_amt_rec.currency_code IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'currency_code',
p_column_value => p_cust_profile_amt_rec.currency_code,
p_old_column_value => l_currency_code,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'currency_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_cust_profile_amt_rec.currency_code IS NOT NULL AND
p_cust_profile_amt_rec.currency_code <> FND_API.G_MISS_CHAR
THEN
check_currency_fk (
p_column => 'currency_code',
p_column_value => p_cust_profile_amt_rec.currency_code,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_PROFILE_AMTS
WHERE CUST_ACCOUNT_PROFILE_ID = p_cust_profile_amt_rec.cust_account_profile_id
AND CURRENCY_CODE = p_cust_profile_amt_rec.currency_code
AND ROWNUM = 1;
IF p_create_update_flag = 'U' AND
p_cust_profile_amt_rec.site_use_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'site_use_id',
p_column_value => p_cust_profile_amt_rec.site_use_id,
p_old_column_value => l_site_use_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'site_use_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_check_foreign_key = FND_API.G_TRUE
THEN
IF p_cust_profile_amt_rec.site_use_id IS NOT NULL AND
p_cust_profile_amt_rec.site_use_id <> FND_API.G_MISS_NUM
THEN
check_cust_site_use_fk (
p_column => 'site_use_id',
p_column_value => p_cust_profile_amt_rec.site_use_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' THEN
IF p_cust_profile_amt_rec.created_by_module <> nvl(l_prof_cbm, 'TCA_V2_API') THEN
validate_created_by_module(
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_cust_profile_amt_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_cust_profile_amt_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_cust_profile_amt_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
IF ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
(
(p_cust_profile_amt_rec.overall_credit_limit <> NVL( l_overall_credit_limit, FND_API.G_MISS_NUM ))
OR
(p_cust_profile_amt_rec.trx_credit_limit <> NVL(l_trx_credit_limit,FND_API.G_MISS_NUM))
)
)
)
THEN
IF p_cust_profile_amt_rec.overall_credit_limit < p_cust_profile_amt_rec.trx_credit_limit
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_VAL_OVERALL_CREDIT' );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_profile_amt_rec.min_fc_invoice_overdue_type <> NVL( l_min_fc_invoice_overdue_type, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'min_fc_invoice_overdue_type',
p_lookup_table => 'AR_LOOKUPS',
p_lookup_type => 'AR_AMOUNT_PERCENT',
p_column_value => p_cust_profile_amt_rec.min_fc_invoice_overdue_type,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_profile_amt_rec.min_fc_balance_overdue_type <> NVL( l_min_fc_balance_overdue_type, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'min_fc_balance_overdue_type',
p_lookup_table => 'AR_LOOKUPS',
p_lookup_type => 'AR_AMOUNT_PERCENT',
p_column_value => p_cust_profile_amt_rec.min_fc_balance_overdue_type,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_profile_amt_rec.interest_type <> NVL( l_interest_type, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'interest_type',
p_lookup_table => 'AR_LOOKUPS',
p_lookup_type => 'AR_INTEREST_PENALTY_TYPE',
p_column_value => p_cust_profile_amt_rec.interest_type,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_profile_amt_rec.penalty_type <> NVL( l_penalty_type, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'penalty_type',
p_lookup_table => 'AR_LOOKUPS',
p_lookup_type => 'AR_INTEREST_PENALTY_TYPE',
p_column_value => p_cust_profile_amt_rec.penalty_type,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_profile_amt_rec.exchange_rate_type <> NVL( l_exchange_rate_type, FND_API.G_MISS_CHAR ) ) )
THEN
check_exchange_rate_type_fk(
p_column => 'exchange_rate_type',
p_column_value => p_cust_profile_amt_rec.exchange_rate_type,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' THEN
IF p_cust_profile_amt_rec.min_fc_invoice_overdue_type is NULL THEN
v_cust_profile_amt_rec.min_fc_invoice_overdue_type := l_min_fc_invoice_overdue_type;
IF p_create_update_flag = 'C' THEN
IF p_cust_profile_amt_rec.min_fc_invoice_overdue_type = FND_API.G_MISS_CHAR THEN
v_cust_profile_amt_rec.min_fc_invoice_overdue_type := NULL;
IF p_create_update_flag = 'U' THEN
IF p_cust_profile_amt_rec.min_fc_balance_overdue_type is NULL THEN
v_cust_profile_amt_rec.min_fc_balance_overdue_type := l_min_fc_balance_overdue_type;
IF p_create_update_flag = 'C' THEN
IF p_cust_profile_amt_rec.min_fc_balance_overdue_type = FND_API.G_MISS_CHAR THEN
v_cust_profile_amt_rec.min_fc_balance_overdue_type := NULL;
IF p_create_update_flag = 'U' THEN
IF p_cust_profile_amt_rec.interest_type is NULL THEN
v_cust_profile_amt_rec.interest_type := l_interest_type;
IF p_create_update_flag = 'C' THEN
IF p_cust_profile_amt_rec.interest_type = FND_API.G_MISS_CHAR THEN
v_cust_profile_amt_rec.interest_type := NULL;
IF p_create_update_flag = 'U' THEN
IF p_cust_profile_amt_rec.penalty_type is NULL THEN
v_cust_profile_amt_rec.penalty_type := l_penalty_type;
IF p_create_update_flag = 'C' THEN
IF p_cust_profile_amt_rec.penalty_type = FND_API.G_MISS_CHAR THEN
v_cust_profile_amt_rec.penalty_type := NULL;
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_cust_acct_site_rec Customer account site record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
* 10-25-2002 P.Suresh o Bug No : 2528119. Added validation for ece_tp_location_code
* in validate_cust_acct_site procedure.
* 12-MAY-2005 Rajib Ranjan Borah o TCA SSA Uptake (Bug 3456489)
*/
PROCEDURE validate_cust_acct_site (
p_create_update_flag IN VARCHAR2,
p_cust_acct_site_rec IN HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := ''; -- 'validate_cust_acct_site'
IF p_create_update_flag = 'U' THEN
SELECT CUST_ACCOUNT_ID, PARTY_SITE_ID, ORIG_SYSTEM_REFERENCE,
CREATED_BY_MODULE, APPLICATION_ID, STATUS, CUSTOMER_CATEGORY_CODE
INTO l_cust_account_id, l_party_site_id, l_orig_system_reference,
l_created_by_module, l_application_id, l_status, l_customer_category_code
FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
-- If primary key value is passed, check for uniqueness.
-- If primary key value is not passed, it will be generated
-- from sequence by table handler.
IF p_cust_acct_site_rec.cust_acct_site_id IS NOT NULL AND
p_cust_acct_site_rec.cust_acct_site_id <> FND_API.G_MISS_NUM
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_rec.cust_acct_site_id;
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'cust_account_id',
p_column_value => p_cust_acct_site_rec.cust_account_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_acct_site_rec.cust_account_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'cust_account_id',
p_column_value => p_cust_acct_site_rec.cust_account_id,
p_old_column_value => l_cust_account_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'cust_account_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
IF p_cust_acct_site_rec.cust_account_id <> -1 AND
p_cust_acct_site_rec.cust_account_id IS NOT NULL AND
p_cust_acct_site_rec.cust_account_id <> FND_API.G_MISS_NUM
THEN
check_cust_account_fk (
p_column => 'cust_account_id',
p_column_value => p_cust_acct_site_rec.cust_account_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'party_site_id',
p_column_value => p_cust_acct_site_rec.party_site_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_acct_site_rec.party_site_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'party_site_id',
p_column_value => p_cust_acct_site_rec.party_site_id,
p_old_column_value => l_party_site_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'party_site_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_cust_acct_site_rec.party_site_id IS NOT NULL AND
p_cust_acct_site_rec.party_site_id <> FND_API.G_MISS_NUM
THEN
check_party_site_fk (
p_column => 'party_site_id',
p_column_value => p_cust_acct_site_rec.party_site_id,
x_return_status => x_return_status );
SELECT 'Y' INTO l_dummy
FROM HZ_LOCATIONS
WHERE LOCATION_ID = (
SELECT LOCATION_ID
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = p_cust_acct_site_rec.party_site_id )
AND CONTENT_SOURCE_TYPE = HZ_PARTY_V2PUB.G_MISS_CONTENT_SOURCE_TYPE;
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
WHERE CUST_ACCOUNT_ID = p_cust_acct_site_rec.cust_account_id
AND PARTY_SITE_ID = p_cust_acct_site_rec.party_site_id
AND ORG_ID = p_cust_acct_site_rec.org_id -- Bug 3456489
AND ROWNUM = 1;
IF p_create_update_flag = 'C' 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
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCT_SITES_ALL
WHERE ORIG_SYSTEM_REFERENCE = p_cust_acct_site_rec.orig_system_reference
AND ORG_ID = p_cust_acct_site_rec.org_id;
and p_create_update_flag = 'U'
then
hz_orig_system_ref_pub.get_owner_table_id
(p_orig_system => p_cust_acct_site_rec.orig_system,
p_orig_system_reference => p_cust_acct_site_rec.orig_system_reference,
p_owner_table_name => 'HZ_CUST_ACCT_SITES_ALL',
x_owner_table_id => l_mosr_owner_table_id,
x_return_status => l_temp_return_status);
IF p_create_update_flag = 'U' AND
p_cust_acct_site_rec.orig_system_reference IS NOT NULL
and l_validate_flag = 'Y'
THEN
validate_nonupdateable (
p_column => 'orig_system_reference',
p_column_value => p_cust_acct_site_rec.orig_system_reference,
p_old_column_value => l_orig_system_reference,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'orig_system_reference is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
SELECT ROWID INTO l_rowid
FROM HZ_CUST_ACCT_SITES_ALL
WHERE TP_HEADER_ID = p_cust_acct_site_rec.tp_header_id
AND ORG_ID = p_cust_acct_site_rec.org_id; -- Bug 3456489
IF p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND l_rowid <> p_rowid )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
IF p_create_update_flag = 'U' AND
p_cust_acct_site_rec.status IS NOT NULL
THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_cust_acct_site_rec.status,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'status cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_acct_site_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'CODE_STATUS',
p_column_value => p_cust_acct_site_rec.status,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_acct_site_rec.customer_category_code <> NVL( l_customer_category_code, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'customer_category_code',
p_lookup_type => 'ADDRESS_CATEGORY',
p_column_value => p_cust_acct_site_rec.customer_category_code,
x_return_status => x_return_status );
SELECT 'Y' INTO l_dummy
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE = p_cust_acct_site_rec.language
AND INSTALLED_FLAG IN ('B', 'I')
AND ROWNUM = 1;
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_cust_acct_site_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_cust_acct_site_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
IF p_cust_acct_site_rec.cust_account_id <> -1 AND
p_cust_acct_site_rec.cust_account_id IS NOT NULL AND
p_cust_acct_site_rec.cust_account_id <> FND_API.G_MISS_NUM
THEN
IF p_cust_acct_site_rec.ece_tp_location_code IS NOT NULL AND
p_cust_acct_site_rec.ece_tp_location_code <> FND_API.G_MISS_CHAR
THEN
select count(1)
into l_count
from hz_cust_acct_sites_all addr -- Bug 3456489
where addr.cust_account_id = p_cust_acct_site_rec.cust_account_id
and addr.ece_tp_location_code = p_cust_acct_site_rec.ece_tp_location_code
and org_id = p_cust_acct_site_rec.org_id; -- Bug 3456489
ELSIF p_create_update_flag = 'U' THEN
IF p_cust_acct_site_rec.ece_tp_location_code IS NOT NULL AND
p_cust_acct_site_rec.ece_tp_location_code <> FND_API.G_MISS_CHAR
THEN
select count(1)
into l_count
from hz_cust_acct_sites_all addr -- Bug 3456489
where addr.cust_account_id = l_cust_account_id
and addr.ece_tp_location_code = p_cust_acct_site_rec.ece_tp_location_code
and addr.cust_acct_site_id <> p_cust_acct_site_rec.cust_acct_site_id
and org_id = p_cust_acct_site_rec.org_id; -- Bug 3456489
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_cust_site_use_rec Customer account site use record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
* 11-08-2001 Rajeshwari P Added validations for fields, tax_code,payment_term_id,
* demand_class_codeprimary_salesrep_id,finchrg_receivables_trx_id,
* order_type_id,order_type_id,ship_partial,item_cross_ref_pref,
* warehouse_id,date_type_preference,
* ship_sets_include_lines_flag and arrivalsets_include_lines_flag
* in procedure VALIDATE_CUST_SITE_USE.
* 03-26-2002 P.Suresh Bug No : 2266165. Added the check that bill_to_flag of
* hz_cust_acct_relate_all table should be Y when validating
* bill_to_site_use_id.
* 04-11-2002 P.Suresh Bug No : 2260832. Added the check for the status
* when validating that only one primary is allowed for
* one site use type per account in the procedure
* validate_cust_site_use.
* 21-05-2002 Rajeshwari P Bug fix 2311760.Commented the code which validates the
* Obsolete column ship_partial.
* 06-13-2002 P.Suresh Bug No : 2403263. Added validation that the contact_id
* should be a foreign key to hz_cust_account_roles.
* cust_account_role_id.
* 06-19-2002 Rajeshwari P Bug 2399491.Validating site_use_id for duplicates
* against hz_cust_site_uses_all table instead of the
* org stripped view, hz_cust_site_uses.
* 03-May-3004 Venkata Sowjanya S Bug No : 3609601. Commented the statements which sets tokens Column1,Column2
* for message HZ_API_INACTIVE_CANNOT_PRIM
* 12-MAY-2005 Rajib Ranjan Borah o TCA SSA Uptake (Bug 3456489)
*/
PROCEDURE validate_cust_site_use (
p_create_update_flag IN VARCHAR2,
p_cust_site_use_rec IN HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := ''; -- 'validate_cust_site_use'
IF p_create_update_flag = 'U' THEN
SELECT CUST_ACCT_SITE_ID, SITE_USE_CODE, ORIG_SYSTEM_REFERENCE,
CREATED_BY_MODULE, APPLICATION_ID, STATUS, SHIP_VIA, FREIGHT_TERM,
SHIP_SETS_INCLUDE_LINES_FLAG,ARRIVALSETS_INCLUDE_LINES_FLAG,
TAX_CODE,DEMAND_CLASS_CODE,PRIMARY_SALESREP_ID,FINCHRG_RECEIVABLES_TRX_ID,
ORDER_TYPE_ID,PRICE_LIST_ID,FOB_POINT,ITEM_CROSS_REF_PREF,
WAREHOUSE_ID,DATE_TYPE_PREFERENCE
INTO l_cust_acct_site_id, l_site_use_code, l_orig_system_reference,
l_created_by_module, l_application_id, l_status, l_ship_via, l_freight_term,
l_ship_sets_inc_lines_f,l_arrivalsets_inc_lines_f,l_tax_code,l_demand_class_code,
l_primary_salesrep_id,l_finchrg_receivables_trx_id,l_order_type_id,l_price_list_id,
l_fob_point,l_item_cross_ref_pref,l_warehouse_id,l_date_type_preference
FROM HZ_CUST_SITE_USES_ALL -- Bug 3456489
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
-- If primary key value is passed, check for uniqueness.
-- If primary key value is not passed, it will be generated
-- from sequence by table handler.
--Bug Fix 2399491, Checking for duplicates in the _all table.
IF p_cust_site_use_rec.site_use_id IS NOT NULL AND
p_cust_site_use_rec.site_use_id <> FND_API.G_MISS_NUM
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID = p_cust_site_use_rec.site_use_id;
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'cust_acct_site_id',
p_column_value => p_cust_site_use_rec.cust_acct_site_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_site_use_rec.cust_acct_site_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'cust_acct_site_id',
p_column_value => p_cust_site_use_rec.cust_acct_site_id,
p_old_column_value => l_cust_acct_site_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'cust_acct_site_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_cust_site_use_rec.cust_acct_site_id IS NOT NULL AND
p_cust_site_use_rec.cust_acct_site_id <> FND_API.G_MISS_NUM
THEN
check_cust_acct_site_fk (
p_column => 'cust_acct_site_id',
p_column_value => p_cust_site_use_rec.cust_acct_site_id,
p_org_id => p_cust_site_use_rec.org_id, -- Bug 3456489
x_return_status => x_return_status );
SELECT CUST_ACCOUNT_ID INTO l_cust_account_id
FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
WHERE CUST_ACCT_SITE_ID = l_cust_acct_site_id;
IF p_create_update_flag = 'U' AND
p_cust_site_use_rec.status IS NOT NULL
THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_cust_site_use_rec.status,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'status cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'CODE_STATUS',
p_column_value => p_cust_site_use_rec.status,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'site_use_code',
p_column_value => p_cust_site_use_rec.site_use_code,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_site_use_rec.site_use_code IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'site_use_code',
p_column_value => p_cust_site_use_rec.site_use_code,
p_old_column_value => l_site_use_code,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'site_use_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_cust_site_use_rec.site_use_code IS NOT NULL AND
p_cust_site_use_rec.site_use_code <> FND_API.G_MISS_CHAR
THEN
validate_lookup (
p_column => 'site_use_code',
p_lookup_type => 'SITE_USE_CODE',
p_column_value => p_cust_site_use_rec.site_use_code,
x_return_status => x_return_status );
( p_create_update_flag = 'C' AND
( p_cust_site_use_rec.status IS NULL OR
p_cust_site_use_rec.status = FND_API.G_MISS_CHAR ) ) OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.status IS NULL AND
l_status = 'A' )
THEN
-- A customer can have only one active DUN, STMTS, LEGAL site use
IF l_site_use_code IN ( 'DUN', 'STMTS', 'LEGAL' ) THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCT_SITES_ALL site,
HZ_CUST_SITE_USES_ALL su
WHERE site.CUST_ACCOUNT_ID = l_cust_account_id
AND site.CUST_ACCT_SITE_ID = su.CUST_ACCT_SITE_ID
AND su.SITE_USE_CODE = l_site_use_code
AND su.STATUS = 'A'
AND ( p_create_update_flag = 'C' OR
p_create_update_flag = 'U' AND su.ROWID <> p_rowid )
AND site.org_id = p_cust_site_use_rec.org_id -- Bug 3456489
AND su.org_id = p_cust_site_use_rec.org_id -- Bug 3456489
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_SITE_USES_ALL -- Bug 3456489
WHERE CUST_ACCT_SITE_ID = nvl(p_cust_site_use_rec.cust_acct_site_id,l_cust_acct_site_id)
AND SITE_USE_CODE = nvl(p_cust_site_use_rec.site_use_code,l_site_use_code)
AND SITE_USE_ID <> nvl(p_cust_site_use_rec.site_use_id,fnd_api.g_miss_num)
AND STATUS = 'A'
AND ORG_ID = p_cust_site_use_rec.org_id -- Bug 3456489
AND ROWNUM = 1;
IF (p_create_update_flag = 'C') THEN
l_profile := HZ_MO_GLOBAL_CACHE.get_auto_site_numbering(
p_cust_site_use_rec.org_id);
IF p_create_update_flag = 'C' THEN
IF p_cust_site_use_rec.location IS NULL OR
p_cust_site_use_rec.location = FND_API.G_MISS_CHAR
THEN
IF l_profile = 'N' THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
ELSIF p_create_update_flag = 'U' AND
p_cust_site_use_rec.location IS NOT NULL
THEN
-- location cannot be set to null during update
validate_cannot_update_to_null (
p_column => 'location',
p_column_value => p_cust_site_use_rec.location,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'location cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCT_SITES_ALL site,
HZ_CUST_SITE_USES_ALL su
WHERE site.CUST_ACCOUNT_ID = l_cust_account_id
AND site.CUST_ACCT_SITE_ID = su.CUST_ACCT_SITE_ID
AND su.SITE_USE_CODE = l_site_use_code
AND su.LOCATION = p_cust_site_use_rec.location
AND ( p_create_update_flag = 'C' OR
p_create_update_flag = 'U' AND su.ROWID <> p_rowid )
AND su.STATUS = 'A'
AND site.org_id = p_cust_site_use_rec.org_id -- Bug 3456489
AND su.org_id = p_cust_site_use_rec.org_id -- Bug 3456489
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCT_SITES_ALL site,
HZ_CUST_SITE_USES_ALL su
WHERE su.SITE_USE_ID = p_cust_site_use_rec.bill_to_site_use_id
AND su.SITE_USE_CODE = 'BILL_TO'
AND su.STATUS = 'A'
AND site.CUST_ACCT_SITE_ID = su.CUST_ACCT_SITE_ID
AND site.STATUS = 'A'
AND site.org_id = p_cust_site_use_rec.org_id -- Bug 3456489
AND su.org_id = p_cust_site_use_rec.org_id -- Bug 3456489
AND site.CUST_ACCOUNT_ID IN (
SELECT l_cust_account_id
FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID
FROM HZ_CUST_ACCT_RELATE_ALL
WHERE RELATED_CUST_ACCOUNT_ID = l_cust_account_id
AND BILL_TO_FLAG = 'Y' );
IF p_create_update_flag = 'U'
and p_cust_site_use_rec.status = 'I'
and l_status = 'A'
and nvl(p_cust_site_use_rec.site_use_code,l_site_use_code)='BILL_TO'
THEN
BEGIN
SELECT 'Y'
INTO l_exist_flag
FROM hz_cust_site_uses_all hsu
WHERE hsu.site_use_code = 'SHIP_TO'
AND hsu.status = 'A'
AND hsu.bill_to_site_use_id = p_cust_site_use_rec.site_use_id
AND rownum = 1;
and p_create_update_flag = 'U'
THEN
hz_orig_system_ref_pub.get_owner_table_id
(p_orig_system => p_cust_site_use_rec.orig_system,
p_orig_system_reference => p_cust_site_use_rec.orig_system_reference,
p_owner_table_name =>'HZ_CUST_SITE_USES_ALL',
x_owner_table_id => l_mosr_owner_table_id,
x_return_status => l_temp_return_status);
IF p_create_update_flag = 'U' AND
p_cust_site_use_rec.orig_system_reference IS NOT NULL
and l_validate_flag = 'Y'
THEN
validate_nonupdateable (
p_column => 'orig_system_reference',
p_column_value => p_cust_site_use_rec.orig_system_reference,
p_old_column_value => l_orig_system_reference,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'orig_system_reference is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.freight_term <> NVL( l_freight_term, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'freight_term',
p_lookup_table => 'SO_LOOKUPS',
p_lookup_type => 'FREIGHT_TERMS',
p_column_value => p_cust_site_use_rec.freight_term,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_site_use_rec.primary_flag IS NOT NULL
THEN
validate_cannot_update_to_null (
p_column => 'primary_flag',
p_column_value => p_cust_site_use_rec.primary_flag,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'primary_flag cannot be updated to NULL. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCT_SITES site,
HZ_CUST_SITE_USES su
WHERE site.CUST_ACCOUNT_ID = l_cust_account_id
AND su.CUST_ACCT_SITE_ID = site.CUST_ACCT_SITE_ID
AND su.SITE_USE_CODE = l_site_use_code
AND su.PRIMARY_FLAG = 'Y'
--Bug 2792589 AND su.STATUS = 'Y'
AND su.STATUS = 'A'
AND ( p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND su.ROWID <> p_rowid ))
AND ROWNUM = 1;
IF p_create_update_flag = 'C' THEN
IF p_cust_site_use_rec.primary_flag IS NULL OR
p_cust_site_use_rec.primary_flag = fnd_api.g_miss_char
THEN
l_primary_flag := 'N';
IF (p_create_update_flag = 'C' or
(p_create_update_flag ='U' and
p_cust_site_use_rec.ship_sets_include_lines_flag <> NVL(l_ship_sets_inc_lines_f,FND_API.G_MISS_CHAR)))
THEN
IF p_cust_site_use_rec.ship_sets_include_lines_flag = 'Y'
THEN
BEGIN
SELECT decode(p_cust_site_use_rec.ship_sets_include_lines_flag,p_cust_site_use_rec.arrivalsets_include_lines_flag,
'N',l_arrivalsets_inc_lines_f,
decode(p_cust_site_use_rec.arrivalsets_include_lines_flag,l_ship_sets_inc_lines_f,
'Y','N'),'Y')
INTO l_dummy
FROM DUAL;
IF (p_create_update_flag ='C' or
(p_create_update_flag ='U' and
p_cust_site_use_rec.arrivalsets_include_lines_flag <> NVL(l_arrivalsets_inc_lines_f,FND_API.G_MISS_CHAR)))
THEN
IF p_cust_site_use_rec.arrivalsets_include_lines_flag = 'Y'
THEN
BEGIN
SELECT decode(p_cust_site_use_rec.arrivalsets_include_lines_flag,p_cust_site_use_rec.ship_sets_include_lines_flag,
'N',l_ship_sets_inc_lines_f,
decode(p_cust_site_use_rec.ship_sets_include_lines_flag,l_arrivalsets_inc_lines_f,
'Y','N'),'Y')
INTO l_dummy
FROM DUAL;
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.tax_code <> NVL( l_tax_code, FND_API.G_MISS_CHAR ) ) )
THEN
check_tax_code(
p_column => 'tax_code',
p_column_value => p_cust_site_use_rec.tax_code,
-- Bug 4713150
-- p_org_id => p_cust_site_use_rec.org_id, -- Bug 3456489
p_called_from => 'validate_cust_site_use',
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.payment_term_id <> NVL( l_payment_term_id, FND_API.G_MISS_NUM ) ) )
THEN
check_payterm_id_fk(
p_column => 'payment_term_id',
p_column_value => p_cust_site_use_rec.payment_term_id,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.demand_class_code <> NVL(l_demand_class_code,FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup(
p_column =>'demand_class_code',
p_lookup_table =>'FND_COMMON_LOOKUPS',
p_lookup_type =>'DEMAND_CLASS',
p_column_value =>p_cust_site_use_rec.demand_class_code,
x_return_status =>x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.primary_salesrep_id <> NVL(l_primary_salesrep_id,FND_API.G_MISS_NUM) ) )
THEN
check_prim_salesrep(
p_column => 'primary_salesrep_id',
p_column_value => p_cust_site_use_rec.primary_salesrep_id,
p_org_id => p_cust_site_use_rec.org_id, -- Bug 3456489
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.finchrg_receivables_trx_id <> NVL(l_finchrg_receivables_trx_id,FND_API.G_MISS_NUM) ) )
THEN
check_finchrg_trx_fk(
p_column => 'finchrg_receivables_trx_id',
p_column_value => p_cust_site_use_rec.finchrg_receivables_trx_id,
p_org_id => p_cust_site_use_rec.org_id, -- Bug 3456489
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.order_type_id <> NVL(l_order_type_id,FND_API.G_MISS_NUM) ) )
THEN
check_ord_type(
p_column => 'order_type_id',
p_column_value => p_cust_site_use_rec.order_type_id,
p_org_id => p_cust_site_use_rec.org_id, -- Bug 3456489
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.price_list_id <> NVL(l_price_list_id,FND_API.G_MISS_NUM) ) )
THEN
check_price_list_fk(
p_column => 'price_list_id',
p_column_value => p_cust_site_use_rec.price_list_id,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.item_cross_ref_pref <> NVL(l_item_cross_ref_pref,FND_API.G_MISS_CHAR) ) )
THEN
IF p_cust_site_use_rec.item_cross_ref_pref NOT IN('INT','CUST')
THEN
check_item_cross_ref(
p_column => 'price_list_id',
p_column_value => p_cust_site_use_rec.item_cross_ref_pref,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.warehouse_id <> NVL(l_warehouse_id,FND_API.G_MISS_NUM) ) )
THEN
check_warehouse(
p_column => 'warehouse_id',
p_column_value => p_cust_site_use_rec.warehouse_id,
x_return_status => x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.fob_point <> NVL(l_fob_point,FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup(
p_column =>'fob_point',
p_lookup_type =>'FOB',
p_column_value =>p_cust_site_use_rec.fob_point,
x_return_status =>x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.date_type_preference <> NVL(l_date_type_preference,FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup(
p_column =>'date_type_preference',
p_lookup_table =>'OE_LOOKUPS',
p_lookup_type =>'REQUEST_DATE_TYPE',
p_column_value =>p_cust_site_use_rec.date_type_preference,
x_return_status =>x_return_status );
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.ship_partial <> NVL(l_ship_partial,FND_API.G_MISS_CHAR) ) )
THEN
validate_lookup (
p_column => 'ship_partial',
p_lookup_type => 'YES/NO',
p_column_value => p_cust_site_use_rec.ship_partial,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_site_use_rec.ship_via <> NVL( l_ship_via, FND_API.G_MISS_CHAR ) ) )
THEN
check_oe_ship_methods_v_fk (
p_entity => 'SITE_USE',
p_column => 'ship_via',
p_column_value => p_cust_site_use_rec.ship_via,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_cust_site_use_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_cust_site_use_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_cust_account_role_rec Customer account role record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
*
*/
PROCEDURE validate_cust_account_role (
p_create_update_flag IN VARCHAR2,
p_cust_account_role_rec IN HZ_CUST_ACCOUNT_ROLE_V2PUB.CUST_ACCOUNT_ROLE_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := ''; -- 'validate_cust_account_role'
IF p_create_update_flag = 'U' THEN
SELECT PARTY_ID, CUST_ACCOUNT_ID, CUST_ACCT_SITE_ID,
ROLE_TYPE, ORIG_SYSTEM_REFERENCE, CREATED_BY_MODULE,
APPLICATION_ID, STATUS
INTO l_party_id, l_cust_account_id, l_cust_acct_site_id,
l_role_type, l_orig_system_reference, l_created_by_module,
l_application_id, l_status
FROM HZ_CUST_ACCOUNT_ROLES
WHERE CUST_ACCOUNT_ROLE_ID = p_cust_account_role_rec.cust_account_role_id;
IF p_create_update_flag = 'C' THEN
-- If primary key value is passed, check for uniqueness.
-- If primary key value is not passed, it will be generated
-- from sequence by table handler.
IF p_cust_account_role_rec.cust_account_role_id IS NOT NULL AND
p_cust_account_role_rec.cust_account_role_id <> FND_API.G_MISS_NUM
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES
WHERE CUST_ACCOUNT_ROLE_ID = p_cust_account_role_rec.cust_account_role_id;
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'party_id',
p_column_value => p_cust_account_role_rec.party_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_account_role_rec.party_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_cust_account_role_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'party_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_cust_account_role_rec.party_id IS NOT NULL AND
p_cust_account_role_rec.party_id <> FND_API.G_MISS_NUM
THEN
check_party_fk (
p_column => 'party_id',
p_column_value => p_cust_account_role_rec.party_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'cust_account_id',
p_column_value => p_cust_account_role_rec.cust_account_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_account_role_rec.cust_account_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'cust_account_id',
p_column_value => p_cust_account_role_rec.cust_account_id,
p_old_column_value => l_cust_account_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'cust_account_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_cust_account_role_rec.cust_account_id IS NOT NULL AND
p_cust_account_role_rec.cust_account_id <> FND_API.G_MISS_NUM
THEN
check_cust_account_fk (
p_column => 'cust_account_id',
p_column_value => p_cust_account_role_rec.cust_account_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_account_role_rec.cust_acct_site_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'cust_acct_site_id',
p_column_value => p_cust_account_role_rec.cust_acct_site_id,
p_old_column_value => l_cust_acct_site_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'cust_acct_site_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_cust_account_role_rec.cust_acct_site_id IS NOT NULL AND
p_cust_account_role_rec.cust_acct_site_id <> FND_API.G_MISS_NUM
THEN
BEGIN
SELECT CUST_ACCOUNT_ID,ORG_ID
INTO l_cust_account_id,l_org_id
FROM HZ_CUST_ACCT_SITES_ALL
WHERE CUST_ACCT_SITE_ID = p_cust_account_role_rec.cust_acct_site_id;
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'role_type',
p_column_value => p_cust_account_role_rec.role_type,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_cust_account_role_rec.role_type IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'role_type',
p_column_value => p_cust_account_role_rec.role_type,
p_old_column_value => l_role_type,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'role_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_cust_account_role_rec.role_type IS NOT NULL AND
p_cust_account_role_rec.role_type <> FND_API.G_MISS_CHAR
THEN
validate_lookup (
p_column => 'role_type',
p_lookup_type => 'ACCT_ROLE_TYPE',
p_column_value => p_cust_account_role_rec.role_type,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' THEN
IF p_cust_account_role_rec.cust_acct_site_id IS NULL OR
p_cust_account_role_rec.cust_acct_site_id = FND_API.G_MISS_NUM
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES
WHERE CUST_ACCOUNT_ID = p_cust_account_role_rec.cust_account_id
AND PARTY_ID = p_cust_account_role_rec.party_id
AND CUST_ACCT_SITE_ID IS NULL
AND ROLE_TYPE = p_cust_account_role_rec.role_type
AND STATUS='A' -- Bug 4624292
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES
WHERE CUST_ACCT_SITE_ID = p_cust_account_role_rec.cust_acct_site_id
AND PARTY_ID = p_cust_account_role_rec.party_id
AND ROLE_TYPE = p_cust_account_role_rec.role_type
AND STATUS='A' -- Bug 4624292
AND ROWNUM = 1;
ELSE -- Update case (Bug 4624292)
IF p_cust_account_role_rec.status='A'
AND l_status='I'
THEN
IF l_cust_acct_site_id IS NOT NULL
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES
WHERE
CUST_ACCT_SITE_ID = nvl(p_cust_account_role_rec.cust_acct_site_id,l_cust_acct_site_id)
AND PARTY_ID = nvl(p_cust_account_role_rec.party_id,l_party_id)
AND ROLE_TYPE = nvl(p_cust_account_role_rec.role_type,l_role_type)
AND STATUS='A'
AND ROWID<>p_rowid
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES
WHERE
CUST_ACCT_SITE_ID IS NULL
AND CUST_ACCOUNT_ID = nvl(p_cust_account_role_rec.cust_account_id,l_cust_account_id)
AND PARTY_ID = nvl(p_cust_account_role_rec.party_id,l_party_id)
AND ROLE_TYPE = nvl(p_cust_account_role_rec.role_type,l_role_type)
AND STATUS='A'
AND ROWID<>p_rowid
AND ROWNUM = 1;
and p_create_update_flag = 'U'
then
hz_orig_system_ref_pub.get_owner_table_id
(p_orig_system => p_cust_account_role_rec.orig_system,
p_orig_system_reference => p_cust_account_role_rec.orig_system_reference,
p_owner_table_name => 'HZ_CUST_ACCOUNT_ROLES',
x_owner_table_id => l_mosr_owner_table_id,
x_return_status => l_temp_return_status);
IF p_create_update_flag = 'U' AND
p_cust_account_role_rec.orig_system_reference IS NOT NULL
and l_validate_flag = 'Y'
THEN
validate_nonupdateable (
p_column => 'orig_system_reference',
p_column_value => p_cust_account_role_rec.orig_system_reference,
p_old_column_value => l_orig_system_reference,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'orig_system_reference is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
l_cust_account_id := p_cust_account_role_rec.cust_account_id;
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES
WHERE CUST_ACCOUNT_ID = l_cust_account_id
AND CUST_ACCT_SITE_ID IS NULL
AND PRIMARY_FLAG = 'Y'
AND ( p_create_update_flag = 'C' OR
p_create_update_flag = 'U' AND ROWID <> p_rowid )
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES
WHERE CUST_ACCOUNT_ID = l_cust_account_id
AND CUST_ACCT_SITE_ID = l_cust_acct_site_id
AND PRIMARY_FLAG = 'Y'
AND ( p_create_update_flag = 'C' OR
p_create_update_flag = 'U' AND ROWID <> p_rowid )
AND ROWNUM = 1;
IF p_create_update_flag = 'U' AND
p_cust_account_role_rec.status IS NOT NULL
THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_cust_account_role_rec.status,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'status cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_cust_account_role_rec.status <> NVL( l_status, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_cust_account_role_rec.status,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_cust_account_role_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_cust_account_role_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_role_responsibility_rec Customer account role responsibility record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
*
*/
PROCEDURE validate_role_responsibility (
p_create_update_flag IN VARCHAR2,
p_role_responsibility_rec IN HZ_CUST_ACCOUNT_ROLE_V2PUB.ROLE_RESPONSIBILITY_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := ''; -- 'validate_role_repsonsibility'
IF p_create_update_flag = 'U' THEN
SELECT RESPONSIBILITY_TYPE, CUST_ACCOUNT_ROLE_ID, ORIG_SYSTEM_REFERENCE ,
CREATED_BY_MODULE, APPLICATION_ID
INTO l_responsibility_type, l_cust_account_role_id, l_orig_system_reference,
l_created_by_module, l_application_id
FROM HZ_ROLE_RESPONSIBILITY
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
-- If primary key value is passed, check for uniqueness.
-- If primary key value is not passed, it will be generated
-- from sequence by table handler.
IF p_role_responsibility_rec.responsibility_id IS NOT NULL AND
p_role_responsibility_rec.responsibility_id <> FND_API.G_MISS_NUM
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_ROLE_RESPONSIBILITY
WHERE RESPONSIBILITY_ID = p_role_responsibility_rec.responsibility_id;
p_create_update_flag => p_create_update_flag,
p_column => 'responsibility_type',
p_column_value => p_role_responsibility_rec.responsibility_type,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_role_responsibility_rec.responsibility_type <> NVL( l_responsibility_type, FND_API.G_MISS_CHAR ) ) )
THEN
validate_lookup (
p_column => 'responsibility_type',
p_lookup_type => 'SITE_USE_CODE',
p_column_value => p_role_responsibility_rec.responsibility_type,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'cust_account_role_id',
p_column_value => p_role_responsibility_rec.cust_account_role_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_role_responsibility_rec.cust_account_role_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'cust_account_role_id',
p_column_value => p_role_responsibility_rec.cust_account_role_id,
p_old_column_value => l_cust_account_role_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'cust_account_role_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_role_responsibility_rec.cust_account_role_id IS NOT NULL AND
p_role_responsibility_rec.cust_account_role_id <> FND_API.G_MISS_NUM
THEN
check_cust_account_role_fk (
p_column => 'cust_account_role_id',
p_column_value => p_role_responsibility_rec.cust_account_role_id,
x_return_status => x_return_status );
'create_update_flag = ' || p_create_update_flag || ' ' ||
'p_rowid = ' || p_rowid || ' ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
SELECT 'Y' INTO l_dummy
FROM HZ_ROLE_RESPONSIBILITY
WHERE CUST_ACCOUNT_ROLE_ID = l_cust_account_role_id
AND RESPONSIBILITY_TYPE = l_responsibility_type
AND ( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND ROWID <> p_rowid ) )
AND ROWNUM = 1;
IF p_create_update_flag = 'U' AND
p_role_responsibility_rec.orig_system_reference IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'orig_system_reference',
p_column_value => p_role_responsibility_rec.orig_system_reference,
p_old_column_value => l_orig_system_reference,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'orig_system_reference is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
l_cust_account_role_id := p_role_responsibility_rec.cust_account_role_id;
SELECT 'Y' INTO l_dummy
FROM HZ_ROLE_RESPONSIBILITY
WHERE CUST_ACCOUNT_ROLE_ID = l_cust_account_role_id
AND PRIMARY_FLAG = 'Y'
AND ( p_create_update_flag = 'C' OR
p_create_update_flag = 'U' AND ROWID <> p_rowid )
AND ROWNUM = 1;
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_role_responsibility_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_role_responsibility_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNTS
WHERE CUST_ACCOUNT_ID = p_column_value;
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
WHERE CUST_ACCT_SITE_ID = p_column_value
AND ORG_ID = p_org_id; -- Bug 3456489
SELECT org_id INTO l_org_id
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID = p_column_value;
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES
WHERE CUST_ACCOUNT_ROLE_ID = p_column_value
AND CUST_ACCOUNT_ID = p_customer_id;
SELECT 'Y' INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES
WHERE CUST_ACCOUNT_ROLE_ID = p_column_value;
SELECT 'Y' INTO l_dummy
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID = p_column_value
/* Bug 3591694.
Retrieve only those records, which are effective on sysdate.
Both effective_start_date and effective_end_date are not null columns and
as such there is no need to explicitely do a NVL.
*/
AND EFFECTIVE_START_DATE <= SYSDATE
AND EFFECTIVE_END_DATE >= SYSDATE
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM AR_COLLECTORS
WHERE COLLECTOR_ID = p_column_value;
SELECT 'Y' INTO l_dummy
FROM HZ_PARTIES
WHERE PARTY_ID = p_column_value;
SELECT 'Y' INTO l_dummy
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = p_column_value;
SELECT 'Y' INTO l_dummy
FROM FND_CURRENCIES
WHERE CURRENCY_CODE = p_column_value
AND CURRENCY_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE))
AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE));
SELECT MIN(ORG_ID) INTO l_org_id
FROM AR_SYSTEM_PARAMETERS;
SELECT 'Y'
into l_dummy
from ra_terms_b
where term_id = p_column_value
-- and billing_cycle_id is null
and trunc(sysdate) between
start_date_active and nvl(end_date_active,trunc(sysdate));
SELECT 'Y' INTO l_dummy
FROM RA_TERMS ra
WHERE ra.term_id = p_column_value
AND trunc(sysdate) between START_DATE_ACTIVE and nvl(END_DATE_ACTIVE,trunc(sysdate));
SELECT 'Y'
into l_dummy
from ra_terms_b
where term_id = p_column_value
and billing_cycle_id is null
and trunc(sysdate) between
start_date_active and nvl(end_date_active,trunc(sysdate));
SELECT 'Y'
into l_dummy
from ra_terms_b t
where t.term_id = p_column_value
and t.billing_cycle_id is not null
and trunc(sysdate) between
t.start_date_active and nvl(t.end_date_active,trunc(sysdate));
SELECT 'Y'
into l_dummy
from AR_STANDARD_TEXT
where STANDARD_TEXT_ID = p_column_value
and trunc(sysdate) between
start_date and nvl(end_date,trunc(sysdate));
SELECT 'Y'
into l_dummy
from gl_daily_conversion_types
where conversion_type <> 'USER'
and conversion_type = p_column_value;
SELECT 'Y'
into l_dummy
from ar_charge_schedules
where schedule_id = p_column_value;
SELECT 'Y' INTO l_dummy
FROM AR_RECEIVABLES_TRX_ALL art
WHERE art.receivables_trx_id = p_column_value
AND status = 'A'
AND art.org_id = p_org_id; -- Bug 3456489
SELECT 'Y' INTO l_dummy
FROM SO_PRICE_LISTS pl
WHERE pl.price_list_id = p_column_value
AND sysdate between nvl(pl.start_date_active,sysdate) and nvl(pl.end_date_active,sysdate);
SELECT 'Y'
INTO l_dummy
FROM qp_list_headers_b lh
WHERE list_header_id = p_column_value
AND list_type_code = 'PRL'
AND (sysdate between nvl(lh.start_date_active,sysdate) and nvl(lh.end_date_active,sysdate)
AND nvl(active_flag, 'N') = 'Y');
SELECT 'Y' INTO l_dummy
FROM MTL_CROSS_REFERENCE_TYPES mtl
WHERE mtl.cross_reference_type = p_column_value;
SELECT 'Y' INTO l_dummy
FROM zx_output_classifications_v
WHERE lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
AND lookup_code = p_column_value
AND trunc(sysdate) BETWEEN trunc(start_date_active) AND nvl(trunc(end_date_active),trunc(sysdate))
AND enabled_flag = 'Y'
-- Bug 5136396
AND org_id IN (l_org_id, -99)
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM AR_VAT_TAX_ALL avt
WHERE avt.tax_code = p_column_value
AND trunc(SYSDATE) BETWEEN START_DATE AND NVL(END_DATE,trunc(sysdate))
AND avt.enabled_flag = 'Y'
AND avt.tax_class = 'O'
AND avt.displayed_flag = 'Y'
AND avt.set_of_books_id = l_sob_id
AND avt.org_id = p_org_id;
SELECT 'Y' INTO l_dummy
FROM ORG_ORGANIZATION_DEFINITIONS org
WHERE org.organization_id = p_column_value
AND trunc(sysdate) <= nvl(trunc(org.disable_date),trunc(sysdate));
SELECT 'Y' INTO l_dummy
-- FROM RA_SALESREPS ras -- Bug 3456489
FROM JTF_RS_SALESREP ras
WHERE ras.salesrep_id = p_column_value
AND ras.org_id = p_org_id
AND sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
AND nvl(status, 'A') = 'A'
AND salesrep_id NOT IN( -1,-2);
SELECT 'Y' INTO l_dummy
FROM OE_ORDER_TYPES_V ot
WHERE ot.order_type_id = p_column_value
AND sysdate between nvl(ot.start_date_active,sysdate) and nvl(ot.end_date_active,sysdate);
SELECT 'Y' INTO l_dummy
FROM OE_TRANSACTION_TYPES_ALL ot
WHERE ot.order_type_id = p_column_value
AND ot.transaction_type_code = 'ORDER'
AND ot.org_id = p_org_id
AND sysdate between nvl(ot.start_date_active,sysdate)
and nvl(ot.end_date_active,sysdate);
SELECT 'Y' into l_dummy
FROM ar_autocash_hierarchies h
WHERE h.status = 'A'
AND h.autocash_hierarchy_id =
p_column_value;
SELECT 'Y' into l_dummy
FROM ar_statement_cycles sc
WHERE sc.status = 'A'
AND sc.statement_cycle_id =
p_column_value;
SELECT 'Y' into l_dummy
FROM ar_dunning_letter_sets dls
WHERE dls.status = 'A'
AND dls.dunning_letter_set_id =
p_column_value;
SELECT 'Y'
into l_dummy
from ra_terms_b
where term_id = p_column_value
-- and billing_cycle_id is null
and trunc(sysdate) between
start_date_active and nvl(end_date_active,trunc(sysdate));
SELECT 'Y'
into l_dummy
from ra_terms_b t
where t.term_id = p_column_value
and t.billing_cycle_id is not null
and trunc(sysdate) between
t.start_date_active and nvl(t.end_date_active,trunc(sysdate));
SELECT 'Y' into l_dummy
FROM ra_terms t
WHERE trunc(sysdate)
BETWEEN t.start_date_active AND
nvl(t.end_date_active,trunc(sysdate))
AND t.term_id =
p_column_value;
SELECT 'Y' into l_dummy
FROM ra_grouping_rules gr
WHERE trunc(sysdate)
BETWEEN gr.start_date AND
nvl(gr.end_date,trunc(sysdate))
AND gr.grouping_rule_id =
p_column_value;
SELECT 'Y' INTO l_dummy
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = l_lookup_code
AND ( ENABLED_FLAG = 'Y' AND
TRUNC( SYSDATE ) BETWEEN
TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) );
SELECT 'Y' INTO l_dummy
FROM SO_LOOKUPS
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = l_lookup_code
AND ( ENABLED_FLAG = 'Y' AND
TRUNC( SYSDATE ) BETWEEN
TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) );
SELECT 'Y' INTO l_dummy
FROM OE_SHIP_METHODS_V
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = l_lookup_code
AND ( ENABLED_FLAG = 'Y' AND
TRUNC( SYSDATE ) BETWEEN
TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) )
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM OE_LOOKUPS
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = l_lookup_code
AND ( ENABLED_FLAG = 'Y' AND
TRUNC( SYSDATE ) BETWEEN
TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) )
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM FND_COMMON_LOOKUPS
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = l_lookup_code
AND ( ENABLED_FLAG = 'Y' AND
TRUNC( SYSDATE ) BETWEEN
TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) )
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = l_lookup_code
AND ( ENABLED_FLAG = 'Y' AND
TRUNC( SYSDATE ) BETWEEN
TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) )
AND ROWNUM = 1;
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
p_restricted IN VARCHAR2 DEFAULT 'N',
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
IF ( p_create_update_flag = 'C' AND
( p_column_value IS NULL OR
p_column_value = FND_API.G_MISS_CHAR ) ) OR
( p_create_update_flag = 'U' AND
p_column_value = FND_API.G_MISS_CHAR )
THEN
l_error := TRUE;
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN NUMBER,
p_restricted IN VARCHAR2 DEFAULT 'N',
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
IF ( p_create_update_flag = 'C' AND
( p_column_value IS NULL OR
p_column_value = FND_API.G_MISS_NUM ) ) OR
( p_create_update_flag = 'U' AND
p_column_value = FND_API.G_MISS_NUM )
THEN
l_error := TRUE;
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN DATE,
p_restricted IN VARCHAR2 DEFAULT 'N',
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
IF ( p_create_update_flag = 'C' AND
( p_column_value IS NULL OR
p_column_value = FND_API.G_MISS_DATE ) ) OR
( p_create_update_flag = 'U' AND
p_column_value = FND_API.G_MISS_DATE )
THEN
l_error := TRUE;
PROCEDURE validate_nonupdateable (
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
p_old_column_value IN VARCHAR2,
p_restricted IN VARCHAR2 DEFAULT 'Y',
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
END validate_nonupdateable;
PROCEDURE validate_nonupdateable (
p_column IN VARCHAR2,
p_column_value IN NUMBER,
p_old_column_value IN NUMBER,
p_restricted IN VARCHAR2 DEFAULT 'Y',
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
END validate_nonupdateable;
PROCEDURE validate_nonupdateable (
p_column IN VARCHAR2,
p_column_value IN DATE,
p_old_column_value IN DATE,
p_restricted IN VARCHAR2 DEFAULT 'Y',
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
END validate_nonupdateable;
p_create_update_flag IN VARCHAR2,
p_start_date_column_name IN VARCHAR2,
p_start_date IN DATE,
p_old_start_date IN DATE,
p_end_date_column_name IN VARCHAR2,
p_end_date IN DATE,
p_old_end_date IN DATE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_start_date DATE := p_old_start_date;
IF p_create_update_flag = 'C' THEN
l_start_date := p_start_date;
ELSIF p_create_update_flag = 'U' THEN
IF p_start_date IS NOT NULL
THEN
IF p_start_date = FND_API.G_MISS_DATE THEN
l_start_date := NULL;
PROCEDURE validate_cannot_update_to_null (
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_column_value = FND_API.G_MISS_CHAR THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
END validate_cannot_update_to_null;
PROCEDURE validate_cannot_update_to_null (
p_column IN VARCHAR2,
p_column_value IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_column_value = FND_API.G_MISS_NUM THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
END validate_cannot_update_to_null;
PROCEDURE validate_cannot_update_to_null (
p_column IN VARCHAR2,
p_column_value IN DATE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_column_value = FND_API.G_MISS_DATE THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
END validate_cannot_update_to_null;
* p_create_update_flag create update flag
* p_created_by_module created by module
* p_old_created_by_module old value of created by module
* x_return_status return status
*/
PROCEDURE validate_created_by_module (
p_create_update_flag IN VARCHAR2,
p_created_by_module IN VARCHAR2,
p_old_created_by_module IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_column CONSTANT VARCHAR2(30) := 'created_by_module';
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => l_column,
p_column_value => p_created_by_module,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_created_by_module IS NOT NULL
THEN
validate_nonupdateable (
p_column => l_column,
p_column_value => p_created_by_module,
p_old_column_value => p_old_created_by_module,
p_restricted => 'N',
x_return_status => x_return_status);
p_message => l_column || ' is non-updateable. It can be updated from NULL to a value. ' ||
'x_return_status = ' || x_return_status,
p_msg_level => fnd_log.level_statement);
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
(p_old_created_by_module IS NULL OR
p_created_by_module <> p_old_created_by_module)))
THEN
validate_lookup (
p_column => l_column,
p_lookup_type => 'HZ_CREATED_BY_MODULES',
p_column_value => p_created_by_module,
x_return_status => x_return_status);
* p_create_update_flag create update flag
* p_application_id application id
* p_old_application_id old value of application id
* x_return_status return status
*/
PROCEDURE validate_application_id (
p_create_update_flag IN VARCHAR2,
p_application_id IN NUMBER,
p_old_application_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_column CONSTANT VARCHAR2(30) := 'application_id';
IF p_create_update_flag = 'U' AND
p_application_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => l_column,
p_column_value => p_application_id,
p_old_column_value => p_old_application_id,
p_restricted => 'N',
x_return_status => x_return_status);
p_message => l_column || ' is non-updateable. It can be updated from NULL to a value. ' ||
'x_return_status = ' || x_return_status,
p_msg_level => fnd_log.level_statement);
p_create_update_flag IN VARCHAR2,
p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_profile_class_rec HZ_CUST_PROFILE_CLASSES%ROWTYPE;
SELECT COUNT(*)
INTO l_bill_level_count
FROM ar_lookups
WHERE lookup_type = 'HZ_CONS_BILL_LEVEL'
AND lookup_code = p_customer_profile_rec.cons_bill_level
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active, SYSDATE+1)
AND enabled_flag = 'Y';
SELECT COUNT(*)
INTO l_bill_type_count
FROM ar_lookups
WHERE lookup_type = 'HZ_CONS_INV_TYPE'
AND lookup_code = p_customer_profile_rec.cons_inv_type
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active, SYSDATE+1)
AND enabled_flag = 'Y'
AND lookup_code <> 'IMPORTED';
SELECT COUNT(*)
INTO l_bill_type_count
FROM ar_lookups
WHERE lookup_type = 'HZ_CONS_INV_TYPE'
AND lookup_code = p_customer_profile_rec.cons_inv_type
AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active, SYSDATE+1)
AND enabled_flag = 'Y';