[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select address_req_id from pos_cont_addr_requests
WHERE address_req_id = p_request_rec.address_request_id for update nowait;
select address_req_id from pos_address_notes
WHERE address_req_id = p_request_rec.address_request_id for update nowait;
select address_request_id into l_lock_id from pos_address_requests
WHERE address_request_id = p_request_rec.address_request_id for update nowait;
UPDATE pos_address_notes
SET party_site_id = l_party_site_id
WHERE address_req_id = p_request_rec.address_request_id;
UPDATE pos_address_requests
SET party_site_id = l_party_site_id,
request_status = 'APPROVED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE address_request_id = p_request_rec.address_request_id;
UPDATE pos_cont_addr_requests
SET party_site_id = l_party_site_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE address_req_id = p_request_rec.address_request_id
AND request_status = 'PENDING'
AND party_site_id IS NULL;
PROCEDURE approve_update_address_req
(p_request_rec IN pos_address_requests%ROWTYPE,
p_vendor_id IN NUMBER,
p_vendor_party_id IN NUMBER,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2
)
IS
l_obj_ver hz_locations.object_version_number%TYPE;
SELECT object_version_number,location_id
from hz_locations
where location_id =
(SELECT location_id
FROM hz_party_sites
WHERE party_site_id = p_request_rec.party_site_id
) FOR UPDATE;
select object_version_number, party_site_name
from hz_party_sites
where party_site_id = p_request_rec.party_site_id FOR UPDATE;
savepoint approve_update_address_req;
select address_request_id into l_lock_id from pos_address_requests
WHERE address_request_id = p_request_rec.address_request_id for update nowait;
fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_address_req' , ' Cannot lock the rows ');
rollback to approve_update_address_req;
rollback to approve_update_address_req;
pos_supplier_address_pkg.update_supplier_address
(p_vendor_id => p_vendor_id,
p_vendor_party_id => p_vendor_party_id,
p_party_site_id => p_request_rec.party_site_id,
p_party_site_name => p_request_rec.party_site_name,
p_address_line1 => p_request_rec.address_line1,
p_address_line2 => p_request_rec.address_line2,
p_address_line3 => p_request_rec.address_line3,
p_address_line4 => p_request_rec.address_line4,
p_country => p_request_rec.country,
p_city => p_request_rec.city,
p_state => p_request_rec.state,
p_province => p_request_rec.province,
p_postal_code => p_request_rec.postal_code,
p_county => p_request_rec.county,
p_rfq_flag => p_request_rec.rfq_flag,
p_pur_flag => p_request_rec.pur_flag,
p_pay_flag => p_request_rec.pay_flag,
p_primary_pay_flag => p_request_rec.primary_pay_flag,
p_phone_area_code => p_request_rec.phone_area_code,
p_phone_number => p_request_rec.phone_number,
p_phone_extension => p_request_rec.phone_extension,
p_fax_area_code => p_request_rec.fax_area_code,
p_fax_number => p_request_rec.fax_number,
p_email_address => p_request_rec.email_address,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE pos_address_requests
SET request_status = 'APPROVED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE address_request_id = p_request_rec.address_request_id;
fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_address_req' , x_msg_data);
rollback to approve_update_address_req;
END approve_update_address_req;
SELECT pcar.cont_addr_request_id,
pcar.request_type,
pv.party_id,
pv.vendor_id,
pcr.contact_party_id,
pcar.party_site_id
FROM pos_cont_addr_requests pcar,
pos_contact_requests pcr,
pos_supplier_mappings psm,
po_vendors pv
WHERE pcar.contact_req_id = p_contact_request_id
AND pcar.request_status = 'PENDING'
AND pcar.party_site_id IS NOT NULL
AND pcar.mapping_id = psm.mapping_id
AND psm.vendor_id = pv.vendor_id
AND pcar.contact_req_id = pcr.contact_request_id;
ELSIF l_rec.request_type = 'DELETE' THEN
pos_supplier_address_pkg.unassign_address_to_contact
(p_contact_party_id => l_rec.contact_party_id,
p_org_party_site_id => l_rec.party_site_id,
p_vendor_id => l_rec.vendor_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE pos_cont_addr_requests
SET request_status = 'APPROVED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE cont_addr_request_id IN
(SELECT pcar.cont_addr_request_id
FROM pos_cont_addr_requests pcar,
pos_contact_requests pcr,
pos_supplier_mappings psm,
po_vendors pv
WHERE pcar.contact_req_id = p_contact_request_id
AND pcar.request_status = 'PENDING'
AND pcar.mapping_id = psm.mapping_id
AND psm.vendor_id = pv.vendor_id
AND pcar.contact_req_id = pcr.contact_request_id
);
select contact_req_id from pos_cont_addr_requests
WHERE contact_req_id = p_request_rec.contact_request_id for update nowait;
select contact_request_id into l_lock_id from pos_contact_requests
where contact_request_id = p_request_rec.contact_request_id for update nowait;
l_step := 'update pos_contact_request with ids';
UPDATE pos_contact_requests
SET contact_party_id = l_person_party_id,
request_status = 'APPROVED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE contact_request_id = p_request_rec.contact_request_id;
UPDATE pos_cont_addr_requests
SET contact_party_id = l_person_party_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE contact_req_id = p_request_rec.contact_request_id
AND contact_party_id IS NULL
AND request_status = 'PENDING';
PROCEDURE approve_update_contact_req
(p_request_rec IN pos_contact_requests%ROWTYPE,
p_vendor_party_id IN NUMBER,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2
)
IS
l_lock_id number;
SAVEPOINT approve_update_contact_req_sp;
select contact_request_id into l_lock_id from pos_contact_requests
where contact_request_id = p_request_rec.contact_request_id for update nowait;
fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_contact_req' , ' Cannot lock the rows ');
IF p_request_rec.request_type <> 'UPDATE' AND
p_request_rec.request_type <> 'DELETE' THEN
x_return_status := fnd_api.g_ret_sts_error;
x_msg_data := 'request_type not UPDATED for contact request id '
|| p_request_rec.contact_request_id;
ROLLBACK TO approve_update_contact_req_sp;
ROLLBACK TO approve_update_contact_req_sp;
ROLLBACK TO approve_update_contact_req_sp;
IF p_request_rec.request_type = 'DELETE' THEN
p_request_inactive_date := sysdate;
pos_supp_contact_pkg.update_supplier_contact
(p_contact_party_id => p_request_rec.contact_party_id,
p_vendor_party_id => p_vendor_party_id,
p_first_name => p_request_rec.first_name,
p_last_name => p_request_rec.last_name,
p_middle_name => p_request_rec.middle_name,
p_contact_title => p_request_rec.contact_title,
p_job_title => p_request_rec.job_title,
p_phone_area_code => p_request_rec.phone_area_code,
p_phone_number => p_request_rec.phone_number,
p_phone_extension => p_request_rec.phone_extension,
p_fax_area_code => p_request_rec.fax_area_code,
p_fax_number => p_request_rec.fax_number,
p_email_address => p_request_rec.email_address,
p_inactive_date => p_request_inactive_date,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO approve_update_contact_req_sp;
UPDATE pos_contact_requests
SET request_status = 'APPROVED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE contact_request_id = p_request_rec.contact_request_id;
ROLLBACK TO approve_update_contact_req_sp;
ROLLBACK TO approve_update_contact_req_sp;
pos_log.log_sqlerrm('POSCONTB', 'in approve_update_contact_req');
END approve_update_contact_req;
select bus_class_request_id into l_lock_id from pos_bus_class_reqs
WHERE bus_class_request_id = p_request_rec.bus_class_request_id for update nowait;
INSERT INTO pos_bus_class_attr
( classification_id
, party_id
, lookup_type
, lookup_code
, start_date_active
, end_date_active
, status
, ext_attr_1
, expiration_date
, certificate_number
, certifying_agency
, class_status
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, vendor_id
)
VALUES
(
pos_bus_class_attr_s.NEXTVAL
, p_vendor_party_id
, p_request_rec.lookup_type
, p_request_rec.lookup_code
, Sysdate
, NULL
, 'A'
, p_request_rec.ext_attr_1
, p_request_rec.expiration_date
, p_request_rec.certification_no
, p_request_rec.certification_agency
, 'APPROVED'
, fnd_global.user_id
, Sysdate
, fnd_global.user_id
, Sysdate
, fnd_global.login_id
, p_vendor_id
);
UPDATE pos_bus_class_reqs
SET request_status = 'APPROVED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE bus_class_request_id = p_request_rec.bus_class_request_id;
PROCEDURE approve_update_bus_class_req
(p_request_rec IN pos_bus_class_reqs%ROWTYPE,
p_vendor_id IN NUMBER,
p_vendor_party_id IN NUMBER, x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2
)
IS
l_lock_id NUMBER;
savepoint approve_update_bus_class_req;
select bus_class_request_id into l_lock_id from pos_bus_class_reqs
WHERE bus_class_request_id = p_request_rec.bus_class_request_id for update nowait;
select classification_id into l_lock_id from pos_bus_class_attr
WHERE classification_id = p_request_rec.classification_id for update nowait;
fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_bus_class_req' , ' Cannot lock the rows ');
UPDATE pos_bus_class_attr
SET ext_attr_1 = p_request_rec.ext_attr_1
, expiration_date = p_request_rec.expiration_date
, certificate_number = p_request_rec.certification_no
, certifying_agency = p_request_rec.certification_agency
, last_updated_by = fnd_global.user_id
, last_update_date = Sysdate
, last_update_login = fnd_global.login_id
WHERE classification_id = p_request_rec.classification_id;
UPDATE pos_bus_class_reqs
SET request_status = 'APPROVED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE bus_class_request_id = p_request_rec.bus_class_request_id;
fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_bus_class_req' , x_msg_data);
rollback to approve_update_bus_class_req;
END approve_update_bus_class_req;
SELECT *
FROM pos_address_requests
WHERE address_request_id = p_request_id FOR UPDATE NOWAIT;
SELECT vendor_id, party_id
FROM pos_supplier_mappings psm
WHERE mapping_id = l_rec.mapping_id;
ELSIF l_rec.request_type = 'UPDATE' THEN
approve_update_address_req
(p_request_rec => l_rec,
p_vendor_id => l_rec2.vendor_id,
p_vendor_party_id => l_rec2.party_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ELSIF l_rec.request_type = 'DELETE' then
UPDATE pos_address_requests
SET request_status = 'APPROVED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE address_request_id = p_request_id;
SELECT *
FROM pos_contact_requests
WHERE contact_request_id = p_request_id FOR UPDATE NOWAIT;
SELECT vendor_id, party_id
FROM pos_supplier_mappings psm
WHERE mapping_id = l_rec.mapping_id;
ELSIF l_rec.request_type = 'UPDATE' OR
l_rec.request_type = 'DELETE' THEN
approve_update_contact_req
(p_request_rec => l_rec,
p_vendor_party_id => l_rec2.party_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT *
FROM pos_bus_class_reqs
WHERE bus_class_request_id = p_request_id FOR UPDATE NOWAIT;
SELECT vendor_id, party_id
FROM pos_supplier_mappings psm
WHERE mapping_id = l_rec.mapping_id;
ELSIF l_rec.request_type = 'UPDATE' THEN
approve_update_bus_class_req
(p_request_rec => l_rec,
p_vendor_id => l_rec2.vendor_id,
p_vendor_party_id => l_rec2.party_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT *
FROM pos_product_service_requests
WHERE ps_request_id = p_request_id FOR UPDATE NOWAIT;
SELECT vendor_id, party_id
FROM pos_supplier_mappings psm
WHERE mapping_id = l_rec.mapping_id;
INSERT INTO pos_sup_products_services
(
classification_id
, vendor_id
, segment1
, segment2
, segment3
, segment4
, segment5
, segment6
, segment7
, segment8
, segment9
, segment10
, segment11
, segment12
, segment13
, segment14
, segment15
, segment16
, segment17
, segment18
, segment19
, segment20
, status
, segment_definition
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES
(
pos_sup_products_services_s.NEXTVAL
, l_rec2.vendor_id
, l_rec.segment1
, l_rec.segment2
, l_rec.segment3
, l_rec.segment4
, l_rec.segment5
, l_rec.segment6
, l_rec.segment7
, l_rec.segment8
, l_rec.segment9
, l_rec.segment10
, l_rec.segment11
, l_rec.segment12
, l_rec.segment13
, l_rec.segment14
, l_rec.segment15
, l_rec.segment16
, l_rec.segment17
, l_rec.segment18
, l_rec.segment19
, l_rec.segment20
, 'A'
, l_rec.segment_definition
, fnd_global.user_id
, Sysdate
, fnd_global.user_id
, Sysdate
, fnd_global.login_id
);
UPDATE pos_product_service_requests
SET request_status = 'APPROVED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE ps_request_id = p_request_id;
select address_req_id from pos_cont_addr_requests
WHERE address_req_id = p_request_id for update nowait;
select address_request_id into l_lock_id from pos_address_requests
WHERE address_request_id = p_request_id for update nowait;
UPDATE pos_address_requests
SET request_status = 'REJECTED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE address_request_id = p_request_id;
UPDATE pos_cont_addr_requests
SET request_status = 'REJECTED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE address_req_id = p_request_id;
select contact_request_id into l_lock_id from pos_contact_requests
WHERE contact_request_id = p_request_id for update nowait;
update pos_cont_addr_requests
set request_status = 'REJECTED',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where contact_req_id = p_request_id;
UPDATE pos_contact_requests
SET request_status = 'REJECTED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE contact_request_id = p_request_id;
select bus_class_request_id into l_lock_id from pos_bus_class_reqs
WHERE bus_class_request_id = p_request_id for update nowait;
UPDATE pos_bus_class_reqs
SET request_status = 'REJECTED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE bus_class_request_id = p_request_id;
select ps_request_id into l_lock_id from pos_product_service_requests
WHERE ps_request_id = p_request_id for update nowait;
UPDATE pos_product_service_requests
SET request_status = 'REJECTED',
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE ps_request_id = p_request_id;
select address_request_id into l_lock_id from pos_address_requests
WHERE address_request_id = p_req_id_tbl(i) for update nowait;
select contact_request_id into l_lock_id from pos_contact_requests
where contact_request_id = p_req_id_tbl(i) for update nowait;
select bus_class_request_id into l_lock_id from pos_bus_class_reqs
where bus_class_request_id = p_req_id_tbl(i) for update nowait;
select ps_request_id into l_lock_id from pos_product_service_requests
WHERE ps_request_id = p_req_id_tbl(i) for update nowait;
select address_request_id into l_lock_id from pos_address_requests
WHERE address_request_id = p_req_id_tbl(i) for update nowait;
select contact_request_id into l_lock_id from pos_contact_requests
where contact_request_id = p_req_id_tbl(i) for update nowait;
/* The Below query selects the details of the contact request like first name,last name,email,phone number */
SELECT
PCR.FIRST_NAME,
PCR.LAST_NAME,
PCR.EMAIL_ADDRESS,
PCR.PHONE_AREA_CODE,
PCR.PHONE_NUMBER,
PCR.PHONE_EXTENSION,
PCR.CONTACT_PARTY_ID,
PSM.PARTY_ID
INTO
l_fName,
l_lName,
l_eMail,
l_phoneAreaCode,
l_phone,
l_phoneExtn,
l_contactPartyId,
l_suppPartyId
FROM
POS_CONTACT_REQUESTS PCR,
POS_SUPPLIER_MAPPINGS PSM
WHERE CONTACT_REQUEST_ID=p_req_id_tbl(i)
AND PCR.MAPPING_ID=PSM.MAPPING_ID;
SELECT Count(*) INTO l_duplicateRow
FROM HZ_PARTIES HPC,
HZ_CONTACT_POINTS HCPP,
HZ_CONTACT_POINTS HCPE,
HZ_RELATIONSHIPS HR
WHERE HR.SUBJECT_ID = l_suppPartyId
AND HCPP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCPP.OWNER_TABLE_ID (+) = HR.PARTY_ID
AND HCPP.PHONE_LINE_TYPE (+) = 'GEN'
AND HCPP.CONTACT_POINT_TYPE (+) = 'PHONE'
AND HCPE.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND HCPE.OWNER_TABLE_ID (+) = HR.PARTY_ID
AND HCPE.CONTACT_POINT_TYPE (+) = 'EMAIL'
AND HR.OBJECT_ID = HPC.PARTY_ID
AND HR.SUBJECT_TYPE = 'ORGANIZATION'
AND HR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HR.OBJECT_TYPE = 'PERSON'
AND HR.RELATIONSHIP_CODE = 'CONTACT'
AND HR.DIRECTIONAL_FLAG = 'B'
AND HR.RELATIONSHIP_TYPE = 'CONTACT'
AND ((HPC.PERSON_FIRST_NAME IS NULL
AND l_fName IS NULL )
OR UPPER(HPC.PERSON_FIRST_NAME) = Upper(l_fName))
AND ((HPC.PERSON_LAST_NAME IS NULL
AND l_lName IS NULL )
OR UPPER(HPC.PERSON_LAST_NAME) = Upper(l_lName))
AND ((HCPP.PHONE_AREA_CODE IS NULL
AND l_phoneAreacODE IS NULL )
OR UPPER(HCPP.PHONE_AREA_CODE) = Upper(l_phoneAreacODE))
AND ((HCPP.PHONE_NUMBER IS NULL
AND l_phone IS NULL )
OR UPPER(HCPP.PHONE_NUMBER) = Upper(l_phone))
AND ((HCPP.PHONE_EXTENSION IS NULL
AND l_phoneExtn IS NULL )
OR UPPER(HCPP.PHONE_EXTENSION) = Upper(l_phoneExtn))
AND ((HCPE.EMAIL_ADDRESS IS NULL
AND l_eMail IS NULL )
OR UPPER(HCPE.EMAIL_ADDRESS) = Upper(l_eMail))
AND (l_contactPartyId IS NULL
OR l_contactPartyId <> HPC.PARTY_ID)
AND ROWNUM < 2;
select bus_class_request_id into l_lock_id from pos_bus_class_reqs
where bus_class_request_id = p_req_id_tbl(i) for update nowait;
select ps_request_id into l_lock_id from pos_product_service_requests
WHERE ps_request_id = p_req_id_tbl(i) for update nowait;
PROCEDURE approve_update_mult_bc_reqs
(
p_pos_bus_rec_tbl IN pos_bus_rec_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_req_id_tbl po_tbl_number;
savepoint approve_update_mult_bc_reqs;
select BUS_CLASS_REQUEST_ID into l_lock_id from pos_bus_class_reqs
WHERE BUS_CLASS_REQUEST_ID = p_pos_bus_rec_tbl(i).BUS_CLASS_REQUEST_ID for update nowait;
fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_mult_bc_reqs' , ' Cannot lock the rows ');
UPDATE pos_bus_class_reqs
SET CERTIFICATION_NO = p_pos_bus_rec_tbl(i).CERTIFICATION_NO,
CERTIFICATION_AGENCY = p_pos_bus_rec_tbl(i).CERTIFICATION_AGENCY,
EXPIRATION_DATE = p_pos_bus_rec_tbl(i).EXPIRATION_DATE
WHERE BUS_CLASS_REQUEST_ID = p_pos_bus_rec_tbl(i).BUS_CLASS_REQUEST_ID;
fnd_log.string(fnd_log.level_statement, g_module || '.' || 'approve_update_mult_bc_reqs' , x_msg_data);
rollback to approve_update_mult_bc_reqs;
END approve_update_mult_bc_reqs;
select ADDRESS_REQUEST_ID into l_lock_id from POS_ADDRESS_REQUESTS
WHERE ADDRESS_REQUEST_ID = p_request_id for update nowait;
UPDATE POS_ADDRESS_REQUESTS
SET PARTY_SITE_NAME = p_party_site_name,
COUNTRY = p_country,
ADDRESS_LINE1 = p_address_line1,
ADDRESS_LINE2 = p_address_line2,
ADDRESS_LINE3 = p_address_line3,
ADDRESS_LINE4 = p_address_line4,
CITY = p_city,
COUNTY = p_county,
STATE = p_state,
PROVINCE = p_province,
POSTAL_CODE = p_postal_code,
PHONE_AREA_CODE = p_phone_area_code,
PHONE_NUMBER = p_phone_number,
FAX_AREA_CODE = p_fax_area_code,
FAX_NUMBER = p_fax_number,
EMAIL_ADDRESS = p_email_address,
RFQ_FLAG = p_rfq_flag,
PAY_FLAG = p_pay_flag,
PUR_FLAG = p_pur_flag,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE ADDRESS_REQUEST_ID = p_request_id;
select party_site_id
into l_party_site_id
from pos_address_requests
where address_request_id = p_request_id;
select CONTACT_REQUEST_ID into l_lock_id from POS_CONTACT_REQUESTS
WHERE CONTACT_REQUEST_ID = p_request_id for update nowait;
UPDATE POS_CONTACT_REQUESTS
SET CONTACT_TITLE = p_contact_title,
FIRST_NAME = p_first_name,
MIDDLE_NAME = p_middle_name,
LAST_NAME = p_last_name,
JOB_TITLE = p_job_title,
EMAIL_ADDRESS = p_email_address,
PHONE_AREA_CODE = p_phone_area_code,
PHONE_NUMBER = p_phone_number,
PHONE_EXTENSION = p_phone_extension,
FAX_AREA_CODE = p_fax_area_code,
FAX_NUMBER = p_fax_number,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE CONTACT_REQUEST_ID = p_request_id;
select CONT_ADDR_REQUEST_ID into l_lock_id from pos_cont_addr_requests
WHERE PARTY_SITE_ID = p_req_id_tbl(i)
and REQUEST_STATUS = 'PENDING'
and CONTACT_REQ_ID = p_cont_req_id for update nowait;
UPDATE pos_cont_addr_requests
SET request_status = 'REJECTED'
WHERE PARTY_SITE_ID = p_req_id_tbl(i)
and REQUEST_STATUS = 'PENDING'
and CONTACT_REQ_ID = p_cont_req_id;
select CONTACT_REQUEST_ID into l_lock_id from POS_CONTACT_REQUESTS
WHERE CONTACT_REQUEST_ID = p_request_id for update nowait;
UPDATE POS_CONTACT_REQUESTS
SET CONTACT_TITLE = p_contact_title,
FIRST_NAME = p_first_name,
MIDDLE_NAME = p_middle_name,
LAST_NAME = p_last_name,
JOB_TITLE = p_job_title,
EMAIL_ADDRESS = p_email_address,
PHONE_AREA_CODE = p_phone_area_code,
PHONE_NUMBER = p_phone_number,
PHONE_EXTENSION = p_phone_extension,
FAX_AREA_CODE = p_fax_area_code,
FAX_NUMBER = p_fax_number,
CREATE_USER_ACCOUNT = p_create_user_acc
WHERE CONTACT_REQUEST_ID = p_request_id;
SELECT fu.user_id,fu.PERSON_PARTY_ID
into x_user_id, x_cont_party_id
FROM fnd_user fu, pos_contact_requests pcr
WHERE pcr.CONTACT_PARTY_ID = fu.PERSON_PARTY_ID
and pcr.contact_request_id = p_request_id;
select pcr.CONTACT_PARTY_ID
into x_cont_party_id
from pos_contact_requests pcr
where pcr.contact_request_id = p_request_id;
PROCEDURE update_addr_req_status
(p_request_id IN NUMBER,
p_party_site_id IN NUMBER,
p_req_status IN VARCHAR2,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2
)
IS
l_lock_id number;
select address_req_id from pos_cont_addr_requests
where address_req_id = p_request_id for update nowait;
savepoint update_addr_req_status;
select address_request_id into l_lock_id from pos_address_requests
WHERE address_request_id = p_request_id for update nowait;
fnd_log.string(fnd_log.level_statement, g_module || '.' || 'update_addr_req_status' , ' Cannot lock the rows ');
UPDATE pos_address_requests
SET request_status = p_req_status,
PARTY_SITE_ID = p_party_site_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE address_request_id = p_request_id;
UPDATE pos_cont_addr_requests
SET party_site_id = p_party_site_id,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE address_req_id = p_request_id
AND request_status = 'PENDING'
AND party_site_id IS NULL;
fnd_log.string(fnd_log.level_statement, g_module || '.' || 'update_addr_req_status' , x_msg_data);
rollback to update_addr_req_status;
END update_addr_req_status;
select cont_addr_request_id
from pos_cont_addr_requests
where mapping_id = p_mapping_id
and nvl(contact_party_id, -1) = nvl(p_cont_party_id, -1)
and nvl(contact_req_id, -1) = nvl(p_cont_req_id, -1)
and nvl(ADDRESS_REQ_ID, -1) = nvl(p_addr_req_id, -1)
and request_type = p_req_type
and request_status = 'PENDING'
and nvl(party_site_id, -1) in
(
select party_site_id
from hz_party_sites
where location_id in
(
select location_id
from hz_party_sites
where party_site_id = nvl(p_party_site_id, -1)
)
) FOR UPDATE NOWAIT;
if(p_request_type = 'DELETE') then
l_rec_req_type := 'ADD';
l_rec_req_type := 'DELETE';
update pos_cont_addr_requests
set request_status = 'DELETED'
where cont_addr_request_id = l_req_rec_exists_rec.cont_addr_request_id;
select contact_request_id
into l_cont_req_id
from pos_contact_requests
where contact_party_id = p_contact_party_id
and request_status = 'PENDING';