INV Move Order Pick Slip
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Move Order Pick List
Application: Inventory
Source: Move Order Pick Slip (XML)
Short Name: INVTOPKL_XML
DB package: INV_INVTOPKL_XMLP_PKG
Description: Move Order Pick List
Application: Inventory
Source: Move Order Pick Slip (XML)
Short Name: INVTOPKL_XML
DB package: INV_INVTOPKL_XMLP_PKG
Run
INV Move Order Pick Slip and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT MMT.PICK_SLIP_NUMBER , MTRH.REQUEST_NUMBER , MTRH.MOVE_ORDER_TYPE , MTRL.LINE_NUMBER , MTRL.LINE_ID , MTRL.TRANSACTION_SOURCE_TYPE_ID , MMT.TRANSACTION_ID , TO_NUMBER(NULL) PARENT_LINE_ID , MMT.INVENTORY_ITEM_ID , null C_ITEM_FLEXDAT , MSI.DESCRIPTION ITEM_DESCRIPTION , MMT.REVISION , MMT.SUBINVENTORY_CODE FR_SUBINV , DECODE(MMT.LOCATOR_ID, NULL, '', INV_PROJECT.GET_LOCATOR(MMT.LOCATOR_ID, :P_ORG_ID)) FR_LOCATOR , MMT.TRANSFER_SUBINVENTORY TO_SUBINV , DECODE(MMT.TRANSFER_LOCATOR_ID, NULL, '', INV_PROJECT.GET_LOCATOR(MMT.TRANSFER_LOCATOR_ID, :P_ORG_ID)) TO_LOCATOR , MTRL.PROJECT_ID , MTRL.TASK_ID , DECODE(MMT.DISTRIBUTION_ACCOUNT_ID, '', '', null) C_TO_ACCOUNT_FLEXDAT , FND.USER_NAME REQUESTER , MMT.TRANSACTION_REFERENCE REFERENCE , MMT.TRANSACTION_UOM TRANSACTION_UOM , ABS(MMT.TRANSACTION_QUANTITY) TRANSACTION_QTY , (SELECT LICENSE_PLATE_NUMBER FROM WMS_LICENSE_PLATE_NUMBERS WHERE LPN_ID = NVL(MMT.CONTENT_LPN_ID, MMT.LPN_ID)) PICK_FR_LPN , (SELECT LICENSE_PLATE_NUMBER FROM WMS_LICENSE_PLATE_NUMBERS WHERE LPN_ID = MMT.TRANSFER_LPN_ID) PICK_TO_LPN , 6 TASK_STATUS , (SELECT DROPPING_ORDER FROM MTL_SECONDARY_INVENTORIES WHERE SECONDARY_INVENTORY_NAME = MMT.TRANSFER_SUBINVENTORY AND ORGANIZATION_ID = MMT.ORGANIZATION_ID) SUB_DROPPING_ORDER , (SELECT DROPPING_ORDER FROM MTL_ITEM_LOCATIONS WHERE INVENTORY_LOCATION_ID = MMT.TRANSFER_LOCATOR_ID AND ORGANIZATION_ID = MMT.ORGANIZATION_ID) LOC_DROPPING_ORDER , MMT.SECONDARY_UOM_CODE SEC_UOM , ABS(MMT.SECONDARY_TRANSACTION_QUANTITY) SEC_TRANSACTION_QTY, fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CF_ITEM, DECODE(MMT.DISTRIBUTION_ACCOUNT_ID, '', '', fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_to_account', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) CF_TO_ACCOUNT, INV_INVTOPKL_XMLP_PKG.cf_sec_uomformula(MMT.INVENTORY_ITEM_ID, MMT.SECONDARY_UOM_CODE) CF_SEC_UOM, INV_INVTOPKL_XMLP_PKG.cf_sec_qtyformula(MMT.INVENTORY_ITEM_ID, ABS(MMT.SECONDARY_TRANSACTION_QUANTITY)) CF_SEC_QTY, INV_INVTOPKL_XMLP_PKG.cf_task_statusformula(6) CF_TASK_STATUS, INV_INVTOPKL_XMLP_PKG.cf_task_idformula(6, TO_NUMBER(NULL), MMT.TRANSACTION_ID) CF_TASK_ID, INV_INVTOPKL_XMLP_PKG.cf_project_numberformula(MTRL.PROJECT_ID) CF_PROJECT_NUMBER, INV_INVTOPKL_XMLP_PKG.cf_task_numberformula(MTRL.TASK_ID,MTRL.PROJECT_ID) CF_TASK_NUMBER, INV_INVTOPKL_XMLP_PKG.cf_wip_infoformula(LINE_ID, MTRH.MOVE_ORDER_TYPE, MTRL.TRANSACTION_SOURCE_TYPE_ID) CF_WIP_INFO, INV_INVTOPKL_XMLP_PKG.cf_so_infoformula(LINE_ID, MOVE_ORDER_TYPE) CF_SO_INFO, INV_INVTOPKL_XMLP_PKG.CP_SO_ORDER_NUMBER_p CP_SO_ORDER_NUMBER, INV_INVTOPKL_XMLP_PKG.CP_SO_LINE_NUMBER_p CP_SO_LINE_NUMBER, INV_INVTOPKL_XMLP_PKG.CP_SO_DELIVERY_NAME_p CP_SO_DELIVERY_NAME, INV_INVTOPKL_XMLP_PKG.CP_WIP_JOB_p CP_WIP_JOB, INV_INVTOPKL_XMLP_PKG.CP_WIP_DEPARTMENT_p CP_WIP_DEPARTMENT, INV_INVTOPKL_XMLP_PKG.CP_WIP_LINE_p CP_WIP_LINE, INV_INVTOPKL_XMLP_PKG.CP_WIP_ENTITY_TYPE_p CP_WIP_ENTITY_TYPE, INV_INVTOPKL_XMLP_PKG.CP_WIP_START_DATE_p CP_WIP_START_DATE, INV_INVTOPKL_XMLP_PKG.CP_WIP_OPERATION_p CP_WIP_OPERATION FROM MTL_MATERIAL_TRANSACTIONS MMT , MTL_TXN_REQUEST_HEADERS MTRH , MTL_TXN_REQUEST_LINES MTRL , MTL_SYSTEM_ITEMS_VL MSI , GL_CODE_COMBINATIONS GCC , FND_USER FND &P_FROM_MMT WHERE MMT.ORGANIZATION_ID = :P_ORG_ID AND MMT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID AND MTRL.HEADER_ID = MTRH.HEADER_ID AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID AND FND.USER_ID = MTRL.CREATED_BY AND GCC.CODE_COMBINATION_ID(+) = MMT.DISTRIBUTION_ACCOUNT_ID AND NVL(MMT.TRANSACTION_QUANTITY, 0) < 0 AND &P_WHERE_MMT UNION ALL SELECT (SELECT PICK_SLIP_NUMBER FROM MTL_MATERIAL_TRANSACTIONS_TEMP WHERE TRANSACTION_TEMP_ID = NVL(MMTT.PARENT_LINE_ID, MMTT.TRANSACTION_TEMP_ID) )PICK_SLIP_NUMBER , MTRH.REQUEST_NUMBER , MTRH.MOVE_ORDER_TYPE , MTRL.LINE_NUMBER , MTRL.LINE_ID , MTRL.TRANSACTION_SOURCE_TYPE_ID , MMTT.TRANSACTION_TEMP_ID TRANSACTION_ID , MMTT.PARENT_LINE_ID , MMTT.INVENTORY_ITEM_ID , null C_ITEM_FLEXDAT , MSI.DESCRIPTION ITEM_DESCRIPTION , MMTT.REVISION , MMTT.SUBINVENTORY_CODE FR_SUBINV , DECODE(MMTT.LOCATOR_ID, NULL, '', INV_PROJECT.GET_LOCATOR(MMTT.LOCATOR_ID, :P_ORG_ID)) FR_LOCATOR , MMTT.TRANSFER_SUBINVENTORY TO_SUBINV , DECODE(MMTT.TRANSFER_TO_LOCATION, NULL, '', INV_PROJECT.GET_LOCATOR(MMTT.TRANSFER_TO_LOCATION, :P_ORG_ID)) TO_LOCATOR , MTRL.PROJECT_ID , MTRL.TASK_ID , DECODE(MMTT.DISTRIBUTION_ACCOUNT_ID, '', '', null) C_TO_ACCOUNT_FLEXDAT , FND.USER_NAME REQUESTER , MMTT.TRANSACTION_REFERENCE REFERENCE , MMTT.TRANSACTION_UOM TRANSACTION_UOM , ABS(MMTT.TRANSACTION_QUANTITY) TRANSACTION_QTY , (SELECT LICENSE_PLATE_NUMBER FROM WMS_LICENSE_PLATE_NUMBERS WHERE LPN_ID = NVL(MMTT.CONTENT_LPN_ID, NVL(MMTT.LPN_ID, MMTT.ALLOCATED_LPN_ID))) , (SELECT LICENSE_PLATE_NUMBER FROM WMS_LICENSE_PLATE_NUMBERS WHERE LPN_ID = NVL(MMTT.TRANSFER_LPN_ID, MMTT.CARTONIZATION_ID)) , NVL((SELECT WDT.STATUS FROM WMS_DISPATCHED_TASKS WDT WHERE TRANSACTION_TEMP_ID = NVL(MMTT.PARENT_LINE_ID, MMTT.TRANSACTION_TEMP_ID)), MMTT.WMS_TASK_STATUS) TASK_STATUS , (SELECT DROPPING_ORDER FROM MTL_SECONDARY_INVENTORIES WHERE SECONDARY_INVENTORY_NAME = MMTT.TRANSFER_SUBINVENTORY AND ORGANIZATION_ID = MMTT.ORGANIZATION_ID) SUB_DROPPING_ORDER , (SELECT DROPPING_ORDER FROM MTL_ITEM_LOCATIONS WHERE INVENTORY_LOCATION_ID = MMTT.TRANSFER_TO_LOCATION AND ORGANIZATION_ID = MMTT.ORGANIZATION_ID) LOC_DROPPING_ORDER , MMTT.SECONDARY_UOM_CODE SEC_UOM , ABS(MMTT.SECONDARY_TRANSACTION_QUANTITY) SEC_TRANSACTION_QTY, fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CF_ITEM, DECODE(MMTT.DISTRIBUTION_ACCOUNT_ID, '', '', fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_to_account', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) CF_TO_ACCOUNT, INV_INVTOPKL_XMLP_PKG.cf_sec_uomformula(MMTT.INVENTORY_ITEM_ID, MMTT.SECONDARY_UOM_CODE) CF_SEC_UOM, INV_INVTOPKL_XMLP_PKG.cf_sec_qtyformula(MMTT.INVENTORY_ITEM_ID, ABS(MMTT.SECONDARY_TRANSACTION_QUANTITY)) CF_SEC_QTY, INV_INVTOPKL_XMLP_PKG.cf_task_statusformula(NVL((SELECT WDT.STATUS FROM WMS_DISPATCHED_TASKS WDT WHERE TRANSACTION_TEMP_ID = NVL(MMTT.PARENT_LINE_ID, MMTT.TRANSACTION_TEMP_ID)), MMTT.WMS_TASK_STATUS)) CF_TASK_STATUS, INV_INVTOPKL_XMLP_PKG.cf_task_idformula(NVL((SELECT WDT.STATUS FROM WMS_DISPATCHED_TASKS WDT WHERE TRANSACTION_TEMP_ID = NVL(MMTT.PARENT_LINE_ID, MMTT.TRANSACTION_TEMP_ID)), MMTT.WMS_TASK_STATUS), MMTT.PARENT_LINE_ID, MMTT.TRANSACTION_TEMP_ID) CF_TASK_ID, INV_INVTOPKL_XMLP_PKG.cf_project_numberformula(MTRL.PROJECT_ID) CF_PROJECT_NUMBER, INV_INVTOPKL_XMLP_PKG.cf_task_numberformula(MTRL.TASK_ID, MTRL.PROJECT_ID) CF_TASK_NUMBER, INV_INVTOPKL_XMLP_PKG.cf_wip_infoformula(MTRL.LINE_ID, MTRH.MOVE_ORDER_TYPE, MTRL.TRANSACTION_SOURCE_TYPE_ID) CF_WIP_INFO, INV_INVTOPKL_XMLP_PKG.cf_so_infoformula(MTRL.LINE_ID, MTRH.MOVE_ORDER_TYPE) CF_SO_INFO, INV_INVTOPKL_XMLP_PKG.CP_SO_ORDER_NUMBER_p CP_SO_ORDER_NUMBER, INV_INVTOPKL_XMLP_PKG.CP_SO_LINE_NUMBER_p CP_SO_LINE_NUMBER, INV_INVTOPKL_XMLP_PKG.CP_SO_DELIVERY_NAME_p CP_SO_DELIVERY_NAME, INV_INVTOPKL_XMLP_PKG.CP_WIP_JOB_p CP_WIP_JOB, INV_INVTOPKL_XMLP_PKG.CP_WIP_DEPARTMENT_p CP_WIP_DEPARTMENT, INV_INVTOPKL_XMLP_PKG.CP_WIP_LINE_p CP_WIP_LINE, INV_INVTOPKL_XMLP_PKG.CP_WIP_ENTITY_TYPE_p CP_WIP_ENTITY_TYPE, INV_INVTOPKL_XMLP_PKG.CP_WIP_START_DATE_p CP_WIP_START_DATE, INV_INVTOPKL_XMLP_PKG.CP_WIP_OPERATION_p CP_WIP_OPERATION FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT , MTL_TXN_REQUEST_HEADERS MTRH , MTL_TXN_REQUEST_LINES MTRL , MTL_SYSTEM_ITEMS_VL MSI , GL_CODE_COMBINATIONS GCC , FND_USER FND WHERE MMTT.ORGANIZATION_ID = :P_ORG_ID AND MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID AND MTRL.HEADER_ID = MTRH.HEADER_ID AND MSI.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = MMTT.ORGANIZATION_ID AND GCC.CODE_COMBINATION_ID(+) = MMTT.DISTRIBUTION_ACCOUNT_ID AND FND.USER_ID = MTRL.CREATED_BY AND NVL(MMTT.TRANSACTION_QUANTITY, 0) > 0 AND NOT EXISTS (SELECT 1 FROM MTL_MATERIAL_TRANSACTIONS_TEMP WHERE PARENT_LINE_ID = MMTT.TRANSACTION_TEMP_ID) AND NVL(MMTT.PARENT_LINE_ID, MMTT.TRANSACTION_TEMP_ID) IN (SELECT NVL(MMTT.PARENT_LINE_ID, MMTT.TRANSACTION_TEMP_ID) FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT , MTL_TXN_REQUEST_LINES MTRL , MTL_TXN_REQUEST_HEADERS MTRH &P_FROM_MMTT WHERE MMTT.ORGANIZATION_ID = :P_ORG_ID AND MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID AND MTRH.HEADER_ID = MTRL.HEADER_ID AND &P_WHERE_MMTT ) UNION ALL SELECT MTRL.PICK_SLIP_NUMBER , MTRH.REQUEST_NUMBER , MTRH.MOVE_ORDER_TYPE , MTRL.LINE_NUMBER , MTRL.LINE_ID , MTRL.TRANSACTION_SOURCE_TYPE_ID , -99999 AS TRANSACTION_ID , TO_NUMBER(NULL) PARENT_LINE_ID , MTRL.INVENTORY_ITEM_ID , null C_ITEM_FLEXDAT , MSI.DESCRIPTION , MTRL.REVISION , MTRL.FROM_SUBINVENTORY_CODE FR_SUBINV , DECODE(MTRL.FROM_LOCATOR_ID, NULL, '', INV_PROJECT.GET_LOCATOR(MTRL.FROM_LOCATOR_ID, :P_ORG_ID)) FR_LOCATOR , MTRL.TO_SUBINVENTORY_CODE TO_SUBINV , DECODE(MTRL.TO_LOCATOR_ID, NULL, '', INV_PROJECT.GET_LOCATOR(MTRL.TO_LOCATOR_ID, :P_ORG_ID)) TO_LOCATOR , MTRL.PROJECT_ID , MTRL.TASK_ID , DECODE(MTRL.TO_ACCOUNT_ID, '', '', null) C_TO_ACCOUNT_FLEXDAT , FND.USER_NAME REQUESTER , MTRL.REFERENCE REFERENCE , MTRL.UOM_CODE TRANSACTION_UOM , ABS(NVL(MTRL.QUANTITY, 0) - NVL(MTRL.QUANTITY_DETAILED, 0)) TRANSACTION_QTY , (SELECT LICENSE_PLATE_NUMBER FROM WMS_LICENSE_PLATE_NUMBERS WHERE LPN_ID = MTRL.LPN_ID) , TO_CHAR(NULL) , 0 TASK_STATUS , (SELECT DROPPING_ORDER FROM MTL_SECONDARY_INVENTORIES WHERE SECONDARY_INVENTORY_NAME = MTRL.TO_SUBINVENTORY_CODE AND ORGANIZATION_ID = MTRL.ORGANIZATION_ID) SUB_DROPPING_ORDER , (SELECT DROPPING_ORDER FROM MTL_ITEM_LOCATIONS WHERE INVENTORY_LOCATION_ID = MTRL.TO_LOCATOR_ID AND ORGANIZATION_ID = MTRL.ORGANIZATION_ID) LOC_DROPPING_ORDER , MTRL.SECONDARY_UOM_CODE SEC_UOM , ABS(NVL(MTRL.SECONDARY_QUANTITY, 0) - NVL(MTRL.SECONDARY_QUANTITY_DETAILED, 0)) SEC_TRANSACTION_QTY, fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CF_ITEM, DECODE(MTRL.TO_ACCOUNT_ID, '', '', fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_to_account', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')) CF_TO_ACCOUNT, INV_INVTOPKL_XMLP_PKG.cf_sec_uomformula(MTRL.INVENTORY_ITEM_ID, MTRL.SECONDARY_UOM_CODE) CF_SEC_UOM, INV_INVTOPKL_XMLP_PKG.cf_sec_qtyformula(MTRL.INVENTORY_ITEM_ID, ABS(NVL(MTRL.SECONDARY_QUANTITY, 0) - NVL(MTRL.SECONDARY_QUANTITY_DETAILED, 0))) CF_SEC_QTY, INV_INVTOPKL_XMLP_PKG.cf_task_statusformula(0) CF_TASK_STATUS, INV_INVTOPKL_XMLP_PKG.cf_task_idformula(0, TO_NUMBER(NULL), -99999) CF_TASK_ID, INV_INVTOPKL_XMLP_PKG.cf_project_numberformula(MTRL.PROJECT_ID) CF_PROJECT_NUMBER, INV_INVTOPKL_XMLP_PKG.cf_task_numberformula(MTRL.TASK_ID, MTRL.PROJECT_ID) CF_TASK_NUMBER, INV_INVTOPKL_XMLP_PKG.cf_wip_infoformula(LINE_ID, MOVE_ORDER_TYPE, TRANSACTION_SOURCE_TYPE_ID) CF_WIP_INFO, INV_INVTOPKL_XMLP_PKG.cf_so_infoformula(LINE_ID, MOVE_ORDER_TYPE) CF_SO_INFO, INV_INVTOPKL_XMLP_PKG.CP_SO_ORDER_NUMBER_p CP_SO_ORDER_NUMBER, INV_INVTOPKL_XMLP_PKG.CP_SO_LINE_NUMBER_p CP_SO_LINE_NUMBER, INV_INVTOPKL_XMLP_PKG.CP_SO_DELIVERY_NAME_p CP_SO_DELIVERY_NAME, INV_INVTOPKL_XMLP_PKG.CP_WIP_JOB_p CP_WIP_JOB, INV_INVTOPKL_XMLP_PKG.CP_WIP_DEPARTMENT_p CP_WIP_DEPARTMENT, INV_INVTOPKL_XMLP_PKG.CP_WIP_LINE_p CP_WIP_LINE, INV_INVTOPKL_XMLP_PKG.CP_WIP_ENTITY_TYPE_p CP_WIP_ENTITY_TYPE, INV_INVTOPKL_XMLP_PKG.CP_WIP_START_DATE_p CP_WIP_START_DATE, INV_INVTOPKL_XMLP_PKG.CP_WIP_OPERATION_p CP_WIP_OPERATION FROM MTL_TXN_REQUEST_HEADERS MTRH , MTL_TXN_REQUEST_LINES MTRL , MTL_SYSTEM_ITEMS_VL MSI , FND_USER FND , GL_CODE_COMBINATIONS GCC &P_FROM_MTRL WHERE MTRL.ORGANIZATION_ID = :P_ORG_ID AND MTRL.HEADER_ID = MTRH.HEADER_ID AND MSI.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = MTRL.ORGANIZATION_ID AND MTRL.CREATED_BY = FND.USER_ID AND NVL(MTRL.QUANTITY, 0) > NVL(MTRL.QUANTITY_DETAILED, 0) AND MTRL.LINE_STATUS IN (3, 7) AND MTRL.TO_ACCOUNT_ID = GCC.CODE_COMBINATION_ID(+) AND &P_WHERE_MTRL ORDER BY 1, 8, 29, 30, 27, 28, 2, 3, 4, 5, 6, 7, 9, 31, 11, 12, 13, 14, 15, 16, 17, 18, 32, 20, 21, 22, 23 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Move Order Number From |
|
LOV Oracle | |
To |
|
LOV Oracle | |
Pick Slip Number From |
|
LOV Oracle | |
To 2 |
|
LOV Oracle | |
Source Subinventory |
|
LOV Oracle | |
Source Locator |
|
Char | |
Destination Subinventory |
|
LOV Oracle | |
Destination Locator |
|
Char | |
Requester |
|
LOV Oracle | |
Date Required From |
|
Date | |
To 3 |
|
Date | |
Print Option |
|
LOV Oracle | |
Move Order Types |
|
LOV Oracle | |
Sales Order From |
|
LOV Oracle | |
To 4 |
|
LOV Oracle | |
Freight Carrier |
|
LOV Oracle | |
Customer |
|
LOV Oracle | |
Auto Allocate Approved lines |
|
LOV Oracle | |
Plan Tasks |
|
LOV Oracle | |
Pick Slip Grouping Rule |
|
LOV Oracle |