[Home] [Help]
34: ) IS
35: Cursor c_open_trx1(l_prd_name in Varchar2,l_trx_type_id in Number) is
36: Select 1 from dual where exists
37: (Select 'X' from fun_trx_batches ftb, fun_trx_headers fth,
38: fun_period_statuses fps,fun_system_options fso where ftb.batch_id = fth.batch_id and
39: ftb.gl_date >= fps.start_date and ftb.gl_date <=
40: fps.end_date and fps.period_name = l_prd_name and
41: fps.trx_type_id = l_trx_type_id and ftb.trx_type_id = l_trx_type_id and
42: ftb.batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2
47: AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
48: Cursor c_open_trx2(l_prd_name in Varchar2) is
49: Select 1 from dual where exists
50: (Select 'X' from fun_trx_batches ftb, fun_trx_headers fth,
51: fun_period_statuses fps,fun_system_options fso where ftb.batch_id = fth.batch_id and
52: ftb.gl_date >= fps.start_date and ftb.gl_date <=
53: fps.end_date and fps.period_name = l_prd_name
54: and ftb.trx_type_id = fps.trx_type_id and
55: ftb.batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2
60: AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
61: Cursor c_open_prd1(l_prd_name in Varchar2, l_trx_type_id in
62: Number) is
63: Select 1 from dual where exists
64: (Select 'X' from fun_period_statuses fps,fun_system_options fso where fps.status = 'O'
65: and trx_type_id = l_trx_type_id and period_name = l_prd_name
66: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
67: AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
68: Cursor c_open_prd2(l_prd_name in Varchar2) is
66: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
67: AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
68: Cursor c_open_prd2(l_prd_name in Varchar2) is
69: Select 1 from dual where exists
70: (Select 'X' from fun_period_statuses fps,fun_system_options fso where fps.status = 'O'
71: and period_name = l_prd_name
72: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
73: AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
74: l_api_name CONSTANT VARCHAR2(30) := 'CLOSE_PERIOD';
169: Update fun_period_statuses set status = 'C'
170: where trx_type_id = p_trx_type_id and
171: period_name = p_period_name
172: AND (inteco_calendar,inteco_period_type) IN
173: (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
174: Else
175: Update fun_period_statuses set status = 'C' where
176: period_name = p_period_name and status in ('O')
177: AND (inteco_calendar,inteco_period_type) IN
174: Else
175: Update fun_period_statuses set status = 'C' where
176: period_name = p_period_name and status in ('O')
177: AND (inteco_calendar,inteco_period_type) IN
178: (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
179: End If;
180: End If; /* l_open */
181: /* Commit if p_commit is not passed False */
182: IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE) ) THEN
248: p_org_id IN NUMBER,
249: x_close OUT NOCOPY VARCHAR2
250: ) IS
251: Cursor c_ic_cal_defined is select inteco_calendar from
252: fun_system_options where
253: inteco_calendar is not null and
254: inteco_period_type is not null;
255: Cursor c_ic_cal(l_period_set_name in Varchar2, l_period_type in
256: Varchar2) IS select
254: inteco_period_type is not null;
255: Cursor c_ic_cal(l_period_set_name in Varchar2, l_period_type in
256: Varchar2) IS select
257: inteco_calendar from
258: fun_system_options where inteco_calendar =
259: l_period_set_name and inteco_period_type =
260: l_period_type;
261: Cursor c_ic_prd_open(l_prd_name in Varchar2) is
262: Select 1 from dual where exists
260: l_period_type;
261: Cursor c_ic_prd_open(l_prd_name in Varchar2) is
262: Select 1 from dual where exists
263: (Select 'X' from
264: fun_period_statuses fps,fun_system_options fso where period_name
265: = l_prd_name and status = 'O'
266: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
267: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
268: Cursor c_open_trx_ap(l_prd_name in Varchar2) is
268: Cursor c_open_trx_ap(l_prd_name in Varchar2) is
269: Select 1 from dual where exists
270: (Select 'X' from
271: fun_trx_batches ftb, fun_trx_headers fth,
272: fun_period_statuses fps,fun_system_options fso where
273: ftb.batch_id = fth.batch_id and
274: ftb.trx_type_id=fps.trx_type_id and
275: ftb.gl_date >= fps.start_date and ftb.gl_date <=
276: fps.end_date and fps.period_name = l_prd_name and
281: cursor c_open_int_ap(l_prd_name in Varchar2) is
282: Select 1 from dual where exists
283: (Select 'X' from
284: ap_invoices_interface api,
285: fun_period_statuses fps,fun_system_options fso
286: where api.source = 'GLOBAL_INTERCOMPANY' and
287: api.org_id = p_org_id and
288: api.gl_date >= fps.start_date and api.gl_date <=
289: fps.end_date and
315: Cursor c_open_trx_gl(l_prd_name in Varchar2) is
316: Select 1 from dual where exists
317: (Select 'X' from
318: fun_trx_batches ftb, fun_trx_headers fth,
319: fun_period_statuses fps,fun_system_options fso where ftb.batch_id = fth.batch_id and
320: ftb.gl_date >= fps.start_date and ftb.gl_date <=
321: fps.end_date and
322: ftb.trx_type_id=fps.trx_type_id and
323: fps.period_name = l_prd_name and
328: cursor c_open_int_gl(l_prd_name in Varchar2) is
329: Select 1 from dual where exists
330: (Select 'X' from
331: gl_interface gi,
332: fun_period_statuses fps,fun_system_options fso
333: where gi.user_je_source_name = 'Global Intercompany' and
334: gi.user_je_category_name = 'Global Intercompany' and
335: gi.ledger_id = p_ledger_id and
336: gi.reference_date >= fps.start_date and gi.accounting_date <=
799: IF (p_trx_type_id is NOT NULL) THEN
800:
801: SELECT START_DATE
802: INTO l_start_date
803: FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
804: WHERE FPS.PERIOD_NAME = p_period_name
805: AND FPS.TRX_TYPE_ID = p_trx_type_id
806: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR, '~~')
807: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,'~~');
807: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,'~~');
808:
809: SELECT END_DATE
810: INTO l_end_date
811: FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
812: WHERE FPS.PERIOD_NAME = p_period_name
813: AND FPS.TRX_TYPE_ID = p_trx_type_id
814: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,'~~')
815: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,'~~');
831: sqlstmt := 'SELECT BATCH_ID, BATCH_NUMBER
832: FROM FUN_TRX_BATCHES
833: WHERE GL_DATE >=
834: (SELECT DISTINCT START_DATE
835: FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
836: WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
837: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
838: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
839: AND GL_DATE <=
837: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
838: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
839: AND GL_DATE <=
840: (SELECT DISTINCT END_DATE
841: FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
842: WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
843: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
844: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
845: AND TRX_TYPE_ID IN
843: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
844: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
845: AND TRX_TYPE_ID IN
846: (SELECT TRX_TYPE_ID
847: FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
848: WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
849: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
850: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
851: AND BATCH_ID IN
1587: cursor c_chk_open1(l_trx_type_id in Number)
1588: is
1589: Select 1 from dual where exists
1590: (Select 'X' from
1591: fun_period_statuses fps,fun_system_options fso where p_sweep_GL_date
1592: >= fps.start_date and p_sweep_GL_date <= fps.end_date and
1593: status = 'O' and trx_type_id = l_trx_type_id
1594: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1595: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
1596: cursor c_chk_prd_open1(l_prd_name in Varchar2,l_trx_type_id in
1597: Number) is
1598: Select 1 from dual where exists
1599: (Select 'X' from
1600: fun_period_statuses fps,fun_system_options fso where period_name = l_prd_name and
1601: trx_type_id = l_trx_type_id and status IN ('O','S')
1602: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1603: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
1604: cursor c_chk_open2(l_prd_name in Varchar2) is
1605: Select 1 from dual where exists
1606: (Select 'X'
1607: from
1608: fun_period_statuses
1609: fps1, fun_period_statuses fps2,fun_system_options fso where p_sweep_GL_date
1610: >= fps1.start_date and p_sweep_GL_date <= fps1.end_date and
1611: fps1.status = 'O' and fps2.period_name = l_prd_name and fps2.status = 'S'
1612: and fps1.trx_type_id = fps2.trx_type_id
1613: AND fps1.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1617: cursor c_chk_prd_open2(l_prd_name in Varchar2) is
1618: Select 1 from dual where exists
1619: (Select 'X'
1620: from
1621: fun_period_statuses fps,fun_system_options fso where period_name = l_prd_name and status = 'O'
1622: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1623: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
1624: l_count number;
1625: l_api_name constant varchar2(30) := 'SWEEPING_TRANSACTIONS';
1668: /* Update period to Sweep In Progress */
1669: Update fun_period_statuses set status = 'S' where
1670: trx_type_id = p_trx_type_id and period_name = p_period_name
1671: AND (inteco_calendar,inteco_period_type) IN
1672: (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
1673: Print('Validation: GL_DATE Passed should be Open Period');
1674: /* Whether GL_DATE passed is in Open Period */
1675: open c_chk_open1(p_trx_type_id);
1676: fetch c_chk_open1 into l_count;
1685: Print('Sweeping the transactions');
1686: /* Update the Periods */
1687: Update fun_trx_batches set gl_date =
1688: p_sweep_GL_date Where gl_date >= (select start_date from
1689: fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
1690: fps.trx_type_id = p_trx_type_id
1691: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1692: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')) and gl_Date <=
1693: (select end_date from fun_period_statuses fps,fun_system_options fso where fps.period_name
1689: fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
1690: fps.trx_type_id = p_trx_type_id
1691: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1692: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')) and gl_Date <=
1693: (select end_date from fun_period_statuses fps,fun_system_options fso where fps.period_name
1694: = p_period_name and fps.trx_type_id = p_trx_type_id
1695: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1696: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1697: And trx_type_id = p_trx_type_id
1705: /* Update the Status of Period as Closed */
1706: if (nvl(p_close,'Y') = 'Y') then
1707: Update fun_period_statuses set status = 'C' where
1708: trx_type_id = p_trx_type_id and period_name = p_period_name
1709: AND (inteco_calendar,inteco_period_type) IN (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') from fun_system_options);
1710: Print('Closing for Period ');
1711: End If;
1712: Else
1713: /* Validate whether period passed is open */
1733: close c_chk_open2;
1734: /* Sweep the transactions */
1735: Update fun_trx_batches set gl_date = p_sweep_GL_date
1736: Where gl_date >= (select distinct start_date from
1737: fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
1738: fps.trx_type_id = p_trx_type_id
1739: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1740: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1741: and gl_Date <= (select distinct end_date from fun_period_statuses fps,fun_system_options fso
1737: fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
1738: fps.trx_type_id = p_trx_type_id
1739: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1740: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1741: and gl_Date <= (select distinct end_date from fun_period_statuses fps,fun_system_options fso
1742: where fps.period_name = p_period_name and
1743: fps.trx_type_id = p_trx_type_id
1744: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1745: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1742: where fps.period_name = p_period_name and
1743: fps.trx_type_id = p_trx_type_id
1744: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1745: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1746: AND trx_type_id IN (select trx_type_id from fun_period_statuses fps,fun_system_options fso
1747: where fps.period_name = p_period_name and
1748: fps.trx_type_id = p_trx_type_id
1749: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1750: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1758: /* Close the Period */
1759: if (nvl(p_close,'Y') = 'Y') then
1760: Update fun_period_statuses set status = 'C' where period_name =
1761: p_period_name and status in ('S')
1762: AND (inteco_calendar,inteco_period_type) IN (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
1763: Print('Closing for Period for all trx types');
1764: End If;
1765: End If; /* if p_trx_type_id */
1766: p_errbuff := null;
1849: ,FND_GLOBAL.LOGIN_ID
1850: ,sysdate
1851: ,inteco_calendar
1852: ,inteco_period_type
1853: from gl_periods,fun_system_options
1854: where period_set_name = p_per_set_name and period_type = p_per_type and
1855: period_year >p_per_year;
1856: END;
1857: PROCEDURE insert_details_for_periods (p_per_year number , p_per_type varchar2 , p_per_set_name varchar2 , p_period_num number,p_trx_type_id number )
1888: ,sysdate
1889: ,INTECO_CALENDAR
1890: ,INTECO_PERIOD_TYPE
1891: from gl_periods,
1892: fun_system_options
1893: where period_set_name = p_per_set_name and period_type = p_per_type and
1894: period_year =p_per_year and period_num > p_period_num ;
1895: END;
1896: PROCEDURE sync_calendars IS
1894: period_year =p_per_year and period_num > p_period_num ;
1895: END;
1896: PROCEDURE sync_calendars IS
1897: CURSOR c_min_gl_per_yr IS
1898: select min(period_year) from gl_periods gl, fun_system_options fun
1899: where
1900: gl.period_set_name = fun.inteco_calendar
1901: and gl.period_type = fun.inteco_period_type ;
1902: CURSOR c_max_gl_per_yr IS
1899: where
1900: gl.period_set_name = fun.inteco_calendar
1901: and gl.period_type = fun.inteco_period_type ;
1902: CURSOR c_max_gl_per_yr IS
1903: select max(period_year) from gl_periods gl, fun_system_options fun
1904: where
1905: gl.period_set_name = fun.inteco_calendar
1906: and gl.period_type = fun.inteco_period_type ;
1907: CURSOR c_max_gl_per_num(c_per_year number)IS
1904: where
1905: gl.period_set_name = fun.inteco_calendar
1906: and gl.period_type = fun.inteco_period_type ;
1907: CURSOR c_max_gl_per_num(c_per_year number)IS
1908: select max(period_num) from gl_periods gl , fun_system_options fun
1909: where
1910: gl.period_set_name = fun.inteco_calendar
1911: and gl.period_type = fun.inteco_period_type
1912: and gl.period_year = c_per_year;
1910: gl.period_set_name = fun.inteco_calendar
1911: and gl.period_type = fun.inteco_period_type
1912: and gl.period_year = c_per_year;
1913: CURSOR c_max_fun_per_yr IS
1914: select max(period_year) period_year,trx_type_id from fun_period_statuses fps, fun_system_options fso
1915: WHERE fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1916: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')
1917: group by trx_type_id ;
1918: CURSOR c_max_fun_per_num(c_per_yr number,c_trx_type number ) IS
1915: WHERE fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1916: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')
1917: group by trx_type_id ;
1918: CURSOR c_max_fun_per_num(c_per_yr number,c_trx_type number ) IS
1919: select max(period_num) from fun_period_statuses fps,fun_system_options fso
1920: where period_year = c_per_yr
1921: and trx_type_id = c_trx_type
1922: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1923: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~');
1923: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~');
1924: CURSOR c_inexist_fun_trx(c_per_year number,c_per_name varchar2 ) IS
1925: select trx_type_id from fun_trx_types_vl v
1926: where not exists
1927: ( select fps.trx_type_id from fun_period_statuses fps,fun_system_options fso
1928: WHERE fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1929: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~') and fps.period_year = c_per_year
1930: and fps.period_name = c_per_name
1931: and fps.trx_type_id = v.trx_type_id);
1950: CLOSE c_max_gl_per_yr;
1951: OPEN c_max_gl_per_num(l_gl_year);
1952: FETCH c_max_gl_per_num into l_gl_num ;
1953: CLOSE c_max_gl_per_num;
1954: SELECT inteco_calendar,inteco_period_type into l_sys_opt_cal_name , l_sys_opt_per_type from fun_system_options;
1955: /************* for inexistent trx types ********************/
1956: OPEN c_min_gl_per_yr;
1957: FETCH c_min_gl_per_yr into l_min_gl_year ;
1958: CLOSE c_min_gl_per_yr;
1972: ELSE
1973: select PERIOD_NUM, period_name
1974: into l_PERIOD_NUM, l_period_name
1975: from
1976: (select min(fps.PERIOD_NUM) PERIOD_NUM, fps.period_name from fun_period_statuses fps, fun_system_options fso
1977: where fps.period_year = l_max_fun_year
1978: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1979: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')
1980: group by fps.period_name
2041: FUNCTION get_fun_prd_status(p_date Date, p_trx_type_id number)
2042: RETURN VARCHAR2 IS
2043: CURSOR c_calendar_defined IS
2044: SELECT fso.inteco_calendar,fso.inteco_period_type
2045: FROM fun_system_options fso;
2046: CURSOR c_select_status IS
2047: select fps.status
2048: from fun_period_statuses fps,fun_system_options fso,
2049: fun_trx_types_vl ftt
2044: SELECT fso.inteco_calendar,fso.inteco_period_type
2045: FROM fun_system_options fso;
2046: CURSOR c_select_status IS
2047: select fps.status
2048: from fun_period_statuses fps,fun_system_options fso,
2049: fun_trx_types_vl ftt
2050: where trunc(p_date) between fps.start_date and fps.end_date
2051: and fps.trx_type_id = ftt.trx_type_id
2052: and ftt.trx_type_id = p_trx_type_id