[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';
165: Raise FND_API.G_EXC_ERROR;
166: end if; /* p_sweep */
167: Else
168: If (p_trx_type_id is not null) then
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);
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
178: (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
179: End If;
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
293: Cursor c_open_trx_ar(l_prd_name in Varchar2) is
294: Select 1 from dual where exists
295: (Select 'X' from
296: fun_trx_batches ftb, fun_trx_headers fth,
297: fun_period_statuses fps where ftb.batch_id = fth.batch_id and
298: ftb.trx_type_id=fps.trx_type_id and
299: ftb.gl_date >= fps.start_date and ftb.gl_date <=
300: fps.end_date and fps.period_name = l_prd_name and
301: fth.status not in ('NEW', 'REJECTED', 'COMPLETE', 'XFER_AR')
303: cursor c_open_int_ar(l_prd_name in Varchar2) is
304: Select 1 from dual where exists
305: (Select 'X' from
306: ra_interface_lines_all ri,
307: fun_period_statuses fps
308: where ri.batch_source_name = 'Global Intercompany' and
309: ri.org_id = p_org_id and
310: ri.gl_date >= fps.start_date and ri.gl_date <=
311: fps.end_date and
313: Cursor c_open_trx_gl(l_prd_name in Varchar2) is
314: Select 1 from dual where exists
315: (Select 'X' from
316: fun_trx_batches ftb, fun_trx_headers fth,
317: fun_period_statuses fps,fun_system_options fso where ftb.batch_id = fth.batch_id and
318: ftb.gl_date >= fps.start_date and ftb.gl_date <=
319: fps.end_date and
320: ftb.trx_type_id=fps.trx_type_id and
321: fps.period_name = l_prd_name and
326: cursor c_open_int_gl(l_prd_name in Varchar2) is
327: Select 1 from dual where exists
328: (Select 'X' from
329: gl_interface gi,
330: fun_period_statuses fps,fun_system_options fso
331: where gi.user_je_source_name = 'Global Intercompany' and
332: gi.user_je_category_name = 'Global Intercompany' and
333: gi.ledger_id = p_ledger_id and
334: gi.reference_date >= fps.start_date and gi.accounting_date <=
605: l_running_total_dr FUN_TRX_BATCHES.RUNNING_TOTAL_DR%TYPE;
606: l_running_total_cr FUN_TRX_BATCHES.RUNNING_TOTAL_CR%TYPE;
607: l_header_trx_id FUN_TRX_HEADERS.TRX_ID%TYPE;
608: l_line_id FUN_TRX_LINES.LINE_ID%TYPE;
609: l_start_date FUN_PERIOD_STATUSES.START_DATE%TYPE;
610: l_end_date FUN_PERIOD_STATUSES.END_DATE%TYPE;
611: sqlstmt VARCHAR2(3000);
612:
613: TYPE c_partial_batches_type IS REF CURSOR;
606: l_running_total_cr FUN_TRX_BATCHES.RUNNING_TOTAL_CR%TYPE;
607: l_header_trx_id FUN_TRX_HEADERS.TRX_ID%TYPE;
608: l_line_id FUN_TRX_LINES.LINE_ID%TYPE;
609: l_start_date FUN_PERIOD_STATUSES.START_DATE%TYPE;
610: l_end_date FUN_PERIOD_STATUSES.END_DATE%TYPE;
611: sqlstmt VARCHAR2(3000);
612:
613: TYPE c_partial_batches_type IS REF CURSOR;
614: -- Bug 7115161. Added Batch Number field
632: IF (p_trx_type_id is NOT NULL) THEN
633:
634: SELECT START_DATE
635: INTO l_start_date
636: FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
637: WHERE FPS.PERIOD_NAME = p_period_name
638: AND FPS.TRX_TYPE_ID = p_trx_type_id
639: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR, '~~')
640: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,'~~');
640: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,'~~');
641:
642: SELECT END_DATE
643: INTO l_end_date
644: FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
645: WHERE FPS.PERIOD_NAME = p_period_name
646: AND FPS.TRX_TYPE_ID = p_trx_type_id
647: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,'~~')
648: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,'~~');
664: sqlstmt := 'SELECT BATCH_ID, BATCH_NUMBER
665: FROM FUN_TRX_BATCHES
666: WHERE GL_DATE >=
667: (SELECT DISTINCT START_DATE
668: FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
669: WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
670: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
671: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
672: AND GL_DATE <=
670: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
671: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
672: AND GL_DATE <=
673: (SELECT DISTINCT END_DATE
674: FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
675: WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
676: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
677: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
678: AND TRX_TYPE_ID IN
676: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
677: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
678: AND TRX_TYPE_ID IN
679: (SELECT TRX_TYPE_ID
680: FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
681: WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
682: AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
683: AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
684: AND BATCH_ID IN
1082: cursor c_chk_open1(l_trx_type_id in Number)
1083: is
1084: Select 1 from dual where exists
1085: (Select 'X' from
1086: fun_period_statuses fps,fun_system_options fso where p_sweep_GL_date
1087: >= fps.start_date and p_sweep_GL_date <= fps.end_date and
1088: status = 'O' and trx_type_id = l_trx_type_id
1089: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1090: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
1091: cursor c_chk_prd_open1(l_prd_name in Varchar2,l_trx_type_id in
1092: Number) is
1093: Select 1 from dual where exists
1094: (Select 'X' from
1095: fun_period_statuses fps,fun_system_options fso where period_name = l_prd_name and
1096: trx_type_id = l_trx_type_id and status IN ('O','S')
1097: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1098: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
1099: cursor c_chk_open2(l_prd_name in Varchar2) is
1099: cursor c_chk_open2(l_prd_name in Varchar2) is
1100: Select 1 from dual where exists
1101: (Select 'X'
1102: from
1103: fun_period_statuses
1104: fps1, fun_period_statuses fps2,fun_system_options fso where p_sweep_GL_date
1105: >= fps1.start_date and p_sweep_GL_date <= fps1.end_date and
1106: fps1.status = 'O' and fps2.period_name = l_prd_name and fps2.status = 'S'
1107: and fps1.trx_type_id = fps2.trx_type_id
1100: Select 1 from dual where exists
1101: (Select 'X'
1102: from
1103: fun_period_statuses
1104: fps1, fun_period_statuses fps2,fun_system_options fso where p_sweep_GL_date
1105: >= fps1.start_date and p_sweep_GL_date <= fps1.end_date and
1106: fps1.status = 'O' and fps2.period_name = l_prd_name and fps2.status = 'S'
1107: and fps1.trx_type_id = fps2.trx_type_id
1108: AND fps1.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1112: cursor c_chk_prd_open2(l_prd_name in Varchar2) is
1113: Select 1 from dual where exists
1114: (Select 'X'
1115: from
1116: fun_period_statuses fps,fun_system_options fso where period_name = l_prd_name and status = 'O'
1117: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1118: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
1119: l_count number;
1120: l_api_name constant varchar2(30) := 'SWEEPING_TRANSACTIONS';
1150: fetch c_chk_prd_open1 into l_count;
1151: if (c_chk_prd_open1%notfound) then
1152: p_errbuff := 'Period name not in open period for the trx';
1153: Print('Period name not in open period for the trx');
1154: Update fun_period_statuses set status = 'O' where
1155: trx_type_id = p_trx_type_id and period_name = p_period_name;
1156: Raise FND_API.G_EXC_ERROR;
1157: End If;
1158: close c_chk_prd_open1;
1157: End If;
1158: close c_chk_prd_open1;
1159: Print('Updating Period Status to Sweep In Progress');
1160: /* Update period to Sweep In Progress */
1161: Update fun_period_statuses set status = 'S' where
1162: trx_type_id = p_trx_type_id and period_name = p_period_name
1163: AND (inteco_calendar,inteco_period_type) IN
1164: (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
1165: Print('Validation: GL_DATE Passed should be Open Period');
1168: fetch c_chk_open1 into l_count;
1169: if (c_chk_open1%notfound) then
1170: p_errbuff := 'GL DATE Passed is not in Open Period';
1171: Print('GL_DATE Passed is not in Open Period');
1172: Update fun_period_statuses set status = 'O' where
1173: trx_type_id = p_trx_type_id and period_name = p_period_name;
1174: Raise FND_API.G_EXC_ERROR;
1175: End If;
1176: close c_chk_open1;
1177: Print('Sweeping the transactions');
1178: /* Update the Periods */
1179: Update fun_trx_batches set gl_date =
1180: p_sweep_GL_date Where gl_date >= (select start_date from
1181: fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
1182: fps.trx_type_id = p_trx_type_id
1183: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1184: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')) and gl_Date <=
1185: (select end_date from fun_period_statuses fps,fun_system_options fso where fps.period_name
1181: fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
1182: fps.trx_type_id = p_trx_type_id
1183: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1184: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')) and gl_Date <=
1185: (select end_date from fun_period_statuses fps,fun_system_options fso where fps.period_name
1186: = p_period_name and fps.trx_type_id = p_trx_type_id
1187: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1188: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1189: And trx_type_id = p_trx_type_id
1195: sweep_partial_batches(p_errbuff, p_period_name, p_trx_type_id, p_sweep_GL_date);
1196:
1197: /* Update the Status of Period as Closed */
1198: if (nvl(p_close,'Y') = 'Y') then
1199: Update fun_period_statuses set status = 'C' where
1200: trx_type_id = p_trx_type_id and period_name = p_period_name
1201: AND (inteco_calendar,inteco_period_type) IN (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') from fun_system_options);
1202: Print('Closing for Period ');
1203: End If;
1211: Raise FND_API.G_EXC_ERROR;
1212: End If;
1213: close c_chk_prd_open2;
1214: /* Update period to Sweep In Progress */
1215: Update fun_period_statuses set status = 'S' where
1216: period_name = p_period_name and status = ('O');
1217: /* Whether GL_DATE passed is in Open Period */
1218: open c_chk_open2(p_period_name);
1219: fetch c_chk_open2 into l_count;
1225: close c_chk_open2;
1226: /* Sweep the transactions */
1227: Update fun_trx_batches set gl_date = p_sweep_GL_date
1228: Where gl_date >= (select distinct start_date from
1229: fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
1230: fps.trx_type_id = p_trx_type_id
1231: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1232: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1233: and gl_Date <= (select distinct end_date from fun_period_statuses fps,fun_system_options fso
1229: fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
1230: fps.trx_type_id = p_trx_type_id
1231: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1232: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1233: and gl_Date <= (select distinct end_date from fun_period_statuses fps,fun_system_options fso
1234: where fps.period_name = p_period_name and
1235: fps.trx_type_id = p_trx_type_id
1236: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1237: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1234: where fps.period_name = p_period_name and
1235: fps.trx_type_id = p_trx_type_id
1236: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1237: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1238: AND trx_type_id IN (select trx_type_id from fun_period_statuses fps,fun_system_options fso
1239: where fps.period_name = p_period_name and
1240: fps.trx_type_id = p_trx_type_id
1241: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1242: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1248: sweep_partial_batches(p_errbuff, p_period_name, p_trx_type_id, p_sweep_GL_date);
1249:
1250: /* Close the Period */
1251: if (nvl(p_close,'Y') = 'Y') then
1252: Update fun_period_statuses set status = 'C' where period_name =
1253: p_period_name and status in ('S')
1254: AND (inteco_calendar,inteco_period_type) IN (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
1255: Print('Closing for Period for all trx types');
1256: End If;
1310: END sweep_transactions;
1311: PROCEDURE insert_details_for_years (p_per_year number , p_per_type varchar2 , p_per_set_name varchar2,p_trx_type_id number)
1312: AS
1313: BEGIN
1314: INSERT INTO fun_period_statuses
1315: (PERIOD_NAME
1316: , PERIOD_YEAR
1317: , START_DATE
1318: , END_DATE
1348: END;
1349: 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 )
1350: AS
1351: BEGIN
1352: INSERT INTO fun_period_statuses
1353: (PERIOD_NAME
1354: , PERIOD_YEAR
1355: , START_DATE
1356: , END_DATE
1402: gl.period_set_name = fun.inteco_calendar
1403: and gl.period_type = fun.inteco_period_type
1404: and gl.period_year = c_per_year;
1405: CURSOR c_max_fun_per_yr IS
1406: select max(period_year) period_year,trx_type_id from fun_period_statuses fps, fun_system_options fso
1407: WHERE fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1408: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')
1409: group by trx_type_id ;
1410: CURSOR c_max_fun_per_num(c_per_yr number,c_trx_type number ) IS
1407: WHERE fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1408: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')
1409: group by trx_type_id ;
1410: CURSOR c_max_fun_per_num(c_per_yr number,c_trx_type number ) IS
1411: select max(period_num) from fun_period_statuses fps,fun_system_options fso
1412: where period_year = c_per_yr
1413: and trx_type_id = c_trx_type
1414: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1415: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~');
1415: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~');
1416: CURSOR c_inexist_fun_trx(c_per_year number,c_per_name varchar2 ) IS
1417: select trx_type_id from fun_trx_types_vl v
1418: where not exists
1419: ( select fps.trx_type_id from fun_period_statuses fps,fun_system_options fso
1420: WHERE fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1421: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~') and fps.period_year = c_per_year
1422: and fps.period_name = c_per_name
1423: and fps.trx_type_id = v.trx_type_id);
1464: ELSE
1465: select PERIOD_NUM, period_name
1466: into l_PERIOD_NUM, l_period_name
1467: from
1468: (select min(fps.PERIOD_NUM) PERIOD_NUM, fps.period_name from fun_period_statuses fps, fun_system_options fso
1469: where fps.period_year = l_max_fun_year
1470: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1471: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')
1472: group by fps.period_name
1536: SELECT fso.inteco_calendar,fso.inteco_period_type
1537: FROM fun_system_options fso;
1538: CURSOR c_select_status IS
1539: select fps.status
1540: from fun_period_statuses fps,fun_system_options fso,
1541: fun_trx_types_vl ftt
1542: where trunc(p_date) between fps.start_date and fps.end_date
1543: and fps.trx_type_id = ftt.trx_type_id
1544: and ftt.trx_type_id = p_trx_type_id