ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C36A508A2241485E844F88D7A3C5C579Y SELECT MSIB.SEGMENT1 ITEM, XIPCQ.LOT_NUMBER LOT_NUMBER, MSIB.DESCRIPTION, (select organization_name from org_organization_definitions where organization_code=XIPCQ.ORGANIZATION_CODE) ORGANIZATION_CODE , XXAPR_ANNEXURE4_PKG.INVOICE_NUMS (XIPCQ.ORGANIZATION_ID, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.LOT_NUMBER) INVOICE_NUMS, XXAPR_ANNEXURE4_PKG.INVOICE_DATES (XIPCQ.ORGANIZATION_ID, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.LOT_NUMBER) INVOICE_DATES, XXAPR_ANNEXURE4_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, 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), XXAPR_ANNEXURE4_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), XXAPR_ANNEXURE4_PKG.SUPPLIER_TYPE (XIPCQ.ORGANIZATION_ID, XIPCQ.INVENTORY_ITEM_ID, XIPCQ.LOT_NUMBER)) SUPPLIER_TYPE, --MLN.ATTRIBUTE5 MFG_DATE, to_char(XXAPL_CONVERT_DT_TO_CL_ZONE_DT(XIPCQ.ORIGINATION_DATE),'MM/DD/RRRR') MFG_DATE, /*v1.2 chnage date format 'DD-MON-YYYY' TO 'MM/DD/RRRR'*/ --v1.3 added timezone fun ' ' || TO_CHAR (XXAPL_CONVERT_DT_TO_CL_ZONE_DT(MLN.EXPIRATION_DATE), 'MM/DD/RRRR') LOT_EXPIRY_DATE, /*v1.2 chnage date format 'DD-MON-YYYY' TO 'MM/DD/RRRR'*/ --v1.3 added timezone fun 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, 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 ,(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 ),( XXAPR_ANNEXURE4_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, --trunc(((MLN.EXPIRATION_DATE-XIPCQ.ORIGINATION_DATE)-(trunc(sysdate)-XIPCQ.ORIGINATION_DATE))/30,2) REMAINING_SHELF_LIFE, trunc((trunc(MLN.EXPIRATION_DATE)- ROLLBACK_DATE)/30,2) REMAINING_SHELF_LIFE, trunc((trunc(MLN.EXPIRATION_DATE)-ROLLBACK_DATE),2) REMAINING_SHELF_DAY FROM XXAPL.XXAPR_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,XIPCQ.ORIGINATION_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 select list expressions) and gains a ledger LOV so users pick the ledger by name while the bind receives the ledger_id. US XXAPR ANNEXURE4 Report Migrated from BI Publisher report XXAPRANNEX4REPORT - "XXAPR ANNEXURE4 Report" (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 strength, INN, FDF manufacturer, pack type), manufacturing and lot expiry dates, low shelf life and expiry goods quantities and values, and remaining shelf life. The data is calculated by the wrapper program (XXAPR_ANNEXURE4_PKG.MAIN), which rolls back inventory balances to the selected period and stores them in table XXAPL.XXAPR_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