select to_number(null) cash_receipt_id,
aip.accounting_date gl_date,
to_char(apc.doc_sequence_value) voucher_no,
to_char(apc.check_number) check_number,
apc.vendor_name vendor_name,
api.accts_pay_code_combination_id account_code,
0 receipts_ent,
0 receipts_acc,
sum(round(aip.amount,5)) payments_ent,
sum(round(aip.amount*nvl(aip.exchange_rate,1),5)) payments_acc,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aip.exchange_rate exchange_rate,
api.payment_currency_code curr,
'ap' flg,
api.vendor_id partyid,
to_char(null) status,
aba.bank_account_id,
'payables' transaction_source
from ap_invoice_payments_all aip,
ap_invoices_all api,
ap_checks_all apc,
ce_bank_accounts aba ,
ce_bank_acct_uses_all cbu
where api.invoice_id = aip.invoice_id
and aip.check_id = apc.check_id
and apc.ce_bank_acct_use_id = cbu.bank_acct_use_id
and aba.bank_account_id=cbu.bank_account_id
and
(
(
apc.status_lookup_code in ('negotiable', 'cleared','voided',
'reconciled unaccounted', 'reconciled', 'cleared but unaccounted' )
and aip.accounting_date between nvl(:p_start_date,aip.accounting_date) and nvl(:p_end_date,aip.accounting_date)
and apc.future_pay_due_date is null
)
or
(
apc.status_lookup_code in ('negotiable')
and apc.future_pay_due_date between nvl(:p_start_date,apc.future_pay_due_date) and nvl(:p_end_date,apc.future_pay_due_date)
)
)
and aba.bank_account_id = :p_bank_account_id
and aip.invoice_payment_type is null
and (api.org_id is null or api.org_id = :p_org_id)
group by aip.accounting_date,
apc.doc_sequence_value,
apc.check_number,
apc.vendor_name,
api.accts_pay_code_combination_id,
aba.bank_account_name,
aba.bank_account_num,
aip.exchange_rate,
api.payment_currency_code,
api.vendor_id,aba.bank_account_id
union all
select to_number(null) cash_receipt_id,
aip.accounting_date gl_date,
to_char(apc.doc_sequence_value) voucher_no,
to_char(apc.check_number) check_number,
apc.vendor_name vendor_name,
api.accts_pay_code_combination_id account_code,
0 receipts_ent,
0 receipts_acc,
sum(round(aip.amount,5)) payments_ent,
sum(round(aip.amount*nvl(aip.exchange_rate,1),5)) payments_acc,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aip.exchange_rate exchange_rate,
api.payment_currency_code curr,
'ap' flg,
api.vendor_id partyid,
to_char(null) status,
aba.bank_account_id,
'payables' transaction_source
from
ap_invoice_distributions_all apid,
ap_invoice_payments_all aip,
ap_checks_all apc,
ap_invoice_lines_all apla,
ap_invoices_all api,
po_vendors pov,
ce_bank_accounts aba
where api.invoice_id=apid.invoice_id
and apla.invoice_id = apid.invoice_id
and apla.line_number = apid.invoice_line_number
and api.invoice_id=aip.invoice_id
and apc.check_id=aip.check_id
and api.vendor_id = pov.vendor_id
and aba.bank_account_id = :p_bank_account_id
and aba.asset_code_combination_id=apid.dist_code_combination_id
and apid.match_status_flag = 'a'
and nvl(api.payment_status_flag,'n') <>'n'
and apid.accounting_date between nvl(:p_start_date,apid.accounting_date) and nvl(:p_end_date,apid.accounting_date)
and (api.org_id is null or api.org_id = :p_org_id)
group by aip.accounting_date,
apc.doc_sequence_value,
apc.check_number,
apc.vendor_name,
api.accts_pay_code_combination_id,
aba.bank_account_name,
aba.bank_account_num,
aip.exchange_rate,
api.payment_currency_code,
api.vendor_id,aba.bank_account_id
union
select acrh.cash_receipt_id cash_receipt_id,
acrh.gl_date gl_date,
acr.receipt_number voucher_no,
null check_number,
nvl(rc.customer_name, acr.comments) vendor_name,
acrh.account_code_combination_id account_code,
round (acrh.amount, 5) receipts_ent,
round (acrh.amount*nvl(acrh.exchange_rate,1), 5) receipts_acc,
0 payments_ent,
0 payments_acc,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
0 exchange_rate,
acr.currency_code curr,
'ar' flg,
rc.customer_id partyid,
acrh.status status,
aba.bank_account_id,
'receivables' transaction_source
from ar_cash_receipt_history_all acrh,
ar_cash_receipts_all acr,
ar_cash_receipts_v rc,
ce_bank_accounts aba
,ce_bank_acct_uses_all cbaua
where acrh.cash_receipt_id(+) = acr.cash_receipt_id
--and nvl(reversal_created_from,'zzzz') <> 'rate adjustment trigger'
and rc.cash_receipt_id = acr.cash_receipt_id
--and rc.remittance_bank_branch_id=aba.bank_branch_id
and rc.remit_bank_acct_use_id=cbaua.bank_acct_use_id
and aba.bank_account_id=cbaua.bank_account_id
and acrh.status in ('cleared', 'remitted', 'confirmed')
and aba.bank_account_id = nvl(:p_bank_account_id, aba.bank_account_id)
and acr.remit_bank_acct_use_id =(select bank_acct_use_id from ce_bank_acct_uses_all
where bank_account_id=:p_bank_account_id and org_id=:p_org_id)
and trunc(acrh.gl_date ) between (:p_start_date) and (:p_end_date)
and (acr.org_id = :p_org_id or acr.org_id is null)
union all
select acrh.cash_receipt_id cash_receipt_id,
acrh.gl_date gl_date,
acr.receipt_number voucher_no,
null check_number,
nvl(rc.customer_name, acr.comments) vendor_name,
acrh.account_code_combination_id account_code,
round(decode(acrh.status,
'reversed',
(acrh.amount)*-1,acrh.amount ),5) receipts_ent,
round(decode(acrh.status,
'reversed',
(acrh.amount*
nvl(acrh.exchange_rate,1))*-1,
acrh.amount*
nvl(acrh.exchange_rate,1)),5) receipts_acc,
0 payments_ent,
0 payments_acc,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
0 exchange_rate,
acr.currency_code curr,
'arrev' flg,
rc.customer_id partyid,
acrh.status status,
aba.bank_account_id,
'receivables' transaction_source
from ar_cash_receipt_history_all acrh,
ar_cash_receipts_all acr,
ar_cash_receipts_v rc,
ce_bank_accounts aba,
ce_bank_acct_uses_all ca
where acrh.cash_receipt_id = acr.cash_receipt_id
--and nvl(reversal_created_from,'zzzz') <> 'rate adjustment trigger'
and rc.remit_bank_acct_use_id=ca.bank_acct_use_id
and aba.bank_account_id=ca.bank_account_id
and acr.cash_receipt_id = rc.cash_receipt_id
and acrh.status in ('reversed')
and aba.bank_account_id = nvl(:p_bank_account_id, aba.bank_account_id)
and trunc(acrh.gl_date ) between (:p_start_date) and (:p_end_date)
and (acr.org_id = :p_org_id or acr.org_id is null)
union
select gjh.je_header_id cash_receipt_id,
gjh.default_effective_date gl_date,
to_char(gjh.doc_sequence_value) voucher_no,
to_char(gjl.je_line_num) check_number,
null vendor_name,
gjl.code_combination_id account_code,
nvl(gjl.entered_dr,0) receipts_ent,
nvl(gjl.accounted_dr,0) receipts_acc,
nvl(gjl.entered_cr,0) payments_ent,
nvl(gjl.accounted_cr,0) payments_acc,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
nvl(gjh.currency_conversion_rate,1) exchange_rate,
gjh.currency_code curr,
'bank_transfer' flg,
null partyid,
null status,
aba.bank_account_id,
gjc.user_je_category_name transaction_source
from gl_je_headers gjh,
gl_je_lines gjl,
ce_bank_accounts aba,
gl_je_sources gjs,
gl_je_categories gjc,
ce_bank_acct_uses_all ca
where 1=1
and aba.bank_account_id = nvl(:p_bank_account_id, aba.bank_account_id)
and aba.asset_code_combination_id = gjl.code_combination_id
and ca.bank_account_id =aba.bank_account_id
and gjl.je_header_id = gjh.je_header_id
--and gjs.user_je_source_name in ('cash management','manual','receivables')
and gjs.je_source_name = gjh.je_source
and gjc.user_je_category_name in ('bank transfers(1)','bank transfers','bank transfer','xxxx accruals','cash management')
and gjc.je_category_name = gjh.je_category
and gjh.default_effective_date between (:p_start_date) and (nvl(:p_end_date,sysdate))
and ca.org_id=:p_org_id
order by 1
Showing posts with label CM. Show all posts
Showing posts with label CM. Show all posts
Tuesday, August 27, 2013
Tuesday, December 27, 2011
Create Bank Conversion
iby_ext_bankacct_pub.create_ext_bank
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_ext_bank_rec => l_extbank_rec,
x_bank_id => l_bank_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_resp
);
l_extbank_rec.bank_number := l_bank_number;
l_extbank_rec.bank_name := TRIM (cv_stg1.bank_name);
l_extbank_rec.country_code := cv_stg1.country_code;
l_extbank_rec.bank_alt_name:=cv_stg1.bank_name_alt;
l_extbank_rec.bank_number:=cv_stg1.bank_number;
iby_ext_bankacct_pub.create_ext_bank_branch
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_ext_bank_branch_rec => l_extbankbranch_rec,
x_branch_id => l_branch_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_resp
);
l_extbankbranch_rec.bank_party_id := cv_stg1.bank_id;
l_extbankbranch_rec.branch_name := cv_stg1.bank_branch_name;
l_extbankbranch_rec.branch_type := cv_stg1.bank_branch_type;
l_extbankbranch_rec.branch_number:=cv_stg1.branch_number;
l_extbankbranch_rec.alternate_branch_name:=cv_stg1.branch_name_alt;
hz_location_v2pub.create_location
(p_init_msg_list => 'T',
p_location_rec => l_location_rec,
x_location_id => l_location_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_location_rec.country := cv_stg1.country_code;
l_location_rec.address1 := cv_stg1.branch_address ;
l_location_rec.city := cv_stg1.city;
l_location_rec.postal_code := cv_stg1.zip_code;
l_location_rec.created_by_module := 'AMS';
hz_party_site_v2pub.create_party_site
(p_init_msg_list => 'T',
p_party_site_rec => l_party_site_rec,
x_party_site_id => l_party_site_id,
x_party_site_number => l_party_site_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_party_site_rec.party_id := l_branch_id;
l_party_site_rec.location_id := l_location_id;
l_party_site_rec.identifying_address_flag := 'Y';
l_party_site_rec.created_by_module := 'AMS';
ce_bank_pub.create_bank_acct
(p_init_msg_list => 'T',
p_acct_rec => l_bankacct_rec,
x_acct_id => l_int_bank_acct_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_bankacct_rec.bank_id:=cv_stg1.bank_id;
l_bankacct_rec.branch_id := cv_stg1.branch_id;
l_bankacct_rec.account_owner_org_id :=cv_stg1.int_acct_owner_org_id;--24276;-- cv_stg1.int_acct_owner_party_id;
l_bankacct_rec.account_owner_party_id := cv_stg1.int_acct_owner_party_id;
l_bankacct_rec.account_classification := 'INTERNAL';
l_bankacct_rec.bank_account_name := cv_stg1.bank_account_name;
l_bankacct_rec.bank_account_num := cv_stg1.bank_account_num;
l_bankacct_rec.currency := cv_stg1.currency_code;
l_bankacct_rec.acct_type:=cv_stg1.acc_type;
l_bankacct_rec.ap_use_allowed_flag := 'Y';
l_bankacct_rec.ar_use_allowed_flag := 'Y';
l_bankacct_rec.asset_code_combination_id := cv_stg1.gl_cash_account_ccid;
l_bankacct_rec.receipt_multi_currency_flag := cv_stg1.multiple_currency_allowed;
l_bankacct_rec.ap_amount_tolerance := 0;
l_bankacct_rec.ar_amount_tolerance := 0;
l_bankacct_rec.ap_percent_tolerance := 0;
l_bankacct_rec.ar_percent_tolerance := 0;
l_bankacct_rec.ce_amount_tolerance := 0;
l_bankacct_rec.ce_percent_tolerance := 0;
l_bankacct_rec.multi_currency_allowed_flag := 'Y';
l_bankacct_rec.zero_amount_allowed := 'N';
l_bankacct_rec.cash_clearing_ccid := cv_stg1.cash_clearing_account_ccid;
l_bankacct_rec.bank_charges_ccid := cv_stg1.bank_charges_account_ccid;
ce_bank_pub.create_bank_acct_use
(p_init_msg_list => 'T',
p_acct_use_rec => l_bankacct_use_rec_type,
x_acct_use_id => l_acct_use_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_bankacct_use_rec_type.bank_account_id :=l_int_bank_acct_id;
l_bankacct_use_rec_type.org_type := 'OU';
l_bankacct_use_rec_type.org_id :=cv_stg1.org_id;
l_bankacct_use_rec_type.ap_use_enable_flag := 'Y';
l_bankacct_use_rec_type.ar_use_enable_flag := 'Y';
l_bankacct_use_rec_type.default_account_flag := 'N';
l_bankacct_use_rec_type.authorized_flag := 'Y';
l_bankacct_use_rec_type.asset_code_combination_id := cv_stg1.gl_cash_account_ccid;
l_bankacct_use_rec_type.bank_charges_ccid := cv_stg1.bank_charges_account_ccid;
l_bankacct_use_rec_type.cash_clearing_ccid := cv_stg1.cash_clearing_account_ccid;
If you are Facing Problem with create bank with the same bank_account_name but different bank_account_name.
You have to apply the patch.
| Service Request Number is :1264514.1 |
| Patch 10261393:R12.CE.B | version: 12.1.3 |
Subscribe to:
Posts (Atom)