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.IEX_UWQ_POP_SUM_TBL_PVT dependencies on AR_TRX_BAL_SUMMARY

Line 126: SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS

122: is
123: --Begin bug#7133605 schekuri 09-Jun-2008
124: --Start bug 6876187 gnramasa 14th mar 08
125: CURSOR c_cust_account_id_1 IS
126: SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
127: WHERE ARS.REFERENCE_1 IS Null
128: AND EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
129: IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
130: AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID);

Line 134: SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS

130: AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID);
131: -- and ied.org_id=nvl(p_org_id,ied.org_id));
132:
133: CURSOR c_cust_account_id_n IS
134: SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
135: WHERE ARS.REFERENCE_1 = 1
136: AND NOT EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
137: IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
138: AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID);

Line 150: SELECT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS

146:
147: --End bug 6876187 gnramasa 14th mar 08
148:
149: CURSOR c_cust_account_id_dln(p_cust_account_id number) IS
150: SELECT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
151: WHERE ARS.REFERENCE_1 = 1
152: AND ARS.CUST_ACCOUNT_ID=P_CUST_ACCOUNT_ID
153: AND NOT EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
154: IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')

Line 162: SELECT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS

158:
159: l_cust_account_id_dln cust_account_id_list_n;
160:
161: CURSOR C_CUST_ACCOUNT_ID_DL1(p_cust_account_id number) IS
162: SELECT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS
163: WHERE ARS.REFERENCE_1 IS Null
164: AND ARS.CUST_ACCOUNT_ID=P_CUST_ACCOUNT_ID
165: -- and ars.org_id=nvl(p_org_id,ars.org_id)
166: for update of reference_1 nowait;

Line 175: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updating Reference_1 of AR_TRX_BAL_SUMMARY for Delinquent Customers');

171:
172: begin
173: /* Begin Kasreeni 3/1/2007 Bug 5905023 We will update everytime instead of once */
174: -- if (p_mode = 'CP') then
175: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updating Reference_1 of AR_TRX_BAL_SUMMARY for Delinquent Customers');
176: --Start bug 6876187 gnramasa 14th mar 08
177: --update ar_trx_bal_summary set reference_1 = 1;
178: --Begin bug#7133605 schekuri 09-Jun-2008
179: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = 1...');

Line 177: --update ar_trx_bal_summary set reference_1 = 1;

173: /* Begin Kasreeni 3/1/2007 Bug 5905023 We will update everytime instead of once */
174: -- if (p_mode = 'CP') then
175: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updating Reference_1 of AR_TRX_BAL_SUMMARY for Delinquent Customers');
176: --Start bug 6876187 gnramasa 14th mar 08
177: --update ar_trx_bal_summary set reference_1 = 1;
178: --Begin bug#7133605 schekuri 09-Jun-2008
179: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = 1...');
180: FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting to update ar_trx_bal_summary with reference_1 = 1...');
181: BEGIN

Line 179: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = 1...');

175: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updating Reference_1 of AR_TRX_BAL_SUMMARY for Delinquent Customers');
176: --Start bug 6876187 gnramasa 14th mar 08
177: --update ar_trx_bal_summary set reference_1 = 1;
178: --Begin bug#7133605 schekuri 09-Jun-2008
179: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = 1...');
180: FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting to update ar_trx_bal_summary with reference_1 = 1...');
181: BEGIN
182: OPEN c_cust_account_id_1;
183: LOOP

Line 180: FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting to update ar_trx_bal_summary with reference_1 = 1...');

176: --Start bug 6876187 gnramasa 14th mar 08
177: --update ar_trx_bal_summary set reference_1 = 1;
178: --Begin bug#7133605 schekuri 09-Jun-2008
179: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = 1...');
180: FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting to update ar_trx_bal_summary with reference_1 = 1...');
181: BEGIN
182: OPEN c_cust_account_id_1;
183: LOOP
184: FETCH c_cust_account_id_1 BULK COLLECT INTO

Line 188: IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = 1...');

184: FETCH c_cust_account_id_1 BULK COLLECT INTO
185: l_cust_account_id_1 LIMIT G_BATCH_SIZE;
186: IF l_cust_account_id_1.count = 0 THEN
187:
188: IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = 1...');
189:
190: CLOSE c_cust_account_id_1;
191: EXIT;
192: ELSE

Line 194: UPDATE AR_TRX_BAL_SUMMARY ARS

190: CLOSE c_cust_account_id_1;
191: EXIT;
192: ELSE
193: FORALL I IN l_cust_account_id_1.first..l_cust_account_id_1.last
194: UPDATE AR_TRX_BAL_SUMMARY ARS
195: SET REFERENCE_1 = '1'
196: WHERE CUST_ACCOUNT_ID = l_cust_account_id_1(I)
197: and reference_1 is null;
198: l_cust_account_id_1.delete;

Line 202: IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');

198: l_cust_account_id_1.delete;
199:
200: commit;
201:
202: IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');
203: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Rows updated in ar_trx_bal_summary with reference_1 = 1-->'||l_cust_account_id_1.count);
204:
205: END IF;
206: END LOOP;

Line 203: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Rows updated in ar_trx_bal_summary with reference_1 = 1-->'||l_cust_account_id_1.count);

199:
200: commit;
201:
202: IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');
203: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Rows updated in ar_trx_bal_summary with reference_1 = 1-->'||l_cust_account_id_1.count);
204:
205: END IF;
206: END LOOP;
207: EXCEPTION

Line 210: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deadlock detected when updating ar_trx_bal_summary.reference to 1' || sqlerrm);

206: END LOOP;
207: EXCEPTION
208:
209: WHEN deadlock_detected THEN
210: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deadlock detected when updating ar_trx_bal_summary.reference to 1' || sqlerrm);
211: LogMessage(FND_LOG.LEVEL_STATEMENT,' Deadlock detected when updating ar_trx_bal_summary.reference to 1.');
212: ROLLBACK;
213: l_cust_account_id_1.delete;
214: if c_cust_account_id_1%ISOPEN then

Line 211: LogMessage(FND_LOG.LEVEL_STATEMENT,' Deadlock detected when updating ar_trx_bal_summary.reference to 1.');

207: EXCEPTION
208:
209: WHEN deadlock_detected THEN
210: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deadlock detected when updating ar_trx_bal_summary.reference to 1' || sqlerrm);
211: LogMessage(FND_LOG.LEVEL_STATEMENT,' Deadlock detected when updating ar_trx_bal_summary.reference to 1.');
212: ROLLBACK;
213: l_cust_account_id_1.delete;
214: if c_cust_account_id_1%ISOPEN then
215: close c_cust_account_id_1;

Line 222: IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = 1 in Deadlock handler');

218: LOOP
219: FETCH c_cust_account_id_1 BULK COLLECT INTO
220: l_cust_account_id_1 LIMIT G_BATCH_SIZE;
221: IF l_cust_account_id_1.count = 0 THEN
222: IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = 1 in Deadlock handler');
223: CLOSE c_cust_account_id_1;
224: EXIT;
225: ELSE
226: FOR i IN l_cust_account_id_1.first..l_cust_account_id_1.last

Line 232: UPDATE AR_TRX_BAL_SUMMARY

228: BEGIN
229: OPEN C_CUST_ACCOUNT_ID_DL1(l_cust_account_id_1(i));
230: FETCH C_CUST_ACCOUNT_ID_DL1 into l_cust_account_id1;
231: EXIT WHEN C_CUST_ACCOUNT_ID_DL1%NOTFOUND;
232: UPDATE AR_TRX_BAL_SUMMARY
233: SET REFERENCE_1 = '1'
234: WHERE cust_account_id=l_cust_account_id1
235: and REFERENCE_1 is null;
236: FND_FILE.PUT_LINE(FND_FILE.LOG,'updated records- '||l_cust_account_id_1.count);

Line 254: IEX_DEBUG_PUB.LOGMESSAGE(l_cust_account_id_1.count || ' rows updated in ar_trx_bal_summary with reference_1 = 1');

250: END;
251:
252: END LOOP;
253:
254: IEX_DEBUG_PUB.LOGMESSAGE(l_cust_account_id_1.count || ' rows updated in ar_trx_bal_summary with reference_1 = 1');
255: l_cust_account_id_1.delete;
256: commit;
257:
258: END IF;

Line 261: FND_FILE.PUT_LINE(FND_FILE.LOG,'Locked by another session when updating ar_trx_bal_summary.reference to 1');

257:
258: END IF;
259: END LOOP;
260: WHEN locked_by_another_session THEN
261: FND_FILE.PUT_LINE(FND_FILE.LOG,'Locked by another session when updating ar_trx_bal_summary.reference to 1');
262: if c_cust_account_id_1%ISOPEN then
263: close c_cust_account_id_1;
264: end if;
265: ROLLBACK;

Line 268: IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = 1');

264: end if;
265: ROLLBACK;
266:
267: WHEN OTHERS THEN
268: IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = 1');
269: END;
270:
271: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = Null...');
272: BEGIN

Line 271: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = Null...');

267: WHEN OTHERS THEN
268: IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = 1');
269: END;
270:
271: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = Null...');
272: BEGIN
273: OPEN c_cust_account_id_n;
274: LOOP
275: FETCH c_cust_account_id_n BULK COLLECT INTO

Line 278: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exit after Update ar_trx_bal_summary on complete with reference_1 = Null...');

274: LOOP
275: FETCH c_cust_account_id_n BULK COLLECT INTO
276: l_cust_account_id_n LIMIT G_BATCH_SIZE;
277: IF l_cust_account_id_n.count = 0 THEN
278: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exit after Update ar_trx_bal_summary on complete with reference_1 = Null...');
279: -- FND_FILE.PUT_LINE(FND_FILE.LOG,SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = Null');
280: CLOSE c_cust_account_id_n;
281: EXIT;
282: ELSE

Line 279: -- FND_FILE.PUT_LINE(FND_FILE.LOG,SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = Null');

275: FETCH c_cust_account_id_n BULK COLLECT INTO
276: l_cust_account_id_n LIMIT G_BATCH_SIZE;
277: IF l_cust_account_id_n.count = 0 THEN
278: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exit after Update ar_trx_bal_summary on complete with reference_1 = Null...');
279: -- FND_FILE.PUT_LINE(FND_FILE.LOG,SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = Null');
280: CLOSE c_cust_account_id_n;
281: EXIT;
282: ELSE
283: FORALL I IN l_cust_account_id_n.first..l_cust_account_id_n.last

Line 284: UPDATE AR_TRX_BAL_SUMMARY ARS

280: CLOSE c_cust_account_id_n;
281: EXIT;
282: ELSE
283: FORALL I IN l_cust_account_id_n.first..l_cust_account_id_n.last
284: UPDATE AR_TRX_BAL_SUMMARY ARS
285: SET REFERENCE_1 = Null
286: WHERE CUST_ACCOUNT_ID = l_cust_account_id_n(I)
287: and reference_1='1';
288: l_cust_account_id_n.delete;

Line 290: FND_FILE.PUT_LINE(FND_FILE.LOG,' Rows updated in ar_trx_bal_summary with reference_1 = Null->'||l_cust_account_id_n.count);

286: WHERE CUST_ACCOUNT_ID = l_cust_account_id_n(I)
287: and reference_1='1';
288: l_cust_account_id_n.delete;
289: commit;
290: FND_FILE.PUT_LINE(FND_FILE.LOG,' Rows updated in ar_trx_bal_summary with reference_1 = Null->'||l_cust_account_id_n.count);
291: LogMessage(FND_LOG.LEVEL_UNEXPECTED,l_cust_account_id_n.count || 'Rows updated in ar_trx_bal_summary with reference_1 = Null');
292: END IF;
293: END LOOP;
294: EXCEPTION

Line 291: LogMessage(FND_LOG.LEVEL_UNEXPECTED,l_cust_account_id_n.count || 'Rows updated in ar_trx_bal_summary with reference_1 = Null');

287: and reference_1='1';
288: l_cust_account_id_n.delete;
289: commit;
290: FND_FILE.PUT_LINE(FND_FILE.LOG,' Rows updated in ar_trx_bal_summary with reference_1 = Null->'||l_cust_account_id_n.count);
291: LogMessage(FND_LOG.LEVEL_UNEXPECTED,l_cust_account_id_n.count || 'Rows updated in ar_trx_bal_summary with reference_1 = Null');
292: END IF;
293: END LOOP;
294: EXCEPTION
295: WHEN deadlock_detected THEN

Line 296: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deadlock detected when updating ar_trx_bal_summary.reference to null' || sqlerrm);

292: END IF;
293: END LOOP;
294: EXCEPTION
295: WHEN deadlock_detected THEN
296: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deadlock detected when updating ar_trx_bal_summary.reference to null' || sqlerrm);
297: LogMessage(FND_LOG.LEVEL_STATEMENT,' Deadlock detected when updating ar_trx_bal_summary.reference to null.');
298: ROLLBACK;
299: l_cust_account_id_n.delete;
300: if c_cust_account_id_n%ISOPEN then

Line 297: LogMessage(FND_LOG.LEVEL_STATEMENT,' Deadlock detected when updating ar_trx_bal_summary.reference to null.');

293: END LOOP;
294: EXCEPTION
295: WHEN deadlock_detected THEN
296: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deadlock detected when updating ar_trx_bal_summary.reference to null' || sqlerrm);
297: LogMessage(FND_LOG.LEVEL_STATEMENT,' Deadlock detected when updating ar_trx_bal_summary.reference to null.');
298: ROLLBACK;
299: l_cust_account_id_n.delete;
300: if c_cust_account_id_n%ISOPEN then
301: close c_cust_account_id_n;

Line 308: IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = null in Deadlock handler');

304: LOOP
305: FETCH c_cust_account_id_n BULK COLLECT INTO
306: l_cust_account_id_n LIMIT G_BATCH_SIZE;
307: IF l_cust_account_id_n.count = 0 THEN
308: IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = null in Deadlock handler');
309: CLOSE c_cust_account_id_n;
310: EXIT;
311: ELSE
312: FOR i IN l_cust_account_id_n.first..l_cust_account_id_n.last

Line 318: UPDATE AR_TRX_BAL_SUMMARY

314: BEGIN
315: OPEN C_CUST_ACCOUNT_ID_DLN(l_cust_account_id_n(i));
316: FETCH C_CUST_ACCOUNT_ID_DLN into l_cust_account_id1;
317: EXIT WHEN C_CUST_ACCOUNT_ID_DLN%NOTFOUND;
318: UPDATE AR_TRX_BAL_SUMMARY
319: SET REFERENCE_1 = null
320: WHERE cust_account_id=l_cust_account_id1
321: and REFERENCE_1 = '1';
322: FND_FILE.PUT_LINE(FND_FILE.LOG,'updated records '||sql%rowcount);

Line 340: IEX_DEBUG_PUB.LOGMESSAGE(l_cust_account_id_1.count || ' rows updated in ar_trx_bal_summary with reference_1 = 1');

336: END;
337:
338: END LOOP;
339:
340: IEX_DEBUG_PUB.LOGMESSAGE(l_cust_account_id_1.count || ' rows updated in ar_trx_bal_summary with reference_1 = 1');
341: l_cust_account_id_1.delete;
342: commit;
343:
344: END IF;

Line 347: FND_FILE.PUT_LINE(FND_FILE.LOG,'Locked by another session when updating ar_trx_bal_summary.reference to 1');

343:
344: END IF;
345: END LOOP;
346: WHEN locked_by_another_session THEN
347: FND_FILE.PUT_LINE(FND_FILE.LOG,'Locked by another session when updating ar_trx_bal_summary.reference to 1');
348: if c_cust_account_id_1%ISOPEN then
349: close c_cust_account_id_1;
350: end if;
351: ROLLBACK;

Line 353: LogMessage(FND_LOG.LEVEL_UNEXPECTED,SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = Null');

349: close c_cust_account_id_1;
350: end if;
351: ROLLBACK;
352: WHEN OTHERS THEN
353: LogMessage(FND_LOG.LEVEL_UNEXPECTED,SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = Null');
354: END;
355:
356: --End bug 6876187 gnramasa 14th mar 08
357: --End bug#7133605 schekuri 09-Jun-2008

Line 359: /*update ar_trx_bal_summary set reference_1 = '1'

355:
356: --End bug 6876187 gnramasa 14th mar 08
357: --End bug#7133605 schekuri 09-Jun-2008
358:
359: /*update ar_trx_bal_summary set reference_1 = '1'
360: where cust_account_id in
361: ( select distinct cust_account_id
362: from iex_delinquencies_all
363: where status in ('DELINQUENT','PREDELINQUENT'));*/

Line 364: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Done updating Reference_1 of AR_TRX_BAL_SUMMARY ');

360: where cust_account_id in
361: ( select distinct cust_account_id
362: from iex_delinquencies_all
363: where status in ('DELINQUENT','PREDELINQUENT'));*/
364: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Done updating Reference_1 of AR_TRX_BAL_SUMMARY ');
365:
366: -- end if;
367: /* End Kasreeni 3/1/2007 Bug 5905023 We will update everytime instead of once */
368: end update_trx_bal_summ_concur;

Line 500: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza

496: if (g_party_lvl_enb='N' and l_allowed_lvl=g_system_level) or g_party_lvl_enb='Y' then
497: IF l_allowed_lvl = 'CUSTOMER' THEN
498: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at Customer level--in OU');
499: delete from IEX_DLN_UWQ_SUMMARY where (party_id,org_id) in
500: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza
501: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date)
502: and trb.org_id=nvl(l_curr_org_id,trb.org_id))
503: and cust_account_id is null and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
504: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at Cust level-->'||sql%rowcount);

Line 508: (select cust_account_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)

504: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at Cust level-->'||sql%rowcount);
505: ELSIF l_allowed_lvl = 'ACCOUNT' THEN
506: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at Account level--in OU');
507: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in
508: (select cust_account_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)
509: and trb.org_id=nvl(l_curr_org_id,trb.org_id))
510: and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
511: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at A/c level-->'||sql%rowcount);
512: ELSIF l_allowed_lvl = 'BILL_TO' THEN

Line 515: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)

511: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at A/c level-->'||sql%rowcount);
512: ELSIF l_allowed_lvl = 'BILL_TO' THEN
513: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at BillTo level--in OU');
514: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
515: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)
516: and trb.org_id=nvl(l_curr_org_id,trb.org_id))
517: and site_use_id is not null;--Added for Bug 8707923 27-Jul-2009 barathsr
518: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at Billto level-->'||sql%rowcount);
519: END IF;

Line 555: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza

551: FND_FILE.PUT_LINE(FND_FILE.LOG,'looping for other levels in party-->'||r_allowed_LEVELS.lookup_code);
552: IF l_allowed_lvl = 'CUSTOMER' THEN
553: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at Customer level--in party ');
554: delete from IEX_DLN_UWQ_SUMMARY where (party_id,org_id) in
555: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza
556: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date))
557: -- and trb.org_id=nvl(l_curr_org_id,trb.org_id)
558: and cust_account_id is null and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
559: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at Cust level-->'||sql%rowcount);

Line 563: (select cust_account_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date))

559: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at Cust level-->'||sql%rowcount);
560: ELSIF l_allowed_lvl = 'ACCOUNT' THEN
561: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at Account level--in party ');
562: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in
563: (select cust_account_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date))
564: -- and trb.org_id=nvl(l_curr_org_id,trb.org_id)
565: and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
566: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at A/C level-->'||sql%rowcount);
567: ELSIF l_allowed_lvl = 'BILL_TO' THEN

Line 570: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date))

566: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at A/C level-->'||sql%rowcount);
567: ELSIF l_allowed_lvl = 'BILL_TO' THEN
568: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at BillTo level--in party ');
569: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
570: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date))
571: -- and trb.org_id=nvl(l_curr_org_id,trb.org_id))
572: and site_use_id is not null;--Added for Bug 8707923 27-Jul-2009 barathsr
573: FND_FILE.PUT_LINE(FND_FILE.LOG,'No. of records deleted at Billto level-->'||sql%rowcount);
574: END IF;

Line 604: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza

600: if G_SYSTEM_LEVEL is not null then
601: IF g_system_level = 'CUSTOMER' THEN
602: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at Customer level--in sys lvl ');
603: delete from IEX_DLN_UWQ_SUMMARY where (party_id,org_id) in
604: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza
605: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date))
606: -- and trb.org_id=nvl(l_curr_org_id,trb.org_id)
607: and cust_account_id is null and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
608: ELSIF g_system_level = 'ACCOUNT' THEN

Line 611: (select cust_account_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date))

607: and cust_account_id is null and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
608: ELSIF g_system_level = 'ACCOUNT' THEN
609: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at Account level--in sys lvl ');
610: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in
611: (select cust_account_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date))
612: -- and trb.org_id=nvl(l_curr_org_id,trb.org_id)
613: and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
614: ELSIF g_system_level = 'BILL_TO' THEN
615: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at BillTo level--in sys lvl');

Line 617: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date))

613: and site_use_id is null;--Added for Bug 8707923 27-Jul-2009 barathsr
614: ELSIF g_system_level = 'BILL_TO' THEN
615: FND_FILE.PUT_LINE(FND_FILE.LOG,'Deleting records at BillTo level--in sys lvl');
616: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
617: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date))
618: -- and trb.org_id=nvl(l_curr_org_id,trb.org_id))
619: and site_use_id is not null;--Added for Bug 8707923 27-Jul-2009 barathsr
620: END IF;
621: end if;

Line 1144: FROM ar_trx_bal_summary trx_summ,

1140: AND(TRUNC(pro.uwq_active_date) > TRUNC(sysdate))))
1141: )
1142: ) pending_promises
1143:
1144: FROM ar_trx_bal_summary trx_summ,
1145: hz_cust_accounts acc,
1146: hz_parties party,
1147: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
1148: jtf_objects_b objb,

Line 1228: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary

1224: phone.phone_extension;*/ --9597052
1225: --End of comment for Bug 9597052 28-Apr-2010 barathsr
1226:
1227: --Begin Bug 9597052 28-Apr-2010 barathsr
1228: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary
1229: --All the other column values are fetched with small cursors from the respective tables and updated in iex_dln_uwq_summary
1230: CURSOR c_iex_billto_uwq_summary(c_level varchar2,c_org_id number)
1231: IS
1232: SELECT

Line 1291: FROM ar_trx_bal_summary trx_summ,

1287: MAX(trx_summ.last_updated_by) last_updated_by,
1288: MAX(trx_summ.creation_date) creation_date,
1289: MAX(trx_summ.created_by) created_by,
1290: MAX(trx_summ.last_update_login) last_update_login
1291: FROM ar_trx_bal_summary trx_summ,
1292: GL_SETS_OF_BOOKS gl,
1293: AR_SYSTEM_PARAMETERS_all sys,
1294: jtf_objects_b objb,
1295: hz_cust_accounts acc,

Line 1497: from ar_trx_bal_summary summ,

1493: select summ.site_use_id,
1494: summ.last_payment_amount last_payment_amount,
1495: summ.currency last_payment_currency,
1496: summ.last_payment_number last_payment_number
1497: from ar_trx_bal_summary summ,
1498: gl_sets_of_books gl,
1499: ar_system_parameters_all sys
1500: where summ.reference_1='1'
1501: and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID

Line 1762: FROM ar_trx_bal_summary trx_summ,

1758: AND(pro.uwq_status = 'PENDING'
1759: AND(TRUNC(pro.uwq_active_date) > TRUNC(sysdate))))
1760: )
1761: ) pending_promises
1762: FROM ar_trx_bal_summary trx_summ,
1763: hz_cust_accounts acc,
1764: hz_parties party,
1765: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
1766: jtf_objects_b objb,

Line 1807: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date))

1803: and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
1804: and trx_summ.org_id = sys.org_id*/ --9597052
1805: -- start bug 5762888 gnramasa 13-July-2007
1806: /* and (trx_summ.cust_account_id, trx_summ.site_use_id, trx_summ.org_id) in
1807: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date))
1808: */
1809: /* and trunc(trx_summ.last_update_date) >= trunc(p_from_date)--9597052
1810: -- end bug 5762888 gnramasa 13-July-2007
1811: --Begin Bug 8707923 27-Jul-2009 barathsr

Line 1851: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary for the date specified

1847: phone.phone_extension;*/ --9597052
1848: -- End - Andre Araujo - 10/20/06 - Added selection using date
1849: --End of comment for Bug 9597052 28-Apr-2010 barathsr
1850: --Begin Bug 9597052 28-Apr-2010 barathsr
1851: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary for the date specified
1852: --All the other column values are fetched with small cursors from the respective tables and updated in iex_dln_uwq_summary
1853:
1854: CURSOR c_iex_billto_uwq_dt_sum(p_from_date date,c_level varchar2,c_org_id number)
1855: IS

Line 1915: FROM ar_trx_bal_summary trx_summ,

1911: MAX(trx_summ.last_updated_by) last_updated_by,
1912: MAX(trx_summ.creation_date) creation_date,
1913: MAX(trx_summ.created_by) created_by,
1914: MAX(trx_summ.last_update_login) last_update_login
1915: FROM ar_trx_bal_summary trx_summ,
1916: GL_SETS_OF_BOOKS gl,
1917: AR_SYSTEM_PARAMETERS_all sys,
1918: jtf_objects_b objb,
1919: hz_cust_accounts acc,

Line 2155: from ar_trx_bal_summary summ,

2151: select summ.site_use_id,
2152: summ.last_payment_amount last_payment_amount,
2153: summ.currency last_payment_currency,
2154: summ.last_payment_number last_payment_number
2155: from ar_trx_bal_summary summ,
2156: gl_sets_of_books gl,
2157: ar_system_parameters_all sys
2158: where summ.reference_1='1'
2159: and gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID

Line 2416: FROM ar_trx_bal_summary trx_summ,

2412: AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
2413: )
2414: ) pending_promises
2415:
2416: FROM ar_trx_bal_summary trx_summ,
2417: hz_cust_accounts acc,
2418: hz_parties party,
2419: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
2420: jtf_objects_b objb,

Line 2479: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary at Account level

2475: phone.phone_extension;*/
2476: --End of comment for Bug 9597052 28-Apr-2010 barathsr
2477:
2478: --Begin Bug 9597052 28-Apr-2010 barathsr
2479: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary at Account level
2480: --All the other column values are fetched with small cursors from the respective tables and updated in iex_dln_uwq_summary
2481:
2482: CURSOR c_iex_acc_uwq_summary(c_level varchar2,c_org_id number) --Added for Bug 8707923 27-Jul-2009 barathsr
2483: IS

Line 2543: FROM ar_trx_bal_summary trx_summ,

2539: MAX(trx_summ.last_updated_by) last_updated_by,
2540: MAX(trx_summ.creation_date) creation_date,
2541: MAX(trx_summ.created_by) created_by,
2542: MAX(trx_summ.last_update_login) last_update_login
2543: FROM ar_trx_bal_summary trx_summ,
2544: hz_cust_accounts acc,
2545: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
2546: jtf_objects_b objb,
2547: GL_SETS_OF_BOOKS gl,

Line 2741: from ar_trx_bal_summary summ,

2737: select summ.cust_account_id,summ.org_id,
2738: summ.last_payment_amount last_payment_amount,
2739: summ.currency last_payment_currency,
2740: summ.last_payment_number last_payment_number
2741: from ar_trx_bal_summary summ,
2742: gl_sets_of_books gl,
2743: ar_system_parameters_all sys
2744: where gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
2745: and summ.org_id = sys.org_id

Line 2998: FROM ar_trx_bal_summary trx_summ,

2994: AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
2995: )
2996: ) pending_promises
2997:
2998: FROM ar_trx_bal_summary trx_summ,
2999: hz_cust_accounts acc,
3000: hz_parties party,
3001: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
3002: jtf_objects_b objb,

Line 3034: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(sysdate)) */

3030: and trx_summ.org_id = sys.org_id
3031: -- start bug 5762888 gnramasa 13-July-2007
3032: /* and (trx_summ.cust_account_id, trx_summ.site_use_id, trx_summ.org_id) in */
3033: /* changed for bug 5677415 by gnramasa on 27/11/2006 */
3034: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(sysdate)) */
3035: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date)) */
3036: -- and trunc(trx_summ.last_update_date) >= trunc(p_from_date)
3037: -- end bug 5762888 gnramasa 13-July-2007
3038: --Begin Bug 8707923 27-Jul-2009 barathsr

Line 3035: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date)) */

3031: -- start bug 5762888 gnramasa 13-July-2007
3032: /* and (trx_summ.cust_account_id, trx_summ.site_use_id, trx_summ.org_id) in */
3033: /* changed for bug 5677415 by gnramasa on 27/11/2006 */
3034: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(sysdate)) */
3035: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date)) */
3036: -- and trunc(trx_summ.last_update_date) >= trunc(p_from_date)
3037: -- end bug 5762888 gnramasa 13-July-2007
3038: --Begin Bug 8707923 27-Jul-2009 barathsr
3039: /* and party.party_id=party_pref.party_id(+)

Line 3070: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary at Account level for a specified date range

3066: -- End - Andre Araujo - 10/20/06 - Added selection using date
3067: --End of comment for Bug 9597052 28-Apr-2010 barathsr
3068:
3069: --Begin Bug 9597052 28-Apr-2010 barathsr
3070: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary at Account level for a specified date range
3071: --All the other column values are fetched with small cursors from the respective tables and updated in iex_dln_uwq_summary
3072:
3073:
3074: CURSOR c_iex_acc_uwq_dt_sum(p_from_date date,c_level varchar2,c_org_id number)

Line 3135: FROM ar_trx_bal_summary trx_summ,

3131: MAX(trx_summ.last_updated_by) last_updated_by,
3132: MAX(trx_summ.creation_date) creation_date,
3133: MAX(trx_summ.created_by) created_by,
3134: MAX(trx_summ.last_update_login) last_update_login
3135: FROM ar_trx_bal_summary trx_summ,
3136: hz_cust_accounts acc,
3137: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
3138: jtf_objects_b objb,
3139: GL_SETS_OF_BOOKS gl,

Line 3366: from ar_trx_bal_summary summ,

3362: select summ.cust_account_id,summ.org_id,
3363: summ.last_payment_amount last_payment_amount,
3364: summ.currency last_payment_currency,
3365: summ.last_payment_number last_payment_number
3366: from ar_trx_bal_summary summ,
3367: gl_sets_of_books gl,
3368: ar_system_parameters_all sys
3369: where gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
3370: and summ.org_id = sys.org_id

Line 3611: FROM ar_trx_bal_summary trx_summ,

3607: AND(TRUNC(pd.uwq_active_date) > TRUNC(sysdate))))
3608: )
3609: pending_promises
3610:
3611: FROM ar_trx_bal_summary trx_summ,
3612: hz_cust_accounts acc,
3613: hz_parties party,
3614: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
3615: jtf_objects_b objb,

Line 3675: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary at Party level

3671: --End of comment for Bug 9597052 28-Apr-2010 barathsr
3672:
3673:
3674: --Begin Bug 9597052 28-Apr-2010 barathsr
3675: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary at Party level
3676: --All the other column values are fetched with small cursors from the respective tables and updated in iex_dln_uwq_summary
3677:
3678:
3679: CURSOR c_iex_cu_uwq_summary(c_level varchar2,c_org_id number)--Added for Bug 8707923 27-Jul-2009 barathsr

Line 3741: FROM ar_trx_bal_summary trx_summ,

3737: MAX(trx_summ.last_updated_by) last_updated_by,
3738: MAX(trx_summ.creation_date) creation_date,
3739: MAX(trx_summ.created_by) created_by,
3740: MAX(trx_summ.last_update_login) last_update_login
3741: FROM ar_trx_bal_summary trx_summ,
3742: hz_cust_accounts acc,
3743: hz_parties party,
3744: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
3745: jtf_objects_b objb,

Line 3943: from ar_trx_bal_summary summ,

3939: select hca.party_id,summ.org_id,
3940: summ.last_payment_amount last_payment_amount,
3941: summ.currency last_payment_currency,
3942: summ.last_payment_number last_payment_number
3943: from ar_trx_bal_summary summ,
3944: hz_cust_accounts hca,
3945: gl_sets_of_books gl,
3946: ar_system_parameters_all sys
3947: where summ.cust_account_id=hca.cust_account_id

Line 4189: FROM ar_trx_bal_summary trx_summ,

4185: AND(TRUNC(pd.uwq_active_date) > TRUNC(sysdate))))
4186: )
4187: pending_promises
4188:
4189: FROM ar_trx_bal_summary trx_summ,
4190: hz_cust_accounts acc,
4191: hz_parties party,
4192: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
4193: jtf_objects_b objb,

Line 4225: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(sysdate)) */

4221: and trx_summ.org_id = sys.org_id
4222: -- start bug 5762888 gnramasa 13-July-2007
4223: /* and (trx_summ.cust_account_id, trx_summ.site_use_id, trx_summ.org_id) in */
4224: /* changed for bug 5677415 by gnramasa on 27/11/2006 */
4225: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(sysdate)) */
4226: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date)) */
4227: -- and trunc(trx_summ.last_update_date) >= trunc(p_from_date)
4228: -- end bug 5762888 gnramasa 13-July-2007
4229: --Begin Bug 8707923 27-Jul-2009 barathsr

Line 4226: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date)) */

4222: -- start bug 5762888 gnramasa 13-July-2007
4223: /* and (trx_summ.cust_account_id, trx_summ.site_use_id, trx_summ.org_id) in */
4224: /* changed for bug 5677415 by gnramasa on 27/11/2006 */
4225: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(sysdate)) */
4226: /* (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(p_from_date)) */
4227: -- and trunc(trx_summ.last_update_date) >= trunc(p_from_date)
4228: -- end bug 5762888 gnramasa 13-July-2007
4229: --Begin Bug 8707923 27-Jul-2009 barathsr
4230: /* and party.party_id=party_pref.party_id(+)

Line 4257: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary at Party level for a specified date range

4253: --End bug 6634879 gnramasa 20th Nov 07
4254: --End of comment for Bug 9597052 28-Apr-2010 barathsr
4255:
4256: --Begin Bug 9597052 28-Apr-2010 barathsr
4257: --This cursor fetches column values from ar_trx_bal_summary table and the values are inserted in iex_dln_uwq_summary at Party level for a specified date range
4258: --All the other column values are fetched with small cursors from the respective tables and updated in iex_dln_uwq_summary
4259:
4260:
4261: CURSOR c_iex_cu_uwq_dt_sum(p_from_date date,c_level varchar2,c_org_id number)

Line 4323: FROM ar_trx_bal_summary trx_summ,

4319: MAX(trx_summ.last_updated_by) last_updated_by,
4320: MAX(trx_summ.creation_date) creation_date,
4321: MAX(trx_summ.created_by) created_by,
4322: MAX(trx_summ.last_update_login) last_update_login
4323: FROM ar_trx_bal_summary trx_summ,
4324: hz_cust_accounts acc,
4325: hz_parties party,
4326: hz_party_preferences party_pref,--Added for Bug 8707923 27-Jul-2009 barathsr
4327: jtf_objects_b objb,

Line 4565: from ar_trx_bal_summary summ,

4561: select hca.party_id,summ.org_id,
4562: summ.last_payment_amount last_payment_amount,
4563: summ.currency last_payment_currency,
4564: summ.last_payment_number last_payment_number
4565: from ar_trx_bal_summary summ,
4566: hz_cust_accounts hca,
4567: gl_sets_of_books gl,
4568: ar_system_parameters_all sys
4569: where summ.cust_account_id=hca.cust_account_id

Line 5011: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza

5007: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Partially repopulating IEX_DLN_UWQ_SUMMARY table...');
5008: /* Begin gnramasa Modified for bug 5677415 27/11/2006 */
5009: /* IF p_level = 'CUSTOMER' THEN
5010: delete from IEX_DLN_UWQ_SUMMARY where (party_id,org_id) in
5011: (select hza.party_id,trb.org_id from ar_trx_bal_summary trb, hz_cust_accounts hza
5012: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date)
5013: and trb.org_id=nvl(p_org_id,trb.org_id));--Added for Bug 8707923 27-Jul-2009 barathsr
5014: ELSIF p_level = 'ACCOUNT' THEN
5015: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in

Line 5016: (select cust_account_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)

5012: where hza.cust_account_id = trb.cust_account_id and trunc(trb.LAST_UPDATE_DATE) >= trunc(l_from_date)
5013: and trb.org_id=nvl(p_org_id,trb.org_id));--Added for Bug 8707923 27-Jul-2009 barathsr
5014: ELSIF p_level = 'ACCOUNT' THEN
5015: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, org_id) in
5016: (select cust_account_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)
5017: and trb.org_id=nvl(p_org_id,trb.org_id));--Added for Bug 8707923 27-Jul-2009 barathsr
5018: ELSIF p_level = 'BILL_TO' THEN
5019: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
5020: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)

Line 5020: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)

5016: (select cust_account_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)
5017: and trb.org_id=nvl(p_org_id,trb.org_id));--Added for Bug 8707923 27-Jul-2009 barathsr
5018: ELSIF p_level = 'BILL_TO' THEN
5019: delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
5020: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)
5021: and trb.org_id=nvl(p_org_id,trb.org_id));--Added for Bug 8707923 27-Jul-2009 barathsr
5022: END IF;
5023: /* delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
5024: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)); */

Line 5024: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)); */

5020: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary trb where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)
5021: and trb.org_id=nvl(p_org_id,trb.org_id));--Added for Bug 8707923 27-Jul-2009 barathsr
5022: END IF;
5023: /* delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
5024: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)); */
5025:
5026: -- LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No of rows deleted: ' || SQL%ROWCOUNT);
5027: /* End gnramasa Modified for bug 5677415 27/11/2006 */
5028: -- LogMessage(FND_LOG.LEVEL_STATEMENT,'Deleted rows that will be repopulated');

Line 9622: FROM ar_trx_bal_summary trx_summ,

9618: fnd_terr.territory_short_name country,
9619: loc.province province,
9620: loc.postal_code postal_code
9621: -- end for the bug#8538945
9622: FROM ar_trx_bal_summary trx_summ,
9623: hz_cust_accounts acc,
9624: hz_parties party,
9625: jtf_objects_b objb,
9626: fnd_territories_tl fnd_terr,

Line 9942: FROM ar_trx_bal_summary o_summ

9938: SELECT o_summ.site_use_id,
9939: o_summ.last_payment_number last_payment_number,
9940: o_summ.last_payment_amount -- Bug 14226230 bibeura
9941: -- iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
9942: FROM ar_trx_bal_summary o_summ
9943: WHERE o_summ.site_use_id in (select object_id from iex_pop_uwq_summ_gt)
9944: AND o_summ.last_payment_date = (SELECT MAX(last_payment_date)
9945: FROM ar_trx_bal_summary
9946: WHERE site_use_id = o_summ.site_use_id);

Line 9945: FROM ar_trx_bal_summary

9941: -- iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
9942: FROM ar_trx_bal_summary o_summ
9943: WHERE o_summ.site_use_id in (select object_id from iex_pop_uwq_summ_gt)
9944: AND o_summ.last_payment_date = (SELECT MAX(last_payment_date)
9945: FROM ar_trx_bal_summary
9946: WHERE site_use_id = o_summ.site_use_id);
9947:
9948: cursor c_bankruptcies is
9949: select sua.site_use_id,

Line 10970: FROM ar_trx_bal_summary trx_summ,

10966: party.county county,
10967: fnd_terr.territory_short_name country,
10968: party.province province,
10969: party.postal_code postal_code
10970: FROM ar_trx_bal_summary trx_summ,
10971: hz_cust_accounts acc,
10972: hz_parties party,
10973: jtf_objects_b objb,
10974: fnd_territories_tl fnd_terr,

Line 11268: FROM ar_trx_bal_summary o_summ

11264: SELECT o_summ.cust_account_id,
11265: o_summ.last_payment_number last_payment_number,
11266: o_summ.last_payment_amount -- Bug 14226230 bibeura
11267: --iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
11268: FROM ar_trx_bal_summary o_summ
11269: WHERE o_summ.cust_account_id in (select object_id from iex_pop_uwq_summ_gt)
11270: AND o_summ.last_payment_date = (SELECT MAX(last_payment_date)
11271: FROM ar_trx_bal_summary
11272: WHERE cust_account_id = o_summ.cust_account_id);

Line 11271: FROM ar_trx_bal_summary

11267: --iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
11268: FROM ar_trx_bal_summary o_summ
11269: WHERE o_summ.cust_account_id in (select object_id from iex_pop_uwq_summ_gt)
11270: AND o_summ.last_payment_date = (SELECT MAX(last_payment_date)
11271: FROM ar_trx_bal_summary
11272: WHERE cust_account_id = o_summ.cust_account_id);
11273:
11274: cursor c_bankruptcies is
11275: select ca.cust_account_id,

Line 12287: FROM ar_trx_bal_summary trx_summ,

12283: party.county county,
12284: fnd_terr.territory_short_name country,
12285: party.province province,
12286: party.postal_code postal_code
12287: FROM ar_trx_bal_summary trx_summ,
12288: hz_cust_accounts acc,
12289: hz_parties party,
12290: jtf_objects_b objb,
12291: fnd_territories_tl fnd_terr,

Line 12577: FROM ar_trx_bal_summary o_summ,

12573: SELECT o_acc.party_id,
12574: o_summ.last_payment_number last_payment_number,
12575: o_summ.last_payment_amount -- Bug 14226230 bibeura
12576: -- iex_uwq_view_pkg.convert_amount(o_summ.last_payment_amount,o_summ.currency) last_payment_amount
12577: FROM ar_trx_bal_summary o_summ,
12578: hz_cust_accounts o_acc
12579: WHERE o_summ.cust_account_id = o_acc.cust_account_id
12580: and o_acc.party_id in (select object_id from iex_pop_uwq_summ_gt)
12581: AND o_summ.last_payment_date = (SELECT MAX(summ.last_payment_date)

Line 12582: FROM ar_trx_bal_summary summ,

12578: hz_cust_accounts o_acc
12579: WHERE o_summ.cust_account_id = o_acc.cust_account_id
12580: and o_acc.party_id in (select object_id from iex_pop_uwq_summ_gt)
12581: AND o_summ.last_payment_date = (SELECT MAX(summ.last_payment_date)
12582: FROM ar_trx_bal_summary summ,
12583: hz_cust_accounts acc
12584: WHERE acc.cust_account_id = summ.cust_account_id
12585: and acc.party_id=o_acc.party_id);
12586: