VIEW: APPS.AR_INVOICE_HEADER_V

Source

View Text - Preformatted

SELECT o_type.meaning open_trx, trx.cust_trx_type_id cust_trx_type_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_salesrep_id primary_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, u_bill.tax_reference bill_to_site_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, c_bill_org_cont.mail_stop 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.salesrep_id, '', '', '', '', -1, '', -2, '', -3, '', sales.name) primary_salesrep_name, org.description ship_via_description, org.organization_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, u_ship.tax_reference ship_to_site_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, c_ship_org_cont.mail_stop ship_to_mail_stop, 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_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_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 bs, ar_system_parameters sp, fnd_territories_vl ft, fnd_territories_vl ft2, fnd_territories_vl dft, hz_cust_profile_amts 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 a_bill, hz_party_sites a_bill_ps, hz_locations a_bill_loc, hz_cust_acct_sites a_ship, hz_party_sites a_ship_ps, hz_locations a_ship_loc, hz_cust_site_uses u_bill, hz_cust_site_uses 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, hz_cust_acct_sites a_remit, hz_party_sites a_remit_ps, hz_locations a_remit_loc, ra_salesreps sales, org_freight org, ra_cust_trx_types types, ra_customer_trx prevtrx, ra_customer_trx trx WHERE trx.complete_flag = 'Y' AND trx.cust_trx_type_id = types.cust_trx_type_id AND trx.ship_via = org.freight_code(+) AND trx.org_id = org.organization_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_salesrep_id = sales.salesrep_id(+) AND trx.bill_to_contact_id = c_bill.cust_account_role_id(+) AND c_bill.party_id = c_bill_rel.party_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.party_id = c_ship_rel.party_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_id = bs.batch_source_id(+) AND trx.previous_customer_trx_id = prevtrx.customer_trx_id(+) AND trx.remit_to_address_id = a_remit.cust_acct_site_id(+) AND a_remit.party_site_id = a_remit_ps.party_site_id(+) AND a_remit_loc.location_id(+) = a_remit_ps.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

View Text - HTML Formatted

SELECT O_TYPE.MEANING OPEN_TRX
, TRX.CUST_TRX_TYPE_ID CUST_TRX_TYPE_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_SALESREP_ID PRIMARY_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
, U_BILL.TAX_REFERENCE BILL_TO_SITE_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
, C_BILL_ORG_CONT.MAIL_STOP 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.SALESREP_ID
, ''
, ''
, ''
, ''
, -1
, ''
, -2
, ''
, -3
, ''
, SALES.NAME) PRIMARY_SALESREP_NAME
, ORG.DESCRIPTION SHIP_VIA_DESCRIPTION
, ORG.ORGANIZATION_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
, U_SHIP.TAX_REFERENCE SHIP_TO_SITE_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
, C_SHIP_ORG_CONT.MAIL_STOP SHIP_TO_MAIL_STOP
, 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_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_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 BS
, AR_SYSTEM_PARAMETERS SP
, FND_TERRITORIES_VL FT
, FND_TERRITORIES_VL FT2
, FND_TERRITORIES_VL DFT
, HZ_CUST_PROFILE_AMTS 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 A_BILL
, HZ_PARTY_SITES A_BILL_PS
, HZ_LOCATIONS A_BILL_LOC
, HZ_CUST_ACCT_SITES A_SHIP
, HZ_PARTY_SITES A_SHIP_PS
, HZ_LOCATIONS A_SHIP_LOC
, HZ_CUST_SITE_USES U_BILL
, HZ_CUST_SITE_USES 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
, HZ_CUST_ACCT_SITES A_REMIT
, HZ_PARTY_SITES A_REMIT_PS
, HZ_LOCATIONS A_REMIT_LOC
, RA_SALESREPS SALES
, ORG_FREIGHT ORG
, RA_CUST_TRX_TYPES TYPES
, RA_CUSTOMER_TRX PREVTRX
, RA_CUSTOMER_TRX TRX
WHERE TRX.COMPLETE_FLAG = 'Y'
AND TRX.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID
AND TRX.SHIP_VIA = ORG.FREIGHT_CODE(+)
AND TRX.ORG_ID = ORG.ORGANIZATION_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_SALESREP_ID = SALES.SALESREP_ID(+)
AND TRX.BILL_TO_CONTACT_ID = C_BILL.CUST_ACCOUNT_ROLE_ID(+)
AND C_BILL.PARTY_ID = C_BILL_REL.PARTY_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.PARTY_ID = C_SHIP_REL.PARTY_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_ID = BS.BATCH_SOURCE_ID(+)
AND TRX.PREVIOUS_CUSTOMER_TRX_ID = PREVTRX.CUSTOMER_TRX_ID(+)
AND TRX.REMIT_TO_ADDRESS_ID = A_REMIT.CUST_ACCT_SITE_ID(+)
AND A_REMIT.PARTY_SITE_ID = A_REMIT_PS.PARTY_SITE_ID(+)
AND A_REMIT_LOC.LOCATION_ID(+) = A_REMIT_PS.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