Oracle R12 Query to find self consumption of finished goods

SELECT   msi.segment1 itemcode,msi.description fg_descr, msi.primary_unit_of_measure fg_uom,
         ROUND(ABS((SELECT SUM (mmt1.transaction_quantity)
          FROM mtl_material_transactions mmt1
          WHERE trunc(mmt1.transaction_date) = :from_start_date
          AND mmt1.transaction_type_id = mtt.transaction_type_id
          AND mmt1.inventory_item_id = msi.inventory_item_id
          AND mmt1.organization_id = msi.organization_id)),3) prod_from_date,
          ROUND(ABS(SUM (mmt.transaction_quantity)),3) prod_date_range,
         ROUND((SELECT ABS(SUM (mmt1.transaction_quantity))
          FROM mtl_material_transactions mmt1
          WHERE 1=1
          AND mmt1.transaction_type_id = mtt.transaction_type_id
          AND mmt1.inventory_item_id = msi.inventory_item_id
          AND mmt1.organization_id = msi.organization_id
          AND mmt1.transaction_date BETWEEN
                   (SELECT start_date
                     FROM cm_cldr_dtl
                    WHERE calendar_code =
                             (SELECT calendar_code
                                FROM cm_cldr_dtl
                               WHERE :from_start_date BETWEEN start_date AND end_date
                               AND calendar_code = 'XX_CST_CL')
                      AND period_code = 1)
              AND (SELECT end_date
                     FROM cm_cldr_dtl
                    WHERE calendar_code =
                             (SELECT calendar_code
                                FROM cm_cldr_dtl
                               WHERE :from_start_date BETWEEN start_date AND end_date
                               AND calendar_code = 'XX_CST_CL')
                      AND period_code = 12)),3) prod_year
    FROM mtl_material_transactions mmt,
         mtl_transaction_types mtt,
         mtl_system_items_b msi,
         gme_batch_header gbh
   WHERE trunc(mmt.transaction_date) BETWEEN :from_start_date AND :to_start_date
     AND msi.organization_id = :p_org_id
     AND mmt.transaction_type_id = mtt.transaction_type_id
     AND mtt.transaction_type_name = 'WIP Issue'
     AND msi.inventory_item_id = NVL(:p_item_id,msi.inventory_item_id)
     AND mmt.inventory_item_id = msi.inventory_item_id
     AND mmt.organization_id = msi.organization_id
     AND mmt.transaction_source_id = gbh.batch_id
     AND msi.item_type = 'FG'
     AND EXISTS
     (SELECT 1
      FROM gme_material_details gmd,gme_batch_header gbh
      WHERE 1=1
      AND gmd.inventory_item_id = msi.inventory_item_id
      AND gmd.batch_id = gbh.batch_id
      AND gmd.organization_id = msi.organization_id
      AND line_type = -1
      AND msi.inventory_item_id in
         (SELECT gmd1.inventory_item_id
          FROM gme_material_details gmd1,gme_batch_header gbh1
          WHERE gbh1.batch_no = NVL(:P_BATCH_NO,gbh1.batch_no)
          AND gmd1.batch_id = gbh1.batch_id
          AND gmd1.line_type in (1,2)))
GROUP BY msi.segment1,msi.description, msi.primary_unit_of_measure,mtt.transaction_type_id,msi.inventory_item_id,msi.organization_id
ORDER BY msi.segment1

Oracle R12 Parameter mandatory based on another parameter

Objective:

                Generally we can enable to disable a parameter based on dependent value sets method. There may be scenario where we have to make a parameter mandatory based on another parameter value. For example, in Purchase Order Document Report, we have to make the Release Number parameter mandatory for Blanket PO and non-mandatory for Standard PO.

We need to create three parameters to achieve that. A parameter for PO Number, release number each and one parameter to make the release number mandatory or non-mandatory. In the following screenshot, PO Number, Release Number and Header Id are created for that respective purposes.

          

Value set for P_PO_NUM(PO Number) parameter

Value set for P_HEADER_ID parameter

             
         

The default type of parameter should be set as SQL Statement.

         

The default value query is given below.

select po_header_id from po_headers_all poh where poh.type_lookup_code = 'BLANKET' and segment1 = :$FLEX$.XX_PO_NUMBER

where XX_PO_NUMBER is the value set created for PO Number parameter.

Value set for P_RELEASE_NUM (Release Number) parameter

The release number parameter should be added with NVL clause in the query because it will be passed only for Blanket parameter but it should be made mandatory in Concurrent Program > Parameters.