[Home] [Help]
20: pstart_date in varchar2,
21: pend_date in varchar2 ) IS
22: select period_name, closing_status,
23: period_type, period_year, period_num
24: from gl_period_statuses
25: where application_id = app_id
26: and set_of_books_id = sob_id
27: and
28: (
1297: period_type,
1298: period_year,
1299: period_num,
1300: quarter_num
1301: from gl_period_statuses ps
1302: --begin anuj
1303: /* Multi-Org Access Control Changes for SSA;Begin;anukumar;09/11/2002*/
1304: where ps.set_of_books_id = sysparm.set_of_books_id
1305: /* Multi-Org Access Control Changes for SSA;End;anukumar;09/11/2002*/
1318: period_type,
1319: period_year,
1320: period_num,
1321: quarter_num
1322: from gl_period_statuses ps,
1323: ar_system_parameters p
1324: where ps.set_of_books_id = p.set_of_books_id
1325: and application_id = 222
1326: and adjustment_period_flag = 'N'
1543: select decode(max(period_name),
1544: '', 0,
1545: 1)
1546: into pg_period(to_char(l_gl_date,'j'))
1547: from gl_period_statuses
1548: where application_id = p_application_id
1549: and set_of_books_id = p_set_of_books_id
1550: and adjustment_period_flag = 'N'
1551: and l_gl_date between start_date and end_date
1561: select decode(max(period_name),
1562: '', 0,
1563: 1)
1564: into pg_period_open(to_char(l_gl_date,'j'))
1565: from gl_period_statuses
1566: where application_id = p_application_id
1567: and set_of_books_id = p_set_of_books_id
1568: and adjustment_period_flag = 'N'
1569: and l_gl_date between start_date and end_date
1583: select decode(max(period_name),
1584: '', 0,
1585: 1)
1586: into num_return_value
1587: from gl_period_statuses
1588: where application_id = p_application_id
1589: and set_of_books_id = p_set_of_books_id
1590: and adjustment_period_flag = 'N'
1591: and l_gl_date between start_date and end_date
1594: select decode(max(period_name),
1595: '', 0,
1596: 1)
1597: into num_return_value
1598: from gl_period_statuses
1599: where application_id = p_application_id
1600: and set_of_books_id = p_set_of_books_id
1601: and adjustment_period_flag = 'N'
1602: and l_gl_date between start_date and end_date
1889:
1890:
1891: SELECT min(start_date)
1892: INTO candidate_gl_date
1893: FROM gl_period_statuses
1894: WHERE application_id = h_application_id
1895: AND set_of_books_id = h_set_of_books_id
1896: AND adjustment_period_flag = 'N'
1897: AND closing_status IN ('O','F','N')
1924:
1925:
1926: select max(d.end_date)
1927: into candidate_gl_date
1928: from gl_period_statuses d,
1929: gl_period_statuses s
1930: where d.application_id = s.application_id
1931: and d.set_of_books_id = s.set_of_books_id
1932: and d.adjustment_period_flag = 'N'
1925:
1926: select max(d.end_date)
1927: into candidate_gl_date
1928: from gl_period_statuses d,
1929: gl_period_statuses s
1930: where d.application_id = s.application_id
1931: and d.set_of_books_id = s.set_of_books_id
1932: and d.adjustment_period_flag = 'N'
1933: and d.end_date < sysdate
1994: Try the open period prior to the trx_date*/
1995:
1996: select max(end_date)
1997: into candidate_gl_date
1998: from gl_period_statuses
1999: where application_id = h_application_id
2000: and set_of_books_id = h_set_of_books_id
2001: and adjustment_period_flag = 'N'
2002: and closing_status = 'O'
2027: /* Try first Open period after trx_date */
2028:
2029: select min(start_date)
2030: into candidate_gl_date
2031: from gl_period_statuses
2032: where application_id = h_application_id
2033: and set_of_books_id = h_set_of_books_id
2034: and adjustment_period_flag = 'N'
2035: and closing_status = 'O'
2057: /* Try first Future period after trx_date */
2058:
2059: select min(start_date)
2060: into candidate_gl_date
2061: from gl_period_statuses
2062: where application_id = h_application_id
2063: and set_of_books_id = h_set_of_books_id
2064: and adjustment_period_flag = 'N'
2065: and closing_status = 'F'
2089: try the previous open period */
2090:
2091: select max(end_date)
2092: into candidate_gl_date
2093: from gl_period_statuses
2094: where application_id = h_application_id
2095: and set_of_books_id = h_set_of_books_id
2096: and adjustment_period_flag = 'N'
2097: and closing_status = 'O'
2124: Changed the function from max to min*/
2125:
2126: select min(start_date)
2127: into candidate_gl_date
2128: from gl_period_statuses
2129: where application_id = h_application_id
2130: and set_of_books_id = h_set_of_books_id
2131: and adjustment_period_flag = 'N'
2132: and closing_status = 'O'
2155:
2156: select max(start_date), max(end_date)
2157: into candidate_start_gl_date,
2158: candidate_end_gl_date
2159: from gl_period_statuses
2160: where application_id = h_application_id
2161: and set_of_books_id = h_set_of_books_id
2162: and adjustment_period_flag = 'N'
2163: and closing_status = 'O';
2189: /* try the first Future period >= sysdate */
2190:
2191: select min(start_date)
2192: into candidate_gl_date
2193: from gl_period_statuses
2194: where application_id = h_application_id
2195: and set_of_books_id = h_set_of_books_id
2196: and adjustment_period_flag = 'N'
2197: and closing_status = 'F'
2221:
2222: select max(start_date), max(end_date)
2223: into candidate_start_gl_date,
2224: candidate_end_gl_date
2225: from gl_period_statuses
2226: where application_id = h_application_id
2227: and set_of_books_id = h_set_of_books_id
2228: and adjustment_period_flag = 'N'
2229: and closing_status = 'F';