AR_INVOICE_HEADER

Details

Schema: FUSION
Object owner: AR
Object type: VIEW

Columns

Name
OPEN_TRX CUST_TRX_TYPE_SEQ_ID CLASS_NAME CLASS TYPE_NAME CUSTOMER_CLASS_CODE BATCH_ID TERM_SEQUENCE_NUMBER CUSTOMER_TRX_ID TRX_NUMBER TRX_DATE SHIP_TO_CUSTOMER_ID SHIP_TO_CONTACT_ID REMIT_TO_ADDRESS_ID OPEN_RECEIVABLE_FLAG INTERNAL_NOTES COMMENTS PREVIOUS_TRX_NUMBER PRINTING_COUNT PRINTING_ORIGINAL_DATE PRINTING_LAST_PRINTED PRINTING_PENDING LAST_PRINTED_SEQUENCE_NUMBER START_DATE_COMMITMENT END_DATE_COMMITMENT INITIAL_CUSTOMER_TRX_ID PREVIOUS_CUSTOMER_TRX_ID INVOICE_CURRENCY_CODE TERM_NAME TERM_ID PRIMARY_RESOURCE_SALESREP_ID SHIP_DATE_ACTUAL SHIP_VIA WAYBILL_NUMBER PURCHASE_ORDER_NUMBER PURCHASE_ORDER_REVISION PURCHASE_ORDER_DATE TERM_DUE_DATE_FROM_PS TERM_PERCENT BILL_TO_CUSTOMER_ID BILL_TO_CONTACT_ID BILL_TO_SITE_USE_ID BILL_TO_LOCATION BILL_TO_CUSTOMER_NUMBER BILL_TO_CUSTOMER_NAME DEFAULT_BILL_ATTN BILL_TO_SITE_TAX_REFERENCE BILL_TO_CUST_TAX_REFERENCE BILL_TO_ADDRESS1 BILL_TO_ADDRESS2 BILL_TO_ADDRESS3 BILL_TO_ADDRESS4 BILL_TO_CITY BILL_TO_STATE BILL_TO_PROVINCE BILL_TO_POSTAL_CODE BILL_TO_COUNTRY BILL_TO_COUNTRY_NAME BILL_TO_CONTACT_FIRST_NAME BILL_TO_CONTACT_LAST_NAME BILL_TO_ATTN BILL_TO_MAIL_STOP LINE_AMOUNT TAX_AMOUNT FREIGHT_AMOUNT TOTAL_AMOUNT PRIMARY_SALESREP_NAME SHIP_VIA_DESCRIPTION SO_ORGANIZATION_ID SHIP_TO_CUSTOMER_NAME SHIP_TO_ADDRESS1 SHIP_TO_ADDRESS2 SHIP_TO_ADDRESS3 SHIP_TO_ADDRESS4 SHIP_TO_CITY SHIP_TO_POSTAL_CODE SHIP_TO_COUNTRY SHIP_TO_COUNTRY_NAME SHIP_TO_SITE_USE_ID SHIP_TO_LOCATION SHIP_TO_SITE_TAX_REFERENCE SHIP_TO_CUST_TAX_REFERENCE SHIP_TO_STATE SHIP_TO_PROVINCE SHIP_TO_CONTACT_FIRST_NAME SHIP_TO_CONTACT_LAST_NAME SHIP_TO_ATTN SHIP_TO_MAIL_STOP TAX_REFERENCE BATCH_SOURCE_NAME AMOUNT_APPLIED_TO_COMMITMENT REMIT_TO_ADDRESS1 REMIT_TO_ADDRESS2 REMIT_TO_ADDRESS3 REMIT_TO_ADDRESS4 REMIT_TO_CITY REMIT_TO_STATE REMIT_TO_POSTAL_CODE REMIT_TO_COUNTRY REMIT_TO_COUNTRY_NAME TAX_REGISTRATION_NUMBER NUMBER_OF_TERMS INTEREST_RATE DESCRIPTION JGZZ_FISCAL_CODE INTERFACE_HEADER_CONTEXT DEFAULT_USSGL_TRX_CODE_CONTEXT DEFAULT_USSGL_TRANSACTION_CODE RECURRED_FROM_TRX_NUMBER STATUS_TRX DOC_SEQUENCE_ID DOC_SEQUENCE_VALUE PAYING_CUSTOMER_ID PAYING_SITE_USE_ID RELATED_BATCH_SOURCE_SEQ_ID DEFAULT_TAX_EXEMPT_FLAG CREATED_FROM ORG_ID REQUEST_ID PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE FINANCE_CHARGES POSTING_CONTROL_ID BILL_TO_ADDRESS_ID RA_POST_LOOP_NUMBER SHIP_TO_ADDRESS_ID CREDIT_METHOD_FOR_RULES CREDIT_METHOD_FOR_INSTALLMENTS RECEIPT_METHOD_ID RELATED_CUSTOMER_TRX_ID INVOICING_RULE_ID FOB_POINT CUSTOMER_BANK_ACCOUNT_ID 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 PRINTING_OPTION CUSTOMER_REFERENCE CUSTOMER_REFERENCE_DATE EXCHANGE_RATE_TYPE EXCHANGE_DATE EXCHANGE_RATE TERRITORY_ID AGREEMENT_ID ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ORIG_SYSTEM_BATCH_NAME POST_REQUEST_ID LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN SET_OF_BOOKS_ID BATCH_SOURCE_SEQ_ID REASON_CODE SOLD_TO_CUSTOMER_ID SOLD_TO_CONTACT_ID SOLD_TO_SITE_USE_ID SHIPMENT_ID TERM_DUE_DATE 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 WH_UPDATE_DATE FORMATTED_BILL_TO_ADDRESS FORMATTED_SHIP_TO_ADDRESS FORMATTED_REMIT_TO_ADDRESS BILL_TEMPLATE_ID

Name

OPEN_TRX

CUST_TRX_TYPE_SEQ_ID

CLASS_NAME

CLASS

TYPE_NAME

CUSTOMER_CLASS_CODE

BATCH_ID

TERM_SEQUENCE_NUMBER

CUSTOMER_TRX_ID

TRX_NUMBER

TRX_DATE

SHIP_TO_CUSTOMER_ID

SHIP_TO_CONTACT_ID

REMIT_TO_ADDRESS_ID

OPEN_RECEIVABLE_FLAG

INTERNAL_NOTES

COMMENTS

PREVIOUS_TRX_NUMBER

PRINTING_COUNT

PRINTING_ORIGINAL_DATE

PRINTING_LAST_PRINTED

PRINTING_PENDING

LAST_PRINTED_SEQUENCE_NUMBER

START_DATE_COMMITMENT

END_DATE_COMMITMENT

INITIAL_CUSTOMER_TRX_ID

PREVIOUS_CUSTOMER_TRX_ID

INVOICE_CURRENCY_CODE

TERM_NAME

TERM_ID

PRIMARY_RESOURCE_SALESREP_ID

SHIP_DATE_ACTUAL

SHIP_VIA

WAYBILL_NUMBER

PURCHASE_ORDER_NUMBER

PURCHASE_ORDER_REVISION

PURCHASE_ORDER_DATE

TERM_DUE_DATE_FROM_PS

TERM_PERCENT

BILL_TO_CUSTOMER_ID

BILL_TO_CONTACT_ID

BILL_TO_SITE_USE_ID

BILL_TO_LOCATION

BILL_TO_CUSTOMER_NUMBER

BILL_TO_CUSTOMER_NAME

DEFAULT_BILL_ATTN

BILL_TO_SITE_TAX_REFERENCE

BILL_TO_CUST_TAX_REFERENCE

BILL_TO_ADDRESS1

BILL_TO_ADDRESS2

BILL_TO_ADDRESS3

BILL_TO_ADDRESS4

BILL_TO_CITY

BILL_TO_STATE

BILL_TO_PROVINCE

BILL_TO_POSTAL_CODE

BILL_TO_COUNTRY

BILL_TO_COUNTRY_NAME

BILL_TO_CONTACT_FIRST_NAME

BILL_TO_CONTACT_LAST_NAME

BILL_TO_ATTN

BILL_TO_MAIL_STOP

LINE_AMOUNT

TAX_AMOUNT

FREIGHT_AMOUNT

TOTAL_AMOUNT

PRIMARY_SALESREP_NAME

SHIP_VIA_DESCRIPTION

SO_ORGANIZATION_ID

SHIP_TO_CUSTOMER_NAME

SHIP_TO_ADDRESS1

SHIP_TO_ADDRESS2

SHIP_TO_ADDRESS3

SHIP_TO_ADDRESS4

SHIP_TO_CITY

SHIP_TO_POSTAL_CODE

SHIP_TO_COUNTRY

SHIP_TO_COUNTRY_NAME

SHIP_TO_SITE_USE_ID

SHIP_TO_LOCATION

SHIP_TO_SITE_TAX_REFERENCE

SHIP_TO_CUST_TAX_REFERENCE

SHIP_TO_STATE

SHIP_TO_PROVINCE

SHIP_TO_CONTACT_FIRST_NAME

SHIP_TO_CONTACT_LAST_NAME

SHIP_TO_ATTN

SHIP_TO_MAIL_STOP

TAX_REFERENCE

BATCH_SOURCE_NAME

AMOUNT_APPLIED_TO_COMMITMENT

REMIT_TO_ADDRESS1

REMIT_TO_ADDRESS2

REMIT_TO_ADDRESS3

REMIT_TO_ADDRESS4

REMIT_TO_CITY

REMIT_TO_STATE

REMIT_TO_POSTAL_CODE

REMIT_TO_COUNTRY

REMIT_TO_COUNTRY_NAME

TAX_REGISTRATION_NUMBER

NUMBER_OF_TERMS

INTEREST_RATE

DESCRIPTION

JGZZ_FISCAL_CODE

INTERFACE_HEADER_CONTEXT

DEFAULT_USSGL_TRX_CODE_CONTEXT

DEFAULT_USSGL_TRANSACTION_CODE

RECURRED_FROM_TRX_NUMBER

STATUS_TRX

DOC_SEQUENCE_ID

DOC_SEQUENCE_VALUE

PAYING_CUSTOMER_ID

PAYING_SITE_USE_ID

RELATED_BATCH_SOURCE_SEQ_ID

DEFAULT_TAX_EXEMPT_FLAG

CREATED_FROM

ORG_ID

REQUEST_ID

PROGRAM_APPLICATION_ID

PROGRAM_ID

PROGRAM_UPDATE_DATE

FINANCE_CHARGES

POSTING_CONTROL_ID

BILL_TO_ADDRESS_ID

RA_POST_LOOP_NUMBER

SHIP_TO_ADDRESS_ID

CREDIT_METHOD_FOR_RULES

CREDIT_METHOD_FOR_INSTALLMENTS

RECEIPT_METHOD_ID

RELATED_CUSTOMER_TRX_ID

INVOICING_RULE_ID

FOB_POINT

CUSTOMER_BANK_ACCOUNT_ID

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

PRINTING_OPTION

CUSTOMER_REFERENCE

CUSTOMER_REFERENCE_DATE

EXCHANGE_RATE_TYPE

EXCHANGE_DATE

EXCHANGE_RATE

TERRITORY_ID

AGREEMENT_ID

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ORIG_SYSTEM_BATCH_NAME

POST_REQUEST_ID

LAST_UPDATE_DATE

LAST_UPDATED_BY

CREATION_DATE

CREATED_BY

LAST_UPDATE_LOGIN

SET_OF_BOOKS_ID

BATCH_SOURCE_SEQ_ID

REASON_CODE

SOLD_TO_CUSTOMER_ID

SOLD_TO_CONTACT_ID

SOLD_TO_SITE_USE_ID

SHIPMENT_ID

TERM_DUE_DATE

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

WH_UPDATE_DATE

FORMATTED_BILL_TO_ADDRESS

FORMATTED_SHIP_TO_ADDRESS

FORMATTED_REMIT_TO_ADDRESS

BILL_TEMPLATE_ID

Query

SQL_Statement
SELECT o_type.meaning open_trx, trx.cust_trx_type_seq_id cust_trx_type_seq_id, l_type.meaning class_name, types.type class, types.name TYPE_NAME, b_bill.customer_class_code customer_class_code, trx.batch_id batch_id, nvl(nvl(tl.sequence_num, ps.terms_sequence_number), 1) term_sequence_number, trx.customer_trx_id customer_trx_id, trx.trx_number trx_number, trx.trx_date trx_date, trx.ship_to_customer_id ship_to_customer_id, trx.ship_to_contact_id ship_to_contact_id, trx.remit_to_address_id remit_to_address_id, types.accounting_affect_flag open_receivable_flag, trx.internal_notes internal_notes, trx.comments comments, prevtrx.trx_number previous_trx_number, nvl(trx.printing_count, 0) printing_count, trx.printing_original_date printing_original_date, trx.printing_last_printed printing_last_printed, trx.printing_pending printing_pending, trx.last_printed_sequence_num last_printed_sequence_number, trx.start_date_commitment start_date_commitment, trx.end_date_commitment end_date_commitment, trx.initial_customer_trx_id initial_customer_trx_id, trx.previous_customer_trx_id previous_customer_trx_id, trx.invoice_currency_code invoice_currency_code, t.name term_name, trx.term_id term_id, trx.primary_resource_salesrep_id primary_resource_salesrep_id, trx.ship_date_actual ship_date_actual, trx.ship_via ship_via, trx.waybill_number waybill_number, trx.purchase_order purchase_order_number, trx.purchase_order_revision purchase_order_revision, trx.purchase_order_date purchase_order_date, ps.due_date term_due_date_from_ps, nvl(tl.relative_amount, 100) (100 / nvl(t.base_amount, 100)) term_percent, trx.bill_to_customer_id bill_to_customer_id, trx.bill_to_contact_id bill_to_contact_id, trx.bill_to_site_use_id bill_to_site_use_id, u_bill.location bill_to_location, b_bill.account_number bill_to_customer_number, substrb(b_bill_party.party_name, 1, 50) bill_to_customer_name, decode(nvl(substrb(c_bill_party.person_first_name, 1, 40), '') \|\| nvl(substrb(c_bill_party.person_last_name, 1, 50), ''), '', nvl(a_type.meaning, 'Attn: Accounts Payable'), substrb(c_bill_party.person_first_name, 1, 40) \|\| ' ' \|\| substrb(c_bill_party.person_last_name, 1, 50)) default_bill_attn, null bill_to_site_tax_reference, / u_bill.tax_reference bill_to_site_tax_reference, null bill_to_cust_tax_reference, /* b_bill_party.tax_reference bill_to_cust_tax_reference, a_bill_loc.address1 bill_to_address1, a_bill_loc.address2 bill_to_address2, a_bill_loc.address3 bill_to_address3, a_bill_loc.address4 bill_to_address4, a_bill_loc.city bill_to_city, a_bill_loc.state bill_to_state, a_bill_loc.province bill_to_province, a_bill_loc.postal_code bill_to_postal_code, a_bill_loc.country bill_to_country, decode(a_bill_loc.country, NULL, NULL, nvl(sp.default_country, 'xxxxxx'), decode(sp.print_home_country_flag, 'Y', dft.territory_short_name, NULL), nvl(ft.territory_short_name, a_bill_loc.country)) bill_to_country_name, substrb(c_bill_party.person_first_name, 1, 40) bill_to_contact_first_name, substrb(c_bill_party.person_last_name, 1, 50) bill_to_contact_last_name, substrb(c_bill_party.person_first_name, 1, 40) \|\| ' ' \|\| substrb(c_bill_party.person_last_name, 1, 50) bill_to_attn, null bill_to_mail_stop, nvl(amount_line_items_original + decode(trx.initial_customer_trx_id, '', 0, nvl(com_adj.amount, 0)), to_number(NULL)) line_amount, nvl(tax_original, to_number(NULL)) tax_amount, nvl(freight_original, to_number(NULL)) freight_amount, ps.amount_due_original + decode(trx.initial_customer_trx_id, '', 0, nvl(com_adj.amount, 0)) total_amount, decode(sales.resource_salesrep_id, '', '', '', '', -1, '', -2, '', -3, '', sales.name) primary_salesrep_name, trx.ship_via ship_via_description, trx.org_id so_organization_id, substrb(b_ship_party.party_name, 1, 50) ship_to_customer_name, a_ship_loc.address1 ship_to_address1, a_ship_loc.address2 ship_to_address2, a_ship_loc.address3 ship_to_address3, a_ship_loc.address4 ship_to_address4, a_ship_loc.city ship_to_city, a_ship_loc.postal_code ship_to_postal_code, a_ship_loc.country ship_to_country, decode(a_ship_loc.country, NULL, NULL, nvl(sp.default_country, 'xxxxxx'), decode(sp.print_home_country_flag, 'Y', dft.territory_short_name, NULL), nvl(ft2.territory_short_name, a_ship_loc.country)) ship_to_country_name, u_ship.site_use_id ship_to_site_use_id, u_ship.location ship_to_location, null ship_to_site_tax_reference, /* u_ship.tax_reference ship_to_site_tax_reference, null ship_to_cust_tax_reference, /* b_ship_party.tax_reference ship_to_cust_tax_reference, a_ship_loc.state ship_to_state, a_ship_loc.province ship_to_province, substrb(c_ship_party.person_first_name, 1, 40) ship_to_contact_first_name, substrb(c_ship_party.person_last_name, 1, 50) ship_to_contact_last_name, substrb(c_ship_party.person_first_name, 1, 40) \|\| ' ' \|\| substrb(c_ship_party.person_last_name, 1, 50) ship_to_attn, null ship_to_mail_stop, null tax_reference, /* nvl(u_ship.tax_reference, nvl(u_bill.tax_reference, nvl(b_ship_party.tax_reference, b_bill_party.tax_reference))) tax_reference, bs.name batch_source_name, ar_invoice_sql_func_pub.get_commit_this_invoice(trx.customer_trx_id) amount_applied_to_commitment, a_remit_loc.address1 remit_to_address1, a_remit_loc.address2 remit_to_address2, a_remit_loc.address3 remit_to_address3, a_remit_loc.address4 remit_to_address4, a_remit_loc.city remit_to_city, a_remit_loc.state remit_to_state, a_remit_loc.postal_code remit_to_postal_code, a_remit_loc.country remit_to_country, decode(a_remit_loc.country, NULL, NULL, nvl(sp.default_country, 'xxxxxx'), decode(sp.print_home_country_flag, 'Y', dft.territory_short_name, NULL), nvl(ft.territory_short_name, a_remit_loc.country)) remit_to_country_name, sp.tax_registration_number tax_registration_number, t_count.number_of_terms number_of_terms, amount.interest_rate, types.description, b_bill_party.jgzz_fiscal_code, trx.interface_header_context, trx.default_ussgl_trx_code_context, trx.default_ussgl_transaction_code, trx.recurred_from_trx_number, trx.status_trx, trx.doc_sequence_id, trx.doc_sequence_value, trx.paying_customer_id, trx.paying_site_use_id, trx.related_batch_source_seq_id, trx.default_tax_exempt_flag, trx.created_from, trx.org_id, trx.request_id, trx.program_application_id, trx.program_id, trx.program_update_date, trx.finance_charges, trx.posting_control_id, trx.bill_to_address_id, trx.ra_post_loop_number, trx.ship_to_address_id, trx.credit_method_for_rules, trx.credit_method_for_installments, trx.receipt_method_id, trx.related_customer_trx_id, trx.invoicing_rule_id, trx.fob_point, trx.customer_bank_account_id, trx.interface_header_attribute1, trx.interface_header_attribute2, trx.interface_header_attribute3, trx.interface_header_attribute4, trx.interface_header_attribute5, trx.interface_header_attribute6, trx.interface_header_attribute7, trx.interface_header_attribute8, trx.interface_header_attribute9, trx.interface_header_attribute10, trx.interface_header_attribute11, trx.interface_header_attribute12, trx.interface_header_attribute13, trx.interface_header_attribute14, trx.interface_header_attribute15, trx.printing_option, trx.customer_reference, trx.customer_reference_date, trx.exchange_rate_type, trx.exchange_date, trx.exchange_rate, trx.territory_id, trx.agreement_id, trx.attribute_category, trx.attribute1, trx.attribute2, trx.attribute3, trx.attribute4, trx.attribute5, trx.attribute6, trx.attribute7, trx.attribute8, trx.attribute9, trx.attribute10, trx.attribute11, trx.attribute12, trx.attribute13, trx.attribute14, trx.attribute15, trx.orig_system_batch_name, trx.post_request_id, trx.last_update_date, trx.last_updated_by, trx.creation_date, trx.created_by, trx.last_update_login, trx.set_of_books_id, trx.batch_source_seq_id, trx.reason_code, trx.sold_to_customer_id, trx.sold_to_contact_id, trx.sold_to_site_use_id, trx.shipment_id, trx.term_due_date, trx.global_attribute1, trx.global_attribute2, trx.global_attribute3, trx.global_attribute4, trx.global_attribute5, trx.global_attribute6, trx.global_attribute7, trx.global_attribute8, trx.global_attribute9, trx.global_attribute10, trx.global_attribute11, trx.global_attribute12, trx.global_attribute13, trx.global_attribute14, trx.global_attribute15, trx.global_attribute16, trx.global_attribute17, trx.global_attribute18, trx.global_attribute19, trx.global_attribute20, trx.global_attribute_category, trx.wh_update_date, hz_format_pub.format_address(a_bill_loc.location_id) formatted_bill_to_address, hz_format_pub.format_address(a_ship_loc.location_id) formatted_ship_to_address, hz_format_pub.format_address(a_remit_loc.location_id) formatted_remit_to_address, trx.bill_template_id FROM ar_lookups o_type, ar_lookups l_type, ar_lookups a_type, ra_batch_sources_all bs, ar_system_parameters sp, fnd_territories_vl ft, fnd_territories_vl ft2, fnd_territories_vl dft, hz_cust_profile_amts_f amount, ar_invoice_count_terms_v t_count, ar_adjustments com_adj, ar_payment_schedules ps, ra_terms_lines tl, ra_terms t, hz_cust_acct_sites_all a_bill, hz_party_sites a_bill_ps, hz_locations a_bill_loc, hz_cust_acct_sites_all a_ship, hz_party_sites a_ship_ps, hz_locations a_ship_loc, hz_cust_site_uses_all u_bill, hz_cust_site_uses_all u_ship, hz_cust_account_roles c_bill, hz_parties c_bill_party, hz_relationships c_bill_rel, hz_org_contacts c_bill_org_cont, hz_cust_account_roles c_ship, hz_parties c_ship_party, hz_relationships c_ship_rel, hz_org_contacts c_ship_org_cont, hz_cust_accounts b_bill, hz_parties b_bill_party, hz_cust_accounts b_ship, hz_parties b_ship_party, ar_remit_to_locs_all locs, hz_locations a_remit_loc, ra_salesreps sales, ra_cust_trx_types_all types, ra_customer_trx prevtrx, ra_customer_trx trx WHERE trx.complete_flag = 'Y' AND trx.cust_trx_type_seq_id = types.cust_trx_type_seq_id AND trx.term_id = tl.term_id(+) AND trx.term_id = t.term_id(+) AND trx.customer_trx_id + 0 = ps.customer_trx_id(+) AND trx.primary_resource_salesrep_id = sales.resource_salesrep_id(+) AND trx.bill_to_contact_id = c_bill.cust_account_role_id(+) AND c_bill.relationship_id = c_bill_rel.relationship_id (+) AND c_bill_rel.subject_table_name(+) = 'HZ_PARTIES' AND c_bill_rel.object_table_name(+) = 'HZ_PARTIES' AND c_bill_rel.directional_flag(+) = 'F' AND c_bill.role_type(+) = 'CONTACT' AND c_bill_org_cont.party_relationship_id(+) = c_bill_rel.relationship_id AND c_bill_rel.subject_id = c_bill_party.party_id(+) AND trx.ship_to_contact_id = c_ship.cust_account_role_id(+) AND c_ship.relationship_id = c_ship_rel.relationship_id (+) AND c_ship_rel.subject_table_name(+) = 'HZ_PARTIES' AND c_ship_rel.object_table_name(+) = 'HZ_PARTIES' AND c_ship_rel.directional_flag(+) = 'F' AND c_ship.role_type(+) = 'CONTACT' AND c_ship_org_cont.party_relationship_id(+) = c_ship_rel.relationship_id AND c_ship_rel.subject_id = c_ship_party.party_id(+) AND trx.bill_to_site_use_id = u_bill.site_use_id AND trx.ship_to_site_use_id = u_ship.site_use_id(+) AND trx.bill_to_customer_id + 0 = b_bill.cust_account_id AND b_bill.party_id = b_bill_party.party_id AND trx.ship_to_customer_id = b_ship.cust_account_id(+) AND b_ship.party_id = b_ship_party.party_id(+) AND trx.batch_source_seq_id = bs.batch_source_seq_id(+) AND trx.previous_customer_trx_id = prevtrx.customer_trx_id(+) AND trx.remit_to_address_seq_id = locs.address_loc_seq_id AND a_remit_loc.location_id = locs.location_id AND trx.bill_to_customer_id = amount.cust_account_id(+) AND trx.bill_to_site_use_id = amount.site_use_id(+) AND trx.invoice_currency_code = amount.currency_code(+) AND ps.payment_schedule_id + decode(ps.class, 'INV', 0, '') = com_adj.payment_schedule_id(+) AND com_adj.subsequent_trx_id IS NULL AND 'C' = com_adj.adjustment_type(+) AND types.default_printing_option = 'PRI' AND l_type.lookup_type = 'INV/CM/ADJ' AND l_type.lookup_code = decode(types.type, 'DEP', 'INV', types.type) AND l_type.lookup_code <> 'ADJ' AND nvl(ps.terms_sequence_number, nvl(tl.sequence_num, 0)) = nvl(tl.sequence_num, nvl(ps.terms_sequence_number, 0)) AND u_bill.cust_acct_site_id = a_bill.cust_acct_site_id(+) AND a_bill.party_site_id = a_bill_ps.party_site_id(+) AND a_bill_loc.location_id(+) = a_bill_ps.location_id AND u_ship.cust_acct_site_id = a_ship.cust_acct_site_id(+) AND a_ship.party_site_id = a_ship_ps.party_site_id(+) AND a_ship_loc.location_id(+) = a_ship_ps.location_id AND o_type.lookup_type = 'YES/NO' AND o_type.lookup_code = decode(nvl(ps.amount_due_remaining, 0), 0, 'N', 'Y') AND a_type.lookup_type = 'ADDRESS_LABEL' AND a_type.lookup_code = 'ATTN_DEFAULT_MSG' AND a_bill_loc.country = ft.territory_code(+) AND a_ship_loc.country = ft2.territory_code(+) AND nvl(sp.default_country, 'US') = dft.territory_code AND nvl(trx.term_id, -1) = t_count.term_id

SQL_Statement

SELECT o_type.meaning open_trx,

trx.cust_trx_type_seq_id cust_trx_type_seq_id,

l_type.meaning class_name,

types.type class,

types.name TYPE_NAME,

b_bill.customer_class_code customer_class_code,

trx.batch_id batch_id,

nvl(nvl(tl.sequence_num, ps.terms_sequence_number), 1) term_sequence_number,

trx.customer_trx_id customer_trx_id,

trx.trx_number trx_number,

trx.trx_date trx_date,

trx.ship_to_customer_id ship_to_customer_id,

trx.ship_to_contact_id ship_to_contact_id,

trx.remit_to_address_id remit_to_address_id,

types.accounting_affect_flag open_receivable_flag,

trx.internal_notes internal_notes,

trx.comments comments,

prevtrx.trx_number previous_trx_number,

nvl(trx.printing_count, 0) printing_count,

trx.printing_original_date printing_original_date,

trx.printing_last_printed printing_last_printed,

trx.printing_pending printing_pending,

trx.last_printed_sequence_num last_printed_sequence_number,

trx.start_date_commitment start_date_commitment,

trx.end_date_commitment end_date_commitment,

trx.initial_customer_trx_id initial_customer_trx_id,

trx.previous_customer_trx_id previous_customer_trx_id,

trx.invoice_currency_code invoice_currency_code,

t.name term_name,

trx.term_id term_id,

trx.primary_resource_salesrep_id primary_resource_salesrep_id,

trx.ship_date_actual ship_date_actual,

trx.ship_via ship_via,

trx.waybill_number waybill_number,

trx.purchase_order purchase_order_number,

trx.purchase_order_revision purchase_order_revision,

trx.purchase_order_date purchase_order_date,

ps.due_date term_due_date_from_ps,

nvl(tl.relative_amount, 100) *(100 / nvl(t.base_amount, 100)) term_percent,

trx.bill_to_customer_id bill_to_customer_id,

trx.bill_to_contact_id bill_to_contact_id,

trx.bill_to_site_use_id bill_to_site_use_id,

u_bill.location bill_to_location,

b_bill.account_number bill_to_customer_number,

substrb(b_bill_party.party_name, 1, 50) bill_to_customer_name,

decode(nvl(substrb(c_bill_party.person_first_name, 1, 40), '*') || nvl(substrb(c_bill_party.person_last_name, 1, 50), '*'), '**', nvl(a_type.meaning, 'Attn: Accounts Payable'), substrb(c_bill_party.person_first_name, 1, 40) || ' ' || substrb(c_bill_party.person_last_name, 1, 50)) default_bill_attn,

null bill_to_site_tax_reference, /* u_bill.tax_reference bill_to_site_tax_reference,

null bill_to_cust_tax_reference, /* b_bill_party.tax_reference bill_to_cust_tax_reference,

a_bill_loc.address1 bill_to_address1,

a_bill_loc.address2 bill_to_address2,

a_bill_loc.address3 bill_to_address3,

a_bill_loc.address4 bill_to_address4,

a_bill_loc.city bill_to_city,

a_bill_loc.state bill_to_state,

a_bill_loc.province bill_to_province,

a_bill_loc.postal_code bill_to_postal_code,

a_bill_loc.country bill_to_country,

decode(a_bill_loc.country, NULL, NULL, nvl(sp.default_country, 'xxxxxx'), decode(sp.print_home_country_flag, 'Y', dft.territory_short_name, NULL), nvl(ft.territory_short_name, a_bill_loc.country)) bill_to_country_name,

substrb(c_bill_party.person_first_name, 1, 40) bill_to_contact_first_name,

substrb(c_bill_party.person_last_name, 1, 50) bill_to_contact_last_name,

substrb(c_bill_party.person_first_name, 1, 40) || ' ' || substrb(c_bill_party.person_last_name, 1, 50) bill_to_attn,

null bill_to_mail_stop,

nvl(amount_line_items_original + decode(trx.initial_customer_trx_id, '', 0, nvl(com_adj.amount, 0)), to_number(NULL)) line_amount,

nvl(tax_original, to_number(NULL)) tax_amount,

nvl(freight_original, to_number(NULL)) freight_amount,

ps.amount_due_original + decode(trx.initial_customer_trx_id, '', 0, nvl(com_adj.amount, 0)) total_amount,

decode(sales.resource_salesrep_id, '', '', '', '', -1, '', -2, '', -3, '', sales.name) primary_salesrep_name,

trx.ship_via ship_via_description,

trx.org_id so_organization_id,

substrb(b_ship_party.party_name, 1, 50) ship_to_customer_name,

a_ship_loc.address1 ship_to_address1,

a_ship_loc.address2 ship_to_address2,

a_ship_loc.address3 ship_to_address3,

a_ship_loc.address4 ship_to_address4,

a_ship_loc.city ship_to_city,

a_ship_loc.postal_code ship_to_postal_code,

a_ship_loc.country ship_to_country,

decode(a_ship_loc.country, NULL, NULL, nvl(sp.default_country, 'xxxxxx'), decode(sp.print_home_country_flag, 'Y', dft.territory_short_name, NULL), nvl(ft2.territory_short_name, a_ship_loc.country)) ship_to_country_name,

u_ship.site_use_id ship_to_site_use_id,

u_ship.location ship_to_location,

null ship_to_site_tax_reference, /* u_ship.tax_reference ship_to_site_tax_reference,

null ship_to_cust_tax_reference, /* b_ship_party.tax_reference ship_to_cust_tax_reference,

a_ship_loc.state ship_to_state,

a_ship_loc.province ship_to_province,

substrb(c_ship_party.person_first_name, 1, 40) ship_to_contact_first_name,

substrb(c_ship_party.person_last_name, 1, 50) ship_to_contact_last_name,

substrb(c_ship_party.person_first_name, 1, 40) || ' ' || substrb(c_ship_party.person_last_name, 1, 50) ship_to_attn,

null ship_to_mail_stop,

null tax_reference, /* nvl(u_ship.tax_reference, nvl(u_bill.tax_reference, nvl(b_ship_party.tax_reference, b_bill_party.tax_reference))) tax_reference,

bs.name batch_source_name,

ar_invoice_sql_func_pub.get_commit_this_invoice(trx.customer_trx_id) amount_applied_to_commitment,

a_remit_loc.address1 remit_to_address1,

a_remit_loc.address2 remit_to_address2,

a_remit_loc.address3 remit_to_address3,

a_remit_loc.address4 remit_to_address4,

a_remit_loc.city remit_to_city,

a_remit_loc.state remit_to_state,

a_remit_loc.postal_code remit_to_postal_code,

a_remit_loc.country remit_to_country,

decode(a_remit_loc.country, NULL, NULL, nvl(sp.default_country, 'xxxxxx'), decode(sp.print_home_country_flag, 'Y', dft.territory_short_name, NULL), nvl(ft.territory_short_name, a_remit_loc.country)) remit_to_country_name,

sp.tax_registration_number tax_registration_number,

t_count.number_of_terms number_of_terms,

amount.interest_rate,

types.description,

b_bill_party.jgzz_fiscal_code,

trx.interface_header_context,

trx.default_ussgl_trx_code_context,

trx.default_ussgl_transaction_code,

trx.recurred_from_trx_number,

trx.status_trx,

trx.doc_sequence_id,

trx.doc_sequence_value,

trx.paying_customer_id,

trx.paying_site_use_id,

trx.related_batch_source_seq_id,

trx.default_tax_exempt_flag,

trx.created_from,

trx.org_id,

trx.request_id,

trx.program_application_id,

trx.program_id,

trx.program_update_date,

trx.finance_charges,

trx.posting_control_id,

trx.bill_to_address_id,

trx.ra_post_loop_number,

trx.ship_to_address_id,

trx.credit_method_for_rules,

trx.credit_method_for_installments,

trx.receipt_method_id,

trx.related_customer_trx_id,

trx.invoicing_rule_id,

trx.fob_point,

trx.customer_bank_account_id,

trx.interface_header_attribute1,

trx.interface_header_attribute2,

trx.interface_header_attribute3,

trx.interface_header_attribute4,

trx.interface_header_attribute5,

trx.interface_header_attribute6,

trx.interface_header_attribute7,

trx.interface_header_attribute8,

trx.interface_header_attribute9,

trx.interface_header_attribute10,

trx.interface_header_attribute11,

trx.interface_header_attribute12,

trx.interface_header_attribute13,

trx.interface_header_attribute14,

trx.interface_header_attribute15,

trx.printing_option,

trx.customer_reference,

trx.customer_reference_date,

trx.exchange_rate_type,

trx.exchange_date,

trx.exchange_rate,

trx.territory_id,

trx.agreement_id,

trx.attribute_category,

trx.attribute1,

trx.attribute2,

trx.attribute3,

trx.attribute4,

trx.attribute5,

trx.attribute6,

trx.attribute7,

trx.attribute8,

trx.attribute9,

trx.attribute10,

trx.attribute11,

trx.attribute12,

trx.attribute13,

trx.attribute14,

trx.attribute15,

trx.orig_system_batch_name,

trx.post_request_id,

trx.last_update_date,

trx.last_updated_by,

trx.creation_date,

trx.created_by,

trx.last_update_login,

trx.set_of_books_id,

trx.batch_source_seq_id,

trx.reason_code,

trx.sold_to_customer_id,

trx.sold_to_contact_id,

trx.sold_to_site_use_id,

trx.shipment_id,

trx.term_due_date,

trx.global_attribute1,

trx.global_attribute2,

trx.global_attribute3,

trx.global_attribute4,

trx.global_attribute5,

trx.global_attribute6,

trx.global_attribute7,

trx.global_attribute8,

trx.global_attribute9,

trx.global_attribute10,

trx.global_attribute11,

trx.global_attribute12,

trx.global_attribute13,

trx.global_attribute14,

trx.global_attribute15,

trx.global_attribute16,

trx.global_attribute17,

trx.global_attribute18,

trx.global_attribute19,

trx.global_attribute20,

trx.global_attribute_category,

trx.wh_update_date,

hz_format_pub.format_address(a_bill_loc.location_id) formatted_bill_to_address,

hz_format_pub.format_address(a_ship_loc.location_id) formatted_ship_to_address,

hz_format_pub.format_address(a_remit_loc.location_id) formatted_remit_to_address,

trx.bill_template_id

FROM ar_lookups o_type,

ar_lookups l_type,

ar_lookups a_type,

ra_batch_sources_all bs,

ar_system_parameters sp,

fnd_territories_vl ft,

fnd_territories_vl ft2,

fnd_territories_vl dft,

hz_cust_profile_amts_f amount,

ar_invoice_count_terms_v t_count,

ar_adjustments com_adj,

ar_payment_schedules ps,

ra_terms_lines tl,

ra_terms t,

hz_cust_acct_sites_all a_bill,

hz_party_sites a_bill_ps,

hz_locations a_bill_loc,

hz_cust_acct_sites_all a_ship,

hz_party_sites a_ship_ps,

hz_locations a_ship_loc,

hz_cust_site_uses_all u_bill,

hz_cust_site_uses_all u_ship,

hz_cust_account_roles c_bill,

hz_parties c_bill_party,

hz_relationships c_bill_rel,

hz_org_contacts c_bill_org_cont,

hz_cust_account_roles c_ship,

hz_parties c_ship_party,

hz_relationships c_ship_rel,

hz_org_contacts c_ship_org_cont,

hz_cust_accounts b_bill,

hz_parties b_bill_party,

hz_cust_accounts b_ship,

hz_parties b_ship_party,

ar_remit_to_locs_all locs,

hz_locations a_remit_loc,

ra_salesreps sales,

ra_cust_trx_types_all types,

ra_customer_trx prevtrx,

ra_customer_trx trx

WHERE trx.complete_flag = 'Y'

AND trx.cust_trx_type_seq_id = types.cust_trx_type_seq_id

AND trx.term_id = tl.term_id(+)

AND trx.term_id = t.term_id(+)

AND trx.customer_trx_id + 0 = ps.customer_trx_id(+)

AND trx.primary_resource_salesrep_id = sales.resource_salesrep_id(+)

AND trx.bill_to_contact_id = c_bill.cust_account_role_id(+)

AND c_bill.relationship_id = c_bill_rel.relationship_id (+)

AND c_bill_rel.subject_table_name(+) = 'HZ_PARTIES'

AND c_bill_rel.object_table_name(+) = 'HZ_PARTIES'

AND c_bill_rel.directional_flag(+) = 'F'

AND c_bill.role_type(+) = 'CONTACT'

AND c_bill_org_cont.party_relationship_id(+) = c_bill_rel.relationship_id

AND c_bill_rel.subject_id = c_bill_party.party_id(+)

AND trx.ship_to_contact_id = c_ship.cust_account_role_id(+)

AND c_ship.relationship_id = c_ship_rel.relationship_id (+)

AND c_ship_rel.subject_table_name(+) = 'HZ_PARTIES'

AND c_ship_rel.object_table_name(+) = 'HZ_PARTIES'

AND c_ship_rel.directional_flag(+) = 'F'

AND c_ship.role_type(+) = 'CONTACT'

AND c_ship_org_cont.party_relationship_id(+) = c_ship_rel.relationship_id

AND c_ship_rel.subject_id = c_ship_party.party_id(+)

AND trx.bill_to_site_use_id = u_bill.site_use_id

AND trx.ship_to_site_use_id = u_ship.site_use_id(+)

AND trx.bill_to_customer_id + 0 = b_bill.cust_account_id

AND b_bill.party_id = b_bill_party.party_id

AND trx.ship_to_customer_id = b_ship.cust_account_id(+)

AND b_ship.party_id = b_ship_party.party_id(+)

AND trx.batch_source_seq_id = bs.batch_source_seq_id(+)

AND trx.previous_customer_trx_id = prevtrx.customer_trx_id(+)

AND trx.remit_to_address_seq_id = locs.address_loc_seq_id

AND a_remit_loc.location_id = locs.location_id

AND trx.bill_to_customer_id = amount.cust_account_id(+)

AND trx.bill_to_site_use_id = amount.site_use_id(+)

AND trx.invoice_currency_code = amount.currency_code(+)

AND ps.payment_schedule_id + decode(ps.class, 'INV', 0, '') = com_adj.payment_schedule_id(+)

AND com_adj.subsequent_trx_id IS NULL

AND 'C' = com_adj.adjustment_type(+)

AND types.default_printing_option = 'PRI'

AND l_type.lookup_type = 'INV/CM/ADJ'

AND l_type.lookup_code = decode(types.type, 'DEP', 'INV', types.type)

AND l_type.lookup_code <> 'ADJ'

AND nvl(ps.terms_sequence_number, nvl(tl.sequence_num, 0)) = nvl(tl.sequence_num, nvl(ps.terms_sequence_number, 0))

AND u_bill.cust_acct_site_id = a_bill.cust_acct_site_id(+)

AND a_bill.party_site_id = a_bill_ps.party_site_id(+)

AND a_bill_loc.location_id(+) = a_bill_ps.location_id

AND u_ship.cust_acct_site_id = a_ship.cust_acct_site_id(+)

AND a_ship.party_site_id = a_ship_ps.party_site_id(+)

AND a_ship_loc.location_id(+) = a_ship_ps.location_id

AND o_type.lookup_type = 'YES/NO'

AND o_type.lookup_code = decode(nvl(ps.amount_due_remaining, 0), 0, 'N', 'Y')

AND a_type.lookup_type = 'ADDRESS_LABEL'

AND a_type.lookup_code = 'ATTN_DEFAULT_MSG'

AND a_bill_loc.country = ft.territory_code(+)

AND a_ship_loc.country = ft2.territory_code(+)

AND nvl(sp.default_country, 'US') = dft.territory_code

AND nvl(trx.term_id, -1) = t_count.term_id