[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(conversion_rate) INTO l_has_rate
FROM gl_daily_rates
WHERE from_currency = p_from_currency AND
to_currency = p_to_currency AND
conversion_type = p_exchange_type AND
conversion_date = p_exchange_date;
SELECT USER_CONVERSION_TYPE
INTO l_user_conversion_type
from GL_DAILY_CONVERSION_TYPES
where conversion_type = p_conversion_type;
l_cur_select VARCHAR2(1200);
select distinct ledger_id
from gl_interface
where user_je_category_name = p_category
and user_je_source_name = p_source
and request_id=p_concreqid;
select_clause VARCHAR2(2500) :='';
insert_clause VARCHAR2(800) :='';
gt_insert_clause VARCHAR2(2500) :='';
select user_je_source_name into l_source from gl_je_sources_tl where
je_source_name = 'Global Intercompany' and language = USERENV('LANG');
select user_je_category_name into l_category from gl_je_categories_tl where
je_category_name = 'Global Intercompany' and language = USERENV('LANG');
l_cur_select := 'SELECT
trxH.status status,
gt.party_type_flag party_type_flag,
Nvl(nvl(decode(glps.closing_status,''O'','''',''F'','''', ''GL_PERIOD_NOT_OPEN''),
decode(FUN_GL_BATCH_TRANSFER.has_valid_conversion_rate(trxB.currency_code,ledgers.currency_code,
trxB.exchange_rate_type,TRUNC(trxB.GL_DATE)),1,'''',0,''FUN_API_CONV_RATE_NOT_FOUND'')),
''FUN_API_TRX_TRANSFERRED'') error_mesg,
gt.trx_id trx_id,
trxH.trx_number trx_number,
trxB.batch_number batch_number,
trxB.batch_id batch_id
from
fun_trx_batches trxB,
fun_trx_headers trxH,
gl_periods periods,
gl_ledgers ledgers,
gl_period_statuses glps,
fun_transfers gt';
select sysdate into l_init_sysdate from dual;
/* Insert data into temp table */
begin
gt_insert_clause :='INSERT INTO fun_transfers(BATCH_ID, trx_id, org_name, org_id, le_id,
ledger_id, party_type_flag,
request_id, trx_status, description)
select gt.BATCH_ID, gt.TRX_ID, P.PARTY_NAME,
GT.PARTY_ID, GT.LE_ID, gt.LEDGER_ID, gt.party_type_flag,
gt.p_request_id,
gt.STATUS, gt.description
from fun_trx_batches trxb, gl_ledgers ledgers,
HZ_PARTIES P,
(
SELECT TB.BATCH_ID , TH.INITIATOR_ID party_id, TH.TRX_ID, ''I''
party_type_flag, '''||p_request_id||''' p_request_id, tB.description Description,
TB.FROM_LE_ID LE_ID, TH.STATUS, tB.from_ledger_id LEDGER_ID
FROM FUN_TRX_HEADERS TH, FUN_TRX_BATCHES TB
WHERE TH.INVOICE_FLAG = ''N''
AND NOT EXISTS ( SELECT TRX_ID FROM
FUN_TRANSFERS FT WHERE FT.TRX_ID = TH.TRX_ID AND
FT.PARTY_TYPE_FLAG = ''I'')
AND TH.STATUS IN (''APPROVED'',''XFER_RECI_GL'')
AND TH.BATCH_ID = TB.BATCH_ID
UNION ALL
SELECT TB.BATCH_ID, TH.RECIPIENT_ID party_id, TH.TRX_ID, ''R''
party_type_flag, '''||p_request_id||''' p_request_id, tH.description Description,
TH.TO_LE_ID LE_ID, TH.STATUS, TH.TO_LEDGER_ID LEDGER_ID
FROM FUN_TRX_HEADERS TH, FUN_TRX_BATCHES TB
WHERE TH.INVOICE_FLAG = ''N''
AND NOT EXISTS ( SELECT TRX_ID FROM
FUN_TRANSFERS FT WHERE FT.TRX_ID = TH.TRX_ID AND
FT.PARTY_TYPE_FLAG = ''R'')
AND TH.STATUS IN (''APPROVED'',''XFER_INI_GL'')
AND TH.BATCH_ID = TB.BATCH_ID
)gt
where trxb.batch_id = gt.batch_id
and ledgers.ledger_id = gt.ledger_id
AND P.PARTY_ID = GT.party_id ';
gt_where_clause := gt_where_clause || ' AND EXISTS (SELECT NULL
FROM XLE_ENTITY_PROFILES
WHERE LEGAL_ENTITY_ID = gt.LE_ID
AND NAME BETWEEN NAME and '''||p_le_high||''')';
gt_where_clause := gt_where_clause || ' AND EXISTS (SELECT NULL
FROM XLE_ENTITY_PROFILES
WHERE LEGAL_ENTITY_ID = gt.LE_ID
AND NAME BETWEEN '''||p_le_low||''' and NAME)';
gt_where_clause := gt_where_clause || ' AND EXISTS (SELECT NULL
FROM XLE_ENTITY_PROFILES
WHERE LEGAL_ENTITY_ID = gt.LE_ID
AND NAME BETWEEN '''||p_le_low||''' and '''||p_le_high||''')';
EXECUTE IMMEDIATE gt_insert_clause||gt_where_clause;
/* Insert data into temp table ends here */
insert_clause := 'INSERT INTO GL_INTERFACE
(STATUS,
GROUP_ID,
SET_OF_BOOKS_ID,
ACCOUNTING_DATE,
CURRENCY_CODE,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
USER_JE_CATEGORY_NAME,
USER_JE_SOURCE_NAME,
CURRENCY_CONVERSION_DATE,
USER_CURRENCY_CONVERSION_TYPE,
ENTERED_DR,
ENTERED_CR,
REFERENCE10,
REFERENCE5,
CODE_COMBINATION_ID,
LEDGER_ID,
REFERENCE21,
REFERENCE22,
REFERENCE23,
REFERENCE24,
REFERENCE25,
PERIOD_NAME,
CHART_OF_ACCOUNTS_ID,
REQUEST_ID,
REFERENCE4)';
select_clause := ' SELECT ''NEW'','''||
p_request_id||''',
LEDGERS.LEDGER_ID,
TRUNC(TRXB.GL_DATE),
TRXB.CURRENCY_CODE,
SYSDATE,
D.CREATED_BY,
''A'','''||
l_category||''','''||
l_source||''',
TRUNC(TRXB.GL_DATE),
FUN_GL_BATCH_TRANSFER.GET_CONVERSION_TYPE(TRXB.EXCHANGE_RATE_TYPE),
D.AMOUNT_DR,
D.AMOUNT_CR,
D.DESCRIPTION,
TRXB.DESCRIPTION,
D.CCID,
LEDGERS.LEDGER_ID,
''Intercompany Transaction'',
TRXB.BATCH_ID,
GT.TRX_ID,
T.LINE_ID,
D.DIST_ID,
PERIODS.PERIOD_NAME,
LEDGERS.CHART_OF_ACCOUNTS_ID,'''||
p_request_id||''',
TRXB.BATCH_NUMBER
FROM
GL_LEDGERS LEDGERS,
GL_PERIOD_STATUSES GLPS,
GL_PERIODS PERIODS,
FUN_TRX_BATCHES TRXB,
FUN_TRX_LINES T,
FUN_DIST_LINES D,
fun_transfers gt
WHERE TRXB.BATCH_ID = GT.BATCH_ID
AND GT.request_id = '''||p_request_id||'''
AND LEDGERS.LEDGER_ID = gt.LEDGER_ID
AND PERIODS.PERIOD_SET_NAME = LEDGERS.PERIOD_SET_NAME
AND TRUNC(TRXB.GL_DATE) BETWEEN PERIODS.START_DATE AND PERIODS.END_DATE
AND PERIODS.ADJUSTMENT_PERIOD_FLAG <> ''Y''
AND GLPS.PERIOD_NAME = PERIODS.PERIOD_NAME
AND GLPS.APPLICATION_ID = 101
AND GLPS.SET_OF_BOOKS_ID = LEDGERS.LEDGER_ID
AND T.TRX_ID = GT.TRX_ID
AND D.TRX_ID = T.TRX_ID
AND D.PARTY_TYPE_FLAG = GT.PARTY_TYPE_FLAG
AND GLPS.CLOSING_STATUS IN (''O'',''F'')
AND FUN_GL_BATCH_TRANSFER.HAS_VALID_CONVERSION_RATE(TRXB.CURRENCY_CODE,LEDGERS.CURRENCY_CODE,
TRXB.EXCHANGE_RATE_TYPE,TRUNC(TRXB.GL_DATE)) = 1';
l_cur_main_query := l_cur_select||l_cur_where;
EXECUTE IMMEDIATE insert_clause || select_clause || where_clause;
FND_FILE.PUT_LINE(FND_FILE.LOG,'INSERT BLOCK: No Data Found');
FND_FILE.PUT_LINE(FND_FILE.LOG,'INSERT BLOCK: Unexpected error:' || sqlcode || sqlerrm);
select gl_journal_import_s.nextval into v_interface_run_id from dual;
insert into gl_interface_control (je_source_name,
status,
set_of_books_id,
group_id,
interface_run_id)
values ( 'Global Intercompany',
'S',
o.ledger_id,
p_request_id, -- Bug No : 7215571
v_interface_run_id);
UPDATE fun_trx_headers
SET status = DECODE (l_party_type_flag_tbl(l_index),
'I',DECODE (status,
'APPROVED','XFER_INI_GL',
'XFER_RECI_GL', 'COMPLETE'),
'R',DECODE (status,
'APPROVED','XFER_RECI_GL',
'XFER_INI_GL', 'COMPLETE'))
WHERE trx_id = l_trx_id_tbl(l_index)
AND l_error_mesg_tbl(l_index) = 'FUN_API_TRX_TRANSFERRED';
UPDATE fun_trx_batches b
SET b.status = 'COMPLETE'
WHERE NOT EXISTS ( SELECT trx_id
FROM fun_trx_headers
WHERE fun_trx_headers.batch_id = b.batch_id
AND status NOT IN ('COMPLETE', 'REJECTED'))
AND b.batch_id = l_batch_id_tbl(l_index)
AND b.status <> 'COMPLETE';
UPDATE fun_trx_batches
SET status = 'COMPLETE'
WHERE status <> 'COMPLETE'
AND NOT EXISTS ( SELECT trx_id
FROM fun_trx_headers
WHERE fun_trx_headers.batch_id = fun_trx_batches.batch_id
AND status NOT IN ('COMPLETE', 'REJECTED'));
DELETE fun_transfers
WHERE REQUEST_ID = P_REQUEST_ID;
DELETE fun_transfers
WHERE REQUEST_ID = P_REQUEST_ID;
DELETE fun_transfers
WHERE REQUEST_ID = P_REQUEST_ID;