APPS.JTF_TERR_JSP_REPORTS dependencies on JTF_TERR_DENORM_RULES_ALL

Line 847: FROM jtf_terr_denorm_rules_all jtdr

843: -- Total # of Active Territories
844: ( SELECT COUNT(*) ACTIVE_TERR_COUNT
845: FROM jtf_terr_all jt
846: WHERE EXISTS ( SELECT jtdr.terr_id
847: FROM jtf_terr_denorm_rules_all jtdr
848: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
849: ) atc,
850:
851: -- Total # of Territories that have Internal and External Reps

Line 855: FROM jtf_terr_denorm_rules_all jtdr

851: -- Total # of Territories that have Internal and External Reps
852: ( SELECT COUNT(*) TERR_DUAL_ASSGN_COUNT
853: FROM jtf_terr_all jt
854: WHERE EXISTS ( SELECT jtdr.terr_id
855: FROM jtf_terr_denorm_rules_all jtdr
856: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
857: AND EXISTS ( SELECT jtr.terr_id
858: FROM jtf_terr_rsc_all jtr
859: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)

Line 871: FROM jtf_terr_denorm_rules_all jtdr

867: -- Territories created last 7 days
868: ( SELECT COUNT(*) TERR_CREATED_COUNT
869: FROM jtf_terr_all jt
870: WHERE EXISTS ( SELECT jtdr.terr_id
871: FROM jtf_terr_denorm_rules_all jtdr
872: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
873: and jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
874: ) tcc,
875:

Line 880: FROM jtf_terr_denorm_rules_all jtdr

876: -- Total # of (SOFT) DELETED Territories
877: ( SELECT COUNT(*) TERR_SOFT_DEL_COUNT
878: FROM jtf_terr_all jt
879: WHERE EXISTS ( SELECT jtdr.terr_id
880: FROM jtf_terr_denorm_rules_all jtdr
881: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
882: and jt.end_date_active BETWEEN SYSDATE-7 AND SYSDATE+1
883: ) tsdc,
884:

Line 889: FROM jtf_terr_denorm_rules_all jtdr

885: -- Total # of UPDATED Territories
886: ( SELECT COUNT(*) TERR_UPDATED_COUNT
887: FROM jtf_terr_all jt
888: WHERE EXISTS ( SELECT jtdr.terr_id
889: FROM jtf_terr_denorm_rules_all jtdr
890: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
891: and jt.last_update_date BETWEEN SYSDATE-7 AND SYSDATE+1
892: ) tuc,
893:

Line 898: FROM jtf_terr_denorm_rules_all jtdr

894: -- Total Distinct # of People Assigned to Active Territories -- 2930
895: ( SELECT COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
896: FROM jtf_terr_rsc_all jtr
897: WHERE EXISTS ( SELECT jtdr.terr_id
898: FROM jtf_terr_denorm_rules_all jtdr
899: WHERE jtdr.resource_exists_flag = 'Y'
900: AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
901: ) arc ;
902:

Line 928: FROM jtf_terr_denorm_rules_all jtdr

924: COUNT(*) ACTIVE_TERR_COUNT
925: FROM jtf_terr_all jt, hr_organization_units hou
926: WHERE jt.org_id = hou.organization_id
927: AND EXISTS ( SELECT jtdr.terr_id
928: FROM jtf_terr_denorm_rules_all jtdr
929: WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
930: GROUP BY hou.name, hou.organization_id
931: ) atc,
932:

Line 941: FROM jtf_terr_denorm_rules_all jtdr

937: COUNT(*) TERR_DUAL_ASSGN_COUNT
938: FROM jtf_terr_all jt, hr_organization_units hou
939: WHERE jt.org_id = hou.organization_id
940: AND EXISTS ( SELECT jtdr.terr_id
941: FROM jtf_terr_denorm_rules_all jtdr
942: WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
943: AND EXISTS ( SELECT jtr.terr_id
944: FROM jtf_terr_rsc_all jtr
945: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)

Line 962: FROM jtf_terr_denorm_rules_all jtdr

958: COUNT(*) TERR_CREATED_COUNT
959: FROM jtf_terr_all jt, hr_organization_units hou
960: WHERE jt.org_id = hou.organization_id
961: and EXISTS ( SELECT jtdr.terr_id
962: FROM jtf_terr_denorm_rules_all jtdr
963: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
964: AND jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
965: GROUP BY hou.name, hou.organization_id
966: ) tcc,

Line 976: FROM jtf_terr_denorm_rules_all jtdr

972: COUNT(*) TERR_SOFT_DEL_COUNT
973: FROM jtf_terr_all jt, hr_organization_units hou
974: WHERE jt.org_id = hou.organization_id
975: and EXISTS ( SELECT jtdr.terr_id
976: FROM jtf_terr_denorm_rules_all jtdr
977: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
978: AND jt.END_DATE_ACTIVE BETWEEN SYSDATE-7 AND SYSDATE+1
979: GROUP BY hou.name, hou.organization_id
980: ) tsdc,

Line 990: FROM jtf_terr_denorm_rules_all jtdr

986: COUNT(*) TERR_UPDATED_COUNT
987: FROM jtf_terr_all jt, hr_organization_units hou
988: WHERE jt.org_id = hou.organization_id
989: and EXISTS ( SELECT jtdr.terr_id
990: FROM jtf_terr_denorm_rules_all jtdr
991: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
992: AND jt.LAST_UPDATE_DATE BETWEEN SYSDATE-7 AND SYSDATE+1
993: GROUP BY hou.name, hou.organization_id
994: ) tuc,

Line 1004: FROM jtf_terr_denorm_rules_all jtdr

1000: COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
1001: FROM jtf_terr_rsc_all jtr, hr_organization_units hou
1002: WHERE jtr.org_id = hou.organization_id
1003: AND EXISTS ( SELECT jtdr.terr_id
1004: FROM jtf_terr_denorm_rules_all jtdr
1005: WHERE jtdr.resource_exists_flag = 'Y'
1006: AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
1007:
1008: GROUP BY hou.name, hou.organization_id

Line 1028: FROM jtf_terr_denorm_rules_all jtdr

1024: exists ( select jtua.terr_id
1025: from jtf_terr_usgs_all jtua
1026: where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
1027: and NOT EXISTS ( SELECT jtdr.terr_id
1028: FROM jtf_terr_denorm_rules_all jtdr
1029: WHERE jtdr.terr_id = jt.terr_id );
1030:
1031: cursor c_NONACTIVE_BY_COUNTRY IS
1032: SELECT

Line 1042: FROM jtf_terr_denorm_rules_all jtdr

1038: and exists ( select jtua.terr_id
1039: from jtf_terr_usgs_all jtua
1040: where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
1041: AND NOT EXISTS ( SELECT jtdr.terr_id
1042: FROM jtf_terr_denorm_rules_all jtdr
1043: WHERE jtdr.terr_id = jt.terr_id )
1044: GROUP BY hou.name, hou.organization_id
1045: ORDER BY INACTIVE_TERR_COUNT DESC;
1046: