The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CONTRACTOR_STATUS, SUPPLIER_NOTIFIED_FLAG
INTO L_CONTRACTOR_STATUS, L_SUPPLIER_NOTIFIED_FLAG
FROM PO_REQUISITION_HEADERS_ALL
WHERE REQUISITION_HEADER_ID = L_REQ_HEADER_ID;
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
g_module_prefix || 'SUPPLIER_NEED_NOTIFY' ||
'L_CONTRACTOR_STATUS, L_SUPPLIER_NOTIFIED_FLAG: ' ||
L_CONTRACTOR_STATUS|| ',' || L_SUPPLIER_NOTIFIED_FLAG);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'SUPPLIER_NEED_NOTIFY' || sqlerrm);
PROCEDURE SELECT_SUPPLIER_NOTIFY (ITEMTYPE IN VARCHAR2,
ITEMKEY IN VARCHAR2,
ACTID IN NUMBER,
FUNCMODE IN VARCHAR2,
RESULTOUT OUT NOCOPY VARCHAR2 )
IS
L_REQUISITION_SUPPLIER_ID PO_REQUISITION_SUPPLIERS.REQUISITION_SUPPLIER_ID%TYPE;
UPDATE PO_REQUISITION_HEADERS_ALL
SET SUPPLIER_NOTIFIED_FLAG = 'Y'
WHERE REQUISITION_HEADER_ID = L_REQ_HEADER_ID;
SELECT MAX(PLS.REQUISITION_SUPPLIER_ID)
INTO L_REQUISITION_SUPPLIER_ID
FROM PO_REQUISITION_SUPPLIERS PLS,
PO_REQUISITION_LINES_ALL PORL
WHERE NVL(PLS.SUPPLIER_NOTIFIED_FLAG,'N') = 'N'
AND PORL.REQUISITION_LINE_ID = PLS.REQUISITION_LINE_ID
AND PORL.REQUISITION_HEADER_ID = L_REQ_HEADER_ID;
UPDATE PO_REQUISITION_HEADERS_ALL
SET SUPPLIER_NOTIFIED_FLAG = 'Y'
WHERE REQUISITION_HEADER_ID = L_REQ_HEADER_ID;
SELECT PORL.ASSIGNMENT_START_DATE,
PORL.ITEM_DESCRIPTION, PORH.SEGMENT1 || ' / ' || PORL.LINE_NUM, PORL.REQUISITION_LINE_ID
INTO L_START_DATE, L_JOB_DESCRIPTION, L_REQ_NUM_LINE_NUM, L_REQUISITION_LINE_ID
FROM PO_REQUISITION_SUPPLIERS PLS,
PO_REQUISITION_LINES_ALL PORL,
PO_REQUISITION_HEADERS_ALL PORH
WHERE PORL.REQUISITION_LINE_ID = PLS.REQUISITION_LINE_ID
AND PLS.REQUISITION_SUPPLIER_ID = L_REQUISITION_SUPPLIER_ID
AND PORL.REQUISITION_HEADER_ID = PORH.REQUISITION_HEADER_ID;
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' ||
'L_START_DATE, L_JOB_DESCRIPTION, L_REQUISITION_SUPPLIER_ID: ' ||
L_START_DATE || ',' || L_JOB_DESCRIPTION || ',' ||
L_REQUISITION_SUPPLIER_ID);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || 'L_NOTIFIER: ' || L_NOTIFIER || ' L_REQUISITION_SUPPLIER_ID :' || L_REQUISITION_SUPPLIER_ID);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || 'L_SUPPLIER_EXISTS: ' || L_SUPPLIER_EXISTS);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || 'L_SUPPLIER_EXISTS: ' || L_SUPPLIER_EXISTS);
SELECT PORL.LINE_NUM, POV.VENDOR_NAME
INTO L_LINE_NUM, L_VENDOR_NAME
FROM PO_REQUISITION_SUPPLIERS PLS,
PO_REQUISITION_LINES_ALL PORL,
PO_VENDORS POV
WHERE PORL.REQUISITION_LINE_ID = PLS.REQUISITION_LINE_ID
AND PLS.REQUISITION_SUPPLIER_ID = L_REQUISITION_SUPPLIER_ID
AND PLS.VENDOR_ID = POV.VENDOR_ID;
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
g_module_prefix || 'SELECT_SUPPLIER_NOTIFY L_LINE_NUM:L_VENDOR_NAME'
|| L_LINE_NUM || ':' || L_VENDOR_NAME);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || sqlerrm);
END SELECT_SUPPLIER_NOTIFY;
PROCEDURE UPDATE_NOTIFY_SUPPLIER (ITEMTYPE IN VARCHAR2,
ITEMKEY IN VARCHAR2,
ACTID IN NUMBER,
FUNCMODE IN VARCHAR2,
RESULTOUT OUT NOCOPY VARCHAR2 )
IS
L_REQUISITION_SUPPLIER_ID Number;
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
g_module_prefix || 'UPDATE_NOTIFY_SUPPLIER' || 'L_REQUISITION_SUPPLIER_ID : ' || L_REQUISITION_SUPPLIER_ID);
UPDATE PO_REQUISITION_SUPPLIERS
SET SUPPLIER_NOTIFIED_FLAG = 'Y', SUPPLIER_NOTIFIED_DATE = SYSDATE
WHERE REQUISITION_SUPPLIER_ID = L_REQUISITION_SUPPLIER_ID;
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'UPDATE_NOTIFY_SUPPLIER' || sqlerrm);
END UPDATE_NOTIFY_SUPPLIER;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_REQ_SUPPLIER_ID:' || L_REQ_SUPPLIER_ID);
SELECT PRS.VENDOR_SITE_ID, PRS.VENDOR_CONTACT_ID, PVS.VENDOR_SITE_CODE, PVS.LANGUAGE
INTO L_VENDOR_SITE_ID, L_VENDOR_CONTACT_ID, L_VENDOR_SITE_CODE, L_VENDOR_SITE_LANG
FROM PO_REQUISITION_SUPPLIERS PRS, PO_VENDOR_SITES_ALL PVS
WHERE PRS.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PRS.REQUISITION_SUPPLIER_ID = L_REQ_SUPPLIER_ID;
SELECT PRL.VENDOR_SITE_ID, PRL.VENDOR_CONTACT_ID, PVS.VENDOR_SITE_CODE, PVS.LANGUAGE
INTO L_VENDOR_SITE_ID, L_VENDOR_CONTACT_ID, L_VENDOR_SITE_CODE, L_VENDOR_SITE_LANG
FROM PO_REQUISITION_LINES PRL, PO_VENDOR_SITES_ALL PVS
WHERE PRL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PRL.REQUISITION_LINE_ID = L_REQ_LINE_ID;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: SUPPLIER SITE DOES NOT EXIST');
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,' L_VENDOR_SITE_ID, L_VENDOR_CONTACT_ID, L_VENDOR_SITE_CODE, L_VENDOR_SITE_LANG:'
|| L_VENDOR_SITE_ID || '*' || L_VENDOR_CONTACT_ID || '*' || L_VENDOR_SITE_CODE || '*' || L_VENDOR_SITE_LANG);
SELECT EMAIL_ADDRESS
INTO L_EMAIL_ADDRESS
FROM PO_VENDOR_SITES_ALL
WHERE VENDOR_SITE_ID = L_VENDOR_SITE_ID;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_EMAIL_ADDRESS FROM SUPPLIER SITE:' || L_EMAIL_ADDRESS || '*');
SELECT EMAIL_ADDRESS
INTO L_EMAIL_ADDRESS
FROM PO_VENDOR_CONTACTS
WHERE VENDOR_CONTACT_ID = L_VENDOR_CONTACT_ID
AND VENDOR_SITE_ID = L_VENDOR_SITE_ID;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_EMAIL_ADDRESS FROM VENDOR CONTACT:' || L_EMAIL_ADDRESS);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_EMAIL_ADDRESS IS NULL');
SELECT WFL.NLS_LANGUAGE, WFL.NLS_TERRITORY INTO L_ADHOCUSER_LANG, L_ADHOCUSER_TERRITORY
FROM WF_LANGUAGES WFL, FND_LANGUAGES_VL FLV
WHERE WFL.CODE = FLV.LANGUAGE_CODE
AND FLV.NLS_LANGUAGE = L_VENDOR_SITE_LANG;
SELECT WFL.NLS_LANGUAGE, WFL.NLS_TERRITORY INTO L_ADHOCUSER_LANG, L_ADHOCUSER_TERRITORY
FROM WF_LANGUAGES WFL, FND_LANGUAGES_VL FLV
WHERE WFL.CODE = FLV.LANGUAGE_CODE
AND FLV.INSTALLED_FLAG = 'B';
SELECT COUNT(*)
INTO L_PERFORMER_EXISTS
FROM WF_USERS
WHERE NAME = L_EMAIL_PERFORMER;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,X_PROGRESS);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'GET_ADHOC_EMAIL_ROLE' || sqlerrm);
UPDATE PO_REQUISITION_LINES_ALL
SET REQS_IN_POOL_FLAG = 'Y'
WHERE REQUISITION_HEADER_ID = L_REQ_HEADER_ID
AND NVL(CONTRACTOR_REQUISITION_FLAG, 'N') = 'Y';
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'SET_REQSINPOOL_FLAG' || sqlerrm);