APPS.BIX_EMAILS_LOAD_PKG dependencies on JTF_IH_INTERACTION_INTERS

Line 137: interactions in jtf_ih_interaction_inters table. If the customer reply back to the agent response then the entry will

133: END init;
134:
135:
136: /* This procedure collects One, two , three and Four done resolutiions. Email center keeps the threads of email
137: interactions in jtf_ih_interaction_inters table. If the customer reply back to the agent response then the entry will
138: be created in the above table with old and new interaction. This table stores both parent and child interaction id.
139:
140: We can find the depth of thread from this table. If the table does not have entry in this
141: table for replied email, then the email interaction is one and done ,

Line 144: jtf_ih_interaction_inters:

140: We can find the depth of thread from this table. If the table does not have entry in this
141: table for replied email, then the email interaction is one and done ,
142: if the depth is 1 then it is two and done and so on.
143:
144: jtf_ih_interaction_inters:
145: This table gets an entry the moment the customer replies to an email. If it doesnt have an entry it
146: means it is ONE AND DONE provided there is a reply.
147:
148: INTERACT_INTERACTION_IDRELATES is the parent interaction

Line 232: FROM jtf_ih_interaction_inters intr3

228: instr(sys_connect_by_path(intr3.interact_interaction_idrelates, ':'), ':', 2)-2))) root_interaction_id,
229: intr3.interact_interaction_idrelates parent,
230: intr3.interact_interaction_id child,
231: level depth
232: FROM jtf_ih_interaction_inters intr3
233: START WITH intr3.interact_interaction_idrelates in
234: (select
235: intr2.interact_interaction_idrelates
236: from jtf_ih_interaction_inters intr2

Line 236: from jtf_ih_interaction_inters intr2

232: FROM jtf_ih_interaction_inters intr3
233: START WITH intr3.interact_interaction_idrelates in
234: (select
235: intr2.interact_interaction_idrelates
236: from jtf_ih_interaction_inters intr2
237: where intr2.interact_interaction_idrelates not in
238: (
239: select
240: intr1.interact_interaction_id

Line 241: from jtf_ih_interaction_inters intr1

237: where intr2.interact_interaction_idrelates not in
238: (
239: select
240: intr1.interact_interaction_id
241: from jtf_ih_interaction_inters intr1
242: )
243: )
244: CONNECT BY intr3.interact_interaction_idrelates = PRIOR intr3.interact_interaction_id
245: ) intr

Line 256: select 1 from jtf_ih_interaction_inters inter

252:
253: ) GROUP BY ROOT_INTERACTION_ID
254: ) iview/* Added */
255: WHERE NOT EXISTS (
256: select 1 from jtf_ih_interaction_inters inter
257: WHERE iview.interaction_id = inter.interact_interaction_id
258: )
259: group by interaction_id
260: ) iview

Line 272: FROM jtf_ih_interaction_inters inter)

268: AND mitm.classification = irc.name(+)
269: AND intr.interaction_id NOT IN (
270: SELECT
271: inter.interact_interaction_id
272: FROM jtf_ih_interaction_inters inter)
273: GROUP BY intr.interaction_id;
274:
275:
276: