APPS.POS_UPDATE_CAPACITY_PKG SQL Statements

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

Line: 21

PROCEDURE INSERT_TEMP_MFG_CAPACITY(
        p_asl_id                    IN   NUMBER,
        p_from_date                 IN   DATE,
        p_to_date                 IN   DATE,
        p_capacity_per_day               IN   NUMBER,
        p_created_by            in number,
        p_capacity_id in number,
        p_status in varchar2,/*
        p_supplier_item_number in varchar2,
        p_item_number in varchar2,
        p_item_description in varchar2,
        p_uom in varchar2,
        p_vendor_id in number,
        p_vendor_name in varchar2,*/
        p_error_code                OUT NOCOPY VARCHAR2,
        p_error_message             OUT NOCOPY VARCHAR2) is


  l_seq number;

Line: 42

    /* Update PO_ASL_ATTRIBUTES form ISP     */
  select POS_MFG_CAPACITY_TEMP_ID_S.NEXTVAL
  into l_seq from sys.dual;

Line: 46

  insert into POS_MFG_CAPACITY_TEMP (
    mfg_capacity_id,
    asl_id,
    last_update_date,
    last_updated_by,
    last_update_login,
    creation_date,
    created_by,
    from_date,
    to_date,
    capacity_per_day,/*
    supplier_item_number,
    item_number,
    item_description,
    uom,
    vendor_id,
    vendor_name,*/
    CAPACITY_ID,
    status)
  values
    (
    l_seq,
    p_asl_id,
    sysdate,
    p_created_by,
    p_created_by,
    sysdate,
    p_created_by,
    p_from_date,
    p_to_date,
    p_capacity_per_day,/*
    p_supplier_item_number,
    p_item_number,
    p_item_description,
    p_uom,
    p_vendor_id,
    p_vendor_name,*/
    p_capacity_id,
    p_status);

Line: 93

    p_ERROR_MESSAGE := 'exception raised during Update';

Line: 95

END INSERT_TEMP_MFG_CAPACITY;

Line: 98

PROCEDURE INSERT_TEMP_CAPACITY_TOLERANCE(
        p_asl_id                    IN   NUMBER,
        p_days_in_advance               IN   NUMBER,
        p_tolerance               IN   NUMBER,
        p_created_by            in number,
        /*
        p_supplier_item_number in varchar2,
        p_item_number in varchar2,
        p_item_description in varchar2,
        p_uom in varchar2,
        p_vendor_id in number,
        p_vendor_name in varchar2,
        */
        p_error_code                OUT NOCOPY VARCHAR2,
        p_error_message             OUT  NOCOPY VARCHAR2) is


  l_seq number;

Line: 118

    /* Update PO_ASL_ATTRIBUTES form ISP     */
  select POS_MFG_CAPACITY_TEMP_ID_S.NEXTVAL
  into l_seq from sys.dual;

Line: 122

  insert into POS_CAPACITY_TOLERANCE_TEMP(
    capacity_tolerance_id,
    asl_id,
    last_update_date,
    last_updated_by,
    last_update_login,
    creation_date,
    created_by,
    days_in_advance,
    tolerance,
    /*
    supplier_item_number,
    item_number,
    item_description,
    uom,
    vendor_id,
    vendor_name,
    */
    status)
  values
    (
    l_seq,
    p_asl_id,
    sysdate,
    p_created_by,
    p_created_by,
    sysdate,
    p_created_by,
    p_days_in_advance,
    p_tolerance,
    /*
    p_supplier_item_number,
    p_item_number,
    p_item_description,
    p_uom,
    p_vendor_id,
    p_vendor_name,
    */
    'NEW');

Line: 169

    p_ERROR_MESSAGE := 'exception raised during Update';

Line: 171

END INSERT_TEMP_CAPACITY_TOLERANCE;

Line: 175

PROCEDURE UPDATE_EXIST(p_asl_id in NUMBER,
        p_return_code out NOCOPY number) is

begin

  select count(*)
  into p_return_code
  from POS_MFG_CAPACITY_TEMP
  where asl_id=p_asl_id and status in ('NEW', 'OLD', 'DEL', 'MOD');

Line: 187

    select count(*)
    into p_return_code
    from POS_CAPACITY_TOLERANCE_TEMP
    where asl_id=p_asl_id and status='NEW';

Line: 193

end UPDATE_EXIST;

Line: 204

    /* Update PO_ASL_ATTRIBUTES form ISP     */

  UPDATE_EXIST(p_asl_id, l_count);

Line: 212

  select to_char(POS_ASL_UPD_ITEMKEY_S.NEXTVAL)
  into l_seq from sys.dual;

Line: 219

                                  Process   => 'UPDATE_CAPACITY');

Line: 244

         SELECT from_date, to_date, capacity_per_day
         FROM   pos_supplier_item_capacity_v
         WHERE  asl_id=id
         order by from_date asc;

Line: 328

         SELECT from_date, to_date, capacity_per_day
         FROM   pos_mfg_capacity_temp
         WHERE  asl_id=id and status in ('NEW', 'OLD', 'MOD')
         order by from_date asc;

Line: 409

         SELECT number_of_days, tolerance
         FROM   po_supplier_item_tolerance
         WHERE  asl_id=id
         order by number_of_days asc;

Line: 464

         SELECT days_in_advance, tolerance
         FROM   POS_CAPACITY_TOLERANCE_TEMP
         WHERE  asl_id=id and status='NEW'
         order by days_in_advance asc;

Line: 543

  select  DESCRIPTION,
          BUYER_ID,
          PLANNER_ID,
          UOM,
          SUPPLIER_ITEM_NUMBER,
          ITEM_NUMBER,
          VENDOR_ID
  into    l_item_description,
          l_buyer_id,
          l_planner_id,
          l_uom,
          l_supplier_item_number,
          l_item_number,
          l_vendor_id
  from POS_ORD_MODIFIERS_V
  where asl_id=l_asl_id;

Line: 593

    select vendor_name
      into l_supplier_username
      from po_vendors
     where vendor_id=l_vendor_id;

Line: 606

  select count(*)
  into l_vendor_id
  from POS_MFG_CAPACITY_TEMP
  where asl_id=l_asl_id and status in ('NEW', 'OLD', 'DEL', 'MOD');

Line: 614

    select last_updated_by
      into l_vendor_id
      from POS_MFG_CAPACITY_TEMP
      where mfg_capacity_id=
             (select min(mfg_capacity_id)
                from POS_MFG_CAPACITY_TEMP
               where asl_id=l_asl_id and
                     status in ('NEW', 'OLD', 'DEL', 'MOD'));

Line: 624

    select last_updated_by
      into l_vendor_id
      from POS_CAPACITY_TOLERANCE_TEMP
      where capacity_tolerance_id=
             (select min(capacity_tolerance_id)
                from POS_CAPACITY_TOLERANCE_TEMP
               where asl_id=l_asl_id and status='NEW');

Line: 688

			     avalue  	 => 'PLSQL:POS_UPDATE_CAPACITY_PKG.GENERATE_CAP_APP_NOTIF/'|| itemtype || ':' || itemkey);

Line: 693

			     avalue => 'PLSQL:POS_UPDATE_CAPACITY_PKG.GENERATE_SUPPL_CAP_NOTIF_APPR/'|| itemtype || ':' || itemkey);

Line: 698

			     avalue  	 => 'PLSQL:POS_UPDATE_CAPACITY_PKG.GENERATE_SUPPL_CAP_NOTIF_REJ/'|| itemtype || ':' || itemkey);

Line: 713

       wf_core.context('POS_UPDATE_CAPACITY_PKG','INIT_ATTRIBUTES',l_progress);

Line: 856

procedure UPDATE_ASL(  itemtype        in  varchar2,
  itemkey         in  varchar2,
  actid           in number,
  funcmode        in  varchar2,
  resultout          out NOCOPY varchar2    ) is

  l_asl_id number;

Line: 874

  CURSOR tol_updates(id number) is
    SELECT
      days_in_advance, tolerance, created_by
    FROM  POS_CAPACITY_TOLERANCE_TEMP
    WHERE asl_id=id and status='NEW';

Line: 880

  CURSOR cap_updates(id number) is
    SELECT
      from_date, to_date, capacity_per_day, capacity_id, created_by, status
    FROM  POS_MFG_CAPACITY_TEMP
    WHERE asl_id=id and status in ('NEW', 'OLD', 'DEL', 'MOD');

Line: 894

  pos_supplier_item_tol_pkg.delete(l_asl_id);

Line: 897

  open tol_updates(l_asl_id);

Line: 899

    FETCH tol_updates INTO l_num_of_days, l_tolerance, l_created_by;

Line: 900

    EXIT WHEN tol_updates%NOTFOUND;

Line: 905

  update POS_CAPACITY_TOLERANCE_TEMP
     set status='ACE'
   where asl_id=l_asl_id
         and status='NEW';

Line: 912

  open cap_updates(l_asl_id);

Line: 914

    FETCH cap_updates INTO l_from_date, l_to_date, l_cap_per_day, l_capacity_id, l_created_by, l_status;

Line: 915

    EXIT WHEN cap_updates%NOTFOUND;

Line: 920

      insert into po_supplier_item_capacity
       (CAPACITY_ID,
        ASL_ID,
        USING_ORGANIZATION_ID,
        FROM_DATE,
        TO_DATE,
        CAPACITY_PER_DAY,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_LOGIN,
        CREATION_DATE,
        CREATED_BY)
      values (
	     po_supplier_item_capacity_s.nextval,
	     l_asl_id,
	     -1,
	     l_from_date,
	     l_to_date,
	     l_cap_per_day,
	     sysdate,
	     l_created_by,
	     l_created_by,
	     sysdate,
	     l_created_by);

Line: 945

      DELETE from po_supplier_item_capacity
      WHERE
        asl_id = l_asl_id AND capacity_id = l_capacity_id;

Line: 949

      UPDATE po_supplier_item_capacity
      SET
        FROM_DATE = l_from_date,
        TO_DATE = l_to_date,
        CAPACITY_PER_DAY = l_cap_per_day,
        last_update_date = Sysdate,
        last_updated_by = l_created_by,
        last_update_login = l_created_by
      WHERE
        asl_id = l_asl_id AND capacity_id = l_capacity_id;

Line: 963

  update POS_MFG_CAPACITY_TEMP
     set status='ACE'
   where asl_id=l_asl_id
         and status in ('NEW', 'OLD', 'DEL', 'MOD');

Line: 971

         wf_core.context('POS_UPDATE_CAPACITY_PKG','UPDATE_ASL',x_progress);

Line: 999

procedure UPDATE_STATUS(  itemtype        in  varchar2,
  itemkey         in  varchar2,
  actid           in number,
  funcmode        in  varchar2,
  resultout          out NOCOPY varchar2    ) is

  l_asl_id number;

Line: 1016

  update POS_MFG_CAPACITY_TEMP
     set status='REJ'
   where asl_id=l_asl_id
         and status in ('NEW', 'OLD', 'DEL', 'MOD');

Line: 1021

  update POS_CAPACITY_TOLERANCE_TEMP
     set status='REJ'
   where asl_id=l_asl_id
         and status='NEW';

Line: 1114

l_document := l_document || '' || fnd_message.get_string('POS','POS_REQUESTED_UPDATES') || '' || NL;

l_document := l_document || '' || fnd_message.get_string('POS','POS_REQUESTED_UPDATES') || '' || NL;