[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 1 from dual where exists
(Select 'X' from fun_trx_batches ftb, fun_trx_headers fth,
fun_period_statuses fps,fun_system_options fso where ftb.batch_id = fth.batch_id and
ftb.gl_date >= fps.start_date and ftb.gl_date <=
fps.end_date and fps.period_name = l_prd_name and
fps.trx_type_id = l_trx_type_id and ftb.trx_type_id = l_trx_type_id and
ftb.batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2
WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED')
AND h2.batch_id = ftb.batch_id) -- Bug No : 6880343
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
Select 1 from dual where exists
(Select 'X' from fun_trx_batches ftb, fun_trx_headers fth,
fun_period_statuses fps,fun_system_options fso where ftb.batch_id = fth.batch_id and
ftb.gl_date >= fps.start_date and ftb.gl_date <=
fps.end_date and fps.period_name = l_prd_name
and ftb.trx_type_id = fps.trx_type_id and
ftb.batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2
WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED')
AND h2.batch_id = ftb.batch_id) -- Bug No : 6880343
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
Select 1 from dual where exists
(Select 'X' from fun_period_statuses fps,fun_system_options fso where fps.status = 'O'
and trx_type_id = l_trx_type_id and period_name = l_prd_name
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
Select 1 from dual where exists
(Select 'X' from fun_period_statuses fps,fun_system_options fso where fps.status = 'O'
and period_name = l_prd_name
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
Update fun_period_statuses set status = 'C'
where trx_type_id = p_trx_type_id and
period_name = p_period_name
AND (inteco_calendar,inteco_period_type) IN
(SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
Update fun_period_statuses set status = 'C' where
period_name = p_period_name and status in ('O')
AND (inteco_calendar,inteco_period_type) IN
(SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
Cursor c_ic_cal_defined is select inteco_calendar from
fun_system_options where
inteco_calendar is not null and
inteco_period_type is not null;
Varchar2) IS select
inteco_calendar from
fun_system_options where inteco_calendar =
l_period_set_name and inteco_period_type =
l_period_type;
Select 1 from dual where exists
(Select 'X' from
fun_period_statuses fps,fun_system_options fso where period_name
= l_prd_name and status = 'O'
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
Select 1 from dual where exists
(Select 'X' from
fun_trx_batches ftb, fun_trx_headers fth,
fun_period_statuses fps,fun_system_options fso where
ftb.batch_id = fth.batch_id and
ftb.trx_type_id=fps.trx_type_id and
ftb.gl_date >= fps.start_date and ftb.gl_date <=
fps.end_date and fps.period_name = l_prd_name and
fth.status not in ('NEW', 'REJECTED', 'COMPLETE')
and fun_tca_pkg.get_ou_id(fth.recipient_id) = p_org_id
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
Select 1 from dual where exists
(Select 'X' from
ap_invoices_interface api,
fun_period_statuses fps,fun_system_options fso
where api.source = 'GLOBAL_INTERCOMPANY' and
api.org_id = p_org_id and
api.gl_date >= fps.start_date and api.gl_date <=
fps.end_date and
fps.period_name = l_prd_name
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
Select 1 from dual where exists
(Select 'X' from
fun_trx_batches ftb, fun_trx_headers fth,
fun_period_statuses fps where ftb.batch_id = fth.batch_id and
ftb.trx_type_id=fps.trx_type_id and
ftb.gl_date >= fps.start_date and ftb.gl_date <=
fps.end_date and fps.period_name = l_prd_name and
fth.status not in ('NEW', 'REJECTED', 'COMPLETE', 'XFER_AR')
and fun_tca_pkg.get_ou_id(fth.initiator_id) = p_org_id);
Select 1 from dual where exists
(Select 'X' from
ra_interface_lines_all ri,
fun_period_statuses fps
where ri.batch_source_name = (SELECT name FROM
RA_BATCH_SOURCES_ALL WHERE BATCH_SOURCE_ID = 22 AND org_id = p_org_id) and
ri.org_id = p_org_id and
ri.gl_date >= fps.start_date and ri.gl_date <=
fps.end_date and
fps.period_name = l_prd_name);
Select 1 from dual where exists
(Select 'X' from
fun_trx_batches ftb, fun_trx_headers fth,
fun_period_statuses fps,fun_system_options fso where ftb.batch_id = fth.batch_id and
ftb.gl_date >= fps.start_date and ftb.gl_date <=
fps.end_date and
ftb.trx_type_id=fps.trx_type_id and
fps.period_name = l_prd_name and
fth.status not in ('NEW', 'REJECTED', 'COMPLETE') and
fth.to_ledger_id = p_ledger_id
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
Select 1 from dual where exists
(Select 'X' from
gl_interface gi,
fun_period_statuses fps,fun_system_options fso
where gi.user_je_source_name = 'Global Intercompany' and
gi.user_je_category_name = 'Global Intercompany' and
gi.ledger_id = p_ledger_id and
gi.reference_date >= fps.start_date and gi.accounting_date <=
fps.end_date and
fps.period_name = l_prd_name
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
SELECT TRX_ID
FROM FUN_TRX_HEADERS
WHERE BATCH_ID = l_batch_id
AND STATUS IN ('SENT','ERROR','RECEIVED');
PROGRAM_UPDATE_DATE FND_ATTACHED_DOCUMENTS.PROGRAM_UPDATE_DATE%TYPE,
ATTRIBUTE_CATEGORY FND_ATTACHED_DOCUMENTS.ATTRIBUTE_CATEGORY%TYPE,
ATTRIBUTE1 FND_ATTACHED_DOCUMENTS.ATTRIBUTE1%TYPE,
ATTRIBUTE2 FND_ATTACHED_DOCUMENTS.ATTRIBUTE2%TYPE,
ATTRIBUTE3 FND_ATTACHED_DOCUMENTS.ATTRIBUTE3%TYPE,
ATTRIBUTE4 FND_ATTACHED_DOCUMENTS.ATTRIBUTE4%TYPE,
ATTRIBUTE5 FND_ATTACHED_DOCUMENTS.ATTRIBUTE5%TYPE,
ATTRIBUTE6 FND_ATTACHED_DOCUMENTS.ATTRIBUTE6%TYPE,
ATTRIBUTE7 FND_ATTACHED_DOCUMENTS.ATTRIBUTE7%TYPE,
ATTRIBUTE8 FND_ATTACHED_DOCUMENTS.ATTRIBUTE8%TYPE,
ATTRIBUTE9 FND_ATTACHED_DOCUMENTS.ATTRIBUTE9%TYPE,
ATTRIBUTE10 FND_ATTACHED_DOCUMENTS.ATTRIBUTE10%TYPE,
ATTRIBUTE11 FND_ATTACHED_DOCUMENTS.ATTRIBUTE11%TYPE,
ATTRIBUTE12 FND_ATTACHED_DOCUMENTS.ATTRIBUTE12%TYPE,
ATTRIBUTE13 FND_ATTACHED_DOCUMENTS.ATTRIBUTE13%TYPE,
ATTRIBUTE14 FND_ATTACHED_DOCUMENTS.ATTRIBUTE14%TYPE,
ATTRIBUTE15 FND_ATTACHED_DOCUMENTS.ATTRIBUTE15%TYPE,
-------------------------------------
DATATYPE_ID FND_DOCUMENTS.DATATYPE_ID%TYPE,
CATEGORY_ID FND_DOCUMENTS.CATEGORY_ID%TYPE,
SECURITY_TYPE FND_DOCUMENTS.SECURITY_TYPE%TYPE,
SECURITY_ID FND_DOCUMENTS.SECURITY_ID%TYPE,
PUBLISH_FLAG FND_DOCUMENTS.PUBLISH_FLAG%TYPE,
IMAGE_TYPE FND_DOCUMENTS.IMAGE_TYPE%TYPE,
STORAGE_TYPE FND_DOCUMENTS.STORAGE_TYPE%TYPE,
USAGE_TYPE FND_DOCUMENTS.USAGE_TYPE%TYPE,
START_DATE_ACTIVE FND_DOCUMENTS.START_DATE_ACTIVE%TYPE,
END_DATE_ACTIVE FND_DOCUMENTS.END_DATE_ACTIVE%TYPE,
---------------------------------------
l_LANGUAGE fnd_documents_tl.LANGUAGE%TYPE,
DESCRIPTION fnd_documents_tl.DESCRIPTION%TYPE,
FILE_NAME fnd_documents.FILE_NAME%TYPE,
MEDIA_ID fnd_documents.MEDIA_ID%TYPE, -- IN OUT NOCOPY
DOC_ATTRIBUTE_CATEGORY fnd_documents_tl.DOC_ATTRIBUTE_CATEGORY%TYPE,
DOC_ATTRIBUTE1 fnd_documents_tl.DOC_ATTRIBUTE1%TYPE,
DOC_ATTRIBUTE2 fnd_documents_tl.DOC_ATTRIBUTE2%TYPE,
DOC_ATTRIBUTE3 fnd_documents_tl.DOC_ATTRIBUTE3%TYPE,
DOC_ATTRIBUTE4 fnd_documents_tl.DOC_ATTRIBUTE4%TYPE,
DOC_ATTRIBUTE5 fnd_documents_tl.DOC_ATTRIBUTE5%TYPE,
DOC_ATTRIBUTE6 fnd_documents_tl.DOC_ATTRIBUTE6%TYPE,
DOC_ATTRIBUTE7 fnd_documents_tl.DOC_ATTRIBUTE7%TYPE,
DOC_ATTRIBUTE8 fnd_documents_tl.DOC_ATTRIBUTE8%TYPE,
DOC_ATTRIBUTE9 fnd_documents_tl.DOC_ATTRIBUTE9%TYPE,
DOC_ATTRIBUTE10 fnd_documents_tl.DOC_ATTRIBUTE10%TYPE,
DOC_ATTRIBUTE11 fnd_documents_tl.DOC_ATTRIBUTE11%TYPE,
DOC_ATTRIBUTE12 fnd_documents_tl.DOC_ATTRIBUTE12%TYPE,
DOC_ATTRIBUTE13 fnd_documents_tl.DOC_ATTRIBUTE13%TYPE,
DOC_ATTRIBUTE14 fnd_documents_tl.DOC_ATTRIBUTE14%TYPE,
DOC_ATTRIBUTE15 fnd_documents_tl.DOC_ATTRIBUTE15%TYPE,
URL FND_DOCUMENTS.URL%TYPE,
TITLE fnd_documents_tl.TITLE%TYPE
);
PROGRAM_UPDATE_DATE FND_ATTACHED_DOCUMENTS.PROGRAM_UPDATE_DATE%TYPE,
ATTRIBUTE_CATEGORY FND_ATTACHED_DOCUMENTS.ATTRIBUTE_CATEGORY%TYPE,
ATTRIBUTE1 FND_ATTACHED_DOCUMENTS.ATTRIBUTE1%TYPE,
ATTRIBUTE2 FND_ATTACHED_DOCUMENTS.ATTRIBUTE2%TYPE,
ATTRIBUTE3 FND_ATTACHED_DOCUMENTS.ATTRIBUTE3%TYPE,
ATTRIBUTE4 FND_ATTACHED_DOCUMENTS.ATTRIBUTE4%TYPE,
ATTRIBUTE5 FND_ATTACHED_DOCUMENTS.ATTRIBUTE5%TYPE,
ATTRIBUTE6 FND_ATTACHED_DOCUMENTS.ATTRIBUTE6%TYPE,
ATTRIBUTE7 FND_ATTACHED_DOCUMENTS.ATTRIBUTE7%TYPE,
ATTRIBUTE8 FND_ATTACHED_DOCUMENTS.ATTRIBUTE8%TYPE,
ATTRIBUTE9 FND_ATTACHED_DOCUMENTS.ATTRIBUTE9%TYPE,
ATTRIBUTE10 FND_ATTACHED_DOCUMENTS.ATTRIBUTE10%TYPE,
ATTRIBUTE11 FND_ATTACHED_DOCUMENTS.ATTRIBUTE11%TYPE,
ATTRIBUTE12 FND_ATTACHED_DOCUMENTS.ATTRIBUTE12%TYPE,
ATTRIBUTE13 FND_ATTACHED_DOCUMENTS.ATTRIBUTE13%TYPE,
ATTRIBUTE14 FND_ATTACHED_DOCUMENTS.ATTRIBUTE14%TYPE,
ATTRIBUTE15 FND_ATTACHED_DOCUMENTS.ATTRIBUTE15%TYPE,
-------------------------------------
DATATYPE_ID FND_DOCUMENTS.DATATYPE_ID%TYPE,
CATEGORY_ID FND_DOCUMENTS.CATEGORY_ID%TYPE,
SECURITY_TYPE FND_DOCUMENTS.SECURITY_TYPE%TYPE,
SECURITY_ID FND_DOCUMENTS.SECURITY_ID%TYPE,
PUBLISH_FLAG FND_DOCUMENTS.PUBLISH_FLAG%TYPE,
IMAGE_TYPE FND_DOCUMENTS.IMAGE_TYPE%TYPE,
STORAGE_TYPE FND_DOCUMENTS.STORAGE_TYPE%TYPE,
USAGE_TYPE FND_DOCUMENTS.USAGE_TYPE%TYPE,
START_DATE_ACTIVE FND_DOCUMENTS.START_DATE_ACTIVE%TYPE,
END_DATE_ACTIVE FND_DOCUMENTS.END_DATE_ACTIVE%TYPE,
---------------------------------------
l_LANGUAGE fnd_documents_tl.LANGUAGE%TYPE,
DESCRIPTION fnd_documents_tl.DESCRIPTION%TYPE,
FILE_NAME fnd_documents.FILE_NAME%TYPE,
MEDIA_ID fnd_documents.MEDIA_ID%TYPE, -- IN OUT NOCOPY
DOC_ATTRIBUTE_CATEGORY fnd_documents_tl.DOC_ATTRIBUTE_CATEGORY%TYPE,
DOC_ATTRIBUTE1 fnd_documents_tl.DOC_ATTRIBUTE1%TYPE,
DOC_ATTRIBUTE2 fnd_documents_tl.DOC_ATTRIBUTE2%TYPE,
DOC_ATTRIBUTE3 fnd_documents_tl.DOC_ATTRIBUTE3%TYPE,
DOC_ATTRIBUTE4 fnd_documents_tl.DOC_ATTRIBUTE4%TYPE,
DOC_ATTRIBUTE5 fnd_documents_tl.DOC_ATTRIBUTE5%TYPE,
DOC_ATTRIBUTE6 fnd_documents_tl.DOC_ATTRIBUTE6%TYPE,
DOC_ATTRIBUTE7 fnd_documents_tl.DOC_ATTRIBUTE7%TYPE,
DOC_ATTRIBUTE8 fnd_documents_tl.DOC_ATTRIBUTE8%TYPE,
DOC_ATTRIBUTE9 fnd_documents_tl.DOC_ATTRIBUTE9%TYPE,
DOC_ATTRIBUTE10 fnd_documents_tl.DOC_ATTRIBUTE10%TYPE,
DOC_ATTRIBUTE11 fnd_documents_tl.DOC_ATTRIBUTE11%TYPE,
DOC_ATTRIBUTE12 fnd_documents_tl.DOC_ATTRIBUTE12%TYPE,
DOC_ATTRIBUTE13 fnd_documents_tl.DOC_ATTRIBUTE13%TYPE,
DOC_ATTRIBUTE14 fnd_documents_tl.DOC_ATTRIBUTE14%TYPE,
DOC_ATTRIBUTE15 fnd_documents_tl.DOC_ATTRIBUTE15%TYPE,
URL FND_DOCUMENTS.URL%TYPE,
TITLE fnd_documents_tl.TITLE%TYPE
);
SELECT START_DATE
INTO l_start_date
FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
WHERE FPS.PERIOD_NAME = p_period_name
AND FPS.TRX_TYPE_ID = p_trx_type_id
AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR, '~~')
AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,'~~');
SELECT END_DATE
INTO l_end_date
FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
WHERE FPS.PERIOD_NAME = p_period_name
AND FPS.TRX_TYPE_ID = p_trx_type_id
AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,'~~')
AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,'~~');
sqlstmt := 'SELECT BATCH_ID, BATCH_NUMBER
FROM FUN_TRX_BATCHES
WHERE GL_DATE >= ''' || l_start_date || '''
AND GL_DATE <= ''' || l_end_date || '''
AND TRX_TYPE_ID = ''' || p_trx_type_id || '''
AND BATCH_ID IN
(SELECT BATCH_ID
FROM FUN_TRX_HEADERS
WHERE STATUS IN (''SENT'',''ERROR'',''RECEIVED''))
AND STATUS NOT IN (''COMPLETE'', ''NEW'')';
sqlstmt := 'SELECT BATCH_ID, BATCH_NUMBER
FROM FUN_TRX_BATCHES
WHERE GL_DATE >=
(SELECT DISTINCT START_DATE
FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
AND GL_DATE <=
(SELECT DISTINCT END_DATE
FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
AND TRX_TYPE_ID IN
(SELECT TRX_TYPE_ID
FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
AND BATCH_ID IN
(SELECT BATCH_ID
FROM FUN_TRX_HEADERS
WHERE STATUS IN (''SENT'',''ERROR'',''RECEIVED''))
AND STATUS NOT IN (''COMPLETE'', ''NEW'');';
SELECT FUN_TRX_BATCHES_S.nextval INTO l_batch_id FROM DUAL;
INSERT INTO FUN_TRX_BATCHES
(BATCH_ID,
BATCH_NUMBER,
GL_DATE,
STATUS,
NOTE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
INITIATOR_ID,
FROM_LE_ID,
FROM_LEDGER_ID,
CONTROL_TOTAL,
RUNNING_TOTAL_CR,
RUNNING_TOTAL_DR,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
DESCRIPTION,
TRX_TYPE_ID,
TRX_TYPE_CODE,
BATCH_DATE,
REJECT_ALLOW_FLAG,
ORIGINAL_BATCH_ID,
REVERSED_BATCH_ID,
FROM_RECURRING_BATCH_ID,
INITIATOR_SOURCE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
AUTO_PRORATION_FLAG)
SELECT l_batch_id,
l_batch_number,
p_sweep_GL_date,
'NEW',
'Original Batch: ' || l_partial_batch.BATCH_NUMBER,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
INITIATOR_ID,
FROM_LE_ID,
FROM_LEDGER_ID,
CONTROL_TOTAL,
RUNNING_TOTAL_CR,
RUNNING_TOTAL_DR,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
DESCRIPTION,
TRX_TYPE_ID,
TRX_TYPE_CODE,
BATCH_DATE,
REJECT_ALLOW_FLAG,
ORIGINAL_BATCH_ID,
REVERSED_BATCH_ID,
FROM_RECURRING_BATCH_ID,
INITIATOR_SOURCE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
AUTO_PRORATION_FLAG
FROM FUN_TRX_BATCHES
WHERE BATCH_ID = l_partial_batch.BATCH_ID;
SELECT FUN_TRX_HEADERS_S.nextval INTO l_header_trx_id FROM DUAL;
SELECT FUN_TRX_LINES_S.nextval INTO l_line_id FROM DUAL;
INSERT INTO FUN_TRX_HEADERS
(TRX_ID,
BATCH_ID,
STATUS,
INIT_WF_KEY,
RECI_WF_KEY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRX_NUMBER,
INITIATOR_ID,
RECIPIENT_ID,
TO_LE_ID,
TO_LEDGER_ID,
INIT_AMOUNT_CR,
INIT_AMOUNT_DR,
RECI_AMOUNT_CR,
RECI_AMOUNT_DR,
AR_INVOICE_NUMBER,
INVOICE_FLAG,
APPROVER_ID,
APPROVAL_DATE,
ORIGINAL_TRX_ID,
REVERSED_TRX_ID,
FROM_RECURRING_TRX_ID,
INITIATOR_INSTANCE_FLAG,
RECIPIENT_INSTANCE_FLAG,
REJECT_REASON,
DESCRIPTION,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY)
SELECT l_header_trx_id,
l_batch_id,
'NEW',
NULL,
NULL,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
TRX_NUMBER,
INITIATOR_ID,
RECIPIENT_ID,
TO_LE_ID,
TO_LEDGER_ID,
INIT_AMOUNT_CR,
INIT_AMOUNT_DR,
RECI_AMOUNT_CR,
RECI_AMOUNT_DR,
AR_INVOICE_NUMBER,
INVOICE_FLAG,
APPROVER_ID,
APPROVAL_DATE,
ORIGINAL_TRX_ID,
REVERSED_TRX_ID,
FROM_RECURRING_TRX_ID,
INITIATOR_INSTANCE_FLAG,
RECIPIENT_INSTANCE_FLAG,
REJECT_REASON,
DESCRIPTION,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY
FROM FUN_TRX_HEADERS
WHERE TRX_ID = l_trx_id.TRX_ID;
SELECT language_code INTO icx_language FROM fnd_languages WHERE NLS_LANGUAGE=icx_language;
'SELECT
atth.ROWID,
atth.ATTACHED_DOCUMENT_ID,
atth.DOCUMENT_ID,
atth.SEQ_NUM,
atth.ENTITY_NAME,
atth.COLUMN1,
atth.PK1_VALUE,
atth.PK2_VALUE,
atth.PK3_VALUE,
atth.PK4_VALUE,
atth.PK5_VALUE,
atth.AUTOMATICALLY_ADDED_FLAG,
atth.REQUEST_ID,
atth.PROGRAM_APPLICATION_ID,
atth.PROGRAM_ID,
atth.PROGRAM_UPDATE_DATE,
atth.ATTRIBUTE_CATEGORY,
atth.ATTRIBUTE1,
atth.ATTRIBUTE2,
atth.ATTRIBUTE3,
atth.ATTRIBUTE4,
atth.ATTRIBUTE5,
atth.ATTRIBUTE6,
atth.ATTRIBUTE7,
atth.ATTRIBUTE8,
atth.ATTRIBUTE9,
atth.ATTRIBUTE10,
atth.ATTRIBUTE11,
atth.ATTRIBUTE12,
atth.ATTRIBUTE13,
atth.ATTRIBUTE14,
atth.ATTRIBUTE15,
docs.DATATYPE_ID,
docs.CATEGORY_ID,
docs.SECURITY_TYPE,
docs.SECURITY_ID,
docs.PUBLISH_FLAG,
docs.IMAGE_TYPE,
docs.STORAGE_TYPE,
docs.USAGE_TYPE,
docs.START_DATE_ACTIVE,
docs.END_DATE_ACTIVE,
tl.LANGUAGE,
tl.DESCRIPTION,
docs.FILE_NAME,
docs.MEDIA_ID,
tl.DOC_ATTRIBUTE_CATEGORY,
tl.DOC_ATTRIBUTE1,
tl.DOC_ATTRIBUTE2,
tl.DOC_ATTRIBUTE3,
tl.DOC_ATTRIBUTE4,
tl.DOC_ATTRIBUTE5,
tl.DOC_ATTRIBUTE6,
tl.DOC_ATTRIBUTE7,
tl.DOC_ATTRIBUTE8,
tl.DOC_ATTRIBUTE9,
tl.DOC_ATTRIBUTE10,
tl.DOC_ATTRIBUTE11,
tl.DOC_ATTRIBUTE12,
tl.DOC_ATTRIBUTE13,
tl.DOC_ATTRIBUTE14,
tl.DOC_ATTRIBUTE15,
docs.URL,
tl.TITLE
from fnd_documents docs,
FND_ATTACHED_DOCUMENTS atth,
fnd_documents_tl tl
where entity_name = ''FUN_TRX_HEADERS''
and tl.LANGUAGE=''' || icx_language || '''
and pk1_value=''' || l_trx_id.TRX_ID || '''
and docs.document_id = atth.document_id
and tl.document_id = atth.document_id';
fnd_attached_documents_pkg.Update_Row(
X_Rowid => l_attchmt_rec_type.ROWID,
X_attached_document_id => l_attchmt_rec_type.ATTACHED_DOCUMENT_ID,
X_document_id => l_attchmt_rec_type.DOCUMENT_ID,
X_last_update_date => sysdate,
X_last_updated_by => FND_GLOBAL.USER_ID,
X_last_update_login => FND_GLOBAL.USER_ID,
X_seq_num => l_attchmt_rec_type.SEQ_NUM,
X_entity_name => l_attchmt_rec_type.ENTITY_NAME,
X_column1 => l_attchmt_rec_type.COLUMN1,
X_pk1_value => l_header_trx_id,
X_pk2_value => l_attchmt_rec_type.PK2_VALUE,
X_pk3_value => l_attchmt_rec_type.PK3_VALUE,
X_pk4_value => l_attchmt_rec_type.PK4_VALUE,
X_pk5_value => l_attchmt_rec_type.PK5_VALUE,
X_automatically_added_flag => l_attchmt_rec_type.AUTOMATICALLY_ADDED_FLAG,
X_request_id => l_attchmt_rec_type.REQUEST_ID,
X_program_application_id => l_attchmt_rec_type.PROGRAM_APPLICATION_ID,
X_program_id => l_attchmt_rec_type.PROGRAM_ID,
X_program_update_date => l_attchmt_rec_type.PROGRAM_UPDATE_DATE,
X_Attribute_Category => l_attchmt_rec_type.ATTRIBUTE_CATEGORY,
X_Attribute1 => l_attchmt_rec_type.ATTRIBUTE1,
X_Attribute2 => l_attchmt_rec_type.ATTRIBUTE2,
X_Attribute3 => l_attchmt_rec_type.ATTRIBUTE3,
X_Attribute4 => l_attchmt_rec_type.ATTRIBUTE4,
X_Attribute5 => l_attchmt_rec_type.ATTRIBUTE5,
X_Attribute6 => l_attchmt_rec_type.ATTRIBUTE6,
X_Attribute7 => l_attchmt_rec_type.ATTRIBUTE7,
X_Attribute8 => l_attchmt_rec_type.ATTRIBUTE8,
X_Attribute9 => l_attchmt_rec_type.ATTRIBUTE9,
X_Attribute10 => l_attchmt_rec_type.ATTRIBUTE10,
X_Attribute11 => l_attchmt_rec_type.ATTRIBUTE11,
X_Attribute12 => l_attchmt_rec_type.ATTRIBUTE12,
X_Attribute13 => l_attchmt_rec_type.ATTRIBUTE13,
X_Attribute14 => l_attchmt_rec_type.ATTRIBUTE14,
X_Attribute15 => l_attchmt_rec_type.ATTRIBUTE15,
/* columns necessary for creating a document on the fly */
X_datatype_id => l_attchmt_rec_type.DATATYPE_ID,
X_category_id => l_attchmt_rec_type.CATEGORY_ID,
X_security_type => l_attchmt_rec_type.SECURITY_TYPE,
X_security_id => l_attchmt_rec_type.SECURITY_ID,
X_publish_flag => l_attchmt_rec_type.PUBLISH_FLAG,
X_image_type => l_attchmt_rec_type.IMAGE_TYPE,
X_storage_type => l_attchmt_rec_type.STORAGE_TYPE,
X_usage_type => l_attchmt_rec_type.USAGE_TYPE,
X_start_date_active => l_attchmt_rec_type.START_DATE_ACTIVE,
X_end_date_active => l_attchmt_rec_type.END_DATE_ACTIVE,
X_language => l_attchmt_rec_type.l_LANGUAGE,
X_description => l_attchmt_rec_type.DESCRIPTION,
X_file_name => l_attchmt_rec_type.FILE_NAME,
X_media_id => l_attchmt_rec_type.MEDIA_ID,
X_doc_Attribute_Category => l_attchmt_rec_type.DOC_ATTRIBUTE_CATEGORY,
X_doc_Attribute1 => l_attchmt_rec_type.DOC_ATTRIBUTE1,
X_doc_Attribute2 => l_attchmt_rec_type.DOC_ATTRIBUTE2,
X_doc_Attribute3 => l_attchmt_rec_type.DOC_ATTRIBUTE3,
X_doc_Attribute4 => l_attchmt_rec_type.DOC_ATTRIBUTE4,
X_doc_Attribute5 => l_attchmt_rec_type.DOC_ATTRIBUTE5,
X_doc_Attribute6 => l_attchmt_rec_type.DOC_ATTRIBUTE6,
X_doc_Attribute7 => l_attchmt_rec_type.DOC_ATTRIBUTE7,
X_doc_Attribute8 => l_attchmt_rec_type.DOC_ATTRIBUTE8,
X_doc_Attribute9 => l_attchmt_rec_type.DOC_ATTRIBUTE9,
X_doc_Attribute10 => l_attchmt_rec_type.DOC_ATTRIBUTE10,
X_doc_Attribute11 => l_attchmt_rec_type.DOC_ATTRIBUTE11,
X_doc_Attribute12 => l_attchmt_rec_type.DOC_ATTRIBUTE12,
X_doc_Attribute13 => l_attchmt_rec_type.DOC_ATTRIBUTE13,
X_doc_Attribute14 => l_attchmt_rec_type.DOC_ATTRIBUTE14,
X_doc_Attribute15 => l_attchmt_rec_type.DOC_ATTRIBUTE15,
X_url => l_attchmt_rec_type.URL,
X_title => l_attchmt_rec_type.TITLE
);
INSERT INTO FUN_TRX_LINES
(LINE_ID,
TRX_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LINE_NUMBER,
LINE_TYPE_FLAG,
INIT_AMOUNT_CR,
INIT_AMOUNT_DR,
RECI_AMOUNT_CR,
RECI_AMOUNT_DR,
DESCRIPTION)
SELECT l_line_id,
l_header_trx_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
LINE_NUMBER,
LINE_TYPE_FLAG,
INIT_AMOUNT_CR,
INIT_AMOUNT_DR,
RECI_AMOUNT_CR,
RECI_AMOUNT_DR,
DESCRIPTION
FROM FUN_TRX_LINES
WHERE TRX_ID = l_trx_id.TRX_ID;
INSERT INTO FUN_DIST_LINES
(TRX_ID,
DIST_ID,
LINE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DIST_NUMBER,
PARTY_ID,
PARTY_TYPE_FLAG,
DIST_TYPE_FLAG,
BATCH_DIST_ID,
AMOUNT_CR,
AMOUNT_DR,
CCID,
DESCRIPTION,
AUTO_GENERATE_FLAG,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY)
SELECT l_header_trx_id,
FUN_DIST_LINES_S.nextval,
l_line_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
DIST_NUMBER,
PARTY_ID,
PARTY_TYPE_FLAG,
DIST_TYPE_FLAG,
BATCH_DIST_ID,
AMOUNT_CR,
AMOUNT_DR,
CCID,
DESCRIPTION,
AUTO_GENERATE_FLAG,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY
FROM FUN_DIST_LINES
WHERE TRX_ID = l_trx_id.TRX_ID;
'SELECT
atth.ROWID,
atth.creation_date,
atth.created_by,
atth.SEQ_NUM,
atth.ENTITY_NAME,
atth.COLUMN1,
atth.PK1_VALUE,
atth.PK2_VALUE,
atth.PK3_VALUE,
atth.PK4_VALUE,
atth.PK5_VALUE,
atth.AUTOMATICALLY_ADDED_FLAG,
atth.REQUEST_ID,
atth.PROGRAM_APPLICATION_ID,
atth.PROGRAM_ID,
atth.PROGRAM_UPDATE_DATE,
atth.ATTRIBUTE_CATEGORY,
atth.ATTRIBUTE1,
atth.ATTRIBUTE2,
atth.ATTRIBUTE3,
atth.ATTRIBUTE4,
atth.ATTRIBUTE5,
atth.ATTRIBUTE6,
atth.ATTRIBUTE7,
atth.ATTRIBUTE8,
atth.ATTRIBUTE9,
atth.ATTRIBUTE10,
atth.ATTRIBUTE11,
atth.ATTRIBUTE12,
atth.ATTRIBUTE13,
atth.ATTRIBUTE14,
atth.ATTRIBUTE15,
docs.DATATYPE_ID,
docs.CATEGORY_ID,
docs.SECURITY_TYPE,
docs.SECURITY_ID,
docs.PUBLISH_FLAG,
docs.IMAGE_TYPE,
docs.STORAGE_TYPE,
docs.USAGE_TYPE,
docs.START_DATE_ACTIVE,
docs.END_DATE_ACTIVE,
tl.LANGUAGE,
tl.DESCRIPTION,
docs.FILE_NAME,
docs.MEDIA_ID,
tl.DOC_ATTRIBUTE_CATEGORY,
tl.DOC_ATTRIBUTE1,
tl.DOC_ATTRIBUTE2,
tl.DOC_ATTRIBUTE3,
tl.DOC_ATTRIBUTE4,
tl.DOC_ATTRIBUTE5,
tl.DOC_ATTRIBUTE6,
tl.DOC_ATTRIBUTE7,
tl.DOC_ATTRIBUTE8,
tl.DOC_ATTRIBUTE9,
tl.DOC_ATTRIBUTE10,
tl.DOC_ATTRIBUTE11,
tl.DOC_ATTRIBUTE12,
tl.DOC_ATTRIBUTE13,
tl.DOC_ATTRIBUTE14,
tl.DOC_ATTRIBUTE15,
docs.URL,
tl.TITLE
from fnd_documents docs,
FND_ATTACHED_DOCUMENTS atth,
fnd_documents_tl tl
where entity_name = ''FUN_TRX_BATCHES''
and tl.LANGUAGE=''' || icx_language || '''
and pk1_value=''' || l_partial_batch.BATCH_ID || '''
and docs.document_id = atth.document_id
and tl.document_id = atth.document_id';
select FND_ATTACHED_DOCUMENTS_S.nextval into l_batch_ATTACHED_DOCUMENT_ID from dual;
fnd_attached_documents_pkg.Insert_Row(
X_Rowid => l_batch_attchmt_rec_type.ROWID,
X_attached_document_id => l_batch_ATTACHED_DOCUMENT_ID,
X_document_id => l_batch_DOCUMENT_ID,
X_creation_date => l_batch_attchmt_rec_type.creation_date,
X_created_by => l_batch_attchmt_rec_type.created_by,
X_last_update_date => sysdate,
X_last_updated_by => FND_GLOBAL.USER_ID,
X_last_update_login => FND_GLOBAL.USER_ID,
X_seq_num => l_batch_attchmt_rec_type.SEQ_NUM,
X_entity_name => l_batch_attchmt_rec_type.ENTITY_NAME,
X_column1 => l_batch_attchmt_rec_type.COLUMN1,
X_pk1_value => l_batch_id,
X_pk2_value => l_batch_attchmt_rec_type.PK2_VALUE,
X_pk3_value => l_batch_attchmt_rec_type.PK3_VALUE,
X_pk4_value => l_batch_attchmt_rec_type.PK4_VALUE,
X_pk5_value => l_batch_attchmt_rec_type.PK5_VALUE,
X_automatically_added_flag => l_batch_attchmt_rec_type.AUTOMATICALLY_ADDED_FLAG,
X_request_id => l_batch_attchmt_rec_type.REQUEST_ID,
X_program_application_id => l_batch_attchmt_rec_type.PROGRAM_APPLICATION_ID,
X_program_id => l_batch_attchmt_rec_type.PROGRAM_ID,
X_program_update_date => l_batch_attchmt_rec_type.PROGRAM_UPDATE_DATE,
X_Attribute_Category => l_batch_attchmt_rec_type.ATTRIBUTE_CATEGORY,
X_Attribute1 => l_batch_attchmt_rec_type.ATTRIBUTE1,
X_Attribute2 => l_batch_attchmt_rec_type.ATTRIBUTE2,
X_Attribute3 => l_batch_attchmt_rec_type.ATTRIBUTE3,
X_Attribute4 => l_batch_attchmt_rec_type.ATTRIBUTE4,
X_Attribute5 => l_batch_attchmt_rec_type.ATTRIBUTE5,
X_Attribute6 => l_batch_attchmt_rec_type.ATTRIBUTE6,
X_Attribute7 => l_batch_attchmt_rec_type.ATTRIBUTE7,
X_Attribute8 => l_batch_attchmt_rec_type.ATTRIBUTE8,
X_Attribute9 => l_batch_attchmt_rec_type.ATTRIBUTE9,
X_Attribute10 => l_batch_attchmt_rec_type.ATTRIBUTE10,
X_Attribute11 => l_batch_attchmt_rec_type.ATTRIBUTE11,
X_Attribute12 => l_batch_attchmt_rec_type.ATTRIBUTE12,
X_Attribute13 => l_batch_attchmt_rec_type.ATTRIBUTE13,
X_Attribute14 => l_batch_attchmt_rec_type.ATTRIBUTE14,
X_Attribute15 => l_batch_attchmt_rec_type.ATTRIBUTE15,
/* columns necessary for creating a document on the fly */
X_datatype_id => l_batch_attchmt_rec_type.DATATYPE_ID,
X_category_id => l_batch_attchmt_rec_type.CATEGORY_ID,
X_security_type => l_batch_attchmt_rec_type.SECURITY_TYPE,
X_security_id => l_batch_attchmt_rec_type.SECURITY_ID,
X_publish_flag => l_batch_attchmt_rec_type.PUBLISH_FLAG,
X_image_type => l_batch_attchmt_rec_type.IMAGE_TYPE,
X_storage_type => l_batch_attchmt_rec_type.STORAGE_TYPE,
X_usage_type => l_batch_attchmt_rec_type.USAGE_TYPE,
X_language => l_batch_attchmt_rec_type.l_LANGUAGE,
X_description => l_batch_attchmt_rec_type.DESCRIPTION,
X_file_name => l_batch_attchmt_rec_type.FILE_NAME,
X_media_id => l_batch_attchmt_rec_type.MEDIA_ID,
X_doc_Attribute_Category => l_batch_attchmt_rec_type.DOC_ATTRIBUTE_CATEGORY,
X_doc_Attribute1 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE1,
X_doc_Attribute2 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE2,
X_doc_Attribute3 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE3,
X_doc_Attribute4 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE4,
X_doc_Attribute5 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE5,
X_doc_Attribute6 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE6,
X_doc_Attribute7 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE7,
X_doc_Attribute8 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE8,
X_doc_Attribute9 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE9,
X_doc_Attribute10 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE10,
X_doc_Attribute11 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE11,
X_doc_Attribute12 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE12,
X_doc_Attribute13 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE13,
X_doc_Attribute14 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE14,
X_doc_Attribute15 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE15,
X_create_doc => 'N',
X_url => l_batch_attchmt_rec_type.URL,
X_title => l_batch_attchmt_rec_type.TITLE
);
--Update Total of the newly created batch
SELECT SUM(INIT_AMOUNT_DR),
SUM (INIT_AMOUNT_CR)
INTO l_running_total_dr, l_running_total_cr
FROM FUN_TRX_HEADERS
WHERE BATCH_ID = l_batch_id;
UPDATE FUN_TRX_BATCHES
SET RUNNING_TOTAL_DR = l_running_total_dr,
RUNNING_TOTAL_CR = l_running_total_cr
WHERE BATCH_ID = l_batch_id;
-- Update status of old transactions
UPDATE FUN_TRX_HEADERS
SET STATUS = 'REJECTED',
REJECT_REASON = 'Swept to New Batch ' || l_batch_number,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE BATCH_ID = l_partial_batch.BATCH_ID
AND STATUS IN ('SENT','ERROR','RECEIVED');
-- Update status of old batch to complete
UPDATE FUN_TRX_BATCHES
SET STATUS = DECODE ((SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 'X' FROM FUN_TRX_HEADERS
WHERE BATCH_ID = l_partial_batch.BATCH_ID
AND STATUS NOT IN ('COMPLETE', 'REJECTED'))), 1, 'SENT', 'COMPLETE'),
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = fnd_global.login_id
WHERE BATCH_ID = l_partial_batch.BATCH_ID;
Select 1 from dual where exists
(Select 'X' from
fun_period_statuses fps,fun_system_options fso where p_sweep_GL_date
>= fps.start_date and p_sweep_GL_date <= fps.end_date and
status = 'O' and trx_type_id = l_trx_type_id
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
Select 1 from dual where exists
(Select 'X' from
fun_period_statuses fps,fun_system_options fso where period_name = l_prd_name and
trx_type_id = l_trx_type_id and status IN ('O','S')
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
Select 1 from dual where exists
(Select 'X'
from
fun_period_statuses
fps1, fun_period_statuses fps2,fun_system_options fso where p_sweep_GL_date
>= fps1.start_date and p_sweep_GL_date <= fps1.end_date and
fps1.status = 'O' and fps2.period_name = l_prd_name and fps2.status = 'S'
and fps1.trx_type_id = fps2.trx_type_id
AND fps1.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps1.inteco_period_type=nvl(fso.inteco_period_type,'~~')
AND fps2.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps2.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
Select 1 from dual where exists
(Select 'X'
from
fun_period_statuses fps,fun_system_options fso where period_name = l_prd_name and status = 'O'
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
Update fun_period_statuses set status = 'O' where
trx_type_id = p_trx_type_id and period_name = p_period_name;
/* Update period to Sweep In Progress */
Update fun_period_statuses set status = 'S' where
trx_type_id = p_trx_type_id and period_name = p_period_name
AND (inteco_calendar,inteco_period_type) IN
(SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
Update fun_period_statuses set status = 'O' where
trx_type_id = p_trx_type_id and period_name = p_period_name;
/* Update the Periods */
Update fun_trx_batches set gl_date =
p_sweep_GL_date Where gl_date >= (select start_date from
fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
fps.trx_type_id = p_trx_type_id
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')) and gl_Date <=
(select end_date from fun_period_statuses fps,fun_system_options fso where fps.period_name
= p_period_name and fps.trx_type_id = p_trx_type_id
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
And trx_type_id = p_trx_type_id
And batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2 WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED') AND h2.batch_id = batch_id)
and status NOT IN ('COMPLETE', 'NEW'); -- Bug No : 6880343
/* Update the Status of Period as Closed */
if (nvl(p_close,'Y') = 'Y') then
Update fun_period_statuses set status = 'C' where
trx_type_id = p_trx_type_id and period_name = p_period_name
AND (inteco_calendar,inteco_period_type) IN (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') from fun_system_options);
/* Update period to Sweep In Progress */
Update fun_period_statuses set status = 'S' where
period_name = p_period_name and status = ('O');
Update fun_trx_batches set gl_date = p_sweep_GL_date
Where gl_date >= (select distinct start_date from
fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
fps.trx_type_id = p_trx_type_id
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
and gl_Date <= (select distinct end_date from fun_period_statuses fps,fun_system_options fso
where fps.period_name = p_period_name and
fps.trx_type_id = p_trx_type_id
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
AND trx_type_id IN (select trx_type_id from fun_period_statuses fps,fun_system_options fso
where fps.period_name = p_period_name and
fps.trx_type_id = p_trx_type_id
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
And batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2 WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED') AND h2.batch_id = batch_id)
and status NOT IN ('COMPLETE', 'NEW'); -- Bug No : 6880343
Update fun_period_statuses set status = 'C' where period_name =
p_period_name and status in ('S')
AND (inteco_calendar,inteco_period_type) IN (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
PROCEDURE insert_details_for_years (p_per_year number , p_per_type varchar2 , p_per_set_name varchar2,p_trx_type_id number)
AS
BEGIN
INSERT INTO fun_period_statuses
(PERIOD_NAME
, PERIOD_YEAR
, START_DATE
, END_DATE
,YEAR_START_DATE
,QUARTER_START_DATE
,STATUS
,TRX_TYPE_ID
,PERIOD_NUM
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,CREATION_DATE
,INTECO_CALENDAR
,INTECO_PERIOD_TYPE
) SELECT period_name ,period_year, START_DATE
, END_DATE
,YEAR_START_DATE
,QUARTER_START_DATE
,'N'
,p_trx_type_id
,PERIOD_NUM
,FND_GLOBAL.LOGIN_ID
,FND_GLOBAL.LOGIN_ID
,SYSDATE
,FND_GLOBAL.LOGIN_ID
,sysdate
,inteco_calendar
,inteco_period_type
from gl_periods,fun_system_options
where period_set_name = p_per_set_name and period_type = p_per_type and
period_year >p_per_year;
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 )
AS
BEGIN
INSERT INTO fun_period_statuses
(PERIOD_NAME
, PERIOD_YEAR
, START_DATE
, END_DATE
,YEAR_START_DATE
,QUARTER_START_DATE
,STATUS
,TRX_TYPE_ID
,PERIOD_NUM
,CREATED_BY
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,CREATION_DATE
,INTECO_CALENDAR
,INTECO_PERIOD_TYPE
) SELECT period_name ,period_year, START_DATE
, END_DATE
,YEAR_START_DATE
,QUARTER_START_DATE
,'N'
,p_trx_type_id
,PERIOD_NUM
,FND_GLOBAL.LOGIN_ID
,FND_GLOBAL.LOGIN_ID
,SYSDATE
,FND_GLOBAL.LOGIN_ID
,sysdate
,INTECO_CALENDAR
,INTECO_PERIOD_TYPE
from gl_periods,
fun_system_options
where period_set_name = p_per_set_name and period_type = p_per_type and
period_year =p_per_year and period_num > p_period_num ;
select min(period_year) from gl_periods gl, fun_system_options fun
where
gl.period_set_name = fun.inteco_calendar
and gl.period_type = fun.inteco_period_type ;
select max(period_year) from gl_periods gl, fun_system_options fun
where
gl.period_set_name = fun.inteco_calendar
and gl.period_type = fun.inteco_period_type ;
select max(period_num) from gl_periods gl , fun_system_options fun
where
gl.period_set_name = fun.inteco_calendar
and gl.period_type = fun.inteco_period_type
and gl.period_year = c_per_year;
select max(period_year) period_year,trx_type_id from fun_period_statuses fps, fun_system_options fso
WHERE fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')
group by trx_type_id ;
select max(period_num) from fun_period_statuses fps,fun_system_options fso
where period_year = c_per_yr
and trx_type_id = c_trx_type
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~');
select trx_type_id from fun_trx_types_vl v
where not exists
( select fps.trx_type_id from fun_period_statuses fps,fun_system_options fso
WHERE fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~') and fps.period_year = c_per_year
and fps.period_name = c_per_name
and fps.trx_type_id = v.trx_type_id);
SELECT inteco_calendar,inteco_period_type into l_sys_opt_cal_name , l_sys_opt_per_type from fun_system_options;
/***** Call procedure to insert for the year passed insert_details_for_years ****/
--Bug: 6512412. Passing l_min_gl_year-1 instead of l_min_gl_year.
insert_details_for_years (p_per_year => (l_min_gl_year-1),
p_per_type => l_sys_opt_per_type,
p_per_set_name =>l_sys_opt_cal_name ,p_trx_type_id => l_inexist_fun_trx.trx_type_id);
select PERIOD_NUM, period_name
into l_PERIOD_NUM, l_period_name
from
(select min(fps.PERIOD_NUM) PERIOD_NUM, fps.period_name from fun_period_statuses fps, fun_system_options fso
where fps.period_year = l_max_fun_year
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')
group by fps.period_name
order by 1 asc )
where rownum = 1;
/***** Call procedure to insert for the year passed insert_details_for_years ****/
--Bug: 6512412. Passing l_min_gl_year-1 instead of l_min_gl_year.
insert_details_for_years (p_per_year => (l_min_gl_year-1),
p_per_type => l_sys_opt_per_type,
p_per_set_name =>l_sys_opt_cal_name ,p_trx_type_id => l_inexist_fun_trx.trx_type_id);
/***** Call procedure to insert for the year passed insert_details_for_years****/
insert_details_for_periods (p_per_year => (l_gl_year), -- bug 6761607.
p_per_type => l_sys_opt_per_type,
p_per_set_name =>l_sys_opt_cal_name ,
p_period_num => l_fun_num, p_trx_type_id => l_max_fun_per_yr.trx_type_id);
/***** Call procedure to insert for the year passed insert_details_for_periods ****/
insert_details_for_periods (p_per_year => l_max_fun_per_yr.period_year,
p_per_type => l_sys_opt_per_type,
p_per_set_name =>l_sys_opt_cal_name ,
p_period_num => l_fun_num, p_trx_type_id => l_max_fun_per_yr.trx_type_id);
/***** Call procedure to insert for the year passed insert_details_for_years ****/
-- bug 6761607.
/*insert_details_for_years (p_per_year => l_max_fun_per_yr.period_year,
p_per_type => l_sys_opt_per_type,
p_per_set_name =>l_sys_opt_cal_name ,p_trx_type_id => l_max_fun_per_yr.trx_type_id); */
/***** Call procedure to insert for the year passed insert_details_for_years ****/
insert_details_for_years (p_per_year => l_max_fun_per_yr.period_year,
p_per_type => l_sys_opt_per_type,
p_per_set_name =>l_sys_opt_cal_name ,p_trx_type_id => l_max_fun_per_yr.trx_type_id);
SELECT fso.inteco_calendar,fso.inteco_period_type
FROM fun_system_options fso;
CURSOR c_select_status IS
select fps.status
from fun_period_statuses fps,fun_system_options fso,
fun_trx_types_vl ftt
where trunc(p_date) between fps.start_date and fps.end_date
and fps.trx_type_id = ftt.trx_type_id
and ftt.trx_type_id = p_trx_type_id
AND fps.status <> 'N'
AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~');
OPEN c_select_status;
FETCH c_select_status into l_status;
if c_select_status%notfound then
l_status :='C';
CLOSE c_select_status;