VIEW: APPS.OZF_RESALE_BATCHES_V

Source

View Text - Preformatted

SELECT rb.resale_batch_id, lk_bt.meaning batch_type, rb.batch_number, rb.batch_count, rb.year, rb.month, rb.report_date, rb.report_start_date, rb.report_end_date, lk_st.meaning status_code, rb.data_source_code, rb.reference_type, rb.reference_number, rb.comments, rb.partner_claim_number, lk_tpc.meaning transaction_purpose_code, lk_ttc.meaning transaction_type_code, rb.org_id, lk_pt.meaning partner_type, rb.partner_id, hp.party_name partner_party_name, hca.account_number partner_cust_account_number, rb.partner_site_id, rb.partner_contact_party_id, rb.partner_contact_name, rb.partner_email, rb.partner_phone, rb.partner_fax, rb.header_tolerance_operand, rb.header_tolerance_calc_code, rb.line_tolerance_operand, rb.line_tolerance_calc_code, rb.currency_code, rb.claimed_amount, rb.allowed_amount, rb.accepted_amount, rb.paid_amount, rb.disputed_amount, rb.lines_invalid, rb.lines_w_tolerance, rb.lines_disputed, rb.batch_set_id_code, lk_cr.meaning credit_code, rb.credit_advice_date, rb.purge_flag FROM ozf_resale_batches_all rb , hz_parties hp , hz_cust_accounts hca , ozf_lookups lk_st , ozf_lookups lk_bt , ozf_lookups lk_tpc , ozf_lookups lk_ttc , ozf_lookups lk_pt , ozf_lookups lk_cr WHERE rb.partner_party_id = hp.party_id AND rb.partner_cust_account_id = hca.cust_account_id AND rb.status_code = lk_st.lookup_code AND lk_st.lookup_type = 'OZF_RESALE_BATCH_STATUS' AND rb.batch_type = lk_bt.lookup_code (+) AND lk_bt.lookup_type = 'OZF_RESALE_BATCH_TYPE' AND rb.transaction_purpose_code = lk_tpc.lookup_code (+) AND lk_tpc.lookup_type (+) = 'OZF_TRANSACTION_PURPOSE_CODE' AND rb.transaction_type_code = lk_ttc.lookup_code (+) AND lk_tpc.lookup_type (+) = 'OZF_TRANSACTION_TYPE_CODE' AND rb.partner_type = lk_pt.lookup_code (+) AND lk_pt.lookup_type (+) = 'OZF_RESALE_PARTNER_TYPE' AND rb.credit_code = lk_cr.lookup_code (+) AND lk_cr.lookup_type (+) = 'OZF_CREDIT_CODE'

View Text - HTML Formatted

SELECT RB.RESALE_BATCH_ID
, LK_BT.MEANING BATCH_TYPE
, RB.BATCH_NUMBER
, RB.BATCH_COUNT
, RB.YEAR
, RB.MONTH
, RB.REPORT_DATE
, RB.REPORT_START_DATE
, RB.REPORT_END_DATE
, LK_ST.MEANING STATUS_CODE
, RB.DATA_SOURCE_CODE
, RB.REFERENCE_TYPE
, RB.REFERENCE_NUMBER
, RB.COMMENTS
, RB.PARTNER_CLAIM_NUMBER
, LK_TPC.MEANING TRANSACTION_PURPOSE_CODE
, LK_TTC.MEANING TRANSACTION_TYPE_CODE
, RB.ORG_ID
, LK_PT.MEANING PARTNER_TYPE
, RB.PARTNER_ID
, HP.PARTY_NAME PARTNER_PARTY_NAME
, HCA.ACCOUNT_NUMBER PARTNER_CUST_ACCOUNT_NUMBER
, RB.PARTNER_SITE_ID
, RB.PARTNER_CONTACT_PARTY_ID
, RB.PARTNER_CONTACT_NAME
, RB.PARTNER_EMAIL
, RB.PARTNER_PHONE
, RB.PARTNER_FAX
, RB.HEADER_TOLERANCE_OPERAND
, RB.HEADER_TOLERANCE_CALC_CODE
, RB.LINE_TOLERANCE_OPERAND
, RB.LINE_TOLERANCE_CALC_CODE
, RB.CURRENCY_CODE
, RB.CLAIMED_AMOUNT
, RB.ALLOWED_AMOUNT
, RB.ACCEPTED_AMOUNT
, RB.PAID_AMOUNT
, RB.DISPUTED_AMOUNT
, RB.LINES_INVALID
, RB.LINES_W_TOLERANCE
, RB.LINES_DISPUTED
, RB.BATCH_SET_ID_CODE
, LK_CR.MEANING CREDIT_CODE
, RB.CREDIT_ADVICE_DATE
, RB.PURGE_FLAG
FROM OZF_RESALE_BATCHES_ALL RB
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS HCA
, OZF_LOOKUPS LK_ST
, OZF_LOOKUPS LK_BT
, OZF_LOOKUPS LK_TPC
, OZF_LOOKUPS LK_TTC
, OZF_LOOKUPS LK_PT
, OZF_LOOKUPS LK_CR
WHERE RB.PARTNER_PARTY_ID = HP.PARTY_ID
AND RB.PARTNER_CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND RB.STATUS_CODE = LK_ST.LOOKUP_CODE
AND LK_ST.LOOKUP_TYPE = 'OZF_RESALE_BATCH_STATUS'
AND RB.BATCH_TYPE = LK_BT.LOOKUP_CODE (+)
AND LK_BT.LOOKUP_TYPE = 'OZF_RESALE_BATCH_TYPE'
AND RB.TRANSACTION_PURPOSE_CODE = LK_TPC.LOOKUP_CODE (+)
AND LK_TPC.LOOKUP_TYPE (+) = 'OZF_TRANSACTION_PURPOSE_CODE'
AND RB.TRANSACTION_TYPE_CODE = LK_TTC.LOOKUP_CODE (+)
AND LK_TPC.LOOKUP_TYPE (+) = 'OZF_TRANSACTION_TYPE_CODE'
AND RB.PARTNER_TYPE = LK_PT.LOOKUP_CODE (+)
AND LK_PT.LOOKUP_TYPE (+) = 'OZF_RESALE_PARTNER_TYPE'
AND RB.CREDIT_CODE = LK_CR.LOOKUP_CODE (+)
AND LK_CR.LOOKUP_TYPE (+) = 'OZF_CREDIT_CODE'