INV Transaction register
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Transaction register
Application: Inventory
Source: Transaction register (XML)
Short Name: INVTRREG_XML
DB package: INV_INVTRREG_XMLP_PKG
Description: Transaction register
Application: Inventory
Source: Transaction register (XML)
Short Name: INVTRREG_XML
DB package: INV_INVTRREG_XMLP_PKG
Run
INV Transaction register and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
SELECT decode(:P_BREAK_ID, -- 1, nvl(null, ''), 1, nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value', 'INV', 'MSTK', 101, msi.ORGANIZATION_ID, msi.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'), ''), 2,nvl(to_number(to_char(mmt.transaction_date,'J')),0), 3,nvl(&C_type_break_column,''), 4,nvl(&C_type_break_column,''), 5,nvl(mtr.reason_name,''), 6,nvl(mmt.subinventory_code,''), -- 7,nvl(null, ''), 7,nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('c_catg_value', 'INV', 'MCAT', mcat.STRUCTURE_ID, NULL, mcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'), ''), NULL ) break_column, decode(:P_BREAK_ID, 1,mmt.inventory_item_id , '1') break_item_id, decode(:P_BREAK_ID, 1,msi.description, '2') break_item_desc, decode(:P_BREAK_ID, 7,mcat.category_id,'3') break_catg_id, decode(:P_BREAK_ID, 1, NULL, &P_ITEM_FLEXDATA) item_flexdata, decode(:P_BREAK_ID,1,'4', msi.inventory_item_id) item_id, decode(:P_BREAK_ID,1,'5', msi.description ) item_desc, msi.lot_control_code item_lot_cntl, decode(:P_BREAK_ID, 2,'6', mmt.transaction_date) txn_date, mmt.revision rev, decode(:P_BREAK_ID, 3,'7' , mtxt.transaction_type_name) txn_type_name, mmt.transaction_source_id txn_source_id, DECODE (mmt.transaction_source_type_id, 1,poh.segment1, --2,null, 2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'), --3,null, 3,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_glcc_flexdata', 'SQLGL', 'GL#', GLC.CHART_OF_ACCOUNTS_ID, NULL, GLC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'), 4,mtrh.request_number , 5,wipe.wip_entity_name, --6,null, 6,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_mdsp_flexdata', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'), 7,porh.segment1, --8,&P_MKTS_FLEXDATA, 8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'), 9,cch.cycle_count_header_name, 10,mpi.physical_inventory_name, 11,cupd.description, --12,&P_MKTS_FLEXDATA, 12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'), mmt.transaction_source_name) txn_source_data, decode(:P_BREAK_ID,6,'8', mmt.subinventory_code) subinv, DECODE (:P_rpt_uom, 1,ROUND(NVL(mmt.transaction_quantity,0),:P_QTY_PRECISION), 2,ROUND(NVL(mmt.primary_quantity,0),:P_QTY_PRECISION), ROUND(NVL(mmt.transaction_quantity,0),:P_QTY_PRECISION)) rpt_qty, DECODE (:P_rpt_uom, 1,NVL(mmt.transaction_quantity,0), 2,NVL(mmt.primary_quantity,0), NVL(mmt.transaction_quantity,0)) unrounded_qty, NVL(mmt.actual_cost,0) * NVL(mmt.primary_quantity,0) unrounded_tot_cost, decode(:P_rpt_uom, 1,mmt.transaction_uom, 2,msi.primary_uom_code ) uom, ROUND((NVL(mmt.actual_cost,0) * NVL(mmt.primary_quantity,0)),:C_std_precision) extd_cost, decode(:P_BREAK_ID,7,NULL, mcat.category_id ) catg_id, decode(:P_BREAK_ID,7,NULL, &P_CATG_FLEXDATA ) catg_flexdata, mmt.transaction_id txn_id, mmt.transaction_set_id txn_set_id, mmt.created_by txn_create_by1, fndu.user_name txn_user_name, mmt.creation_date txn_create_dt, mmt.transaction_reference txn_ref, decode(:P_BREAK_ID,5,NULL, mtr.reason_name) reason_name, mmt.locator_id locator_id, --null loc_flexdata, decode(mil.inventory_location_id,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_locator_value', 'INV', 'MTLL', 101, mil.ORGANIZATION_ID, mil.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE')) loc_flexdata, mmt.transaction_source_type_id txn_src_type_id, mut.serial_number serial_number, msi.lot_control_code item_lot_control, decode(:P_BREAK_ID, 2, nvl(&P_ITEM_FLEXDATA, ' '), 1,nvl(to_number(to_char(mmt.transaction_date,'J')),0), 4,nvl( DECODE (mmt.transaction_source_type_id, 1,poh.segment1, --2,&P_MKTS_FLEXDATA, 2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'), 3,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_glcc_flexdata', 'SQLGL', 'GL#', GLC.CHART_OF_ACCOUNTS_ID, NULL, GLC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'), 5,wipe.wip_entity_name, 6,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_mdsp_flexdata', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'), 7,porh.segment1, --8,&P_MKTS_FLEXDATA, 8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'), 9,cch.cycle_count_header_name, 10,mpi.physical_inventory_name, 11,cupd.description, --12,&P_MKTS_FLEXDATA, 12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'), mmt.transaction_source_name), ' '), 3,nvl( DECODE (mmt.transaction_source_type_id, 1,poh.segment1, --2,&P_MKTS_FLEXDATA, 2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'), 3,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_glcc_flexdata', 'SQLGL', 'GL#', GLC.CHART_OF_ACCOUNTS_ID, NULL, GLC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'), 5,wipe.wip_entity_name, 6,fnd_flex_xml_publisher_apis.process_kff_combination_1('p_mdsp_flexdata', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'), 7,porh.segment1, --8,&P_MKTS_FLEXDATA, 8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'), 9,cch.cycle_count_header_name, 10,mpi.physical_inventory_name, 11,cupd.description, --12,&P_MKTS_FLEXDATA, 12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'), mmt.transaction_source_name) ,' '), 5,nvl(to_number(to_char(mmt.transaction_date,'J')),0), 6,nvl(to_number(to_char(mmt.transaction_date,'J')),0), 7,nvl(to_number(to_char(mmt.transaction_date,'J')),0), NULL ) Break2, decode(:P_BREAK_ID, 2, nvl(mmt.revision, ' '), 1,nvl(mmt.revision, ' '), 4,nvl(to_number(to_char(mmt.transaction_date,'J')),0), 3,nvl(to_number(to_char(mmt.transaction_date,'J')),0), 5,nvl(&P_ITEM_FLEXDATA, ' '), 6,nvl(&P_ITEM_FLEXDATA, ' '), 7,nvl(&P_ITEM_FLEXDATA, ' '), NULL) Break3, decode(:P_BREAK_ID, 2, ' ', 1, ' ', 3,nvl(&P_ITEM_FLEXDATA, ' '), 4,nvl(&P_ITEM_FLEXDATA, ' '), 5, ' ', 6, ' ', 7, ' ', NULL) Break4, DECODE(msi.tracking_quantity_ind,'PS',ROUND(NVL(mmt.secondary_transaction_quantity,0),:P_QTY_PRECISION)) sec_qty, DECODE(msi.tracking_quantity_ind,'PS',mmt.secondary_uom_code) sec_uom, INV_INVTRREG_XMLP_PKG.c_break_date_valueformula( decode(:P_BREAK_ID, 1, nvl(null, ''), 2,nvl(to_number(to_char(mmt.transaction_date,'J')),0), 3,nvl(&C_type_break_column,''), 4,nvl(&C_type_break_column,''), 5,nvl(mtr.reason_name,''), 6,nvl(mmt.subinventory_code,''), 7,nvl(null, ''), NULL ) )C_break_date_value, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_break_item_value', 'INV', 'MSTK', 101, msi.ORGANIZATION_ID, msi.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_break_item_value, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_break_catg_value', 'INV', 'MCAT', mcat.STRUCTURE_ID, NULL, mcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_break_catg_value, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value', 'INV', 'MSTK', 101, msi.ORGANIZATION_ID, msi.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_item_value, Decode(1,Decode(mmt.transaction_source_type_id,2,1,8,1,12,1,16,1,2),fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),null) C_txn_src_value, INV_INVTRREG_XMLP_PKG.calc_unit_cost(DECODE ( :P_rpt_uom , 1 , NVL ( mmt.transaction_quantity , 0 ) , 2 , NVL ( mmt.primary_quantity , 0 ) , NVL ( mmt.transaction_quantity , 0 ) ), NVL ( mmt.actual_cost , 0 ) * NVL ( mmt.primary_quantity , 0 ), :C_ext_precision) C_unit_cost, decode(mil.inventory_location_id,null,null,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_locator_value', 'INV', 'MTLL', 101, mil.ORGANIZATION_ID, mil.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE')) C_locator_value, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_catg_value', 'INV', 'MCAT', mcat.STRUCTURE_ID, NULL, mcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_catg_value FROM MTL_SYSTEM_ITEMS_VL msi, MTL_TRANSACTION_TYPES mtxt, MTL_TRANSACTION_REASONS mtr, MTL_ITEM_CATEGORIES micat, MTL_ITEM_LOCATIONS mil, MTL_CATEGORIES mcat, MTL_MATERIAL_TRANSACTIONS mmt, FND_USER fndu, PO_HEADERS_ALL poh, MTL_SALES_ORDERS mkts, GL_CODE_COMBINATIONS glc, WIP_ENTITIES wipe, MTL_GENERIC_DISPOSITIONS mdsp, CST_COST_UPDATES cupd, MTL_CYCLE_COUNT_HEADERS cch, MTL_PHYSICAL_INVENTORIES mpi, PO_REQUISITION_HEADERS_ALL porh, MTL_UNIT_TRANSACTIONS mut, MTL_TXN_REQUEST_HEADERS mtrh &C_type_break_from WHERE &P_CATG_WHERE and &P_ITEM_WHERE and mmt.reason_id = mtr.reason_id (+) and mmt.created_by = fndu.user_id (+) and mmt.transaction_type_id = mtxt.transaction_type_id (+) and mmt.locator_id = mil.inventory_location_id (+) and mmt.organization_id = mil.organization_id(+) and mmt.transaction_source_type_id != 11 and (mmt.transaction_source_type_id != 13 or mmt.transaction_type_id != 80) and mmt.transaction_id = mut.transaction_id (+) &C_reason_where &C_txn_type_where &C_subinv_where &C_type_break_where &C_source_type_where and ( (:P_TXN_SOURCE_TYPE_ID=2 and &P_source_where1) or (:P_TXN_SOURCE_TYPE_ID=3 and &P_source_where3) or (:P_TXN_SOURCE_TYPE_ID=6 and &P_source_where2) or (:P_TXN_SOURCE_TYPE_ID=8 and &P_source_where1) or (:P_TXN_SOURCE_TYPE_ID=12 and &P_source_where1) or (:P_TXN_SOURCE_TYPE_ID not in (2,3,6,8,12) ) or (:P_TXN_SOURCE_TYPE_ID is null) ) &C_source_where and mmt.transaction_source_id = poh.po_header_id (+) and mmt.transaction_source_id = mkts.sales_order_id (+) and mmt.transaction_source_id = glc.code_combination_id (+) and mmt.transaction_source_id = wipe.wip_entity_id (+) and mmt.organization_id = wipe.organization_id (+) and mmt.transaction_source_id = mdsp.disposition_id (+) and mmt.transaction_source_id = porh.requisition_header_id (+) and mmt.transaction_source_id = cch.cycle_count_header_id (+) and mmt.transaction_source_id = mtrh.header_id(+) and mmt.organization_id = cch.organization_id (+) and mmt.transaction_source_id = mpi.physical_inventory_id (+) and mmt.organization_id = mpi.organization_id (+) and mmt.transaction_source_id = cupd.cost_update_id (+) and mmt.organization_id = cupd.organization_id (+) and mmt.inventory_item_id = msi.inventory_item_id and mmt.organization_id = msi.organization_id and micat.category_id = mcat.category_id and mmt.inventory_item_id = micat.inventory_item_id and micat.category_set_id = :P_CATG_SET_ID and mmt.organization_id = micat.organization_id and (trunc(mmt.transaction_date) --between (:P_TXN_DATE_LO) and (:P_TXN_DATE_HI) ) between (:LP_TXN_DATE_LO) and (:LP_TXN_DATE_HI) ) and mmt.organization_id = :P_ORG and NVL(logical_transaction,2) = 2 &C_order_by |