APPS.MSC_CL_POST_PUBLISH SQL Statements

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

Line: 8

select plan_id,
	   inventory_item_id,
	   company_id,
	   company_site_id,
	   exception_group,
	   exception_type,
	   count(*)
from   msc_x_exception_details
where  plan_id = -1
and    exception_group IN (1,2,4,6,7,8)
group by plan_id,
	     inventory_item_id,
		 company_id,
		 company_site_id,
		 exception_group,
		 exception_type;

Line: 50

PROCEDURE UPDATE_EXCEPTION_SUMMARY(p_summary_status OUT NOCOPY NUMBER) IS
BEGIN

  BEGIN

      OPEN excepSummary;

Line: 69

       LOG_MESSAGE('Delete Exceptions : Error while fetching exception summary');

Line: 78

       LOG_MESSAGE('Updated Exception Summary - '||a_plan_id.COUNT);

Line: 84

               update msc_item_exceptions
               set  exception_count = a_count(i)
               where plan_id = a_plan_id(i)
               and   company_id = a_company_id(i)
               and   company_site_id = a_company_site_id(i)
               and   inventory_item_id = a_inventory_item_id(i)
               and   exception_type = a_exception_type(i)
               and   exception_group = a_exception_group(i)
               and   version = 0;

Line: 97

           LOG_MESSAGE('Delete Exceptions : Error while updating Exception Summary');

Line: 108

END UPDATE_EXCEPTION_SUMMARY;

Line: 137

		' delete msc_x_exception_details '
	    ||' where exception_type  '|| G_EXCEP_TYPES
	    ||' and exception_group  '|| G_EXCEP_GROUPS
	    ||' and company_id = 1 '
	    ||' and company_site_id  '|| l_site_string
	    ||' and plan_id = -1 ';

Line: 165

	    ' delete msc_x_exception_details '
	    ||' where exception_type '|| G_EXCEP_TYPES
	    ||' and exception_group '|| G_EXCEP_GROUPS
	    ||' and customer_id = 1 '
	    ||' and customer_site_id '||l_site_string
	    ||' and plan_id = -1';

Line: 196

    	' delete msc_x_exception_details '
	    ||' where exception_type '|| G_EXCEP_TYPES
	    ||' and exception_group '|| G_EXCEP_GROUPS
	    ||' and supplier_id = 1 '
	    ||' and supplier_site_id '|| l_site_string
		||' and plan_id = -1';

Line: 223

   delete msc_item_exceptions mie
   where
   plan_id = -1
   and exception_group in (1,2,4,6,7,8)
   and not exists( select 1
				   from msc_x_exception_details med
				   where med.company_id = mie.company_id
				   and   med.company_site_id = mie.company_site_id
				   and   med.plan_id = mie.plan_id
				   and   med.plan_id = -1
				   and   med.inventory_item_id = mie.inventory_item_id
				   and   med.exception_type = mie.exception_type
				   and   med.exception_group = mie.exception_group);

Line: 268

	   LOG_MESSAGE('Delete Exceptions : Error while fetching exception summary');

Line: 277

	   LOG_MESSAGE('Updated Exception Summary - '||a_plan_id.COUNT);

Line: 283

               update msc_item_exceptions
               set  exception_count = a_count(i)
			   where plan_id = a_plan_id(i)
			   and   company_id = a_company_id(i)
			   and   company_site_id = a_company_site_id(i)
			   and   inventory_item_id = a_inventory_item_id(i)
			   and   exception_type = a_exception_type(i)
			   and   exception_group = a_exception_group(i)
			   and	 version = 0;

Line: 296

		   LOG_MESSAGE('Delete Exceptions : Error while updating Exception Summary');

Line: 310

	-- Delete exceptions which do not have transaction_ids present in
	-- msc_sup_dem_entries
	--===============================================================

	v_sql_stmt := null;

Line: 319

	    ' delete msc_x_exception_details med '
	    ||' where transaction_id1 is not null'
        ||' and   exception_type  '||G_DUPLICATE_EXCEP_TYPES
	    ||' and   exception_group  '||G_EXCEP_GROUPS
	    ||' and   not exists ( select 1 '
        ||'					   from msc_sup_dem_entries msde'
        ||'					   where msde.transaction_id = med.transaction_id1)';

Line: 329

        LOG_MESSAGE('No. of deleted exceptions where first transaction does not present in CP transactions - '||SQL%ROWCOUNT);

Line: 347

        ' delete msc_x_exception_details med '
	    ||' where transaction_id2 is not null '
        ||' and exception_type  '||G_DUPLICATE_EXCEP_TYPES
	    ||' and exception_group  '||G_EXCEP_GROUPS
	    ||' and not exists ( select 1 '
	    ||' 				 from msc_sup_dem_entries msde '
        ||'					 where msde.transaction_id = med.transaction_id2)';

Line: 357

        LOG_MESSAGE('No. of deleted exceptions where second transaction does not present in CP transactions - '||SQL%ROWCOUNT);

Line: 369

    UPDATE_EXCEPTION_SUMMARY(l_summary_status);