CML_SERIVCEPOS_V

Details

• Schema: FUSION

• Object owner: FUSION

• Object type: VIEW

Columns

Name
PO_HEADER_ID PO_NUMBER PO_LINE_ID LINE_NUMBER CMR_PO_LINE_LOCATION_ID SHIPMENT_NUMBER PROCUREMENT_BUSINESS_UNIT_ID DELIVER_TO_BUSINESS_UNIT_ID SOLD_TO_LEGAL_ENTITY_ID CURRENCY_CODE VENDOR_ID VENDOR_SITE_ID AMOUNT AMOUNT_IN_FUNC_CURRENCY NONRECOVERABLE_TAX NR_TAX_IN_FUNC_CURRENCY EVENT_DATE

Query

SQL_Statement

SELECT cpod.po_header_id, cpod.po_number, cpod.po_line_id, cpod.line_number, cpod.cmr_po_line_location_id, cpod.shipment_number, cpod.procurement_business_unit_id, cpod.deliver_to_business_unit_id, cpod.sold_to_legal_entity_id, cpod.currency_code, cpod.vendor_id, cpod.vendor_site_id, SUM(NVL(cpod.tax_exclusive_price,0)) amount, SUM(NVL(cpod.tax_exclusive_price,0))* NVL(cpod.currency_conversion_rate,1) amount_in_func_currency, SUM(cpod.nonrecoverable_tax + cpod.nonrecoverable_inclusive_tax) * (NVL(cpod.tax_exclusive_price,0)) nonrecoverable_tax, SUM(cpod.nonrecoverable_tax + cpod.nonrecoverable_inclusive_tax)* NVL(cpod.currency_conversion_rate,1)*(NVL(cpod.tax_exclusive_price,0)) nr_tax_in_func_currency, (SELECT MAX(event_date) FROM cmr_purchase_order_dtls cpod1 WHERE cpod1.po_header_id = cpod.po_header_id AND cpod1.po_line_id =cpod.po_line_id AND cpod1.cmr_po_line_location_id = cpod.cmr_po_line_location_id ) event_date FROM cmr_purchase_order_dtls cpod WHERE cpod.purchase_basis = 'SERVICES' AND cpod.active_flag = 'Y' GROUP BY cpod.po_header_id, cpod.po_number, cpod.po_line_id, cpod.line_number, cpod.cmr_po_line_location_id, cpod.shipment_number, cpod.procurement_business_unit_id, cpod.deliver_to_business_unit_id, cpod.sold_to_legal_entity_id, cpod.currency_code, cpod.vendor_id, cpod.vendor_site_id, cpod.tax_exclusive_price, cpod.currency_conversion_rate