eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ASN_MIG_SALES_TEAM_PVT dependencies on AS_ACCESSES_ALL

Line 191: FROM AS_ACCESSES_ALL_OPP_TEMP

187: WHERE lead_id BETWEEN p_start_id AND p_end_id; */
188:
189: CURSOR c_opps_in_range(p_start_id NUMBER, p_end_id NUMBER) IS
190: SELECT DISTINCT lead_id
191: FROM AS_ACCESSES_ALL_OPP_TEMP
192: WHERE lead_id BETWEEN p_start_id AND p_end_id;
193:
194: /* CURSOR c_uniq_steam(p_lead_id NUMBER) IS
195: SELECT max(decode(SALESFORCE_ROLE_CODE, NULL, 'N', 'Y') || ACCESS_ID) code_access_id,

Line 202: FROM AS_ACCESSES_ALL

198: max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
199: max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
200: max(nvl(OWNER_FLAG, 'N')) owner_flag,
201: max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
202: FROM AS_ACCESSES_ALL
203: WHERE lead_id = p_lead_id
204: GROUP BY salesforce_id, sales_group_id, partner_customer_id,
205: partner_cont_party_id
206: HAVING count(access_id) > 1; */

Line 217: FROM AS_ACCESSES_ALL_OPP_TEMP -- AS_ACCESSES_ALL

213: max(FREEZE_FLAG) freeze_flag,
214: max(TEAM_LEADER_FLAG) team_leader_flag,
215: max(OWNER_FLAG) owner_flag,
216: max(CONTRIBUTOR_FLAG) contributor_flag
217: FROM AS_ACCESSES_ALL_OPP_TEMP -- AS_ACCESSES_ALL
218: WHERE lead_id = p_lead_id
219: GROUP BY lead_id,salesforce_id, sales_group_id, partner_customer_id,
220: partner_cont_party_id;
221:

Line 283: UPDATE AS_ACCESSES_ALL_ALL -- @@

279: ' contributor_flag ' || uniq_steam_rec.contributor_flag);
280: END IF;
281:
282: l_access_id := substr(uniq_steam_rec.code_access_id, 2);
283: UPDATE AS_ACCESSES_ALL_ALL -- @@
284: SET DELETE_FLAG = 'Y',
285: LAST_UPDATED_BY = l_user_id,
286: LAST_UPDATE_DATE = sysdate,
287: LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id

Line 298: UPDATE AS_ACCESSES_ALL -- @@

294: delete_flag IS NULL;
295:
296: l_remove_count := l_remove_count + SQL%ROWCOUNT;
297:
298: UPDATE AS_ACCESSES_ALL -- @@
299: SET FREEZE_FLAG = uniq_steam_rec.freeze_flag,
300: TEAM_LEADER_FLAG = uniq_steam_rec.team_leader_flag,
301: OWNER_FLAG = uniq_steam_rec.owner_flag,
302: CONTRIBUTOR_FLAG = uniq_steam_rec.contributor_flag,

Line 412: FROM AS_ACCESSES_ALL_LEAD_TEMP

408: l_user_id NUMBER;
409:
410: CURSOR c_leads_in_range(p_start_id NUMBER, p_end_id NUMBER) IS
411: SELECT DISTINCT sales_lead_id
412: FROM AS_ACCESSES_ALL_LEAD_TEMP
413: WHERE sales_lead_id BETWEEN p_start_id AND p_end_id;
414:
415:
416:

Line 424: FROM AS_ACCESSES_ALL

420: max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
421: max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
422: max(nvl(OWNER_FLAG, 'N')) owner_flag,
423: max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
424: FROM AS_ACCESSES_ALL
425: WHERE sales_lead_id = p_sales_lead_id
426: GROUP BY salesforce_id, sales_group_id
427: HAVING count(access_id) > 1; */ -- @@
428:

Line 438: FROM AS_ACCESSES_ALL_LEAD_TEMP -- AS_ACCESSES_ALL

434: max(FREEZE_FLAG) freeze_flag,
435: max(TEAM_LEADER_FLAG) team_leader_flag,
436: max(OWNER_FLAG) owner_flag,
437: max(CONTRIBUTOR_FLAG) contributor_flag
438: FROM AS_ACCESSES_ALL_LEAD_TEMP -- AS_ACCESSES_ALL
439: WHERE sales_lead_id = p_sales_lead_id
440: GROUP BY salesforce_id, sales_group_id;
441:
442: --Code added for ASN migration approach suggested by lester -- End

Line 498: UPDATE AS_ACCESSES_ALL_ALL -- @@

494:
495: l_updated_flag := true;
496:
497: l_access_id := substr(uniq_steam_rec.code_access_id, 2);
498: UPDATE AS_ACCESSES_ALL_ALL -- @@
499: SET DELETE_FLAG = 'Y',
500: LAST_UPDATED_BY = l_user_id,
501: LAST_UPDATE_DATE = sysdate,
502: LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id

Line 511: UPDATE AS_ACCESSES_ALL -- @@

507: delete_flag IS NULL;
508:
509: l_remove_count := l_remove_count + SQL%ROWCOUNT;
510:
511: UPDATE AS_ACCESSES_ALL -- @@
512: SET FREEZE_FLAG = uniq_steam_rec.freeze_flag,
513: TEAM_LEADER_FLAG = uniq_steam_rec.team_leader_flag,
514: OWNER_FLAG = uniq_steam_rec.owner_flag,
515: CONTRIBUTOR_FLAG = uniq_steam_rec.contributor_flag,

Line 654: large as_accesses_all table should just take a few minutes.

650: /*--Bug#5816258:- we should have the single, top level
651: parent program fire 3 parallel query, full scan sqls
652: on as_accesses to insert into 3 tables the few thousand dups of each type
653: (cust, leads, opps) ..parallel full scans even of the
654: large as_accesses_all table should just take a few minutes.
655: - then proceed to launch the worker programs to query up thier respective dup
656: set from the above tables, and do the corresponding updates. */
657: BEGIN
658: IF (fnd_conc_global.request_data IS NULL) THEN

Line 662: into AS_ACCESSES_ALL_CUST_TEMP CUST

658: IF (fnd_conc_global.request_data IS NULL) THEN
659:
660: --Create temp table for customer -- @@
661: INSERT /*+ APPEND PARALLEL(CUST) */
662: into AS_ACCESSES_ALL_CUST_TEMP CUST
663: (customer_id,
664: code_access_id,
665: salesforce_id,
666: sales_group_id,

Line 681: FROM AS_ACCESSES_ALL A ---- @@

677: max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
678: max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
679: max(nvl(OWNER_FLAG, 'N')) owner_flag,
680: max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
681: FROM AS_ACCESSES_ALL A ---- @@
682: WHERE lead_id IS NULL AND sales_lead_id IS NULL
683: GROUP BY customer_id,salesforce_id, sales_group_id, partner_customer_id,
684: partner_cont_party_id
685: HAVING count(access_id) > 1;

Line 688: INSERT /+ APPEND PARALLEL(CUST) / into AS_ACCESSES_ALL_LEAD_TEMP CUST

684: partner_cont_party_id
685: HAVING count(access_id) > 1;
686: COMMIT;
687:
688: INSERT /*+ APPEND PARALLEL(CUST) */ into AS_ACCESSES_ALL_LEAD_TEMP CUST
689: (sales_lead_id,
690: code_access_id,
691: salesforce_id,
692: sales_group_id,

Line 704: FROM AS_ACCESSES_ALL A ---- @@

700: max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
701: max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
702: max(nvl(OWNER_FLAG, 'N')) owner_flag,
703: max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
704: FROM AS_ACCESSES_ALL A ---- @@
705: WHERE sales_lead_id IS NOT NULL
706: GROUP BY sales_lead_id,salesforce_id, sales_group_id
707: HAVING count(access_id) > 1;
708: COMMIT;

Line 710: INSERT /+ APPEND PARALLEL(CUST) / into AS_ACCESSES_ALL_OPP_TEMP CUST

706: GROUP BY sales_lead_id,salesforce_id, sales_group_id
707: HAVING count(access_id) > 1;
708: COMMIT;
709:
710: INSERT /*+ APPEND PARALLEL(CUST) */ into AS_ACCESSES_ALL_OPP_TEMP CUST
711: (lead_id,
712: code_access_id,
713: salesforce_id,
714: sales_group_id,

Line 728: FROM AS_ACCESSES_ALL A ---- @@

724: max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
725: max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
726: max(nvl(OWNER_FLAG, 'N')) owner_flag,
727: max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
728: FROM AS_ACCESSES_ALL A ---- @@
729: WHERE Lead_id IS NOT NULL
730: GROUP BY lead_id,salesforce_id, sales_group_id, partner_customer_id,
731: partner_cont_party_id
732: HAVING count(access_id) > 1;

Line 750: From AS_ACCESSES_ALL_OPP_TEMP;

746: l_id_list.delete;
747: IF i=1 THEN --Opp
748: Select count(DISTINCT lead_id),min(lead_id),max(lead_id)
749: into l_dup_count_opp,l_dup_min_opp,l_max_id
750: From AS_ACCESSES_ALL_OPP_TEMP;
751:
752: l_max_num_rows := l_dup_count_opp;
753: l_start_id := l_dup_min_opp-1;
754: ls_program := 'ASN_MIG_DUP_ST_OPP_PRG';

Line 759: FROM AS_ACCESSES_ALL_OPP_TEMP

755:
756: Select distinct lead_id
757: BULK COLLECT INTO
758: l_id_list
759: FROM AS_ACCESSES_ALL_OPP_TEMP
760: ORDER BY lead_id;
761:
762: ELSIF i=2 THEN --cust
763:

Line 766: From AS_ACCESSES_ALL_CUST_TEMP;

762: ELSIF i=2 THEN --cust
763:
764: Select count(DISTINCT customer_id),min(customer_id),max(customer_id)
765: into l_dup_count_cust,l_dup_min_cust,l_max_id
766: From AS_ACCESSES_ALL_CUST_TEMP;
767:
768: l_max_num_rows := l_dup_count_cust;
769: l_start_id := l_dup_min_cust-1;
770: ls_program := 'ASN_MIG_DUP_ST_CUST_PRG';

Line 775: FROM AS_ACCESSES_ALL_CUST_TEMP

771:
772: Select distinct customer_id
773: BULK COLLECT INTO
774: l_id_list
775: FROM AS_ACCESSES_ALL_CUST_TEMP
776: ORDER BY customer_id;
777: ELSE -- lead
778: Select count(DISTINCT sales_lead_id),min(sales_lead_id),max(sales_lead_id)
779: into l_dup_count_lead,l_dup_min_lead,l_max_id

Line 780: From AS_ACCESSES_ALL_LEAD_TEMP;

776: ORDER BY customer_id;
777: ELSE -- lead
778: Select count(DISTINCT sales_lead_id),min(sales_lead_id),max(sales_lead_id)
779: into l_dup_count_lead,l_dup_min_lead,l_max_id
780: From AS_ACCESSES_ALL_LEAD_TEMP;
781:
782: l_max_num_rows := l_dup_count_lead;
783: l_start_id := l_dup_min_lead-1;
784: ls_program := 'ASN_MIG_DUP_ST_LEAD_PRG';

Line 788: FROM AS_ACCESSES_ALL_LEAD_TEMP

784: ls_program := 'ASN_MIG_DUP_ST_LEAD_PRG';
785: Select distinct sales_lead_id
786: BULK COLLECT INTO
787: l_id_list
788: FROM AS_ACCESSES_ALL_LEAD_TEMP
789: ORDER BY sales_lead_id;
790: END IF;
791: --Code added for ASN migration approach suggested by lester --Bug#5816258 -- End
792: --

Line 1046: FROM AS_ACCESSES_ALL_cust_TEMP temp ,hz_parties hz

1042: AND party_type IN ('ORGANIZATION', 'PERSON');*/
1043:
1044: CURSOR c_parties_in_range(p_start_id NUMBER, p_end_id NUMBER) IS
1045: SELECT distinct temp.customer_id
1046: FROM AS_ACCESSES_ALL_cust_TEMP temp ,hz_parties hz
1047: WHERE temp.customer_id BETWEEN p_start_id AND p_end_id
1048: AND hz.party_type IN ('ORGANIZATION', 'PERSON')
1049: AND temp.customer_id=hz.party_id ;
1050:

Line 1059: FROM AS_ACCESSES_ALL

1055: max(nvl(FREEZE_FLAG, 'N')) freeze_flag,
1056: max(nvl(TEAM_LEADER_FLAG, 'N')) team_leader_flag,
1057: max(nvl(OWNER_FLAG, 'N')) owner_flag,
1058: max(nvl(CONTRIBUTOR_FLAG, 'N')) contributor_flag
1059: FROM AS_ACCESSES_ALL
1060: WHERE customer_id = p_party_id AND lead_id IS NULL AND sales_lead_id IS NULL
1061: GROUP BY salesforce_id, sales_group_id, partner_customer_id,
1062: partner_cont_party_id
1063: HAVING count(access_id) > 1; */ -- @@

Line 1075: FROM AS_ACCESSES_ALL_CUST_TEMP -- AS_ACCESSES_ALL

1071: max(FREEZE_FLAG) freeze_flag,
1072: max(TEAM_LEADER_FLAG) team_leader_flag,
1073: max(OWNER_FLAG) owner_flag,
1074: max(CONTRIBUTOR_FLAG) contributor_flag
1075: FROM AS_ACCESSES_ALL_CUST_TEMP -- AS_ACCESSES_ALL
1076: WHERE customer_id = p_party_id
1077: GROUP BY customer_id,salesforce_id, sales_group_id, partner_customer_id,
1078: partner_cont_party_id;
1079: --Code added for ASN migration approach suggested by lester -- End

Line 1136: UPDATE AS_ACCESSES_ALL_ALL -- @@

1132:
1133: l_updated_flag := true;
1134:
1135: l_access_id := substr(uniq_steam_rec.code_access_id, 2);
1136: UPDATE AS_ACCESSES_ALL_ALL -- @@
1137: SET DELETE_FLAG = 'Y',
1138: LAST_UPDATED_BY = l_user_id,
1139: LAST_UPDATE_DATE = sysdate,
1140: LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id

Line 1153: UPDATE AS_ACCESSES_ALL -- @@

1149: delete_flag IS NULL;
1150:
1151: l_remove_count := l_remove_count + SQL%ROWCOUNT;
1152:
1153: UPDATE AS_ACCESSES_ALL -- @@
1154: SET FREEZE_FLAG = uniq_steam_rec.freeze_flag,
1155: TEAM_LEADER_FLAG = uniq_steam_rec.team_leader_flag,
1156: OWNER_FLAG = uniq_steam_rec.owner_flag,
1157: CONTRIBUTOR_FLAG = uniq_steam_rec.contributor_flag,

Line 1659: UPDATE AS_ACCESSES_ALL

1655: --Customer Id updation
1656: --Customer Id updation
1657: FORALL I IN l_lead_id.first..l_lead_id.last
1658: -- Fix Customer Id in Opp Sales Team
1659: UPDATE AS_ACCESSES_ALL
1660: SET CUSTOMER_ID = l_customer_id(i),
1661: LAST_UPDATED_BY = l_user_id,
1662: LAST_UPDATE_DATE = sysdate,
1663: LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id