[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;
486: , l_step
487: );
488: END IF;
489:
490: UPDATE pos_contact_requests
491: SET contact_party_id = l_person_party_id,
492: request_status = 'APPROVED',
493: last_update_date = Sysdate,
494: last_updated_by = fnd_global.user_id,
527:
528: END approve_new_contact_req;
529:
530: PROCEDURE approve_update_contact_req
531: (p_request_rec IN pos_contact_requests%ROWTYPE,
532: p_vendor_party_id IN NUMBER,
533: x_return_status OUT nocopy VARCHAR2,
534: x_msg_count OUT nocopy NUMBER,
535: x_msg_data OUT nocopy VARCHAR2
542:
543: -- Lock the rows: This is done as part of ECO 5209555
544: BEGIN
545:
546: select contact_request_id into l_lock_id from pos_contact_requests
547: where contact_request_id = p_request_rec.contact_request_id for update nowait;
548:
549: EXCEPTION
550:
620: ROLLBACK TO approve_update_contact_req_sp;
621: RETURN;
622: END IF;
623:
624: UPDATE pos_contact_requests
625: SET request_status = 'APPROVED',
626: last_update_date = Sysdate,
627: last_updated_by = fnd_global.user_id,
628: last_update_login = fnd_global.login_id
958: )
959: IS
960: CURSOR l_cur IS
961: SELECT *
962: FROM pos_contact_requests
963: WHERE contact_request_id = p_request_id FOR UPDATE NOWAIT;
964: -- ECO 5209555 Add the nowait clause
965:
966: l_rec l_cur%ROWTYPE;
1089: ROLLBACK TO approve_new_contact_req_sp;
1090: RETURN;
1091: END IF;
1092:
1093: UPDATE pos_contact_requests
1094: SET request_status = 'APPROVED',
1095: last_update_date = Sysdate,
1096: last_updated_by = fnd_global.user_id,
1097: last_update_login = fnd_global.login_id
1459:
1460: savepoint reject_contact_req;
1461: -- Lock the rows: This is done as part of ECO 5209555
1462: BEGIN
1463: select contact_request_id into l_lock_id from pos_contact_requests
1464: WHERE contact_request_id = p_request_id for update nowait;
1465: EXCEPTION
1466:
1467: WHEN OTHERS THEN
1480: last_updated_by = fnd_global.user_id,
1481: last_update_login = fnd_global.login_id
1482: where contact_req_id = p_request_id;
1483:
1484: UPDATE pos_contact_requests
1485: SET request_status = 'REJECTED',
1486: last_update_date = Sysdate,
1487: last_updated_by = fnd_global.user_id,
1488: last_update_login = fnd_global.login_id
1668: -- Lock the rows: This is done as part of ECO 5209555
1669: BEGIN
1670:
1671: for i in 1..p_req_id_tbl.COUNT LOOP
1672: select contact_request_id into l_lock_id from pos_contact_requests
1673: where contact_request_id = p_req_id_tbl(i) for update nowait;
1674:
1675: END LOOP;
1676:
1920: -- Lock the rows: This is done as part of ECO 5209555
1921: BEGIN
1922:
1923: for i in 1..p_req_id_tbl.COUNT LOOP
1924: select contact_request_id into l_lock_id from pos_contact_requests
1925: where contact_request_id = p_req_id_tbl(i) for update nowait;
1926:
1927: END LOOP;
1928:
1960: l_phoneExtn,
1961: l_contactPartyId,
1962: l_suppPartyId
1963: FROM
1964: POS_CONTACT_REQUESTS PCR,
1965: POS_SUPPLIER_MAPPINGS PSM
1966: WHERE CONTACT_REQUEST_ID=p_req_id_tbl(i)
1967: AND PCR.MAPPING_ID=PSM.MAPPING_ID;
1968:
2449: savepoint chg_contact_req_approval;
2450:
2451: -- Lock the rows: This is done as part of ECO 5209555
2452: BEGIN
2453: select CONTACT_REQUEST_ID into l_lock_id from POS_CONTACT_REQUESTS
2454: WHERE CONTACT_REQUEST_ID = p_request_id for update nowait;
2455: EXCEPTION
2456:
2457: WHEN OTHERS THEN
2465:
2466: x_return_status := FND_API.G_RET_STS_SUCCESS;
2467:
2468:
2469: UPDATE POS_CONTACT_REQUESTS
2470: SET CONTACT_TITLE = p_contact_title,
2471: FIRST_NAME = p_first_name,
2472: MIDDLE_NAME = p_middle_name,
2473: LAST_NAME = p_last_name,
2603: savepoint new_contact_req_approval;
2604:
2605: -- Lock the rows: This is done as part of ECO 5209555
2606: BEGIN
2607: select CONTACT_REQUEST_ID into l_lock_id from POS_CONTACT_REQUESTS
2608: WHERE CONTACT_REQUEST_ID = p_request_id for update nowait;
2609: EXCEPTION
2610:
2611: WHEN OTHERS THEN
2619:
2620: x_return_status := FND_API.G_RET_STS_SUCCESS;
2621: x_user_id := -1 ;
2622: x_cont_party_id := -1 ;
2623: UPDATE POS_CONTACT_REQUESTS
2624: SET CONTACT_TITLE = p_contact_title,
2625: FIRST_NAME = p_first_name,
2626: MIDDLE_NAME = p_middle_name,
2627: LAST_NAME = p_last_name,
2658: if p_create_user_acc = 'Y' then
2659:
2660: SELECT fu.user_id,fu.PERSON_PARTY_ID
2661: into x_user_id, x_cont_party_id
2662: FROM fnd_user fu, pos_contact_requests pcr
2663: WHERE pcr.CONTACT_PARTY_ID = fu.PERSON_PARTY_ID
2664: and pcr.contact_request_id = p_request_id;
2665:
2666: ELSE
2665:
2666: ELSE
2667: select pcr.CONTACT_PARTY_ID
2668: into x_cont_party_id
2669: from pos_contact_requests pcr
2670: where pcr.contact_request_id = p_request_id;
2671:
2672: end if;
2673:
2900: l_cont_req_id NUMBER;
2901: BEGIN
2902: select contact_request_id
2903: into l_cont_req_id
2904: from pos_contact_requests
2905: where contact_party_id = p_contact_party_id
2906: and request_status = 'PENDING';
2907:
2908: return l_cont_req_id ;