[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Insert_Header(
p_recipient_name In Fun_Interface_Headers.recipient_name%type,
p_to_le_name In Fun_Interface_Headers.to_le_name%type,
p_trx_tbl In FUN_TRX_PUB.full_trx_tbl_type) Is
Begin
If p_trx_tbl.count = 1 Then
Insert Into Fun_Interface_Headers(
trx_id,
trx_number,
recipient_id,
recipient_name,
to_le_id,
to_le_name,
to_ledger_id,
batch_id,
init_amount_cr,
init_amount_dr,
invoicing_rule_flag,
from_recurring_trx_id,
initiator_instance_flag,
recipient_instance_flag,
description,
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,
import_status_code)
Values(
p_trx_tbl(1).trx_id,
p_trx_tbl(1).trx_number,
p_trx_tbl(1).recipient_id,
p_recipient_name,
p_trx_tbl(1).to_le_id,
p_to_le_name,
p_trx_tbl(1).to_ledger_id,
p_trx_tbl(1).batch_id,
p_trx_tbl(1).init_amount_cr,
p_trx_tbl(1).init_amount_dr,
p_trx_tbl(1).invoice_flag,
p_trx_tbl(1).from_recurring_trx_id,
p_trx_tbl(1).initiator_instance_flag,
p_trx_tbl(1).recipient_instance_flag,
p_trx_tbl(1).description,
p_trx_tbl(1).attribute1,
p_trx_tbl(1).attribute2,
p_trx_tbl(1).attribute3,
p_trx_tbl(1).attribute4,
p_trx_tbl(1).attribute5,
p_trx_tbl(1).attribute6,
p_trx_tbl(1).attribute7,
p_trx_tbl(1).attribute8,
p_trx_tbl(1).attribute9,
p_trx_tbl(1).attribute10,
p_trx_tbl(1).attribute11,
p_trx_tbl(1).attribute12,
p_trx_tbl(1).attribute13,
p_trx_tbl(1).attribute14,
p_trx_tbl(1).attribute15,
p_trx_tbl(1).attribute_category,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
null);
End Insert_Header;
Procedure Insert_Dists(
p_dist_lines_tbl In FUN_TRX_PUB.full_dist_line_tbl_type,
p_count In Number) Is
Begin
Insert Into Fun_Interface_Dist_Lines(
trx_id,
dist_id,
batch_dist_id,
dist_number,
party_id,
party_type_flag,
dist_type_flag,
amount_cr,
amount_dr,
ccid,
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,
import_status_code,
description)
Values(
p_dist_lines_tbl(p_count).trx_id,
p_dist_lines_tbl(p_count).dist_id,
null,
p_dist_lines_tbl(p_count).dist_number,
p_dist_lines_tbl(p_count).party_id,
p_dist_lines_tbl(p_count).party_type_flag,
p_dist_lines_tbl(p_count).dist_type_flag,
p_dist_lines_tbl(p_count).amount_cr,
p_dist_lines_tbl(p_count).amount_dr,
p_dist_lines_tbl(p_count).ccid,
p_dist_lines_tbl(p_count).attribute1,
p_dist_lines_tbl(p_count).attribute2,
p_dist_lines_tbl(p_count).attribute3,
p_dist_lines_tbl(p_count).attribute4,
p_dist_lines_tbl(p_count).attribute5,
p_dist_lines_tbl(p_count).attribute6,
p_dist_lines_tbl(p_count).attribute7,
p_dist_lines_tbl(p_count).attribute8,
p_dist_lines_tbl(p_count).attribute9,
p_dist_lines_tbl(p_count).attribute10,
p_dist_lines_tbl(p_count).attribute11,
p_dist_lines_tbl(p_count).attribute12,
p_dist_lines_tbl(p_count).attribute13,
p_dist_lines_tbl(p_count).attribute14,
p_dist_lines_tbl(p_count).attribute15,
p_dist_lines_tbl(p_count).attribute_category,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
null,
p_dist_lines_tbl(p_count).description);
End Insert_Dists;
Procedure Insert_Batch(
p_batch_rec In FUN_TRX_PUB.full_batch_rec_type,
p_initiator_name In Fun_Interface_Batches.initiator_name%type,
p_trx_type_name In Fun_Interface_Batches.trx_type_name%type,
p_from_le_name In Fun_Interface_Headers.to_le_name%type,
p_insert_flag In Varchar2) Is
l_group_id NUMBER;
SELECT fun_interface_controls_s.nextval
INTO l_group_id
FROM dual;
If p_insert_flag = 'Y' Then
Insert Into Fun_Interface_Batches(
source,
group_id,
batch_id,
batch_number,
initiator_id,
initiator_name,
from_le_id,
from_le_name,
from_ledger_id,
control_total,
running_total_cr,
running_total_dr,
currency_code,
exchange_rate_type,
description,
trx_type_id,
trx_type_code,
trx_type_name,
gl_date,
batch_date,
reject_allowed_flag,
from_recurring_batch_id,
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,
import_status_code,
note)
Values(
'Global Intercompany',
l_group_id,
p_batch_rec.batch_id,
p_batch_rec.batch_number,
p_batch_rec.initiator_id,
p_initiator_name,
p_batch_rec.from_le_id,
p_from_le_name,
p_batch_rec.from_ledger_id,
p_batch_rec.control_total,
null,
null,
p_batch_rec.currency_code,
p_batch_rec.exchange_rate_type,
p_batch_rec.description,
p_batch_rec.trx_type_id,
p_batch_rec.trx_type_code,
p_trx_type_name,
p_batch_rec.gl_Date,
p_batch_rec.batch_Date,
p_batch_rec.reject_allow_flag,
p_batch_rec.from_recurring_batch_id,
p_batch_rec.attribute1,
p_batch_rec.attribute2,
p_batch_rec.attribute3,
p_batch_rec.attribute4,
p_batch_rec.attribute5,
p_batch_rec.attribute6,
p_batch_rec.attribute7,
p_batch_rec.attribute8,
p_batch_rec.attribute9,
p_batch_rec.attribute10,
p_batch_rec.attribute11,
p_batch_rec.attribute12,
p_batch_rec.attribute13,
p_batch_rec.attribute14,
p_batch_rec.attribute15 ,
p_batch_rec.attribute_category,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
null,
p_batch_rec.note);
Insert into Fun_Interface_Controls(
source,
group_id)
Values(
'Global Intercompany',
l_group_id);
Elsif p_insert_flag = 'N' Then
Update Fun_Interface_Batches Set
currency_code = p_batch_rec.currency_code,
exchange_rate_type = p_batch_rec.exchange_rate_type,
description = p_batch_rec.description,
trx_type_id = p_batch_rec.trx_type_id,
trx_type_code = p_batch_rec.trx_type_code,
trx_type_name = p_trx_type_name,
gl_date = p_batch_rec.gl_Date,
batch_date = p_batch_rec.batch_Date,
reject_allowed_flag = p_batch_rec.reject_allow_flag,
from_recurring_batch_id = p_batch_rec.from_recurring_batch_id,
attribute1 = p_batch_rec.attribute1,
attribute2 = p_batch_rec.attribute2,
attribute3 = p_batch_rec.attribute3,
attribute4 = p_batch_rec.attribute4,
attribute5 = p_batch_rec.attribute5,
attribute6 = p_batch_rec.attribute6,
attribute7 = p_batch_rec.attribute7,
attribute8 = p_batch_rec.attribute8,
attribute9 = p_batch_rec.attribute9,
attribute10 = p_batch_rec.attribute10,
attribute11 = p_batch_rec.attribute11,
attribute12 = p_batch_rec.attribute12,
attribute13 = p_batch_rec.attribute13,
attribute14 = p_batch_rec.attribute14,
attribute15 = p_batch_rec.attribute15,
attribute_category = p_batch_rec.attribute_category,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
note = p_batch_rec.note
Where batch_number = p_batch_rec.batch_number;
End Insert_Batch;
p_batch_insert In Varchar2) Is
l_batch_rec Fun_Trx_Pvt.batch_rec_type;
l_insert Varchar2(1);
Select legal_entity_id
From Xle_Firstparty_Information_V
Where party_id = cp_le_party_id;
IF (p_batch_insert ='Y') THEN
l_insert := FND_API.G_TRUE;
l_insert := FND_API.G_FALSE;
l_insert,
l_batch_rec,
l_trx_tbl,
l_init_dist_tbl,
l_dist_lines_tbl);
l_trx_tbl.Delete;
l_init_dist_tbl.Delete;
l_dist_lines_tbl.Delete;
Select *
From Fun_Interface_Batches
Where batch_number = cp_batch_number;
Select Fun_Trx_Batches_S.nextval batch_id
From Dual;
Select hzp.party_id
From Hz_Parties Hzp, hz_party_usg_assignments hu
Where hzp.party_type = 'ORGANIZATION'
And hzp.party_name = cp_party_name
And hzp.party_id = hu.party_id
And hu.party_usage_code = 'INTERCOMPANY_ORG';
Select legal_entity_id
From Xle_Firstparty_Information_V
Where party_id = cp_le_party_id;
Select name
From xle_firstparty_information_v
Where party_id = cp_party_id;
Select trx_type_code, trx_type_id, allow_invoicing_flag
From Fun_Trx_Types_Vl
Where trx_type_name = cp_trx_type_name;
Select *
From Fun_Interface_Headers
Where batch_id = cp_batch_id
And trx_number = cp_trx_number;
Select Fun_Trx_Headers_S.nextval trx_id
From Dual;
Select count(distinct trx_number) dupl_reci_count
From Fun_Interface_Headers
Where batch_id = cp_batch_id
And trx_number <> cp_trx_number
And recipient_name = cp_party_name;
Select *
From Fun_Interface_Dist_Lines
Where trx_id = cp_trx_id
And party_id = cp_party_id
And ccid = cp_ccid
And party_type_flag = cp_party_type_flag
And dist_number=cp_dist_number;
Select Fun_Dist_Lines_S.nextval dist_id
From dual;
Select chart_of_accounts_id
From Hz_Parties hzp,
xle_firstparty_information_v xfi ,
Gl_Ledger_Le_V led
Where hzp.party_name = cp_party_name
And fun_tca_pkg.get_le_id(hzp.party_id) = xfi.party_id
And xfi.legal_entity_id = led.legal_entity_id
And led.ledger_category_code = 'PRIMARY';
Select fa.application_short_name appl_short_name
From Fnd_Id_Flex_Structures fs, Fnd_Application fa
Where fs.application_id = fa.application_id
And id_flex_num = cp_chart_of_accounts_id
And id_flex_code = 'GL#';
Select nvl(Sum(nvl(amount_dr,0)),0) Dr_Sum,
nvl(Sum(nvl(amount_cr,0)),0) Cr_Sum
From Fun_Interface_Dist_Lines
Where trx_id = cp_trx_id
And party_id = cp_party_id
And party_type_flag = cp_party_type_flag;
l_batch_insert Varchar2(1);
l_trx_insert Varchar2(1);
l_init_dist_line_insert Varchar2(1);
l_reci_dist_line_insert Varchar2(1);
select length(p_batch_number) into l_length from dual; --6846666
l_batch_insert := 'N';
l_batch_insert := 'Y';
l_trx_insert := 'N';
l_trx_insert := 'Y';
l_init_dist_line_insert := 'N';
l_init_dist_line_insert := 'Y';
'GL_CODE_COMBINATIONS', -- select_comb_from_view
NULL, -- no_combmsg
NULL -- where_clause_msg
) Then
Raise Ccid_Gen_Excpt;
l_reci_dist_line_insert := 'N';
l_reci_dist_line_insert := 'Y';
l_batch_insert);
Insert_Batch(
l_batch_rec,
p_initiator_name,
p_trx_type_name,
l_from_le_name,
l_batch_insert);
If nvl(l_trx_insert,'N') = 'Y' Then
Insert_Header(
p_recipient_name,
l_to_le_name,
l_trx_tbl);
If nvl(l_init_dist_line_insert,'N') = 'Y' Then
Insert_Dists(
l_dist_lines_tbl,
l_count);
If nvl(l_reci_dist_line_insert,'N') = 'Y' Then
Insert_Dists(
l_dist_lines_tbl,
l_count + 1);
Procedure Delete_Batch(
p_batch_number In Fun_Interface_Batches.batch_number%type) IS
BEGIN
DELETE Fun_Interface_Dist_Lines
WHERE trx_id in (SELECT trx_id
FROM Fun_Interface_Headers fih,
fun_interface_batches fib
WHERE fih.batch_id = fib.batch_id
AND fib.source = 'Global Intercompany'
AND fib.batch_number = p_batch_number);
DELETE Fun_Interface_Headers
WHERE batch_id in (SELECT batch_id
FROM fun_interface_batches
WHERE source = 'Global Intercompany'
AND batch_number = p_batch_number);
DELETE Fun_Interface_Controls
WHERE GROUP_ID IN (SELECT GROUP_ID
FROM fun_interface_batches
WHERE source = 'Global Intercompany'
AND batch_number = p_batch_number)
AND SOURCE = 'Global Intercompany';
DELETE fun_interface_batches
WHERE source = 'Global Intercompany'
AND batch_number = p_batch_number;
SELECT trxh.trx_number, party_type_flag, dist_amount,
DECODE(init_amount_cr, null, init_amount_dr, init_amount_cr) trx_amount
FROM fun_interface_headers trxH,
(SELECT trxH.trx_id,
fd.dist_type_flag,
party_type_flag ,
sum(decode(fd.amount_cr, null, fd.amount_dr, fd.amount_cr)) DIST_AMOUNT
FROM fun_interface_headers trxH, FUN_INTERFACE_DIST_LINES fd
WHERE trxH.trx_id = fd.trx_id
AND dist_type_flag = 'L'
GROUP BY trxH.trx_id,
fd.dist_type_flag,
party_type_flag
ORDER BY trxH.trx_id) fun_dists
WHERE fun_dists.trx_id = trxh.trx_id
AND trxh.batch_id in (SELECT batch_id
FROM fun_interface_batches
WHERE source = 'Global Intercompany'
AND batch_number = p_batch_number)
ORDER BY party_type_flag;
Delete_Batch(p_batch_number);
Delete_Batch(p_batch_number);
Delete_Batch(p_batch_number);