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.JTF_TERR_JSP_REPORTS dependencies on JTF_TERR

Line 1: package body JTF_TERR_JSP_REPORTS AS

1: package body JTF_TERR_JSP_REPORTS AS
2: /* $Header: jtfpjrpb.pls 120.7.12010000.11 2009/07/24 09:15:14 ppillai ship $ */
3: ---------------------------------------------------------
4: -- Start of Comments
5: -- ---------------------------------------------------

Line 6: -- PACKAGE NAME: JTF_TERR_JSP_REPORTS

2: /* $Header: jtfpjrpb.pls 120.7.12010000.11 2009/07/24 09:15:14 ppillai ship $ */
3: ---------------------------------------------------------
4: -- Start of Comments
5: -- ---------------------------------------------------
6: -- PACKAGE NAME: JTF_TERR_JSP_REPORTS
7: -- ---------------------------------------------------
8: -- PURPOSE
9: -- JTF/A Territories JSP Reports Package
10: -- NOTES

Line 89: from jtf_terr j

85: -- all the territories we will be reporting on
86: /*
87: CURSOR cur_terr is
88: select j.terr_id, j.name, j.rank
89: from jtf_terr j
90: where NVL(j.end_date_active, sysdate) >= sysdate
91: AND j.start_date_active <= sysdate
92: AND EXISTS
93: ( select jtr.terr_id

Line 94: from jtf_terr_rsc jtr, jtf_terr_qual jtq

90: where NVL(j.end_date_active, sysdate) >= sysdate
91: AND j.start_date_active <= sysdate
92: AND EXISTS
93: ( select jtr.terr_id
94: from jtf_terr_rsc jtr, jtf_terr_qual jtq
95: where jtr.terr_id = jtq.terr_id
96: and jtr.resource_id = decode(lc_resource_id ,null, jtr.resource_id, lc_resource_id)
97: and jtq.qual_usg_id = decode(lc_qual_usg_id ,null, jtq.qual_usg_id, lc_qual_usg_id)
98: AND jtr.terr_id = j.terr_id

Line 105: from jtf_terr_transactions_v

101: -- qualifier types
102: CURSOR c_get_qual_types(ci_terr_id NUMBER) IS
103: select qual_type_usg_id, WF_NOTIFICATION.SubstituteSpecialChars(qualifier_type_name) qualifier_type_name,
104: WF_NOTIFICATION.SubstituteSpecialChars(qualifier_type_description) qualifier_type_description
105: from jtf_terr_transactions_v
106: where terr_id = ci_terr_id;
107:
108:
109: -- dynamic qualifiers

Line 116: FROM jtf_terr_qualifiers_v

112: TERR_ID,
113: QUAL_USG_ID,
114: ORG_ID,
115: WF_NOTIFICATION.SubstituteSpecialChars(qualifier_name) qualifier_name
116: FROM jtf_terr_qualifiers_v
117: WHERE qualifier_type_name <> 'RESOURCE'
118: and terr_id = ci_terr_id
119: and terr_qual_id is not null -- added becuse we have some real bad data in jtadom
120: ;

Line 130: JTF_TERR_QUAL_ALL JTQ ,

126: JTQ.QUAL_USG_ID,
127: JTQ.ORG_ID,
128: WF_NOTIFICATION.SUBSTITUTESPECIALCHARS(JSQ.NAME) QUALIFIER_NAME
129: FROM
130: JTF_TERR_QUAL_ALL JTQ ,
131: JTF_QUAL_USGS_ALL JQU ,
132: JTF_SEEDED_QUAL_ALL_TL JSQ ,
133: JTF_QUAL_TYPE_USGS_ALL JQTU ,
134: JTF_QUAL_TYPES JQT

Line 153: FROM jtf_terr_qualifiers_v

149: TERR_ID,
150: QUAL_USG_ID,
151: ORG_ID,
152: WF_NOTIFICATION.SubstituteSpecialChars(qualifier_name) qualifier_name
153: FROM jtf_terr_qualifiers_v
154: WHERE qualifier_type_name = 'RESOURCE'
155: and terr_id = ci_terr_id;
156:
157:

Line 167: ,decode(j1.id_used_flag, 'Y', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char_id, NULL)

163: -- , WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR_DESC) LOW_VALUE_CHAR_DESC
164: --, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR_DESC) HIGH_VALUE_CHAR_DESC
165: -- Commented for bug 8365663
166: ,decode(j2.display_type, 'CHAR'
167: ,decode(j1.id_used_flag, 'Y', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char_id, NULL)
168: ,'N', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL), NULL)
169: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)
170: ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR)) low_value_char_desc
171:

Line 168: ,'N', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL), NULL)

164: --, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR_DESC) HIGH_VALUE_CHAR_DESC
165: -- Commented for bug 8365663
166: ,decode(j2.display_type, 'CHAR'
167: ,decode(j1.id_used_flag, 'Y', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char_id, NULL)
168: ,'N', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL), NULL)
169: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)
170: ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR)) low_value_char_desc
171:
172: ,decode(j2.display_type, 'CHAR'

Line 169: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)

165: -- Commented for bug 8365663
166: ,decode(j2.display_type, 'CHAR'
167: ,decode(j1.id_used_flag, 'Y', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char_id, NULL)
168: ,'N', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL), NULL)
169: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)
170: ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR)) low_value_char_desc
171:
172: ,decode(j2.display_type, 'CHAR'
173: ,jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.high_value_char, NULL)

Line 173: ,jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.high_value_char, NULL)

169: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)
170: ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR)) low_value_char_desc
171:
172: ,decode(j2.display_type, 'CHAR'
173: ,jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.high_value_char, NULL)
174: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.low_value_char_id, NULL)
175: , WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR)) high_value_char_desc
176: , j1.LOW_VALUE_NUMBER
177: , j1.HIGH_VALUE_NUMBER

Line 174: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.low_value_char_id, NULL)

170: ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR)) low_value_char_desc
171:
172: ,decode(j2.display_type, 'CHAR'
173: ,jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.high_value_char, NULL)
174: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.low_value_char_id, NULL)
175: , WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR)) high_value_char_desc
176: , j1.LOW_VALUE_NUMBER
177: , j1.HIGH_VALUE_NUMBER
178: , j1.INTEREST_TYPE

Line 191: -- more to come directly from the jtf_terr_values_desc_v view

187: , j1.LOW_VALUE_DATE
188: , j1.HIGH_VALUE_DATE
189: , DISPLAY_TYPE
190: , CONVERT_TO_ID_FLAG
191: -- more to come directly from the jtf_terr_values_desc_v view
192: FROM jtf_terr_values_desc_v j1
193: WHERE j1.terr_qual_id = ci_terr_qual_id
194: and j1.terr_value_id is not null -- added becuse we have some real bad data in jtadom
195: ORDER BY j1.LOW_VALUE_CHAR_DESC, j1.COMPARISON_OPERATOR;*/--COmmented for bug 7237992

Line 192: FROM jtf_terr_values_desc_v j1

188: , j1.HIGH_VALUE_DATE
189: , DISPLAY_TYPE
190: , CONVERT_TO_ID_FLAG
191: -- more to come directly from the jtf_terr_values_desc_v view
192: FROM jtf_terr_values_desc_v j1
193: WHERE j1.terr_qual_id = ci_terr_qual_id
194: and j1.terr_value_id is not null -- added becuse we have some real bad data in jtadom
195: ORDER BY j1.LOW_VALUE_CHAR_DESC, j1.COMPARISON_OPERATOR;*/--COmmented for bug 7237992
196: /*

Line 216: FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3

212: , null LOW_VALUE_DATE
213: , null HIGH_VALUE_DATE
214: , DISPLAY_TYPE
215: , CONVERT_TO_ID_FLAG
216: FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3
217: WHERE j1.terr_qual_id = ci_terr_qual_id
218: AND j1.terr_qual_id = j3.terr_qual_id
219: AND j2.QUAL_USG_ID = j3.QUAL_USG_ID
220: AND j1.terr_value_id is not null

Line 231: ,decode(j1.id_used_flag, 'Y', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char_id, NULL)

227: --, WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR) LOW_VALUE_CHAR_DESC
228: --, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR) HIGH_VALUE_CHAR_DESC
229: -- Commented for bug 8365663
230: ,decode(j2.display_type, 'CHAR'
231: ,decode(j1.id_used_flag, 'Y', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char_id, NULL)
232: ,'N', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL), NULL)
233: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)
234: ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR))
235: low_value_char_desc

Line 232: ,'N', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL), NULL)

228: --, WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR) HIGH_VALUE_CHAR_DESC
229: -- Commented for bug 8365663
230: ,decode(j2.display_type, 'CHAR'
231: ,decode(j1.id_used_flag, 'Y', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char_id, NULL)
232: ,'N', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL), NULL)
233: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)
234: ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR))
235: low_value_char_desc
236:

Line 233: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)

229: -- Commented for bug 8365663
230: ,decode(j2.display_type, 'CHAR'
231: ,decode(j1.id_used_flag, 'Y', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char_id, NULL)
232: ,'N', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL), NULL)
233: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.low_value_char, NULL)
234: ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR))
235: low_value_char_desc
236:
237: ,decode(j2.display_type, 'CHAR'

Line 238: ,jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.high_value_char, NULL)

234: ,WF_NOTIFICATION.SubstituteSpecialChars(j1.LOW_VALUE_CHAR))
235: low_value_char_desc
236:
237: ,decode(j2.display_type, 'CHAR'
238: ,jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.high_value_char, NULL)
239: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.low_value_char_id, NULL)
240: , WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR))
241: high_value_char_desc
242: , j1.LOW_VALUE_NUMBER

Line 239: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.low_value_char_id, NULL)

235: low_value_char_desc
236:
237: ,decode(j2.display_type, 'CHAR'
238: ,jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.high_value_char, NULL)
239: ,'DEP_2FIELDS_1CHAR_1ID', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.low_value_char_id, NULL)
240: , WF_NOTIFICATION.SubstituteSpecialChars(j1.HIGH_VALUE_CHAR))
241: high_value_char_desc
242: , j1.LOW_VALUE_NUMBER
243: , j1.HIGH_VALUE_NUMBER

Line 247: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.interest_type_id, NULL), NULL) INTEREST_TYPE

243: , j1.HIGH_VALUE_NUMBER
244: -- , j1.INTEREST_TYPE_ID INTEREST_TYPE
245: -- , j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
246: -- , j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
247: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.interest_type_id, NULL), NULL) INTEREST_TYPE
248: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.primary_interest_code_id, NULL), NULL) PRIMARY_INTEREST_CODE
249: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.secondary_interest_code_id, NULL), NULL) SECONDARY_INTEREST_CODE
250:
251: , j1.CURRENCY_CODE CURRENCY_DESC

Line 248: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.primary_interest_code_id, NULL), NULL) PRIMARY_INTEREST_CODE

244: -- , j1.INTEREST_TYPE_ID INTEREST_TYPE
245: -- , j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
246: -- , j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
247: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.interest_type_id, NULL), NULL) INTEREST_TYPE
248: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.primary_interest_code_id, NULL), NULL) PRIMARY_INTEREST_CODE
249: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.secondary_interest_code_id, NULL), NULL) SECONDARY_INTEREST_CODE
250:
251: , j1.CURRENCY_CODE CURRENCY_DESC
252: , j1.LOW_VALUE_CHAR_ID

Line 249: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.secondary_interest_code_id, NULL), NULL) SECONDARY_INTEREST_CODE

245: -- , j1.PRIMARY_INTEREST_CODE_ID PRIMARY_INTEREST_CODE
246: -- , j1.SECONDARY_INTEREST_CODE_ID SECONDARY_INTEREST_CODE
247: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.interest_type_id, NULL), NULL) INTEREST_TYPE
248: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.primary_interest_code_id, NULL), NULL) PRIMARY_INTEREST_CODE
249: , decode(j2.display_type, 'INTEREST_TYPE', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.secondary_interest_code_id, NULL), NULL) SECONDARY_INTEREST_CODE
250:
251: , j1.CURRENCY_CODE CURRENCY_DESC
252: , j1.LOW_VALUE_CHAR_ID
253: , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME

Line 254: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, j1.value2_id),

250:
251: , j1.CURRENCY_CODE CURRENCY_DESC
252: , j1.LOW_VALUE_CHAR_ID
253: , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
254: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, j1.value2_id),
255: 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_2FIELDS',
256: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
257: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), NULL)) VALUE1_DESC
258: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,

Line 255: 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_2FIELDS',

251: , j1.CURRENCY_CODE CURRENCY_DESC
252: , j1.LOW_VALUE_CHAR_ID
253: , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
254: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, j1.value2_id),
255: 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_2FIELDS',
256: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
257: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), NULL)) VALUE1_DESC
258: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,
259: j1.value2_id, j1.value3_id), 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999),

Line 256: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',

252: , j1.LOW_VALUE_CHAR_ID
253: , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
254: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, j1.value2_id),
255: 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_2FIELDS',
256: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
257: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), NULL)) VALUE1_DESC
258: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,
259: j1.value2_id, j1.value3_id), 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999),
260: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999), NULL)) VALUE2_DESC

Line 257: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), NULL)) VALUE1_DESC

253: , WF_NOTIFICATION.SubstituteSpecialChars(j1.CNR_GROUP_ID) CNR_GROUP_NAME
254: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, j1.value2_id),
255: 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_2FIELDS',
256: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
257: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), NULL)) VALUE1_DESC
258: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,
259: j1.value2_id, j1.value3_id), 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999),
260: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999), NULL)) VALUE2_DESC
261: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_3FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, NULL),

Line 258: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,

254: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, j1.value2_id),
255: 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_2FIELDS',
256: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
257: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), NULL)) VALUE1_DESC
258: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,
259: j1.value2_id, j1.value3_id), 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999),
260: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999), NULL)) VALUE2_DESC
261: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_3FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, NULL),
262: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, j1.value4_id), NULL)) VALUE3_DESC

Line 259: j1.value2_id, j1.value3_id), 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999),

255: 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_2FIELDS',
256: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
257: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), NULL)) VALUE1_DESC
258: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,
259: j1.value2_id, j1.value3_id), 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999),
260: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999), NULL)) VALUE2_DESC
261: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_3FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, NULL),
262: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, j1.value4_id), NULL)) VALUE3_DESC
263: , null LOW_VALUE_DATE

Line 260: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999), NULL)) VALUE2_DESC

256: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), 'DEP_3FIELDS_CHAR_3IDS',
257: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), NULL)) VALUE1_DESC
258: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,
259: j1.value2_id, j1.value3_id), 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999),
260: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999), NULL)) VALUE2_DESC
261: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_3FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, NULL),
262: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, j1.value4_id), NULL)) VALUE3_DESC
263: , null LOW_VALUE_DATE
264: , null HIGH_VALUE_DATE

Line 261: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_3FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, NULL),

257: jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql1, j1.value1_id, -9999), NULL)) VALUE1_DESC
258: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,
259: j1.value2_id, j1.value3_id), 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999),
260: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999), NULL)) VALUE2_DESC
261: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_3FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, NULL),
262: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, j1.value4_id), NULL)) VALUE3_DESC
263: , null LOW_VALUE_DATE
264: , null HIGH_VALUE_DATE
265: , DISPLAY_TYPE

Line 262: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, j1.value4_id), NULL)) VALUE3_DESC

258: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_2FIELDS_CHAR_2IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2,
259: j1.value2_id, j1.value3_id), 'DEP_2FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999),
260: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql2, j1.value2_id, -9999), NULL)) VALUE2_DESC
261: , WF_NOTIFICATION.SubstituteSpecialChars(decode(j2.display_type, 'DEP_3FIELDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, NULL),
262: 'DEP_3FIELDS_CHAR_3IDS', jtf_territory_pvt.get_terr_value_desc(j2.convert_to_id_flag, j2.display_type, j2.column_count, j2.display_sql3, j1.value3_id, j1.value4_id), NULL)) VALUE3_DESC
263: , null LOW_VALUE_DATE
264: , null HIGH_VALUE_DATE
265: , DISPLAY_TYPE
266: , CONVERT_TO_ID_FLAG

Line 267: FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3

263: , null LOW_VALUE_DATE
264: , null HIGH_VALUE_DATE
265: , DISPLAY_TYPE
266: , CONVERT_TO_ID_FLAG
267: FROM jtf_terr_values_all j1, JTF_QUAL_USGS_ALL j2, JTF_TERR_QUAL_ALL j3
268: WHERE j1.terr_qual_id = ci_terr_qual_id
269: AND j1.terr_qual_id = j3.terr_qual_id
270: AND j2.QUAL_USG_ID = j3.QUAL_USG_ID
271: AND j1.terr_value_id is not null

Line 276: from jtf_terr_resources_v jtrv

272: ORDER BY j1.COMPARISON_OPERATOR;
273: -- resources
274: CURSOR c_get_resource(ci_terr_id NUMBER) IS
275: /* select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(resource_name) resource_name, resource_type, terr_rsc_id
276: from jtf_terr_resources_v jtrv
277: where jtrv.terr_id = ci_terr_id
278: order by resource_name;
279: */
280: select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(jtf_territory_resource_pvt.get_resource_name(

Line 280: select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(jtf_territory_resource_pvt.get_resource_name(

276: from jtf_terr_resources_v jtrv
277: where jtrv.terr_id = ci_terr_id
278: order by resource_name;
279: */
280: select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(jtf_territory_resource_pvt.get_resource_name(
281: RESOURCE_ID , DECODE( RESOURCE_TYPE , 'RS_SUPPLIER', 'RS_SUPPLIER_CONTACT' ,
282: RESOURCE_TYPE ) )) resource_name, resource_type, terr_rsc_id
283: from JTF_TERR_RSC
284: where terr_id = ci_terr_id

Line 283: from JTF_TERR_RSC

279: */
280: select resource_id, WF_NOTIFICATION.SubstituteSpecialChars(jtf_territory_resource_pvt.get_resource_name(
281: RESOURCE_ID , DECODE( RESOURCE_TYPE , 'RS_SUPPLIER', 'RS_SUPPLIER_CONTACT' ,
282: RESOURCE_TYPE ) )) resource_name, resource_type, terr_rsc_id
283: from JTF_TERR_RSC
284: where terr_id = ci_terr_id
285: order by resource_name;
286:
287: -- resource accesses

Line 290: from jtf_terr_rsc_access_v

286:
287: -- resource accesses
288: CURSOR c_get_rsc_access(ci_terr_rsc_id NUMBER) IS
289: select terr_rsc_access_id, access_type, WF_NOTIFICATION.SubstituteSpecialChars(meaning) meaning
290: from jtf_terr_rsc_access_v
291: where terr_rsc_id = ci_terr_rsc_id;
292:
293: lp_sysdate DATE := SYSDATE;
294: l_match_qual NUMBER := 0;

Line 303: FROM hr_operating_units hr, jtf_terr_all jt

299:
300: /*Added for bug 7315889 */
301: CURSOR c_get_operating_unit(ci_terr_id VARCHAR2) IS
302: SELECT distinct hr.name operating_unit
303: FROM hr_operating_units hr, jtf_terr_all jt
304: WHERE hr.organization_id = jt.org_id
305: AND jt.terr_id = ci_terr_id;
306:
307: -- ADDED FOR BUG 7315889

Line 312: --dbms_output.put_line('JTF_TERR_JSP_REPORTS.definition_rpt: BEGIN ');

308: l_terr_operating_unit hr_operating_units.name%TYPE;
309:
310: begin
311:
312: --dbms_output.put_line('JTF_TERR_JSP_REPORTS.definition_rpt: BEGIN ');
313: --dbms_output.put_line(p_param1 || ' / ' || p_param2 || ' / ' || p_param3 || ' / ' || p_param4 || ' / ' || p_param5);
314: -- loop through cur_terr
315: l_out_index := 0;
316:

Line 322: from jtf_terr_all j

318: if p_rpt_type = 'LOOKUP_TERR' and p_param5 IS NOT NULL then
319: open terr_name_cur for
320: select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
321: j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
322: from jtf_terr_all j
323: where j.terr_id = p_param5;
324:
325:
326: -- ADDED FOR BUG 7315889

Line 350: from jtf_terr j

346: --dbms_output.put_line('get territory by property');
347: open terr_name_cur for
348: select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
349: j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
350: from jtf_terr j
351: WHERE ( TRUNC(j.end_date_active) >= NVL(p_param4, lp_sysdate)
352: AND
353: TRUNC(j.start_date_active) <= NVL(p_param4, lp_sysdate)
354: )

Line 357: from jtf_terr_rsc jtr, jtf_terr_qual jtq, jtf_terr_usgs jtu

353: TRUNC(j.start_date_active) <= NVL(p_param4, lp_sysdate)
354: )
355: AND EXISTS
356: ( select jtr.terr_id
357: from jtf_terr_rsc jtr, jtf_terr_qual jtq, jtf_terr_usgs jtu
358: where jtr.terr_id = jtq.terr_id
359: and jtr.terr_id = jtu.terr_id
360: and jtr.resource_id = decode(l_resource_id ,null, jtr.resource_id, l_resource_id)
361: /* ARPATEL: 10/16, bug#2832442 */

Line 369: FROM jtf_terr_all jt

365: AND jtr.terr_id = j.terr_id
366: )
367: AND NOT EXISTS (
368: SELECT jt.terr_id
369: FROM jtf_terr_all jt
370: WHERE ( ( NVL(jt.end_date_active, lp_sysdate) <= NVL(p_param4, lp_sysdate) ) OR
371: ( NVL(jt.start_date_active, lp_sysdate) > NVL(p_param4, lp_sysdate) )
372: )
373: CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1

Line 380: from jtf_terr j

376: --dbms_output.put_line('get territory by_id mode ');
377: open terr_name_cur for
378: select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
379: j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
380: from jtf_terr j
381: where j.terr_id = p_param5;
382:
383: end if;
384: elsif p_rpt_type = 'CHANGES' then

Line 389: from jtf_terr j, jtf_terr_usgs jtu

385: --dbms_output.put_line('creating changes cursor ');
386: open terr_name_cur for
387: select j.terr_id, WF_NOTIFICATION.SubstituteSpecialChars(j.name) name, j.rank, j.start_date_active, j.end_date_active,
388: j.last_update_date, WF_NOTIFICATION.SubstituteSpecialChars(j.description) description
389: from jtf_terr j, jtf_terr_usgs jtu
390: where 1=1
391: --and j.terr_id = '19027'
392: AND j.terr_id = jtu.terr_id
393: AND j.start_date_active <= sysdate

Line 403: FROM jtf_terr_all jt

399: )
400: AND jtu.source_id = NVL(p_param3, jtu.source_id)
401: AND NOT EXISTS (
402: SELECT jt.terr_id
403: FROM jtf_terr_all jt
404: WHERE ( ( NVL(jt.end_date_active, lp_sysdate) <= NVL(p_param4, lp_sysdate) ) OR
405: ( NVL(jt.start_date_active, lp_sysdate) >= NVL(p_param4, lp_sysdate) )
406: )
407: CONNECT BY PRIOR jt.parent_territory_id = jt.terr_id AND jt.terr_id <> 1

Line 869: FROM jtf_terr_all jt

865: ROUND((atc.ACTIVE_TERR_COUNT / decode(arc.ACTIVE_DIST_REP_COUNT, 0, 1, arc.ACTIVE_DIST_REP_COUNT)),2) TERR_PER_REP
866: FROM
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,

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 877: FROM jtf_terr_all jt

873: ) atc,
874:
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

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 882: FROM jtf_terr_rsc_all jtr

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)
884: AND jtr.terr_id = jt.terr_id )
885: AND EXISTS ( SELECT jtr.terr_id
886: FROM jtf_terr_rsc_all jtr

Line 886: FROM jtf_terr_rsc_all jtr

882: FROM jtf_terr_rsc_all jtr
883: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)
884: AND jtr.terr_id = jt.terr_id )
885: AND EXISTS ( SELECT jtr.terr_id
886: FROM jtf_terr_rsc_all jtr
887: WHERE jtr.role IN ('SALES_REP', 'Sales Representative', 'SALES_MANAGER', 'Sales Manager', NULL)
888: AND jtr.terr_id = jt.terr_id )
889: ) tdac,
890:

Line 893: FROM jtf_terr_all jt

889: ) tdac,
890:
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

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 902: FROM jtf_terr_all jt

898: ) tcc,
899:
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

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 911: FROM jtf_terr_all jt

907: ) tsdc,
908:
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

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 920: FROM jtf_terr_rsc_all jtr

916: ) tuc,
917:
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)

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 949: FROM jtf_terr_all jt, hr_organization_units hou

945: ( SELECT
946: hou.name,
947: hou.organization_id,
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)

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 962: FROM jtf_terr_all jt, hr_organization_units hou

958: ( SELECT
959: hou.name,
960: hou.organization_id,
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)

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 968: FROM jtf_terr_rsc_all jtr

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)
970: AND jtr.terr_id = jt.terr_id )
971: AND EXISTS ( SELECT jtr.terr_id
972: FROM jtf_terr_rsc_all jtr

Line 972: FROM jtf_terr_rsc_all jtr

968: FROM jtf_terr_rsc_all jtr
969: WHERE jtr.role IN ('TELESALES_AGENT', 'Telesales Agent', 'TELESALES_MANAGER', 'Telesales Manager', NULL)
970: AND jtr.terr_id = jt.terr_id )
971: AND EXISTS ( SELECT jtr.terr_id
972: FROM jtf_terr_rsc_all jtr
973: WHERE jtr.role IN ('SALES_REP', 'Sales Representative', 'SALES_MANAGER', 'Sales Manager', NULL)
974: AND jtr.terr_id = jt.terr_id )
975: GROUP BY hou.name, hou.organization_id
976: ) tdac,

Line 983: FROM jtf_terr_all jt, hr_organization_units hou

979: ( SELECT
980: hou.name,
981: hou.organization_id,
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 )

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 997: FROM jtf_terr_all jt, hr_organization_units hou

993: ( SELECT
994: hou.name,
995: hou.organization_id,
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 )

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 1011: FROM jtf_terr_all jt, hr_organization_units hou

1007: ( SELECT
1008: hou.name,
1009: hou.organization_id,
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 )

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 1025: FROM jtf_terr_rsc_all jtr, hr_organization_units hou

1021: ( SELECT
1022: hou.name,
1023: hou.organization_id,
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'

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 1046: FROM jtf_terr_all jt

1042:
1043: cursor c_NONACTIVE_GLOBAL IS
1044: SELECT 'All' name,
1045: COUNT(*) INACTIVE_TERR_COUNT
1046: FROM jtf_terr_all jt
1047: WHERE
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)

Line 1049: from jtf_terr_usgs_all jtua

1045: COUNT(*) INACTIVE_TERR_COUNT
1046: FROM jtf_terr_all jt
1047: WHERE
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 );

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 1060: FROM jtf_terr_all jt, hr_organization_units hou

1056: SELECT
1057: hou.name name,
1058: COUNT(*) INACTIVE_TERR_COUNT,
1059: hou.organization_id org_id
1060: FROM jtf_terr_all jt, hr_organization_units hou
1061: WHERE jt.org_id = hou.organization_id
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)

Line 1063: from jtf_terr_usgs_all jtua

1059: hou.organization_id org_id
1060: FROM jtf_terr_all jt, hr_organization_units hou
1061: WHERE jt.org_id = hou.organization_id
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 )

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: