XXEUR ANNEXURE4 Report (Revised)

➜ Back to Library
Description
Categories: Custom Reports APL
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,  ...  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.   more 
  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 NameSQL textValidation
REQUEST_ID
xipcq.request_id=:request_id
Number
LEDGER_ID
 
LOV
MIS_PURPOSE
xipcq.asst_sub_inv=1
LOV Oracle
Download
Related Blitz Reports
XXAPR ANNEXURE4 Report AI INV On Hand Qty Value CAC Inventory Lot and Locator OPM Value (Period-End) AI INV Period Close Details TESTEP BOM CAC Purchase Price Variance INV Material Transactions2 CAC Inventory and Intransit Value (Period-End) - Discrete/OPM APL Lot Cost Trend
Blitz Report™