[Home] [Help]
52: * initialize PL/SQL table x_segment.
53: * 26-JUL-2007 Himadri Chakroborty Bug #6133153
54: *changed the size of the variables l_fiscal_year and l_period_type to 15
55: * 09-Oct-2008 Pramod B.H. Bug 7458002
56: * a)Modified procedure process_alloc_dtl() to merge records into gl_aloc_dtl
57: * instead of deleting and inserting rows in gl_aloc_dtl.
58: * b)Modified procedure delete_allocations() to delete only obsoleted rows
59: * in gl_aloc_dtl.
60: * 31-Aug-2009 Parag Kanetkar Bug 9931797 Delete allocations for only those codes
53: * 26-JUL-2007 Himadri Chakroborty Bug #6133153
54: *changed the size of the variables l_fiscal_year and l_period_type to 15
55: * 09-Oct-2008 Pramod B.H. Bug 7458002
56: * a)Modified procedure process_alloc_dtl() to merge records into gl_aloc_dtl
57: * instead of deleting and inserting rows in gl_aloc_dtl.
58: * b)Modified procedure delete_allocations() to delete only obsoleted rows
59: * in gl_aloc_dtl.
60: * 31-Aug-2009 Parag Kanetkar Bug 9931797 Delete allocations for only those codes
61: * for which process is being run.
55: * 09-Oct-2008 Pramod B.H. Bug 7458002
56: * a)Modified procedure process_alloc_dtl() to merge records into gl_aloc_dtl
57: * instead of deleting and inserting rows in gl_aloc_dtl.
58: * b)Modified procedure delete_allocations() to delete only obsoleted rows
59: * in gl_aloc_dtl.
60: * 31-Aug-2009 Parag Kanetkar Bug 9931797 Delete allocations for only those codes
61: * for which process is being run.
62: * 14-Jul-2011 Pramod B.H. Bug#12600219
63: * (i)Modified procedure put_alloc_expenses(). Now passing new out parameter
306: AND event_class_code = 'GLCOSTALOC'
307: AND accounted_flag IS NOT NULL
308: AND transaction_id IN
309: (SELECT allocdtl_id
310: FROM gl_aloc_dtl
311: WHERE period_id = g_period_id
312: AND cost_type_id = g_cost_type_id
313: AND nvl(gl_posted_ind, 0) <> 1
314: AND (alloc_id,line_no) IN
331: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module||'.begin','Deleting Allocations');
332: END IF;
333: /* Bug 7458002 - Commented
334: DELETE FROM
335: gl_aloc_dtl
336: WHERE
337: period_id = g_period_id AND
338: cost_type_id = g_cost_type_id
339: AND alloc_id IN (
343: AND alloc_code between nvl(v_from_alloc_code,alloc_code)
344: AND nvl(v_to_alloc_code,alloc_code)
345: ); */
346:
347: /* Bug 7458002 - To delete only obsoleted rows in gl_aloc_dtl */
348: /* Bug 9931797 Customer may be running for only a subset of allocation code
349: Prior query then deletes rest of the data */
350: /* DELETE FROM
351: gl_aloc_dtl
347: /* Bug 7458002 - To delete only obsoleted rows in gl_aloc_dtl */
348: /* Bug 9931797 Customer may be running for only a subset of allocation code
349: Prior query then deletes rest of the data */
350: /* DELETE FROM
351: gl_aloc_dtl
352: WHERE period_id = g_period_id
353: AND cost_type_id = g_cost_type_id
354: AND (alloc_id,line_no) NOT IN (
355: SELECT b.alloc_id, b.line_no
429: END IF;
430: -- End Bug 12561766
431:
432: DELETE FROM
433: gl_aloc_dtl
434: WHERE period_id = g_period_id
435: AND cost_type_id = g_cost_type_id
436: AND nvl(gl_posted_ind, 0) <> 1 -- Bug 12561766
437: AND (alloc_id,line_no) IN (
445: );
446:
447:
448:
449: alloc_log_msg( C_LOG_FILE, TO_CHAR(SQL%ROWCOUNT) || ' Rows deleted from Allocations table gl_aloc_dtl' );
450:
451: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
452: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,c_module||l_local_module||'.end','Deleting Allocations');
453: END IF;
453: END IF;
454:
455: EXCEPTION
456: WHEN NO_DATA_FOUND THEN
457: alloc_log_msg(C_LOG_FILE, '0 Rows deleted from gl_aloc_dtl ');
458: v_status := 0;
459: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
460: THEN
461: FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,c_module||l_local_module,'0 Rows deleted from gl_aloc_dtl');
457: alloc_log_msg(C_LOG_FILE, '0 Rows deleted from gl_aloc_dtl ');
458: v_status := 0;
459: IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
460: THEN
461: FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,c_module||l_local_module,'0 Rows deleted from gl_aloc_dtl');
462: END IF;
463:
464: WHEN OTHERS THEN
465: alloc_log_msg(C_LOG_FILE, to_char(SQLCODE) || ' ' || SQLERRM);
1636: alloc_log_msg(C_LOG_FILE, ' Line: '||to_char (cur_alocinp_tmp.line_no) || ', % = '||to_char(round(x_alloc_percent,4)) ||
1637: ', Allocated Expense = ' ||to_char(round(x_allocated_amount,4)));
1638:
1639: /* Bug 7458002 - Commented
1640: INSERT INTO gl_aloc_dtl
1641: (
1642: PERIOD_ID,
1643: COST_TYPE_ID,
1644: ALLOC_ID,
1683: P_program_id,
1684: SYSDATE
1685: ); */
1686: /* Bug 7458002 - replaced Insert with Merge */
1687: MERGE INTO gl_aloc_dtl gdtl
1688: USING ( SELECT g_period_id period_id,
1689: g_cost_type_id cost_type_id,
1690: cur_alocinp_tmp.alloc_id alloc_id,
1691: cur_alocinp_tmp.line_no line_no
1761: ;
1762: END IF;
1763: END LOOP;
1764:
1765: /* Bug 7458002 - To delete obsoleted rows in gl_aloc_dtl (Start) */
1766: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1767: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,g_calling_module||l_local_module||'.delete_allocations','deleting Allocations ...');
1768: END IF;
1769: delete_allocations(v_from_alloc_code, v_to_alloc_code, x_status);