CML_POSCHEDULES_V

Details

• Schema: FUSION

• Object owner: FUSION

• Object type: VIEW

Columns

Name

PO_HEADER_ID PO_NUMBER PO_LINE_ID CMR_PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID LINE_NUMBER INVENTORY_ITEM_ID CATEGORY_ID DEFAULT_INVENTORY_ORG_ID PURCHASE_BASIS 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 PROCUREMENT_BUSINESS_UNIT_ID DELIVER_TO_BUSINESS_UNIT_ID SOLD_TO_LEGAL_ENTITY_ID REQUISITIONING_BU_ID CURRENCY_CODE VENDOR_ID VENDOR_SITE_ID AGENT_ID

Query

SQL_Statement

SELECT cpod.po_header_id, cpod.po_number, cpod.po_line_id, cpod.cmr_po_line_location_id, cpod.po_line_location_id, cpod.line_number, cpod.inventory_item_id, cpod.category_id, cpod.default_inventory_org_id, cpod.purchase_basis, 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, AVG((cpod.nonrecoverable_tax+nonrecoverable_inclusive_tax) * NVL(cpod.currency_conversion_rate,1)) 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, cpod.procurement_business_unit_id, cpod.deliver_to_business_unit_id, cpod.sold_to_legal_entity_id, cpod.requisitioning_bu_id, cpod.currency_code, cpod.vendor_id, cpod.vendor_site_id, cpod.agent_id 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' GROUP BY cpod.po_header_id, cpod.po_number, 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, cpod.procurement_business_unit_id, cpod.deliver_to_business_unit_id, cpod.sold_to_legal_entity_id, cpod.requisitioning_bu_id, cpod.currency_code, cpod.vendor_id, cpod.vendor_site_id, cpod.agent_id, cpod.inventory_item_id, cpod.category_id, cpod.default_inventory_org_id, cpod.purchase_basis, cpod.line_number