[Home] [Help]
269: pv.vendor_id,
270: pcr.contact_party_id,
271: pcar.party_site_id
272: FROM pos_cont_addr_requests pcar,
273: pos_contact_requests pcr,
274: pos_supplier_mappings psm,
275: po_vendors pv
276: WHERE pcar.contact_req_id = p_contact_request_id
277: AND pcar.request_status = 'PENDING'
333: last_update_login = fnd_global.login_id
334: WHERE cont_addr_request_id IN
335: (SELECT pcar.cont_addr_request_id
336: FROM pos_cont_addr_requests pcar,
337: pos_contact_requests pcr,
338: pos_supplier_mappings psm,
339: po_vendors pv
340: WHERE pcar.contact_req_id = p_contact_request_id
341: AND pcar.request_status = 'PENDING'
358:
359: END approve_cont_addr_requests;
360:
361: PROCEDURE approve_new_contact_req
362: (p_request_rec IN pos_contact_requests%ROWTYPE,
363: p_vendor_id IN NUMBER,
364: p_vendor_party_id IN NUMBER,
365: p_user_name IN VARCHAR2,
366: x_return_status OUT nocopy VARCHAR2,
385:
386: -- Lock the rows: This is done as part of ECO 5209555
387: BEGIN
388:
389: select contact_request_id into l_lock_id from pos_contact_requests
390: where contact_request_id = p_request_rec.contact_request_id for update nowait;
391:
392: open l_cont_addr_cur;
393: fetch l_cont_addr_cur into l_cont_addr_rec;
481: , l_step
482: );
483: END IF;
484:
485: UPDATE pos_contact_requests
486: SET contact_party_id = l_person_party_id,
487: request_status = 'APPROVED',
488: last_update_date = Sysdate,
489: last_updated_by = fnd_global.user_id,
522:
523: END approve_new_contact_req;
524:
525: PROCEDURE approve_update_contact_req
526: (p_request_rec IN pos_contact_requests%ROWTYPE,
527: p_vendor_party_id IN NUMBER,
528: x_return_status OUT nocopy VARCHAR2,
529: x_msg_count OUT nocopy NUMBER,
530: x_msg_data OUT nocopy VARCHAR2
537:
538: -- Lock the rows: This is done as part of ECO 5209555
539: BEGIN
540:
541: select contact_request_id into l_lock_id from pos_contact_requests
542: where contact_request_id = p_request_rec.contact_request_id for update nowait;
543:
544: EXCEPTION
545:
610: ROLLBACK TO approve_update_contact_req_sp;
611: RETURN;
612: END IF;
613:
614: UPDATE pos_contact_requests
615: SET request_status = 'APPROVED',
616: last_update_date = Sysdate,
617: last_updated_by = fnd_global.user_id,
618: last_update_login = fnd_global.login_id
948: )
949: IS
950: CURSOR l_cur IS
951: SELECT *
952: FROM pos_contact_requests
953: WHERE contact_request_id = p_request_id FOR UPDATE NOWAIT;
954: -- ECO 5209555 Add the nowait clause
955:
956: l_rec l_cur%ROWTYPE;
1405:
1406: savepoint reject_contact_req;
1407: -- Lock the rows: This is done as part of ECO 5209555
1408: BEGIN
1409: select contact_request_id into l_lock_id from pos_contact_requests
1410: WHERE contact_request_id = p_request_id for update nowait;
1411: EXCEPTION
1412:
1413: WHEN OTHERS THEN
1426: last_updated_by = fnd_global.user_id,
1427: last_update_login = fnd_global.login_id
1428: where contact_req_id = p_request_id;
1429:
1430: UPDATE pos_contact_requests
1431: SET request_status = 'REJECTED',
1432: last_update_date = Sysdate,
1433: last_updated_by = fnd_global.user_id,
1434: last_update_login = fnd_global.login_id
1614: -- Lock the rows: This is done as part of ECO 5209555
1615: BEGIN
1616:
1617: for i in 1..p_req_id_tbl.COUNT LOOP
1618: select contact_request_id into l_lock_id from pos_contact_requests
1619: where contact_request_id = p_req_id_tbl(i) for update nowait;
1620:
1621: END LOOP;
1622:
1866: -- Lock the rows: This is done as part of ECO 5209555
1867: BEGIN
1868:
1869: for i in 1..p_req_id_tbl.COUNT LOOP
1870: select contact_request_id into l_lock_id from pos_contact_requests
1871: where contact_request_id = p_req_id_tbl(i) for update nowait;
1872:
1873: END LOOP;
1874:
1906: l_phoneExtn,
1907: l_contactPartyId,
1908: l_suppPartyId
1909: FROM
1910: POS_CONTACT_REQUESTS PCR,
1911: POS_SUPPLIER_MAPPINGS PSM
1912: WHERE CONTACT_REQUEST_ID=p_req_id_tbl(i)
1913: AND PCR.MAPPING_ID=PSM.MAPPING_ID;
1914:
2390: savepoint chg_contact_req_approval;
2391:
2392: -- Lock the rows: This is done as part of ECO 5209555
2393: BEGIN
2394: select CONTACT_REQUEST_ID into l_lock_id from POS_CONTACT_REQUESTS
2395: WHERE CONTACT_REQUEST_ID = p_request_id for update nowait;
2396: EXCEPTION
2397:
2398: WHEN OTHERS THEN
2406:
2407: x_return_status := FND_API.G_RET_STS_SUCCESS;
2408:
2409:
2410: UPDATE POS_CONTACT_REQUESTS
2411: SET CONTACT_TITLE = p_contact_title,
2412: FIRST_NAME = p_first_name,
2413: MIDDLE_NAME = p_middle_name,
2414: LAST_NAME = p_last_name,
2534: savepoint new_contact_req_approval;
2535:
2536: -- Lock the rows: This is done as part of ECO 5209555
2537: BEGIN
2538: select CONTACT_REQUEST_ID into l_lock_id from POS_CONTACT_REQUESTS
2539: WHERE CONTACT_REQUEST_ID = p_request_id for update nowait;
2540: EXCEPTION
2541:
2542: WHEN OTHERS THEN
2550:
2551: x_return_status := FND_API.G_RET_STS_SUCCESS;
2552: x_user_id := -1 ;
2553: x_cont_party_id := -1 ;
2554: UPDATE POS_CONTACT_REQUESTS
2555: SET CONTACT_TITLE = p_contact_title,
2556: FIRST_NAME = p_first_name,
2557: MIDDLE_NAME = p_middle_name,
2558: LAST_NAME = p_last_name,
2584: if p_create_user_acc = 'Y' then
2585:
2586: SELECT fu.user_id,fu.PERSON_PARTY_ID
2587: into x_user_id, x_cont_party_id
2588: FROM fnd_user fu, pos_contact_requests pcr
2589: WHERE pcr.CONTACT_PARTY_ID = fu.PERSON_PARTY_ID
2590: and pcr.contact_request_id = p_request_id;
2591:
2592: ELSE
2591:
2592: ELSE
2593: select pcr.CONTACT_PARTY_ID
2594: into x_cont_party_id
2595: from pos_contact_requests pcr
2596: where pcr.contact_request_id = p_request_id;
2597:
2598: end if;
2599:
2826: l_cont_req_id NUMBER;
2827: BEGIN
2828: select contact_request_id
2829: into l_cont_req_id
2830: from pos_contact_requests
2831: where contact_party_id = p_contact_party_id
2832: and request_status = 'PENDING';
2833:
2834: return l_cont_req_id ;