ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 2EFF3F814ADD422C90F222F10405434FY SELECT '''' || MSIB.SEGMENT1 ITEM, '''' || XIPCQ.LOT_NUMBER LOT_NUMBER, MSIB.DESCRIPTION, XIPCQ.ORGANIZATION_CODE, XXEUR_XXEURANX4RRW_PKG.INVOICE_NUMS (XIPCQ.ORGANIZATION_ID, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.LOT_NUMBER) INVOICE_NUMS, XXEUR_XXEURANX4RRW_PKG.INVOICE_DATES (XIPCQ.ORGANIZATION_ID, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.LOT_NUMBER) INVOICE_DATES, XXEUR_XXEURANX4RRW_PKG.PURCHASE_DATE (XIPCQ.ORGANIZATION_ID, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.LOT_NUMBER) PURCHASE_DATE, MSIB.PRIMARY_UOM_CODE UOM, (SELECT MDEV.ELEMENT_VALUE FROM MTL_DESCR_ELEMENT_VALUES MDEV WHERE MDEV.INVENTORY_ITEM_ID = XIPCQ.INVENTORY_ITEM_ID AND MDEV.ELEMENT_NAME = 'PACK SIZE' AND ROWNUM = 1) PACK_SIZE, (SELECT GL.CURRENCY_CODE FROM GL_LEDGERS GL WHERE GL.LEDGER_ID = :ledger_id) CURR, NVL (SUM (CLSG_QTY), 0) CLSG_QTY, NVL (AVG (ITEM_COST), 0) ITEM_COST, NVL (SUM (ITEM_VALUE), 0) ITEM_VALUE, /*XXEUR_XXEURANX4RRW_PKG.SUPPLIER (XIPCQ.ORGANIZATION_ID, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.LOT_NUMBER) SUPPLIER, XXEUR_XXEURANX4RRW_PKG.SUPPLIER_TYPE (XIPCQ.ORGANIZATION_ID, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.LOT_NUMBER) SUPPLIER_TYPE,*/ NVL((select MDEV.element_value from mtl_descr_element_values MDEV where MDEV.inventory_item_id = XIPCQ.inventory_item_id and MDEV.element_name = 'SUPPLIER / UNIT' and MDEV.element_value IS NOT NULL and rownum = 1), XXEUR_XXEURANX4RRW_PKG.SUPPLIER (XIPCQ.ORGANIZATION_ID, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.LOT_NUMBER)) SUPPLIER, NVL((select APS.vendor_type_lookup_code from mtl_descr_element_values MDEV, ap_suppliers APS where MDEV.inventory_item_id = XIPCQ.inventory_item_id and MDEV.element_name = 'SUPPLIER / UNIT' and MDEV.element_value IS NOT NULL and APS.vendor_name = MDEV.element_value and rownum = 1), XXEUR_XXEURANX4RRW_PKG.SUPPLIER_TYPE (XIPCQ.ORGANIZATION_ID, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.LOT_NUMBER)) SUPPLIER_TYPE, MLN.ATTRIBUTE5 MFG_DATE, ' ' || TO_CHAR (MLN.EXPIRATION_DATE, 'DD/MM/YYYY') LOT_EXPIRY_DATE, CASE WHEN (TRUNC(MLN.EXPIRATION_DATE) - TRUNC(SYSDATE)) BETWEEN 184 AND 364 THEN SUM (CLSG_QTY) ELSE 0 END LOW_SHELF_LIFE_QTY, CASE WHEN (TRUNC(MLN.EXPIRATION_DATE) - TRUNC(SYSDATE)) BETWEEN 184 AND 364 THEN NVL ( (SUM (ITEM_VALUE) / 2), 0) ELSE 0 END LOW_SHELF_LIFE_VALUE, CASE WHEN (TRUNC(MLN.EXPIRATION_DATE) - TRUNC(SYSDATE)) < 183 THEN SUM (CLSG_QTY) ELSE 0 END EXPIRY_GOODS_QTY, (NVL (SUM (ITEM_VALUE), 0) - CASE WHEN (TRUNC(MLN.EXPIRATION_DATE) - TRUNC(SYSDATE)) BETWEEN 184 AND 264 THEN NVL ( (SUM (ITEM_VALUE) / 2), 0) ELSE 0 END) BAL_VALUE, /*(SELECT CSTT.NEW_COST FROM CST_CG_COST_HISTORY_V CSTT WHERE CSTT.ORGANIZATION_ID = XIPCQ.ORGANIZATION_ID AND CSTT.INVENTORY_ITEM_ID = XIPCQ.INVENTORY_ITEM_ID AND TRUNC (CSTT.TRANSACTION_COSTED_DATE) = TRUNC (XIPCQ.ROLLBACK_DATE) AND (CSTT.TRANSACTION_COSTED_DATE, CSTT.TRANSACTION_ID) IN (SELECT MAX (CSTTM.TRANSACTION_COSTED_DATE), MAX (CSTTM.TRANSACTION_ID) FROM CST_CG_COST_HISTORY_V CSTTM WHERE CSTTM.ORGANIZATION_ID = XIPCQ.ORGANIZATION_ID AND CSTTM.INVENTORY_ITEM_ID = XIPCQ.INVENTORY_ITEM_ID AND TRUNC (CSTTM.TRANSACTION_COSTED_DATE) = TRUNC (XIPCQ.ROLLBACK_DATE))) */ 0 CST_ITEM_COST ,(SELECT mdev.element_value FROM mtl_descr_element_values mdev WHERE mdev.inventory_item_id = XIPCQ.inventory_item_id AND mdev.element_name = 'DOSAGE FORM' GROUP BY MDEV.ELEMENT_VALUE ) DOSAGE_FORM ,CASE WHEN :ledger_id = 2223 THEN (SELECT mdev.element_value FROM mtl_descr_element_values mdev WHERE mdev.inventory_item_id = XIPCQ.inventory_item_id AND mdev.element_name = 'DOSAGE ROUTE' GROUP BY MDEV.ELEMENT_VALUE ) ELSE (SELECT mdev.element_value FROM mtl_descr_element_values mdev WHERE mdev.inventory_item_id = XIPCQ.inventory_item_id AND mdev.element_name = 'DOSAGE ROUTE' GROUP BY MDEV.ELEMENT_VALUE ) END DOSAGE_ROUTE ,(SELECT mdev.element_value FROM mtl_descr_element_values mdev WHERE mdev.inventory_item_id = XIPCQ.inventory_item_id AND mdev.element_name = 'DOSAGE STRENGTH' GROUP BY MDEV.ELEMENT_VALUE ) DOSAGE_STRENGTH ,(SELECT mdev.element_value FROM mtl_descr_element_values mdev WHERE mdev.inventory_item_id = XIPCQ.inventory_item_id AND mdev.element_name = 'INN' GROUP BY MDEV.ELEMENT_VALUE ) INN ,NVL((SELECT mdev.element_value FROM mtl_descr_element_values mdev WHERE mdev.inventory_item_id = XIPCQ.inventory_item_id AND mdev.element_name = 'FDF MANUFACTURER' GROUP BY MDEV.ELEMENT_VALUE ),(XXEUR_XXEURANX4RRW_PKG.MFG_NAME(XIPCQ.ORGANIZATION_ID,XIPCQ.INVENTORY_ITEM_ID,XIPCQ.LOT_NUMBER))) FDF_MANUFACTURER ,(SELECT mdev.element_value FROM mtl_descr_element_values mdev WHERE mdev.inventory_item_id = XIPCQ.inventory_item_id AND mdev.element_name = 'PACK TYPE' GROUP BY MDEV.ELEMENT_VALUE ) PACK_TYPE FROM XXAPL.XXEUR_INV_PERIOD_CLSG_QTY_TEMP XIPCQ, MTL_SYSTEM_ITEMS_B MSIB, MTL_LOT_NUMBERS MLN WHERE 1=1 AND MSIB.ORGANIZATION_ID = XIPCQ.ORGANIZATION_ID AND MSIB.INVENTORY_ITEM_ID = XIPCQ.INVENTORY_ITEM_ID AND MLN.ORGANIZATION_ID = XIPCQ.ORGANIZATION_ID AND MLN.INVENTORY_ITEM_ID = XIPCQ.INVENTORY_ITEM_ID AND MLN.LOT_NUMBER = XIPCQ.LOT_NUMBER GROUP BY XIPCQ.REQUEST_ID, XIPCQ.ROLLBACK_DATE, XIPCQ.ORGANIZATION_ID, XIPCQ.ORGANIZATION_CODE, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.ITEM, XIPCQ.LOT_NUMBER, MSIB.SEGMENT1, MSIB.DESCRIPTION, MSIB.PRIMARY_UOM_CODE, MLN.ATTRIBUTE5, MLN.EXPIRATION_DATE HAVING NVL (SUM (CLSG_QTY), 0) != 0 ORDER BY MSIB.SEGMENT1, XIPCQ.LOT_NUMBER Parameter rework to Blitz conversion standards: REQUEST_ID converted from an embedded bind to a 1=1 anchor with SQL_TEXT; the MIS_PURPOSE self-referencing DECODE filter replaced by a Yes/No matching value parameter injecting the asset subinventory restriction; LEDGER_ID keeps its bind anchor (used in the currency lookup and the DOSAGE ROUTE ledger 2223 case expression) and gains a ledger LOV so users pick the ledger by name while the bind receives the ledger_id. US XXEUR ANNEXURE4 Report (Revised) Migrated from BI Publisher report XXEURANX4RR - "XXEUR ANNEXURE4 Report (Revised)" (XXAPL Custom Application). Lists lot level inventory closing stock (Annexure 4) as of a rollback period: item, lot number, organization, closing quantity, item cost and value, supplier and invoice details, item catalog attributes (pack size, dosage form, dosage route, dosage strength, INN, FDF manufacturer, pack type), manufacturing and lot expiry dates, and low shelf life and expiry goods quantities and values. The data is calculated by the wrapper program (XXEUR_XXEURANX4RRW_PKG.MAIN), which rolls back inventory balances to the selected period and stores them in table XXAPL.XXEUR_INV_PERIOD_CLSG_QTY_TEMP under its own concurrent request id. Enter that request id in the REQUEST_ID parameter. Note the wrapper deletes the temporary data when it completes, so it should be adjusted to submit this Blitz Report instead of the XML Publisher report, or to retain the data. The original EUROPEAN_FORMAT parameter only controlled number formatting in the BI Publisher layout and is not needed in Blitz Report, where formatting is handled by the Excel template. Custom Reports APL 1=1 :ledger_id 1 10 1=1 xipcq.request_id=:request_id Number Y US REQUEST_ID Concurrent request id of the wrapper program run that populated the closing stock data 2 20 :ledger_id LOV custom Y select gl.ledger_id id, gl.name value, gl.description description from gl_ledgers gl order by gl.name Y US LEDGER_ID Ledger used to derive the reporting currency. Select the ledger by name. 3 30 1=1 xipcq.asst_sub_inv=1 LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y US MIS_PURPOSE Yes restricts the output to asset subinventories