DPP Supplier Claims Detail

SELECT
oc.claim_number CLAIM_NUMBER,
dl.supplier_part_num VENDOR_ITEM_NUMBER,
MSIK.concatenated_segments ITEM_NUMBER,
MSIK.description ITEM_DESCRIPTION,
dl.covered_inventory COVERED_INVENTORY,
MUOM.unit_of_measure UOM,
dl.price_change PRICE_CHANGE,
ROUND((dl.price_change * dl.covered_inventory),fl.precision) COVERED_AMOUNT,
dl.APPROVED_INVENTORY APPROVED_QUANTITY,
round(dl.claim_amount,fl.precision) APPROVED_AMOUNT,
round(abs((dl.price_change * dl.covered_inventory) - dl.claim_amount),fl.precision) NET_AMOUNT,
fl.description CURR
FROM
dpp_transaction_headers_all dh
JOIN
dpp_transaction_lines_all dl
ON dh.transaction_header_id = dl.transaction_header_id
AND dh.transaction_number = :TRANSACTION_NUMBER
LEFT OUTER JOIN
dpp_transaction_claims_all dc
ON
dc.claim_id = dl.supp_dist_claim_id
LEFT OUTER JOIN
ozf_claims_all oc
ON
oc.claim_id = dc.claim_id
INNER JOIN
mtl_system_items_kfv msiK
ON dl.inventory_item_id = msiK.inventory_item_id
join
fnd_currencies_vl fl
on fl.currency_code =dh.trx_currency
join
MTL_UNITS_OF_MEASURE_vL MUOM
ON MUOM.uom_code = dl.uom
join
financials_system_params_all fspa
on fspa.org_id = dh.org_id
AND fspa.INVENTORY_ORGANIZATION_ID = msik.organization_id

SELECT oc.claim_number CLAIM_NUMBER, dl.supplier_part_num VENDOR_ITEM_NUMBER, MSIK.concatenated_segments ITEM_NUMBER, MSIK.description ITEM_DESCRIPTION, dl.covered_inventory COVERED_INVENTORY, MUOM.unit_of_measure UOM, dl.price_change PRICE_CHANGE, ROUND((dl.price_change * dl.covered_inventory),fl.precision) COVERED_AMOUNT, dl.APPROVED_INVENTORY APPROVED_QUANTITY, round(dl.claim_amount,fl.precision) APPROVED_AMOUNT, round(abs((dl.price_change * dl.covered_inventory) - dl.claim_amount),fl.precision) NET_AMOUNT, fl.description CURR FROM dpp_transaction_headers_all dh JOIN dpp_transaction_lines_all dl ON dh.transaction_header_id = dl.transaction_header_id AND dh.transaction_number = :TRANSACTION_NUMBER LEFT OUTER JOIN dpp_transaction_claims_all dc ON dc.claim_id = dl.supp_dist_claim_id LEFT OUTER JOIN ozf_claims_all oc ON oc.claim_id = dc.claim_id INNER JOIN mtl_system_items_kfv msiK ON dl.inventory_item_id = msiK.inventory_item_id join fnd_currencies_vl fl on fl.currency_code =dh.trx_currency join MTL_UNITS_OF_MEASURE_vL MUOM ON MUOM.uom_code = dl.uom join financials_system_params_all fspa on fspa.org_id = dh.org_id AND fspa.INVENTORY_ORGANIZATION_ID = msik.organization_id