The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE do_update_loc_assignment(
p_location_id IN NUMBER,
p_do_addr_val IN VARCHAR2,
x_addr_val_status OUT NOCOPY VARCHAR2,
x_addr_warn_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2, /* Changed from OUT NOCOPY to IN OUT*/
p_lock_flag IN VARCHAR2 := FND_API.G_TRUE
);
* o Passed address1 to address4 to update_profile_pvt
*
+===========================================================================**/
PROCEDURE do_create_loc_assignment(
p_location_id IN NUMBER,
x_loc_id OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2, /* Changed from OUT NOCOPY to IN OUT*/
p_lock_flag IN VARCHAR2 := FND_API.G_FALSE,
p_created_by_module IN VARCHAR2,
p_application_id IN NUMBER
) IS
l_org_id NUMBER;
SELECT
LOCATION_ID,
ADDRESS_STYLE,
COUNTRY,
STATE,
PROVINCE,
COUNTY,
CITY,
POSTAL_CODE,
POSTAL_PLUS4_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10
FROM HZ_LOCATIONS WHERE LOCATION_ID = p_location_id;
SELECT 'Y'
INTO l_is_remit_to_location
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM HZ_PARTY_SITES PS
WHERE PS.LOCATION_ID = p_location_id
AND PS.PARTY_ID = -1);
| do_update_loc_assignment
|
| DESCRIPTION
| Updates loc assignments
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_location_id
| p_lock_flag
| OUT:
| x_loc_id
| IN/ OUT:
| x_return_status
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
|
* 05-29-2003 Ramesh Ch o Bug 2800555.Initialized arp_standard.sysparm with
* ar_system_parameters row values.
* 01-30-2004 Rajib Ranjan Borah o Bug 3395521.
* o Modified IF clause to handle NULL
* o Passed address1 to address4 to update_profile_pvt
* o Moved the call to update_location_pvt to outside
* the loop.
* o Updated the db_* variables to prevent unnecessary
* additional updates to HZ_LOCATIONS.
*
+===========================================================================**/
-- Removed following parameters since it id not used in the procedure
-- x_loc_id, p_created_by_module, p_application_id, x_org_id
-- Added p_do_addr_val, x_addr_val_status and x_addr_warn_msg for address validation.
PROCEDURE do_update_loc_assignment(
p_location_id IN NUMBER,
p_do_addr_val IN VARCHAR2,
x_addr_val_status OUT NOCOPY VARCHAR2,
x_addr_warn_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2, /* Changed from OUT NOCOPY to IN OUT*/
p_lock_flag IN VARCHAR2 := FND_API.G_TRUE
) IS
l_is_remit_to_location VARCHAR2(1) := 'N';
l_allow_update_std VARCHAR2(1);
SELECT
LOCATION_ID,
ADDRESS_STYLE,
COUNTRY,
STATE,
PROVINCE,
COUNTY,
CITY,
POSTAL_CODE,
POSTAL_PLUS4_CODE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10
FROM HZ_LOCATIONS WHERE LOCATION_ID = p_location_id;
SELECT DATE_VALIDATED, VALIDATION_STATUS_CODE
INTO l_date_validated, l_validation_status_code
FROM HZ_LOCATIONS
WHERE LOCATION_ID = p_location_id;
l_allow_update_std := nvl(fnd_profile.value('HZ_UPDATE_STD_ADDRESS'), 'Y');
IF(l_allow_update_std = 'N' AND
l_date_validated IS NOT NULL AND
l_validation_status_code IS NOT NULL)
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_NO_UPDATE');
SELECT 'Y'
INTO l_is_remit_to_location
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM HZ_PARTY_SITES PS
WHERE PS.LOCATION_ID = p_location_id
AND PS.PARTY_ID = -1);
SELECT 'Y'
INTO l_loc_assg_exists
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM HZ_LOC_ASSIGNMENTS
WHERE LOCATION_ID = p_location_id);
HZ_GNR_PKG.delete_gnr(
p_locId => p_location_id,
p_locTbl => 'HZ_LOCATIONS',
x_status => x_return_status);
END do_update_loc_assignment;
* PROCEDURE update_loc_assignment
*
* DESCRIPTION
* Updates location assignment.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_location_id Location ID.
* p_lock_flag Lock record or not. Default is FND_API.G_TRUE.
* p_created_by_module Module name which creates this record.
* p_application_id Application ID which creates this record.
* IN/OUT:
* 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).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
* x_loc_id Location assignment ID.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE update_loc_assignment(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_location_id IN NUMBER,
p_lock_flag IN VARCHAR2 :=FND_API.G_TRUE,
p_created_by_module IN VARCHAR2,
p_application_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loc_id OUT NOCOPY NUMBER
) IS
l_org_id VARCHAR2(2000);
update_loc_assignment(
p_init_msg_list,
p_location_id ,
p_lock_flag,
p_created_by_module,
p_application_id,
x_return_status,
x_msg_count,
x_msg_data ,
x_loc_id,
l_org_id
);
END update_loc_assignment;
* PROCEDURE update_loc_assignment
*
* DESCRIPTION
* Updates location assignment.
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_location_id Location ID.
* p_lock_flag Lock record or not. Default is FND_API.G_TRUE.
* p_created_by_module Module name which creates this record.
* p_application_id Application ID which creates this record.
* IN/OUT:
* 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).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
* x_loc_id Location assignment ID.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 09-18-2003 P.Suresh o Created.
*
*/
PROCEDURE update_loc_assignment(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_location_id IN NUMBER,
p_lock_flag IN VARCHAR2 :=FND_API.G_TRUE,
p_created_by_module IN VARCHAR2,
p_application_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loc_id OUT NOCOPY NUMBER ,
x_org_id OUT NOCOPY VARCHAR2
) IS
l_location_id NUMBER := p_location_id;
SAVEPOINT update_loc_assignment;
do_update_loc_assignment( l_location_id,
'N',
l_addr_val_status,
l_addr_warn_msg,
x_return_status,
p_lock_flag);
ROLLBACK TO update_loc_assignment;
ROLLBACK TO update_loc_assignment;
ROLLBACK TO update_loc_assignment;
ROLLBACK TO update_loc_assignment;
END update_loc_assignment;
* PROCEDURE update_loc_assignment
*
* DESCRIPTION
* Updates location assignment(overloaded procedure with address validation).
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_init_msg_list Initialize message stack if it is set to
* FND_API.G_TRUE. Default is FND_API.G_FALSE.
* p_location_id Location ID.
* p_lock_flag Lock record or not. Default is FND_API.G_TRUE.
* p_created_by_module Module name which creates this record.
* p_application_id Application ID which creates this record.
* IN/OUT:
* 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).
* x_msg_count Number of messages in message stack.
* x_msg_data Message text if x_msg_count is 1.
* x_loc_id Location assignment ID.
*
* NOTES
*
* MODIFICATION HISTORY
*
* 09-18-2003 P.Suresh o Created.
*
*/
PROCEDURE update_loc_assignment(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_location_id IN NUMBER,
p_lock_flag IN VARCHAR2 := FND_API.G_TRUE,
p_do_addr_val IN VARCHAR2,
x_addr_val_status OUT NOCOPY VARCHAR2,
x_addr_warn_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_location_id NUMBER := p_location_id;
SAVEPOINT update_loc_assignment;
do_update_loc_assignment( l_location_id,
p_do_addr_val,
x_addr_val_status,
x_addr_warn_msg,
x_return_status,
p_lock_flag);
ROLLBACK TO update_loc_assignment;
ROLLBACK TO update_loc_assignment;
ROLLBACK TO update_loc_assignment;
ROLLBACK TO update_loc_assignment;
END update_loc_assignment;