eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.INV_MMX_WRAPPER_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 81


**  p_item_select
**         Item Number.
**         Default Value is NULL.
**  p_handle_rep_item
**         Parameter for Repetitive item handling.
**         1- Create Requistion
**         2- Create Discrete Job
**         3- Do not Restock ,ie Report Only.
**         Default Value is 3.
**  p_pur_revision
**         Parameter for Purchasing by Revision.
**         Used for Revision controlled items.
**         It can be 'Yes' or 'No' or NULL.
**         Default value is NULL.
**  p_cat_select
**         Item Category.
**         Defualt value is 'NULL'
**  p_cat_set_id
**         Category Set Id.
**         Default value is NULL.
**  p_mcat_struct
**         Category Structure Number.
**         Default value is NULL.
**  p_level
**         Min Max Planning Level.
**         1-Organization
**         2-Subinventory
**         Default value is 2.
**  p_restock
**         Restocking is required or not.
**         If Restock is No, only the report will be generated and
**         no replenishment will happen.
**         Default value is 1.
**  p_include_nonnet
**         Include Non-netable Subinventories or not.
**         Default value is 1.
**  p_include_po
**         Include PO as Supply or not.
**         Default value is 1.
**  p_include_mo   -- Added for Bug 3057273
**         Include Move Orders as Supply or not.
**         Default value is 1.
**  p_include_wip
**         Include WIP as Supply or not.
**         Default value is 2.
**  p_include_if
**         Include Interface as Supply or not.
**         Default value is 1.
**  p_net_rsv
**         Inlclude Reserved Orders as Demands or not.
**         Default value is 1.
**  p_net_unrsv
**         Inlclude Unreserved Orders as Demands or not.
**         Default value is 1.
**  p_net_wip
**         Inlclude WIP Jobs as Demands or not.
**         Default value is 2.
**  p_dd_loc_id
**         Default Delivery To Location Id of the Planning Org.
**         Default value is NULL.
**  p_buyer_hi
**         Buyer Name From.
**         Default value is NULL.
**  p_buyer_lo
**         Buyer Name To.
**         Default value is NULL.
**  p_range_buyer
**         Where clause for Range of Buyers.
**         Default Value is '1 = 1'.
**  p_range_sql
**         Where clause for Range of Items,Categories and Planners.
**         Default Value is '1 = 1'.
**  p_sort
**         Min Max Report Sort By Criteria.
**         1-Inventory Item
**         2-Category
**         3-Planner
**         4-Buyer
**         Default Value is 1.
**  p_selection
**         Parameter for Min Max planned Item selection criteria.
**         1- Min Max planned Items under minimum Qty.
**         2- Min Max planned Items over minimum Qty.
**         3- All Min Max planned Items.
**         Deafualt value is 3.
**  p_sysdate
**         Current System Date.
**         Default Value is sysdate.
**  p_s_cutoff
**        Supply Cut Off Date.
**        Default Value is NULL.
**  p_d_cutoff
**        Demand Cut Off Date.
**        Default Value is NULL.
**
** Output Parameters:
**
**  x_return_status
**        Return status indicating success, error or unexpected error.
**  x_msg_count
**        Number of messages in the message list.
**  x_msg_data
**        If the number of messages in message list is 1, contains
**        message text.
**
** ---------------------------------------------------------------------------
*/

PROCEDURE exec_min_max
( x_return_status     OUT NOCOPY VARCHAR2
, x_msg_count         OUT NOCOPY NUMBER
, x_msg_data          OUT NOCOPY VARCHAR2
, p_organization_id   IN  NUMBER
, p_user_id           IN  NUMBER
, p_subinv_tbl        IN  SubInvTableType
, p_employee_id       IN  NUMBER
, p_gen_report        IN  VARCHAR2
, p_mo_line_grouping  IN  NUMBER
, p_item_select       IN  VARCHAR2
, p_handle_rep_item   IN  NUMBER
, p_pur_revision      IN  NUMBER
, p_cat_select        IN  VARCHAR2
, p_cat_set_id        IN  NUMBER
, p_mcat_struct       IN  NUMBER
, p_level             IN  NUMBER
, p_restock           IN  NUMBER
, p_include_nonnet    IN  NUMBER
, p_include_po        IN  NUMBER
, p_include_mo        IN  NUMBER
, p_include_wip       IN  NUMBER
, p_include_if        IN  NUMBER
, p_net_rsv           IN  NUMBER
, p_net_unrsv         IN  NUMBER
, p_net_wip           IN  NUMBER
, p_dd_loc_id         IN  NUMBER
, p_buyer_hi          IN  VARCHAR2
, p_buyer_lo          IN  VARCHAR2
, p_range_buyer       IN  VARCHAR2
, p_range_sql         IN  VARCHAR2
, p_sort              IN  VARCHAR2
, p_selection         IN  NUMBER
, p_sysdate           IN  DATE
, p_s_cutoff          IN  DATE
, p_d_cutoff          IN  DATE
)  IS

l_proc CONSTANT    VARCHAR2(30) := 'EXEC_MIN_MAX';

Line: 244


l_item_select      VARCHAR2(300);

Line: 245


l_cat_select       VARCHAR2(300);

Line: 270


  SELECT secondary_inventory_name
  FROM mtl_secondary_inventories  msi
  WHERE msi.organization_id = cp_org_id
  AND EXISTS
  (  SELECT 1
     FROM mtl_item_sub_inventories  misi
     WHERE misi.organization_id       = msi.organization_id
     AND misi.secondary_inventory     = msi. secondary_inventory_name
     AND misi.inventory_planning_code = 2
  );

Line: 304


                     ', p_item_select: '      || p_item_select              || fnd_global.local_chr(10)||
                     ', p_handle_rep_item: '  || to_char(p_handle_rep_item) || fnd_global.local_chr(10)||
                     ', p_pur_revision: '     || to_char(p_pur_revision)    || fnd_global.local_chr(10)||
                     ', p_cat_select: '       || p_cat_select               || fnd_global.local_chr(10)||
                     ', p_cat_set_id: '       || to_char(p_cat_set_id)      || fnd_global.local_chr(10)||
                     ', p_mcat_struct: '      || to_char(p_mcat_struct)     || fnd_global.local_chr(10)||
                     ', p_level: '            || to_char(p_level)           || fnd_global.local_chr(10)||
                     ', p_restock: '          || to_char(p_restock)         || fnd_global.local_chr(10)||
                     ', p_include_nonnet: '   || to_char(p_include_nonnet)  || fnd_global.local_chr(10)||
                     ', p_include_po: '       || to_char(p_include_po)      || fnd_global.local_chr(10)||
                     ', p_include_mo: '       || to_char(p_include_mo)      || fnd_global.local_chr(10)||
                     ', p_include_wip: '      || to_char(p_include_wip)     || fnd_global.local_chr(10)||
                     ', p_include_if: '       || to_char(p_include_if)      || fnd_global.local_chr(10)
                     , l_proc
                     , 9);

Line: 330


                     ', p_selection: '        || to_char(p_selection)       || fnd_global.local_chr(10)||
                     ', p_sysdate: '          || to_char(p_sysdate,  'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10)||
                     ', p_s_cutoff: '         || to_char(p_s_cutoff, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10)||
                     ', p_s_cutoff: '         || to_char(p_s_cutoff, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10)||
                     ', p_d_cutoff: '         || to_char(p_d_cutoff, 'DD-MON-YYYY HH24:MI:SS') || fnd_global.local_chr(10)
                     , l_proc
                     , 9);

Line: 356


                  SELECT STRUCTURE_ID
                  INTO   l_mcat_struct
                  FROM   MTL_CATEGORY_SETS
                  WHERE  CATEGORY_SET_ID = P_CAT_SET_ID;

Line: 374


            SELECT CSET.CATEGORY_SET_ID, CSET.STRUCTURE_ID
            INTO   l_cat_set_id,l_mcat_struct
            FROM   MTL_CATEGORY_SETS CSET,
                   MTL_DEFAULT_CATEGORY_SETS DEF
            WHERE  DEF.CATEGORY_SET_ID = CSET.CATEGORY_SET_ID
            AND    DEF.FUNCTIONAL_AREA_ID = 1;

Line: 402


            SELECT EMPLOYEE_ID
            INTO   l_employee_id
            FROM   FND_USER
            WHERE  USER_ID = P_USER_ID;

Line: 445


             SELECT LOC.LOCATION_ID
             INTO   l_dd_loc_id
             FROM   HR_ORGANIZATION_UNITS ORG,HR_LOCATIONS LOC
             WHERE  ORG.ORGANIZATION_ID = nvl(p_organization_id,-1)
             AND    ORG.LOCATION_ID = LOC.LOCATION_ID;

Line: 513


        SELECT OPERATING_UNIT, OPERATING_UNIT
        INTO   l_operating_unit, l_po_org_id
        FROM   ORG_ORGANIZATION_DEFINITIONS
        WHERE  ORGANIZATION_ID = P_ORGANIZATION_ID;

Line: 541


        SELECT CUSTOMER_ID,SITE_USE_ID
        INTO   l_cust_id,l_site_use_id
        FROM   PO_LOCATION_ASSOCIATIONS_ALL
        WHERE  LOCATION_ID = l_dd_loc_id
          AND org_id=l_operating_unit;

Line: 588


        SELECT NVL(REQ_ENCUMBRANCE_FLAG, 'N')
        INTO   l_encum_flag
        FROM   FINANCIALS_SYSTEM_PARAMS_ALL
        WHERE  NVL(ORG_ID,-11) = NVL(l_operating_unit,-11);

Line: 611


        SELECT P.CALENDAR_CODE, P.CALENDAR_EXCEPTION_SET_ID
        INTO   l_cal_code, l_exception_set_id
        FROM   MTL_PARAMETERS P
        WHERE  P.ORGANIZATION_ID = P_ORGANIZATION_ID;

Line: 635


   l_item_select := NVL(P_ITEM_SELECT,('C.SEGMENT1'));

Line: 636


   l_cat_select  := NVL(P_CAT_SELECT,('B.SEGMENT1||B.SEGMENT2'));

Line: 638


   print_debug('Item and Category are: ' || l_item_select  ||','|| l_cat_select
              , l_proc
              , 9);

Line: 647


        SELECT 1
        INTO   l_valid
        FROM   MFG_LOOKUPS
        WHERE  LOOKUP_TYPE = 'MTL_MINMAX_RPT_SORT_BY'
        AND    LOOKUP_CODE = NVL(P_SORT,1);

Line: 666


        SELECT 1
        INTO   l_valid
        FROM   MFG_LOOKUPS
        WHERE  LOOKUP_TYPE = 'MTL_MINMAX_RPT_SEL'
        AND    LOOKUP_CODE = NVL(P_SELECTION,3);

Line: 697


        SELECT WIP_JOB_SCHEDULE_INTERFACE_S.NEXTVAL
        INTO l_wip_batch_id
        FROM SYS.DUAL;

Line: 723


        SELECT WSM_LOT_JOB_INTERFACE_S.NEXTVAL
        INTO l_osfm_batch_id
        FROM SYS.DUAL;

Line: 807


         ( p_item_select       => l_item_select
         , p_handle_rep_item   => NVL(p_handle_rep_item,3)
         , p_pur_revision      => l_pur_revision
         , p_cat_select        => l_cat_select
         , p_cat_set_id        => l_cat_set_id
         , p_mcat_struct       => l_mcat_struct
         , p_level             => NVL(p_level,2)
         , p_restock           => NVL(p_restock,1)
         , p_include_nonnet    => l_include_no_net
         , p_include_po        => NVL(p_include_po,1)
         , p_include_mo        => NVL(p_include_mo,1)
         , p_include_wip       => NVL(p_include_wip,2)
         , p_include_if        => NVL(P_include_if,1)
         , p_net_rsv           => NVL(p_net_rsv,1)
         , p_net_unrsv         => NVL(p_net_unrsv,1)
         , p_net_wip           => NVL(p_net_wip,2)
         , p_org_id            => p_organization_id
         , p_user_id           => p_user_id
         , p_employee_id       => l_employee_id
         , p_subinv            => l_subinv_tbl(l_subinv_count)
         , p_dd_loc_id         => l_dd_loc_id
         , p_wip_batch_id      => l_wip_batch_id
         , p_approval          => l_approval
         , p_buyer_hi          => p_buyer_hi
         , p_buyer_lo          => p_buyer_lo
         , p_range_buyer       => l_range_buyer
         , p_cust_id           => l_cust_id
         , p_cust_site_id      => l_site_use_id
         , p_po_org_id         => l_po_org_id
         , p_range_sql         => NVL(p_range_sql,'1 = 1')
         , p_sort              => NVL(p_sort,1)
         , p_selection         => NVL(p_selection,3)
         , p_sysdate           => l_sysdate
         , p_s_cutoff          => l_s_cutoff
         , p_d_cutoff          => l_d_cutoff
         , p_order_by          => l_order_by
         , p_encum_flag        => l_encum_flag
         , p_cal_code          => l_cal_code
         , p_exception_set_id  => l_exception_set_id
         , p_gen_report        => l_gen_report
         , x_return_status     => l_return_status
         , x_msg_data          => l_msg_data
         , p_osfm_batch_id     => l_osfm_batch_id               /* Added for Bug 6807835 */
         );

Line: 878


      ( p_item_select       => l_item_select
      , p_handle_rep_item   => NVL(p_handle_rep_item,3)
      , p_pur_revision      => l_pur_revision
      , p_cat_select        => l_cat_select
      , p_cat_set_id        => l_cat_set_id
      , p_mcat_struct       => l_mcat_struct
      , p_level             => NVL(p_level,2)
      , p_restock           => NVL(p_restock,1)
      , p_include_nonnet    => l_include_no_net
      , p_include_po        => NVL(p_include_po,1)
      , p_include_mo        => NVL(p_include_mo,1)
      , p_include_wip       => NVL(p_include_wip,2)
      , p_include_if        => NVL(P_include_if,1)
      , p_net_rsv           => NVL(p_net_rsv,1)
      , p_net_unrsv         => NVL(p_net_unrsv,1)
      , p_net_wip           => NVL(p_net_wip,2)
      , p_org_id            => p_organization_id
      , p_user_id           => p_user_id
      , p_employee_id       => l_employee_id
      , p_subinv            => NULL
      , p_dd_loc_id         => l_dd_loc_id
      , p_wip_batch_id      => l_wip_batch_id
      , p_approval          => l_approval
      , p_buyer_hi          => p_buyer_hi
      , p_buyer_lo          => p_buyer_lo
      , p_range_buyer       => l_range_buyer
      , p_cust_id           => l_cust_id
      , p_cust_site_id      => l_site_use_id
      , p_po_org_id         => l_po_org_id
      , p_range_sql         => NVL(p_range_sql,'1 = 1')
      , p_sort              => NVL(p_sort,1)
      , p_selection         => NVL(p_selection,3)
      , p_sysdate           => l_sysdate
      , p_s_cutoff          => l_s_cutoff
      , p_d_cutoff          => l_d_cutoff
      , p_order_by          => l_order_by
      , p_encum_flag        => l_encum_flag
      , p_cal_code          => l_cal_code
      , p_exception_set_id  => l_exception_set_id
      , p_gen_report        => l_gen_report
      , x_return_status     => l_return_status
      , x_msg_data          => l_msg_data
      , p_osfm_batch_id     => l_osfm_batch_id               /* Added for Bug 6807835 */
      );

Line: 953


   SELECT COUNT(*)
   INTO l_count
   FROM WIP_JOB_SCHEDULE_INTERFACE
   WHERE GROUP_ID = l_wip_batch_id;

Line: 986


   SELECT count(*)
   INTO l_job_count
   FROM WSM_LOT_JOB_INTERFACE
   WHERE GROUP_ID = l_osfm_batch_id;

Line: 1467


        l_trohdr_rec.last_updated_by            :=   p_user_id;

Line: 1468


        l_trohdr_rec.last_update_date           :=   sysdate;

Line: 1469


        l_trohdr_rec.last_update_login          :=   p_user_id;