[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
decode(ftl.init_amount_cr,0, ftl.init_amount_dr,
NULL, ftl.init_amount_dr,
(ftl.init_amount_cr * (-1))),
ftl.line_id
FROM FUN_TRX_LINES ftl
WHERE p_trx_id = ftl.trx_id;
SELECT ftb.batch_id,
ftb.batch_number,
fth.trx_id,
fth.trx_number,
ftb.initiator_id,
ftb.from_le_id,
ftb.from_ledger_id,
fth.recipient_id,
fth.to_le_id,
ftb.trx_type_id,
ftb.exchange_rate_type,
ftb.currency_code,
ledgers.currency_code,
ftb.description,
ftb.gl_date,
ftb.batch_id,
fth.trx_id,
ftb.from_ledger_id,
ftb.batch_date
FROM FUN_TRX_BATCHES ftb,
FUN_TRX_HEADERS fth,
GL_LEDGERS ledgers
WHERE fth.batch_id= ftb.batch_id
AND fth.status='APPROVED'
AND ledgers.ledger_id = ftb.from_ledger_id
AND trunc(ftb.gl_date) between trunc(nvl(l_date_low,ftb.gl_date-1))
and trunc(nvl(l_date_high, ftb.gl_date+1))
AND nvl(p_org_id,1) = nvl2(p_org_id,fun_tca_pkg.get_ou_id(ftb.initiator_id),1)
AND ftb.from_le_id = nvl(p_le_id,ftb.from_le_id)
AND fth.invoice_flag = 'Y'
ORDER BY ftb.initiator_id;
SELECT
DECODE(FDL.dist_type_flag, 'L',
decode(fdl.amount_cr,
0, fdl.amount_dr * (-1),
NULL, fdl.amount_dr * (-1),
fdl.amount_cr),
'R', NULL,
NULL),
DECODE(FDL.dist_type_flag, 'L', NULL,
'R', 100,
NULL),
DECODE(FDL.dist_type_flag, 'R', 'REC',
'L', 'REV',
NULL),
fdl.ccid,
fth.batch_id,
fth.trx_id,
ftl.line_id
FROM FUN_TRX_HEADERS fth,
FUN_TRX_LINES ftl,
FUN_DIST_LINES fdl
WHERE ftl.trx_id=fth.trx_id
AND fth.trx_id = p_trx_id
AND ftl.line_id=fdl.line_id
AND fdl.party_type_flag='I';
SELECT COUNT(*)
FROM gl_period_statuses glps
WHERE TRUNC(p_trx_date) BETWEEN glps.start_date AND glps.end_date
AND glps.application_id = 222
AND glps.set_of_books_id = p_ledger_id
AND glps.adjustment_period_flag <> 'Y'
AND glps.closing_status IN ('O','F');
SELECT count(*)
FROM hr_operating_units ou
WHERE organization_id = p_ou_id
AND date_from <= p_trx_date
AND NVL(date_to, p_trx_date) >= p_trx_date;
select PAYMENT_TERM_ID
from HZ_CUST_SITE_USES_ALL
where site_use_code = 'BILL_TO'
and site_use_id = p_site_use_id;
select STANDARD_TERMS
from HZ_CUSTOMER_PROFILES
where cust_account_id = p_cust_acct_id;
select hr.name into l_org_name from hr_operating_units hr
where hr.organization_id = p_org_id;
select xle.name into l_le_name from xle_entity_profiles xle
where xle.legal_entity_id = p_le_id;
SELECT name into l_line.BATCH_SOURCE_NAME FROM
RA_BATCH_SOURCES_ALL WHERE BATCH_SOURCE_ID = 22 AND org_id = l_ou_id;
INSERT INTO RA_INTERFACE_LINES_ALL
(
AMOUNT,
BATCH_SOURCE_NAME,
CONVERSION_TYPE,
CURRENCY_CODE,
CUST_TRX_TYPE_ID,
CUST_TRX_TYPE_NAME,
DESCRIPTION,
GL_DATE,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4,
INTERFACE_LINE_CONTEXT,
LINE_TYPE,
MEMO_LINE_ID,
MEMO_LINE_NAME,
ORG_ID,
ORIG_SYSTEM_BILL_ADDRESS_ID,
ORIG_SYSTEM_BILL_CUSTOMER_ID,
SET_OF_BOOKS_ID,
TRX_DATE,
TAXABLE_FLAG,
TERM_ID,
LEGAL_ENTITY_ID,
SOURCE_EVENT_CLASS_CODE,
PRIMARY_SALESREP_ID
)
VALUES
(
l_line.AMOUNT,
l_line.BATCH_SOURCE_NAME,
l_line.CONVERSION_TYPE,
l_line.CURRENCY_CODE,
l_ar_trx_type_id,
l_ar_trx_type_name,
NVL(l_line.DESCRIPTION,
'Transactions from Global Intercompany'),
l_line.GL_DATE,
l_line.INTERFACE_LINE_ATTRIBUTE1,
l_line.INTERFACE_LINE_ATTRIBUTE2,
l_line.INTERFACE_LINE_ATTRIBUTE3,
l_batch_num,
l_line.INTERFACE_LINE_CONTEXT,
l_line.LINE_TYPE,
l_memo_line_id,
l_memo_line_name,
l_line.ORG_ID,
l_line.ORIG_SYSTEM_BILL_ADDRESS_ID,
l_line.ORIG_SYSTEM_BILL_CUSTOMER_ID,
l_line.SET_OF_BOOKS_ID,
l_line.TRX_DATE,
--Bug 9285035: Changed the value From 'S' to 'Y'
--'S' ,
'Y' ,
l_default_term_id,
l_le_id,
'INTERCOMPANY_TRX',
'-3'
);
INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
(
INTERFACE_LINE_CONTEXT ,
INTERFACE_LINE_ATTRIBUTE1 ,
INTERFACE_LINE_ATTRIBUTE2 ,
INTERFACE_LINE_ATTRIBUTE3 ,
INTERFACE_LINE_ATTRIBUTE4 ,
INTERFACE_LINE_ATTRIBUTE5 ,
INTERFACE_LINE_ATTRIBUTE6 ,
INTERFACE_LINE_ATTRIBUTE7 ,
INTERFACE_LINE_ATTRIBUTE8 ,
INTERFACE_LINE_ATTRIBUTE9 ,
INTERFACE_LINE_ATTRIBUTE10 ,
INTERFACE_LINE_ATTRIBUTE11 ,
INTERFACE_LINE_ATTRIBUTE12 ,
INTERFACE_LINE_ATTRIBUTE13 ,
INTERFACE_LINE_ATTRIBUTE14 ,
INTERFACE_LINE_ATTRIBUTE15,
SALES_CREDIT_PERCENT_SPLIT,
SALES_CREDIT_TYPE_ID,
SALESREP_ID,
ORG_ID
)
VALUES
(
l_line.INTERFACE_LINE_CONTEXT,
l_line.INTERFACE_LINE_ATTRIBUTE1,
l_line.INTERFACE_LINE_ATTRIBUTE2,
l_line.INTERFACE_LINE_ATTRIBUTE3,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'100',
'1',
'-3',
l_line.ORG_ID
);
INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL
(
ACCOUNT_CLASS,
AMOUNT,
percent,
CODE_COMBINATION_ID,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4,
INTERFACE_LINE_CONTEXT,
ORG_ID
)
VALUES
(
l_dist_line.ACCOUNT_CLASS,
l_dist_line.AMOUNT,
l_dist_line.percent,
l_dist_line.CODE_COMBINATION_ID,
l_dist_line.INTERFACE_LINE_ATTRIBUTE1,
l_dist_line.INTERFACE_LINE_ATTRIBUTE2,
l_dist_line.INTERFACE_LINE_ATTRIBUTE3,
l_batch_num,
l_dist_line.INTERFACE_LINE_CONTEXT,
l_dist_line.ORG_ID
);
-- update transaction status
FUN_TRX_PVT.update_trx_status(p_api_version =>1.0,
x_return_status =>l_return_status,
x_msg_count => l_message_count,
x_msg_data => l_message_data,
p_trx_id => l_trx_id,
p_update_status_to => 'XFER_AR');
SELECT name into l_src_name FROM
RA_BATCH_SOURCES_ALL WHERE BATCH_SOURCE_ID = 22 AND org_id = org_id_table(I);