[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select etb.registration_number
From
xle_establishment_v etb
,xle_bsv_associations bsv
,gl_ledger_le_v gl
,jl_br_ar_ret_interface jlint
Where
etb.legal_entity_id = gl.legal_entity_id
And bsv.legal_parent_id = etb.legal_entity_id
And etb.establishment_id = bsv.legal_construct_id
And bsv.entity_name = cp_lacct_balancing_segment
And gl.ledger_id = cp_ledger_id
-- Bug 10334411 Start
And rtrim(upper(substr(translate(etb.establishment_name,
'????cCuUaAeEoOaAaAeEiIoOuU!@#$%&*()_+=[]{}/\?:<>|.',
'aAoOcCuUaAeEoOaAaAeEiIoOuU '),1,80))) --bug 12853583 Increased length to 80
--bug 12853583 = rtrim(upper(jlint.company_name)) --bug 8412707 + bug8527766
like rtrim(upper(translate(jlint.company_name,
'????cCuUaAeEoOaAaAeEiIoOuU!@#$%&*()_+=[]{}/\?:<>|.',
'aAoOcCuUaAeEoOaAaAeEiIoOuU ')))||'%' --bug 12853583 Introduced translate
--bug 13910114 Introduced LIKE
-- Bug 10334411 End
And file_control = cp_file_control
And ENTRY_SEQUENTIAL_NUMBER = cp_ent_seq_num
AND trunc(SYSDATE) between trunc(nvl(bsv.effective_from, SYSDATE))
and trunc(nvl(bsv.effective_to,SYSDATE)); --bug 9239401
Select etb.registration_number
From
xle_establishment_v etb
,xle_bsv_associations bsv
,gl_ledger_le_v gl
,JL_BR_AR_RET_INTERFACE_EXT jlint
Where
etb.legal_entity_id = gl.legal_entity_id
And bsv.legal_parent_id = etb.legal_entity_id
And etb.establishment_id = bsv.legal_construct_id
And bsv.entity_name = cp_lacct_balancing_segment
And gl.ledger_id = cp_ledger_id
-- Bug 14204545 Start
And rtrim(upper(substr(translate(etb.establishment_name,
'????cCuUaAeEoOaAaAeEiIoOuU!@#$%&*()_+=[]{}/\?:<>|.',
'aAoOcCuUaAeEoOaAaAeEiIoOuU '),1,80))) --bug 12853583 Increased length to 80
--bug 12853583 = rtrim(upper(jlint.company_name)) --bug 8412707 + bug8527766
like rtrim(upper(translate(jlint.company_name,
'????cCuUaAeEoOaAaAeEiIoOuU!@#$%&*()_+=[]{}/\?:<>|.',
'aAoOcCuUaAeEoOaAaAeEiIoOuU ')))||'%' --bug 12853583 Introduced translate
--bug 13910114 Introduced LIKE
-- Bug 14204545 End
And file_control = cp_file_control
And ENTRY_SEQUENTIAL_NUMBER = cp_ent_seq_num
AND trunc(SYSDATE) between trunc(nvl(bsv.effective_from, SYSDATE))
and trunc(nvl(bsv.effective_to,SYSDATE)); --bug 9239401
SELECT distinct bank_number
INTO X_jlbr_bank_number
FROM jl_br_ar_bank_occurrences
WHERE bank_number = X_bank_number
AND ROWNUM < 2;
SELECT prof.bank_or_branch_number,
nvl(occ.bank_party_id,0) --bug 8527766 - can be NULL in occ table
INTO X_jlbr_bank_number,
p_bank_party_id
FROM jl_br_ar_bank_occurrences occ,
hz_organization_profiles prof
WHERE prof.bank_or_branch_number = X_bank_number
AND occ.bank_party_id = prof.party_id
AND prof.home_country ='BR'
AND rownum = 1;
SELECT occ.std_occurrence_code
INTO X_bank_occurrence_code_std
FROM jl_br_ar_bank_occurrences occ,
hz_organization_profiles prof
WHERE occ.bank_occurrence_code = X_bank_occurrence_code
AND prof.bank_or_branch_number = X_bank_number
AND occ.bank_party_id = prof.party_id
AND prof.home_country ='BR'
AND occ.bank_occurrence_type = 'RETURN_OCCURRENCE'
AND SYSDATE between TRUNC(prof.effective_start_date)
AND NVL(TRUNC(prof.effective_end_date), SYSDATE+1);
SELECT distinct aba.global_attribute7
INTO X_dual_num
FROM ap_bank_accounts_all aba,
jl_br_ar_collection_docs cd,
ar_payment_schedules_all arps
WHERE cd.document_id = X_company_use
AND aba.bank_account_id = cd.bank_account_id
AND arps.payment_schedule_id = cd.payment_schedule_id
-- bug 1892303
-- AND arps.trx_number||'-'||to_char(arps.terms_sequence_number) = X_your_number
AND to_number(aba.global_attribute7) = X_company_code;
SELECT distinct acct.secondary_account_reference
INTO X_dual_num
FROM ce_bank_accounts acct,
ce_bank_acct_uses_all acctUse,
jl_br_ar_collection_docs cd,
ar_payment_schedules_all arps
WHERE cd.document_id = X_company_use
AND acctUse.bank_acct_use_id = cd.bank_acct_use_id
AND acct.bank_account_id = acctUse.bank_account_id
AND arps.payment_schedule_id = cd.payment_schedule_id
-- bug 1892303
-- AND arps.trx_number||'-'||to_char(arps.terms_sequence_number) = trim(X_your_number)
AND to_number(acct.secondary_account_reference) = X_company_code;
SELECT doc.payment_schedule_id,
doc.document_status,
bra.bank_number
INTO X_payment_schedule_id,
X_document_status,
X_remittance_bank
FROM jl_br_ar_collection_docs doc,
ap_bank_accounts_all acc,
ap_bank_branches bra
WHERE doc.document_id = X_company_use
AND doc.bank_account_id = acc.bank_account_id
AND acc.bank_branch_id = bra.bank_branch_id;
SELECT doc.payment_schedule_id,
doc.document_status,
HzPartyBank.party_id bank_id
INTO X_payment_schedule_id,
X_document_status,
X_remittance_bank_id
FROM jl_br_ar_collection_docs doc,
ce_bank_accounts CeBankAccount,
ce_bank_acct_uses_all CeBankAcctUse,
hz_parties HzPartyBank
Where doc.bank_acct_use_id = CeBankAcctUse.bank_acct_use_id
And CeBankAcctUse.bank_account_id = CeBankAccount.bank_account_id
And CeBankAccount.BANK_ID = HzPartyBank.PARTY_ID
--And HzPartyBank.Country = 'BR'
And doc.document_id = X_company_use;
SELECT payment_schedule_id
INTO X_payment_schedule_id
FROM ar_payment_schedules
WHERE payment_schedule_id = X_payment_schedule_id;
SELECT payment_schedule_id
INTO X_payment_schedule_id
FROM ar_payment_schedules
WHERE trx_number|| '-' ||to_char(terms_sequence_number)=X_your_number;
SELECT doc.document_status,
bra.bank_number
INTO X_document_status,
X_remittance_bank
FROM jl_br_ar_collection_docs doc,
ap_bank_accounts_all acc,
ap_bank_branches bra
WHERE doc.payment_schedule_id = X_payment_schedule_id
AND doc.bank_account_id = acc.bank_account_id
AND acc.bank_branch_id = bra.bank_branch_id;
SELECT set_of_books_id,
global_attribute1
INTO l_ledger_id,
l_acct_balancing_segment
FROM ar_system_parameters;
/* Select etb.registration_number
INTO x_cgc
From
xle_establishment_v etb
,xle_bsv_associations bsv
,gl_ledger_le_v gl
Where
etb.legal_entity_id = gl.legal_entity_id
And bsv.legal_parent_id = etb.legal_entity_id
And etb.establishment_id = bsv.legal_construct_id
And bsv.entity_name = l_acct_balancing_segment
And gl.ledger_id = l_ledger_id; */
/* SELECT distinct (decode(inf.register_type, 3, '00000000000000',
to_number(lpad(inf.register_number,9,'0')||
lpad(inf.register_subsidiary,4,'0')||
lpad(inf.register_digit,2,'0'))))
INTO X_cgc
FROM jl_br_company_infos inf,
hz_cust_acct_sites_all adr,
ra_customer_trx_all trx,
ar_payment_schedules pay
WHERE pay.payment_schedule_id = X_payment_schedule_id
AND trx.customer_trx_id = pay.customer_trx_id
AND adr.cust_acct_site_id = trx.remit_to_address_id
AND inf.accounting_balancing_segment = adr.global_attribute1
AND inf.set_of_books_id = trx.set_of_books_id; */
SELECT substr(pty.party_name, 1,50)
INTO X_customer_name1
FROM hz_cust_accounts_all cst,
hz_parties pty,
ra_customer_trx trx
WHERE trx.bill_to_customer_id = cst.cust_account_id
AND cst.party_id = pty.party_id
AND trx.customer_trx_id = (SELECT customer_trx_id
FROM ar_payment_schedules
WHERE payment_schedule_id =
X_payment_schedule_id);
SELECT nvl(doc.document_amount,0)
INTO X_document_amount
FROM jl_br_ar_bank_occurrences ban,
jl_br_ar_occurrence_docs_all doc,
jl_br_ar_collection_docs cob
WHERE ban.std_occurrence_code = 'REMITTANCE'
AND ban.bank_occurrence_type = 'REMITTANCE_OCCURRENCE'
--AND ban.bank_number = X_bank_number
AND ban.bank_party_id = p_bank_party_id
AND doc.bank_occurrence_code = ban.bank_occurrence_code
AND doc.occurrence_status in ('CONFIRMED')
--AND doc.bank_number = ban.bank_number
AND doc.bank_party_id = ban.bank_party_id
AND doc.bank_occurrence_type = ban.bank_occurrence_type
AND doc.document_id = cob.document_id
/*
AND cob.document_status in ('FORMATTED','CONFIRMED')
AND cob.payment_schedule_id = X_payment_schedule_id;