CST_GP_POD_GT_SCHEDULE_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
CMR_PO_LINE_LOCATION_ID CONVERTED_TAX_EXCLUSIVE_PRICE CONVERTED_AVG_NR_TAX POS_QTY_ORDERED_PRIMARY_UOM POS_QTY_SOURCE_DOC_UOM TRADE_ORG_BU_FUNC_CURR_CODE MATCH_OPTION NC_TAX_EXCLUSIVE_PRICE NC_NR_TAX

Query

SQL_Statement

select cgpodgt.cmr_po_line_location_id, tax_exclusive_price*nvl(CURRENCY_CONVERSION_RATE,1)/CONV_FACTOR_TO_PRIMARY_UOM converted_tax_exclusive_price, sum((CPOD.QUANTITY_ORDERED - CPOD.QUANTITY_CANCELLED)*((NR_TAX_IN_PRIMARY_UOM + (NVL(nonrecoverable_inclusive_tax,0)/CONV_FACTOR_TO_PRIMARY_UOM))*NVL(CURRENCY_CONVERSION_RATE,1)))/decode(sum(CPOD.QUANTITY_ORDERED - CPOD.QUANTITY_CANCELLED),0,1,sum(CPOD.QUANTITY_ORDERED - CPOD.QUANTITY_CANCELLED)) converted_avg_nr_tax, sum((CPOD.QUANTITY_ORDERED - CPOD.QUANTITY_CANCELLED)*CPOD.CONV_FACTOR_TO_PRIMARY_UOM) pos_qty_ordered_primary_uom , sum(CPOD.QUANTITY_ORDERED - CPOD.QUANTITY_CANCELLED) pos_qty_source_doc_uom , CPOD.trade_org_BU_FUNC_CURR_CODE, CPOD.MATCH_OPTION, tax_exclusive_price*nvl(CURRENCY_CONVERSION_RATE,1) nc_tax_exclusive_price, sum((CPOD.QUANTITY_ORDERED - CPOD.QUANTITY_CANCELLED)*((NONRECOVERABLE_TAX + NVL(nonrecoverable_inclusive_tax,0))*NVL(CURRENCY_CONVERSION_RATE,1)))/decode(sum(CPOD.QUANTITY_ORDERED - CPOD.QUANTITY_CANCELLED),0,1,sum(CPOD.QUANTITY_ORDERED - CPOD.QUANTITY_CANCELLED)) nc_nr_tax FROM cst_gp_purchase_order_dists_gt cgpodgt, CMR_PURCHASE_ORDER_DTLS CPOD WHERE cgpodgt.cmr_po_line_location_id = CPOD.cmr_po_line_location_id and ((cpod.processed_by_ca_flag = 'Y' and (active_flag = 'Y' OR active_for_ca_flag = 'Y')) OR (cpod.processed_by_ca_flag = 'N' and cgpodgt.cmr_po_distribution_id = cpod.cmr_po_distribution_id and cgpodgt.event_date = cpod.event_date) ) group by cgpodgt.cmr_po_line_location_id, CPOD.tax_exclusive_price, CPOD.currency_conversion_rate, CPOD.conv_factor_to_primary_uom, CPOD.trade_org_BU_FUNC_CURR_CODE, CPOD.match_option