APPS.JTF_TERR_JSP_REPORTS dependencies on JTF_TERR_DENORM_RULES_ALL

Line 871: FROM jtf_terr_denorm_rules_all jtdr

867: -- Total # of Active Territories
868: ( SELECT COUNT(*) ACTIVE_TERR_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: ) atc,
874:
875: -- Total # of Territories that have Internal and External Reps

Line 879: FROM jtf_terr_denorm_rules_all jtdr

875: -- Total # of Territories that have Internal and External Reps
876: ( SELECT COUNT(*) TERR_DUAL_ASSGN_COUNT
877: FROM jtf_terr_all jt
878: WHERE EXISTS ( SELECT jtdr.terr_id
879: FROM jtf_terr_denorm_rules_all jtdr
880: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
881: AND EXISTS ( SELECT jtr.terr_id
882: FROM jtf_terr_rsc_all jtr
883: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)

Line 895: FROM jtf_terr_denorm_rules_all jtdr

891: -- Territories created last 7 days
892: ( SELECT COUNT(*) TERR_CREATED_COUNT
893: FROM jtf_terr_all jt
894: WHERE EXISTS ( SELECT jtdr.terr_id
895: FROM jtf_terr_denorm_rules_all jtdr
896: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
897: and jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
898: ) tcc,
899:

Line 904: FROM jtf_terr_denorm_rules_all jtdr

900: -- Total # of (SOFT) DELETED Territories
901: ( SELECT COUNT(*) TERR_SOFT_DEL_COUNT
902: FROM jtf_terr_all jt
903: WHERE EXISTS ( SELECT jtdr.terr_id
904: FROM jtf_terr_denorm_rules_all jtdr
905: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
906: and jt.end_date_active BETWEEN SYSDATE-7 AND SYSDATE+1
907: ) tsdc,
908:

Line 913: FROM jtf_terr_denorm_rules_all jtdr

909: -- Total # of UPDATED Territories
910: ( SELECT COUNT(*) TERR_UPDATED_COUNT
911: FROM jtf_terr_all jt
912: WHERE EXISTS ( SELECT jtdr.terr_id
913: FROM jtf_terr_denorm_rules_all jtdr
914: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
915: and jt.last_update_date BETWEEN SYSDATE-7 AND SYSDATE+1
916: ) tuc,
917:

Line 922: FROM jtf_terr_denorm_rules_all jtdr

918: -- Total Distinct # of People Assigned to Active Territories -- 2930
919: ( SELECT COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
920: FROM jtf_terr_rsc_all jtr
921: WHERE EXISTS ( SELECT jtdr.terr_id
922: FROM jtf_terr_denorm_rules_all jtdr
923: WHERE jtdr.resource_exists_flag = 'Y'
924: AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
925: ) arc ;
926:

Line 952: FROM jtf_terr_denorm_rules_all jtdr

948: COUNT(*) ACTIVE_TERR_COUNT
949: FROM jtf_terr_all jt, hr_organization_units hou
950: WHERE jt.org_id = hou.organization_id
951: AND EXISTS ( SELECT jtdr.terr_id
952: FROM jtf_terr_denorm_rules_all jtdr
953: WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
954: GROUP BY hou.name, hou.organization_id
955: ) atc,
956:

Line 965: FROM jtf_terr_denorm_rules_all jtdr

961: COUNT(*) TERR_DUAL_ASSGN_COUNT
962: FROM jtf_terr_all jt, hr_organization_units hou
963: WHERE jt.org_id = hou.organization_id
964: AND EXISTS ( SELECT jtdr.terr_id
965: FROM jtf_terr_denorm_rules_all jtdr
966: WHERE jtdr.terr_id = jt.terr_id and jtdr.source_id = p_param2)
967: AND EXISTS ( SELECT jtr.terr_id
968: FROM jtf_terr_rsc_all jtr
969: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)

Line 986: FROM jtf_terr_denorm_rules_all jtdr

982: COUNT(*) TERR_CREATED_COUNT
983: FROM jtf_terr_all jt, hr_organization_units hou
984: WHERE jt.org_id = hou.organization_id
985: and EXISTS ( SELECT jtdr.terr_id
986: FROM jtf_terr_denorm_rules_all jtdr
987: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
988: AND jt.creation_date BETWEEN SYSDATE-7 AND SYSDATE+1
989: GROUP BY hou.name, hou.organization_id
990: ) tcc,

Line 1000: FROM jtf_terr_denorm_rules_all jtdr

996: COUNT(*) TERR_SOFT_DEL_COUNT
997: FROM jtf_terr_all jt, hr_organization_units hou
998: WHERE jt.org_id = hou.organization_id
999: and EXISTS ( SELECT jtdr.terr_id
1000: FROM jtf_terr_denorm_rules_all jtdr
1001: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
1002: AND jt.END_DATE_ACTIVE BETWEEN SYSDATE-7 AND SYSDATE+1
1003: GROUP BY hou.name, hou.organization_id
1004: ) tsdc,

Line 1014: FROM jtf_terr_denorm_rules_all jtdr

1010: COUNT(*) TERR_UPDATED_COUNT
1011: FROM jtf_terr_all jt, hr_organization_units hou
1012: WHERE jt.org_id = hou.organization_id
1013: and EXISTS ( SELECT jtdr.terr_id
1014: FROM jtf_terr_denorm_rules_all jtdr
1015: WHERE jtdr.source_id = p_param2 and jtdr.terr_id = jt.terr_id )
1016: AND jt.LAST_UPDATE_DATE BETWEEN SYSDATE-7 AND SYSDATE+1
1017: GROUP BY hou.name, hou.organization_id
1018: ) tuc,

Line 1028: FROM jtf_terr_denorm_rules_all jtdr

1024: COUNT(DISTINCT jtr.resource_id) ACTIVE_DIST_REP_COUNT
1025: FROM jtf_terr_rsc_all jtr, hr_organization_units hou
1026: WHERE jtr.org_id = hou.organization_id
1027: AND EXISTS ( SELECT jtdr.terr_id
1028: FROM jtf_terr_denorm_rules_all jtdr
1029: WHERE jtdr.resource_exists_flag = 'Y'
1030: AND jtdr.terr_id = jtr.terr_id and jtdr.source_id = p_param2)
1031:
1032: GROUP BY hou.name, hou.organization_id

Line 1052: FROM jtf_terr_denorm_rules_all jtdr

1048: exists ( select jtua.terr_id
1049: from jtf_terr_usgs_all jtua
1050: where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
1051: and NOT EXISTS ( SELECT jtdr.terr_id
1052: FROM jtf_terr_denorm_rules_all jtdr
1053: WHERE jtdr.terr_id = jt.terr_id );
1054:
1055: cursor c_NONACTIVE_BY_COUNTRY IS
1056: SELECT

Line 1066: FROM jtf_terr_denorm_rules_all jtdr

1062: and exists ( select jtua.terr_id
1063: from jtf_terr_usgs_all jtua
1064: where jtua.source_id = p_param2 and jtua.terr_id = jt.terr_id)
1065: AND NOT EXISTS ( SELECT jtdr.terr_id
1066: FROM jtf_terr_denorm_rules_all jtdr
1067: WHERE jtdr.terr_id = jt.terr_id )
1068: GROUP BY hou.name, hou.organization_id
1069: ORDER BY INACTIVE_TERR_COUNT DESC;
1070: