DBA Data[Home] [Help]

APPS.PO_APPROVAL_REMINDER_SV SQL Statements

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

Line: 14

  PROCEDURE NAME:       Select_Unapprove_docs

  DESCRIPTION:          This server procedure is defined as a concurrent
                        PL/SQL executable program and is scheduled to run
                        from the Concurrent Manager at a regular intervals
                        (e.g. every day).


  CHANGE HISTORY:       WLAU       7/15/1997     Created

===========================================================================*/

  PROCEDURE Select_Unapprove_docs  IS

   l_ItemType            VARCHAR2(100) := 'APVRMDER';
Line: 35

     l_progress := 'PO_APPROVAL_REMINDER_SV.Select_Unapprove_docs: 01 - BEGIN ';
Line: 37

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 55

     l_progress := 'PO_APPROVAL_REMINDER_SV.Select_Unapprove_docs: 900 - END ';
Line: 57

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 65

             wf_core.context ('PO_APPROVAL_REMINDER_SV','Select_Unapprove_docs ' || l_progress);
Line: 66

            l_progress := 'PO_APPROVAL_REMINDER_SV.Select_Unapprove_docs: 990 - ' ||
                          'EXCEPTION sql error: ' || sqlcode;
Line: 69

               /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 75

  END Select_Unapprove_docs;
Line: 84

                        - Open cursor PO_REQUISITION_HEADERS table to select
                          Incomplete or Requires_reapproval documents.

                        - For each unapprove document, initiate the
                          PO Approval Reminder workflow notification.



  CHANGE HISTORY:       WLAU       7/15/1997     Created
===========================================================================*/
 PROCEDURE Process_unapprove_reqs IS

   -- Define cursor for selecting unapprove document to start the Purchasing
   -- Approval Reminder workflow process.
   --

   CURSOR unapprove_req IS
           SELECT Requisition_Header_ID, Segment1, Type_Lookup_Code, Preparer_ID
            FROM  PO_REQUISITION_HEADERS
            WHERE NVL(authorization_status,'INCOMPLETE') IN
		     ('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL', 'RETURNED')
              AND NVL(cancel_flag,'N') = 'N'
              AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED';
Line: 128

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 174

	       -- /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 201

    	       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 231

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 241

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 250

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 265

                        - Open cursor PO_HEADERS table to select
                          Incomplete or Requires_reapproval documents.

                        - For each unapprove document, initiate the
                          PO Approval Reminder workflow notification.

  CHANGE HISTORY:       WLAU       7/15/1997     Created
===========================================================================*/

 PROCEDURE Process_unapprove_pos IS

   -- Define cursor for selecting unapprove document to start the Purchasing
   -- Approval Reminder workflow process.
   --

   CURSOR unapprove_PO IS
           SELECT PO_Header_ID, Segment1, Type_Lookup_Code, Agent_ID
            FROM  PO_HEADERS
            WHERE NVL(authorization_status,'INCOMPLETE') IN
		     ('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL')
--              AND WF_ITEM_TYPE = NULL
--              AND WF_ITEM_KEY  = NULL
              AND type_lookup_code in ('STANDARD','PLANNED','BLANKET','CONTRACT')
              AND NVL(cancel_flag,'N') = 'N'
              AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED';
Line: 311

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 364

	       -- /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 390

    	       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 421

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 431

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 440

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 455

                        - Open cursor PO_RELEASES table to select
                          Incomplete or Requires_reapproval documents.

                        - For each unapprove document, initiate the
                          PO Approval Reminder workflow notification.

  CHANGE HISTORY:       WLAU       7/15/1997     Created
===========================================================================*/

 PROCEDURE Process_unapprove_releases IS

   -- Define cursor for selecting unapprove document to start the Purchasing
   -- Approval Reminder workflow process.
   --


   CURSOR unapprove_REL IS
           SELECT PORH.PO_release_ID, POH.Segment1, PORH.release_num,
	          POH.Type_Lookup_Code, PORH.Agent_ID
            FROM  PO_RELEASES_ALL PORH,    -- 
	          PO_HEADERS POH
            WHERE NVL(PORH.authorization_status,'INCOMPLETE') IN
		     ('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL')
--              AND WF_ITEM_TYPE = NULL
--              AND WF_ITEM_KEY  = NULL
              AND NVL(PORH.cancel_flag,'N') = 'N'
              AND NVL(PORH.closed_code,'OPEN') <> 'FINALLY CLOSED'
              AND POH.PO_HEADER_ID = PORH.PO_HEADER_ID;
Line: 504

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 556

	       -- /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 584

    	       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 613

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 622

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 631

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 648

                        - For each selected document, initiate the
                          PO Approval Reminder workflow notification

  CHANGE HISTORY:       WLAU       11/15/1997     Created
===========================================================================*/

 PROCEDURE Process_po_acceptance IS

   -- Define cursor for selecting approved POs with acceptance required

/* Bug# 1595348: kagarwal
** Desc: If the PO/Rel is accepted using Web Supplier Portal, the acceptance
** is registered in the PO Acceptances table. Hence we need to check the PO
** Acceptances table also.
*/
   -- Bug 5074128(forward fix4772820)
   -- poh.acceptance_required_flag can be Y or D
   CURSOR PO_acceptance IS
           SELECT poh.PO_Header_ID, poh.Segment1, poh.Type_Lookup_Code,
		  poh.Agent_ID, NVL(poh.Acceptance_Due_Date, SYSDATE)
            FROM  PO_HEADERS poh
            WHERE NVL(poh.authorization_status,'INCOMPLETE') = 'APPROVED'
	      AND NVL(poh.acceptance_required_flag,'N') in ('Y','D')
              AND poh.type_lookup_code in
                    ('STANDARD','PLANNED','BLANKET','CONTRACT')
              AND NVL(poh.cancel_flag,'N') = 'N'
              AND NVL(poh.closed_code,'OPEN') <> 'FINALLY CLOSED'
              AND not exists (
			SELECT poa.ACCEPTANCE_ID
			FROM PO_ACCEPTANCES poa
			WHERE NVL(poa.accepted_flag, 'N') = 'Y'
			AND poa.po_header_id = poh.po_header_id
			AND nvl(poa.revision_num,0) = nvl(poh.revision_num,0));
Line: 706

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 761

 /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 770

    /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 775

            /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 799

            /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 804

                       select wfn.MESSAGE_NAME
              into l_message_name
              from wf_item_activity_statuses wias, wf_notifications wfn
             where wias.notification_id = wfn.group_id
               and wias.item_type = 'APVRMDER'
               and wias.item_key = l_ItemKey ;
Line: 814

                    /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 845

    	          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 852

                 /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 881

                     /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 885

            select wfn.MESSAGE_NAME
              into l_message_name
              from wf_item_activity_statuses wias, wf_notifications wfn
             where wias.notification_id = wfn.group_id
              and  wias.item_type = 'APVRMDER'
              and  wias.item_key = l_ItemKey ;
Line: 894

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 922

    	       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 951

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 960

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 968

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 985

                        - For each selected document, initiate the
                          PO Approval Reminder workflow notification

  CHANGE HISTORY:       WLAU       11/15/1997     Created
===========================================================================*/

 PROCEDURE Process_rel_acceptance IS

   -- Define cursor for selecting approved releases with acceptance required

/* Bug# 1595348: kagarwal
** Desc: If the PO/Rel is accepted using Web Supplier Portal, the acceptance
** is registered in the PO Acceptances table. Hence we need to check the PO
** Acceptances table also.
*/

/* Bug# 2633688: kagarwal
** Desc: When accepting releases from ISP, the po header id is
** left null in the po acceptances table (See Bug 2188005) hence
** removing the condition 'poa.po_header_id = poh.po_header_id'
*/


   CURSOR REL_acceptance IS
           SELECT PORH.PO_release_ID, POH.Segment1, PORH.release_num,
	          POH.Type_Lookup_Code, PORH.Agent_ID,
                  NVL(PORH.Acceptance_Due_Date, SYSDATE)
            FROM  PO_RELEASES_ALL PORH,   -- 
	          PO_HEADERS POH
            WHERE NVL(PORH.authorization_status,'INCOMPLETE') = 'APPROVED'
   		AND NVL(PORH.acceptance_required_flag,'N')= 'Y'
              AND NVL(PORH.cancel_flag,'N') = 'N'
              AND NVL(PORH.closed_code,'OPEN') <> 'FINALLY CLOSED'
              AND POH.PO_HEADER_ID = PORH.PO_HEADER_ID
	      AND not exists (
                        SELECT poa.ACCEPTANCE_ID
                        FROM PO_ACCEPTANCES poa
                        WHERE NVL(poa.accepted_flag, 'N') = 'Y'
                    /*    AND poa.po_header_id = poh.po_header_id */
                        AND porh.po_release_id = poa.po_release_id
                        AND nvl(poa.revision_num,0) = nvl(porh.revision_num,0));
Line: 1048

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1105

                         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1110

                   /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1115

                     /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1139

                           /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1145

         select wfn.MESSAGE_NAME
         into l_message_name
         from wf_item_activity_statuses wias, wf_notifications wfn
         where wias.notification_id = wfn.group_id
         and   wias.item_type = 'APVRMDER'
         and   wias.item_key = l_ItemKey ;
Line: 1159

                  /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1190

    	          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1197

                                 /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1226

                                 /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1232

                    select wfn.MESSAGE_NAME
                      into   l_message_name
                      from wf_item_activity_statuses wias, wf_notifications wfn
                     where wias.notification_id = wfn.group_id
                          and   wias.item_type = 'APVRMDER'
                          and   wias.item_key = l_ItemKey ;
Line: 1245

                                 /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1272

    	       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1301

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1311

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1319

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1335

                        - Open cursor PO_HEADERS table to select
                          RFQ and Quote documents.

                        - For each selected document, initiate the
                          PO Approval Reminder workflow notification.

  CHANGE HISTORY:       WLAU       11/15/1997     Created
===========================================================================*/

 PROCEDURE Process_rfq_quote IS

   -- Define cursor for selecting RFQ and Quote documents to start the Purchasing
   -- Approval Reminder workflow process.
   --
/* Bug# 1541123: kagarwal
** Desc: If the End_date for RFQ or Quotation is null
** then it means that the RFQ or Quote does not expire
** hence we need to change the nvl value of End_date
** to SYSDATE + 1.
**
** Also changing for Reply_date and RFQ_close_date
*/
/* Bug# 1764388: kagarwal
** Desc:  If the End_date for Quotation is null then it means that the Quote
** does not expire. In this case we should not consider the Quote_warning_delay
** in the CURSOR RFQ_QUOTE.
*/

   CURSOR RFQ_QUOTE IS
           SELECT PO_Header_ID,
		  Segment1,
		  Type_Lookup_Code,
		  Quote_type_lookup_code,
	          Agent_id,
	          Status_lookup_code,
	 	  NVL(Reply_date,SYSDATE + 1),
		  NVL(RFQ_close_date,SYSDATE + 1),
		  NVL(End_date,SYSDATE + 1),
		  decode(End_date, NULL, 0, NVL(Quote_warning_delay,0))
	          Quote_warning_delay
            FROM  PO_HEADERS
            WHERE NVL(Status_lookup_code,'I') IN ('I','A')
              AND type_lookup_code in ('RFQ','QUOTATION');
Line: 1406

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1468

      		--/* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1473

      		--/* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1477

      		--/* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1482

      		--/* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1495

      		   --/* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1511

         /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1536

	       -- /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1546

   	       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1580

      /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1590

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1598

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1640

   	/* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1730

   	/* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1741

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(l_itemtype,l_itemkey,l_progress);
Line: 1802

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 1892

		select authorization_status into l_req_status
		from po_requisition_headers
		where requisition_header_id = l_doc_header_id;
Line: 1959

           	SELECT NVL(acceptance_due_date,SYSDATE)
             	INTO l_acceptance_due_date
             	FROM PO_HEADERS
            	WHERE po_header_id = l_doc_header_id;
Line: 1965

           	SELECT NVL(acceptance_due_date, SYSDATE)
             	INTO l_acceptance_due_date
             	FROM PO_RELEASES
            	WHERE po_release_id = l_doc_header_id;
Line: 2000

   	      SELECT NVL(Reply_date,SYSDATE),
		     NVL(RFQ_close_date,SYSDATE),
		     NVL(End_date,SYSDATE),
		     NVL(Quote_warning_delay,0),
		     Status_lookup_code,
		     Quote_type_lookup_code
	        INTO l_rfq_reply_date,
		     l_rfq_close_date,
	  	     l_quote_end_date_active,
	       	     l_quote_warning_delay,
  		     l_status_lookup_code,
 		     l_quote_type_lookup_code
                FROM PO_HEADERS
               WHERE PO_HEADER_ID = l_doc_header_id;
Line: 2075

       	/* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2088

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2098

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2106

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2153

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2285

       /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2295

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2304

                /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
Line: 2357

        select user_id
        from fnd_user
        where employee_id =  p_PreparerID;
Line: 2379

          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug('APVRMDER',NULL,l_progress);
Line: 2392

        select to_char(PO_WF_ITEMKEY_S.nextval) into l_seq from sys.dual;
Line: 2416

              PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
Line: 2449

	 * Deleted the sql query which selected wf_approval_itemtype and
	 * wf_approval_process from PO_DOCUMENT_TYPES. Instead call the
	 * new overloaded procedure po_approve_sv.get_document_types.
	 * We call this here even though this is called in start_wf_process
	 * since we need these values to set some workflow attributes
	 * before we call start_Wf_process.
	*/

	       l_progress := 'PO_APPROVAL_REMINDER_SV.SetUpWorkFlow: 03 ' ||
                        p_DocumentTypeCode  || p_DocumentSubtype ||
			p_DocumentNumber;
Line: 2461

              PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
Line: 2527

                 PO_WF_DEBUG_PKG.insert_debug('POXWARMB',NULL,l_progress);
Line: 2553

		  /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug('APVRMDER',l_itemkey,l_progress);
Line: 2565

			/* DEBUG */  PO_WF_DEBUG_PKG.insert_debug('APVRMDER',l_itemkey,l_progress);
Line: 2602

	     /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
Line: 2631

	     /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
Line: 2757

	     /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
Line: 2768

		   /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
Line: 2804

	  select wias.activity_status_code, wias.item_key
            from wf_item_activity_statuses_v wias, wf_items_v wi
           where wias.item_type = 'APVRMDER'
             and wias.item_key like l_itemkey||'%'
             and wias.item_type     = wi.item_type
             and wias.item_key      = wi.item_key
             and wias.activity_name = wi.root_activity;
Line: 2820

	      select agent_id
	      into   l_agent_id
	      from   po_headers_all      /*Bug6632095: using base table instead of view */
	      where  po_header_id = p_DocumentID;
Line: 2848

	      select agent_id
	      into   l_agent_id
	      from   po_releases_all     /*Bug6632095: using base table instead of view */
	      where  po_release_id = p_DocumentID;
Line: 2867

	      select agent_id
	      into   l_agent_id
	      from   po_headers_all      /*Bug6632095: using base table instead of view */
	      where  po_header_id = p_DocumentID;
Line: 2892

	      select preparer_id
	      into   l_agent_id
	      from   po_requisition_headers
	      where  requisition_header_id = p_DocumentID;
Line: 2915

	      select agent_id
	      into   l_agent_id
	      from   po_releases_all      /*Bug6632095: using base table instead of view */
	      where  po_release_id = p_DocumentID;
Line: 2937

	      select type_lookup_code
	      into   l_doc_subtype
	      from   po_headers_all      /*Bug6632095: using base table instead of view */
	      where  po_header_id = p_DocumentID;
Line: 2970

	      select poh.type_lookup_code
	      into   l_doc_subtype
	      from   po_headers_all poh,    -- 
	             po_releases_all por    /*Bug6632095: using base table instead of view */
	      where  por.po_header_id = poh.po_header_id and
		     por.po_release_id = p_DocumentId;
Line: 3055

		   PO_WF_DEBUG_PKG.insert_debug(null,null,x_progress);
Line: 3105

		PO_WF_DEBUG_PKG.insert_debug(null,null,x_progress);
Line: 3136

	   -- /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
Line: 3142

		SELECT		'Y', WI.end_date
		  INTO	        p_item_exist, p_item_end_date
		  FROM		WF_ITEMS_V WI
		 WHERE		WI.ITEM_TYPE = p_ItemType
		   AND          WI.ITEM_KEY  = p_ItemKey;
Line: 3150

	    -- /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
Line: 3166

        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_progress);
Line: 3210

    SELECT POL2.po_line_id,
           POL2.svc_amount_notif_sent,
           POL2.amount,
           SUM_DATA.total_amount_billed amount_billed,
           POL2.svc_completion_notif_sent,
           POL2.expiration_date,
           POL2.contractor_first_name,
           POL2.contractor_last_name,
           PJ.name job_name
    FROM po_lines_all POL2,
         per_jobs_vl PJ,
         (  SELECT PLL.po_line_id
                 , SUM(PLL.amount_billed) total_amount_billed
            FROM   po_line_locations PLL
                 , po_lines_all POL
                 , po_headers_all poh
            WHERE  poh.type_lookup_code = 'STANDARD'
             AND   poh.po_header_id = POL.po_header_id
             AND   POL.purchase_basis = 'TEMP LABOR'
             AND   POL.po_line_id = PLL.po_line_id
             AND   NVL(PLL.approved_flag, 'N') = 'Y'
             AND   NVL(PLL.cancel_flag, 'N') = 'N'
             AND   NVL(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
             AND   (    PLL.payment_type IS NULL
                     OR PLL.payment_type NOT IN ('DELIVERY','ADVANCE')
                   )
             GROUP BY PLL.po_line_id
         ) SUM_DATA
    WHERE POL2.po_line_id = SUM_DATA.po_line_id
      AND PJ.job_id = POL2.job_id
      AND (    (     p_amount_threshold IS NOT NULL
                 AND SUM_DATA.total_amount_billed
                                      >= POL2.amount * p_amount_threshold / 100
               )
            OR (     p_completion_threshold IS NOT NULL
                 AND TRUNC(sysdate) >= TRUNC(POL2.expiration_date) - p_completion_threshold
               )
          );
Line: 3266

      SELECT user_profile_option_name
      INTO l_user_profile_name
      FROM FND_PROFILE_OPTIONS_VL
      WHERE profile_option_name = l_profile_name;
Line: 3330

      UPDATE po_lines_all
      SET svc_amount_notif_sent = 'Y',
          last_update_date = sysdate,
          last_updated_by = fnd_global.user_id
      WHERE po_line_id = l_tl_line_rec.po_line_id;
Line: 3354

      UPDATE po_lines_all
      SET svc_completion_notif_sent = 'Y',
          last_update_date = sysdate,
          last_updated_by = fnd_global.user_id
      WHERE po_line_id = l_tl_line_rec.po_line_id;
Line: 3510

select   POH.po_header_id,
         POH.segment1
 INTO    l_po_header_id,
         l_document_number
 FROM    po_lines  POL,
         po_headers_all POH
 WHERE   POL.po_line_id = p_po_line_id
 AND     POL.po_header_id = POH.po_header_id;
Line: 3521

    select   PRL.requisition_header_id
    INTO    l_req_header_id
    FROM    po_lines  POL,
        po_line_locations_all PLL,
        po_requisition_lines_all PRL
   WHERE   POL.po_line_id = p_po_line_id
   AND   POL.po_line_id = PLL.po_line_id
   AND   PLL.line_location_id = PRL.line_location_id;