[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ftb.batch_number,
ftb.gl_date,
ftb.batch_date,
fth.initiator_id,
fth.recipient_id,
fth.invoice_flag,
-- decode(fth.init_amount_cr,0, fth.init_amount_dr, NULL, fth.init_amount_dr, fth.init_amount_cr),
-- Bug No. 6854675 Changed the select statement to fetch a numeric value
--ltrim(to_char(decode(nvl(ftb.running_total_cr,0),0,ftb.running_total_dr,ftb.running_total_cr),'999999999.99')),
decode(nvl(ftb.running_total_cr,0),0,ftb.running_total_dr,ftb.running_total_cr),
ftb.currency_code,
hzp.party_name ,
fth.trx_number,
ini.party_name
INTO l_batch_number,
l_gl_date,
l_batch_date,
l_initiator_id,
l_recipient_id,
l_invoice_flag,
l_trx_amt,
l_currency,
l_recipient_name,
l_trx_number,
l_initiator_name
FROM fun_trx_batches ftb,
fun_trx_headers fth,
hz_parties hzp,
hz_parties ini
WHERE ftb.batch_id = l_batch_id
AND ftb.batch_id=fth.batch_id
AND fth.recipient_id=hzp.party_id
AND fth.initiator_id=ini.party_id
AND fth.trx_id=l_trx_id;
SELECT p.PARTY_NAME PERSON_NAME
INTO l_initiator_person
FROM hz_parties p,
fnd_user fu,
fun_trx_batches b
WHERE fu.person_party_id = p.party_id
AND fu.user_id = b.created_by
AND b.batch_id = l_batch_id;
| PROCEDURE UPDATE_STATUS |
|------------------------------------------------------|
| Parameters item_type IN Varchar2 |
| item_key IN Varchar2 |
| act_id IN NUMBER |
| funcmode IN Varchar2 |
| resultout IN Varchar2 |
| |
|------------------------------------------------------|
| Description |
| Update the intercompany transaction |
| status |
| |
| |
| |
| |
|-----------------------------------------------------*/
PROCEDURE UPDATE_STATUS ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2)
IS
-- Local variables
l_trx_id NUMBER;
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 => l_status);
WF_CORE.Context('FUN_INITIATOR_WF_PKG', 'UPDATE_STATUS',
itemtype, itemkey, actid, funcmode);
END UPDATE_STATUS;
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_number,
ftb.initiator_id,
ftb.from_le_id,
ftb.from_ledger_id,
fth.recipient_id,
fth.to_le_id,
ftb.trx_type_id,
-- fth.ledger_id,
ftb.exchange_rate_type,
ftb.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
WHERE fth.trx_id=p_trx_id
AND fth.batch_id=ftb.batch_id
AND fth.status='APPROVED';
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 fth.trx_id=p_trx_id
AND ftl.trx_id=fth.trx_id
AND ftl.line_id=fdl.line_id
AND fdl.party_type_flag='I';
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 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_ar_memo_line_id,
l_ar_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'
'Y',
--'S',
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
);
'Number of Lines: After Insert' || l_count || 'ORG:' || l_line.org_id || 'Test2.75');
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
);
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 LTRIM(TO_CHAR(DECODE(NVL(H.RECI_AMOUNT_CR,0),0,H.RECI_AMOUNT_DR,
H.RECI_AMOUNT_CR),
'999999999D99'))
INTO l_trx_amt
FROM FUN_TRX_HEADERS H
WHERE H.TRX_ID = l_trx_id;
SELECT rct.trx_number invoice_number,
rct.interface_header_attribute1 batch_id,
rct.interface_header_attribute2 trx_id
FROM ra_customer_trx_all rct,
ra_batch_sources_all rbs
WHERE rct.request_id=p_request_id
AND rct.batch_source_id=rbs.batch_source_id
AND NVL(rct.org_id, -99) =NVL(rbs.org_id, -99)
AND rbs.name=(SELECT name FROM
RA_BATCH_SOURCES_ALL WHERE BATCH_SOURCE_ID = 22 AND org_id = rct.org_id);
UPDATE FUN_TRX_HEADERS
SET ar_invoice_number = l_trans.invoice_number
WHERE trx_id = l_trans.trx_id
AND batch_id = l_trans.batch_id
AND ar_invoice_number IS NULL;
l_parameter_list_out.delete();
PROCEDURE update_trx_headers(p_request_id IN NUMBER)
IS
l_trx_header_type fun_trx_header_type;
SELECT TO_NUMBER(RCT.interface_header_attribute1),
TO_NUMBER(RCT.interface_header_attribute2),
RCT.trx_number
BULK COLLECT INTO
l_trx_header_type.attribute1,
l_trx_header_type.attribute2,
l_trx_header_type.invoice_number
FROM RA_CUSTOMER_TRX_ALL RCT,
RA_BATCH_SOURCES_ALL RBS
WHERE RBS.name = (SELECT name FROM
RA_BATCH_SOURCES_ALL WHERE BATCH_SOURCE_ID = 22 AND org_id = rct.org_id)
AND RBS.batch_source_id = RCT.batch_source_id
AND RBS.org_id = RCT.org_id
AND RCT.request_id = p_request_id;
UPDATE FUN_TRX_HEADERS
SET ar_invoice_number = l_trx_header_type.invoice_number(i)
WHERE batch_id = l_trx_header_type.attribute1(i)
AND trx_id = l_trx_header_type.attribute2(i);
END update_trx_headers;
update_trx_headers(l_request_id);