[Home] [Help]
33: x_request_id OUT NOCOPY NUMBER
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
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
43: WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
44: 'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED')
45: AND h2.batch_id = ftb.batch_id) -- Bug No : 6880343
46: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
46: AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
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
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
56: WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
57: 'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED')
58: AND h2.batch_id = ftb.batch_id) -- Bug No : 6880343
59: 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
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 <=
292: AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
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
312: fps.period_name = l_prd_name);
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
590:
591: CURSOR c_trx_id (l_batch_id in NUMBER)
592: IS
593: SELECT TRX_ID
594: FROM FUN_TRX_HEADERS
595: WHERE BATCH_ID = l_batch_id
596: AND STATUS IN ('SENT','ERROR','RECEIVED');
597:
598: l_control_date_tbl FUN_SEQ.CONTROL_DATE_TBL_TYPE;
603: l_batch_id FUN_TRX_BATCHES.BATCH_ID%TYPE;
604: l_batch_number FUN_TRX_BATCHES.BATCH_NUMBER%TYPE;
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);
655: AND GL_DATE <= ''' || l_end_date || '''
656: AND TRX_TYPE_ID = ''' || p_trx_type_id || '''
657: AND BATCH_ID IN
658: (SELECT BATCH_ID
659: FROM FUN_TRX_HEADERS
660: WHERE STATUS IN (''SENT'',''ERROR'',''RECEIVED''))
661: AND STATUS NOT IN (''COMPLETE'', ''NEW'')';
662:
663: ELSE
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
685: (SELECT BATCH_ID
686: FROM FUN_TRX_HEADERS
687: WHERE STATUS IN (''SENT'',''ERROR'',''RECEIVED''))
688: AND STATUS NOT IN (''COMPLETE'', ''NEW'');';
689: END IF;
690:
818: FOR l_trx_id in c_trx_id(l_partial_batch.BATCH_ID)
819: LOOP
820: l_header_trx_id := 0;
821: l_line_id := 0;
822: SELECT FUN_TRX_HEADERS_S.nextval INTO l_header_trx_id FROM DUAL;
823: SELECT FUN_TRX_LINES_S.nextval INTO l_line_id FROM DUAL;
824:
825: INSERT INTO FUN_TRX_HEADERS
826: (TRX_ID,
821: l_line_id := 0;
822: SELECT FUN_TRX_HEADERS_S.nextval INTO l_header_trx_id FROM DUAL;
823: SELECT FUN_TRX_LINES_S.nextval INTO l_line_id FROM DUAL;
824:
825: INSERT INTO FUN_TRX_HEADERS
826: (TRX_ID,
827: BATCH_ID,
828: STATUS,
829: INIT_WF_KEY,
914: ATTRIBUTE13,
915: ATTRIBUTE14,
916: ATTRIBUTE15,
917: ATTRIBUTE_CATEGORY
918: FROM FUN_TRX_HEADERS
919: WHERE TRX_ID = l_trx_id.TRX_ID;
920:
921: INSERT INTO FUN_TRX_LINES
922: (LINE_ID,
1027: --Update Total of the newly created batch
1028: SELECT SUM(INIT_AMOUNT_DR),
1029: SUM (INIT_AMOUNT_CR)
1030: INTO l_running_total_dr, l_running_total_cr
1031: FROM FUN_TRX_HEADERS
1032: WHERE BATCH_ID = l_batch_id;
1033:
1034: UPDATE FUN_TRX_BATCHES
1035: SET RUNNING_TOTAL_DR = l_running_total_dr,
1036: RUNNING_TOTAL_CR = l_running_total_cr
1037: WHERE BATCH_ID = l_batch_id;
1038:
1039: -- Update status of old transactions
1040: UPDATE FUN_TRX_HEADERS
1041: SET STATUS = 'REJECTED',
1042: REJECT_REASON = 'Swept to New Batch ' || l_batch_number,
1043: LAST_UPDATED_BY = fnd_global.user_id,
1044: LAST_UPDATE_DATE = sysdate,
1049: -- Update status of old batch to complete
1050: UPDATE FUN_TRX_BATCHES
1051: SET STATUS = DECODE ((SELECT 1
1052: FROM DUAL
1053: WHERE EXISTS (SELECT 'X' FROM FUN_TRX_HEADERS
1054: WHERE BATCH_ID = l_partial_batch.BATCH_ID
1055: AND STATUS NOT IN ('COMPLETE', 'REJECTED'))), 1, 'SENT', 'COMPLETE'),
1056: LAST_UPDATED_BY = fnd_global.user_id,
1057: LAST_UPDATE_DATE = sysdate,
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
1190: And batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2 WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
1191: 'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED') AND h2.batch_id = batch_id)
1192: and status NOT IN ('COMPLETE', 'NEW'); -- Bug No : 6880343
1193:
1194: -- 6892783: Added to Sweep Partially Complete Batches
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,'~~'))
1243: And batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2 WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
1244: 'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED') AND h2.batch_id = batch_id)
1245: and status NOT IN ('COMPLETE', 'NEW'); -- Bug No : 6880343
1246:
1247: -- 6892783: Added to Sweep Partially Complete Batches