[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PARTY_NAME INTO l_party_name from HZ_PARTIES
WHERE PARTY_ID = p_party_id;
SELECT count(*) into l_assignment_count
FROM HZ_PARTIES HZP,
HZ_PARTIES HZP2,
HZ_RELATIONSHIPS HZR,
HZ_ORG_CONTACTS HZC,
HZ_ORG_CONTACT_ROLES HZCR
WHERE HZR.RELATIONSHIP_CODE='CONTACT_OF'
AND HZR.RELATIONSHIP_TYPE='CONTACT'
AND HZR.DIRECTIONAL_FLAG='F'
AND HZR.SUBJECT_TABLE_NAME='HZ_PARTIES'
AND HZR.OBJECT_TABLE_NAME='HZ_PARTIES'
AND HZR.SUBJECT_TYPE='PERSON'
AND HZR.OBJECT_ID=HZP2.PARTY_ID
AND HZR.SUBJECT_ID=HZP.PARTY_ID
AND HZR.OBJECT_ID = p_party_id
AND HZC.PARTY_RELATIONSHIP_ID = HZR.RELATIONSHIP_ID
AND HZCR.ORG_CONTACT_ID = HZC.ORG_CONTACT_ID
AND HZCR.ROLE_TYPE = 'INTERCOMPANY_CONTACT_FOR'
AND FUN_SECURITY.IS_ACCESS_VALID(HZP.PARTY_ID, HZP2.PARTY_ID) = 'Y' -- Access1
AND DECODE(HZR.STATUS,'A','Y','I','N') = 'Y' -- status
AND HZR.ADDITIONAL_INFORMATION1 = 'Y' -- Notification
AND sysdate BETWEEN
nvl(HZR.start_date, sysdate -1)
AND nvl(HZR.end_date, sysdate + 1);
SELECT COUNT(*)
FROM fun_trx_batches
WHERE initiator_id = p_initiator_id
AND batch_number = p_batch_number;
SELECT le.le_effective_from, le.le_effective_to
FROM xle_firstparty_information_v le, hz_parties parties
WHERE parties.party_id = p_party_id
AND EXISTS (SELECT 1
FROM hz_party_usg_assignments hua
WHERE hua.party_id = parties.party_id
AND hua.party_usage_code = 'INTERCOMPANY_ORG')
AND fun_tca_pkg.get_le_id(p_party_id) = le.party_id
AND p_le_id = le.legal_entity_id;
SELECT segment_value
FROM GL_LEDGER_LE_BSV_SPECIFIC_V
WHERE ledger_id = p_ledger_id
AND (legal_entity_id = p_le_id or legal_entity_id is null);
SELECT COUNT(*)
FROM gl_ledger_le_bsv_gt gt
WHERE bal_seg_value = l_bal_seg_value
AND ledger_id = p_ledger_id
AND legal_entity_id = p_le_id;
SELECT bal_seg_value_option_code, bal_seg_column_name, name
INTO l_bsv_val, l_bal_seg_column_name, g_le_name
FROM gl_ledgers
WHERE ledger_id=p_ledger_id;
execute immediate 'SELECT ' || l_bal_seg_column_name ||
' FROM gl_code_combinations WHERE code_combination_id = :1'
INTO l_bal_seg_value
using p_ccid;
Select COUNT(*)
INTO l_count
from GL_LEDGER_LE_BSV_SPECIFIC_V
where segment_value = l_bal_seg_value
and ledger_id = p_ledger_id
and (legal_entity_id = p_le_id or legal_entity_id is null);
SELECT chart_of_accounts_id, bal_seg_column_name, legal_entity_name
INTO g_chart_of_accounts_id, g_bal_seg_column_name, g_le_name
FROM gl_ledger_le_bsv_gt
WHERE ledger_id = p_ledger_id
AND ROWNUM < 2;
l_stmt_str := 'SELECT ' || g_bal_seg_column_name ||
' FROM gl_code_combinations WHERE code_combination_id = ' ||
p_ccid;
SELECT COUNT(*)
FROM fnd_currencies_vl
WHERE currency_code = p_curr_code
AND enabled_flag = 'Y'
AND nvl(start_date_active, p_trx_date) <= nvl(p_trx_date, sysdate)
AND nvl(end_date_active, p_trx_date) >= nvl(p_trx_date, sysdate);
SELECT COUNT(*)
FROM GL_DAILY_RATES_V
WHERE conversion_date = p_trx_date
AND exchange_rate_type = l_conv_type
AND from_currency = p_curr_code
AND to_currency = l_func_curr;
SELECT count(*)
FROM GL_PERIOD_STATUSES PST
WHERE pst.application_id = 435
AND pst.closing_status <> 'N'
AND pst.adjustment_period_flag <> 'Y'
AND pst.ledger_id = p_from_le_id;
SELECT PST.PERIOD_NAME
, PST.PERIOD_YEAR
, PST.PERIOD_NUM
, PST.START_DATE
, PST.END_DATE
FROM GL_PERIOD_STATUSES PST
, AR_SYSTEM_PARAMETERS SP
WHERE PST.SET_OF_BOOKS_ID = SP.SET_OF_BOOKS_ID
AND PST.APPLICATION_ID = 222
AND PST.CLOSING_STATUS <> 'N'
AND PST.ADJUSTMENT_PERIOD_FLAG <> 'Y'
*/
/* Problem: Need to wait for Intercompany period to exist
OPEN period_open_csr;
p_insert IN VARCHAR2 ,
p_batch_rec IN OUT NOCOPY BATCH_REC_TYPE,
p_trx_tbl IN OUT NOCOPY TRX_TBL_TYPE,
p_init_dist_tbl IN OUT NOCOPY INIT_DIST_TBL_TYPE,
p_dist_lines_tbl IN OUT NOCOPY DIST_LINE_TBL_TYPE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'INIT_BATCH_VALIDATE';
IF (nvl(p_insert,FND_API.G_TRUE) = FND_API.G_TRUE) THEN
Is_Batch_Num_Unique(x_return_status => l_return_status,
p_batch_number => p_batch_rec.batch_number,
p_initiator_id => p_batch_rec.initiator_id);
SELECT count(*)
FROM hr_operating_units ou
WHERE organization_id = l_from_ou_id
AND date_from <= p_trx_date
AND NVL(date_to, p_trx_date) >= p_trx_date; --
SELECT legal_entity_id
INTO l_from_le_id
FROM xle_firstparty_information_v
WHERE party_id = l_from_le_party_id;
SELECT party_name
INTO initiator_name
FROM hz_parties
WHERE party_id=p_initiator_id;
SELECT party_name
INTO recipient_name
FROM hz_parties
WHERE party_id=p_recipient_id;
/* SELECT count(initiator_id) INTO l_initiator_id
FROM fun_trx_batches batches
WHERE batch_id = p_batch_id
AND original_batch_id IS NULL
AND reversed_batch_id IS NULL
AND status = 'COMPLETE'
AND NOT EXISTS (SELECT 'Transaction already reversed'
FROM fun_trx_headers hdrs
WHERE hdrs.batch_id = p_batch_id
AND (hdrs.original_trx_id IS NOT NULL
OR
hdrs.reversed_trx_id IS NOT NULL));*/
SELECT count(initiator_id) INTO l_initiator_id
FROM fun_trx_batches batches
WHERE batch_id = p_batch_id
AND original_batch_id IS NULL
AND reversed_batch_id IS NULL
AND(
(status in ('COMPLETE')
AND NOT EXISTS (SELECT 'Transaction already reversed'
FROM fun_trx_headers hdrs
WHERE hdrs.batch_id = p_batch_id
AND (hdrs.original_trx_id IS NOT NULL
OR
hdrs.reversed_trx_id IS NOT NULL))
) OR
(
NOT EXISTS (SELECT 'Transaction not reversed'
FROM fun_trx_headers hdrs
WHERE hdrs.batch_id = p_batch_id
AND (hdrs.original_trx_id IS NOT NULL
OR hdrs.reversed_trx_id IS NOT NULL)
AND hdrs.status in ('COMPLETE', 'APPROVED'))
)
);
SELECT fun_trx_batches_s.nextval INTO l_reversed_batch_id FROM dual;
UPDATE fun_trx_batches
SET reversed_batch_id = l_reversed_batch_id
WHERE batch_id = p_batch_id;
INSERT INTO fun_trx_batches(BATCH_ID,
BATCH_NUMBER,
INITIATOR_ID,
FROM_LE_ID,
FROM_LEDGER_ID,
CONTROL_TOTAL,
RUNNING_TOTAL_CR,
RUNNING_TOTAL_DR,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
STATUS,
DESCRIPTION,
NOTE,
TRX_TYPE_ID,
TRX_TYPE_CODE,
GL_DATE,
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,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
auto_proration_flag)
SELECT l_reversed_batch_id,
l_reversed_batch_number,
INITIATOR_ID,
FROM_LE_ID,
FROM_LEDGER_ID,
NULL,
DECODE(p_reversal_method, 'CHANGE', DECODE(RUNNING_TOTAL_CR, NULL, NULL,(-1) * (RUNNING_TOTAL_CR)),
RUNNING_TOTAL_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(RUNNING_TOTAL_DR, NULL, NULL,(-1) * (RUNNING_TOTAL_DR)),
RUNNING_TOTAL_CR),
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
'SENT',
p_reversed_description,
NULL,
TRX_TYPE_ID,
TRX_TYPE_CODE,
p_reversed_gl_date,
p_reversed_batch_date,
REJECT_ALLOW_FLAG,
p_batch_id,
NULL,
NULL,
INITIATOR_SOURCE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
auto_proration_flag
FROM fun_trx_batches
WHERE batch_id = p_batch_id;
INSERT INTO fun_trx_headers(TRX_ID,
TRX_NUMBER,
INITIATOR_ID,
RECIPIENT_ID,
TO_LE_ID,
TO_LEDGER_ID,
BATCH_ID,
STATUS,
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,
INIT_WF_KEY,
RECI_WF_KEY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT fun_trx_headers_s.nextval,
TRX_NUMBER, -- Problem: what to use
INITIATOR_ID,
RECIPIENT_ID,
TO_LE_ID,
TO_LEDGER_ID,
l_reversed_batch_id,
'SENT',
DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_DR, NULL, NULL, (-1) *(INIT_AMOUNT_CR)),
INIT_AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_DR, NULL, NULL, (-1) *(INIT_AMOUNT_DR)),
INIT_AMOUNT_CR),
DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_DR, NULL, NULL, (-1) *(RECI_AMOUNT_CR)),
RECI_AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_DR, NULL, NULL, (-1) *(RECI_AMOUNT_DR)),
RECI_AMOUNT_CR),
NULL,
INVOICE_FLAG,
NULL,
NULL,
TRX_ID,
NULL,
NULL,
INITIATOR_INSTANCE_FLAG,
RECIPIENT_INSTANCE_FLAG,
NULL,
p_reversed_description,
NULL,
NULL,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM fun_trx_headers
WHERE batch_id = p_batch_id
AND STATUS in ('COMPLETE', 'APPROVED'); -- Bug: 6625360. AND STATUS = 'COMPLETE';
UPDATE fun_trx_headers hdrs1
SET (reversed_trx_id) = (SELECT trx_id
FROM fun_trx_headers hdrs2
WHERE hdrs2.original_trx_id = hdrs1.trx_id)
WHERE hdrs1.batch_id = p_batch_id;
INSERT INTO fun_batch_dists(BATCH_DIST_ID,
LINE_NUMBER,
BATCH_ID,
CCID,
AMOUNT_CR,
AMOUNT_DR,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT fun_batch_dist_s.nextval,
LINE_NUMBER,
l_reversed_batch_id,
CCID,
DECODE(p_reversal_method, 'CHANGE', (-1) *(nvl(AMOUNT_CR,0)),
AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', (-1) *(nvl(AMOUNT_DR,0)),
AMOUNT_CR),
DESCRIPTION,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM fun_batch_dists dist
WHERE dist.batch_id = p_batch_id;
INSERT INTO fun_trx_lines(LINE_ID,
TRX_ID,
LINE_NUMBER,
LINE_TYPE_FLAG,
INIT_AMOUNT_CR,
INIT_AMOUNT_DR,
RECI_AMOUNT_CR,
RECI_AMOUNT_DR,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT fun_trx_lines_s.nextval,
headers.trx_id,
LINE_NUMBER,
LINE_TYPE_FLAG,
DECODE(p_reversal_method, 'CHANGE',DECODE(lines.INIT_AMOUNT_CR, NULL,NULL, (-1) * (lines.INIT_AMOUNT_CR)),
lines.INIT_AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(lines.INIT_AMOUNT_DR, NULL, NULL, (-1) *(lines.INIT_AMOUNT_DR)),
lines.INIT_AMOUNT_CR),
DECODE(p_reversal_method, 'CHANGE', DECODE(lines.RECI_AMOUNT_CR, NULL,NULL, (-1) * (lines.RECI_AMOUNT_CR)),
lines.RECI_AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(lines.RECI_AMOUNT_DR, NULL, NULL, (-1) * (lines.RECI_AMOUNT_DR)),
lines.RECI_AMOUNT_CR),
lines.DESCRIPTION,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM fun_trx_headers headers, fun_trx_lines lines
WHERE headers.batch_id = l_reversed_batch_id
AND headers.original_trx_id = lines.trx_id;
INSERT INTO fun_dist_lines(DIST_ID,
LINE_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,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
trx_id)
SELECT fun_dist_lines_s.nextval,
reversed_lines.LINE_ID,
orig_dists.DIST_NUMBER,
orig_dists.PARTY_ID,
orig_dists.PARTY_TYPE_FLAG,
orig_dists.DIST_TYPE_FLAG,
NULL,
DECODE(p_reversal_method, 'CHANGE', DECODE(orig_dists.AMOUNT_CR, NULL, NULL, (-1) *(orig_dists.AMOUNT_CR)),
orig_dists.AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(orig_dists.AMOUNT_DR, NULL, NULL, (-1) *(orig_dists.AMOUNT_DR)),
orig_dists.AMOUNT_CR),
orig_dists.CCID,
orig_dists.DESCRIPTION,
orig_dists.AUTO_GENERATE_FLAG,
orig_dists.ATTRIBUTE1,
orig_dists.ATTRIBUTE2,
orig_dists.ATTRIBUTE3,
orig_dists.ATTRIBUTE4,
orig_dists.ATTRIBUTE5,
orig_dists.ATTRIBUTE6,
orig_dists.ATTRIBUTE7,
orig_dists.ATTRIBUTE8,
orig_dists.ATTRIBUTE9,
orig_dists.ATTRIBUTE10,
orig_dists.ATTRIBUTE11,
orig_dists.ATTRIBUTE12,
orig_dists.ATTRIBUTE13,
orig_dists.ATTRIBUTE14,
orig_dists.ATTRIBUTE15,
orig_dists.ATTRIBUTE_CATEGORY,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
reversed_hdrs.trx_id
FROM fun_trx_headers reversed_hdrs,
fun_trx_lines reversed_lines,
--fun_batch_dists reversed_b_dists,
fun_trx_lines orig_lines,
fun_dist_lines orig_dists
WHERE reversed_hdrs.batch_id = l_reversed_batch_id
AND reversed_hdrs.trx_id = reversed_lines.trx_id
AND reversed_hdrs.original_trx_id = orig_lines.trx_id
AND orig_lines.line_id = orig_dists.line_id
AND orig_dists.dist_type_flag='L';
SELECT initiator_id INTO l_initiator_id
FROM fun_trx_headers headers
WHERE headers.trx_id = p_trx_tbl_id(1)
AND headers.reversed_trx_id IS NULL
AND headers.original_trx_id IS NULL
AND headers.status in ('COMPLETE', 'APPROVED');--Bug: 6625360. AND headers.status = 'COMPLETE';
SELECT fun_trx_batches_s.nextval INTO l_reversed_batch_id FROM dual;
SELECT nvl(h.INIT_AMOUNT_CR,0), nvl(h.INIT_AMOUNT_DR,0)
INTO l_batch_cr, l_batch_dr
FROM fun_trx_headers h
WHERE h.trx_id=p_trx_tbl_id(i);
INSERT INTO fun_trx_batches(BATCH_ID,
BATCH_NUMBER,
INITIATOR_ID,
FROM_LE_ID,
FROM_LEDGER_ID,
CONTROL_TOTAL,
RUNNING_TOTAL_CR,
RUNNING_TOTAL_DR,
CURRENCY_CODE,
EXCHANGE_RATE_TYPE,
STATUS,
DESCRIPTION,
NOTE,
TRX_TYPE_ID,
TRX_TYPE_CODE,
GL_DATE,
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,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
auto_proration_flag)
SELECT l_reversed_batch_id,
l_reversed_batch_number,
batches.INITIATOR_ID,
batches.FROM_LE_ID,
batches.FROM_LEDGER_ID,
NULL,
DECODE(p_reversal_method, 'CHANGE', (-1) *(l_total_batch_cr),
l_total_batch_dr),
DECODE(p_reversal_method, 'CHANGE', (-1) *(l_total_batch_dr),
l_total_batch_cr),
batches.CURRENCY_CODE,
batches.EXCHANGE_RATE_TYPE,
'SENT',
p_reversed_description,
NULL,
batches.TRX_TYPE_ID,
batches.TRX_TYPE_CODE,
p_reversed_gl_date,
p_reversed_batch_date,
batches.REJECT_ALLOW_FLAG,
batches.batch_id,
NULL,
NULL,
batches.INITIATOR_SOURCE,
batches.ATTRIBUTE1,
batches.ATTRIBUTE2,
batches.ATTRIBUTE3,
batches.ATTRIBUTE4,
batches.ATTRIBUTE5,
batches.ATTRIBUTE6,
batches.ATTRIBUTE7,
batches.ATTRIBUTE8,
batches.ATTRIBUTE9,
batches.ATTRIBUTE10,
batches.ATTRIBUTE11,
batches.ATTRIBUTE12,
batches.ATTRIBUTE13,
batches.ATTRIBUTE14,
batches.ATTRIBUTE15,
batches.ATTRIBUTE_CATEGORY,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
batches.auto_proration_flag
FROM fun_trx_batches batches, fun_trx_headers headers
WHERE batches.batch_id = headers.batch_id
AND headers.trx_id = p_trx_tbl_id(1);
INSERT INTO fun_trx_headers(TRX_ID,
TRX_NUMBER,
INITIATOR_ID,
RECIPIENT_ID,
TO_LE_ID,
TO_LEDGER_ID,
BATCH_ID,
STATUS,
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,
INIT_WF_KEY,
RECI_WF_KEY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT fun_trx_headers_s.nextval,
TRX_NUMBER, -- Problem: what to use
INITIATOR_ID,
RECIPIENT_ID,
TO_LE_ID,
TO_LEDGER_ID,
l_reversed_batch_id,
'SENT',
DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_DR, NULL, NULL, (-1) *(INIT_AMOUNT_CR)),
INIT_AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_DR, NULL, NULL, (-1) *(INIT_AMOUNT_DR)),
INIT_AMOUNT_CR),
DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_DR, NULL, NULL, (-1) *(RECI_AMOUNT_CR)),
RECI_AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(INIT_AMOUNT_DR, NULL, NULL, (-1) *(RECI_AMOUNT_DR)),
RECI_AMOUNT_CR),
NULL,
INVOICE_FLAG,
NULL,
NULL,
TRX_ID,
NULL,
NULL,
INITIATOR_INSTANCE_FLAG,
RECIPIENT_INSTANCE_FLAG,
NULL,
p_reversed_description,
NULL,
NULL,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM fun_trx_headers
WHERE trx_id = p_trx_tbl_id(i)
AND STATUS in ('COMPLETE', 'APPROVED'); --Bug: 6625360. AND STATUS = 'COMPLETE';
UPDATE fun_trx_headers hdrs1
SET (reversed_trx_id) = (SELECT trx_id
FROM fun_trx_headers hdrs2
WHERE hdrs2.original_trx_id = hdrs1.trx_id)
WHERE hdrs1.trx_id = p_trx_tbl_id(i);
UPDATE fun_trx_batches
SET RUNNING_TOTAL_CR=(Select SUM(nvl(INIT_AMOUNT_CR,0))
from fun_trx_headers
where batch_id=l_reversed_batch_id
),
RUNNING_TOTAL_DR=(Select SUM(nvl(INIT_AMOUNT_DR,0))
from fun_trx_headers
where batch_id=l_reversed_batch_id
)
where batch_id=l_reversed_batch_id;
INSERT INTO fun_trx_lines(LINE_ID,
TRX_ID,
LINE_NUMBER,
LINE_TYPE_FLAG,
INIT_AMOUNT_CR,
INIT_AMOUNT_DR,
RECI_AMOUNT_CR,
RECI_AMOUNT_DR,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT fun_trx_lines_s.nextval,
headers.trx_id,
LINE_NUMBER,
LINE_TYPE_FLAG,
DECODE(p_reversal_method, 'CHANGE', DECODE(lines.INIT_AMOUNT_CR, NULL, NULL, (-1) * (lines.INIT_AMOUNT_CR)),
lines.INIT_AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(lines.INIT_AMOUNT_DR, NULL, NULL, (-1) * (lines.INIT_AMOUNT_DR)),
lines.INIT_AMOUNT_CR),
DECODE(p_reversal_method, 'CHANGE', DECODE(lines.RECI_AMOUNT_CR, NULL, NULL, (-1) * (lines.RECI_AMOUNT_CR)),
lines.RECI_AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(lines.RECI_AMOUNT_DR, NULL, NULL, (-1) * (lines.RECI_AMOUNT_DR)),
lines.RECI_AMOUNT_CR),
lines.DESCRIPTION,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM fun_trx_headers headers, fun_trx_lines lines
WHERE headers.batch_id = l_reversed_batch_id
AND headers.original_trx_id = lines.trx_id;
INSERT INTO fun_dist_lines(DIST_ID,
LINE_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,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
trx_id)
SELECT fun_dist_lines_s.nextval,
reversed_lines.LINE_ID,
orig_dists.DIST_NUMBER,
orig_dists.PARTY_ID,
orig_dists.PARTY_TYPE_FLAG,
orig_dists.DIST_TYPE_FLAG,
NULL,
DECODE(p_reversal_method, 'CHANGE', DECODE(orig_dists.AMOUNT_CR, NULL, NULL, (-1) *(orig_dists.AMOUNT_CR)),
orig_dists.AMOUNT_DR),
DECODE(p_reversal_method, 'CHANGE', DECODE(orig_dists.AMOUNT_DR, NULL, NULL, (-1) *(orig_dists.AMOUNT_DR)),
orig_dists.AMOUNT_CR),
orig_dists.CCID,
orig_dists.DESCRIPTION,
orig_dists.AUTO_GENERATE_FLAG,
orig_dists.ATTRIBUTE1,
orig_dists.ATTRIBUTE2,
orig_dists.ATTRIBUTE3,
orig_dists.ATTRIBUTE4,
orig_dists.ATTRIBUTE5,
orig_dists.ATTRIBUTE6,
orig_dists.ATTRIBUTE7,
orig_dists.ATTRIBUTE8,
orig_dists.ATTRIBUTE9,
orig_dists.ATTRIBUTE10,
orig_dists.ATTRIBUTE11,
orig_dists.ATTRIBUTE12,
orig_dists.ATTRIBUTE13,
orig_dists.ATTRIBUTE14,
orig_dists.ATTRIBUTE15,
orig_dists.ATTRIBUTE_CATEGORY,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
reversed_hdrs.trx_id
FROM fun_trx_headers reversed_hdrs,
fun_trx_lines reversed_lines,
--fun_batch_dists reversed_b_dists,
fun_trx_lines orig_lines,
fun_dist_lines orig_dists
WHERE reversed_hdrs.batch_id = l_reversed_batch_id
AND reversed_hdrs.trx_id = reversed_lines.trx_id
AND reversed_hdrs.original_trx_id = orig_lines.trx_id
AND orig_lines.line_id = orig_dists.line_id
AND orig_dists.dist_type_flag='L';
PROCEDURE Update_Trx_Status
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_trx_id IN NUMBER,
p_update_status_to IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRX_STATUS';
SELECT status
FROM fun_trx_headers
WHERE trx_id = p_trx_id FOR UPDATE;
SAVEPOINT Update_Trx_Status;
Debug('UPDATE_TRX_STATUS(+)');
IF (l_status = p_update_status_to OR
(p_update_status_to = 'RECEIVED' AND l_status <> 'SENT')) THEN
Debug('CONSISTENT STATUS');
(l_status = 'NEW' AND p_update_status_to = 'SENT') OR
(l_status = 'SENT' AND p_update_status_to = 'ERROR') OR
(l_status = 'RECEIVED' AND p_update_status_to = 'ERROR') OR
(l_status = 'ERROR' AND p_update_status_to = 'SENT') OR
(l_status = 'ERROR' AND p_update_status_to = 'DELETED') OR
(l_status = 'SENT' AND p_update_status_to = 'RECEIVED') OR
(l_status = 'SENT' AND p_update_status_to = 'APPROVED') OR
(l_status = 'SENT' AND p_update_status_to = 'REJECTED') OR
(l_status = 'RECEIVED' AND p_update_status_to = 'APPROVED') OR
(l_status = 'RECEIVED' AND p_update_status_to = 'REJECTED') OR
(l_status = 'APPROVED' AND p_update_status_to = 'XFER_INI_GL') OR
(l_status = 'APPROVED' AND p_update_status_to = 'XFER_RECI_GL') OR
(l_status = 'APPROVED' AND p_update_status_to = 'XFER_AR') OR
(l_status = 'APPROVED' AND p_update_status_to = 'COMPLETE') OR
(l_status = 'XFER_AR' AND p_update_status_to = 'COMPLETE') OR
(l_status = 'XFER_INI_GL' AND p_update_status_to = 'COMPLETE') OR
(l_status = 'XFER_RECI_GL' AND p_update_status_to = 'COMPLETE')
THEN
UPDATE fun_trx_headers
SET status = p_update_status_to
WHERE trx_id = p_trx_id;
SELECT batch_id
INTO l_batch_id
FROM fun_trx_headers
WHERE trx_id = p_trx_id;
IF (p_update_status_to = 'ERROR')
THEN
-- Update batch to ERROR if all
-- transactions are at status ERROR
UPDATE fun_trx_batches
SET status = 'ERROR'
WHERE batch_id = l_batch_id
AND NOT EXISTS (SELECT 'X'
FROM fun_trx_headers
WHERE batch_id = l_batch_id
AND status <> 'ERROR');
ELSIF (p_update_status_to IN ('COMPLETE', 'REJECTED'))
THEN
-- Update batch to COMPLETE if all
-- transactions are at status COMPLETE or REJECTTED
UPDATE fun_trx_batches
SET status = 'COMPLETE'
WHERE batch_id = l_batch_id
AND NOT EXISTS (SELECT 'X'
FROM fun_trx_headers
WHERE batch_id = l_batch_id
AND status NOT IN ('COMPLETE','REJECTED'));
FND_MESSAGE.SET_NAME('FUN', 'Can not update status given');
Debug('UPDATE_TRX_STATUS(-)');
ROLLBACK TO Update_Trx_Status;
ROLLBACK TO Update_Trx_Status;
ROLLBACK TO Update_Trx_Status;
CURSOR l_batch_cursor IS SELECT batch_id,
batch_number,
initiator_id,
from_le_id,
from_ledger_id,
control_total,
currency_code,
exchange_rate_type,
status,
description,
trx_type_id,
trx_type_code,
gl_date,
batch_date,
reject_allow_flag,
from_recurring_batch_id,
auto_proration_flag
FROM fun_trx_batches
WHERE batch_id = p_batch_id;
CURSOR l_trx_cursor IS SELECT trx_id,
initiator_id,
recipient_id,
to_le_id,
to_ledger_id,
batch_id,
status,
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,
NULL,
trx_number
FROM fun_trx_headers
WHERE batch_id = p_batch_id;
CURSOR l_init_dist_cursor IS SELECT batch_dist_id,
line_number,
batch_id,
ccid,
amount_cr,
amount_dr,
description
FROM fun_batch_dists
WHERE batch_id = p_batch_id;
CURSOR l_dist_lines_cursor IS SELECT dists.dist_id,
dists.dist_number,
lines.trx_id,
dists.line_id,
dists.party_id,
dists.party_type_flag,
dists.dist_type_flag,
dists.batch_dist_id,
dists.amount_cr,
dists.amount_dr,
dists.ccid,
hdrs.trx_number
FROM fun_trx_headers hdrs,
fun_trx_lines lines,
fun_dist_lines dists
WHERE hdrs.batch_id = p_batch_id
AND hdrs.trx_id = lines.trx_id
AND lines.line_id = dists.line_id
AND dists.party_type_flag = 'I'
AND dists.dist_type_flag = 'R';
SELECT nvl( minimum_accountable_unit, power( 10, (-1 * precision)))
INTO l_mau
FROM FND_CURRENCIES
WHERE currency_code = l_currency_code;
DELETE FROM fun_dist_lines
WHERE party_type_flag = 'I'
AND dist_type_flag = 'L'
--AND auto_generate_flag = 'Y'
AND line_id IN
(SELECT line_id
FROM fun_trx_lines trx_lines
WHERE trx_lines.trx_id = p_trx_tbl(i).trx_id);
SELECT line_id
INTO l_line_id
FROM fun_trx_lines
WHERE trx_id = p_trx_tbl(t).trx_id;
INSERT INTO fun_dist_lines(DIST_ID,
LINE_ID,
DIST_NUMBER,
PARTY_ID,
PARTY_TYPE_FLAG,
DIST_TYPE_FLAG,
BATCH_DIST_ID,
AMOUNT_CR,
AMOUNT_DR,
CCID,
DESCRIPTION,
AUTO_GENERATE_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
trx_id)
VALUES (fun_dist_lines_s.nextval,
l_line_id,
i,
p_trx_tbl(t).initiator_id,
'I',
'L',
p_init_dist_tbl(i).batch_dist_id,
l_amount_cr,
l_amount_dr,
p_init_dist_tbl(i).ccid,
p_init_dist_tbl(i).description,
'Y',
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
p_trx_tbl(t).trx_id);
SELECT count(*)
FROM hr_operating_units ou
WHERE organization_id = l_to_ou_id
AND date_from <= p_trx_date
AND NVL(date_to, p_trx_date) >= p_trx_date; --
SELECT count(*)
FROM GL_PERIOD_STATUSES PST
WHERE pst.application_id = 200
AND pst.closing_status <> 'N'
AND pst.adjustment_period_flag <> 'Y'
AND pst.ledger_id = p_to_le_id; */
SELECT legal_entity_id
INTO l_from_le_id
FROM xle_firstparty_information_v
WHERE party_id = l_from_le_party_id;
SELECT COUNT(*)
FROM gl_period_statuses glps,
gl_periods periods,
gl_ledgers ledgers
WHERE periods.period_set_name = ledgers.period_set_name
AND TRUNC(p_trx_date) BETWEEN periods.start_date AND periods.end_date
AND glps.period_name = periods.period_name
AND glps.application_id = 222
AND glps.set_of_books_id = ledgers.ledger_id
AND glps.set_of_books_id = p_ledger_id
AND ledgers.ledger_id = p_ledger_id
AND glps.adjustment_period_flag <> 'Y'
AND glps.closing_status IN ('O','F');
SELECT initiator_id, trx_type_id, batch_date, from_ledger_id
INTO l_initiator_id, l_trx_type_id, l_trx_date, l_from_ledger_id
FROM fun_trx_batches
WHERE batch_id = p_batch_id;
SELECT recipient_id, to_le_id
INTO l_recipient_id, l_to_le_id
FROM fun_trx_headers
WHERE trx_id = p_trx_id;
l_sql := ' SELECT notif.notification_id ' ||
' FROM wf_item_activity_statuses wias, ' ||
' wf_notifications notif ' ||
' WHERE wias.item_type IN (''FUNRMAIN'', ''FUNIMAIN'', ' ||
' ''FUNRTVAL'') ' ||
' AND wias.notification_id = notif.notification_id ' ||
' AND notif.status = ''OPEN''';
'Transaction Deleted');