APPS.AML_PURGE_SALES_LEADS SQL Statements

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

Line: 30

RECS_DELETED del_count;

Line: 49

   Recs_deleted(1)  := 0;

Line: 50

   Recs_deleted(2)  := 0;

Line: 51

   Recs_deleted(3)  := 0;

Line: 52

   Recs_deleted(4)  := 0;

Line: 53

   Recs_deleted(5)  := 0;

Line: 54

   Recs_deleted(6)  := 0;

Line: 56

   Recs_deleted(8)  := 0;

Line: 57

   Recs_deleted(9)  := 0;

Line: 58

   Recs_deleted(10) := 0;

Line: 59

   Recs_deleted(11) := 0;

Line: 63

l_no_of_rows_deleted  CONSTANT varchar2(100) := ' rows deleted from the table ';

Line: 65

   aml_Debug(1,Recs_deleted(1)||l_no_of_rows_deleted||'AS_ACCESSES_ALL');

Line: 66

   aml_debug(1,Recs_deleted(2)||l_no_of_rows_deleted||'AS_CHANGED_ACCOUNTS_ALL');

Line: 67

   aml_debug(1,Recs_deleted(3)||l_no_of_rows_deleted||'AML_INTERACTION_LEADS');

Line: 68

   aml_debug(1,Recs_deleted(4)||l_no_of_rows_deleted||'AS_SALES_LEADS_LOG');

Line: 69

   aml_debug(1,Recs_deleted(5)||l_no_of_rows_deleted||'AS_SALES_LEAD_CONTACTS');

Line: 70

   aml_debug(1,Recs_deleted(6)||l_no_of_rows_deleted||'AS_SALES_LEAD_LINES');

Line: 72

   aml_debug(1,Recs_deleted(8)||l_no_of_rows_deleted||'AML_MONITOR_LOG');

Line: 73

   aml_debug(1,Recs_deleted(9)||l_no_of_rows_deleted||'PV_ENTITY_RULES_APPLIED');

Line: 74

   aml_debug(1,Recs_deleted(10)||l_no_of_rows_deleted||'AS_TERRITORY_ACCESSES');

Line: 75

   aml_debug(1,Recs_deleted(11)||l_no_of_rows_deleted||'AS_SALES_LEADS');

Line: 79

PROCEDURE Delete_Unqualified_Leads(P_Id_Tab IN AML_PURGE_SALES_LEADS.Sales_Lead_Id_Tab) IS
BEGIN

   FORALL I IN P_Id_Tab.FIRST..P_Id_Tab.LAST
      DELETE
	FROM AS_ACCESSES_ALL
       WHERE Sales_Lead_Id = P_Id_Tab(I);

Line: 86

      Recs_deleted(1) := Recs_deleted(1) + SQL%ROWCOUNT;

Line: 89

       DELETE
	 FROM AS_CHANGED_ACCOUNTS_ALL
	WHERE Sales_Lead_Id = P_Id_Tab(I);

Line: 92

       Recs_deleted(2) := Recs_deleted(2) + SQL%ROWCOUNT;

Line: 95

       DELETE
	 FROM AML_INTERACTION_LEADS
	WHERE Sales_Lead_Id = P_Id_Tab(I);

Line: 98

      Recs_deleted(3) := Recs_deleted(3) + SQL%ROWCOUNT;

Line: 101

       DELETE
	 FROM AS_SALES_LEADS_LOG
	WHERE Sales_Lead_Id = P_Id_Tab(I);

Line: 104

       Recs_deleted(4) := Recs_deleted(4) + SQL%ROWCOUNT;

Line: 107

       DELETE
	 FROM AS_SALES_LEAD_CONTACTS
	WHERE Sales_Lead_Id = P_Id_Tab(I);

Line: 110

       Recs_deleted(5) := Recs_deleted(5) + SQL%ROWCOUNT;

Line: 113

       DELETE
	 FROM AS_SALES_LEAD_LINES
	WHERE Sales_Lead_Id = P_Id_Tab(I);

Line: 116

       Recs_deleted(6) := Recs_deleted(6) + SQL%ROWCOUNT;

Line: 119

       DELETE
	 FROM AS_SALES_LEAD_OPPORTUNITY
	WHERE Sales_Lead_Id = P_Id_Tab(I);

Line: 122

       Recs_deleted(7) := Recs_deleted(7) + SQL%ROWCOUNT;*/

Line: 125

       DELETE
	 FROM AML_MONITOR_LOG
	WHERE Sales_Lead_Id = P_Id_Tab(I);

Line: 128

       Recs_deleted(8) := Recs_deleted(8) + SQL%ROWCOUNT;

Line: 131

       DELETE
	 FROM PV_ENTITY_RULES_APPLIED
	WHERE Entity_Id = P_Id_Tab(I);

Line: 134

       Recs_deleted(9) := Recs_deleted(9) + SQL%ROWCOUNT;

Line: 137

       DELETE
	 FROM AS_TERRITORY_ACCESSES
	WHERE Access_Id = P_Id_Tab(I);

Line: 140

       Recs_deleted(10) := Recs_deleted(10) + SQL%ROWCOUNT;

Line: 143

       DELETE
	 FROM AS_SALES_LEADS
	WHERE Sales_Lead_Id = P_Id_Tab(I);

Line: 146

       Recs_deleted(11) := Recs_deleted(11) + SQL%ROWCOUNT;

Line: 148

END Delete_Unqualified_Leads;

Line: 175

SELECT Sales_Lead_Id
  FROM As_sales_leads
 WHERE Qualified_Flag = 'N'
   AND TRUNC(Creation_Date) BETWEEN TRUNC(l_start_date) AND TRUNC(l_end_date)
   AND status_code <> fnd_profile.value('AS_LEAD_LINK_STATUS')
   AND status_code = fnd_profile.value('AS_DEFAULT_LEAD_STATUS');

Line: 197

      table AS_SALES_LEADS are deleted. Since the number of records to be purged will be high this is done in batch
      of 10000 records. The Sales Lead Ids which are stored in a Pl/Sql table are then passed to the local procedure
      Delete_Unqualified_Leads. Since this procedure takes sales lead ids in batches of 10000,
      for the remaining records it has be done with a simple select statement.

      For example, if there are 102300 unqualified leads, then this loop will be executed 10 times. There will be
      2300 pending unqualified leads which will be processed in the simple SELECT that immediately follows this cursor.*/

   OPEN Collect_Sales_Lead_Ids(l_start_date, l_end_date);

Line: 210

	 Id_Tab.DELETE;

Line: 217

      Delete_Unqualified_Leads(Id_Tab); --Pass the lead nos to the Purge_Qualified_Leads procedure to delete them.

Line: 224

   SELECT Sales_Lead_Id BULK COLLECT
     INTO Id_Tab
     FROM AS_SALES_LEADS
    WHERE Qualified_Flag = 'N'
      AND TRUNC(Creation_Date) BETWEEN TRUNC(l_start_date) AND TRUNC(l_end_date)
      AND status_code <> fnd_profile.value('AS_LEAD_LINK_STATUS')
      AND status_code = fnd_profile.value('AS_DEFAULT_LEAD_STATUS');

Line: 233

      Delete_Unqualified_Leads(Id_Tab);