APPS.MSD_COMMON_UTILITIES_LB SQL Statements

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

Line: 12

select level_pk
from
msd_level_values_lb
where
level_id = p_level_id
and sr_level_pk = p_sr_level_pk ;

Line: 29

  select msd_level_values_s.nextval into x_level_pk
  from   sys.dual ;

Line: 49

select demand_plan_id
from
msd_demand_plans
where
liab_plan_id = p_plan_id ;

Line: 72

select liab_plan_id
from
msd_demand_plans
where
demand_plan_id = p_demand_plan_id ;

Line: 102

select  mic.category_name
from msc_item_categories mic ,
MSC_TRADING_PARTNERS mtp
where
mtp.partner_type = 3
and mtp.sr_tp_id = mtp.master_organization
and mtp.sr_tp_id = mic.organization_id
and mic.inventory_item_id = p_inventory_item_id
and mic.category_set_id = p_category_set_id
and mic.sr_instance_id = mtp.sr_instance_id
order by mic.category_name ;

Line: 138

select  mic.description
from msc_item_categories mic ,
MSC_TRADING_PARTNERS mtp
where
mtp.partner_type = 3
and mtp.sr_tp_id = mtp.master_organization
and mtp.sr_tp_id = mic.organization_id
and mic.inventory_item_id = p_inventory_item_id
and mic.category_set_id = p_category_set_id
and mic.sr_instance_id = mtp.sr_instance_id
order by mic.category_name ;

Line: 170

select start_date
from msc_plans
where
plan_id = p_plan_id ;

Line: 195

select cutoff_date
from msc_plans
where
plan_id = p_plan_id ;

Line: 216

select compile_designator
from msc_plans
where
plan_id = p_plan_id ;

Line: 241

        SELECT
        distinct
        mdpar.parameter_type ,
        mcd.planning_server_view_name ,
        mcd.description
        FROM   msd_dp_parameters mdpar ,
               msd_cs_definitions mcd,
               msd_demand_plans mdp,
	       msd_cs_defn_dim_dtls mcddd
        where mdpar.demand_plan_id =mdp.demand_plan_id
        and mdp.demand_plan_name = 'LIABILTY_PLAN'
        and mdp.template_flag = 'Y'
        and mdpar.parameter_type =mcd.name
	and mcd.cs_definition_id = mcddd.cs_definition_id
	and mcddd.dimension_code = 'TIM'
	and nvl( mcd.planning_server_view_name, 'NA')  <> 'NA'
	and nvl(mcd.liability_user_flag , 'N') <> 'Y' ;

Line: 272

  select sysdate into x_cp_liab_end_date from dual ;

Line: 280

   v_sql_stmt := 'select max(end_date) from  '|| x_input_param_rec.planning_server_view_name|| '  where cs_name  = '||''''||x_collab_liab||''''  ;

Line: 306

select md.organization_id
from
msc_designators md,
msc_plans mp
where
md.designator = mp.compile_designator
and mp.plan_id = p_plan_id ;

Line: 335

select md.sr_instance_id
from
msc_designators md ,
msc_plans mp
where
md.designator = mp.compile_designator
and mp.plan_id = p_plan_id ;

Line: 358

/* This api is called by DPE to update the details of  the previous liability */

procedure  liability_post_process( p_demand_plan_id IN NUMBER ,
                                 p_scenario_name IN VARCHAR2 ,
                                 p_senario_rev_num IN NUMBER)


IS
CURSOR c_demand_plan
IS
select
mdp.demand_plan_id ,
mdp.plan_start_date ,
mds.scenario_id
from
msd_demand_plans mdp ,
msd_dp_scenarios  mds
where mdp.demand_plan_id = p_demand_plan_id
and mdp.demand_plan_id = mds.demand_plan_id
and mds.SCENARIO_DESIGNATOR = 'TOTAL_LIABILITY'
and mdp.plan_type = 'LIABILITY';

Line: 387

      UPDATE msd_demand_plans SET liability_revision_num =  p_senario_rev_num ,
       prev_liab_pub_plan_start_date =  x_demand_plan_rec.plan_start_date
      WHERE demand_plan_id =  p_demand_plan_id ;

Line: 392

      UPDATE MSD_DP_SCENARIO_REVISIONS SET  plan_start_date = x_demand_plan_rec.plan_start_date
      WHERE demand_plan_id = p_demand_plan_id and revision = p_senario_rev_num
      and scenario_id = x_demand_plan_rec.scenario_id   ;

Line: 410

/* Updates supply plan dates in msd demand Plans */

FUNCTION liability_plan_update( p_demand_plan_id IN NUMBER )
RETURN NUMBER
IS
CURSOR c_demand_plan
IS
select
mds.scenario_id
from
msd_demand_plans mdp ,
msd_dp_scenarios  mds
where mdp.demand_plan_id = p_demand_plan_id
and mdp.demand_plan_id = mds.demand_plan_id
and mds.SCENARIO_DESIGNATOR = 'TOTAL_LIABILITY' /*  This will be translatable */
and mdp.plan_type = 'LIABILITY';

Line: 431

select
max(revision),
plan_start_date
from
MSD_DP_SCENARIO_REVISIONS
where
demand_plan_id = p_demand_plan_id
and scenario_id = p_scenario_id
group by
plan_start_date
;

Line: 460

      UPDATE msd_demand_plans SET previous_plan_start_date =  x_plan_start_date , LIABILITY_REVISION_NUM = x_rev_num
      WHERE  demand_plan_id = p_demand_plan_id ;

Line: 473

          UPDATE msd_demand_plans SET previous_plan_start_date = x_demand_plan_rec.prev_liab_pub_plan_start_date
          WHERE  demand_plan_id = p_demand_plan_id ;

Line: 484

          UPDATE msd_demand_plans SET prev_liab_pub_plan_start_date = x_demand_plan_rec.previous_plan_start_date
          WHERE  demand_plan_id = p_demand_plan_id ;

Line: 495

 END liability_plan_update ;

Line: 504

select calendar_code from
msc_trading_partners
where partner_type = 3
and sr_instance_id = p_instance_id
and sr_tp_id = p_org_id ;

Line: 529

select
uom_code
 from msc_uom_conversions
 where upper(uom_code) = 'EA' and rownum = 1 ;

Line: 582

select

fnd_profile.value('APPS_SERVLET_AGENT') ||

'/oowa/aw92/'||

'dbapps.xwdevkit/xwd_init?apps.' ||

nvl(fnd_profile.value('MSD_CODE_AW'),'ODPCODE') ||

'/dp.init.shell?' ||

'/IDF=' || p_function_id ||

'/PLAN_TYPE=LIABILITY' ||

'/ID=' || p_demand_plan_id ||

'/SHR=MSD' || p_demand_plan_id ||

'/UID=' || fnd_global.user_id ||

'/RID=' || fnd_global.resp_id ||

'/RAID=' || fnd_global.resp_appl_id

from dual ;

Line: 649

select
SUPPLIER_ID,
SUPPLIER_SITE_ID ,
ORGANIZATION_ID ,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
AUTHORIZATION_CODE,
cutoff_days,
INCLUDE_LIABILITY_AGREEMENT,
ASL_LIABILITY_AGREEMENT_BASIS
from
msc_asl_auth_details
where
plan_id = -1
/* and INCLUDE_LIABILITY_AGREEMENT = 1   This filter will remove any disabled agreement */
order
by
SR_INSTANCE_ID,
SUPPLIER_ID,
SUPPLIER_SITE_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
TRANSACTION_ID ;

Line: 690

UPDATE   msc_item_suppliers
set  INCLUDE_LIABILITY_AGREEMENT = NULL ,
ASL_LIABILITY_AGREEMENT_BASIS =NULL
where
plan_id = p_plan_id ;

Line: 716

 UPDATE msc_asl_auth_details
 set start_days = x_end_days  ,
       end_days =  x_end_days + cutoff_days
  where
 PLAN_ID  = -1 and
SUPPLIER_ID  = x_sup_item_org.SUPPLIER_ID and
SUPPLIER_SITE_ID = x_sup_item_org.SUPPLIER_SITE_ID and
ORGANIZATION_ID  = x_sup_item_org.ORGANIZATION_ID and
SR_INSTANCE_ID = x_sup_item_org.SR_INSTANCE_ID and
INVENTORY_ITEM_ID = x_sup_item_org.INVENTORY_ITEM_ID and
AUTHORIZATION_CODE  = x_sup_item_org.AUTHORIZATION_CODE ;

Line: 739

UPDATE   msc_item_suppliers
set  INCLUDE_LIABILITY_AGREEMENT = x_sup_item_org. INCLUDE_LIABILITY_AGREEMENT ,
ASL_LIABILITY_AGREEMENT_BASIS = x_sup_item_org.ASL_LIABILITY_AGREEMENT_BASIS
where
SUPPLIER_ID  = x_sup_item_org.SUPPLIER_ID and
SUPPLIER_SITE_ID = x_sup_item_org.SUPPLIER_SITE_ID and
ORGANIZATION_ID  = x_sup_item_org.ORGANIZATION_ID and
SR_INSTANCE_ID = x_sup_item_org.SR_INSTANCE_ID and
INVENTORY_ITEM_ID = x_sup_item_org.INVENTORY_ITEM_ID and
plan_id = p_plan_id  ;

Line: 763

  select msi2.uom_code
    from msc_system_items msi,
         msc_system_items msi2,
         msc_trading_partners mtp
   where msi2.organization_id = mtp.master_organization
     and msi.organization_id = mtp.sr_tp_id
     and msi.sr_instance_id = mtp.sr_instance_id
     and msi.inventory_item_id = msi2.inventory_item_id
     and msi2.plan_id = -1
     and msi.inventory_item_id = p_item_id
     and msi.plan_id = p_dp_plan_id
     order by msi.sr_instance_id, msi.organization_id desc;

Line: 801

 select 1 from msd_demand_plans
                                  where liab_plan_id = p_plan_id
                                  and nvl( DP_BUILD_ERROR_FLAG, 'NO')  = 'NO'
                                  and plan_type = 'LIABILITY' ;