[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* PRIVATE PROCEDURE delete_trx_batch
* ----------------------------------------------------
* Lock and delete a transaction. And if that is
* the last transaction in the batch, then delete the
* batch too.
* ---------------------------------------------------*/
PROCEDURE delete_trx_batch (
p_batch_id IN number,
p_trx_id IN number)
IS
l_n_trx number;
SELECT batch_id INTO l_n_trx
FROM fun_trx_batches
WHERE batch_id = p_batch_id
FOR UPDATE;
SELECT COUNT(h.trx_id) INTO l_n_trx
FROM fun_trx_batches b,
fun_trx_headers h
WHERE h.batch_id = b.batch_id AND
b.batch_id = p_batch_id;
DELETE FROM fun_dist_lines
WHERE line_id IN
( SELECT line_id
FROM fun_trx_lines
WHERE trx_id = p_trx_id );
DELETE FROM fun_trx_lines
WHERE trx_id = p_trx_id;
DELETE FROM fun_trx_headers
WHERE trx_id = p_trx_id;
DELETE FROM fun_trx_batches
WHERE batch_id = p_batch_id;
END delete_trx_batch;
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
INTO l_rec.batch_id, l_rec.batch_number, l_rec.initiator_id,
l_rec.from_le_id, l_rec.from_ledger_id, l_rec.control_total,
l_rec.currency_code, l_rec.exchange_rate_type, l_rec.status,
l_rec.description, l_rec.trx_type_id, l_rec.trx_type_code,
l_rec.gl_date, l_rec.batch_date, l_rec.reject_allowed,
l_rec.from_recurring_batch
FROM fun_trx_batches
WHERE batch_id = p_batch_id;
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
INTO l_rec.trx_id, l_rec.initiator_id, l_rec.recipient_id,
l_rec.to_le_id, l_rec.to_ledger_id, l_rec.batch_id,
l_rec.status, l_rec.init_amount_cr, l_rec.init_amount_dr,
l_rec.reci_amount_cr, l_rec.reci_amount_dr, l_rec.ar_invoice_number,
l_rec.invoicing_rule, l_rec.approver_id, l_rec.approval_date,
l_rec.original_trx_id, l_rec.reversed_trx_id, l_rec.from_recurring_trx_id,
l_rec.initiator_instance, l_rec.recipient_instance
FROM fun_trx_headers
WHERE trx_id = p_trx_id;
SELECT d.dist_id, d.line_id, d.party_id,
d.party_type_flag, d.dist_type_flag, d.batch_dist_id,
d.amount_cr, d.amount_dr, d.ccid
FROM fun_dist_lines d, fun_trx_lines l
WHERE party_type_flag = 'R' AND
d.line_id = l.line_id AND
l.trx_id = p_trx_id;
SELECT
b.batch_number,
ltrim(to_char(decode(nvl(b.running_total_cr,0),
0, b.running_total_dr,
b.running_total_cr),'99999999999999999999999999999999999999999999999.99'))||' '||b.currency_code,
h.party_name
INTO l_batch_num, l_trx_amt, l_initiator_name
FROM fun_trx_batches b, hz_parties h
WHERE batch_id = l_batch_id
AND b.initiator_id = h.party_id;
select b.batch_number,ltrim(to_char(decode(nvl(h.reci_amount_cr,0),
0,h.reci_amount_dr,
h.reci_amount_cr),'99999999999999999999999999999999999999999999999.99'))||' '||b.currency_code
into l_batch_num, l_trx_amt
from fun_trx_headers h, fun_trx_batches b
where b.batch_id = l_batch_id
and h.trx_id = l_trx_id;
SELECT init.party_name
INTO l_initiator_name
FROM fun_trx_headers,
hz_parties init
WHERE trx_id = l_trx_id
AND initiator_id = init.party_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.last_updated_by
AND b.batch_id = l_batch_id;
SELECT trx_number ,
rec.party_name
INTO l_trx_num, l_recipient_name
FROM fun_trx_headers,
hz_parties rec
WHERE trx_id = l_trx_id
AND recipient_id = rec.party_id;
UPDATE fun_trx_headers
SET reci_wf_key = itemkey
WHERE trx_id = l_trx_id;
* PROCEDURE delete_trx
* ----------------------------------------------------
* Delete the transaction from the recipient's DB.
* ---------------------------------------------------*/
PROCEDURE delete_trx (
itemtype IN varchar2,
itemkey IN varchar2,
actid IN number,
funcmode IN varchar2,
resultout IN OUT NOCOPY varchar2)
IS
l_trx_id number;
delete_trx_batch(l_batch_id, l_trx_id);
END delete_trx;
SELECT invoice_flag INTO l_result
FROM fun_trx_headers
WHERE trx_id = l_trx_id;
SELECT status INTO l_status
FROM fun_trx_headers
WHERE trx_id = l_trx_id;
SELECT t.manual_approve_flag INTO l_result
FROM fun_trx_batches b, fun_trx_types_b t
WHERE b.batch_id = l_batch_id AND
b.trx_type_id = t.trx_type_id;
wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_APPROVED',
itemtype, itemkey, TO_CHAR(actid), funcmode);
SELECT initiator_instance_flag INTO l_result
FROM fun_trx_headers
WHERE trx_id = l_trx_id;
SELECT recipient_id INTO l_party_id
FROM fun_trx_headers
WHERE trx_id = l_trx_id;
SELECT reject_allow_flag INTO l_result
FROM fun_trx_batches
WHERE batch_id = l_batch_id;
SELECT usr.user_id
FROM fnd_user usr
WHERE usr.user_name = p_user_name;
SELECT usr.user_id
FROM fnd_user usr
WHERE usr.email_address = p_user_email;
SELECT REPLACE(l_user_email, 'email:') INTO l_user_email FROM dual;
ame_api2.updateApprovalStatus(
applicationIdIn => 435,
transactionTypeIn => 'FUN_IC_RECI_TRX',
transactionIdIn => l_trx_id,
approverIn => l_approver_record);
UPDATE fun_trx_headers
SET approver_id = l_user_id,
approval_date = SYSDATE
WHERE trx_id = l_trx_id;
fun_trx_pvt.update_trx_status
(p_api_version => 1.0,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trx_id => l_trx_id,
p_update_status_to => 'REJECTED');
UPDATE fun_trx_headers
SET reject_reason = l_reason
WHERE trx_id = l_trx_id;
ame_api2.updateApprovalStatus(
applicationIdIn => 435,
transactionTypeIn => 'FUN_IC_RECI_TRX',
transactionIdIn => l_trx_id,
approverIn => l_approver_record);
ame_api2.updateApprovalStatus(
applicationIdIn => 435,
transactionTypeIn => 'FUN_IC_RECI_TRX',
transactionIdIn => l_trx_id,
approverIn => l_approver_record,
forwardeeIn => l_forwardee_record);
SELECT b.from_le_id, b.gl_date, h.to_le_id, h.to_ledger_id,
fun_tca_pkg.get_ou_id(h.initiator_id),
fun_tca_pkg.get_ou_id(h.recipient_id),
b.batch_date, b.initiator_id, h.recipient_id
INTO l_from_le_id, l_gl_date, l_to_le_id, l_to_ledger_id,
l_from_org_id, l_to_org_id, l_trx_date,
l_initiator_id,
l_recipient_id
FROM fun_trx_batches b,
fun_trx_headers h
WHERE b.batch_id = l_batch_id AND
h.trx_id = l_trx_id AND
h.batch_id = b.batch_id;
select ltrim(to_char(decode(nvl(h.reci_amount_cr,0),
0,h.reci_amount_dr,
h.reci_amount_cr),'999999999.99'))||' '||b.currency_code
into l_trx_amt
from fun_trx_headers h, fun_trx_batches b
where b.batch_id = l_batch_id
and h.trx_id = l_trx_id;
SELECT init.party_name
INTO l_initiator_name
FROM fun_trx_headers,
hz_parties init
WHERE trx_id = l_trx_id
AND initiator_id = init.party_id;
SELECT batch_date, gl_date, currency_code,
exchange_rate_type,
fun_tca_pkg.get_ou_id(initiator_id) from_org_id
INTO l_batch_date, l_gl_date, l_currency,
l_exchange_rate_type,
l_from_org_id
FROM fun_trx_batches
WHERE batch_id = l_batch_id;
SELECT ar_invoice_number INTO l_invoice_Num
FROM fun_trx_headers
WHERE trx_id = l_trx_id;
SELECT status INTO l_status
FROM fun_trx_headers
WHERE trx_id = l_trx_id;
l_params.delete();
l_params.delete();
l_params.delete();
l_params.delete();
SELECT recipient_id INTO l_party_id
FROM fun_trx_headers
WHERE trx_id = l_trx_id;
l_params.delete();
* PROCEDURE update_status_error
* ----------------------------------------------------
* Update status to error.
* ---------------------------------------------------
PROCEDURE update_status_error (
itemtype IN varchar2,
itemkey IN varchar2,
actid IN number,
funcmode IN varchar2,
resultout IN OUT NOCOPY varchar2)
IS
l_trx_id number;
fun_trx_pvt.update_trx_status
(p_api_version => 1.0,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trx_id => l_trx_id,
p_update_status_to => 'ERROR');
wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_ERROR',
itemtype, itemkey, TO_CHAR(actid), funcmode);
END update_status_error;
* PROCEDURE update_status_rejected
* ----------------------------------------------------
* Update status to rejected.
* ---------------------------------------------------*/
PROCEDURE update_status_rejected (
itemtype IN varchar2,
itemkey IN varchar2,
actid IN number,
funcmode IN varchar2,
resultout IN OUT NOCOPY varchar2)
IS
l_trx_id number;
fun_trx_pvt.update_trx_status
(p_api_version => 1.0,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trx_id => l_trx_id,
p_update_status_to => 'REJECTED');
wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_REJECTED',
itemtype, itemkey, TO_CHAR(actid), funcmode);
END update_status_rejected;
* PROCEDURE update_status_approved
* ----------------------------------------------------
* Update status to approved.
* ---------------------------------------------------*/
PROCEDURE update_status_approved (
itemtype IN varchar2,
itemkey IN varchar2,
actid IN number,
funcmode IN varchar2,
resultout IN OUT NOCOPY varchar2)
IS
l_trx_id number;
fun_trx_pvt.update_trx_status
(p_api_version => 1.0,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trx_id => l_trx_id,
p_update_status_to => 'APPROVED');
wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_APPROVED',
itemtype, itemkey, TO_CHAR(actid), funcmode);
wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_APPROVED',
itemtype, itemkey, TO_CHAR(actid), funcmode);
END update_status_approved;
SELECT WPA.ACTIVITY_NAME
INTO l_activity_name
from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA
where WIAS.ITEM_TYPE = 'FUNRMAIN'
and WIAS.ITEM_KEY = p_eventkey
and WIAS.ACTIVITY_STATUS = wf_engine.eng_notified
and WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
and ((WPA.PROCESS_NAME = 'RECIPIENT_APPROVAL'
AND WPA.ACTIVITY_NAME IN ('FIX_ACCT_DIST_NTF',
'FIX_ACCT_DIST_NTF_NO_REJ')) OR
(WPA.PROCESS_NAME = 'SEND_APPROVAL_NOTIFICATION'
AND WPA.ACTIVITY_NAME IN ('APPROVAL_NTF', 'APPROVAL_ONLY_NTF')));
ame_api2.updateApprovalStatus(
applicationIdIn => 435,
transactionTypeIn => 'FUN_IC_RECI_TRX',
transactionIdIn => p_trx_id,
approverIn => l_approver_record);
UPDATE fun_trx_headers
SET approver_id = FND_GLOBAL.USER_ID,
approval_date = SYSDATE
WHERE trx_id = p_trx_id;
UPDATE fun_trx_headers
SET status = 'RECEIVED'
WHERE trx_id = p_trx_id;
SELECT reject_allow_flag INTO l_result
FROM fun_trx_batches
WHERE batch_id = p_batch_id;
SELECT WPA.ACTIVITY_NAME
INTO l_activity_name
from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA
where WIAS.ITEM_TYPE = 'FUNRMAIN'
and WIAS.ITEM_KEY = p_eventkey
and WIAS.ACTIVITY_STATUS = wf_engine.eng_notified
and WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
and ((WPA.PROCESS_NAME = 'RECIPIENT_APPROVAL'
AND WPA.ACTIVITY_NAME IN ('FIX_ACCT_DIST_NTF')) OR
(WPA.PROCESS_NAME = 'SEND_APPROVAL_NOTIFICATION'
AND WPA.ACTIVITY_NAME IN ('APPROVAL_NTF', 'APPROVAL_ONLY_NTF')));
fun_trx_pvt.update_trx_status
(p_api_version => 1.0,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trx_id => p_trx_id,
p_update_status_to => 'REJECTED');
ame_api2.updateApprovalStatus(
applicationIdIn => 435,
transactionTypeIn => 'FUN_IC_RECI_TRX',
transactionIdIn => p_trx_id,
approverIn => l_approver_record);
UPDATE fun_trx_headers
SET status = 'RECEIVED'
WHERE trx_id = p_trx_id;
SELECT WPA.ACTIVITY_NAME
INTO l_activity_name
from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA
where WIAS.ITEM_TYPE = 'FUNRMAIN'
and WIAS.ITEM_KEY = p_eventkey
and WIAS.ACTIVITY_STATUS = wf_engine.eng_notified
and WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
and ((WPA.PROCESS_NAME = 'RECIPIENT_APPROVAL'
AND WPA.ACTIVITY_NAME IN ('FIX_ACCT_DIST_NTF','FIX_ACCT_DIST_NTF_NO_REJ')) OR
(WPA.PROCESS_NAME = 'SEND_APPROVAL_NOTIFICATION'
AND WPA.ACTIVITY_NAME IN ('APPROVAL_NTF', 'APPROVAL_ONLY_NTF')));
fun_trx_pvt.update_trx_status
(p_api_version => 1.0,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trx_id => p_trx_id,
p_update_status_to => 'REJECTED');
ame_api2.updateApprovalStatus(
applicationIdIn => 435,
transactionTypeIn => 'FUN_IC_RECI_TRX',
transactionIdIn => p_trx_id,
approverIn => l_approver_record);
UPDATE fun_trx_headers
SET status = 'RECEIVED'
WHERE trx_id = p_trx_id;
* Insert a default intercompany account for recipient
* accounting to fun_dist_lines
* ---------------------------------------------------*/
procedure recipient_interco_acct (
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in OUT NOCOPY varchar2)
IS
l_trx_id number;
SELECT COUNT(*)
INTO l_dist_exist
FROM fun_dist_lines d
WHERE d.trx_id = l_trx_id
AND d.dist_type_flag = 'L'
AND d.party_type_flag = 'R';
SELECT b.from_le_id, h.to_le_id, h.reci_amount_cr, h.reci_amount_dr
INTO l_from_le_id, l_to_le_id, l_reci_amount_cr, l_reci_amount_dr
FROM FUN_TRX_BATCHES b, FUN_TRX_HEADERS h
WHERE b.batch_id = l_batch_id
AND b.batch_id = h.batch_id
AND h.trx_id = l_trx_id;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'Inserting distribution rows');
FOR crec in (SELECT
dl.LINE_ID,
--dl.PARTY_ID,
h.RECIPIENT_ID,
dl.amount_dr,
dl.amount_cr
--FUN_TRX_ENTRY_UTIL.GET_DEFAULT_CCID(l_to_le_id, l_from_le_id, 'P') CCID
FROM fun_dist_lines dl, fun_trx_lines l, fun_trx_headers h
WHERE l.trx_id = l_trx_id
AND dl.trx_id = h.trx_id
AND dl.line_id = l.line_id
AND dl.party_type_flag = 'I'
AND dl.dist_type_flag = 'L')
LOOP
SELECT FUN_DIST_LINES_S.nextval INTO l_dist_id FROM dual;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'Inserting distribution ' || l_dist_id);
INSERT into FUN_DIST_LINES(
DIST_ID,
LINE_ID,
DIST_NUMBER,
PARTY_ID,
PARTY_TYPE_FLAG,
DIST_TYPE_FLAG,
AMOUNT_CR,
AMOUNT_DR,
CCID,
AUTO_GENERATE_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
trx_id)
VALUES(
l_dist_id,
crec.line_id,
l_dist_id,
--crec.party_id,
crec.recipient_id,
'R',
'L',
crec.amount_dr,
crec.amount_cr,
l_ccid,
'N',
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
l_trx_id);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'Done inserting');
select status into l_trx_status
from fun_trx_headers
where trx_id = l_trx_id;
UPDATE fun_trx_headers
SET status = 'RECEIVED'
WHERE trx_id = l_trx_id;
SELECT
dist.dist_id ,
dist.dist_number ,
dist.trx_id ,
dist.line_id ,
head.initiator_id ,
head.recipient_id ,
btch.from_le_id initiator_le_id ,
head.to_le_id recipient_le_id ,
btch.gl_date ,
DECODE(p_party_type, 'I', btch.from_ledger_id,
head.to_ledger_id) ledger_id ,
dist.amount_cr ,
dist.amount_dr ,
dist.ccid ,
fun_util.get_account_segment_value (DECODE(p_party_type, 'I',
btch.from_ledger_id,
head.to_ledger_id),
dist.ccid,
'GL_BALANCING') dist_bsv ,
dist.description,
head.init_amount_cr,
head.init_amount_dr,
head.reci_amount_cr,
head.reci_amount_dr
FROM fun_dist_lines dist,
fun_trx_headers head,
fun_trx_batches btch
WHERE dist.trx_id = head.trx_id
AND head.batch_id = btch.batch_id
AND dist.party_type_flag = p_party_type
AND head.trx_id = p_trx_id
AND dist.dist_type_flag = 'L';
last_updated_by NUMBER,
last_update_date DATE,
last_update_login NUMBER,
trx_id NUMBER (15));
SELECT b.batch_id,
b.initiator_id initiator_id
,b.from_le_id from_le_id
,b.from_ledger_id from_ledger_id
,b.currency_code currency_code
,b.attribute1 bat_attribute1
,b.attribute2 bat_attribute2
,b.attribute3 bat_attribute3
,b.attribute4 bat_attribute4
,b.attribute5 bat_attribute5
,b.attribute6 bat_attribute6
,b.attribute7 bat_attribute7
,b.attribute8 bat_attribute8
,b.attribute9 bat_attribute9
,b.attribute10 bat_attribute10
,b.attribute11 bat_attribute11
,b.attribute12 bat_attribute12
,b.attribute13 bat_attribute13
,b.attribute14 bat_attribute14
,b.attribute15 bat_attribute15
,b.attribute_category bat_attribute_category
,t.trx_id trx_id
,t.recipient_id recipient_id
,t.to_le_id to_le_id
,t.to_ledger_id to_ledger_id
,t.attribute1 trx_attribute1
,t.attribute2 trx_attribute2
,t.attribute3 trx_attribute3
,t.attribute4 trx_attribute4
,t.attribute5 trx_attribute5
,t.attribute6 trx_attribute6
,t.attribute7 trx_attribute7
,t.attribute8 trx_attribute8
,t.attribute9 trx_attribute9
,t.attribute10 trx_attribute10
,t.attribute11 trx_attribute11
,t.attribute12 trx_attribute12
,t.attribute13 trx_attribute13
,t.attribute14 trx_attribute14
,t.attribute15 trx_attribute15
,t.attribute_category trx_attribute_category
,y.trx_type_id trx_type_id
,y.manual_approve_flag manual_approve_flag
,y.allow_invoicing_flag allow_invoicing_flag
,y.vat_taxable_flag vat_taxable_flag
,y.allow_interest_accrual_flag allow_interest_accrual_flag
,y.attribute1 typ_attribute1
,y.attribute2 typ_attribute2
,y.attribute3 typ_attribute3
,y.attribute4 typ_attribute4
,y.attribute5 typ_attribute5
,y.attribute6 typ_attribute6
,y.attribute7 typ_attribute7
,y.attribute8 typ_attribute8
,y.attribute9 typ_attribute9
,y.attribute10 typ_attribute10
,y.attribute11 typ_attribute11
,y.attribute12 typ_attribute12
,y.attribute13 typ_attribute13
,y.attribute14 typ_attribute14
,y.attribute15 typ_attribute15
,y.attribute_category typ_attribute_category
,l.chart_of_accounts_id coa_id
,b.batch_date
,b.gl_date
FROM fun_trx_batches b,
fun_trx_headers t,
fun_trx_types_vl y,
gl_ledgers l
WHERE b.batch_id = t.batch_id
AND b.trx_type_id = y.trx_type_id
AND t.to_ledger_id = l.ledger_id
AND t.trx_id = p_trx_id;
SELECT amb_context_code,
account_definition_code
FROM fun_trx_acct_definitions
WHERE ledger_id = p_ledger_id;
SELECT vals.segment_value
FROM gl_ledger_le_bsv_specific_v vals
WHERE vals.legal_entity_id = p_le_id
AND vals.ledger_id = p_ledger_id
AND p_gl_date BETWEEN Nvl(vals.start_date, p_gl_date) AND Nvl(vals.end_date, p_gl_date)
AND (SELECT COUNT(*)
FROM gl_ledger_le_bsv_specific_v vals1
WHERE vals1.legal_entity_id = p_le_id
AND vals1.ledger_id = p_ledger_id
AND p_gl_date BETWEEN Nvl(vals1.start_date, p_gl_date) AND Nvl(vals1.end_date, p_gl_date)) = 1;
l_insert_flag VARCHAR2(1) := 'N';
select NVL(invoice_flag, 'N')
INTO l_invoice_flag
from fun_trx_headers
where trx_id = p_trx_id;
SELECT MIN(DIST_BSV)
INTO l_init_bsv
FROM (SELECT FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
HEAD.TO_LEDGER_ID),
DIST.CCID,'GL_BALANCING') DIST_BSV,
DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
FROM FUN_DIST_LINES DIST,
FUN_TRX_HEADERS HEAD,
FUN_TRX_BATCHES BTCH
WHERE DIST.TRX_ID = HEAD.TRX_ID
AND HEAD.BATCH_ID = BTCH.BATCH_ID
AND HEAD.TRX_ID = p_trx_id
GROUP BY FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
HEAD.TO_LEDGER_ID),
DIST.CCID,'GL_BALANCING'),
DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
HAVING SUM(NVL(DIST.AMOUNT_DR,0.00)) <> SUM(NVL(DIST.AMOUNT_CR,0.00))
AND SUM(NVL(DIST.AMOUNT_CR,0.00)) <> 0.00);
SELECT MIN(DIST_BSV)
INTO l_reci_bsv
FROM (SELECT FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
HEAD.TO_LEDGER_ID),
DIST.CCID,'GL_BALANCING') DIST_BSV,
DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
FROM FUN_DIST_LINES DIST,
FUN_TRX_HEADERS HEAD,
FUN_TRX_BATCHES BTCH
WHERE DIST.TRX_ID = HEAD.TRX_ID
AND HEAD.BATCH_ID = BTCH.BATCH_ID
AND HEAD.TRX_ID = p_trx_id
GROUP BY FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
HEAD.TO_LEDGER_ID),
DIST.CCID,'GL_BALANCING'),
DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
HAVING SUM(NVL(DIST.AMOUNT_DR,0.00)) <> SUM(NVL(DIST.AMOUNT_CR,0.00))
AND SUM(NVL(DIST.AMOUNT_DR,0.00)) <> 0.00);
SELECT MIN(DIST_BSV)
INTO l_reci_bsv
FROM (SELECT FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
HEAD.TO_LEDGER_ID),
DIST.CCID,'GL_BALANCING') DIST_BSV,
DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
FROM FUN_DIST_LINES DIST,
FUN_TRX_HEADERS HEAD,
FUN_TRX_BATCHES BTCH
WHERE DIST.TRX_ID = HEAD.TRX_ID
AND HEAD.BATCH_ID = BTCH.BATCH_ID
AND HEAD.TRX_ID = p_trx_id
GROUP BY FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
HEAD.TO_LEDGER_ID),
DIST.CCID,'GL_BALANCING'),
DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
HAVING SUM(NVL(DIST.AMOUNT_DR,0.00)) <> SUM(NVL(DIST.AMOUNT_CR,0.00))
AND SUM(NVL(DIST.AMOUNT_CR,0.00)) <> 0.00);
SELECT MIN(DIST_BSV)
INTO l_init_bsv
FROM (SELECT FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
HEAD.TO_LEDGER_ID),
DIST.CCID,'GL_BALANCING') DIST_BSV,
DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
FROM FUN_DIST_LINES DIST,
FUN_TRX_HEADERS HEAD,
FUN_TRX_BATCHES BTCH
WHERE DIST.TRX_ID = HEAD.TRX_ID
AND HEAD.BATCH_ID = BTCH.BATCH_ID
AND HEAD.TRX_ID = p_trx_id
GROUP BY FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
HEAD.TO_LEDGER_ID),
DIST.CCID,'GL_BALANCING'),
DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
HAVING SUM(NVL(DIST.AMOUNT_DR,0.00)) <> SUM(NVL(DIST.AMOUNT_CR,0.00))
AND SUM(NVL(DIST.AMOUNT_DR,0.00)) <> 0.00);
SELECT l.chart_of_accounts_id
INTO l_from_coa_id
from gl_ledgers l
WHERE l.ledger_id = l_trx_dtl_rec.from_ledger_id;
l_insert_flag := 'N';
SELECT 'Y' INTO l_insert_flag
FROM FUN_INTER_ACCOUNTS_V
WHERE FROM_LE_ID = l_init_dist_tbl(1).initiator_le_id
AND LEDGER_ID = l_init_dist_tbl(1).ledger_id
AND TO_LE_ID = l_init_dist_tbl(1).recipient_le_id
AND CCID = l_ccid
AND TYPE = 'R'
AND TRANS_BSV = l_init_bsv
AND TP_BSV = l_reci_bsv;
l_insert_flag := 'N';
l_insert_flag := 'Y';
IF (l_insert_flag = 'N') THEN
INSERT INTO FUN_INTER_ACCOUNTS_ADDL(FROM_LE_ID
, LEDGER_ID
, TO_LE_ID
, CCID
, TYPE
, START_DATE
, DEFAULT_FLAG
, OBJECT_VERSION_NUMBER
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, TRANS_BSV
, TP_BSV
)
VALUES(l_init_dist_tbl(1).initiator_le_id
, l_init_dist_tbl(1).ledger_id
, l_init_dist_tbl(1).recipient_le_id
, l_ccid
, 'R'
, SYSDATE
, NULL
, 1
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, fnd_global.login_id
, l_init_bsv
, l_reci_bsv
);
SELECT FUN_DIST_LINES_S.nextval
INTO l_new_dist_tbl(l_new_index).dist_id
FROM dual;
l_new_dist_tbl(l_new_index).last_updated_by := FND_GLOBAL.USER_ID;
l_new_dist_tbl(l_new_index).last_update_date := SYSDATE;
l_new_dist_tbl(l_new_index).last_update_login := FND_GLOBAL.LOGIN_ID;
SELECT l.chart_of_accounts_id
INTO l_to_coa_id
from gl_ledgers l
WHERE l.ledger_id = l_trx_dtl_rec.to_ledger_id;
l_insert_flag := 'N';
SELECT 'Y' INTO l_insert_flag
FROM FUN_INTER_ACCOUNTS_V
WHERE FROM_LE_ID = l_reci_dist_tbl(1).recipient_le_id
AND LEDGER_ID = l_reci_dist_tbl(1).ledger_id
AND TO_LE_ID = l_reci_dist_tbl(1).initiator_le_id
AND CCID = l_ccid
AND TYPE = 'P'
AND TRANS_BSV = l_reci_bsv
AND TP_BSV = l_init_bsv;
l_insert_flag := 'N';
l_insert_flag := 'Y';
IF (l_insert_flag = 'N') THEN
INSERT INTO FUN_INTER_ACCOUNTS_ADDL(FROM_LE_ID
, LEDGER_ID
, TO_LE_ID
, CCID
, TYPE
, START_DATE
, DEFAULT_FLAG
, OBJECT_VERSION_NUMBER
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, TRANS_BSV
, TP_BSV
)
VALUES(l_reci_dist_tbl(1).recipient_le_id
, l_reci_dist_tbl(1).ledger_id
, l_reci_dist_tbl(1).initiator_le_id
, l_ccid
, 'P'
, SYSDATE
, NULL
, 1
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, fnd_global.login_id
, l_reci_bsv
, l_init_bsv
);
SELECT FUN_DIST_LINES_S.nextval
INTO l_new_dist_tbl(l_new_index).dist_id
FROM dual;
l_new_dist_tbl(l_new_index).last_updated_by := FND_GLOBAL.USER_ID;
l_new_dist_tbl(l_new_index).last_update_date := SYSDATE;
l_new_dist_tbl(l_new_index).last_update_login := FND_GLOBAL.LOGIN_ID;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'If Sucessful Delete existing lines and insert.');
DELETE fun_dist_lines
WHERE trx_id = l_trx_id
AND dist_type_flag IN ('R','P');
INSERT into FUN_DIST_LINES
VALUES l_new_dist_tbl(i);
SELECT b.batch_id,
b.initiator_id initiator_id
,b.from_le_id from_le_id
,b.from_ledger_id from_ledger_id
,b.currency_code currency_code
,b.attribute1 bat_attribute1
,b.attribute2 bat_attribute2
,b.attribute3 bat_attribute3
,b.attribute4 bat_attribute4
,b.attribute5 bat_attribute5
,b.attribute6 bat_attribute6
,b.attribute7 bat_attribute7
,b.attribute8 bat_attribute8
,b.attribute9 bat_attribute9
,b.attribute10 bat_attribute10
,b.attribute11 bat_attribute11
,b.attribute12 bat_attribute12
,b.attribute13 bat_attribute13
,b.attribute14 bat_attribute14
,b.attribute15 bat_attribute15
,b.attribute_category bat_attribute_category
,t.trx_id trx_id
,t.recipient_id recipient_id
,t.to_le_id to_le_id
,t.to_ledger_id to_ledger_id
,t.attribute1 trx_attribute1
,t.attribute2 trx_attribute2
,t.attribute3 trx_attribute3
,t.attribute4 trx_attribute4
,t.attribute5 trx_attribute5
,t.attribute6 trx_attribute6
,t.attribute7 trx_attribute7
,t.attribute8 trx_attribute8
,t.attribute9 trx_attribute9
,t.attribute10 trx_attribute10
,t.attribute11 trx_attribute11
,t.attribute12 trx_attribute12
,t.attribute13 trx_attribute13
,t.attribute14 trx_attribute14
,t.attribute15 trx_attribute15
,t.attribute_category trx_attribute_category
,y.trx_type_id trx_type_id
,y.manual_approve_flag manual_approve_flag
,y.allow_invoicing_flag allow_invoicing_flag
,y.vat_taxable_flag vat_taxable_flag
,y.allow_interest_accrual_flag allow_interest_accrual_flag
,y.attribute1 typ_attribute1
,y.attribute2 typ_attribute2
,y.attribute3 typ_attribute3
,y.attribute4 typ_attribute4
,y.attribute5 typ_attribute5
,y.attribute6 typ_attribute6
,y.attribute7 typ_attribute7
,y.attribute8 typ_attribute8
,y.attribute9 typ_attribute9
,y.attribute10 typ_attribute10
,y.attribute11 typ_attribute11
,y.attribute12 typ_attribute12
,y.attribute13 typ_attribute13
,y.attribute14 typ_attribute14
,y.attribute15 typ_attribute15
,y.attribute_category typ_attribute_category
,l.chart_of_accounts_id coa_id
,b.batch_date
,b.gl_date
FROM fun_trx_batches b,
fun_trx_headers t,
fun_trx_types_vl y,
gl_ledgers l
WHERE b.batch_id = t.batch_id
AND b.trx_type_id = y.trx_type_id
AND t.to_ledger_id = l.ledger_id
AND t.trx_id = p_trx_id;
SELECT amb_context_code,
account_definition_code
FROM fun_trx_acct_definitions
WHERE ledger_id = p_ledger_id;
SELECT vals.segment_value
FROM gl_ledger_le_bsv_specific_v vals
WHERE vals.legal_entity_id = p_le_id
AND vals.ledger_id = p_ledger_id
AND p_gl_date BETWEEN Nvl(vals.start_date, p_gl_date) AND Nvl(vals.end_date, p_gl_date)
AND (SELECT COUNT(*)
FROM gl_ledger_le_bsv_specific_v vals1
WHERE vals1.legal_entity_id = p_le_id
AND vals1.ledger_id = p_ledger_id
AND p_gl_date BETWEEN Nvl(vals1.start_date, p_gl_date) AND Nvl(vals1.end_date, p_gl_date)) = 1;
select CCID
into l_init_ccid
from fun_dist_lines
where trx_id = p_trx_id
and party_type_flag = 'I'
and dist_type_flag = 'L'
and rownum = 1;
fun_trx_pvt.update_trx_status
(p_api_version => 1.0,
x_return_status => l_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trx_id => l_trx_id,
p_update_status_to => 'ERROR');
select 'X'
from WF_USER_ROLE_ASSIGNMENTS role
where role.role_name = 'FUN_ADHOC_RECI_'||trx_id
and role.user_name = FND_GLOBAL.USER_NAME;