OSS_TXN_INVOICES

Details

Schema: FUSION
Object owner: OSS
Object type: VIEW

Columns

Name
CUSTOMER_TRX_ID TRANSACTION_NUMBER TRANSACTION_DATE TRANSACTION_CLASS TRANSACTION_CLASS_MEANING COMPLETE_FLAG COMPLETE_FLAG_MEANING TRANSACTION_TYPE_ID TRANSACTION_TYPE_CODE TRANSACTION_TYPE_NAME BILL_TO_CONTACT_ID BILL_TO_CONTACT BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER BILL_TO_SITE_USE_ID BILL_TO_SITE_NAME SHIP_TO_PARTY_ID SHIP_TO_CUSTOMER SHIP_TO_CONTACT_ID SHIP_TO_PARTY_CONTACT SHIP_TO_PARTY_SITE_USE_ID SHIP_TO_PARTY_SITE_NAME TRANSACTION_PAYMENT_TERM_ID TRANSACTION_PAYMENT_TERM_NAME TRANSACTION_TERM_DUE_DATE ORIGINAL_TRANSACTION_NUMBER PREVIOUS_CUSTOMER_TRX_ID INVOICE_TOTAL_AMOUNT INVOICE_CURRENCY_CODE

Name

CUSTOMER_TRX_ID

TRANSACTION_NUMBER

TRANSACTION_DATE

TRANSACTION_CLASS

TRANSACTION_CLASS_MEANING

COMPLETE_FLAG

COMPLETE_FLAG_MEANING

TRANSACTION_TYPE_ID

TRANSACTION_TYPE_CODE

TRANSACTION_TYPE_NAME

BILL_TO_CONTACT_ID

BILL_TO_CONTACT

BILL_TO_CUSTOMER_ID

BILL_TO_CUSTOMER

BILL_TO_SITE_USE_ID

BILL_TO_SITE_NAME

SHIP_TO_PARTY_ID

SHIP_TO_CUSTOMER

SHIP_TO_CONTACT_ID

SHIP_TO_PARTY_CONTACT

SHIP_TO_PARTY_SITE_USE_ID

SHIP_TO_PARTY_SITE_NAME

TRANSACTION_PAYMENT_TERM_ID

TRANSACTION_PAYMENT_TERM_NAME

TRANSACTION_TERM_DUE_DATE

ORIGINAL_TRANSACTION_NUMBER

PREVIOUS_CUSTOMER_TRX_ID

INVOICE_TOTAL_AMOUNT

INVOICE_CURRENCY_CODE

Query

SQL_Statement
SELECT txn.customer_trx_id, txn.trx_number AS transaction_number, txn.trx_date AS transaction_date, txn.trx_class AS transaction_class, ( SELECT ar_lookups.meaning FROM ar_lookups WHERE ar_lookups.lookup_type = 'INV/CM' AND ar_lookups.lookup_code = txn.trx_class AND ROWNUM = 1 ) AS transaction_class_meaning, txn.complete_flag, ( SELECT meaning FROM ar_lookups WHERE lookup_type = 'YES_NO_SKIP' AND lookup_code = txn.complete_flag ) AS complete_flag_meaning, txn.cust_trx_type_seq_id AS transaction_type_id, typ.type AS transaction_type_code, typ.name AS transaction_type_name, txn.bill_to_contact_id, pbilltocontact.party_name AS bill_to_contact, txn.bill_to_customer_id, pbilltocustomer.party_name AS bill_to_customer, txn.bill_to_site_use_id, su_bill.location AS bill_to_site_name, txn.ship_to_party_id, shiptocust.party_name AS ship_to_customer, txn.ship_to_contact_id AS ship_to_contact_id, party.party_name AS ship_to_party_contact, txn.ship_to_party_site_use_id, su_ship.location AS ship_to_party_site_name, txn.term_id AS transaction_payment_term_id, term.name AS transaction_payment_term_name, txn.term_due_date AS transaction_term_due_date, txn.old_trx_number AS original_transaction_number, txn.previous_customer_trx_id, ( SELECT nvl(SUM(lines.extended_amount), 0) FROM ra_customer_trx_lines_all lines WHERE txn.customer_trx_id = lines.customer_trx_id ) AS invoice_total_amount, invoice_currency_code FROM ra_customer_trx_all txn, ra_cust_trx_types_all typ, ra_terms_tl term, hz_parties shiptocust, hz_parties pbilltocustomer, hz_cust_accounts ca, hz_parties pbilltocontact, hz_cust_account_roles car, hz_cust_site_uses_all su_bill, hz_party_sites ps_bill, hz_locations hl_bill, hz_cust_site_uses_all su_ship, hz_party_sites ps_ship, hz_locations hl_ship, hz_cust_account_roles acct_role, hz_parties party WHERE txn.bill_to_customer_id = ca.cust_account_id (+) AND pbilltocustomer.party_id (+) = ca.party_id AND txn.bill_to_contact_id = car.cust_account_role_id (+) AND pbilltocontact.party_id (+) = car.contact_person_id AND txn.bill_to_site_use_id = su_bill.site_use_id (+) AND su_bill.cust_acct_site_id = ps_bill.party_site_id (+) AND ps_bill.location_id = hl_bill.location_id (+) AND txn.ship_to_contact_id = acct_role.cust_account_role_id (+) AND acct_role.contact_person_id = party.party_id (+) AND txn.ship_to_party_site_use_id = su_ship.site_use_id (+) AND su_ship.cust_acct_site_id = ps_ship.party_site_id (+) AND ps_ship.location_id = hl_ship.location_id (+) AND txn.cust_trx_type_seq_id = typ.cust_trx_type_seq_id AND term.term_id (+) = txn.term_id AND txn.ship_to_party_id = shiptocust.party_id (+) AND term.language (+) = userenv('LANG') AND EXISTS ( SELECT 1 FROM oss_bill_lines bill_ln WHERE bill_ln.trx_id = txn.customer_trx_id AND bill_ln.sent_yn = 'Y' )

SQL_Statement

SELECT

txn.customer_trx_id,

txn.trx_number AS transaction_number,

txn.trx_date AS transaction_date,

txn.trx_class AS transaction_class,

(

SELECT

ar_lookups.meaning

FROM

ar_lookups

WHERE

ar_lookups.lookup_type = 'INV/CM'

AND ar_lookups.lookup_code = txn.trx_class

AND ROWNUM = 1

) AS transaction_class_meaning,

txn.complete_flag,

(

SELECT

meaning

FROM

ar_lookups

WHERE

lookup_type = 'YES_NO_SKIP'

AND lookup_code = txn.complete_flag

) AS complete_flag_meaning,

txn.cust_trx_type_seq_id AS transaction_type_id,

typ.type AS transaction_type_code,

typ.name AS transaction_type_name,

txn.bill_to_contact_id,

pbilltocontact.party_name AS bill_to_contact,

txn.bill_to_customer_id,

pbilltocustomer.party_name AS bill_to_customer,

txn.bill_to_site_use_id,

su_bill.location AS bill_to_site_name,

txn.ship_to_party_id,

shiptocust.party_name AS ship_to_customer,

txn.ship_to_contact_id AS ship_to_contact_id,

party.party_name AS ship_to_party_contact,

txn.ship_to_party_site_use_id,

su_ship.location AS ship_to_party_site_name,

txn.term_id AS transaction_payment_term_id,

term.name AS transaction_payment_term_name,

txn.term_due_date AS transaction_term_due_date,

txn.old_trx_number AS original_transaction_number,

txn.previous_customer_trx_id,

(

SELECT

nvl(SUM(lines.extended_amount), 0)

FROM

ra_customer_trx_lines_all lines

WHERE

txn.customer_trx_id = lines.customer_trx_id

) AS invoice_total_amount,

invoice_currency_code

FROM

ra_customer_trx_all txn,

ra_cust_trx_types_all typ,

ra_terms_tl term,

hz_parties shiptocust,

hz_parties pbilltocustomer,

hz_cust_accounts ca,

hz_parties pbilltocontact,

hz_cust_account_roles car,

hz_cust_site_uses_all su_bill,

hz_party_sites ps_bill,

hz_locations hl_bill,

hz_cust_site_uses_all su_ship,

hz_party_sites ps_ship,

hz_locations hl_ship,

hz_cust_account_roles acct_role,

hz_parties party

WHERE

txn.bill_to_customer_id = ca.cust_account_id (+)

AND pbilltocustomer.party_id (+) = ca.party_id

AND txn.bill_to_contact_id = car.cust_account_role_id (+)

AND pbilltocontact.party_id (+) = car.contact_person_id

AND txn.bill_to_site_use_id = su_bill.site_use_id (+)

AND su_bill.cust_acct_site_id = ps_bill.party_site_id (+)

AND ps_bill.location_id = hl_bill.location_id (+)

AND txn.ship_to_contact_id = acct_role.cust_account_role_id (+)

AND acct_role.contact_person_id = party.party_id (+)

AND txn.ship_to_party_site_use_id = su_ship.site_use_id (+)

AND su_ship.cust_acct_site_id = ps_ship.party_site_id (+)

AND ps_ship.location_id = hl_ship.location_id (+)

AND txn.cust_trx_type_seq_id = typ.cust_trx_type_seq_id

AND term.term_id (+) = txn.term_id

AND txn.ship_to_party_id = shiptocust.party_id (+)

AND term.language (+) = userenv('LANG')

AND EXISTS (

SELECT

1

FROM

oss_bill_lines bill_ln

WHERE

bill_ln.trx_id = txn.customer_trx_id

AND bill_ln.sent_yn = 'Y'

)