VIEW: APPS.AR_INVOICE_ADJ_V

Source

View Text - Preformatted

SELECT trx.trx_number trx_number, trx.trx_date trx_date, trx.customer_trx_id customer_trx_id, o_type.meaning open_trx, trx.cust_trx_type_id cust_trx_type_id, b_bill.cust_account_id customer_id, types.name trx_type_name, b_bill.customer_class_code customer_class_code, trx.batch_id batch_id, nvl(tl.sequence_num,1) term_sequence_number, adj.adjustment_id adjustment_id, adj.adjustment_number adjustment_number, adj.apply_date apply_date, trx.ship_to_customer_id ship_customer_id, trx.ship_to_contact_id ship_contact_id, trx.remit_to_address_id remit_to_address_id, l_type.meaning class_name, types.accounting_affect_flag open_receivable_flag, trx.internal_notes internal_notes, trx.comments trx_comments, 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, b_bill.account_number bill_to_customer_number, 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, 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, /* Get the Country description . It implements the get_country_description local function in RAXINV.rdf. */ decode( a_bill_loc.country, 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, adj.line_adjusted line_amount_adjusted, adj.tax_adjusted tax_amount_adjusted, adj.freight_adjusted freight_amount_adjusted, adj.amount total_amount_adjusted, 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, /* Get the Country description . It implements the get_country_description local function in RAXINV.rdf. */ 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(ft.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, 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 , /* Get the Country description . It implements the get_country_description local function in RAXINV.rdf. */ 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, adj.last_updated_by, adj.last_update_date, adj.last_update_login, adj.created_by, adj.creation_date, adj.gl_date, adj.set_of_books_id, adj.code_combination_id, adj.type, adj.adjustment_type, adj.status, adj.receivables_charges_adjusted, adj.associated_cash_receipt_id, adj.chargeback_customer_trx_id, adj.customer_trx_line_id, adj.subsequent_trx_id, adj.payment_schedule_id, adj.receivables_trx_id, adj.distribution_set_id, adj.gl_posted_date, adj.comments, adj.automatically_generated, adj.created_from, adj.reason_code, adj.postable, adj.approved_by, adj.attribute_category, adj.attribute1, adj.attribute2, adj.attribute3, adj.attribute4, adj.attribute5, adj.attribute6, adj.attribute7, adj.attribute8, adj.attribute9, adj.attribute10, adj.attribute11, adj.attribute12, adj.attribute13, adj.attribute14, adj.attribute15, adj.posting_control_id, adj.acctd_amount, adj.program_application_id, adj.program_id, adj.program_update_date, adj.request_id, adj.org_id, adj.ussgl_transaction_code, adj.ussgl_transaction_code_context, adj.doc_sequence_value, adj.doc_sequence_id, adj.associated_application_id, adj.cons_inv_id FROM ar_lookups o_type, ar_lookups l_type, ar_lookups a_type, ar_adjustments com_adj, ar_system_parameters sp, fnd_territories_vl ft, fnd_territories_vl dft, ar_payment_schedules ps, ar_adjustments adj, 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 trx WHERE trx.cust_trx_type_id = types.cust_trx_type_id AND trx.complete_flag = 'Y' AND trx.term_id = tl.term_id(+) AND trx.term_id = t.term_id(+) AND trx.customer_trx_id = ps.customer_trx_id(+) AND trx.customer_trx_id = adj.customer_trx_id AND trx.primary_salesrep_id = sales.salesrep_id(+) AND trx.ship_via = org.freight_code(+) AND trx.org_id = org.organization_id(+) AND trx.ship_to_customer_id = b_ship.cust_account_id(+) AND b_ship.party_id = b_ship_party.party_id(+) AND trx.bill_to_customer_id = b_bill.cust_account_id AND b_bill.party_id = b_bill_party.party_id AND trx.ship_to_site_use_id = u_ship.site_use_id(+) AND trx.bill_to_site_use_id = u_bill.site_use_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_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.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 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 = 'ADJ' AND NVL(ps.terms_sequence_number, NVL(tl.sequence_num,0)) = NVL(tl.sequence_num, NVL(ps.terms_sequence_number,0)) AND DECODE(ps.payment_schedule_id, '',0,NVL(t.printing_lead_days,0)) = 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 adj.status = 'A' AND a_type.lookup_type = 'ADDRESS_LABEL' AND a_type.lookup_code = 'ATTN_DEFAULT_MSG' AND a_bill_loc.country = ft.territory_code(+) AND nvl(sp.default_country,'US') = dft.territory_code UNION SELECT trx.trx_number trx_number, trx.trx_date trx_date, trx.customer_trx_id customer_trx_id, o_type.meaning open_trx, trx.cust_trx_type_id cust_trx_type_id, b_bill.cust_account_id customer_id, types.name trx_type_name, b_bill.customer_class_code customer_class_code, trx.batch_id batch_id, ps.terms_sequence_number term_sequence_number, adj.adjustment_id adjustment_id, adj.adjustment_number adjustment_number, adj.apply_date apply_date, trx.ship_to_customer_id ship_customer_id, trx.ship_to_contact_id ship_contact_id, trx.remit_to_address_id remit_to_address_id, l_type.meaning class_name, types.accounting_affect_flag open_receivable_flag, trx.internal_notes internal_notes, trx.comments trx_comments, 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, b_bill.account_number bill_to_customer_number, 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, 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, /* Get the Country description . It implements the get_country_description local function in RAXINV.rdf. */ 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, adj.line_adjusted line_amount_adjusted, adj.tax_adjusted tax_amount_adjusted, adj.freight_adjusted freight_amount_adjusted, adj.amount total_amount_adjusted, 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, /* Get the Country description . It implements the get_country_description local function in RAXINV.rdf. */ 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(ft.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))) trx_tax_reference, 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 , /* Get the Country description . It implements the get_country_description local function in RAXINV.rdf. */ 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, adj.last_updated_by, adj.last_update_date, adj.last_update_login, adj.created_by, adj.creation_date, adj.gl_date, adj.set_of_books_id, adj.code_combination_id, adj.type, adj.adjustment_type, adj.status, adj.receivables_charges_adjusted, adj.associated_cash_receipt_id, adj.chargeback_customer_trx_id, adj.customer_trx_line_id, adj.subsequent_trx_id, adj.payment_schedule_id, adj.receivables_trx_id, adj.distribution_set_id, adj.gl_posted_date, adj.comments, adj.automatically_generated, adj.created_from, adj.reason_code, adj.postable, adj.approved_by, adj.attribute_category, adj.attribute1, adj.attribute2, adj.attribute3, adj.attribute4, adj.attribute5, adj.attribute6, adj.attribute7, adj.attribute8, adj.attribute9, adj.attribute10, adj.attribute11, adj.attribute12, adj.attribute13, adj.attribute14, adj.attribute15, adj.posting_control_id, adj.acctd_amount, adj.program_application_id, adj.program_id, adj.program_update_date, adj.request_id, adj.org_id, adj.ussgl_transaction_code, adj.ussgl_transaction_code_context, adj.doc_sequence_value, adj.doc_sequence_id, adj.associated_application_id, adj.cons_inv_id FROM ar_lookups o_type, ar_lookups l_type, ar_lookups a_type, ar_adjustments com_adj, ar_system_parameters sp, fnd_territories_vl ft, fnd_territories_vl dft, ar_payment_schedules ps, ar_adjustments adj, 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 trx WHERE trx.customer_trx_id = ps.customer_trx_id AND trx.complete_flag = 'Y' AND trx.cust_trx_type_id = types.cust_trx_type_id AND trx.customer_trx_id = adj.customer_trx_id AND trx.primary_salesrep_id = sales.salesrep_id(+) AND trx.ship_via = org.freight_code(+) AND trx.org_id = org.organization_id(+) AND trx.ship_to_customer_id = b_ship.cust_account_id(+) ANd b_ship.party_id = b_ship_party.party_id(+) AND trx.bill_to_customer_id = b_bill.cust_account_id AND b_bill.party_id = b_bill_party.party_id AND trx.ship_to_site_use_id = u_ship.site_use_id(+) AND trx.bill_to_site_use_id = u_bill.site_use_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_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.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.term_id = t.term_id AND tl.term_id = t.term_id 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 = 'ADJ' AND ps.terms_sequence_number = tl.sequence_num AND t.printing_lead_days > 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 NVL(tl.sequence_num,1) = 1 AND adj.status = 'A' AND a_type.lookup_type = 'ADDRESS_LABEL' AND a_type.lookup_code = 'ATTN_DEFAULT_MSG' AND a_bill_loc.country = ft.territory_code(+) AND nvl(sp.default_country,'US') = dft.territory_code

View Text - HTML Formatted

SELECT TRX.TRX_NUMBER TRX_NUMBER
, TRX.TRX_DATE TRX_DATE
, TRX.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, O_TYPE.MEANING OPEN_TRX
, TRX.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, B_BILL.CUST_ACCOUNT_ID CUSTOMER_ID
, TYPES.NAME TRX_TYPE_NAME
, B_BILL.CUSTOMER_CLASS_CODE CUSTOMER_CLASS_CODE
, TRX.BATCH_ID BATCH_ID
, NVL(TL.SEQUENCE_NUM
, 1) TERM_SEQUENCE_NUMBER
, ADJ.ADJUSTMENT_ID ADJUSTMENT_ID
, ADJ.ADJUSTMENT_NUMBER ADJUSTMENT_NUMBER
, ADJ.APPLY_DATE APPLY_DATE
, TRX.SHIP_TO_CUSTOMER_ID SHIP_CUSTOMER_ID
, TRX.SHIP_TO_CONTACT_ID SHIP_CONTACT_ID
, TRX.REMIT_TO_ADDRESS_ID REMIT_TO_ADDRESS_ID
, L_TYPE.MEANING CLASS_NAME
, TYPES.ACCOUNTING_AFFECT_FLAG OPEN_RECEIVABLE_FLAG
, TRX.INTERNAL_NOTES INTERNAL_NOTES
, TRX.COMMENTS TRX_COMMENTS
, 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
, B_BILL.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUMBER
, 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
, 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
, /* GET THE COUNTRY DESCRIPTION . IT IMPLEMENTS THE GET_COUNTRY_DESCRIPTION LOCAL FUNCTION IN RAXINV.RDF. */ DECODE( A_BILL_LOC.COUNTRY
, 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
, ADJ.LINE_ADJUSTED LINE_AMOUNT_ADJUSTED
, ADJ.TAX_ADJUSTED TAX_AMOUNT_ADJUSTED
, ADJ.FREIGHT_ADJUSTED FREIGHT_AMOUNT_ADJUSTED
, ADJ.AMOUNT TOTAL_AMOUNT_ADJUSTED
, 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
, /* GET THE COUNTRY DESCRIPTION . IT IMPLEMENTS THE GET_COUNTRY_DESCRIPTION LOCAL FUNCTION IN RAXINV.RDF. */ 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(FT.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
, 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
, /* GET THE COUNTRY DESCRIPTION . IT IMPLEMENTS THE GET_COUNTRY_DESCRIPTION LOCAL FUNCTION IN RAXINV.RDF. */ 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
, ADJ.LAST_UPDATED_BY
, ADJ.LAST_UPDATE_DATE
, ADJ.LAST_UPDATE_LOGIN
, ADJ.CREATED_BY
, ADJ.CREATION_DATE
, ADJ.GL_DATE
, ADJ.SET_OF_BOOKS_ID
, ADJ.CODE_COMBINATION_ID
, ADJ.TYPE
, ADJ.ADJUSTMENT_TYPE
, ADJ.STATUS
, ADJ.RECEIVABLES_CHARGES_ADJUSTED
, ADJ.ASSOCIATED_CASH_RECEIPT_ID
, ADJ.CHARGEBACK_CUSTOMER_TRX_ID
, ADJ.CUSTOMER_TRX_LINE_ID
, ADJ.SUBSEQUENT_TRX_ID
, ADJ.PAYMENT_SCHEDULE_ID
, ADJ.RECEIVABLES_TRX_ID
, ADJ.DISTRIBUTION_SET_ID
, ADJ.GL_POSTED_DATE
, ADJ.COMMENTS
, ADJ.AUTOMATICALLY_GENERATED
, ADJ.CREATED_FROM
, ADJ.REASON_CODE
, ADJ.POSTABLE
, ADJ.APPROVED_BY
, ADJ.ATTRIBUTE_CATEGORY
, ADJ.ATTRIBUTE1
, ADJ.ATTRIBUTE2
, ADJ.ATTRIBUTE3
, ADJ.ATTRIBUTE4
, ADJ.ATTRIBUTE5
, ADJ.ATTRIBUTE6
, ADJ.ATTRIBUTE7
, ADJ.ATTRIBUTE8
, ADJ.ATTRIBUTE9
, ADJ.ATTRIBUTE10
, ADJ.ATTRIBUTE11
, ADJ.ATTRIBUTE12
, ADJ.ATTRIBUTE13
, ADJ.ATTRIBUTE14
, ADJ.ATTRIBUTE15
, ADJ.POSTING_CONTROL_ID
, ADJ.ACCTD_AMOUNT
, ADJ.PROGRAM_APPLICATION_ID
, ADJ.PROGRAM_ID
, ADJ.PROGRAM_UPDATE_DATE
, ADJ.REQUEST_ID
, ADJ.ORG_ID
, ADJ.USSGL_TRANSACTION_CODE
, ADJ.USSGL_TRANSACTION_CODE_CONTEXT
, ADJ.DOC_SEQUENCE_VALUE
, ADJ.DOC_SEQUENCE_ID
, ADJ.ASSOCIATED_APPLICATION_ID
, ADJ.CONS_INV_ID
FROM AR_LOOKUPS O_TYPE
, AR_LOOKUPS L_TYPE
, AR_LOOKUPS A_TYPE
, AR_ADJUSTMENTS COM_ADJ
, AR_SYSTEM_PARAMETERS SP
, FND_TERRITORIES_VL FT
, FND_TERRITORIES_VL DFT
, AR_PAYMENT_SCHEDULES PS
, AR_ADJUSTMENTS ADJ
, 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 TRX
WHERE TRX.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID
AND TRX.COMPLETE_FLAG = 'Y'
AND TRX.TERM_ID = TL.TERM_ID(+)
AND TRX.TERM_ID = T.TERM_ID(+)
AND TRX.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID(+)
AND TRX.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID
AND TRX.PRIMARY_SALESREP_ID = SALES.SALESREP_ID(+)
AND TRX.SHIP_VIA = ORG.FREIGHT_CODE(+)
AND TRX.ORG_ID = ORG.ORGANIZATION_ID(+)
AND TRX.SHIP_TO_CUSTOMER_ID = B_SHIP.CUST_ACCOUNT_ID(+)
AND B_SHIP.PARTY_ID = B_SHIP_PARTY.PARTY_ID(+)
AND TRX.BILL_TO_CUSTOMER_ID = B_BILL.CUST_ACCOUNT_ID
AND B_BILL.PARTY_ID = B_BILL_PARTY.PARTY_ID
AND TRX.SHIP_TO_SITE_USE_ID = U_SHIP.SITE_USE_ID(+)
AND TRX.BILL_TO_SITE_USE_ID = U_BILL.SITE_USE_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_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.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 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 = 'ADJ'
AND NVL(PS.TERMS_SEQUENCE_NUMBER
, NVL(TL.SEQUENCE_NUM
, 0)) = NVL(TL.SEQUENCE_NUM
, NVL(PS.TERMS_SEQUENCE_NUMBER
, 0))
AND DECODE(PS.PAYMENT_SCHEDULE_ID
, ''
, 0
, NVL(T.PRINTING_LEAD_DAYS
, 0)) = 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 ADJ.STATUS = 'A'
AND A_TYPE.LOOKUP_TYPE = 'ADDRESS_LABEL'
AND A_TYPE.LOOKUP_CODE = 'ATTN_DEFAULT_MSG'
AND A_BILL_LOC.COUNTRY = FT.TERRITORY_CODE(+)
AND NVL(SP.DEFAULT_COUNTRY
, 'US') = DFT.TERRITORY_CODE UNION SELECT TRX.TRX_NUMBER TRX_NUMBER
, TRX.TRX_DATE TRX_DATE
, TRX.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, O_TYPE.MEANING OPEN_TRX
, TRX.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, B_BILL.CUST_ACCOUNT_ID CUSTOMER_ID
, TYPES.NAME TRX_TYPE_NAME
, B_BILL.CUSTOMER_CLASS_CODE CUSTOMER_CLASS_CODE
, TRX.BATCH_ID BATCH_ID
, PS.TERMS_SEQUENCE_NUMBER TERM_SEQUENCE_NUMBER
, ADJ.ADJUSTMENT_ID ADJUSTMENT_ID
, ADJ.ADJUSTMENT_NUMBER ADJUSTMENT_NUMBER
, ADJ.APPLY_DATE APPLY_DATE
, TRX.SHIP_TO_CUSTOMER_ID SHIP_CUSTOMER_ID
, TRX.SHIP_TO_CONTACT_ID SHIP_CONTACT_ID
, TRX.REMIT_TO_ADDRESS_ID REMIT_TO_ADDRESS_ID
, L_TYPE.MEANING CLASS_NAME
, TYPES.ACCOUNTING_AFFECT_FLAG OPEN_RECEIVABLE_FLAG
, TRX.INTERNAL_NOTES INTERNAL_NOTES
, TRX.COMMENTS TRX_COMMENTS
, 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
, B_BILL.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUMBER
, 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
, 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
, /* GET THE COUNTRY DESCRIPTION . IT IMPLEMENTS THE GET_COUNTRY_DESCRIPTION LOCAL FUNCTION IN RAXINV.RDF. */ 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
, ADJ.LINE_ADJUSTED LINE_AMOUNT_ADJUSTED
, ADJ.TAX_ADJUSTED TAX_AMOUNT_ADJUSTED
, ADJ.FREIGHT_ADJUSTED FREIGHT_AMOUNT_ADJUSTED
, ADJ.AMOUNT TOTAL_AMOUNT_ADJUSTED
, 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
, /* GET THE COUNTRY DESCRIPTION . IT IMPLEMENTS THE GET_COUNTRY_DESCRIPTION LOCAL FUNCTION IN RAXINV.RDF. */ 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(FT.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))) TRX_TAX_REFERENCE
, 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
, /* GET THE COUNTRY DESCRIPTION . IT IMPLEMENTS THE GET_COUNTRY_DESCRIPTION LOCAL FUNCTION IN RAXINV.RDF. */ 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
, ADJ.LAST_UPDATED_BY
, ADJ.LAST_UPDATE_DATE
, ADJ.LAST_UPDATE_LOGIN
, ADJ.CREATED_BY
, ADJ.CREATION_DATE
, ADJ.GL_DATE
, ADJ.SET_OF_BOOKS_ID
, ADJ.CODE_COMBINATION_ID
, ADJ.TYPE
, ADJ.ADJUSTMENT_TYPE
, ADJ.STATUS
, ADJ.RECEIVABLES_CHARGES_ADJUSTED
, ADJ.ASSOCIATED_CASH_RECEIPT_ID
, ADJ.CHARGEBACK_CUSTOMER_TRX_ID
, ADJ.CUSTOMER_TRX_LINE_ID
, ADJ.SUBSEQUENT_TRX_ID
, ADJ.PAYMENT_SCHEDULE_ID
, ADJ.RECEIVABLES_TRX_ID
, ADJ.DISTRIBUTION_SET_ID
, ADJ.GL_POSTED_DATE
, ADJ.COMMENTS
, ADJ.AUTOMATICALLY_GENERATED
, ADJ.CREATED_FROM
, ADJ.REASON_CODE
, ADJ.POSTABLE
, ADJ.APPROVED_BY
, ADJ.ATTRIBUTE_CATEGORY
, ADJ.ATTRIBUTE1
, ADJ.ATTRIBUTE2
, ADJ.ATTRIBUTE3
, ADJ.ATTRIBUTE4
, ADJ.ATTRIBUTE5
, ADJ.ATTRIBUTE6
, ADJ.ATTRIBUTE7
, ADJ.ATTRIBUTE8
, ADJ.ATTRIBUTE9
, ADJ.ATTRIBUTE10
, ADJ.ATTRIBUTE11
, ADJ.ATTRIBUTE12
, ADJ.ATTRIBUTE13
, ADJ.ATTRIBUTE14
, ADJ.ATTRIBUTE15
, ADJ.POSTING_CONTROL_ID
, ADJ.ACCTD_AMOUNT
, ADJ.PROGRAM_APPLICATION_ID
, ADJ.PROGRAM_ID
, ADJ.PROGRAM_UPDATE_DATE
, ADJ.REQUEST_ID
, ADJ.ORG_ID
, ADJ.USSGL_TRANSACTION_CODE
, ADJ.USSGL_TRANSACTION_CODE_CONTEXT
, ADJ.DOC_SEQUENCE_VALUE
, ADJ.DOC_SEQUENCE_ID
, ADJ.ASSOCIATED_APPLICATION_ID
, ADJ.CONS_INV_ID
FROM AR_LOOKUPS O_TYPE
, AR_LOOKUPS L_TYPE
, AR_LOOKUPS A_TYPE
, AR_ADJUSTMENTS COM_ADJ
, AR_SYSTEM_PARAMETERS SP
, FND_TERRITORIES_VL FT
, FND_TERRITORIES_VL DFT
, AR_PAYMENT_SCHEDULES PS
, AR_ADJUSTMENTS ADJ
, 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 TRX
WHERE TRX.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND TRX.COMPLETE_FLAG = 'Y'
AND TRX.CUST_TRX_TYPE_ID = TYPES.CUST_TRX_TYPE_ID
AND TRX.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_ID
AND TRX.PRIMARY_SALESREP_ID = SALES.SALESREP_ID(+)
AND TRX.SHIP_VIA = ORG.FREIGHT_CODE(+)
AND TRX.ORG_ID = ORG.ORGANIZATION_ID(+)
AND TRX.SHIP_TO_CUSTOMER_ID = B_SHIP.CUST_ACCOUNT_ID(+)
AND B_SHIP.PARTY_ID = B_SHIP_PARTY.PARTY_ID(+)
AND TRX.BILL_TO_CUSTOMER_ID = B_BILL.CUST_ACCOUNT_ID
AND B_BILL.PARTY_ID = B_BILL_PARTY.PARTY_ID
AND TRX.SHIP_TO_SITE_USE_ID = U_SHIP.SITE_USE_ID(+)
AND TRX.BILL_TO_SITE_USE_ID = U_BILL.SITE_USE_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_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.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.TERM_ID = T.TERM_ID
AND TL.TERM_ID = T.TERM_ID
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 = 'ADJ'
AND PS.TERMS_SEQUENCE_NUMBER = TL.SEQUENCE_NUM
AND T.PRINTING_LEAD_DAYS > 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 NVL(TL.SEQUENCE_NUM
, 1) = 1
AND ADJ.STATUS = 'A'
AND A_TYPE.LOOKUP_TYPE = 'ADDRESS_LABEL'
AND A_TYPE.LOOKUP_CODE = 'ATTN_DEFAULT_MSG'
AND A_BILL_LOC.COUNTRY = FT.TERRITORY_CODE(+)
AND NVL(SP.DEFAULT_COUNTRY
, 'US') = DFT.TERRITORY_CODE