INV Lot transaction register
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Lot transaction register
Application: Inventory
Source: Lot transaction register (XML)
Short Name: INVTRLNT_XML
DB package: INV_INVTRLNT_XMLP_PKG
Description: Lot transaction register
Application: Inventory
Source: Lot transaction register (XML)
Short Name: INVTRLNT_XML
DB package: INV_INVTRLNT_XMLP_PKG
Run
INV Lot transaction register and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
SELECT null break_column , msi.inventory_item_id break_item_id, msi.description break_item_desc, mtln.transaction_date txn_date, mtln.lot_number lot_number , mmt.vendor_lot_number vend_lot, mmt.revision rev, mtxt.transaction_type_name txn_type_name, mtln.transaction_source_id txn_source_id, DECODE (mtln.transaction_source_type_id, 1,poh.segment1, 2,null, 3,null, 5,wipe.wip_entity_name, 6,null, 7,porh.segment1, 8,&P_MKTS_FLEXDATA, 9,cch.cycle_count_header_name, 10,mpi.physical_inventory_name, 11,cupd.description, 12,&P_MKTS_FLEXDATA, mtln.transaction_source_name) txn_source_data, mmt.subinventory_code subinv, sum(DECODE (:P_rpt_uom, 1,ROUND(NVL(mtln.transaction_quantity,0),:P_QTY_PRECISION), 2,ROUND(NVL(mtln.primary_quantity,0),:P_QTY_PRECISION), ROUND(NVL(mtln.transaction_quantity,0),:P_QTY_PRECISION))) rpt_qty, DECODE (:P_rpt_uom, 1,mmt.transaction_uom, 2,msi.primary_uom_code, mmt.transaction_uom) uom, sum(ROUND((round(NVL(mmt.actual_cost,0),:C_ext_precision) * round(NVL(mtln.primary_quantity,0),:P_QTY_PRECISION)),:C_std_precision)) extd_cost, mcat.category_id catg_id, null catg_flexdata, mtln.transaction_id txn_id, mmt.transaction_set_id txn_set_id, mtln.created_by txn_create_by1, fndu.user_name txn_user_name, trunc(mtln.creation_date) txn_create_dt, mmt.transaction_reference txn_ref, mtr.reason_name reason_name, mmt.locator_id locator_id, null loc_flexdata, mtln.transaction_source_type_id txn_src_type_id, SUM(ROUND(NVL(mtln.secondary_transaction_quantity,0), :P_QTY_PRECISION)) sec_qty, msi.secondary_uom_code , mtln.grade_code, 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, --&C_txn_src_value C_txn_src_value, decode(mtln.transaction_source_type_id,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),3,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_2', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),6,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_3', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'),8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),DECODE (mtln.transaction_source_type_id, 1,poh.segment1, 2,null, 3,null, 5,wipe.wip_entity_name, 6,null, 7,porh.segment1, 8,&P_MKTS_FLEXDATA, 9,cch.cycle_count_header_name, 10,mpi.physical_inventory_name, 11,cupd.description, 12,&P_MKTS_FLEXDATA, mtln.transaction_source_name)) C_txn_src_value, INV_INVTRLNT_XMLP_PKG.c_secondary_uomformula(msi.inventory_item_id, SUM ( ROUND ( NVL ( mtln.secondary_transaction_quantity , 0 ) , :P_QTY_PRECISION ) ), msi.secondary_uom_code) C_secondary_uom, INV_INVTRLNT_XMLP_PKG.calc_unit_cost(sum ( DECODE ( :P_rpt_uom , 1 , ROUND ( NVL ( mtln.transaction_quantity , 0 ) , :P_QTY_PRECISION ) , 2 , ROUND ( NVL ( mtln.primary_quantity , 0 ) , :P_QTY_PRECISION ) , ROUND ( NVL ( mtln.transaction_quantity , 0 ) , :P_QTY_PRECISION ) ) ), sum ( ROUND ( ( round ( NVL ( mmt.actual_cost , 0 ) , :C_ext_precision ) * round ( NVL ( mtln.primary_quantity , 0 ) , :P_QTY_PRECISION ) ) , :C_std_precision ) ), :C_ext_precision) C_unit_cost, 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, INV_INVTRLNT_XMLP_PKG.CP_sec_qty_p CP_sec_qty FROM MTL_TRANSACTION_LOT_NUMBERS mtln, MTL_SYSTEM_ITEMS msi, MTL_MATERIAL_TRANSACTIONS mmt, MTL_TRANSACTION_TYPES mtxt, MTL_TRANSACTION_REASONS mtr, MTL_ITEM_CATEGORIES micat, MTL_ITEM_LOCATIONS mil, MTL_CATEGORIES mcat, FND_USER fndu, PO_HEADERS_ALL poh, MTL_SALES_ORDERS mkts, GL_CODE_COMBINATIONS glcc, 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 WHERE &P_CATG_WHERE AND &P_ITEM_WHERE and mmt.reason_id = mtr.reason_id (+) and mtln.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 mtln.transaction_source_type_id != 11 &C_lot_where &C_txn_type_where &C_subinv_where &C_reason_where &C_txn_source_type_where &C_source_where and mtln.transaction_source_id = poh.po_header_id (+) and mtln.transaction_source_id = mkts.sales_order_id (+) and mtln.transaction_source_id = glcc.code_combination_id (+) and mtln.transaction_source_id = wipe.wip_entity_id (+) and mtln.organization_id = wipe.organization_id (+) and mtln.transaction_source_id = mdsp.disposition_id (+) and mtln.transaction_source_id = porh.requisition_header_id (+) and mtln.transaction_source_id = cch.cycle_count_header_id (+) and mtln.organization_id = cch.organization_id (+) and mtln.transaction_source_id = mpi.physical_inventory_id (+) and mtln.organization_id = mpi.organization_id (+) and mtln.transaction_source_id = cupd.cost_update_id (+) and mtln.organization_id = cupd.organization_id (+) and mtln.inventory_item_id = msi.inventory_item_id and mtln.organization_id = msi.organization_id and micat.category_id = mcat.category_id and mtln.inventory_item_id = micat.inventory_item_id and micat.category_set_id = :P_CATG_SET_ID and mtln.organization_id = micat.organization_id and mtln.transaction_id = mmt.transaction_id and (mtln.transaction_date) >= :P_TXN_DATE_LO and (mtln.transaction_date) <= :P_TXN_DATE_HI +1-1/(24*3600) and mtln.organization_id = :P_ORG GROUP BY msi.inventory_item_id, msi.description , mtln.transaction_date , mtln.lot_number , mmt.vendor_lot_number , mmt.revision , mtxt.transaction_type_name , mtln.transaction_source_id , DECODE (mtln.transaction_source_type_id, 1,poh.segment1, 2,null, 3,null, 5,wipe.wip_entity_name, 6,null, 7,porh.segment1, 8,(mkts.segment1||mkts.segment2||mkts.segment3||mkts.segment4||mkts.segment5||mkts.segment6||mkts.segment7||mkts.segment8||mkts.segment9||mkts.segment10||mkts.segment11||mkts.segment12||mkts.segment13||mkts.segment14||mkts.segment15||mkts.segment16||mkts.segment17||mkts.segment18||mkts.segment19||mkts.segment20), 9,cch.cycle_count_header_name, 10,mpi.physical_inventory_name, 11,cupd.description, 12,(mkts.segment1||mkts.segment2||mkts.segment3||mkts.segment4||mkts.segment5||mkts.segment6||mkts.segment7||mkts.segment8||mkts.segment9||mkts.segment10||mkts.segment11||mkts.segment12||mkts.segment13||mkts.segment14||mkts.segment15||mkts.segment16||mkts.segment17||mkts.segment18||mkts.segment19||mkts.segment20), mtln.transaction_source_name) , mmt.subinventory_code , DECODE (:P_rpt_uom, 1,mmt.transaction_uom, 2,msi.primary_uom_code, mmt.transaction_uom) , mcat.category_id , null , mtln.transaction_id , mmt.transaction_set_id , mtln.created_by , fndu.user_name , trunc(mtln.creation_date) , mmt.transaction_reference , mtr.reason_name , mmt.locator_id , mtln.transaction_source_type_id, msi.secondary_uom_code , mtln.grade_code, 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'), decode(mtln.transaction_source_type_id,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),3,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_2', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),6,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_3', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'),8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_txn_src_value_1', 'INV', 'MKTS', 101, NULL, mkts.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),DECODE (mtln.transaction_source_type_id, 1,poh.segment1, 2,null, 3,null, 5,wipe.wip_entity_name, 6,null, 7,porh.segment1, 8,&P_MKTS_FLEXDATA, 9,cch.cycle_count_header_name, 10,mpi.physical_inventory_name, 11,cupd.description, 12,&P_MKTS_FLEXDATA, mtln.transaction_source_name)), 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'), fnd_flex_xml_publisher_apis.process_kff_combination_1('c_catg_value', 'INV', 'MCAT', mcat.STRUCTURE_ID, NULL, mcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') order by 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'),msi.inventory_item_id,msi.secondary_uom_code,mtln.grade_code,mtln.transaction_id /*ORDER BY mtln.lot_number, mtln.transaction_date, mtln.transaction_id*/ |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Unit of Measure |
|
LOV Oracle | |
Transaction Dates From |
|
Date | |
To |
|
Date | |
Audit Detail |
|
LOV Oracle | |
Reason Detail |
|
LOV Oracle | |
Location Detail |
|
LOV Oracle | |
Category Detail |
|
LOV Oracle | |
Serial Number Detail |
|
LOV Oracle | |
Lot Numbers From |
|
LOV Oracle | |
To 2 |
|
LOV Oracle | |
Items From |
|
Char | |
To 3 |
|
Char | |
Transaction Types From |
|