XXAPR ANNEXURE4 Report

➜ Back to Library
Description
Categories: Custom Reports APL
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), manufa ...  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.   more 
   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 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
XXEUR ANNEXURE4 Report (Revised) AI INV On Hand Qty Value AI INV Period Close Details TESTEP BOM INV Material Transactions2 CAC Inventory Lot and Locator OPM Value (Period-End) APL Lot Cost Trend CAC Purchase Price Variance CAC Calculate Average Item Costs
Blitz Report™