DBA Data[Home] [Help]

APPS.POR_CONTRACTOR_NOTIFY SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 34

  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;
Line: 40

        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);
Line: 57

        PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'SUPPLIER_NEED_NOTIFY' || sqlerrm);
Line: 63

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;
Line: 92

       UPDATE PO_REQUISITION_HEADERS_ALL
 	   SET SUPPLIER_NOTIFIED_FLAG = 'Y'
         WHERE REQUISITION_HEADER_ID = L_REQ_HEADER_ID;
Line: 100

      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;
Line: 116

        UPDATE PO_REQUISITION_HEADERS_ALL
 	   SET SUPPLIER_NOTIFIED_FLAG = 'Y'
         WHERE REQUISITION_HEADER_ID = L_REQ_HEADER_ID;
Line: 122

      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;
Line: 133

        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);
Line: 183

        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);
Line: 201

 	  PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
	    g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || 'L_SUPPLIER_EXISTS: ' ||  L_SUPPLIER_EXISTS);
Line: 210

 	  PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
	    g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || 'L_SUPPLIER_EXISTS: ' ||  L_SUPPLIER_EXISTS);
Line: 214

        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;
Line: 224

 	   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);
Line: 255

        PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'SELECT_SUPPLIER_NOTIFY' || sqlerrm);
Line: 258

END SELECT_SUPPLIER_NOTIFY;
Line: 260

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;
Line: 276

     PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
	g_module_prefix || 'UPDATE_NOTIFY_SUPPLIER' || 'L_REQUISITION_SUPPLIER_ID : ' || L_REQUISITION_SUPPLIER_ID);
Line: 280

  UPDATE PO_REQUISITION_SUPPLIERS
     SET SUPPLIER_NOTIFIED_FLAG = 'Y', SUPPLIER_NOTIFIED_DATE = SYSDATE
   WHERE REQUISITION_SUPPLIER_ID = L_REQUISITION_SUPPLIER_ID;
Line: 289

        PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'UPDATE_NOTIFY_SUPPLIER' || sqlerrm);
Line: 292

END UPDATE_NOTIFY_SUPPLIER;
Line: 319

     PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_REQ_SUPPLIER_ID:' || L_REQ_SUPPLIER_ID);
Line: 325

    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;
Line: 333

    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;
Line: 344

       PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: SUPPLIER SITE DOES NOT EXIST');
Line: 350

     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);
Line: 356

   SELECT EMAIL_ADDRESS
     INTO L_EMAIL_ADDRESS
     FROM PO_VENDOR_SITES_ALL
    WHERE VENDOR_SITE_ID = L_VENDOR_SITE_ID;
Line: 366

     PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_EMAIL_ADDRESS FROM SUPPLIER SITE:' || L_EMAIL_ADDRESS || '*');
Line: 372

     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;
Line: 379

       PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_EMAIL_ADDRESS FROM VENDOR CONTACT:' || L_EMAIL_ADDRESS);
Line: 390

       PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,'POR_CONTRACTOR_NOTIFY.GET_ADHOC_EMAIL_ROLE: L_EMAIL_ADDRESS IS NULL');
Line: 402

    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;
Line: 409

    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';
Line: 416

  SELECT COUNT(*)
    INTO L_PERFORMER_EXISTS
    FROM WF_USERS
   WHERE NAME = L_EMAIL_PERFORMER;
Line: 438

     PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE,ITEMKEY,X_PROGRESS);
Line: 446

        PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'GET_ADHOC_EMAIL_ROLE' || sqlerrm);
Line: 472

       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';
Line: 481

        PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, g_module_prefix || 'SET_REQSINPOOL_FLAG' || sqlerrm);