[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
314: fps.period_name = l_prd_name);
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
592:
593: CURSOR c_trx_id (l_batch_id in NUMBER)
594: IS
595: SELECT TRX_ID
596: FROM FUN_TRX_HEADERS
597: WHERE BATCH_ID = l_batch_id
598: AND STATUS IN ('SENT','ERROR','RECEIVED');
599:
600: l_control_date_tbl FUN_SEQ.CONTROL_DATE_TBL_TYPE;
605: l_batch_id FUN_TRX_BATCHES.BATCH_ID%TYPE;
606: l_batch_number FUN_TRX_BATCHES.BATCH_NUMBER%TYPE;
607: l_running_total_dr FUN_TRX_BATCHES.RUNNING_TOTAL_DR%TYPE;
608: l_running_total_cr FUN_TRX_BATCHES.RUNNING_TOTAL_CR%TYPE;
609: l_header_trx_id FUN_TRX_HEADERS.TRX_ID%TYPE;
610: l_line_id FUN_TRX_LINES.LINE_ID%TYPE;
611: l_start_date FUN_PERIOD_STATUSES.START_DATE%TYPE;
612: l_end_date FUN_PERIOD_STATUSES.END_DATE%TYPE;
613: sqlstmt VARCHAR2(3000);
822: AND GL_DATE <= ''' || l_end_date || '''
823: AND TRX_TYPE_ID = ''' || p_trx_type_id || '''
824: AND BATCH_ID IN
825: (SELECT BATCH_ID
826: FROM FUN_TRX_HEADERS
827: WHERE STATUS IN (''SENT'',''ERROR'',''RECEIVED''))
828: AND STATUS NOT IN (''COMPLETE'', ''NEW'')';
829:
830: ELSE
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
852: (SELECT BATCH_ID
853: FROM FUN_TRX_HEADERS
854: WHERE STATUS IN (''SENT'',''ERROR'',''RECEIVED''))
855: AND STATUS NOT IN (''COMPLETE'', ''NEW'');';
856: END IF;
857:
987: FOR l_trx_id in c_trx_id(l_partial_batch.BATCH_ID)
988: LOOP
989: l_header_trx_id := 0;
990: l_line_id := 0;
991: SELECT FUN_TRX_HEADERS_S.nextval INTO l_header_trx_id FROM DUAL;
992: SELECT FUN_TRX_LINES_S.nextval INTO l_line_id FROM DUAL;
993:
994: INSERT INTO FUN_TRX_HEADERS
995: (TRX_ID,
990: l_line_id := 0;
991: SELECT FUN_TRX_HEADERS_S.nextval INTO l_header_trx_id FROM DUAL;
992: SELECT FUN_TRX_LINES_S.nextval INTO l_line_id FROM DUAL;
993:
994: INSERT INTO FUN_TRX_HEADERS
995: (TRX_ID,
996: BATCH_ID,
997: STATUS,
998: INIT_WF_KEY,
1083: ATTRIBUTE13,
1084: ATTRIBUTE14,
1085: ATTRIBUTE15,
1086: ATTRIBUTE_CATEGORY
1087: FROM FUN_TRX_HEADERS
1088: WHERE TRX_ID = l_trx_id.TRX_ID;
1089:
1090: ------Bug#: 7129198 --- START
1091:
1164: from fnd_documents docs,
1165: FND_ATTACHED_DOCUMENTS atth,
1166: fnd_documents_tl tl
1167:
1168: where entity_name = ''FUN_TRX_HEADERS''
1169: and tl.LANGUAGE=''' || icx_language || '''
1170: and pk1_value=''' || l_trx_id.TRX_ID || '''
1171: and docs.document_id = atth.document_id
1172: and tl.document_id = atth.document_id';
1532: --Update Total of the newly created batch
1533: SELECT SUM(INIT_AMOUNT_DR),
1534: SUM (INIT_AMOUNT_CR)
1535: INTO l_running_total_dr, l_running_total_cr
1536: FROM FUN_TRX_HEADERS
1537: WHERE BATCH_ID = l_batch_id;
1538:
1539: UPDATE FUN_TRX_BATCHES
1540: SET RUNNING_TOTAL_DR = l_running_total_dr,
1541: RUNNING_TOTAL_CR = l_running_total_cr
1542: WHERE BATCH_ID = l_batch_id;
1543:
1544: -- Update status of old transactions
1545: UPDATE FUN_TRX_HEADERS
1546: SET STATUS = 'REJECTED',
1547: REJECT_REASON = 'Swept to New Batch ' || l_batch_number,
1548: LAST_UPDATED_BY = fnd_global.user_id,
1549: LAST_UPDATE_DATE = sysdate,
1554: -- Update status of old batch to complete
1555: UPDATE FUN_TRX_BATCHES
1556: SET STATUS = DECODE ((SELECT 1
1557: FROM DUAL
1558: WHERE EXISTS (SELECT 'X' FROM FUN_TRX_HEADERS
1559: WHERE BATCH_ID = l_partial_batch.BATCH_ID
1560: AND STATUS NOT IN ('COMPLETE', 'REJECTED'))), 1, 'SENT', 'COMPLETE'),
1561: LAST_UPDATED_BY = fnd_global.user_id,
1562: LAST_UPDATE_DATE = sysdate,
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
1698: And batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2 WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
1699: 'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED') AND h2.batch_id = batch_id)
1700: and status NOT IN ('COMPLETE', 'NEW'); -- Bug No : 6880343
1701:
1702: -- 6892783: Added to Sweep Partially Complete Batches
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,'~~'))
1751: And batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2 WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
1752: 'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED') AND h2.batch_id = batch_id)
1753: and status NOT IN ('COMPLETE', 'NEW'); -- Bug No : 6880343
1754:
1755: -- 6892783: Added to Sweep Partially Complete Batches