[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 = 'Global Intercompany' 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');
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;
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;
--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.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;