[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT p.party_id
FROM hz_parties p
WHERE p.party_name = l_initiator_name
and p.party_type='ORGANIZATION'
and exists (select u.party_usg_assignment_id from hz_party_usg_assignments u
where u.party_usage_code = 'INTERCOMPANY_ORG'
and u.party_id = p.party_id);
SELECT legal_entity_id
FROM xle_firstparty_information_v
WHERE name=l_from_le_name;
/*SELECT hp.party_id
FROM hz_parties hp, hz_code_assignments hca
WHERE hp.party_name = l_From_Le_name
and hca.owner_table_name like 'HZ_PARTIES'
and hca.owner_table_id = hp.party_id
and hca.class_code like 'LEGAL_ENTITY'
and hca.class_category like 'LEGAL_FUNCTION';*/
SELECT ledger_id
FROM gl_ledger_le_v
WHERE legal_entity_id = l_from_le_id
AND ledger_category_code = 'PRIMARY';
SELECT trx_type_id
FROM fun_Trx_types_vl
WHERE trx_type_name = l_Trx_type_name;
SELECT trx_type_id
FROM fun_Trx_types_vl
WHERE trx_type_code = l_Trx_type_code;
SELECT trx_type_code
FROM fun_Trx_types_vl
WHERE trx_type_id = l_Trx_type_id;
SELECT legal_entity_id
FROM xle_firstparty_information_v
WHERE party_id = l_party_id;
SELECT p.party_id
FROM hz_parties p
WHERE p.party_name = l_recipient_name
and p.party_type='ORGANIZATION'
and exists (select u.party_usg_assignment_id from hz_party_usg_assignments u
where u.party_usage_code = 'INTERCOMPANY_ORG'
and u.party_id = p.party_id);
SELECT legal_entity_id
FROM xle_firstparty_information_v
WHERE name=l_to_le_name;
/*SELECT hp.party_id
FROM hz_parties hp, hz_code_assignments hca
WHERE hp.party_name = l_to_Le_name
and hca.owner_table_name like 'HZ_PARTIES'
and hca.owner_table_id = hp.party_id;
SELECT ledger_id
FROM gl_ledger_le_v
WHERE legal_entity_id = l_to_le_id
AND ledger_category_code = 'PRIMARY';
SELECT legal_entity_id
FROM xle_firstparty_information_v
WHERE party_id = l_party_id;
Select * from fun_interface_headers
Where batch_id = l_batch_id;
Select * from fun_interface_dist_lines
Where trx_id = l_trx_id;
Select * from fun_interface_batchdists
Where batch_id = l_batch_id;
Select allow_invoicing_flag
from fun_trx_types_vl
where trx_type_id = c_trx_type_id;
SELECT hzp.party_id
INTO l_user_id
FROM hz_parties hzp,
fnd_user u,
per_all_people_f pap,
(SELECT fnd_global.user_id() AS
user_id
FROM dual)
curr
WHERE curr.user_id = u.user_id
AND u.employee_id = pap.person_id
AND pap.party_id = hzp.party_id
AND SYSDATE between u.start_date AND nvl(u.end_date,sysdate) --bug 10026066
AND SYSDATE between pap.effective_start_date AND pap.effective_end_date; --bug #10021420
/* Delete any rejections from previous failed imports*/
Delete from fun_interface_rejections ftr
where ftr.batch_id in(select ftb.batch_id
from fun_interface_batches ftb
where ftb.source = p_source
and ftb.group_id = p_group_id);
SELECT exchg_rate_type, default_currency
INTO l_exchg_rate_type, l_default_currency
FROM fun_system_options;
/*Select Batches for a given Source and Group.*/
Print('Main Package ~~~'||'Reject Allowed Derived from System Options');
SELECT * FROM fun_interface_batches
WHERE source = p_source
AND group_id = p_group_id
AND batch_id = NVL(p_batch_id,batch_id)
AND batch_id IN(SELECT DISTINCT fib.batch_id
FROM fun_interface_batches fib,
hz_parties hzp,
fnd_grants fg,
fnd_object_instance_sets fois,
hz_relationships hzr,
hz_org_contacts hc,
hz_org_contact_roles hcr
WHERE hzp.party_type = 'ORGANIZATION'
AND EXISTS
(SELECT 1
FROM hz_party_usg_assignments hua
WHERE hua.party_id = hzp.party_id
AND hua.party_usage_code = 'INTERCOMPANY_ORG'
AND hua.effective_start_date <= sysdate
AND(hua.effective_end_date >= sysdate OR effective_end_date IS NULL))
AND fg.parameter1 = to_char(hzp.party_id)
AND fg.instance_set_id = fois.instance_set_id
AND fois.instance_set_name = 'FUN_TRX_BATCHES_SET'
AND 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 = hzp.party_id
AND fg.grantee_key = l_grantee_key
AND hzp.party_id = fib.initiator_id --10030555
AND hc.party_relationship_id = hzr.relationship_id
AND hcr.org_contact_id = hc.org_contact_id
AND hcr.role_type = 'INTERCOMPANY_CONTACT_FOR'
AND hzr.subject_id = l_user_id
AND hzr.status = 'A'))
LOOP
l_batch_count := l_batch_count + 1;
SELECT numbering_type
INTO l_numbering_type
FROM fun_system_options;
/* SELECT FUN_SEQ_S1.nextval
INTO l_batch_num
FROM dual;
SELECT nvl(sum(init_amount_dr),0), nvl(sum(init_amount_cr),0)
INTO l_running_total_dr, l_running_total_cr
FROM fun_interface_headers
WHERE batch_id = curr_batch.batch_id;
Print('Main Package ~~~'||' Update the missing Attributes for a Batch in interface tables');
Update fun_interface_batches
set initiator_id=l_batch_rec.initiator_id,
from_le_id=l_batch_rec.from_le_id,
from_ledger_id=l_batch_rec.from_ledger_id,
trx_type_id=l_batch_rec.trx_type_id,
trx_type_code=l_batch_rec.trx_type_code
where batch_id=curr_batch.batch_id;
Update fun_trx_headers
set recipient_id=l_trx_tbl(l_count).recipient_id,
to_ledger_id=l_trx_tbl(l_count).to_ledger_id,
to_le_id=l_trx_tbl(l_count).to_le_id
where trx_id=curr_head.trx_id;
Print('Main Package ~~~'||' Call the Public API to Validate and Insert Intercompany Transactions');
p_insert => FND_API.G_TRUE,
p_batch_rec => l_batch_rec,
p_trx_tbl => l_trx_tbl,
p_init_dist_tbl => l_init_dist_tbl,
p_dist_lines_tbl => l_dist_lines_tbl,
p_debug =>p_debug
);
Print('Main Package ~~~'||'Validation and Insertion Complete with Status' || l_return_status);
UPDATE fun_interface_batches set import_status_code = Overall_Status
Where batch_id = curr_batch.batch_id;
l_trx_tbl.delete;
l_init_dist_tbl.delete;
l_dist_lines_tbl.delete;
/*update control table with the request id */
update fun_interface_controls
set request_id = v_request_id,
date_processed = sysdate
where source = p_source
and group_id = p_group_id;
Delete from fun_interface_dist_lines where
Trx_id in (select trx_id from fun_interface_headers where
batch_id in (select batch_id from fun_interface_batches where
source = p_source and group_id = p_group_id and import_status_code = 'A')) ;
Delete from fun_interface_batchdists where
batch_id in (select batch_id from fun_interface_batches where
source = p_source and group_id = p_group_id and import_status_code = 'A');
Delete from fun_interface_headers where
Batch_id in (select batch_id from fun_interface_batches where
source = p_source and group_id = p_group_id and import_status_code = 'A');
Delete from fun_interface_batches where
source = p_source and group_id = p_group_id
and import_status_code = 'A';
select count(*) into l_count
from fun_interface_rejections ftr
where ftr.batch_id in(select ftb.batch_id
from fun_interface_batches ftb
where ftb.source = p_source
and ftb.group_id = p_group_id);
Delete from fun_interface_controls where source = p_source
and group_id = p_group_id;
DELETE FROM fun_interface_dist_lines
WHERE trx_id IN
(SELECT trx_id
FROM fun_interface_headers
WHERE batch_id = p_batch_id)
;
DELETE FROM fun_interface_batchdists
WHERE batch_id = p_batch_id;
DELETE FROM fun_interface_headers
WHERE batch_id = p_batch_id;
DELETE FROM fun_interface_batches
WHERE batch_id = p_batch_id
AND import_status_code = 'R';
DELETE FROM fun_interface_rejections
where batch_id=p_batch_id;
SELECT tag
INTO l_encoding
FROM fnd_lookup_values
WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
AND lookup_code =
(SELECT VALUE
FROM v$nls_parameters
WHERE parameter = 'NLS_CHARACTERSET')
AND LANGUAGE = 'US';
SELECT hzp.party_id
INTO l_user_id
FROM hz_parties hzp,
fnd_user u,
per_all_people_f pap,
(SELECT fnd_global.user_id() AS
user_id
FROM dual)
curr
WHERE curr.user_id = u.user_id
AND u.employee_id = pap.person_id
AND pap.party_id = hzp.party_id
AND trunc(SYSDATE) between trunc(u.start_date) AND trunc(nvl(u.end_date, sysdate))
AND SYSDATE between pap.effective_start_date AND pap.effective_end_date; --bug #10021420
l_debug_info := 'Select Group ids and Batches...';
(SELECT DISTINCT group_id
FROM fun_interface_batches
WHERE source = p_source
AND(decode(p_group_id, NULL, 1, group_id)) =(decode(p_group_id, NULL, 1, p_group_id))
AND import_status_code = 'R')
LOOP
l_batch_count := 0;
(SELECT DISTINCT currency_code
FROM fun_interface_batches
WHERE group_id = rec.group_id
AND import_status_code = 'R')
LOOP
put_starttag('CURRENCY_CODE_RECORD');
(SELECT DISTINCT from_le_name
FROM fun_interface_batches
WHERE group_id = rec.group_id
AND currency_code = rec2.currency_code
AND import_status_code = 'R')
LOOP
put_starttag('FROM_LE_NAME_RECORD');
l_qryctx := dbms_xmlgen.newcontext('SELECT FIB.GROUP_ID GROUP_ID,
FIB.CURRENCY_CODE CURRENCY_CODE,
FIB.FROM_LE_NAME FROM_LE_NAME,
FIB.FROM_LE_ID FROM_LE_ID,
FIB.INITIATOR_NAME INITIATOR_NAME,
FIB.INITIATOR_ID INITIATOR_ID,
FIB.BATCH_NUMBER BATCH_NUMBER,
FIB.BATCH_DATE BATCH_DATE,
FIH.RECIPIENT_NAME RECIPIENT_NAME,
DECODE(FIH.INIT_AMOUNT_CR,NULL,0,FIH.INIT_AMOUNT_CR) INIT_AMOUNT_CR,
DECODE(FIH.INIT_AMOUNT_DR,NULL,0,FIH.INIT_AMOUNT_DR) INIT_AMOUNT_DR
FROM FUN_INTERFACE_BATCHES FIB,
FUN_INTERFACE_HEADERS FIH,
HZ_PARTIES HZP,
FND_GRANTS FG,
FND_OBJECT_INSTANCE_SETS FOIS,
HZ_RELATIONSHIPS HZR,
HZ_ORG_CONTACTS HC,
HZ_ORG_CONTACT_ROLES HCR
WHERE FIB.BATCH_ID=FIH.BATCH_ID
AND FIB.GROUP_ID=:GROUP_ID
AND FIB.IMPORT_STATUS_CODE=''R''
AND FIB.CURRENCY_CODE=:CURRENCY_CODE
AND FIB.FROM_LE_NAME=:FROM_LE_NAME
AND HZP.PARTY_TYPE = ''ORGANIZATION''
AND EXISTS
(SELECT 1
FROM HZ_PARTY_USG_ASSIGNMENTS HUA
WHERE HUA.PARTY_ID = HZP.PARTY_ID
AND HUA.PARTY_USAGE_CODE = ''INTERCOMPANY_ORG''
AND HUA.EFFECTIVE_START_DATE <= SYSDATE
AND(HUA.EFFECTIVE_END_DATE >= SYSDATE OR EFFECTIVE_END_DATE IS NULL))
AND FG.PARAMETER1 = TO_CHAR(HZP.PARTY_ID)
AND FG.INSTANCE_SET_ID = FOIS.INSTANCE_SET_ID
AND FOIS.INSTANCE_SET_NAME = ''FUN_TRX_BATCHES_SET''
AND FG.GRANTEE_KEY = :GRANTEE_KEY
AND 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=HZP.PARTY_ID
AND HZP.PARTY_NAME = FIB.INITIATOR_NAME
AND HC.PARTY_RELATIONSHIP_ID = HZR.RELATIONSHIP_ID
AND HCR.ORG_CONTACT_ID = HC.ORG_CONTACT_ID
AND HCR.ROLE_TYPE = ''INTERCOMPANY_CONTACT_FOR''
AND HZR.SUBJECT_ID = :SUBJECT_ID
AND HZR.STATUS = ''A''');
(SELECT DISTINCT fib.batch_id
FROM fun_interface_batches fib,
hz_parties hzp,
fnd_grants fg,
fnd_object_instance_sets fois,
hz_relationships hzr,
hz_org_contacts hc,
hz_org_contact_roles hcr
WHERE fib.group_id = rec.group_id
AND fib.import_status_code = 'R'
AND fib.currency_code = rec2.currency_code
AND fib.from_le_name = rec3.from_le_name
AND hzp.party_type = 'ORGANIZATION'
AND EXISTS
(SELECT 1
FROM hz_party_usg_assignments hua
WHERE hua.party_id = hzp.party_id
AND hua.party_usage_code = 'INTERCOMPANY_ORG'
AND hua.effective_start_date <= sysdate
AND(hua.effective_end_date >= sysdate OR effective_end_date IS NULL))
AND fg.parameter1 = to_char(hzp.party_id)
AND fg.instance_set_id = fois.instance_set_id
AND fois.instance_set_name = 'FUN_TRX_BATCHES_SET'
AND fg.grantee_key = l_grantee_key
AND 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 = hzp.party_id
AND hzp.party_name = fib.initiator_name
AND hc.party_relationship_id = hzr.relationship_id
AND hcr.org_contact_id = hc.org_contact_id
AND hcr.role_type = 'INTERCOMPANY_CONTACT_FOR'
AND hzr.subject_id = l_user_id
AND hzr.status = 'A')
LOOP
l_temp_batch_count := l_temp_batch_count + 1;
DELETE FROM fun_interface_controls
WHERE source = p_source
AND group_id = rec.group_id;