CML_MATERIAL_PO_SCHEDULES_V

Details

• Schema: FUSION

• Object owner: CML

• Object type: VIEW

Columns

Name
PO_HEADER_ID PO_LINE_ID CMR_PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID SHIPMENT_NUMBER QTY_IN_PRIMARY_UOM QTY_IN_SECONDARY_UOM UOM_CODE SECONDARY_UOM_CODE PRICE PRICE_IN_FUNC_CURRENCY NR_TAX_IN_FUNC_CURRENCY DELIVER_TO_INVENTORY_ORG_ID SHIP_TO_LOCATION_ID NEED_BY_DATE PROMISED_DATE DESTINATION_TYPE_CODE ACCRUE_ON_RECEIPT_FLAG EVENT_DATE

Query

SQL_Statement

SELECT cpod.po_header_id, cpod.po_line_id, cpod.cmr_po_line_location_id, cpod.po_line_location_id, cpod.shipment_number, SUM(NVL(cpod.quantity_ordered ,0) - NVL(cpod.quantity_cancelled,0)) qty_in_primary_uom, SUM(NVL(cpod.secondary_quantity,0)- NVL(cpod.secondary_qty_cancelled,0)) qty_in_secondary_uom, cpod.uom_code, cpod.secondary_uom_code, cpod.tax_exclusive_price price, SUM((nvl(cpod.quantity_ordered, 0) - nvl(cpod.quantity_cancelled, 0)) * cpod.tax_exclusive_price * nvl(cpod.currency_conversion_rate , 1)) / DECODE(SUM((nvl(cpod.quantity_ordered, 0) - nvl(cpod.quantity_cancelled, 0))), 0, 1, SUM((nvl(cpod.quantity_ordered, 0 ) - nvl(cpod.quantity_cancelled, 0)))) price_in_func_currency, SUM((cpod.nonrecoverable_tax + nonrecoverable_inclusive_tax) * nvl(cpod.currency_conversion_rate, 1) *(nvl(cpod.quantity_ordered , 0) - nvl(cpod.quantity_cancelled, 0))) / DECODE(SUM((nvl(cpod.quantity_ordered, 0) - nvl(cpod.quantity_cancelled, 0))), 0, 1 , SUM((nvl(cpod.quantity_ordered, 0) - nvl(cpod.quantity_cancelled, 0)))) nr_tax_in_func_currency, cpod.deliver_to_inventory_org_id, cpod.ship_to_location_id, cpod.need_by_date, cpod.promised_date, cpod.destination_type_code, cpod.accrue_on_receipt_flag, MAX(event_date) event_date FROM cmr_purchase_order_dtls cpod WHERE cpod.accrue_on_receipt_flag = 'Y' AND cpod.purchase_basis = 'GOODS' AND cpod.active_flag = 'Y' AND NVL(cpod.consigned_flag, 'N') = 'N' AND cpod.quantity_ordered <> cpod.quantity_cancelled GROUP BY cpod.po_header_id, cpod.po_line_id, cpod.cmr_po_line_location_id, cpod.po_line_location_id, cpod.shipment_number, cpod.uom_code, cpod.secondary_uom_code, cpod.tax_exclusive_price, cpod.deliver_to_inventory_org_id, cpod.ship_to_location_id, cpod.need_by_date, cpod.promised_date, cpod.destination_type_code, cpod.accrue_on_receipt_flag