APPS.ASN_MIG_SALES_TEAM_PVT SQL Statements

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

Line: 40

  l_last_update_dates   DateTab;

Line: 44

  l_prev_last_update_date   DATE;

Line: 45

  l_last_update_date        DATE;

Line: 49

  l_update_count            NUMBER;

Line: 52

    SELECT log_id, lead_line_id, last_update_date
    FROM   as_lead_lines_log
    WHERE  lead_id = p_lead_id
    ORDER BY lead_line_id ASC, last_update_date DESC, log_id DESC;

Line: 58

    SELECT log_id, sales_credit_id, last_update_date
    FROM   as_sales_credits_log
    WHERE  lead_id = p_lead_id
    ORDER BY sales_credit_id ASC, last_update_date DESC, log_id DESC;

Line: 72

  INTO l_log_ids, l_lead_line_ids, l_last_update_dates;

Line: 81

  l_update_count := 0;

Line: 88

          l_prev_last_update_date := l_future_date;

Line: 92

      l_last_update_date := trunc(l_last_update_dates(i));

Line: 93

      IF l_prev_last_update_date = l_last_update_date THEN
          l_endday_log_flag := 'N';

Line: 98

      l_prev_last_update_date := l_last_update_date;

Line: 102

      UPDATE as_lead_lines_log -- @@
      SET    endday_log_flag = l_endday_log_flags(i)
      WHERE  log_id = l_log_ids(i) AND endday_log_flag IS NULL;

Line: 106

    l_update_count := SQL%ROWCOUNT;

Line: 112

                   'Num Lead Line Logs Updated=' || l_update_count);

Line: 117

  INTO l_log_ids, l_sales_credit_ids, l_last_update_dates;

Line: 126

  l_update_count := 0;

Line: 133

          l_prev_last_update_date := l_future_date;

Line: 137

      l_last_update_date := trunc(l_last_update_dates(i));

Line: 138

      IF l_prev_last_update_date = l_last_update_date THEN
          l_endday_log_flag := 'N';

Line: 143

      l_prev_last_update_date := l_last_update_date;

Line: 147

      UPDATE as_sales_credits_log  -- @@
      SET    endday_log_flag = l_endday_log_flags(i)
      WHERE  log_id = l_log_ids(i) AND endday_log_flag IS NULL;

Line: 151

    l_update_count := SQL%ROWCOUNT;

Line: 158

                   ' Num Sales Credits Updated=' || l_update_count);

Line: 185

    SELECT lead_id, customer_id
    FROM   as_leads_all
    WHERE  lead_id BETWEEN p_start_id AND p_end_id; */

Line: 190

    SELECT DISTINCT lead_id
    FROM   AS_ACCESSES_ALL_OPP_TEMP
    WHERE  lead_id BETWEEN p_start_id AND p_end_id;

Line: 195

    SELECT max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) code_access_id,
           salesforce_id, sales_group_id, partner_customer_id,
           partner_cont_party_id,
           max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
           max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
           max(nvl(OWNER_FLAG, 'N')) owner_flag,
           max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
    FROM   AS_ACCESSES_ALL
    WHERE  lead_id = p_lead_id
    GROUP BY salesforce_id, sales_group_id, partner_customer_id,
             partner_cont_party_id
    HAVING count(access_id) > 1; */

Line: 210

SELECT lead_id,max(code_access_id) code_access_id,
           salesforce_id, sales_group_id, partner_customer_id,
           partner_cont_party_id,
           max(FREEZE_FLAG) freeze_flag,
           max(TEAM_LEADER_FLAG) team_leader_flag,
           max(OWNER_FLAG) owner_flag,
           max(CONTRIBUTOR_FLAG) contributor_flag
    FROM  AS_ACCESSES_ALL_OPP_TEMP  -- AS_ACCESSES_ALL
    WHERE  lead_id = p_lead_id
    GROUP BY lead_id,salesforce_id, sales_group_id, partner_customer_id,
             partner_cont_party_id;

Line: 222

CURSOR c1 IS SELECT as_leads_s.nextval FROM dual;

Line: 283

          UPDATE AS_ACCESSES_ALL_ALL -- @@
          SET DELETE_FLAG = 'Y',
              LAST_UPDATED_BY = l_user_id,
              LAST_UPDATE_DATE = sysdate,
              LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
          WHERE lead_id = l_lead_id AND  -- @@
                salesforce_id = uniq_steam_rec.salesforce_id AND
                nvl(sales_group_id, -37) = nvl(uniq_steam_rec.sales_group_id, -37) AND
                nvl(partner_customer_id, -37) = nvl(uniq_steam_rec.partner_customer_id, -37) AND
                nvl(partner_cont_party_id, -37) = nvl(uniq_steam_rec.partner_cont_party_id, -37) AND
                access_id <> l_access_id AND
                delete_flag IS NULL;

Line: 298

          UPDATE AS_ACCESSES_ALL -- @@
          SET FREEZE_FLAG = uniq_steam_rec.freeze_flag,
              TEAM_LEADER_FLAG = uniq_steam_rec.team_leader_flag,
              OWNER_FLAG = uniq_steam_rec.owner_flag,
              CONTRIBUTOR_FLAG = uniq_steam_rec.contributor_flag,
              LAST_UPDATED_BY = l_user_id,
              LAST_UPDATE_DATE = sysdate,
              LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
          WHERE access_id = l_access_id;

Line: 405

  l_updated_flag            BOOLEAN;

Line: 411

    SELECT DISTINCT sales_lead_id
    FROM   AS_ACCESSES_ALL_LEAD_TEMP
    WHERE  sales_lead_id BETWEEN p_start_id AND p_end_id;

Line: 418

    SELECT max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) code_access_id,
           salesforce_id, sales_group_id,
           max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
           max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
           max(nvl(OWNER_FLAG, 'N')) owner_flag,
           max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
    FROM   AS_ACCESSES_ALL
    WHERE  sales_lead_id = p_sales_lead_id
    GROUP BY salesforce_id, sales_group_id
    HAVING count(access_id) > 1; */ -- @@

Line: 432

SELECT sales_lead_id,max(code_access_id) code_access_id,
           salesforce_id, sales_group_id,
           max(FREEZE_FLAG) freeze_flag,
           max(TEAM_LEADER_FLAG) team_leader_flag,
           max(OWNER_FLAG) owner_flag,
           max(CONTRIBUTOR_FLAG) contributor_flag
    FROM   AS_ACCESSES_ALL_LEAD_TEMP -- AS_ACCESSES_ALL
    WHERE  sales_lead_id = p_sales_lead_id
    GROUP BY salesforce_id, sales_group_id;

Line: 471

        l_updated_flag := false;

Line: 495

          l_updated_flag := true;

Line: 498

          UPDATE AS_ACCESSES_ALL_ALL -- @@
          SET DELETE_FLAG = 'Y',
              LAST_UPDATED_BY = l_user_id,
              LAST_UPDATE_DATE = sysdate,
              LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
          WHERE sales_lead_id =  l_sales_lead_id AND  -- @@
                salesforce_id = uniq_steam_rec.salesforce_id AND
                nvl(sales_group_id, -37) = nvl(uniq_steam_rec.sales_group_id, -37) AND
                access_id <> l_access_id AND
                delete_flag IS NULL;

Line: 511

          UPDATE AS_ACCESSES_ALL   -- @@
          SET FREEZE_FLAG = uniq_steam_rec.freeze_flag,
              TEAM_LEADER_FLAG = uniq_steam_rec.team_leader_flag,
              OWNER_FLAG = uniq_steam_rec.owner_flag,
              CONTRIBUTOR_FLAG = uniq_steam_rec.contributor_flag,
              LAST_UPDATED_BY = l_user_id,
              LAST_UPDATE_DATE = sysdate,
              LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
          WHERE access_id = l_access_id;

Line: 528

        IF l_updated_flag THEN
            l_uncommitted_leads := l_uncommitted_leads + 1;

Line: 613

  CURSOR c1 IS SELECT hz_parties_s.nextval FROM dual;

Line: 633

    SELECT lead_id, customer_id
    FROM   as_leads_all
    WHERE  lead_id BETWEEN p_start_id AND p_end_id;

Line: 637

    CURSOR c2 IS SELECT as_leads_s.nextval FROM dual;

Line: 652

  on as_accesses to insert into 3 tables the few thousand dups of each type
(cust, leads, opps) ..parallel full scans even of the
  large as_accesses_all table should just take a few minutes.
- then proceed to launch the worker programs to query up thier respective dup
set from the above tables, and do the corresponding updates.  */
BEGIN
 IF (fnd_conc_global.request_data IS NULL) THEN

--Create temp table for customer  -- @@
INSERT /*+ APPEND PARALLEL(CUST) */
into AS_ACCESSES_ALL_CUST_TEMP CUST
(customer_id,
code_access_id,
salesforce_id,
sales_group_id,
partner_customer_id,
partner_cont_party_id,
freeze_flag,
team_leader_flag,
owner_flag,
contributor_flag)
Select /*+ PARALLEL(A)*/
customer_id,max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) code_access_id,
           salesforce_id, sales_group_id, partner_customer_id,
           partner_cont_party_id,
           max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
           max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
           max(nvl(OWNER_FLAG, 'N')) owner_flag,
           max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
    FROM   AS_ACCESSES_ALL A ---- @@
    WHERE   lead_id IS NULL AND sales_lead_id IS NULL
    GROUP BY customer_id,salesforce_id, sales_group_id, partner_customer_id,
             partner_cont_party_id
    HAVING count(access_id) > 1;

Line: 688

INSERT /*+ APPEND PARALLEL(CUST) */ into AS_ACCESSES_ALL_LEAD_TEMP CUST
(sales_lead_id,
code_access_id,
salesforce_id,
sales_group_id,
freeze_flag,
team_leader_flag,
owner_flag,
contributor_flag)
Select /*+ PARALLEL(A)*/
sales_lead_id,max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID),
           salesforce_id, sales_group_id,
           max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
           max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
           max(nvl(OWNER_FLAG, 'N')) owner_flag,
           max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
    FROM   AS_ACCESSES_ALL A  ---- @@
    WHERE sales_lead_id IS NOT NULL
    GROUP BY sales_lead_id,salesforce_id, sales_group_id
    HAVING count(access_id) > 1;

Line: 710

INSERT /*+ APPEND PARALLEL(CUST) */ into AS_ACCESSES_ALL_OPP_TEMP CUST
(lead_id,
code_access_id,
salesforce_id,
sales_group_id,
partner_customer_id,
partner_cont_party_id,
freeze_flag,
team_leader_flag,
owner_flag,
contributor_flag)
Select /*+ PARALLEL(A)*/
Lead_id,max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) ,
           salesforce_id, sales_group_id, partner_customer_id,partner_cont_party_id,
           max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
           max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
           max(nvl(OWNER_FLAG, 'N')) owner_flag,
           max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
    FROM   AS_ACCESSES_ALL  A ---- @@
    WHERE Lead_id IS NOT NULL
    GROUP BY lead_id,salesforce_id, sales_group_id, partner_customer_id,
             partner_cont_party_id
    HAVING count(access_id) > 1;

Line: 746

l_id_list.delete;

Line: 748

	Select count(DISTINCT lead_id),min(lead_id),max(lead_id)
	into l_dup_count_opp,l_dup_min_opp,l_max_id
	From  AS_ACCESSES_ALL_OPP_TEMP;

Line: 756

	Select distinct lead_id
	BULK COLLECT INTO
	l_id_list
	FROM AS_ACCESSES_ALL_OPP_TEMP
	ORDER BY lead_id;

Line: 764

	Select count(DISTINCT customer_id),min(customer_id),max(customer_id)
	into l_dup_count_cust,l_dup_min_cust,l_max_id
	From  AS_ACCESSES_ALL_CUST_TEMP;

Line: 772

	Select distinct customer_id
	BULK COLLECT INTO
	l_id_list
	FROM AS_ACCESSES_ALL_CUST_TEMP
	ORDER BY customer_id;

Line: 778

	Select count(DISTINCT sales_lead_id),min(sales_lead_id),max(sales_lead_id)
	into l_dup_count_lead,l_dup_min_lead,l_max_id
	From  AS_ACCESSES_ALL_LEAD_TEMP;

Line: 785

	Select distinct sales_lead_id
	BULK COLLECT INTO
	l_id_list
	FROM AS_ACCESSES_ALL_LEAD_TEMP
	ORDER BY sales_lead_id;

Line: 1033

  l_updated_flag            BOOLEAN;

Line: 1039

    SELECT party_id
    FROM   hz_parties
    WHERE  party_id BETWEEN p_start_id AND p_end_id
           AND party_type IN ('ORGANIZATION', 'PERSON');*/

Line: 1045

   SELECT distinct temp.customer_id
   FROM  AS_ACCESSES_ALL_cust_TEMP temp ,hz_parties hz
	WHERE  temp.customer_id  BETWEEN p_start_id AND p_end_id
           AND hz.party_type IN ('ORGANIZATION', 'PERSON')
		AND temp.customer_id=hz.party_id ;

Line: 1052

    SELECT max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) code_access_id,
           salesforce_id, sales_group_id, partner_customer_id,
           partner_cont_party_id,
           max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
           max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
           max(nvl(OWNER_FLAG, 'N')) owner_flag,
           max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
    FROM   AS_ACCESSES_ALL
    WHERE  customer_id = p_party_id AND lead_id IS NULL AND sales_lead_id IS NULL
    GROUP BY salesforce_id, sales_group_id, partner_customer_id,
             partner_cont_party_id
    HAVING count(access_id) > 1; */ -- @@

Line: 1068

    SELECT customer_id,max(code_access_id) code_access_id,
           salesforce_id, sales_group_id, partner_customer_id,
           partner_cont_party_id,
           max(FREEZE_FLAG) freeze_flag,
           max(TEAM_LEADER_FLAG) team_leader_flag,
           max(OWNER_FLAG) owner_flag,
           max(CONTRIBUTOR_FLAG) contributor_flag
    FROM   AS_ACCESSES_ALL_CUST_TEMP -- AS_ACCESSES_ALL
WHERE  customer_id = p_party_id
    GROUP BY customer_id,salesforce_id, sales_group_id, partner_customer_id,
             partner_cont_party_id;

Line: 1108

        l_updated_flag := false;

Line: 1133

          l_updated_flag := true;

Line: 1136

          UPDATE AS_ACCESSES_ALL_ALL   -- @@
          SET DELETE_FLAG = 'Y',
              LAST_UPDATED_BY = l_user_id,
              LAST_UPDATE_DATE = sysdate,
              LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
          WHERE customer_id = l_party_id AND  -- @@
	        lead_id IS NULL AND
                sales_lead_id IS NULL AND
                salesforce_id = uniq_steam_rec.salesforce_id AND
                nvl(sales_group_id, -37) = nvl(uniq_steam_rec.sales_group_id, -37) AND
                nvl(partner_customer_id, -37) = nvl(uniq_steam_rec.partner_customer_id, -37) AND
                nvl(partner_cont_party_id, -37) = nvl(uniq_steam_rec.partner_cont_party_id, -37) AND
                access_id <> l_access_id AND
                delete_flag IS NULL;

Line: 1153

          UPDATE AS_ACCESSES_ALL -- @@
          SET FREEZE_FLAG = uniq_steam_rec.freeze_flag,
              TEAM_LEADER_FLAG = uniq_steam_rec.team_leader_flag,
              OWNER_FLAG = uniq_steam_rec.owner_flag,
              CONTRIBUTOR_FLAG = uniq_steam_rec.contributor_flag,
              LAST_UPDATED_BY = l_user_id,
              LAST_UPDATE_DATE = sysdate,
              LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
          WHERE access_id = l_access_id;

Line: 1170

        IF l_updated_flag THEN
            l_uncommitted_parties := l_uncommitted_parties + 1;

Line: 1253

 CURSOR c1 IS SELECT count(lead_id) FROM as_leads_all;

Line: 1501

  l_last_update_dates   DateTab;

Line: 1505

  l_prev_last_update_date   DATE;

Line: 1506

  l_last_update_date        DATE;

Line: 1510

  l_update_count            NUMBER;

Line: 1515

    SELECT lead_id, customer_id
    FROM   as_leads_all;

Line: 1519

    SELECT log_id, lead_line_id, last_update_date
    FROM   as_lead_lines_log
    WHERE  lead_id = p_lead_id
    ORDER BY lead_line_id ASC, last_update_date DESC, log_id DESC;

Line: 1525

    SELECT log_id, sales_credit_id, last_update_date
    FROM   as_sales_credits_log
    WHERE  lead_id = p_lead_id
    ORDER BY sales_credit_id ASC, last_update_date DESC, log_id DESC;

Line: 1565

			            INTO l_log_ids, l_lead_line_ids, l_last_update_dates;

Line: 1573

					l_update_count := 0;

Line: 1579

						          l_prev_last_update_date := l_future_date;

Line: 1583

							l_last_update_date := trunc(l_last_update_dates(i));

Line: 1584

						      IF l_prev_last_update_date = l_last_update_date THEN
							l_endday_log_flag := 'N';

Line: 1589

							l_prev_last_update_date := l_last_update_date;

Line: 1592

						UPDATE as_lead_lines_log -- @@
						SET    endday_log_flag = l_endday_log_flags(i)
						WHERE  log_id = l_log_ids(i) AND endday_log_flag IS NULL;

Line: 1596

						l_update_count := SQL%ROWCOUNT;

Line: 1601

					                   'Num Lead Line Logs Updated=' || l_update_count);

Line: 1606

				INTO l_log_ids, l_sales_credit_ids, l_last_update_dates;

Line: 1615

				l_update_count := 0;

Line: 1622

				          l_prev_last_update_date := l_future_date;

Line: 1626

				        l_last_update_date := trunc(l_last_update_dates(i));

Line: 1627

				      IF l_prev_last_update_date = l_last_update_date THEN
					l_endday_log_flag := 'N';

Line: 1632

					l_prev_last_update_date := l_last_update_date;

Line: 1636

					UPDATE as_sales_credits_log  -- @@
					SET    endday_log_flag = l_endday_log_flags(i)
					WHERE  log_id = l_log_ids(i) AND endday_log_flag IS NULL;

Line: 1640

					l_update_count := SQL%ROWCOUNT;

Line: 1648

					' Num Sales Credits Updated=' || l_update_count);

Line: 1659

				UPDATE AS_ACCESSES_ALL
				SET CUSTOMER_ID = l_customer_id(i),
				LAST_UPDATED_BY = l_user_id,
				LAST_UPDATE_DATE = sysdate,
				LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
				WHERE LEAD_ID = l_lead_id(i) AND
				nvl(CUSTOMER_ID, -37) <> l_customer_id(i);