CST_B_GROSS_MARGIN_DETAILS_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name

DOC_LINE_ID GL_DATE PROFIT_CENTER_BU_ID BU_NAME COST_ORG_ID COST_ORG_NAME COST_BOOK_ID COST_BOOK_CODE CURRENCY_CODE EXTENDED_PRECISION PRECISION CURRENCY_NAME INVENTORY_ORG_ID ITEM_ORGANIZATION_ID INV_ORG_NAME INVENTORY_ITEM_ID ITEM_NUMBER CONFIG_INVENTORY_ITEM_ID CONFIG_ITEM_NUMBER ROOT_INVENTORY_ITEM_ID ROOT_ITEM_NUMBER CATEGORY_ID CATEGORY_NAME SHIPMENT_NUMBER SHIPMENT_TYPE DOO_ORDER_NUMBER DOO_ORDER_TYPE INVOICE_SOURCE INVOICE_NUMBER INVOICE_ID INVOICE_LINE_ID REVENUE_RECOGNITION_SRC CUSTOMER_ID SHIP_TO_CUSTOMER_ID ORDER_TYPE_CODE CUSTOMER_NAME CUSTOMER_TYPE SUCCESSOR_BU_NAME SUCCESSOR_BU_ID RECOGNIZED_REV RECOGNIZED_COGS_AMOUNT RECOGNIZED_GROSS_MARGIN UNRECOGNIZED_REV UNRECOGNIZED_COGS_AMOUNT UNRECOGNIZED_GROSS_MARGIN RECOGNIZED_COGS_PROFIT_AMT UNRECOGNIZED_COGS_PROFIT_AMT RECOGNIZED_COGS_OVERHEAD_AMT UNRECOGNIZED_COGS_OVERHEAD_AMT RECOGNIZED_COGS_RESOURCE_AMT UNRECOGNIZED_COGS_RESOURCE_AMT COGS_ACCOUNT_ID

Query

SQL_Statement

SELECT NVL(cogs.doc_line_id, rev.doc_line_id) doc_line_id, NVL(cogs.gl_date, rev.gl_date) gl_date, NVL(cogs.profit_center_bu_id, rev.profit_center_bu_id) profit_center_bu_id, ( SELECT hr.NAME FROM hr_all_organization_units_vl hr WHERE hr.organization_id =NVL(cogs.profit_center_bu_id, rev.profit_center_bu_id ) ) bu_name, NVL(cogs.cost_org_id,rev.cost_org_id) cost_org_id, ( SELECT hr.NAME FROM hr_all_organization_units_vl hr WHERE hr.organization_id = NVL(cogs.cost_org_id, rev.cost_org_id) ) cost_org_name, NVL(cogs.cost_book_id,rev.cost_book_id) cost_book_id, ( SELECT ccb.cost_book_code FROM cst_cost_books_b ccb WHERE ccb.cost_book_id = NVL(cogs.cost_book_id, rev.cost_book_id) ) cost_book_code, NVL(cogs.currency_code, rev.currency_code) currency_code, currency.extended_precision, currency.PRECISION, currency.NAME currency_name, NVL(cogs.inventory_org_id, rev.inventory_org_id) inventory_org_id, NVL(cogs.ITEM_ORGANIZATION_ID, rev.ITEM_ORGANIZATION_ID) ITEM_ORGANIZATION_ID, ( SELECT hr.NAME FROM hr_all_organization_units_vl hr WHERE hr.organization_id = NVL(cogs.inventory_org_id, rev.inventory_org_id) ) inv_org_name, nvl(cogs.inventory_item_id, rev.inventory_item_id) inventory_item_id, ( SELECT esi.item_number FROM egp_system_items_vl esi WHERE NVL(cogs.ITEM_ORGANIZATION_ID, rev.ITEM_ORGANIZATION_ID) = esi.organization_id AND nvl(cogs.inventory_item_id, rev.inventory_item_id) = esi.inventory_item_id ) item_number, NVL(cogs.config_inventory_item_id, rev.config_inventory_item_id) config_inventory_item_id, ( SELECT esi.item_number FROM egp_system_items_vl esi WHERE NVL(cogs.ITEM_ORGANIZATION_ID, rev.ITEM_ORGANIZATION_ID) = esi.organization_id AND NVL(cogs.config_inventory_item_id, rev.config_inventory_item_id) = esi.inventory_item_id ) config_item_number, NVL(cogs.root_inventory_item_id, rev.root_inventory_item_id) root_inventory_item_id, ( SELECT esi.item_number FROM egp_system_items_vl esi WHERE NVL(cogs.ITEM_ORGANIZATION_ID, rev.ITEM_ORGANIZATION_ID) = esi.organization_id AND NVL(cogs.root_inventory_item_id, rev.root_inventory_item_id) = esi.inventory_item_id ) root_item_number, ( SELECT eic.category_id FROM egp_default_category_sets edcs, egp_item_categories eic WHERE edcs.functional_area_id = 5 AND edcs.category_set_id = eic.category_set_id AND eic.inventory_item_id = NVL(cogs.inventory_item_id, rev.inventory_item_id) AND eic.organization_id = NVL(cogs.ITEM_ORGANIZATION_ID, rev.ITEM_ORGANIZATION_ID) AND ROWNUM = 1 ) category_id, ( SELECT ec.category_name FROM egp_default_category_sets edcs, egp_item_categories eic, egp_categories_vl ec WHERE edcs.functional_area_id = 5 AND edcs.category_set_id = eic.category_set_id AND eic.inventory_item_id = NVL(cogs.inventory_item_id, rev.inventory_item_id) AND eic.organization_id = NVL(cogs.ITEM_ORGANIZATION_ID, rev.ITEM_ORGANIZATION_ID) AND ec.category_id = eic.category_id AND ROWNUM = 1 ) category_name, NVL(cogs.sales_order_shipment_number, rev.sales_order_shipment_number) shipment_number, NVL(cogs.sales_order_shipment_type, rev.sales_order_shipment_type) shipment_type, NVL(cogs.doo_order_number, rev.doo_order_number) doo_order_number, NVL(cogs.doo_order_type, rev.doo_order_type) doo_order_type, NVL(rev.invoice_source, 'DOO') invoice_source, rev.invoice_number invoice_number, rev.invoice_id invoice_id, rev.invoice_line_id invoice_line_id, rev.external_system_reference revenue_recognition_src, NVL(rev.customer_id, cogs.customer_id) customer_id, NVL(rev.ship_to_customer_id, cogs.ship_to_customer_id) ship_to_customer_id, /* ( SELECT h.order_type_code FROM doo_headers_all h, doo_fulfill_lines_all d WHERE NVL(rev.doc_line_id, cogs.doc_line_id) = d.fulfill_line_id AND h.header_id = d.header_id )*/ dha.order_type_code order_type_code, ( SELECT pt.party_name FROM hz_cust_accounts hca, hz_parties pt WHERE hca.party_id = pt.party_id AND hca.cust_account_id = NVL(rev.customer_id, cogs.customer_id) ) customer_name, CASE WHEN NVL(cogs.successor_bu_id, rev.successor_bu_id) IS NULL THEN 'R' ELSE 'I' END customer_type, ( SELECT NAME FROM hr_all_organization_units_vl hr WHERE hr.organization_id = NVL(cogs.successor_bu_id,rev.successor_bu_id) ) successor_bu_name, NVL(cogs.successor_bu_id, rev.successor_bu_id) successor_bu_id, NVL(rev.recognized_rev,0) recognized_rev, NVL(cogs.recognized_cogs_amount,0) recognized_cogs_amount, (NVL(rev.recognized_rev,0) - NVL(cogs.recognized_cogs_amount,0)) recognized_gross_margin, NVL(rev.unrecognized_rev,0) unrecognized_rev, NVL(cogs.unrecognized_cogs_amount,0) unrecognized_cogs_amount, (NVL(rev.unrecognized_rev,0) - NVL(cogs.unrecognized_cogs_amount,0)) unrecognized_gross_margin, NVL(recognized_cogs_profit_amt,0) recognized_cogs_profit_amt, NVL(unrecognized_cogs_profit_amt,0) unrecognized_cogs_profit_amt, NVL(recognized_cogs_overhead_amt,0) recognized_cogs_overhead_amt, NVL(unrecognized_cogs_overhead_amt,0) unrecognized_cogs_overhead_amt, NVL(recognized_cogs_resource_amt,0) recognized_cogs_resource_amt, NVL(unrecognized_cogs_resource_amt,0) unrecognized_cogs_resource_amt, cogs_account_id FROM ( ( SELECT NVL(flow_instance_id, doo_fullfill_line_id) doc_line_id, MAX(profit_center_bu_id) profit_center_bu_id, MAX(cost_org_id) cost_org_id, MAX(cost_book_id) cost_book_id, MAX(ledger_currency) currency_code, MAX(sales_order_shipment_number) sales_order_shipment_number, MAX(sales_order_shipment_type) sales_order_shipment_type, MAX(invoice_source) invoice_source, MAX(invoice_number) invoice_number, MAX(invoice_id) invoice_id, MAX(invoice_line_id) invoice_line_id, MAX(customer_id) customer_id, MAX(ship_to_customer_id) ship_to_customer_id, MAX(successor_bu_id) successor_bu_id, TRUNC(gl_date) gl_date, MAX(inventory_org_id) inventory_org_id, MAX(ITEM_ORGANIZATION_ID) ITEM_ORGANIZATION_ID, MAX(inventory_item_id) inventory_item_id, MAX(config_inventory_item_id) config_inventory_item_id, MAX(root_inventory_item_id) root_inventory_item_id, MAX(doo_order_number) doo_order_number, MAX(doo_order_type) doo_order_type, SUM(NVL(recognized_rev,0)) recognized_rev, SUM(CASE WHEN external_system_reference = 'VRM' THEN 0 ELSE (CASE WHEN line_no = 1 THEN NVL(total_rev,0) ELSE 0 END) - NVL(recognized_rev,0) END) unrecognized_rev, MAX(external_system_reference) external_system_reference FROM cst_revenue_details_v GROUP BY flow_instance_id, doo_fullfill_line_id, TRUNC(gl_date) ) rev FULL OUTER JOIN ( SELECT CASE WHEN base_txn_type_id = 19 THEN doo_fullfill_line_id ELSE NVL( to_number(flow_instance_id), doo_fullfill_line_id) END doc_line_id, MAX(profit_center_bu_id) profit_center_bu_id, MAX(cost_org_id) cost_org_id, MAX(cost_book_id) cost_book_id, MAX(base_currency_code) currency_code, TRUNC(gl_date) gl_date, MAX(source_doc_no) sales_order_shipment_number, MAX(source_doc_type) sales_order_shipment_type, MAX(customer_id) customer_id, MAX(ship_to_customer_id) ship_to_customer_id, MAX(successor_bu_id) successor_bu_id, MAX(inventory_org_id) inventory_org_id, MAX(ITEM_ORGANIZATION_ID) ITEM_ORGANIZATION_ID, MAX(inventory_item_id) inventory_item_id, MAX(config_inventory_item_id) config_inventory_item_id, MAX(root_inventory_item_id) root_inventory_item_id, MAX(doo_order_number) doo_order_number, MAX(doo_order_type) doo_order_type, SUM(NVL(cogs_amount,0)) recognized_cogs_amount, SUM(NVL(dcogs_amount,0)) unrecognized_cogs_amount, MAX(cogs_account_id) cogs_account_id, SUM( CASE WHEN cost_element_type = 'PROFIT_IN_INVENTORY' THEN NVL(cogs_amount, 0) ELSE 0 END) recognized_cogs_profit_amt, SUM( CASE WHEN cost_element_type = 'PROFIT_IN_INVENTORY' THEN NVL(dcogs_amount ,0) ELSE 0 END) unrecognized_cogs_profit_amt, SUM( CASE WHEN cost_element_type IN ('OVERHEAD', 'MATERIAL_OVERHEAD') THEN NVL(cogs_amount,0) ELSE 0 END) recognized_cogs_overhead_amt , SUM( CASE WHEN cost_element_type IN ('OVERHEAD', 'MATERIAL_OVERHEAD') THEN NVL(dcogs_amount,0) ELSE 0 END) unrecognized_cogs_overhead_amt, SUM( CASE WHEN cost_element_type = 'RESOURCE' THEN NVL(cogs_amount,0) ELSE 0 END) recognized_cogs_resource_amt, SUM( CASE WHEN cost_element_type = 'RESOURCE' THEN NVL(dcogs_amount,0) ELSE 0 END) unrecognized_cogs_resource_amt FROM cst_cogs_details_v GROUP BY base_txn_type_id, doo_fullfill_line_id, flow_instance_id, doo_fullfill_line_id, TRUNC(gl_date) ) cogs ON ( rev.doc_line_id = cogs.doc_line_id AND rev.gl_date = cogs.gl_date ) LEFT OUTER JOIN doo_fulfill_lines_all dfla ON ( NVL(rev.doc_line_id, cogs.doc_line_id) = dfla.fulfill_line_id) LEFT OUTER JOIN doo_headers_all dha ON dha.header_id = dfla.header_id ), fnd_currencies_vl currency WHERE currency.currency_code = NVL(cogs.currency_code, rev.currency_code)