DBA Data[Home] [Help]

APPS.PO_VENDORMERGE_GRP SQL Statements

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

Line: 51

         select distinct pad.autosource_rule_id,
                pad.sequence_num,
                pad.document_line_id
         from   po_autosource_documents pad
         where  pad.vendor_id = p_dup_vendor_id;
Line: 65

	 l_last_updated_by    number;
Line: 88

         l_last_updated_by := FND_GLOBAL.user_id;
Line: 109

      UPDATE po_headers_draft_all pohd
      SET    pohd.vendor_id = p_vendor_id,
             pohd.vendor_site_id = p_vendor_site_id,
             pohd.vendor_contact_id = (SELECT vendor_contact_id
                                       FROM   ap_supplier_contacts
                                       WHERE org_party_site_id = (SELECT party_site_id
                                                                  FROM   ap_supplier_sites_all
                                                                  WHERE vendor_site_id = p_vendor_site_id)
                                                                 AND per_party_id = (SELECT per_party_id
                                                                                      FROM   ap_supplier_contacts
                                                                                     WHERE vendor_contact_id = pohd.vendor_contact_id)),
             pohd.last_updated_by = l_last_updated_by,
             pohd.last_update_date = sysdate
      WHERE  pohd.vendor_id = p_dup_vendor_id
      AND pohd.vendor_site_id = p_dup_vendor_site_id
      AND NOT EXISTS (SELECT 'Exclude CLM Document'
                      FROM   po_doc_style_headers pods
                      WHERE  pods.style_id = pohd.style_id
                             AND NVL(pods.CLM_FLAG,'N') = 'Y')
      AND EXISTS (  SELECT 'Exclude draft_type is not equal to MOD'
                        FROM po_drafts dft
                        WHERE dft.document_id=pohd.po_header_id
                        AND   dft.draft_id= pohd.draft_id
                        AND   dft.draft_type='MOD');
Line: 135

      UPDATE  PO_GA_ORG_ASSIGN_DRAFT PGOA
      SET     PGOA.vendor_site_id = p_vendor_site_id,
              PGOA.last_update_date = SYSDATE,
              PGOA.last_updated_by = l_last_updated_by,
              PGOA.last_update_login = FND_GLOBAL.login_id
      WHERE   PGOA.vendor_site_id = p_dup_vendor_site_id
      AND NOT EXISTS (SELECT 'Exclude CLM Document'
                                  FROM   po_doc_style_headers pods,
                                         po_headers ph
                                  WHERE  pods.style_id = ph.style_id
                                    AND  ph.PO_HEADER_ID= pgoa.PO_HEADER_ID
                                    AND  NVL(pods.CLM_FLAG,'N') = 'Y')
      AND EXISTS (  SELECT 'Exclude draft_type is not equal to MOD'
                        FROM po_drafts dft
                        WHERE dft.document_id=pgoa.po_header_id
                        AND   dft.draft_id= pgoa.draft_id
                        AND   dft.draft_type='MOD');
Line: 156

      UPDATE po_headers ph
      SET    ph.vendor_id = p_vendor_id,
             ph.vendor_site_id = p_vendor_site_id,
             ph.vendor_contact_id = (SELECT vendor_contact_id
                                     FROM   ap_supplier_contacts
                                     WHERE org_party_site_id = (SELECT party_site_id
                                                                FROM   ap_supplier_sites_all
                                                                WHERE vendor_site_id = p_vendor_site_id)
                                     AND per_party_id = (SELECT per_party_id
                                                                FROM   ap_supplier_contacts
                                                               WHERE vendor_contact_id = ph.vendor_contact_id)),
           last_updated_by = l_last_updated_by,
           last_update_date = SYSDATE
      WHERE  ph.vendor_id = p_dup_vendor_id
      AND ph.vendor_site_id = p_dup_vendor_site_id
      AND NOT EXISTS (SELECT 'CLM Document'  --
                      FROM   po_doc_style_headers pods
                      WHERE  pods.style_id = ph.style_id
                        AND  NVL(pods.CLM_FLAG,'N') = 'Y');
Line: 177

          UPDATE po_rfq_vendors
          SET    vendor_id        = p_vendor_id,
                 vendor_site_id   = p_vendor_site_id,
                 last_updated_by  = l_last_updated_by,
                 last_update_date = sysdate
       	 WHERE  vendor_id      = p_dup_vendor_id
         AND    vendor_site_id = p_dup_vendor_site_id ;
Line: 185

          DELETE from po_rfq_vendors prv
          WHERE  vendor_id = p_dup_vendor_id
          AND    vendor_site_id = p_dup_vendor_site_id;
Line: 193

         UPDATE po_headers_archive pha
         SET    pha.vendor_id      = p_vendor_id,
          	pha.vendor_site_id = p_vendor_site_id
       	 WHERE  pha.vendor_id      = p_dup_vendor_id
         AND    pha.vendor_site_id = p_dup_vendor_site_id
         AND NOT EXISTS (SELECT 'CLM Document' --
                         FROM   po_doc_style_headers pods
                         WHERE  pods.style_id = pha.style_id
                           AND  NVL(pods.CLM_FLAG,'N') = 'Y');
Line: 204

          PO_LOG.stmt(d_module, d_progress, 'updated PO_HEADERS_ARCHIVE');
Line: 210

         UPDATE po_vendor_list_entries pv1
         SET    pv1.vendor_id      = p_vendor_id,
            	pv1.vendor_site_id = p_vendor_site_id
         WHERE  pv1.vendor_id      = p_dup_vendor_id
         AND    pv1.vendor_site_id = p_dup_vendor_site_id
         AND    not exists
  			(select vendor_id
                         from po_vendor_list_entries pv2
                         where pv2.vendor_id      = p_vendor_id
                         and pv2.vendor_site_id   = p_vendor_site_id
                         and pv2.vendor_list_header_id =
                                pv1.vendor_list_header_id);
Line: 231

         DELETE from po_vendor_list_entries pvl
         WHERE  vendor_id      = p_dup_vendor_id
         AND    vendor_site_id = p_dup_vendor_site_id ;
Line: 237

          PO_LOG.stmt(d_module, d_progress, 'updated PO_VENDOR_LIST_ENTRIES');
Line: 241

         UPDATE po_autosource_vendors pav1
         SET    pav1.vendor_id      = p_vendor_id
         WHERE  pav1.vendor_id      = p_dup_vendor_id
         AND    not exists
 		(select vendor_id
                from po_autosource_vendors pav2
                where pav2.vendor_id      = p_vendor_id
                and pav2.autosource_rule_id =
                pav1.autosource_rule_id) ;
Line: 253

         UPDATE po_autosource_vendors pav1
         SET pav1.split     = (SELECT sum (pav3.split)
                               FROM   po_autosource_vendors pav3
                               WHERE  pav3.autosource_rule_id =
 				      pav1.autosource_rule_id
                  	       AND    pav3.vendor_id IN
	                              (p_vendor_id, p_dup_vendor_id))
         WHERE  pav1.vendor_id      = p_vendor_id
       	 AND    exists
 		(select pav2.vendor_id
                 from po_autosource_vendors pav2
                 where pav2.vendor_id      = p_dup_vendor_id
                 and pav2.autosource_rule_id =
                                pav1.autosource_rule_id) ;
Line: 278

         DELETE from po_autosource_vendors pavl
         WHERE  vendor_id      = p_dup_vendor_id ;
Line: 284

          PO_LOG.stmt(d_module, d_progress, 'updated PO_AUTOSOURCE_VENDORS');
Line: 298

              select nvl(max(sequence_num),0)
              into   l_max_seq_num
              from   po_autosource_documents
              where  autosource_rule_id  = l_rule_id
              and    vendor_id           = p_vendor_id;
Line: 306

              update po_autosource_documents
              set    vendor_id          = p_vendor_id,
                     sequence_num       = l_new_seq_num
              where  autosource_rule_id = l_rule_id
              and    vendor_id          = p_dup_vendor_id
              and    sequence_num       = l_seq_num
              and    not exists
                    (select 'already have PAD for this rule, vendor, doc line'
                     from   po_autosource_documents
                     where  autosource_rule_id = l_rule_id
                     and    vendor_id = p_vendor_id
                     and    document_line_id = l_doc_line_id);
Line: 329

         delete from po_autosource_documents
         where  vendor_id = p_dup_vendor_id;
Line: 335

          PO_LOG.stmt(d_module, d_progress, 'updated PO_AUTOSOURCE_DOCUMENTS');
Line: 342

        UPDATE  po_ga_org_assignments PGOA
        SET     PGOA.vendor_site_id = p_vendor_site_id,
                PGOA.last_update_date = SYSDATE,
                PGOA.last_updated_by = l_last_updated_by,
                PGOA.last_update_login = FND_GLOBAL.login_id
        WHERE   PGOA.vendor_site_id = p_dup_vendor_site_id
          AND   NOT EXISTS (SELECT 'CLM Document' --
                                  FROM   po_doc_style_headers pods,
                                         po_headers ph
                                  WHERE  pods.style_id = ph.style_id
                                    AND  ph.PO_HEADER_ID= pgoa.PO_HEADER_ID
                                    AND  NVL(pods.CLM_FLAG,'N') = 'Y');
Line: 355

        UPDATE  po_ga_org_assignments_archive PGOA
        SET     PGOA.vendor_site_id = p_vendor_site_id,
                PGOA.last_update_date = SYSDATE,
                PGOA.last_updated_by = l_last_updated_by,
                PGOA.last_update_login = FND_GLOBAL.login_id
        WHERE   PGOA.vendor_site_id = p_dup_vendor_site_id
          AND  NOT EXISTS (SELECT 'CLM Document' --
                                  FROM   po_doc_style_headers pods,
                                         po_headers ph
                                  WHERE  pods.style_id = ph.style_id
                                    AND  ph.PO_HEADER_ID= pgoa.PO_HEADER_ID
                                    AND  NVL(pods.CLM_FLAG,'N') = 'Y');
Line: 368

          PO_LOG.stmt(d_module, d_progress, 'updated PO_GA_ORG_ASSIGNMENTS');
Line: 372

         UPDATE PO_REQUISITION_LINES prl
         SET    prl.suggested_vendor_name =  (select pov1.vendor_name
                                          from   po_vendors pov1
                          		  where  pov1.vendor_id =
         		                         p_vendor_id),
                prl.suggested_vendor_location =  (select pvs1.vendor_site_code
 				              from   po_vendor_sites pvs1
					      where  pvs1.vendor_site_id =
				                     p_vendor_site_id)
         WHERE  prl.suggested_vendor_name in     (select pov2.vendor_name
 					      from   po_vendors pov2
 					      where  pov2.vendor_id =
  					             p_dup_vendor_id)
 	 AND    prl.suggested_vendor_location in (select pvs2.vendor_site_code
 					      from   po_vendor_sites pvs2
 					      where  vendor_site_id =
     						     p_dup_vendor_site_id)
    AND NOT EXISTS (SELECT 'CLM Document'
                          FROM Po_Requisition_Headers_All prha
                          WHERE prha.Requisition_Header_Id = prl.Requisition_Header_Id
                           AND NVL(PRHA.FEDERAL_FLAG,'N')='Y');
Line: 395

         UPDATE po_requisition_lines prl
         SET    prl.vendor_id     = p_vendor_id,
	        prl.vendor_site_id = p_vendor_site_id,
              prl.last_update_date = sysdate,
              prl.last_updated_by  = l_last_updated_by
         WHERE   prl.vendor_id = p_dup_vendor_id
         AND     prl.vendor_site_id = p_dup_vendor_site_id
         AND NOT EXISTS (SELECT 'CLM Document'
                          FROM Po_Requisition_Headers_All prha
                          WHERE prha.Requisition_Header_Id = prl.Requisition_Header_Id
                           AND NVL(PRHA.FEDERAL_FLAG,'N')='Y');
Line: 408

         UPDATE po_requisition_lines prl
       	 SET    prl.vendor_id       = p_vendor_id,
	       prl.last_update_date = sysdate,
               prl.last_updated_by  = l_last_updated_by
       	 WHERE  prl.vendor_id = p_dup_vendor_id
 	 AND    prl.vendor_site_id is null
         AND    exists
        	( select vendor_id
		  from   po_vendors
		  where  vendor_id = p_dup_vendor_id
 		  and    nvl(end_date_active, sysdate+1) <= sysdate)
      AND NOT EXISTS (SELECT 'Exclude CLM Document'
                          FROM Po_Requisition_Headers_All prha
                          WHERE prha.Requisition_Header_Id = prl.Requisition_Header_Id
                           AND NVL(PRHA.FEDERAL_FLAG,'N')='Y');
Line: 425

          PO_LOG.stmt(d_module, d_progress, 'updated PO_REQUISITION_LINES');
Line: 430

       UPDATE  po_reqexpress_lines_all PRL
       SET     PRL.suggested_vendor_id = p_vendor_id,
               PRL.suggested_vendor_site_id = p_vendor_site_id,
               PRL.last_update_date = SYSDATE,
               PRL.last_updated_by = l_last_updated_by
       WHERE   PRL.suggested_vendor_id = p_dup_vendor_id
       AND     PRL.suggested_vendor_site_id = p_dup_vendor_site_id;
Line: 446

       UPDATE  po_reqexpress_lines_all PRL
       SET     PRL.suggested_vendor_id = p_vendor_id,
               last_update_date = SYSDATE,
               last_updated_by = l_last_updated_by
       WHERE   PRL.suggested_vendor_id = p_dup_vendor_id
       AND     PRL.suggested_vendor_site_id IS NULL
       AND     EXISTS (
                   SELECT  NULL
                   FROM    po_vendors PV
                   WHERE   PV.vendor_id = p_dup_vendor_id
                   AND     NVL(PV.end_date_active, SYSDATE + 1) <= SYSDATE);
Line: 460

          PO_LOG.stmt(d_module, d_progress, 'updated PO_REQEXPRESS_LINES_ALL');
Line: 464

         UPDATE po_approved_supplier_list poasl1
         SET    poasl1.vendor_id      = p_vendor_id,
            	poasl1.vendor_site_id = p_vendor_site_id
         WHERE  poasl1.vendor_id      = p_dup_vendor_id
 	 AND    poasl1.vendor_site_id = p_dup_vendor_site_id
         	AND    not exists
  		       ( select vendor_id
                         from   po_approved_supplier_list poasl2
                         where  poasl2.vendor_id      = p_vendor_id
                         and    poasl2.vendor_site_id = p_vendor_site_id
                         and    nvl(poasl2.item_id, -99) =
                                            nvl(poasl1.item_id, -99)
  		         and nvl(poasl2.category_id, -99) =
                                            nvl(poasl1.category_id, -99)
    		         and  poasl2.using_organization_id =
                                            poasl1.using_organization_id) ;
Line: 486

         DELETE from po_approved_supplier_list poasl
  	 WHERE  vendor_id      = p_dup_vendor_id
 	 AND    vendor_site_id = p_dup_vendor_site_id ;
Line: 493

         UPDATE po_approved_supplier_list poasl1
         SET    poasl1.vendor_id      = p_vendor_id
         WHERE  poasl1.vendor_id      = p_dup_vendor_id
     	 AND    poasl1.vendor_site_id is null
         AND    exists
		( select vendor_id
		  from   po_vendors
		  where  vendor_id = p_dup_vendor_id
		  and    nvl(end_date_active, sysdate+1) <= sysdate)
       	 AND    not exists
		( select vendor_id
                  from   po_approved_supplier_list poasl2
                  where  poasl2.vendor_id             = p_vendor_id
                  and    poasl2.vendor_site_id is null
                  and    nvl(poasl2.item_id, -99)     =
                                                nvl(poasl1.item_id, -99)
 		  and    nvl(poasl2.category_id, -99) =
                                                nvl(poasl1.category_id, -99)
		  and    poasl2.using_organization_id =
                                                poasl1.using_organization_id);
Line: 517

         DELETE from po_approved_supplier_list poasl
 	 WHERE  vendor_id      = p_dup_vendor_id
 	 AND    vendor_site_id is null
 	 AND    exists
 		( select vendor_id
 		  from   po_vendors
 		  where  vendor_id = p_dup_vendor_id
 		  and    nvl(end_date_active, sysdate+1) <= sysdate);
Line: 527

          PO_LOG.stmt(d_module, d_progress, 'updated PO_APPROVED_SUPPLIER_LIST');
Line: 531

         UPDATE po_asl_attributes poasl1
         SET    poasl1.vendor_id      = p_vendor_id,
        	poasl1.vendor_site_id = p_vendor_site_id
         WHERE  poasl1.vendor_id      = p_dup_vendor_id
    	 AND    poasl1.vendor_site_id = p_dup_vendor_site_id
       	 AND    not exists
		(select vendor_id
                 from po_asl_attributes poasl2
                 where poasl2.vendor_id           = p_vendor_id
                 and poasl2.vendor_site_id        = p_vendor_site_id
                 and nvl(poasl2.item_id, -99)     = nvl(poasl1.item_id, -99)
    	         and nvl(poasl2.category_id, -99) = nvl(poasl1.category_id, -99)
 	         and poasl2.using_organization_id =
 					poasl1.using_organization_id);
Line: 553

         DELETE from po_asl_attributes poasl
         WHERE  vendor_id      = p_dup_vendor_id
	 AND    vendor_site_id = p_dup_vendor_site_id ;
Line: 560

         UPDATE po_asl_attributes poasl1
         SET    poasl1.vendor_id      = p_vendor_id
         WHERE  poasl1.vendor_id      = p_dup_vendor_id
	 AND    poasl1.vendor_site_id is null
         AND    exists
		( select vendor_id
		  from   po_vendors
		  where  vendor_id = p_dup_vendor_id
		  and    nvl(end_date_active, sysdate+1) <= sysdate)
       	AND    not exists
		(select vendor_id
                 from po_asl_attributes poasl2
                 where poasl2.vendor_id      = p_vendor_id
                 and poasl2.vendor_site_id is null
                 and nvl(poasl2.item_id, -99) = nvl(poasl1.item_id, -99)
   	         and nvl(poasl2.category_id, -99) = nvl(poasl1.category_id, -99)
                 and poasl2.using_organization_id =
 					poasl1.using_organization_id);
Line: 582

         DELETE from po_asl_attributes poasl
         WHERE  vendor_id      = p_dup_vendor_id
 	 AND    vendor_site_id is null
         AND    exists
 		( select vendor_id
     	          from   po_vendors
 		  where  vendor_id = p_dup_vendor_id
 		  and    nvl(end_date_active, sysdate+1) <= sysdate) ;
Line: 592

          PO_LOG.stmt(d_module, d_progress, 'updated PO_ASL_ATTRIBUTES');
Line: 605

	 FND_MESSAGE.SET_TOKEN('ROWS_DELETED',l_row_count);