[Home] [Help]
264: PA_EXPENDITURE_ITEMS_ALL ei,
265: PA_IMPLEMENTATIONS_ALL imp,
266: (select ledger_id,min(min_value) min_value, max(max_value) max_value from
267: PA_XLA_UPG_CTRL
268: where reference = 'GL_PERIOD_STATUSES'
269: AND status = 'P'
270: AND batch_id = p_upg_batch_id
271: group by ledger_id) ctrl,
272: HR_ORGANIZATION_INFORMATION hoi
278: AND (cdl.line_type in ('C','D') OR ei.system_linkage_function in ('BTC','INV','ST','PJ','USG','WIP','OT'))
279: AND cdl.org_id = imp.org_id
280: AND cdl.gl_date between to_date(ctrl.min_value,'J') and to_date (ctrl.max_value,'J')
281: AND ctrl.ledger_id = imp.set_of_books_id
282: --AND ctrl.reference = 'GL_PERIOD_STATUSES'
283: --AND ctrl.status = 'P'
284: --AND ctrl.batch_id = p_upg_batch_id
285: AND hoi.organization_id = imp.org_id
286: AND hoi.org_information_context = 'Operating Unit Information'
340: PA_EXPENDITURE_ITEMS_ALL ei,
341: PA_IMPLEMENTATIONS_ALL imp,
342: (select ledger_id,min(min_value) min_value, max(max_value) max_value from
343: PA_XLA_UPG_CTRL
344: where reference = 'GL_PERIOD_STATUSES'
345: AND status = 'P'
346: AND batch_id = p_upg_batch_id
347: group by ledger_id) ctrl,
348: HR_ORGANIZATION_INFORMATION hoi
353: AND cdl.expenditure_item_id = ei.expenditure_item_id
354: AND cdl.org_id = imp.org_id
355: AND cdl.gl_date between to_date(ctrl.min_value,'J') and to_date (ctrl.max_value,'J')
356: AND ctrl.ledger_id = imp.set_of_books_id
357: --AND ctrl.reference = 'GL_PERIOD_STATUSES'
358: --AND ctrl.status = 'P'
359: --AND ctrl.batch_id = p_upg_batch_id
360: AND hoi.organization_id = imp.org_id
361: AND hoi.org_information_context = 'Operating Unit Information'
1545: IS
1546:
1547: CURSOR c_date_range is
1548: SELECT ledger_id, to_char(min(start_date),'J') min_date, to_char(max(end_date),'J') max_date
1549: FROM gl_period_statuses
1550: WHERE application_id = 275
1551: AND migration_status_code = 'P'
1552: GROUP BY ledger_id;
1553:
1568: BEGIN
1569:
1570: --------------------------------------------------------------------------------------------------
1571: -- For each ledger min and max gl dates are stored in the control table so as to avoid joining
1572: -- with gl_period_statuses table in the main upgrade
1573: ---------------------------------------------------------------------------------------------------
1574:
1575: INSERT INTO pa_xla_upg_ctrl (REFERENCE, MIN_VALUE, MAX_VALUE,
1576: LEDGER_ID, BATCH_ID, STATUS)
1573: ---------------------------------------------------------------------------------------------------
1574:
1575: INSERT INTO pa_xla_upg_ctrl (REFERENCE, MIN_VALUE, MAX_VALUE,
1576: LEDGER_ID, BATCH_ID, STATUS)
1577: SELECT 'GL_PERIOD_STATUSES', to_char(min(start_date),'J') , to_char(max(end_date),'J') , ledger_id, p_batch_id, 'P'
1578: FROM gl_period_statuses
1579: WHERE application_id = 275
1580: AND migration_status_code = 'P'
1581: AND exists (select 1 from pa_implementations_all where set_of_books_id
1574:
1575: INSERT INTO pa_xla_upg_ctrl (REFERENCE, MIN_VALUE, MAX_VALUE,
1576: LEDGER_ID, BATCH_ID, STATUS)
1577: SELECT 'GL_PERIOD_STATUSES', to_char(min(start_date),'J') , to_char(max(end_date),'J') , ledger_id, p_batch_id, 'P'
1578: FROM gl_period_statuses
1579: WHERE application_id = 275
1580: AND migration_status_code = 'P'
1581: AND exists (select 1 from pa_implementations_all where set_of_books_id
1582: = ledger_id and SAME_PA_GL_PERIOD = 'N')
1583: GROUP BY ledger_id;
1584:
1585: INSERT INTO pa_xla_upg_ctrl (REFERENCE, MIN_VALUE, MAX_VALUE,
1586: LEDGER_ID, BATCH_ID, STATUS)
1587: SELECT 'GL_PERIOD_STATUSES', to_char(min(start_date),'J') , to_char(max(end_date),'J') , ledger_id, p_batch_id, 'P'
1588: FROM gl_period_statuses
1589: WHERE application_id = 8721
1590: AND migration_status_code = 'P'
1591: AND exists (select 1 from pa_implementations_all where set_of_books_id
1584:
1585: INSERT INTO pa_xla_upg_ctrl (REFERENCE, MIN_VALUE, MAX_VALUE,
1586: LEDGER_ID, BATCH_ID, STATUS)
1587: SELECT 'GL_PERIOD_STATUSES', to_char(min(start_date),'J') , to_char(max(end_date),'J') , ledger_id, p_batch_id, 'P'
1588: FROM gl_period_statuses
1589: WHERE application_id = 8721
1590: AND migration_status_code = 'P'
1591: AND exists (select 1 from pa_implementations_all where set_of_books_id
1592: = ledger_id and SAME_PA_GL_PERIOD = 'Y')
1595: select min(to_date(min_value,'J') ) , max(to_date(max_value,'J') )
1596: into l_start_date, l_end_date
1597: from pa_xla_upg_ctrl
1598: where batch_id = p_batch_id
1599: and reference = 'GL_PERIOD_STATUSES';
1600:
1601:
1602:
1603:
1609: SELECT distinct per1.ledger_id,
1610: per1.ledger_id,
1611: per1.period_name,
1612: p_batch_id
1613: FROM gl_period_statuses per,
1614: gl_period_statuses per1
1615: WHERE per.application_id in(275,8721)
1616: AND per.migration_status_code ='P'
1617: AND per1.application_id =101
1610: per1.ledger_id,
1611: per1.period_name,
1612: p_batch_id
1613: FROM gl_period_statuses per,
1614: gl_period_statuses per1
1615: WHERE per.application_id in(275,8721)
1616: AND per.migration_status_code ='P'
1617: AND per1.application_id =101
1618: AND per1.ledger_id = per.ledger_id