APPS.PO_SIGNATURE_PVT SQL Statements

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

Line: 25

SELECT PHA.segment1,
       PHA.revision_num,
       PHA.comments,
       VO.vendor_name,
       PHA.type_lookup_code,
       PHA.po_header_id,
       HRL_B.location_code bill_to_location,
       HRL_S.location_code ship_to_location,
       DECODE(PHA.vendor_contact_id, NULL, NULL,
         VC.last_name||', '||VC.first_name) vendor_contact,
       PHA.blanket_total_amount
  FROM PO_HEADERS_ALL   PHA,
       PO_VENDORS     VO,
       PO_VENDOR_CONTACTS VC,
       HR_LOCATIONS_ALL_TL HRL_S,
       HR_LOCATIONS_ALL_TL HRL_B
 WHERE PHA.po_header_id =  p_po_header_id
   AND PHA.vendor_id    =  VO.vendor_id
   AND  VC.vendor_contact_id (+) = PHA.vendor_contact_id
   AND  HRL_S.location_id (+) = PHA.ship_to_location_id
   AND  HRL_S.language(+) = USERENV('LANG')
   AND  HRL_B.location_id (+) = PHA.bill_to_location_id
   AND  HRL_B.language(+) = USERENV('LANG');

Line: 110

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 190

       SELECT type_name
         INTO l_doc_display_name
         FROM PO_DOCUMENT_TYPES
        WHERE document_type_code = l_document_type
          AND document_subtype = l_document_subtype;

Line: 232

		 SELECT NVL(poh.conterms_exist_flag,'N')
		 INTO l_conterms_exist
		 FROM po_headers_merge_v poh
		 WHERE poh.po_header_id = l_document_id
		 AND poh.draft_id = l_draft_id;

Line: 278

		SELECT fu.user_name
			INTO l_supplier_user_name
		  FROM po_supplier_contacts_val_v pscv,
			po_headers_merge_v phm,
			fnd_user fu
		  WHERE pscv.vendor_contact_id = phm.vendor_contact_id
		   AND pscv.vendor_site_id      = phm.vendor_site_id
           AND fu.user_name = pscv.user_name
		   AND phm.po_header_id         = l_document_id
		   AND phm.draft_id     = l_draft_id
           AND trunc(sysdate) BETWEEN trunc(fu.start_date)
           AND trunc(nvl(fu.end_date,SYSDATE+1));

Line: 302

      SELECT wf_item_key
      INTO l_po_itemkey
      FROM po_headers_all poh
      WHERE poh.po_header_id = l_document_id;

Line: 318

      PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 406

     PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);

Line: 453

          /* SELECT HRL.name
            INTO l_buyer_org
            FROM HR_OPERATING_UNITS HRO,
                 HR_LEGAL_ENTITIES HRL
           WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
             AND HRO.organization_id = l_orgid; */

Line: 527

     PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);

Line: 534

     PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);

Line: 621

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 739

      SELECT displayed_field
        INTO l_response_code
        FROM Po_Lookup_Codes
       WHERE Lookup_Type = 'ERECORD_RESPONSE'
         AND Lookup_Code = l_response;

Line: 750

      SELECT displayed_field
        INTO l_reason_code
        FROM Po_Lookup_Codes
       WHERE Lookup_Type = 'ERECORD_REASON'
         AND Lookup_Code = 'ERES_REASON';

Line: 761

      SELECT displayed_field
        INTO l_signer_type
        FROM Po_Lookup_Codes
       WHERE Lookup_Type = 'ERECORD_SIGNER_TYPE'
         AND Lookup_Code = Decode(l_signer,'SUPPLIER','SUPPLIER','BUYER','CUSTOMER');

Line: 793

      PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 849

      PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 868

             PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
  		                  'End erecords_exception:PO_SIGNATURE_PVT.CREATE_ERECORD ');

Line: 870

             PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
  		                  'ERROR RETURNED '||l_msg_data);

Line: 938

   l_last_update_date          PO_ACCEPTANCES.last_update_date%TYPE;

Line: 939

   l_last_updated_by           PO_ACCEPTANCES.last_updated_by%TYPE;

Line: 940

   l_last_update_login         PO_ACCEPTANCES.last_update_login%TYPE;

Line: 968

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 974

    PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1019

         SELECT HP.person_title
           INTO l_role
           FROM FND_USER FU,
                HZ_PARTIES HP
          WHERE HP.party_id = FU.customer_id
            AND FU.user_id = fnd_global.user_id;

Line: 1032

         SELECT pvc.first_name||' '||pvc.last_name vendor_contact_name
           INTO l_vendor_contact_name
           FROM PO_VENDOR_CONTACTS pvc,
                PO_HEADERS_ALL poh
          WHERE pvc.vendor_contact_id = poh.vendor_contact_id
		    AND pvc.vendor_site_id = poh.vendor_site_id
            AND poh.po_header_id = l_document_id;

Line: 1078

                                select employee_id
                                 into l_employee_id
                                from fnd_user
                                where user_name = l_ko_user_name;

Line: 1102

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1136

	select nvl(clm_flag,'N')
		into l_is_clm_enabled
	  from po_doc_style_headers psh,
	  po_headers_merge_v poh
	where psh.style_id = poh.style_id
	and poh.po_header_id = l_document_id
	and poh.draft_id = l_draft_id;

Line: 1152

   		SELECT wf_item_type,
			         wf_item_key
			    INTO l_po_itemtype,
			         l_po_itemkey
			    FROM PO_HEADERS_MERGE_V
          WHERE po_header_id = l_document_id
          AND draft_id = l_draft_id;

Line: 1160

   		SELECT wf_item_type,
			         wf_item_key
			    INTO l_po_itemtype,
			         l_po_itemkey
			    FROM PO_HEADERS_ALL
          WHERE po_header_id = l_document_id;

Line: 1173

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1177

      SELECT displayed_field
        INTO l_response_code
        FROM Po_Lookup_Codes
       WHERE Lookup_Type = 'ERECORD_RESPONSE'
         AND Lookup_Code = l_response;

Line: 1194

			PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1200

			l_progress := 'PO_SIGNATURE_PVT.Post_Signature: before call to PO_ACCEPTANCES_INS_PVT.insert_row';

Line: 1201

			PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1206

  PO_ACCEPTANCES_INS_PVT.insert_row(
           x_rowid	                        => l_rowid,
           x_acceptance_id		            => l_acceptance_id,
           p_creation_date		            => sysdate,
           p_created_by		                => fnd_global.user_id,
           p_po_header_id		            => l_document_id,
		   p_draft_id						=> l_draft_id,
           p_po_release_id		            => Null,
           p_action		                    => l_response_code,
           p_action_date		            => l_acceptance_date,-- CONTERMS FPJ
           p_employee_id		            => l_employee_id,
           p_revision_num		            => l_revision_num,
           p_accepted_flag		            => l_accepted_flag,
           p_acceptance_lookup_code	        => Null,
           p_note		                    => l_acceptance_note,
           p_accepting_party                => l_accepting_party,
           p_signature_flag                 => 'Y',
           p_erecord_id                     => l_erecord_id,
           p_role                           => l_role,
           x_last_update_date               => l_last_update_date,
           x_last_updated_by                => l_last_updated_by,
           x_last_update_login              => l_last_update_login,
		  -- p_vendor_contact_id		    => l_vendor_contact_id, -- CLM Apprvl proxy signature process
		   p_vendor_contact_name    		=> l_vendor_contact_name, -- CLM Apprvl proxy signature process
		   p_vendor_contact_title			=> l_vendor_contact_title, -- CLM Apprvl proxy signature process
		   p_vendor_signed_date				=> l_vendor_signed_date      -- CLM Apprvl proxy signature process
		   );

Line: 1244

	/* bug9924585, if clm is enabled we need to call the overloaded proc update_po_details which will be an autonomous transaction*/
	if l_is_clm_enabled = 'Y' then
      -- CLM Apprvl - Added the parameter draft_id on the call to update_po_details
		  Update_Po_Details(p_po_header_id        => l_document_id,
							p_draft_id			  => l_draft_id,
							p_status              => 'REJECTED',
							p_action_code         => l_action_code,
							p_object_type_code    => l_document_type_code,
							p_object_subtype_code => l_document_sub_type_code,
							p_employee_id         => l_employee_id,
							p_revision_num        => l_revision_num);

Line: 1256

			Update_Po_Details(p_po_header_id        => l_document_id,
							p_status              => 'REJECTED',
							p_action_code         => l_action_code,
							p_object_type_code    => l_document_type_code,
							p_object_subtype_code => l_document_sub_type_code,
							p_employee_id         => l_employee_id,
							p_revision_num        => l_revision_num);

Line: 1281

     /* bug9924585, if clm is enabled we need to call the overloaded proc update_po_details which will be an autonomous transaction*/
	if l_is_clm_enabled = 'Y' then
      -- CLM Apprvl - Added the parameter draft_id on the call to update_po_details
		  Update_Po_Details(p_po_header_id        => l_document_id,
							p_draft_id			  => l_draft_id,
							p_status              => 'APPROVED',
							p_action_code         => 'SIGNED',
							p_object_type_code    => l_document_type_code,
							p_object_subtype_code => l_document_sub_type_code,
							p_employee_id         => l_employee_id,
							p_revision_num        => l_revision_num);

Line: 1293

			Update_Po_Details(p_po_header_id        => l_document_id,
							p_status              => 'APPROVED',
							p_action_code         => 'SIGNED',
							p_object_type_code    => l_document_type_code,
							p_object_subtype_code => l_document_sub_type_code,
							p_employee_id         => l_employee_id,
							p_revision_num        => l_revision_num);

Line: 1309

      PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
                p_po_header_id      => l_document_id,
                p_signed_date       => l_acceptance_date,
    	        x_return_status     => l_return_status,
                x_msg_data          => l_msg_data,
                x_msg_count         => l_msg_count);

Line: 1329

     l_progress := 'PO_SIGNATURE_PVT.Post_Signature: 03'||'Updated PO tables';

Line: 1330

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1346

             PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
  		                  'End contracts_call_exception:PO_SIGNATURE_PVT.POST_SIGNATURE ');

Line: 1348

             PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
  		                  'ERROR RETURNED '||l_msg_data);

Line: 1361

             PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
  		                  'End binding_exception:PO_SIGNATURE_PVT.POST_SIGNATURE ');

Line: 1363

             PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey,
  		                  'ERROR RETURNED '||l_msg_data);

Line: 1429

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1484

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1488

  SELECT wf_item_key
  INTO  l_po_itemkey
  FROM po_headers_all poh
  WHERE poh.po_header_id = l_document_id;

Line: 1497

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1557

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1612

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1615

  SELECT wf_item_key
  INTO  l_po_itemkey
  FROM po_headers_all poh
  WHERE poh.po_header_id = l_document_id;

Line: 1624

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1708

     PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);

Line: 1767

          /* SELECT HRL.name
            INTO l_buyer_org
            FROM HR_OPERATING_UNITS HRO,
                 HR_LEGAL_ENTITIES HRL
           WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
             AND HRO.organization_id = l_orgid; */

Line: 1858

     PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);

Line: 1865

     PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);

Line: 1930

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1986

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 2069

     PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);

Line: 2121

         /* SELECT HRL.name
            INTO l_buyer_org
            FROM HR_OPERATING_UNITS HRO,
                 HR_LEGAL_ENTITIES HRL
           WHERE HRO.default_legal_context_id = HRL.organization_id -- Bug#5527795
             AND HRO.organization_id = l_orgid;*/

Line: 2212

     PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);

Line: 2219

     PO_WF_DEBUG_PKG.insert_debug(l_item_type,l_item_key,l_progress);

Line: 2285

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 2396

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

Line: 2421

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

Line: 2478

l_progress:='000'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress);

Line: 2507

l_progress:='010'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Document Type = '||l_document_type);

Line: 2508

l_progress:='020'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Document ID = '||l_document_id);

Line: 2515

    	SELECT acceptance_required_flag
					INTO   l_acceptance_flag
					FROM   po_headers_merge_v
					WHERE  po_header_id = l_document_id
					AND draft_id = l_draft_id;

Line: 2523

l_progress:='030'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Acceptance Required Flag = '||l_acceptance_flag);

Line: 2532

		l_progress:='040'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Signature Required = TRUE');

Line: 2535

		l_progress:='050'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Signature Required = FALSE');

Line: 2543

        PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||SQLERRM);

Line: 2582

      /*SELECT 'Y'
        INTO l_signatures
        FROM dual
       WHERE EXISTS (SELECT 1
                       FROM PO_ACTION_HISTORY
                      WHERE object_id = p_document_id
                        AND object_type_code IN ('PO','PA')
                        AND action_code = 'SIGNED');*/

Line: 2592

 	        SELECT 'Y'
 	        INTO l_signatures
 	        FROM dual
 	        WHERE EXISTS (SELECT 1
 	                        FROM PO_ACCEPTANCES
 	                       WHERE po_header_id = p_document_id
 	                         AND accepting_party ='S'
 	                         AND action = 'Accepted');

Line: 2729

			 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 2747

			SELECT employee_id
			 INTO l_contracting_officer
			FROM fnd_user
			WHERE user_name = WF_ENGINE.context_new_role;

Line: 2756

		--Bug11068447 . If the KO signature notification is forwarded or transferred to a new contracting officer then the document must be updated with the new KO.
		IF l_draft_id <> -1 THEN

			UPDATE po_headers_draft_all
			 SET clm_contract_officer = l_contracting_officer
			WHERE po_header_id = l_document_id
			AND draft_id = l_draft_id
			AND clm_contract_officer <> l_contracting_officer;

Line: 2766

			UPDATE po_headers_all
			 SET clm_contract_officer = l_contracting_officer
			WHERE po_header_id = l_document_id
			AND clm_contract_officer <> l_contracting_officer;

Line: 2841

			 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 2859

			SELECT employee_id
			 INTO l_contracting_officer
			FROM fnd_user
			WHERE user_name = WF_ENGINE.context_new_role;

Line: 2868

		--Bug11068447 . If the KO signature notification is forwarded or transferred to a new contracting officer then the document must be updated with the new KO.
		IF l_draft_id <> -1 THEN

			UPDATE po_headers_draft_all
			 SET clm_contract_officer = l_contracting_officer
			WHERE po_header_id = l_document_id
			AND draft_id = l_draft_id
			AND clm_contract_officer <> l_contracting_officer;

Line: 2878

			UPDATE po_headers_all
			 SET clm_contract_officer = l_contracting_officer
			WHERE po_header_id = l_document_id
			AND clm_contract_officer <> l_contracting_officer;

Line: 2926

PROCEDURE Update_Po_Details(p_po_header_id        IN NUMBER,
                            p_status              IN VARCHAR2,
                            p_action_code         IN VARCHAR2,
                            p_object_type_code    IN VARCHAR2,
                            p_object_subtype_code IN VARCHAR2,
                            p_employee_id         IN NUMBER,
                            p_revision_num        IN NUMBER
                            ) IS

  l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PO_DETAILS'; -- Bug 3602512

Line: 2945

  d_module CONSTANT  VARCHAR2(70) := 'po.plsql.PO_SIGNATURE_PVT.UPDATE_PO_DETAILS';

Line: 2963

        /*Added NVL condition for approved_flag to update the value after buyer signed the
        document.*/

        UPDATE PO_LINE_LOCATIONS_ALL
           SET approved_flag = 'Y',
               approved_date = sysdate,
               last_updated_by = fnd_global.user_id,
               last_update_login = fnd_global.login_id,
               last_update_date = sysdate
         WHERE po_header_id = p_po_header_id
           AND NVL(cancel_flag,'N') = 'N'
           AND NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
           -- : Include PREPAYMENT shipment_type
           AND shipment_type IN ('STANDARD','BLANKET','SCHEDULED','PREPAYMENT')
           AND NVL(approved_flag,'N') <> 'Y';

Line: 2992

    UPDATE PO_HEADERS_ALL
       SET authorization_status      = p_status,
           approved_flag             = l_approved_flag,
           pending_signature_flag    = 'N',
           acceptance_required_flag  = 'N',
           acceptance_due_date       = Null,
           last_updated_by           = FND_GLOBAL.user_id,
           last_update_login         = FND_GLOBAL.login_id,
           last_update_date          = sysdate
     WHERE po_header_id = p_po_header_id;

Line: 3014

PO_ACTION_HISTORY_SV.insert_action_history(
   p_doc_id_tbl            => po_tbl_number(p_po_header_id)
,  p_doc_type_tbl          => po_tbl_varchar30(p_object_type_code)
,  p_doc_subtype_tbl       => po_tbl_varchar30(p_object_subtype_code)
,  p_doc_revision_num_tbl  => po_tbl_number(p_revision_num)
,  p_action_code_tbl       => po_tbl_varchar30(p_action_code)
,  p_employee_id           => p_employee_id -- bug3738420
);

Line: 3085

    PO_DELREC_PVT.create_update_delrec (
      p_api_version => 1.0,
      x_return_status => l_return_status,
      x_msg_count => l_msg_count,
      x_msg_data => l_msg_data,
      p_action => 'APPROVE',
      p_doc_type => p_object_type_code,
      p_doc_subtype => p_object_subtype_code,
      p_doc_id => p_po_header_id,
      p_line_id => NULL,
      p_line_location_id => NULL
    );

Line: 3124

END UPDATE_PO_DETAILS;

Line: 3160

PROCEDURE Update_Po_Details(p_po_header_id        IN NUMBER,
							p_draft_id			  IN NUMBER,		-- CLM Apprvl
                            p_status              IN VARCHAR2,
                            p_action_code         IN VARCHAR2,
                            p_object_type_code    IN VARCHAR2,
                            p_object_subtype_code IN VARCHAR2,
                            p_employee_id         IN NUMBER,
                            p_revision_num        IN NUMBER
                            ) IS
	pragma AUTONOMOUS_TRANSACTION; --bug 9924585 making this an autonomous transaction

Line: 3171

  l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PO_DETAILS'; -- Bug 3602512

Line: 3191

		UPDATE PO_HEADERS_DRAFT_ALL
		   SET pending_signature_flag    = 'N',
			   --acceptance_required_flag  = 'N',
			   acceptance_due_date       = Null,
			   last_updated_by           = FND_GLOBAL.user_id,
			   last_update_login         = FND_GLOBAL.login_id,
			   last_update_date          = sysdate
		 WHERE po_header_id = p_po_header_id
		 AND draft_id = p_draft_id;

Line: 3201

		UPDATE PO_HEADERS_ALL
		   SET pending_signature_flag    = 'N',
			   acceptance_required_flag  = 'N',
			   acceptance_due_date       = Null,
			   last_updated_by           = FND_GLOBAL.user_id,
			   last_update_login         = FND_GLOBAL.login_id,
			   last_update_date          = sysdate
		 WHERE po_header_id = p_po_header_id;

Line: 3223

	PO_ACTION_HISTORY_SV.insert_action_history(
	   p_doc_id_tbl            => po_tbl_number(p_draft_id)
	,  p_doc_type_tbl          => po_tbl_varchar30(p_object_type_code)
	,  p_doc_subtype_tbl       => po_tbl_varchar30(p_object_subtype_code)
	,  p_doc_revision_num_tbl  => po_tbl_number(p_revision_num)
	,  p_action_code_tbl       => po_tbl_varchar30(p_action_code)
	,  p_employee_id           => p_employee_id -- bug3738420
	);

Line: 3233

	PO_ACTION_HISTORY_SV.insert_action_history(
	   p_doc_id_tbl            => po_tbl_number(p_po_header_id)
	,  p_doc_type_tbl          => po_tbl_varchar30(p_object_type_code)
	,  p_doc_subtype_tbl       => po_tbl_varchar30(p_object_subtype_code)
	,  p_doc_revision_num_tbl  => po_tbl_number(p_revision_num)
	,  p_action_code_tbl       => po_tbl_varchar30(p_action_code)
	,  p_employee_id           => p_employee_id -- bug3738420
	);

Line: 3260

END UPDATE_PO_DETAILS;

Line: 3299

    SELECT to_char(PO_WF_ITEMKEY_S.NEXTVAL)
    INTO l_seq_for_item_key
    FROM sys.dual;

Line: 3354

    SELECT item_key
      INTO l_itemkey
      FROM WF_ITEMS
     WHERE item_type = 'POAPPRV'
       AND item_key LIKE l_itemkey_like
       AND end_date IS NULL;

Line: 3400

    SELECT item_key
      INTO l_itemkey
      FROM WF_ITEMS
     WHERE item_type = 'POAPPRV'
       AND item_key = p_itemkey
       AND end_date IS NULL;

Line: 3454

        SELECT WPA.activity_name
          INTO l_activity_name
          FROM WF_PROCESS_ACTIVITIES WPA,
               WF_ITEM_ACTIVITY_STATUSES WIA
         WHERE WIA.item_type        = 'POAPPRV'
           AND WIA.item_key         = p_itemkey
           AND WIA.process_activity = WPA.INSTANCE_ID
           AND WPA.activity_name   IN ('BLOCK_PREAPP','BLOCK_CHGAPP')
           AND WIA.activity_status  = 'NOTIFIED';

Line: 3520

        SELECT object_revision_num
          FROM PO_ACTION_HISTORY PAH
         WHERE PAH.object_id = p_po_header_id
           AND PAH.object_type_code IN ('PO','PA')
           AND (
                (PAH.action_code = 'SIGNED')
                            OR
                (PAH.action_code = 'APPROVE'
                     and
                   not exists (
                      SELECT 1
                        FROM PO_ACTION_HISTORY PAH1
                       WHERE PAH1.object_id = PAH.object_id
                         AND PAH1.object_type_code = pah.object_type_code
                         AND PAH1.action_code IN ('BUYER REJECTED','SUPPLIER REJECTED')
                         AND PAH1.object_revision_num = PAH.object_revision_num
                   )
                 )
               )
           AND PAH.object_revision_num < p_revision_num
      ORDER BY object_revision_num DESC;

Line: 3640

            SELECT NVL(pending_signature_flag,'N')
              INTO x_pending_signature
              FROM PO_HEADERS_ALL
             WHERE po_header_id = p_po_header_id
               AND nvl(user_hold_flag, 'N') <> 'Y';

Line: 3658

            SELECT 'Y'
              INTO x_erecord_exist
              FROM dual
             WHERE EXISTS (SELECT 1
                             FROM PO_ACCEPTANCES
                            WHERE po_header_id = p_po_header_id
                              AND revision_num = p_revision_num
                              AND signature_flag = 'Y'
                              AND erecord_id IS NOT NULL);

Line: 3744

      SELECT SUM(Decode(Accepting_Party,'B',Decode(Accepted_Flag,'Y',1,0))) Buyer_Accepted,
             SUM(Decode(Accepting_Party,'B',Decode(Accepted_Flag,'Y',0,1))) Buyer_Rejected,
             SUM(Decode(Accepting_Party,'S',Decode(Accepted_Flag,'Y',1,0))) Supplier_Accepted,
             SUM(Decode(Accepting_Party,'S',Decode(Accepted_Flag,'Y',0,1))) Supplier_Rejected
        INTO l_buyer_accepted_count,
             l_buyer_rejected_count,
             l_supplier_accepted_count,
             l_supplier_rejected_count
        FROM PO_ACCEPTANCES
       WHERE Po_Header_Id = p_po_header_id
         AND Revision_Num = p_revision_num
         AND Signature_Flag = 'Y';

Line: 3771

      SELECT
         Type_Lookup_Code
      ,  DECODE(  type_lookup_code
               ,  PO_CONSTANTS_SV.BLANKET, PO_CONSTANTS_SV.PA
               ,  PO_CONSTANTS_SV.CONTRACT, PO_CONSTANTS_SV.PA
               ,  PO_CONSTANTS_SV.PO
               )
      ,  Agent_Id,
             wf_item_type,
             wf_item_key
        INTO l_type_lookup_code,
             l_object_code,
             l_agent_id,
             l_po_itemtype,
             l_po_itemkey
        FROM PO_HEADERS_ALL
       WHERE Po_Header_Id = p_po_header_id;

Line: 3804

        update_po_details(
                  p_po_header_id        => p_po_header_id,
                  p_status              => 'REJECTED',
                  p_action_code         => 'BUYER REJECTED',
                  p_object_type_code    => l_object_code,
                  p_object_subtype_code => l_type_lookup_code,
                  p_employee_id         => l_employee_id,    -- bug3738420
                  p_revision_num        => p_revision_num);

Line: 3859

        update_po_details(
                  p_po_header_id        => p_po_header_id,
                  p_status              => 'APPROVED',
                  p_action_code         => 'SIGNED',
                  p_object_type_code    => l_object_code,
                  p_object_subtype_code => l_type_lookup_code,
                  p_employee_id         => l_employee_id,   -- bug3738420
                  p_revision_num        => p_revision_num);

Line: 3877

          SELECT max(action_date)
          INTO l_acceptance_date
          FROM PO_ACCEPTANCES
          WHERE Po_Header_Id = p_po_header_id
            AND Revision_Num = p_revision_num
            AND Signature_Flag = 'Y'
            AND ACCEPTING_PARTY IN ('B','S')
            AND ACCEPTED_FLAG= 'Y';

Line: 3890

      PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
                p_po_header_id      => p_po_header_id,
                p_signed_date       => l_acceptance_date,
    		    x_return_status     => l_return_status,
                x_msg_data          => l_msg_data,
                x_msg_count         => l_msg_count);

Line: 3982

        SELECT Count(Signature_Flag)
          INTO l_no_signatures
          FROM PO_ACCEPTANCES
         WHERE po_header_id = p_po_header_id
           AND revision_num = p_revision_num
           AND signature_flag = 'Y'
           AND accepting_party = 'B' --bug 3420562
           AND erecord_id IS NULL;

Line: 4010

        SELECT Count(Signature_Flag)
          INTO l_no_signatures
          FROM PO_ACCEPTANCES
         WHERE po_header_id = p_po_header_id
           AND revision_num = p_revision_num
           AND signature_flag = 'Y'
           AND erecord_id IS NOT NULL;

Line: 4065

    SELECT DECODE(accepted_flag, 'N', 'Y', 'N')
    INTO x_supplier_rejected
    FROM po_acceptances
    WHERE po_header_id = p_po_header_id
      AND revision_num = p_revision_num
      AND accepting_party = 'S'
      AND signature_flag = 'Y';

Line: 4085

    SELECT 'Y'
    INTO x_buyer_rejected
    FROM po_acceptances
    WHERE po_header_id = p_po_header_id
      AND revision_num = p_revision_num
      AND accepting_party = 'B'
      AND accepted_flag = 'N'
      AND signature_flag= 'Y';

Line: 4136

                                        x_if_acc_flag_updated  OUT NOCOPY VARCHAR2)
IS
  l_was_sign_reqd boolean := FALSE;

Line: 4145

  x_if_acc_flag_updated := 'N';

Line: 4153

    UPDATE PO_HEADERS_ALL POH
    SET POH.acceptance_required_flag = 'S'
    WHERE POH.po_header_id = p_document_id;

Line: 4156

    x_if_acc_flag_updated := 'Y';

Line: 4161

    PO_LOG.proc_end(d_module, 'x_if_acc_flag_updated', x_if_acc_flag_updated);

Line: 4202

            x_if_acc_flag_updated OUT NOCOPY VARCHAR2)
IS
  d_module   CONSTANT        VARCHAR2(70) := 'po.plsql.PO_SIGNATURE_PVT.if_rev_and_signed_set_acc_flag';

Line: 4208

  SELECT revision_num
  INTO l_revision_num
  FROM po_headers_all
  WHERE po_header_id = p_document_id;

Line: 4218

      x_if_acc_flag_updated => x_if_acc_flag_updated);

Line: 4220

    x_if_acc_flag_updated := 'N';

Line: 4279

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

Line: 4313

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

Line: 4319

                select 'Y'
                into l_is_doc_signed
                from po_acceptances
                where po_header_id = l_document_id
                and draft_id = l_draft_id
                and action = 'Accepted'
                and rownum=1;

Line: 4333

                select 'Y'
                into l_is_doc_signed
                from po_acceptances
                where po_header_id = l_document_id
                and action = 'Accepted'
                and rownum=1;

Line: 4350

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

Line: 4409

l_progress:='000'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress);

Line: 4446

l_progress:='010'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' Document Type = '||l_document_type);

Line: 4447

l_progress:='020'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' Document ID = '||l_document_id);

Line: 4449

l_progress:='040'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' Draft ID = '||l_draft_id);

Line: 4457

				SELECT acceptance_required_flag
					INTO   l_acceptance_flag
					FROM   po_headers_merge_v
					WHERE  po_header_id = l_document_id
					AND draft_id = l_draft_id;

Line: 4463

			  SELECT acceptance_required_flag
					INTO   l_acceptance_flag
					FROM   po_headers_merge_v
					WHERE  po_header_id = l_document_id;

Line: 4470

l_progress:='030'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' Acceptance Required Flag = '||l_acceptance_flag);

Line: 4476

				l_progress:='040'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' l_acceptance_mode ='||l_acceptance_mode);

Line: 4479

				l_progress:='050'; PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||l_progress||' l_acceptance_mode = '||l_acceptance_mode);

Line: 4487

        PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||SQLERRM);

Line: 4541

		 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 4573

		  l_edit_po_url := po_reqapproval_init1.get_mod_url(p_po_header_id => l_document_id,p_draft_id => l_draft_id, p_doc_subtype => l_doc_subtype, p_mode => 'update');

Line: 4575

		  l_edit_po_url := po_reqapproval_init1.get_po_url(p_po_header_id => l_document_id, p_doc_subtype => l_doc_subtype, p_mode => 'update');

Line: 4585

	 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||SQLERRM);

Line: 4634

  l_user_id    po_lines.last_updated_by%TYPE := -1;

Line: 4635

  l_login_id   po_lines.last_update_login%TYPE := -1;

Line: 4643

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 4667

		select employee_id
		 into l_contracting_officer
		from fnd_user
		where user_name = l_ko_user_name;

Line: 4689

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 4741

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 4800

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 4905

l_user_id    po_lines.last_updated_by%TYPE := -1;

Line: 4906

l_login_id   po_lines.last_update_login%TYPE := -1;

Line: 4915

		 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 4930

	PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'l_forward_to_username: '||l_forward_to_username);

Line: 4961

		select employee_id
		 into l_contracting_officer
		from fnd_user
		where user_name = l_ko_user_name;

Line: 4970

	--Bug11068447 . If the KO signature notification is forwarded or transferred to a new contracting officer then the document must be updated with the new KO.
	IF l_draft_id <> -1 THEN

		UPDATE po_headers_draft_all
		 SET clm_contract_officer = l_contracting_officer
		WHERE po_header_id = l_document_id
		AND draft_id = l_draft_id
		AND clm_contract_officer <> l_contracting_officer;

Line: 4980

		UPDATE po_headers_all
		 SET clm_contract_officer = l_contracting_officer
		WHERE po_header_id = l_document_id
		AND clm_contract_officer <> l_contracting_officer;

Line: 5000

     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,'PO_SIGNATURE_PVT.Check_Warrant_for_KO: l_return_status: '||l_return_status);

Line: 5021

        UPDATE  po_headers_all
        SET     pending_signature_flag = 'C'
        WHERE   po_header_id = l_document_id;

Line: 5031

        UPDATE  po_headers_draft_all
        SET     pending_signature_flag = 'C'
        WHERE   po_header_id = l_document_id
        AND     draft_id = l_draft_id;

Line: 5043

	 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head||':'||SQLERRM);