[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT meaning INTO x_lookup_meaning
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 meaning INTO x_lookup_meaning
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 meaning INTO x_lookup_meaning
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 meaning INTO x_lookup_meaning
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 ) ) )
-- Bug 4112157
--AND ROWNUM = 1;
SELECT nls_language INTO x_lookup_meaning
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE = l_lookup_code;
SELECT INCL_UNRELATED_ENTITIES INTO l_dummy
FROM HZ_RELATIONSHIP_TYPES
WHERE RELATIONSHIP_TYPE = l_relationship_type
AND ROWNUM = 1;
* p_create_update_flag 'C' ( create mode ), 'U' ( update mode )
* p_column Column name you want to validate.
* p_column_value Column value
* p_restriced If set to 'Y', p_column_value should be passed
* in with some value in both create and update
* mode. If set to 'N', p_column_value can be
* NULL in update mode. Default is 'N'.
* IN/OUT:
* x_return_status Return status.
*
* NOTES
* The procedure is overloaded for different column type, i.e. VARCHAR2,
* NUMBER, and DATE.
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
*
*/
PROCEDURE validate_mandatory (
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
p_restricted IN VARCHAR2,
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,
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,
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
*
* DESCRIPTION
* Validate nonupdateable field.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_column Column name you want to validate.
* p_column_value Column value
* p_old_column_value Current database column value
* p_restriced If set to 'Y', column can not be updated
* even the database value is null. This is
* default value and as long as p_column_value
* is not equal to p_old_column_error, return
* status will be set to error.
* If set to 'N', if database value is null,
* we can update it to a value. If database value
* is not null and if p_column_value is not equal
* to p_old_column_value, return status will be
* set to error.
* IN/OUT:
* x_return_status Return status.
*
* NOTES
* The procedure is overloaded for different column type, i.e. VARCHAR2,
* NUMBER, and DATE. The procedure should be called in update mode.
*
* For example:
* IF p_create_update_flag = 'U' THEN
* validate_nonupdateable( ... );
PROCEDURE validate_nonupdateable (
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
p_old_column_value IN VARCHAR2,
p_restricted IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_old_column_value VARCHAR2(2000);
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,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_old_column_value NUMBER;
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,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_old_column_value DATE;
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
END validate_nonupdateable;
* p_create_update_flag 'C' ( create mode ), 'U' ( update mode )
* p_start_date_column_name Column name of start date
* p_start_date New start date
* p_old_start_date Database start date in update mode
* p_end_date_column_name Column name of end date
* p_end_date New end date
* p_old_end_date Database end date in update mode
* IN/OUT:
* x_return_status Return status.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
*
*/
PROCEDURE validate_start_end_date (
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
*
* DESCRIPTION
* Validate column cannot be updated to null.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_column Column name you want to validate.
* p_column_value Column value
* IN/OUT:
* x_return_status Return status.
*
* NOTES
* The procedure is overloaded for different column type, i.e. VARCHAR2,
* NUMBER, and DATE. The procedure should be called in update mode.
*
* For example:
* IF p_create_update_flag = 'U' THEN
* validate_cannot_update_to_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;
* PROCEDURE validate_cannot_update_to_null
*
* DESCRIPTION
* Validate column cannot be updated to null.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_column Column name you want to validate.
* p_lookup_table Table/view name you want to validate against to.
* For now, we are supporting
* AR_LOOKUPS
* SO_LOOKUPS
* OE_SHIP_METHODS_V
* FND_LOOKUP_VALUES
* Default value is AR_LOOKUPS
* p_lookup_type FND lookup type
* p_column_value Column value
* IN/OUT:
* x_return_status Return status.
*
* NOTES
* The procedure is using cache strategy for performance improvement.
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
*
*/
PROCEDURE validate_lookup (
p_column IN VARCHAR2,
p_lookup_table IN VARCHAR2,
p_lookup_type IN VARCHAR2,
p_column_value IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
SELECT SPID INTO l_spid
FROM V$PROCESS
WHERE ADDR = (
SELECT PADDR
FROM V$SESSION
WHERE AUDSID = USERENV('SESSIONID') );
* last_updated_by
* last_update_date
* last_update_login
* request_id
* program_id
* program_application_id
* program_update_date
* user_id
* application_id
*
* DESCRIPTION
* Return standard who value.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Jianying Huang o Created.
* 01-27-2003 Sreedhar Mohan o Added application_id.
*
*/
FUNCTION created_by RETURN NUMBER IS
BEGIN
RETURN NVL(FND_GLOBAL.user_id,-1);
FUNCTION last_updated_by RETURN NUMBER IS
BEGIN
RETURN NVL(FND_GLOBAL.user_id,-1);
END last_updated_by;
FUNCTION last_update_date RETURN DATE IS
BEGIN
RETURN SYSDATE;
END last_update_date;
FUNCTION last_update_login RETURN NUMBER IS
BEGIN
IF FND_GLOBAL.conc_login_id = -1 OR
FND_GLOBAL.conc_login_id IS NULL
THEN
RETURN FND_GLOBAL.login_id;
END last_update_login;
FUNCTION program_update_date RETURN DATE IS
BEGIN
IF program_id IS NULL THEN
RETURN NULL;
END program_update_date;
* p_create_update_flag 'C' is for create. 'U' is for update.
* p_column_value Value of the column.
* p_default_value Default value of the column. Please note,
* for V1 API, most columns are defaulted to
* FND_API.G_MISS_XXX and for V2 API, we do
* not have default value for most columns.
* p_old_column_value Database value of the column. Only used
* in update mode.
*
* NOTES
* I am not making the function as public for now because it is used only by
* obsoleting content_source_type.
*
* MODIFICATION HISTORY
*
* 03-01-2002 Jianying Huang o Created.
*/
FUNCTION isColumnHasValue (
p_api_version IN VARCHAR2,
p_create_update_flag IN VARCHAR2,
p_column_value IN VARCHAR2,
p_default_value IN VARCHAR2,
p_old_column_value IN VARCHAR2
) RETURN VARCHAR2 IS
l_column_value VARCHAR2(2000);
IF p_create_update_flag = 'C' AND
l_column_value <> FND_API.G_MISS_CHAR AND
l_column_value <> NVL(p_default_value, FND_API.G_MISS_CHAR)
THEN
RETURN 'Y';
p_create_update_flag IN VARCHAR2,
p_column_value IN NUMBER,
p_default_value IN NUMBER,
p_old_column_value IN NUMBER
) RETURN VARCHAR2 IS
l_column_value NUMBER;
IF p_create_update_flag = 'C' AND
l_column_value <> FND_API.G_MISS_NUM AND
l_column_value <> NVL(p_default_value, FND_API.G_MISS_NUM)
THEN
RETURN 'Y';
p_create_update_flag IN VARCHAR2,
p_column_value IN DATE,
p_default_value IN DATE,
p_old_column_value IN DATE
) RETURN VARCHAR2 IS
l_column_value DATE;
IF p_create_update_flag = 'C' AND
l_column_value <> FND_API.G_MISS_DATE AND
l_column_value <> NVL(p_default_value, FND_API.G_MISS_DATE)
THEN
RETURN 'Y';
* p_create_update_flag 'C' is for create. 'U' is for update.
* p_column Column name.
* p_column_value Value of the column.
* p_default_value Default value of the column. Please note,
* for V1 API, most columns are defaulted to
* FND_API.G_MISS_XXX and for V2 API, we do
* not have default value for most columns.
* p_old_column_value Database value of the column. Only used
* in update mode.
* OUT:
* x_return_status Return FND_API.G_RET_STS_ERROR if user
* is trying to pass value into an obsolete
* column in development site.
*
* NOTES
* I am not making the function as public for now because it is used only by
* obsoleting content_source_type. It is worth to call this function only when
* you obsolete one column. If you are obsoleting more than one columns, it
* is better to cancat them and then decide if need to raise exception. For
* this limitation, it is not worth to provide the function for NUMBER and
* DATE type of column.
*
* MODIFICATION HISTORY
*
* 03-01-2002 Jianying Huang o Created.
*/
PROCEDURE Check_ObsoleteColumn (
p_api_version IN VARCHAR2,
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
p_default_value IN VARCHAR2,
p_old_column_value IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF FND_PROFILE.value('HZ_API_ERR_ON_OBSOLETE_COLUMN') = 'Y' AND
isColumnHasValue (
p_api_version, p_create_update_flag,
p_column_value, p_default_value, p_old_column_value) = 'Y'
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OBSOLETE_COLUMN' );
select al.MEANING
-- psu.site_use_type,
-- psu.primary_per_type
from hz_party_sites ps,
hz_party_site_uses psu,
ar_lookups al
where
ps.party_site_id = l_party_site_id and
psu.party_site_id = ps.party_site_id and
psu.status = 'A' and
al.lookup_type = 'PARTY_SITE_USE_CODE' and
al.lookup_code = psu.SITE_USE_TYPE
order by primary_per_type DESC;
select al.MEANING
from hz_party_sites ps,
hz_party_site_uses psu,
ar_lookups al
where
ps.party_site_id = l_party_site_id and
psu.party_site_id = ps.party_site_id and
psu.status = 'A' and
al.lookup_type = 'PARTY_SITE_USE_CODE' and
al.lookup_code = psu.SITE_USE_TYPE
order by primary_per_type DESC;
select al.MEANING
from hz_cust_acct_sites s,
hz_cust_site_uses u,
ar_lookups al
where
s.cust_acct_site_id = l_acct_site_id and
u.cust_acct_site_id = s.cust_acct_site_id and
u.status = 'A' and
al.lookup_type = 'SITE_USE_CODE' and
al.lookup_code = u.SITE_USE_CODE
order by al.MEANING;
SELECT lu.meaning
FROM hz_org_contact_roles ocr,
fnd_lookup_values lu
WHERE ocr.org_contact_id = p_org_contact_id
AND ocr.status = 'A'
AND lu.view_application_id = 222
AND lu.language = userenv('LANG')
AND lu.lookup_type = 'CONTACT_ROLE_TYPE'
AND lu.lookup_code = ocr.role_type;
select
contact_point_id
INTO l_contact_point_id
from hz_contact_points CPPH
where contact_point_type = 'PHONE'
and primary_flag = 'Y'
and status = 'A'
and OWNER_TABLE_NAME = 'HZ_PARTIES'
and OWNER_TABLE_ID = p_party_id
and rownum = 1;
select email_address
INTO l_primary_email
from hz_contact_points
where contact_point_type = 'EMAIL'
and primary_flag = 'Y'
and status = 'A'
and OWNER_TABLE_NAME = 'HZ_PARTIES'
and OWNER_TABLE_ID = p_party_id
and rownum = 1;
select 'N'
from hz_dnb_request_log log, hz_organization_profiles org
where log.party_id= p_party_id
and org.party_id=log.party_id
and org.actual_content_source='DNB'
and org.effective_end_date is NULL
and trunc(org.last_update_date) > trunc(log.last_update_date)
and rownum =1
UNION
select 'Y'
from hz_dnb_request_log log, hz_organization_profiles org
where log.party_id= p_party_id
and org.party_id=log.party_id
and org.actual_content_source='DNB'
and org.effective_end_date is NULL
and trunc(org.last_update_date) = trunc(log.last_update_date)
and rownum =1;
select 'E' into l_exist
from hz_dnb_request_log
where party_id = p_party_id
and rownum = 1;
select 'Y' into l_exist from hz_organization_profiles org
where org.party_id = p_party_id
and org.actual_content_source='DNB'
and org.effective_end_date is NULL
and trunc(org.last_update_date) =
(select trunc(max(log.last_update_date)) from hz_dnb_request_log log
where log.party_id = p_party_id);
SELECT null
FROM hz_contact_preferences
WHERE contact_level_table = p_contact_level_table
AND contact_level_table_id = p_contact_level_table_id
AND preference_code = p_preference_code
AND status = 'A'
AND (preference_end_date IS NULL OR
TRUNC(preference_end_date) >= TRUNC(sysdate))
AND TRUNC(preference_start_date) <= TRUNC(sysdate)
AND ROWNUM = 1;
SELECT 'Y'
FROM HZ_ORIG_SYSTEMS_B
WHERE orig_system = p_content_source
AND orig_system_type = 'PURCHASED'
-- AND status = 'A'
AND rownum = 1;
SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND language = p_language
AND enabled_flag = 'Y'
AND (end_date_active IS NULL
OR end_date_active >= sysdate);
SELECT description
FROM FND_LOOKUP_VALUES
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND language = p_language
AND enabled_flag = 'Y'
AND (end_date_active IS NULL
OR end_date_active >= sysdate);
select psu.primary_per_type
from hz_party_site_uses psu
where
psu.party_site_id = l_party_site_id and
psu.status = 'A' and
psu.site_use_type = 'BILL_TO' and
psu.primary_per_type = 'Y'
order by primary_per_type DESC;
select psu.primary_per_type
from hz_party_site_uses psu
where
psu.party_site_id = l_party_site_id and
psu.status = 'A' and
psu.site_use_type = 'SHIP_TO' and
psu.primary_per_type = 'Y'
order by primary_per_type DESC;
* 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
BEGIN
-- skip mandatory and non-updateable check from logical API
IF G_CALLING_API IS NULL THEN
-- created_by_module is mandatory field
-- Since created_by_module is non-updateable, we only need to check mandatory
-- during creation.
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'created_by_module',
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 => 'created_by_module',
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 => 'created_by_module 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 => 'created_by_module',
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);
SELECT null
FROM hz_code_assignments c
WHERE c.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
AND c.class_category = 'RELATIONSHIP_TYPE_GROUP'
AND c.class_code = p_relationship_group_code
AND sysdate between c.start_date_active and nvl(c.end_date_active, sysdate+1)
AND c.status = 'A'
AND c.owner_table_id = p_relationship_type_id
AND ROWNUM = 1;