I'm using 9i vesion, and the query is based on this view:
CREATE OR REPLACE VIEW Statement_View (row_id,
customer_trx_id,
trx_number,
old_trx_number,
ct_related_trx_number,
ct_model_trx_number,
trx_date,
term_due_date,
previous_customer_trx_id,
initial_customer_trx_id,
related_batch_source_id,
related_customer_trx_id,
cust_trx_type_id,
batch_id,
batch_source_id,
reason_code,
term_id,
primary_salesrep_id,
agreement_id,
credit_method_for_rules,
credit_method_for_installments,
receipt_method_id,
invoicing_rule_id,
ship_via,
fob_point,
finance_charges,
complete_flag,
customer_bank_account_id,
recurred_from_trx_number,
status_trx,
default_tax_exempt_flag,
sold_to_customer_id,
sold_to_site_use_id,
sold_to_contact_id,
bill_to_customer_id,
bill_to_site_use_id,
raa_bill_to_address_id,
bill_to_contact_id,
bill_to_taxpayer_id,
ship_to_customer_id,
ship_to_site_use_id,
raa_ship_to_address_id,
ship_to_contact_id,
ship_to_taxpayer_id,
remit_to_address_id,
invoice_currency_code,
created_from,
set_of_books_id,
printing_original_date,
printing_last_printed,
printing_option,
printing_count,
printing_pending,
last_printed_sequence_num,
purchase_order,
purchase_order_revision,
purchase_order_date,
customer_reference,
customer_reference_date,
comments,
internal_notes,
exchange_rate_type,
exchange_date,
exchange_rate,
territory_id,
end_date_commitment,
start_date_commitment,
orig_system_batch_name,
ship_date_actual,
waybill_number,
doc_sequence_id,
doc_sequence_value,
paying_customer_id,
paying_site_use_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
interface_header_context,
interface_header_attribute1,
interface_header_attribute2,
interface_header_attribute3,
interface_header_attribute4,
interface_header_attribute5,
interface_header_attribute6,
interface_header_attribute7,
interface_header_attribute8,
interface_header_attribute9,
interface_header_attribute10,
interface_header_attribute11,
interface_header_attribute12,
interface_header_attribute13,
interface_header_attribute14,
interface_header_attribute15,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
default_ussgl_transaction_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
rac_bill_to_customer_name,
rac_bill_to_customer_num,
su_bill_to_location,
raa_bill_to_address1,
raa_bill_to_address2,
raa_bill_to_address3_db,
raa_bill_to_address3,
raa_bill_to_city,
raa_bill_to_county,
raa_bill_to_state,
raa_bill_to_province,
raa_bill_to_postal_code,
ft_bill_to_country,
raa_bill_to_concat_address,
raco_bill_to_contact_name,
rac_ship_to_customer_name,
rac_ship_to_customer_num,
su_ship_to_location,
raa_ship_to_address1,
raa_ship_to_address2,
raa_ship_to_address3_db,
raa_ship_to_address3,
raa_ship_to_city,
raa_ship_to_county,
raa_ship_to_state,
raa_ship_to_province,
raa_ship_to_postal_code,
ft_ship_to_country,
raa_ship_to_concat_address,
raco_ship_to_contact_name,
rac_sold_to_customer_name,
rac_sold_to_customer_num,
rac_paying_customer_name,
rac_paying_customer_num,
su_paying_customer_location,
raa_remit_to_address1,
raa_remit_to_address2,
raa_remit_to_address3_db,
raa_remit_to_address3,
raa_remit_to_city,
raa_remit_to_county,
raa_remit_to_state,
raa_remit_to_province,
raa_remit_to_postal_code,
ft_remit_to_country,
raa_concat_remit_to_address,
apba_bank_account_name,
apba_bank_account_num,
apba_inactive_date,
apb_customer_bank_name,
apb_customer_bank_branch_name,
arm_receipt_method_name,
arm_payment_type_code,
arc_creation_method_code,
bs_batch_source_name,
bs_auto_trx_numbering_flag,
bs_copy_doc_number_flag,
rab_batch_name,
ctt_type_name,
ctt_class,
ras_primary_salesrep_name,
ras_primary_salesrep_num,
rat_term_name,
rat_term_in_use_flag,
soa_agreement_name,
of_ship_via_name,
of_organization_id,
al_fob_meaning,
al_default_tax_exempt_flag,
ct_reference,
gd_gl_date,
gdct_user_exchange_rate_type,
ct_invoice_for_cb,
ps_dispute_amount,
ps_dispute_date,
dh_max_dispute_date,
rev_recog_run_flag,
posted_flag,
selected_for_payment_flag,
activity_flag,
ctt_post_to_gl_flag,
ctt_open_receivables_flag,
ctt_allow_freight_flag,
ctt_creation_sign,
ctt_allow_overapplication_flag,
ctt_natural_app_only_flag,
ctt_tax_calculation_flag,
ctt_default_status,
ctt_default_term,
ctt_default_printing_option,
rules_flag,
printed_flag,
cm_against_trx_flag,
site_status,
customer_status,
override_terms,
commitments_exist_flag,
agreements_exist_flag,
atchmt_flag,
global_attribute21,
global_attribute22,
global_attribute23,
global_attribute24,
global_attribute25,
global_attribute26,
global_attribute27,
global_attribute28,
global_attribute29,
global_attribute30,
bs_batch_source_type,
org_id,
ra_billing_number,
legal_entity_id,
payment_trxn_extension_id,
arm_payment_channel_code,
billing_date
)
AS
SELECT ct.ROWID, ct.customer_trx_id, ct.trx_number, ct.old_trx_number,
ct_rel.trx_number, ct.recurred_from_trx_number, ct.trx_date,
arpt_sql_func_util.get_first_real_due_date (ct.customer_trx_id,
ct.term_id,
ct.trx_date
),
ct.previous_customer_trx_id, ct.initial_customer_trx_id,
ct.related_batch_source_id, ct.related_customer_trx_id,
ct.cust_trx_type_id, ct.batch_id, ct.batch_source_id,
ct.reason_code, ct.term_id, ct.primary_salesrep_id, ct.agreement_id,
ct.credit_method_for_rules, ct.credit_method_for_installments,
ct.receipt_method_id, ct.invoicing_rule_id, ct.ship_via,
ct.fob_point, ct.finance_charges, ct.complete_flag,
ct.customer_bank_account_id, ct.recurred_from_trx_number,
ct.status_trx, ct.default_tax_exempt_flag, ct.sold_to_customer_id,
ct.sold_to_site_use_id, ct.sold_to_contact_id,
ct.bill_to_customer_id, ct.bill_to_site_use_id,
raa_bill.cust_acct_site_id, ct.bill_to_contact_id,
rac_bill_party.jgzz_fiscal_code, ct.ship_to_customer_id,
ct.ship_to_site_use_id, raa_ship.cust_acct_site_id,
ct.ship_to_contact_id, rac_ship_party.jgzz_fiscal_code,
ct.remit_to_address_id, ct.invoice_currency_code, ct.created_from,
ct.set_of_books_id, ct.printing_original_date,
ct.printing_last_printed, ct.printing_option, ct.printing_count,
ct.printing_pending, ct.last_printed_sequence_num,
ct.purchase_order, ct.purchase_order_revision,
ct.purchase_order_date, ct.customer_reference,
ct.customer_reference_date, ct.comments, ct.internal_notes,
ct.exchange_rate_type, ct.exchange_date, ct.exchange_rate,
ct.territory_id, ct.end_date_commitment, ct.start_date_commitment,
ct.orig_system_batch_name, ct.ship_date_actual, ct.waybill_number,
ct.doc_sequence_id, ct.doc_sequence_value, ct.paying_customer_id,
ct.paying_site_use_id, ct.attribute_category, ct.attribute1,
ct.attribute2, ct.attribute3, ct.attribute4, ct.attribute5,
ct.attribute6, ct.attribute7, ct.attribute8, ct.attribute9,
ct.attribute10, ct.attribute11, ct.attribute12, ct.attribute13,
ct.attribute14, ct.attribute15, ct.interface_header_context,
ct.interface_header_attribute1, ct.interface_header_attribute2,
ct.interface_header_attribute3, ct.interface_header_attribute4,
ct.interface_header_attribute5, ct.interface_header_attribute6,
ct.interface_header_attribute7, ct.interface_header_attribute8,
ct.interface_header_attribute9, ct.interface_header_attribute10,
ct.interface_header_attribute11, ct.interface_header_attribute12,
ct.interface_header_attribute13, ct.interface_header_attribute14,
ct.interface_header_attribute15, ct.global_attribute1,
ct.global_attribute2, ct.global_attribute3, ct.global_attribute4,
ct.global_attribute5, ct.global_attribute6, ct.global_attribute7,
ct.global_attribute8, ct.global_attribute9, ct.global_attribute10,
ct.global_attribute11, ct.global_attribute12, ct.global_attribute13,
ct.global_attribute14, ct.global_attribute15, ct.global_attribute16,
ct.global_attribute17, ct.global_attribute18, ct.global_attribute19,
ct.global_attribute20, ct.global_attribute_category,
ct.default_ussgl_transaction_code, ct.last_update_date,
ct.last_updated_by, ct.creation_date, ct.created_by,
ct.last_update_login, ct.request_id, rac_bill_party.party_name,
rac_bill.account_number, su_bill.LOCATION, raa_bill_loc.address1,
raa_bill_loc.address2, raa_bill_loc.address3,
DECODE
(raa_bill.cust_acct_site_id,
NULL, NULL,
arh_addr_pkg.format_last_address_line
(raa_bill_loc.address_style,
raa_bill_loc.address3,
raa_bill_loc.address4,
raa_bill_loc.city,
raa_bill_loc.county,
raa_bill_loc.state,
raa_bill_loc.province,
ft_bill.territory_short_name,
raa_bill_loc.postal_code
)
),
raa_bill_loc.city, raa_bill_loc.county, raa_bill_loc.state,
raa_bill_loc.province, raa_bill_loc.postal_code,
ft_bill.territory_short_name,
DECODE
(raa_bill.cust_acct_site_id,
NULL, NULL,
arh_addr_pkg.arxtw_format_address (raa_bill_loc.address_style,
raa_bill_loc.address1,
raa_bill_loc.address2,
raa_bill_loc.address3,
raa_bill_loc.address4,
raa_bill_loc.city,
raa_bill_loc.county,
raa_bill_loc.state,
raa_bill_loc.province,
raa_bill_loc.postal_code,
ft_bill.territory_short_name
)
),
DECODE (SUBSTRB (raco_bill_party.person_last_name, 1, 50),
NULL, SUBSTRB (raco_bill_party.person_first_name, 1, 40),
SUBSTRB (raco_bill_party.person_last_name, 1, 50)
|| ', '
|| SUBSTRB (raco_bill_party.person_first_name, 1, 40)
),
rac_ship_party.party_name, rac_ship.account_number,
su_ship.LOCATION, raa_ship_loc.address1, raa_ship_loc.address2,
raa_ship_loc.address3,
DECODE
(raa_ship.cust_acct_site_id,
NULL, NULL,
arh_addr_pkg.format_last_address_line
(raa_ship_loc.address_style,
raa_ship_loc.address3,
raa_ship_loc.address4,
raa_ship_loc.city,
raa_ship_loc.county,
raa_ship_loc.state,
raa_ship_loc.province,
ft_ship.territory_short_name,
raa_ship_loc.postal_code
)
),
raa_ship_loc.city, raa_ship_loc.county, raa_ship_loc.state,
raa_ship_loc.province, raa_ship_loc.postal_code,
ft_ship.territory_short_name,
DECODE
(raa_ship.cust_acct_site_id,
NULL, NULL,
arh_addr_pkg.arxtw_format_address (raa_ship_loc.address_style,
raa_ship_loc.address1,
raa_ship_loc.address2,
raa_ship_loc.address3,
raa_ship_loc.address4,
raa_ship_loc.city,
raa_ship_loc.county,
raa_ship_loc.state,
raa_ship_loc.province,
raa_ship_loc.postal_code,
ft_ship.territory_short_name
)
),
DECODE (SUBSTRB (raco_ship_party.person_last_name, 1, 50),
NULL, SUBSTRB (raco_ship_party.person_first_name, 1, 40),
SUBSTRB (raco_ship_party.person_last_name, 1, 50)
|| ', '
|| SUBSTRB (raco_ship_party.person_first_name, 1, 40)
),
rac_sold_party.party_name, rac_sold.account_number,
rac_paying_party.party_name, rac_paying.account_number,
su_paying.LOCATION, raa_remit_loc.address1, raa_remit_loc.address2,
raa_remit_loc.address3,
DECODE
(raa_remit.cust_acct_site_id,
NULL, NULL,
arh_addr_pkg.format_last_address_line
(raa_remit_loc.address_style,
raa_remit_loc.address3,
raa_remit_loc.address4,
raa_remit_loc.city,
raa_remit_loc.county,
raa_remit_loc.state,
raa_remit_loc.province,
ft_remit.territory_short_name,
raa_remit_loc.postal_code
)
),
raa_remit_loc.city, raa_remit_loc.county, raa_remit_loc.state,
raa_remit_loc.province, raa_remit_loc.postal_code,
ft_remit.territory_short_name,
DECODE
(raa_remit.cust_acct_site_id,
NULL, NULL,
arh_addr_pkg.arxtw_format_address (raa_remit_loc.address_style,
raa_remit_loc.address1,
raa_remit_loc.address2,
raa_remit_loc.address3,
raa_remit_loc.address4,
raa_remit_loc.city,
raa_remit_loc.county,
raa_remit_loc.state,
raa_remit_loc.province,
raa_remit_loc.postal_code,
ft_remit.territory_short_name
)
),
NULL, NULL, NULL, NULL, NULL, arm.NAME, arm.payment_channel_code,
arc.creation_method_code, bs.NAME, bs.auto_trx_numbering_flag,
bs.copy_doc_number_flag, rab.NAME, ctt.NAME, ctt.TYPE,
arpt_sql_func_util.get_salesrep_name_number (ct.primary_salesrep_id,
'NAME',
ct.org_id
),
arpt_sql_func_util.get_salesrep_name_number (ct.primary_salesrep_id,
'NUMBER',
ct.org_id
),
rat.NAME, rat.in_use, soa.NAME, orf.description,
orf.organization_id, al_fob.meaning, al_tax.meaning,
ct.ct_reference, gd.gl_date, gdct.user_conversion_type,
arpt_sql_func_util.get_cb_invoice (ct.customer_trx_id, ctt.TYPE),
arpt_sql_func_util.get_dispute_amount (ct.customer_trx_id,
ctt.TYPE,
ctt.accounting_affect_flag
),
arpt_sql_func_util.get_dispute_date (ct.customer_trx_id,
ctt.TYPE,
ctt.accounting_affect_flag
),
arpt_sql_func_util.get_max_dispute_date (ct.customer_trx_id,
ctt.TYPE,
ctt.accounting_affect_flag
),
arpt_sql_func_util.get_revenue_recog_run_flag (ct.customer_trx_id,
ct.invoicing_rule_id
),
arpt_sql_func_util.get_posted_flag (ct.customer_trx_id,
ctt.post_to_gl,
ct.complete_flag
),
arpt_sql_func_util.get_selected_for_payment_flag
(ct.customer_trx_id,
ctt.accounting_affect_flag,
ct.complete_flag
),
arpt_sql_func_util.get_activity_flag (ct.customer_trx_id,
ctt.accounting_affect_flag,
ct.complete_flag,
ctt.TYPE,
ct.initial_customer_trx_id,
ct.previous_customer_trx_id
),
ctt.post_to_gl, ctt.accounting_affect_flag, ctt.allow_freight_flag,
ctt.creation_sign, ctt.allow_overapplication_flag,
ctt.natural_application_only_flag, ctt.tax_calculation_flag,
ctt.default_status, ctt.default_term, ctt.default_printing_option,
DECODE (ct.invoicing_rule_id, NULL, 'N', 'Y'),
DECODE (ct.printing_last_printed, NULL, 'N', 'Y'),
DECODE (ct.previous_customer_trx_id, NULL, 'N', 'Y'),
su_bill.status, rac_bill.status,
arpt_sql_func_util.get_override_terms (ct.bill_to_customer_id,
ct.bill_to_site_use_id
),
DECODE (ct.initial_customer_trx_id,
NULL, DECODE (ctt.TYPE,
'DEP', 'N',
'GUAR', 'N',
'CB', 'N',
'Y'
),
'Y'
),
DECODE
(ct.agreement_id,
NULL, DECODE
(ctt.TYPE,
'CM', 'N',
arpt_sql_func_util.get_agreements_exist_flag
(ct.bill_to_customer_id,
ct.trx_date
)
),
'Y'
),
fnd_attachment_util_pkg.get_atchmt_exists ('RA_CUSTOMER_TRX',
ct.customer_trx_id
),
ct.global_attribute21, ct.global_attribute22, ct.global_attribute23,
ct.global_attribute24, ct.global_attribute15, ct.global_attribute26,
ct.global_attribute27, ct.global_attribute28, ct.global_attribute29,
ct.global_attribute30, bs.batch_source_type, ct.org_id, NULL,
ct.legal_entity_id, ct.payment_trxn_extension_id,
arm.payment_channel_code, ct.billing_date /* R12:ECB */
FROM ra_cust_trx_line_gl_dist_all gd,
ra_customer_trx ct,
hz_cust_accounts rac_bill,
hz_parties rac_bill_party,
hz_cust_accounts rac_ship,
hz_parties rac_ship_party,
hz_cust_accounts rac_sold,
hz_parties rac_sold_party,
hz_cust_accounts rac_paying,
hz_parties rac_paying_party,
hz_cust_site_uses_all su_bill,
hz_cust_site_uses_all su_ship,
hz_cust_site_uses_all su_paying,
fnd_territories_vl ft_bill,
fnd_territories_vl ft_ship,
fnd_territories_vl ft_remit,
hz_cust_acct_sites raa_bill,
hz_party_sites raa_bill_ps,
hz_locations raa_bill_loc,
hz_cust_acct_sites raa_ship,
hz_party_sites raa_ship_ps,
hz_locations raa_ship_loc,
hz_cust_acct_sites raa_remit,
hz_party_sites raa_remit_ps,
hz_locations raa_remit_loc,
hz_cust_account_roles raco_ship,
hz_parties raco_ship_party,
hz_relationships raco_ship_rel,
hz_cust_account_roles raco_bill,
hz_parties raco_bill_party,
hz_relationships raco_bill_rel,
ar_receipt_methods arm,
ar_receipt_classes arc,
ra_batch_sources_all bs,
ra_batches_all rab,
ra_cust_trx_types_all ctt,
ra_terms rat,
so_agreements soa,
org_freight orf,
gl_daily_conversion_types gdct,
ra_customer_trx_all ct_rel,
ar_lookups al_fob,
ar_lookups al_tax
WHERE ct.customer_trx_id = gd.customer_trx_id
AND 'REC' = gd.account_class
AND 'Y' = gd.latest_rec_flag
AND ct.related_customer_trx_id = ct_rel.customer_trx_id(+)
AND ct.bill_to_customer_id = rac_bill.cust_account_id
AND rac_bill.party_id = rac_bill_party.party_id
AND ct.ship_to_customer_id = rac_ship.cust_account_id(+)
AND rac_ship.party_id = rac_ship_party.party_id(+)
AND ct.sold_to_customer_id = rac_sold.cust_account_id(+)
AND rac_sold.party_id = rac_sold_party.party_id(+)
AND ct.paying_customer_id = rac_paying.cust_account_id(+)
AND rac_paying.party_id = rac_paying_party.party_id(+)
AND ct.bill_to_site_use_id = su_bill.site_use_id
AND ct.ship_to_site_use_id = su_ship.site_use_id(+)
AND ct.paying_site_use_id = su_paying.site_use_id(+)
AND su_bill.cust_acct_site_id = raa_bill.cust_acct_site_id
AND raa_bill.party_site_id = raa_bill_ps.party_site_id
AND raa_bill_loc.location_id = raa_bill_ps.location_id
AND su_ship.cust_acct_site_id = raa_ship.cust_acct_site_id(+)
AND raa_ship.party_site_id = raa_ship_ps.party_site_id(+)
AND raa_ship_loc.location_id(+) = raa_ship_ps.location_id
AND ct.bill_to_contact_id = raco_bill.cust_account_role_id(+)
AND raco_bill.party_id = raco_bill_rel.party_id(+)
AND raco_bill_rel.subject_table_name(+) = 'HZ_PARTIES'
AND raco_bill_rel.object_table_name(+) = 'HZ_PARTIES'
AND raco_bill_rel.directional_flag(+) = 'F'
AND raco_bill.role_type(+) = 'CONTACT'
AND raco_bill_rel.subject_id = raco_bill_party.party_id(+)
AND ct.ship_to_contact_id = raco_ship.cust_account_role_id(+)
AND raco_ship.party_id = raco_ship_rel.party_id(+)
AND raco_ship_rel.subject_table_name(+) = 'HZ_PARTIES'
AND raco_ship_rel.object_table_name(+) = 'HZ_PARTIES'
AND raco_ship_rel.directional_flag(+) = 'F'
AND raco_ship.role_type(+) = 'CONTACT'
AND raco_ship_rel.subject_id = raco_ship_party.party_id(+)
AND ct.remit_to_address_id = raa_remit.cust_acct_site_id(+)
AND raa_remit.party_site_id = raa_remit_ps.party_site_id(+)
AND raa_remit_loc.location_id(+) = raa_remit_ps.location_id
AND raa_bill_loc.country = ft_bill.territory_code(+)
AND raa_ship_loc.country = ft_ship.territory_code(+)
AND raa_remit_loc.country = ft_remit.territory_code(+)
AND ct.receipt_method_id = arm.receipt_method_id(+)
AND arm.receipt_class_id = arc.receipt_class_id(+)
AND ct.batch_source_id = bs.batch_source_id
AND ct.batch_id = rab.batch_id(+)
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.TYPE <> 'BR'
AND ct.term_id = rat.term_id(+)
AND ct.agreement_id = soa.agreement_id(+)
AND ct.exchange_rate_type = gdct.conversion_type(+)
AND 'FOB' = al_fob.lookup_type(+)
AND ct.fob_point = al_fob.lookup_code(+)
AND ct.ship_via = orf.freight_code(+)
AND ct.org_id = orf.organization_id(+)
AND 'TAX_CONTROL_FLAG' = al_tax.lookup_type(+)
AND ct.default_tax_exempt_flag = al_tax.lookup_code(+)
AND ctt.org_id = ct.org_id
AND bs.org_id = ct.org_id
/
|
] 
Blog
Search
Help
Members
Register
Login
Home
