ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223EF703869E053B46B63588994Y SELECT hca.account_number CUSTOMER_NUMBER, hca.account_name CUSTOMER_NAME, msik.concatenated_segments item_number, msik.description item_description, a.sold_to_party_id party_id, a.inventory_item_id inventory_item_id, muomv.unit_of_measure primary_uom, a.transaction_date, SUM(decode(a.transfer_type, 'IN', nvl(a.common_quantity, 0), 0)) AS common_quantity_in, SUM(decode(a.transfer_type, 'OUT', nvl(a.common_quantity, 0), 0)) AS common_quantity_out, SUM(decode(a.transfer_type, 'IN', nvl(a.common_quantity, 0), 0)) -SUM(decode(a.transfer_type, 'OUT', nvl(a.common_quantity, 0), 0)) AS onhand_quantity FROM dpp_customer_claims_all dcc, hz_cust_accounts hca, dpp_transaction_headers_all dh, ap_suppliers aps, ap_supplier_sites_all apss, hr_operating_units hr, ozf_sales_transactions_all a, mtl_units_of_measure_vl muomv, mtl_system_items_kfv msik, mtl_parameters mp, financials_system_params_all fspa WHERE muomv.uom_code = a.primary_uom_code AND dh.org_id = hr.organization_id AND fspa.org_id = dh.org_id AND fspa.inventory_organization_id = msik.organization_id AND dcc.customer_inv_line_id=:CUST_INV_LINE_ID AND dcc.cust_account_id = hca.cust_account_id AND dcc.inventory_item_id = a.inventory_item_id AND dcc.inventory_item_id= msik.inventory_item_id AND dcc.transaction_header_id = dh.transaction_header_id AND dcc.cust_account_id = :CUST_ACC_ID AND dh.vendor_id = aps.vendor_id AND dh.transaction_number = :TRANSACTION_NUMBER AND dh.vendor_site_id = apss.vendor_site_id AND aps.enabled_flag = 'Y' AND aps.hold_flag = 'N' AND aps.vendor_id = apss.vendor_id AND hca.party_id = a.sold_to_party_id AND a.error_flag = 'N' AND a.inventory_item_id = :INVENTORY_ITEM_ID AND((dh.days_covered IS NULL AND a.transaction_date <= dh.effective_start_date) OR(dh.days_covered IS NOT NULL AND a.transaction_date BETWEEN dh.effective_start_date -dh.days_covered AND dh.effective_start_date)) GROUP BY a.sold_to_party_id, a.inventory_item_id, muomv.unit_of_measure, a.transaction_date, msik.concatenated_segments, msik.description, hca.account_number, hca.account_name order by a.transaction_date DPP DPPCUSTINV US DPP Customer Inventory Detail Application: Oracle Price Protection Source: Short Name: DPPCUSTINV DB package: BI Publisher :cust_acc_id :cust_inv_line_id :inventory_item_id :transaction_number