VIEW: APPS.OZF_RESALE_LINES_V

Source

View Text - Preformatted

SELECT lines.resale_line_id, lines.status_code, lkup.meaning user_status, lines.resale_transfer_type, lkuprtt.meaning transfer_type_meaning, lines.date_invoiced, lines.invoice_number, lines.end_cust_party_name distributor_num, lines.orig_system_item_number, lines.orig_system_uom, lines.orig_system_quantity, lines.end_cust_party_id, hzpec.party_name derived_end_cust_party_name, lines.inventory_item_id, prod.concatenated_segments derived_inventory_item_name, lines.item_number, lines.item_description, lines.uom_code, uommap.unit_of_measure derived_uom, adj.agreement_name distributoragreement, adj.agreement_id agreement, qp.name as derived_agreement_name, adj.corrected_agreement_id, adj.corrected_agreement_name, nvl(lines.purchase_price,0) purchase_price, nvl(lines.selling_price,0) selling_price, nvl(adj.claimed_amount,0) claimed_amount, nvl(adj.total_claimed_amount,0) total_claimed_amount, nvl(lines.orig_system_purchase_price,0) orig_system_purchase_price , nvl(adj.calculated_price,0) calculated_price , nvl(adj.allowed_amount,0) allowed_amount, nvl(adj.total_allowed_amount,0) total_allowed_amount, nvl(adj.total_accepted_amount,0) total_accepted_amount , nvl((adj.total_accepted_amount - adj.total_claimed_amount),0) disputedamount, origsysuom.unit_of_measure orig_sys_uom, lines.product_transfer_date, lines.ship_from_cust_account_id, hzpshf.party_name derived_ship_from_party_name, lines.ship_from_party_name ship_from_cust_name, lines.ship_from_site_id, null ship_from_site_address, lines.ship_from_contact_party_id, lines.ship_from_contact_name, lines.sold_from_cust_account_id, hzpsof.party_name derived_sold_from_party_name, lines.sold_from_party_name sold_from_cust_name, lines.sold_from_site_id, null sold_from_site_address, lines.sold_from_contact_party_id, null sold_from_contact_party_name, lines.bill_to_cust_account_id, decode (lines.direct_customer_flag, 'F', hzpbt.party_name, capbt.party_name) derived_bill_to_party_name, lines.bill_to_party_name bill_to_cust_name, lines.bill_to_site_use_id, null bill_to_site_use_address, 'BILL_TO' bill_to_site_use_type, lines.bill_to_contact_party_id, lines.bill_to_contact_name, lines.ship_to_cust_account_id, hzpsht.party_name derived_ship_to_party_name, lines.ship_to_party_name ship_to_cust_name, lines.ship_to_site_use_id, null ship_to_site_use_address, 'SHIP_TO' ship_to_site_use_type, lines.ship_to_contact_party_id, lines.ship_to_party_name, lines.end_cust_site_use_id, null end_cust_site_use_address, lines.end_cust_party_site_id, null end_cust_party_site_address, lines.end_cust_party_name, lines.direct_customer_flag, adj.agreement_type, adj.agreement_price, adj.agreement_uom_code, lines.price_list_id, lines.price_list_name, lines.orig_system_reference, lines.orig_system_line_reference, lines.orig_system_currency_code, lines.orig_system_selling_price, lines.orig_system_purchase_uom, adj.orig_system_agreement_uom, adj.orig_system_agreement_name, adj.orig_system_agreement_type, adj.orig_system_agreement_status, lines.currency_code, lines.exchange_rate, lines.exchange_rate_type, lines.exchange_rate_date, lines.po_number, lines.po_release_number, lines.po_type, lines.order_number, lines.date_ordered, adj.accepted_amount, adj.line_tolerance_amount, adj.tolerance_flag, lines.purchase_uom_code, lines.acctd_selling_price, lines.quantity, adj.acctd_calculated_price, adj.calculated_amount, adj.credit_code, adj.credit_advice_date, lines.upc_code, lines.product_category_id, lines.category_name, lines.orig_system_purchase_curr, adj.orig_system_agreement_curr, adj.orig_system_agreement_price, lines.date_shipped, lines.acctd_purchase_price, lines.tracing_flag, lines.end_cust_site_use_code from ozf_resale_lines_all lines, ozf_resale_adjustments_all adj, ozf_lookups lkup, hz_parties hzpec, hz_parties hzpbt, hz_cust_accounts hzca, hz_parties capbt, hz_parties hzpsht, hz_parties hzpshf, hz_cust_accounts cashf, hz_parties hzpsof, hz_cust_accounts casof, mtl_units_of_measure uommap, mtl_units_of_measure origsysuom, qp_list_headers qp, mtl_system_items_vl prod, ozf_lookups lkuprtt WHERE lines.resale_line_id = adj.resale_line_id (+) and lines.uom_code = uommap.uom_code (+) and lkup.lookup_type='OZF_RESALE_LINE_STATUS' and lkup.lookup_code=lines.status_code and lines.end_cust_party_id = hzpec.party_id (+) and lines.bill_to_party_id = hzpbt.party_id (+) and lines.bill_to_cust_account_id = hzca.cust_account_id and hzca.party_id = capbt.party_id and lines.ship_to_party_id = hzpsht.party_id (+) and lines.ship_from_cust_account_id = cashf.cust_account_id (+) and cashf.cust_account_id = hzpshf.party_id (+) and lines.sold_from_cust_account_id = casof.cust_account_id (+) and casof.party_id = hzpsof.party_id (+) and adj.agreement_id = qp.list_header_id(+) and lines.inventory_item_id = prod.inventory_item_id (+) and prod.organization_id (+)= FND_PROFILE.value('AMS_ITEM_ORGANIZATION_ID') and lkuprtt.lookup_type (+)= 'OZF_RESALE_TRANSFER_TYPE' and lines.resale_transfer_type = lkuprtt.lookup_code (+) and lines.orig_system_uom = origsysuom.uom_code (+)

View Text - HTML Formatted

SELECT LINES.RESALE_LINE_ID
, LINES.STATUS_CODE
, LKUP.MEANING USER_STATUS
, LINES.RESALE_TRANSFER_TYPE
, LKUPRTT.MEANING TRANSFER_TYPE_MEANING
, LINES.DATE_INVOICED
, LINES.INVOICE_NUMBER
, LINES.END_CUST_PARTY_NAME DISTRIBUTOR_NUM
, LINES.ORIG_SYSTEM_ITEM_NUMBER
, LINES.ORIG_SYSTEM_UOM
, LINES.ORIG_SYSTEM_QUANTITY
, LINES.END_CUST_PARTY_ID
, HZPEC.PARTY_NAME DERIVED_END_CUST_PARTY_NAME
, LINES.INVENTORY_ITEM_ID
, PROD.CONCATENATED_SEGMENTS DERIVED_INVENTORY_ITEM_NAME
, LINES.ITEM_NUMBER
, LINES.ITEM_DESCRIPTION
, LINES.UOM_CODE
, UOMMAP.UNIT_OF_MEASURE DERIVED_UOM
, ADJ.AGREEMENT_NAME DISTRIBUTORAGREEMENT
, ADJ.AGREEMENT_ID AGREEMENT
, QP.NAME AS DERIVED_AGREEMENT_NAME
, ADJ.CORRECTED_AGREEMENT_ID
, ADJ.CORRECTED_AGREEMENT_NAME
, NVL(LINES.PURCHASE_PRICE
, 0) PURCHASE_PRICE
, NVL(LINES.SELLING_PRICE
, 0) SELLING_PRICE
, NVL(ADJ.CLAIMED_AMOUNT
, 0) CLAIMED_AMOUNT
, NVL(ADJ.TOTAL_CLAIMED_AMOUNT
, 0) TOTAL_CLAIMED_AMOUNT
, NVL(LINES.ORIG_SYSTEM_PURCHASE_PRICE
, 0) ORIG_SYSTEM_PURCHASE_PRICE
, NVL(ADJ.CALCULATED_PRICE
, 0) CALCULATED_PRICE
, NVL(ADJ.ALLOWED_AMOUNT
, 0) ALLOWED_AMOUNT
, NVL(ADJ.TOTAL_ALLOWED_AMOUNT
, 0) TOTAL_ALLOWED_AMOUNT
, NVL(ADJ.TOTAL_ACCEPTED_AMOUNT
, 0) TOTAL_ACCEPTED_AMOUNT
, NVL((ADJ.TOTAL_ACCEPTED_AMOUNT - ADJ.TOTAL_CLAIMED_AMOUNT)
, 0) DISPUTEDAMOUNT
, ORIGSYSUOM.UNIT_OF_MEASURE ORIG_SYS_UOM
, LINES.PRODUCT_TRANSFER_DATE
, LINES.SHIP_FROM_CUST_ACCOUNT_ID
, HZPSHF.PARTY_NAME DERIVED_SHIP_FROM_PARTY_NAME
, LINES.SHIP_FROM_PARTY_NAME SHIP_FROM_CUST_NAME
, LINES.SHIP_FROM_SITE_ID
, NULL SHIP_FROM_SITE_ADDRESS
, LINES.SHIP_FROM_CONTACT_PARTY_ID
, LINES.SHIP_FROM_CONTACT_NAME
, LINES.SOLD_FROM_CUST_ACCOUNT_ID
, HZPSOF.PARTY_NAME DERIVED_SOLD_FROM_PARTY_NAME
, LINES.SOLD_FROM_PARTY_NAME SOLD_FROM_CUST_NAME
, LINES.SOLD_FROM_SITE_ID
, NULL SOLD_FROM_SITE_ADDRESS
, LINES.SOLD_FROM_CONTACT_PARTY_ID
, NULL SOLD_FROM_CONTACT_PARTY_NAME
, LINES.BILL_TO_CUST_ACCOUNT_ID
, DECODE (LINES.DIRECT_CUSTOMER_FLAG
, 'F'
, HZPBT.PARTY_NAME
, CAPBT.PARTY_NAME) DERIVED_BILL_TO_PARTY_NAME
, LINES.BILL_TO_PARTY_NAME BILL_TO_CUST_NAME
, LINES.BILL_TO_SITE_USE_ID
, NULL BILL_TO_SITE_USE_ADDRESS
, 'BILL_TO' BILL_TO_SITE_USE_TYPE
, LINES.BILL_TO_CONTACT_PARTY_ID
, LINES.BILL_TO_CONTACT_NAME
, LINES.SHIP_TO_CUST_ACCOUNT_ID
, HZPSHT.PARTY_NAME DERIVED_SHIP_TO_PARTY_NAME
, LINES.SHIP_TO_PARTY_NAME SHIP_TO_CUST_NAME
, LINES.SHIP_TO_SITE_USE_ID
, NULL SHIP_TO_SITE_USE_ADDRESS
, 'SHIP_TO' SHIP_TO_SITE_USE_TYPE
, LINES.SHIP_TO_CONTACT_PARTY_ID
, LINES.SHIP_TO_PARTY_NAME
, LINES.END_CUST_SITE_USE_ID
, NULL END_CUST_SITE_USE_ADDRESS
, LINES.END_CUST_PARTY_SITE_ID
, NULL END_CUST_PARTY_SITE_ADDRESS
, LINES.END_CUST_PARTY_NAME
, LINES.DIRECT_CUSTOMER_FLAG
, ADJ.AGREEMENT_TYPE
, ADJ.AGREEMENT_PRICE
, ADJ.AGREEMENT_UOM_CODE
, LINES.PRICE_LIST_ID
, LINES.PRICE_LIST_NAME
, LINES.ORIG_SYSTEM_REFERENCE
, LINES.ORIG_SYSTEM_LINE_REFERENCE
, LINES.ORIG_SYSTEM_CURRENCY_CODE
, LINES.ORIG_SYSTEM_SELLING_PRICE
, LINES.ORIG_SYSTEM_PURCHASE_UOM
, ADJ.ORIG_SYSTEM_AGREEMENT_UOM
, ADJ.ORIG_SYSTEM_AGREEMENT_NAME
, ADJ.ORIG_SYSTEM_AGREEMENT_TYPE
, ADJ.ORIG_SYSTEM_AGREEMENT_STATUS
, LINES.CURRENCY_CODE
, LINES.EXCHANGE_RATE
, LINES.EXCHANGE_RATE_TYPE
, LINES.EXCHANGE_RATE_DATE
, LINES.PO_NUMBER
, LINES.PO_RELEASE_NUMBER
, LINES.PO_TYPE
, LINES.ORDER_NUMBER
, LINES.DATE_ORDERED
, ADJ.ACCEPTED_AMOUNT
, ADJ.LINE_TOLERANCE_AMOUNT
, ADJ.TOLERANCE_FLAG
, LINES.PURCHASE_UOM_CODE
, LINES.ACCTD_SELLING_PRICE
, LINES.QUANTITY
, ADJ.ACCTD_CALCULATED_PRICE
, ADJ.CALCULATED_AMOUNT
, ADJ.CREDIT_CODE
, ADJ.CREDIT_ADVICE_DATE
, LINES.UPC_CODE
, LINES.PRODUCT_CATEGORY_ID
, LINES.CATEGORY_NAME
, LINES.ORIG_SYSTEM_PURCHASE_CURR
, ADJ.ORIG_SYSTEM_AGREEMENT_CURR
, ADJ.ORIG_SYSTEM_AGREEMENT_PRICE
, LINES.DATE_SHIPPED
, LINES.ACCTD_PURCHASE_PRICE
, LINES.TRACING_FLAG
, LINES.END_CUST_SITE_USE_CODE
FROM OZF_RESALE_LINES_ALL LINES
, OZF_RESALE_ADJUSTMENTS_ALL ADJ
, OZF_LOOKUPS LKUP
, HZ_PARTIES HZPEC
, HZ_PARTIES HZPBT
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES CAPBT
, HZ_PARTIES HZPSHT
, HZ_PARTIES HZPSHF
, HZ_CUST_ACCOUNTS CASHF
, HZ_PARTIES HZPSOF
, HZ_CUST_ACCOUNTS CASOF
, MTL_UNITS_OF_MEASURE UOMMAP
, MTL_UNITS_OF_MEASURE ORIGSYSUOM
, QP_LIST_HEADERS QP
, MTL_SYSTEM_ITEMS_VL PROD
, OZF_LOOKUPS LKUPRTT
WHERE LINES.RESALE_LINE_ID = ADJ.RESALE_LINE_ID (+)
AND LINES.UOM_CODE = UOMMAP.UOM_CODE (+)
AND LKUP.LOOKUP_TYPE='OZF_RESALE_LINE_STATUS'
AND LKUP.LOOKUP_CODE=LINES.STATUS_CODE
AND LINES.END_CUST_PARTY_ID = HZPEC.PARTY_ID (+)
AND LINES.BILL_TO_PARTY_ID = HZPBT.PARTY_ID (+)
AND LINES.BILL_TO_CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID
AND HZCA.PARTY_ID = CAPBT.PARTY_ID
AND LINES.SHIP_TO_PARTY_ID = HZPSHT.PARTY_ID (+)
AND LINES.SHIP_FROM_CUST_ACCOUNT_ID = CASHF.CUST_ACCOUNT_ID (+)
AND CASHF.CUST_ACCOUNT_ID = HZPSHF.PARTY_ID (+)
AND LINES.SOLD_FROM_CUST_ACCOUNT_ID = CASOF.CUST_ACCOUNT_ID (+)
AND CASOF.PARTY_ID = HZPSOF.PARTY_ID (+)
AND ADJ.AGREEMENT_ID = QP.LIST_HEADER_ID(+)
AND LINES.INVENTORY_ITEM_ID = PROD.INVENTORY_ITEM_ID (+)
AND PROD.ORGANIZATION_ID (+)= FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID')
AND LKUPRTT.LOOKUP_TYPE (+)= 'OZF_RESALE_TRANSFER_TYPE'
AND LINES.RESALE_TRANSFER_TYPE = LKUPRTT.LOOKUP_CODE (+)
AND LINES.ORIG_SYSTEM_UOM = ORIGSYSUOM.UOM_CODE (+)