ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 92EDCA82624B0FBDE053BB6B6358DEEDY select x.ledger, x.operating_unit, x.org_code, x.item_number, x.item_description, x.item_type, x.item_status, x.make_buy_code, &category_columns x.transaction_type, x.item_internal_order_flag, x.item_internal_order_enabled, x.item_invoiceable, x.item_invoice_enabled, x.transaction_id, x.transaction_date, x.sales_order_number, x.so_line_number, x.uom_code, x.ship_quantity, x.unit_cost, x.sales_unit_price, nvl(x.sales_unit_price,0)-x.unit_cost unit_margin, x.sales_quantity_invoiced from ( select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, msiv.concatenated_segments item_number, msiv.description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type, msiv.inventory_item_status_code item_status, xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy_code, mtt.transaction_type_name transaction_type, xxen_util.yes(msiv.internal_order_flag) item_internal_order_flag, xxen_util.yes(msiv.internal_order_enabled_flag) item_internal_order_enabled, xxen_util.yes(msiv.invoiceable_item_flag) item_invoiceable, xxen_util.yes(msiv.invoice_enabled_flag) item_invoice_enabled, to_char(mmt.transaction_id) transaction_id, mmt.transaction_date, ooha.order_number sales_order_number, oola.line_number so_line_number, msiv.primary_uom_code uom_code, mmt.primary_quantity ship_quantity, mmt.new_cost unit_cost, (select max(rctla.unit_selling_price) from ra_customer_trx_lines_all rctla where to_char(oola.line_id)=rctla.interface_line_attribute6 and rctla.interface_line_context='INTERCOMPANY') sales_unit_price, (select sum(rctla.quantity_invoiced) from ra_customer_trx_lines_all rctla where to_char(oola.line_id)=rctla.interface_line_attribute6 and rctla.interface_line_context='INTERCOMPANY') sales_quantity_invoiced, msiv.inventory_item_id, msiv.organization_id from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, mtl_material_transactions mmt, mtl_transaction_types mtt, oe_order_lines_all oola, oe_order_headers_all ooha, mtl_system_items_vl msiv where 1=1 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and mp.organization_id=mmt.organization_id and mmt.transaction_source_type_id=8 and mmt.transaction_action_id<>28 and mmt.transaction_type_id=mtt.transaction_type_id and mmt.trx_source_line_id=oola.line_id and oola.header_id=ooha.header_id and mmt.organization_id=msiv.organization_id and mmt.inventory_item_id=msiv.inventory_item_id ) x order by x.ledger, x.operating_unit, x.org_code, x.item_number, x.transaction_date, x.sales_order_number, x.so_line_number Rebuilt to the Blitz Report SQL standards: the intercompany invoice line is found through its text key, an order line with several invoice lines no longer fails, flags show Yes or blank. Organization security from the Blitz Report organization access, with operating unit and ledger security through profiles Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security. #,##0.00000;[Red](#,##0.00000) US CAC Intercompany SO Shipping Transaction vs. Invoice Price Report to compare the transaction item cost from the selling internal org to the invoice sales price the selling internal organization is charging the internal receiving organization. /* +=============================================================================+ -- | Copyright 2017 - 2022 Douglas Volz Consulting, Inc. | -- | All rights reserved. | -- | Permission to use this code is granted provided the original author is | -- | acknowledged. No warranties, express or otherwise is included in this | -- | permission. | -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -- | -- | Program Name: xxx_verify_so_price_vs_cost_txn.sql -- | -- | Parameters: -- | p_trx_date_from -- starting shipping transaction date, mandatory -- | p_trx_date_to -- ending shipping transaction date, mandatory -- | -- | Description: -- | Report to compare the transaction item cost from the selling internal org -- | to the invoice sales price the selling internal organization is charging the -- | receiving organization. -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 01 May 2017 Douglas Volz Initial Coding -- | 1.1 03 Sep 2019 Douglas Volz Add Ledger, Operating Unit, Item Type, Status -- | and item categories for cost and inventory. -- | 1.2 27 Jan 2020 Douglas Volz Added Ledger, Operating Unit and Org Code -- | parameters. -- | 1.3 09 Jul 2022 Douglas Volz Changes for multi-language lookup values. -- | 1.4 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics &category_columns 1=1 1 10 1=1 mmt.transaction_date>=:p_trx_date_from Date select max(oap.period_start_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id Y US Transaction Date From 2 20 1=1 mmt.transaction_date<:p_trx_date_to+1 Date select max(oap.schedule_close_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id Y US Transaction Date To 3 30 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>',p_table_alias=>'x') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 4 40 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>',p_table_alias=>'x') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 5 50 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>',p_table_alias=>'x') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 6 60 1=1 gl.name=:p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name US Ledger