INV Vendor lot trace
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Vendor lot trace report
Application: Inventory
Source: Vendor lot trace report (XML)
Short Name: INVTRVLT_XML
DB package: INV_INVTRVLT_XMLP_PKG
Description: Vendor lot trace report
Application: Inventory
Source: Vendor lot trace report (XML)
Short Name: INVTRVLT_XML
DB package: INV_INVTRVLT_XMLP_PKG
SELECT NVL( MTLN.SUPPLIER_LOT_NUMBER , 'None') VENDOR_LOT2, --17153772 NVL( MTLN.LOT_NUMBER, 'None') TXN_LOT_NUM2, null ITEM_FLEX2, MSI.INVENTORY_ITEM_ID ITEM_ID2, MTLN.TRANSACTION_ID TXN_ID2, MMT.REVISION ITEM_REV2, MSI.DESCRIPTION ITEM_DESC2, MTT.TRANSACTION_TYPE_NAME TXN_TYPE_NAME2, MTLN.TRANSACTION_SOURCE_ID TXN_SRC_ID2, MTLN.TRANSACTION_SOURCE_TYPE_ID TXN_SRC_TYPE_ID2, DECODE(MTLN.TRANSACTION_SOURCE_TYPE_ID, 1,POH.SEGMENT1, 2,null, 3,null, 4,NULL, 5,WIP.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, 13,MTLN.TRANSACTION_SOURCE_NAME, MTLN.TRANSACTION_SOURCE_NAME) TXN_SOURCE_DATA2, MMT.TRANSACTION_DATE TXN_DATE2, MTLN.ORGANIZATION_ID ORG_ID2, MMT.TRANSACTION_UOM TXN_UOM2, ROUND(NVL(MTLN.PRIMARY_QUANTITY,0), :P_qty_precision) TXN_LOT_QTY2, DECODE(MSI.TRACKING_QUANTITY_IND, 'PS', MMT.SECONDARY_UOM_CODE, NULL) SEC_TXN_UOM2, DECODE(MSI.TRACKING_QUANTITY_IND, 'PS', ROUND(NVL(MTLN.SECONDARY_TRANSACTION_QUANTITY,0),:P_qty_precision), NULL) SEC_TXN_LOT_QTY2, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value2', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ITEM_VALUE2, decode(MTLN.TRANSACTION_SOURCE_TYPE_ID,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source_value2_1', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),3,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source_value2_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_source_value2_3', 'INV', 'MDSP', 101, MDSP.ORGANIZATION_ID, MDSP.DISPOSITION_ID, 'ALL', 'Y', 'VALUE'),8,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source_value2_1', 'INV', 'MKTS', 101, NULL, MKTS.SALES_ORDER_ID, 'ALL', 'Y', 'VALUE'),12,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_source_value2_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,WIP.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, 13,MTLN.TRANSACTION_SOURCE_NAME, MTLN.TRANSACTION_SOURCE_NAME)) C_SOURCE_VALUE2 --&C_source_value2 C_SOURCE_VALUE2 FROM MTL_SYSTEM_ITEMS MSI, MTL_TRANSACTION_LOT_NUMBERS MTLN, MTL_MATERIAL_TRANSACTIONS MMT, PO_HEADERS_ALL POH, PO_REQUISITION_HEADERS_ALL PORH, MTL_SALES_ORDERS MKTS, GL_CODE_COMBINATIONS GLCC, WIP_ENTITIES WIP, MTL_GENERIC_DISPOSITIONS MDSP, CST_COST_UPDATES CUPD, MTL_CYCLE_COUNT_HEADERS CCH, MTL_TRANSACTION_TYPES MTT, MTL_PHYSICAL_INVENTORIES MPI WHERE MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID (+) AND &P_ITEM_WHERE AND MTLN.ORGANIZATION_ID = :P_ORG AND MTLN.ORGANIZATION_ID = MMT.ORGANIZATION_ID (+) AND MTLN.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MTLN.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MTLN.TRANSACTION_ID = MMT.TRANSACTION_ID --17153772 &C_LOT_WHERE AND ( :P_VEND_LOT_NUMBER IS NULL OR MTLN.SUPPLIER_LOT_NUMBER = :P_VEND_LOT_NUMBER ) AND trunc(MTLN.TRANSACTION_DATE) >= :P_START_DATE ---17304210 AND trunc(MTLN.TRANSACTION_DATE) <= :P_END_DATE_1 --17304210 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 = WIP.WIP_ENTITY_ID (+) AND MTLN.ORGANIZATION_ID = WIP.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 (+) ) ORDER BY mtln.supplier_lot_number , mtln.lot_number , &P_ITEM_ORDER_BY, mmt.revision, mtln.transaction_date, mtln.transaction_id |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Sort By |
|
LOV Oracle | |
Dates From |
|
Date | |
To |
|
Date | |
Lot Numbers From |
|
LOV Oracle | |
To 2 |
|
LOV Oracle | |
Vendor Lot Number |
|
Char | |
Items From |
|
Char | |
To 3 |
|
Char |