[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select set_of_books_id
into l_books_id
from ar_system_parameters_all
where org_id = p_reporting_entity_id;
SELECT currency_code,
name
INTO l_currency_code,
l_sob_name
FROM gl_sets_of_books
WHERE set_of_books_id = l_books_id;
l_as_of_date := 'SELECT arl.meaning,
rah.trx_date,
rah.gl_date,
rah.status
FROM ar_transaction_history_all rah,
ar_lookups arl
WHERE arl.lookup_code = rah.status '||
l_org_where_rah ||
'AND arl.lookup_type = ''TRANSACTION_HISTORY_STATUS'''||
'AND rah.transaction_history_id = (SELECT MAX(rah1.transaction_history_id)
FROM ar_transaction_history_all rah1
WHERE rah1.trx_date <= to_char(:b_status_date) '||
l_org_where_rah1 ||
'AND rah1.customer_trx_id = :b_trx_id)';
l_remit_batch := 'SELECT arb.name
,arb.remit_method_code
,arb.with_recourse_flag
,rm.name
,rabb.bank_name remit_bank_name
,rabb.bank_number remit_bank_number
,rabb.bank_branch_name remit_branch_name
,rabb.branch_number remit_branch_number
,cba.bank_account_name remit_bank_acc_name
,cba.bank_account_num remit_bank_acc_number
,cba.bank_account_id remit_bank_acc_id
,rabb.city remit_branch_city
,rabb.state remit_branch_state
,rabb.country remit_branch_country
,rabb.zip remit_branch_postal_code
,rabb.address_line1 remit_branch_address1
,rabb.address_line2 remit_branch_address2
,rabb.address_line3 remit_branch_address3
,cba.check_digits
,cba.currency_code remit_bank_acc_curr
,rma.risk_elimination_days
FROM ar_transaction_history_all rah
,ar_batches_all arb
,ar_receipt_methods rm
,ar_receipt_method_accounts rma
,ce_bank_accounts cba
,ce_bank_acct_uses raba
,ce_bank_branches_v rabb
WHERE rah.batch_id = arb.batch_id
AND arb.receipt_method_id = rm.receipt_method_id(+)
AND rm.receipt_method_id = rma.receipt_method_id(+)
AND arb.remit_bank_acct_use_id = raba.bank_acct_use_id(+)
AND raba.bank_account_id = cba.bank_account_id (+)
AND cba.bank_branch_id = rabb.branch_party_id(+) '||
l_org_where_rah ||
l_org_where_arb ||
l_org_where_raba ||
'AND rah.transaction_history_id = (SELECT max(rah1.transaction_history_id)
FROM ar_transaction_history_all rah1
WHERE rah1.trx_date <= to_char(:b_status_date) '||
l_org_where_rah1 ||
'AND rah1.batch_id IS NOT NULL
AND rah1.customer_trx_id = :b_trx_id)';
l_open_amount := 'SELECT nvl(SUM(app.amount_applied),0)
FROM ra_customer_trx_all trx,
ar_receivable_applications_all app
WHERE trx.customer_trx_id = app.applied_customer_trx_id
AND app.applied_customer_trx_id = :b_trx_id '||
l_org_where_trx ||
l_org_where_app ||
'AND app.status = ''APP'''||
'AND trunc(app.apply_date) > :b_status_as_of_date';
l_receipt_reversal := 'SELECT distinct DECODE(cr.reversal_reason_code, NULL, NULL
, initcap(arl.meaning))
FROM ar_cash_receipts_all cr,
ar_receivable_applications_all app,
ar_lookups arl
WHERE cr.cash_receipt_id = app.cash_receipt_id '||
l_org_where_app ||
l_org_where_cr ||
'AND cr.reversal_reason_code = arl.lookup_code (+)
AND arl.lookup_type (+) = ''CKAJST_REASON'''||
'AND app.applied_customer_trx_id = :b_trx_id';
l_assigned_amount := 'SELECT nvl(sum(trl.extended_amount),0),
nvl(sum(trl.extended_acctd_amount),0)
FROM ra_customer_trx_lines_all trl
WHERE trl.customer_trx_id = :b_trx_id '||
l_org_where_trl;
'SELECT trx.customer_trx_id
,trx.trx_number transaction_number
,trx.doc_sequence_value document_number
,fds.name document_sequence_name
,trx.invoice_currency_code currency_code
,ctt.magnetic_format_code
,nvl(ps.amount_due_original,0) original_entered_amount
,nvl(ps.amount_due_remaining,0) open_entered_amount
,nvl(ps.acctd_amount_due_remaining,0) open_functional_amount
,substrb(party.party_name,1,50) drawee_name
,cust_acct.account_number drawee_number
,party.jgzz_fiscal_code drawee_taxpayer_id
,rasu.tax_reference drawee_vat_reg_number
,loc.city drawee_city
,loc.state drawee_state
,loc.country drawee_country
,loc.postal_code drawee_postal_code
,arl_class.meaning drawee_class
,arl_category.meaning drawee_category
,rasu.location drawee_location
,trx.trx_date issue_date
,trx.term_due_date maturity_date
,ctt.drawee_issued_flag issued_by_drawee
,ctt.signed_flag signed_by_drawee
,ctt.name transaction_type
,rabb.bank_name remit_bank_name
,rabb.bank_number remit_bank_number
,rabb.bank_branch_name remit_branch_name
,rabb.branch_number remit_branch_number
,cba.bank_account_name remit_bank_acc_name
,cba.bank_account_num remit_bank_acc_number
,cba.bank_account_id remit_bank_acc_id
,rabb.city remit_branch_city
,rabb.state remit_branch_state
,rabb.country remit_branch_country
,rabb.zip remit_branch_postal_code
,rabb.address_line1 remit_branch_address1
,rabb.address_line2 remit_branch_address2
,rabb.address_line3 remit_branch_address3
,trx.override_remit_account_flag remit_bank_allow_override
,cba.check_digits remit_bank_acc_check_digits
,cba.currency_code remit_bank_acc_curr
,abb.bank_name drawee_bank_name
,abb.bank_number drawee_bank_number
,abb.bank_branch_name drawee_branch_name
,abb.branch_number drawee_branch_number
,aba.bank_account_name drawee_bank_acc_name
,aba.bank_account_num drawee_bank_acc_number
,abb.city drawee_branch_city
,abb.state drawee_branch_state
,abb.country drawee_branch_country
,abb.zip drawee_branch_postal_code
,abb.address_line1 drawee_branch_address1
,abb.address_line2 drawee_branch_address2
,abb.address_line3 drawee_branch_address3
,aba.check_digits drawee_bank_acc_check_digits
,aba.currency_code drawee_bank_acc_curr
,trx.comments
,decode(ps.amount_due_remaining, 0 , to_number(null)
, trunc(sysdate) - ps.due_date) days_late
,trx.printing_last_printed last_printed_date
,loc.address1 drawee_address1
,loc.address2 drawee_address2
,loc.address3 drawee_address3
,substrb(party.person_first_name,1,40) ||'' ''||substrb(party.person_last_name,1,50) drawee_contact
,trx.special_instructions
,rah.status status_code
,rab.name creation_batch_name
,rabs.name transaction_batch_source
,nvl(ps.exchange_rate,1)
FROM ra_cust_trx_types_all ctt
,ra_customer_trx_all trx
,hz_cust_acct_sites raa
,hz_party_sites party_site
,hz_locations loc
,hz_cust_account_roles acct_role
,hz_parties rel_party
,hz_relationships rel
,hz_cust_site_uses_all rasu
,ap_bank_accounts_all aba
,ce_bank_branches_v abb
,hz_cust_accounts cust_acct
,hz_parties party
,ce_bank_branches_v rabb
,ce_bank_accounts cba
,ce_bank_acct_uses raba
,ar_lookups arl
,ar_lookups arl_class
,ar_lookups arl_category
,ar_transaction_history_all rah
,fnd_document_sequences fds
,ar_payment_schedules_all ps
,ra_batches_all rab
,ra_batch_sources_all rabs
WHERE trx.cust_trx_type_id = ctt.cust_trx_type_id
AND trx.batch_source_id = rabs.batch_source_id
AND trx.drawee_site_use_id = rasu.site_use_id (+)
AND rasu.cust_acct_site_id = raa.cust_acct_site_id (+)
AND raa.party_site_id = party_site.party_site_id (+)
AND loc.location_id(+) = party_site.location_id
AND trx.drawee_bank_account_id = aba.bank_account_id (+)
AND aba.bank_branch_id = abb.branch_party_id (+)
AND trx.drawee_id = cust_acct.cust_account_id
AND cust_acct.party_id = party.party_id
AND trx.remit_bank_acct_use_id = raba.bank_acct_use_id (+)
AND raba.bank_account_id = cba.bank_account_id (+)
AND cba.bank_branch_id = rabb.branch_party_id (+)
AND trx.drawee_contact_id = acct_role.cust_account_role_id (+)
AND acct_role.party_id = rel.party_id (+)
AND rel.subject_id = rel_party.party_id(+)
AND rel.subject_table_name(+) = ''HZ_PARTIES'''||
'AND rel.object_table_name(+) = ''HZ_PARTIES'''||
'AND rel.directional_flag(+) = ''F'''||
'AND rah.customer_trx_id = trx.customer_trx_id
AND rah.current_record_flag = ''Y'''||
'AND arl.lookup_code = rah.status
AND arl.lookup_type = ''TRANSACTION_HISTORY_STATUS'''||
'AND trx.doc_sequence_id = fds.doc_sequence_id(+)
AND cust_acct.customer_class_code = arl_class.lookup_code(+)
AND arl_class.lookup_type(+) = ''CUSTOMER_CLASS'''||
'AND party.category_code = arl_category.lookup_code(+)
AND arl_category.lookup_type(+) = ''CUSTOMER_CATEGORY'''||
l_org_where_trx ||
l_org_where_rabs ||
l_org_where_ctt ||
l_org_where_raa ||
l_org_where_raa ||
l_org_where_rasu ||
l_org_where_aba ||
l_org_where_raba ||
l_org_where_rah ||
l_org_where_ps ||
l_org_where_rab||
l_status_where ||
l_excluded_status_where ||
l_transaction_type_where ||
l_maturity_date_where ||
l_drawee_name_where ||
l_drawee_number_where ||
l_drawee_bank_name_where ||
l_issue_date_where ||
l_on_hold_where ||
'AND trx.customer_trx_id = ps.customer_trx_id(+)
AND trx.batch_id = rab.batch_id(+)
AND rah.status <> ''INCOMPLETE''',
DBMS_SQL.native);
SELECT MIN(trx_date)
INTO l_creation_gl_date
FROM ar_transaction_history_all
WHERE event = 'COMPLETED'
AND customer_trx_id = v_customer_trx_id;
SELECT MIN(maturity_date)
INTO l_original_maturity_date
FROM ar_transaction_history_all
WHERE customer_trx_id = v_customer_trx_id
AND status IN ('PENDING_REMITTANCE', 'PENDING_ACCEPTANCE')
AND event = 'COMPLETED';
SELECT MAX(trx_date)
INTO l_unpaid_date
FROM ar_transaction_history_all
WHERE status = 'UNPAID'
AND customer_trx_id = v_customer_trx_id;
SELECT MAX(trx_date)
INTO l_acceptance_date
FROM ar_transaction_history_all
WHERE event = 'ACCEPTED'
AND customer_trx_id = v_customer_trx_id;
SELECT MAX(trx_date)
INTO l_remit_date
FROM ar_transaction_history_all
WHERE batch_id IS NOT NULL
AND customer_trx_id = v_customer_trx_id;
| Insert Data into Interface Table |
+------------------------------------------------------------------*/
INSERT INTO ar_br_status_rep_itf
(creation_date
,created_by
,last_update_login
,last_update_date
,last_updated_by
,request_id
,status
,status_date
,transaction_number
,document_number
,document_sequence_name
,currency
,magnetic_format_code
,entered_amount
,functional_amount
,balance_due
,functional_balance_due
,drawee_name
,drawee_number
,jgzz_fiscal_code
,drawee_vat_reg_number
,drawee_city
,drawee_state
,drawee_country
,drawee_postal_code
,drawee_class
,drawee_category
,drawee_location
,issue_date
,creation_gl_date
,status_gl_date
,maturity_date
,original_maturity_date
,issued_by_drawee
,signed_by_drawee
,transaction_type
,transaction_batch_source
,remit_bank_name
,remit_bank_number
,remit_branch_name
,remit_branch_number
,remit_bank_acc_name
,remit_bank_acc_number
,remit_branch_city
,remit_branch_state
,remit_branch_country
,remit_branch_postal_code
,remit_branch_address1
,remit_branch_address2
,remit_branch_address3
,remit_bank_allow_override
,remit_bank_acc_check_digits
,remit_bank_acc_curr
,drawee_bank_name
,drawee_bank_number
,drawee_branch_name
,drawee_branch_number
,drawee_bank_acc_name
,drawee_bank_acc_number
,drawee_branch_city
,drawee_branch_state
,drawee_branch_country
,drawee_branch_postal_code
,drawee_branch_address1
,drawee_branch_address2
,drawee_branch_address3
,drawee_bank_acc_check_digits
,drawee_bank_acc_curr
,unpaid_date
,acceptance_date
,comments
,days_late
,last_printed_date
,remittance_date
,drawee_address1
,drawee_address2
,drawee_address3
,drawee_contact
,special_instructions
,remittance_batch_name
,remittance_method
,with_recourse
,remittance_payment_method
,risk_elimination_days
,creation_batch_name
,assigned_entered_amount
,assigned_functional_amount
,unpaid_receipt_reversal_reason
,functional_currency_code
,organization_name
)
VALUES
(sysdate
,p_user_id
,l_login_id
,sysdate
,p_user_id
,p_request_id
,v_status
,v_status_date
,v_transaction_number
,v_document_number
,v_document_sequence_name
,v_currency_code
,v_magnetic_format_code
,v_original_entered_amount
,l_new_original_funct_amt
,v_open_entered_amount
,v_open_functional_amount
,v_drawee_name
,v_drawee_number
,v_drawee_taxpayer_id
,v_drawee_vat_reg_number
,v_drawee_city
,v_drawee_state
,v_drawee_country
,v_drawee_postal_code
,v_drawee_class
,v_drawee_category
,v_drawee_location
,v_issue_date
,l_creation_gl_date
,v_status_gl_date
,v_maturity_date
,l_original_maturity_date
,v_issued_by_drawee
,v_signed_by_drawee
,v_transaction_type
,v_transaction_batch_source
,v_remit_bank_name
,v_remit_bank_number
,v_remit_branch_name
,v_remit_branch_number
,v_remit_bank_acc_name
,v_remit_bank_acc_number
,v_remit_branch_city
,v_remit_branch_state
,v_remit_branch_country
,v_remit_branch_postal_code
,v_remit_branch_address1
,v_remit_branch_address2
,v_remit_branch_address3
,v_remit_bank_allow_override
,v_remit_bank_acc_check_digits
,v_remit_bank_acc_curr
,v_drawee_bank_name
,v_drawee_bank_number
,v_drawee_branch_name
,v_drawee_branch_number
,v_drawee_bank_acc_name
,v_drawee_bank_acc_number
,v_drawee_branch_city
,v_drawee_branch_state
,v_drawee_branch_country
,v_drawee_branch_postal_code
,v_drawee_branch_address1
,v_drawee_branch_address2
,v_drawee_branch_address3
,v_drawee_bank_acc_check_digits
,v_drawee_bank_acc_curr
,l_unpaid_date
,l_acceptance_date
,v_comments
,v_days_late
,v_last_printed_date
,l_remit_date
,v_drawee_address1
,v_drawee_address2
,v_drawee_address3
,v_drawee_contact
,v_special_instructions
,v_remittance_batch_name
,v_remittance_method
,v_with_recourse
,v_remittance_payment_method
,v_risk_elimination_days
,v_creation_batch_name
,v_assigned_entered_amount
,v_assigned_functional_amount
,v_unpaid_receipt_rev_reason
,l_currency_code
,l_sob_name
);