[Home] [Help]
93: distinct org.organization_id,
94: sched.from_date
95: from isc_dbi_plan_organizations org,
96: isc_dbi_plans plan,
97: opi_dbi_baseline_schedules sched,
98: opi_dbi_baseline_plans setup
99: where sched.next_collection_date <= g_sysdate
100: and sched.schedule_type = 1
101: and sched.baseline_id = setup.baseline_id
171: mtl_system_Items_b msi,
172: (select /*+ no_merge use_hash(sched,setup,plan,orgs) */
173: orgs.organization_id,
174: sched.from_date
175: from opi_dbi_baseline_schedules sched,
176: opi_dbi_baseline_plans setup,
177: isc_dbi_plans plan,
178: isc_dbi_plan_organizations orgs
179: where sched.next_collection_date <= :g_sysdate
216: ) io,
217: (select /*+ no_merge use_hash(sched,setup,plan,orgs) */
218: orgs.organization_id,
219: sched.from_date
220: from opi_dbi_baseline_schedules sched,
221: opi_dbi_baseline_plans setup,
222: isc_dbi_plans plan,
223: isc_dbi_plan_organizations orgs
224: where sched.next_collection_date <= :g_sysdate
697: distinct org.organization_id,
698: sched.from_date
699: from isc_dbi_plan_organizations org,
700: isc_dbi_plans plan,
701: opi_dbi_baseline_schedules sched,
702: opi_dbi_baseline_plans setup
703: where sched.next_collection_date <= g_sysdate
704: and sched.schedule_type = 1
705: and sched.baseline_id = setup.baseline_id
831: mtl_parameters mp,
832: mtl_system_Items_b msi,
833: (select orgs.organization_id,
834: sched.from_date
835: from opi_dbi_baseline_schedules sched,
836: opi_dbi_baseline_plans setup,
837: isc_dbi_plans plan,
838: isc_dbi_plan_organizations orgs
839: where sched.next_collection_date <= :g_sysdate
863: :g_last_update_login
864: from
865: (select orgs.organization_id,
866: sched.from_date
867: from opi_dbi_baseline_schedules sched,
868: opi_dbi_baseline_plans setup,
869: isc_dbi_plans plan,
870: isc_dbi_plan_organizations orgs
871: where sched.next_collection_date <= :g_sysdate
1052: select def.baseline_id,
1053: def.data_start_date,
1054: sched.from_date
1055: from OPI_DBI_BASELINE_DEFINITIONS def,
1056: OPI_DBI_BASELINE_SCHEDULES sched
1057: where def.baseline_id = sched.baseline_id
1058: and sched.next_collection_date <= trunc(sysdate)
1059: and sched.schedule_type = 1
1060: ;
1182: from
1183: (
1184: select sched.baseline_id,
1185: sched.from_date
1186: from OPI_DBI_BASELINE_SCHEDULES sched,
1187: OPI_DBI_BASELINE_PLANS setup
1188: where sched.next_collection_date <= g_sysdate
1189: and sched.schedule_type = 1
1190: and sched.baseline_id = setup.baseline_id
1232: into l_count
1233: from
1234: (
1235: select orgs.organization_id, count(orgs.plan_id) num_of_plans
1236: from OPI_DBI_BASELINE_SCHEDULES sched,
1237: OPI_DBI_BASELINE_PLANS setup,
1238: ISC_DBI_PLANS plan,
1239: ISC_DBI_PLAN_ORGANIZATIONS orgs
1240: where sched.next_collection_date <= g_sysdate
1259: BIS_COLLECTION_UTILITIES.put_line('Check from_date against plan run date.');
1260: BEGIN
1261: select count(*)
1262: into l_count
1263: from OPI_DBI_BASELINE_SCHEDULES sched,
1264: OPI_DBI_BASELINE_PLANS setup,
1265: ISC_DBI_PLANS plan
1266: where sched.baseline_id = setup.baseline_id
1267: and setup.plan_name = plan.compile_designator
1294: RPAD(TO_CHAR(sched.from_date, 'DD-MON-YYYY'), 20, ' ') || ' ' ||
1295: RPAD(TO_CHAR(plan.data_start_date, 'DD-MON-YYYY'), 20, ' ')
1296: bulk collect into l_strings
1297: from OPI_DBI_BASELINE_DEFINITIONS def,
1298: OPI_DBI_BASELINE_SCHEDULES sched,
1299: OPI_DBI_BASELINE_PLANS setup,
1300: ISC_DBI_PLANS plan
1301: where def.baseline_id = sched.baseline_id
1302: and sched.baseline_id = setup.baseline_id
1325: l_segment_num := 70;
1326: BEGIN
1327: select def.baseline_name, sched.baseline_id, def.last_collected_date
1328: bulk collect into l_strings, l_baseline_ids, l_collected_dates
1329: from OPI_DBI_BASELINE_SCHEDULES sched,
1330: OPI_DBI_BASELINE_DEFINITIONS def
1331: where sched.next_collection_date <= g_sysdate
1332: and sched.schedule_type = 1
1333: and def.baseline_id = sched.baseline_id
1450: plan.compile_designator,
1451: plan.organization_id,
1452: plan.cutoff_date
1453: from
1454: opi_dbi_baseline_schedules sched,
1455: opi_dbi_baseline_plans setup,
1456: isc_dbi_plans plan
1457: where sched.next_collection_date <= :g_sysdate
1458: and sched.schedule_type = 1
1569: select SUM(decode(sign(sched.from_date - def.last_from_date), -1, 1, 0)) del_cnt,
1570: SUM(decode(sign(sched.from_date - def.last_from_date), 1, 1, 0)) arv_cnt
1571: into l_delete, l_archive
1572: from OPI_DBI_BASELINE_DEFINITIONS def,
1573: OPI_DBI_BASELINE_SCHEDULES sched
1574: where sched.next_collection_date <= g_sysdate
1575: and sched.schedule_type = 1
1576: and sched.baseline_id = def.baseline_id
1577: ;
1591: where
1592: rowid in
1593: (select f.rowid
1594: from OPI_DBI_PTP_PLAN_F f,
1595: OPI_DBI_BASELINE_SCHEDULES sched
1596: where sched.next_collection_date <= g_sysdate
1597: and sched.schedule_type = 1
1598: and f.baseline_id = sched.baseline_id
1599: and f.transaction_date >= sched.from_date
1642: g_sysdate,
1643: g_last_updated_by,
1644: g_last_update_login
1645: from OPI_PTP_SUM_STG_MV stg,
1646: OPI_DBI_BASELINE_SCHEDULES sched,
1647: fii_time_day day
1648: where sched.next_collection_date <= g_sysdate
1649: and sched.schedule_type = 1
1650: and stg.baseline_id = sched.baseline_id
1706: g_sysdate,
1707: g_last_updated_by,
1708: g_last_update_login
1709: from OPI_DBI_BASELINE_DEFINITIONS def,
1710: OPI_DBI_BASELINE_SCHEDULES sched,
1711: OPI_DBI_BASELINE_PLANS setup,
1712: ISC_DBI_PLANS plan,
1713: ISC_DBI_PLAN_ORGANIZATIONS orgs
1714: where def.baseline_id = sched.baseline_id
1730: min(nvl(sched.TO_DATE, plan.cutoff_date)),
1731: g_sysdate,
1732: g_last_updated_by,
1733: g_last_update_login
1734: from OPI_DBI_BASELINE_SCHEDULES sched,
1735: OPI_DBI_BASELINE_PLANS setup,
1736: ISC_DBI_PLANS plan
1737: where def.baseline_id = sched.baseline_id
1738: and sched.baseline_id = setup.baseline_id
1740: and setup.owning_org_id = plan.organization_id
1741: group by def.data_start_date, sched.FROM_DATE
1742: )
1743: where def.baseline_id in
1744: (select baseline_id from opi_dbi_baseline_schedules
1745: where next_collection_date <= g_sysdate
1746: and schedule_type = 1
1747: )
1748: ;
1816: where baseline_id = l_baseline_record.baseline_id
1817: ;
1818: END IF;
1819:
1820: update OPI_DBI_BASELINE_SCHEDULES
1821: set from_date = null,
1822: to_date = null,
1823: schedule_type = null,
1824: next_collection_date = null