DPP Customer Inventory Detail

Description
Categories: BI Publisher, Sales
Application: Oracle Price Protection
Source:
Short Name: DPPCUSTINV
DB package:
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