APPS.PON_AUCTION_APPROVAL_PKG SQL Statements

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

Line: 38

select count(auction_header_id),
       nvl(sum(decode(approval_status,'APPROVED',1,0)),0)
into l_approval_count,l_approved_count
from pon_neg_team_members
where auction_header_id = p_auction_header_id
and approver_flag='Y';

Line: 56

      select activity_label
      into l_activity_status
      from wf_item_activity_statuses_v
      where item_type = 'PONAPPRV'
      and item_key = p_top_process_item_key
      and activity_status_code = 'NOTIFIED';

Line: 80

   select activity_label
   into l_activity_status
   from wf_item_activity_statuses_v
   where item_type = 'PONAPPRV'
   and item_key = p_top_process_item_key
   and activity_status_code = 'NOTIFIED';

Line: 114

   select user_id
     into l_user_id
     from fnd_user
    where user_name = p_user_name;

Line: 122

select neg.approver_flag, neg.approval_status, auc.approval_status,
       wf_approval_item_key
into l_user_approval,l_user_status,l_auction_status,l_parent_process_itemKey
from pon_neg_team_members neg, pon_auction_headers_all auc
where auc.auction_header_id = p_auction_header_id
and neg.auction_header_id= auc.auction_header_id
and neg.user_id = l_user_id;

Line: 131

update pon_neg_team_members
set approver_flag ='N'
where auction_header_id = p_auction_header_id
and user_id = l_user_id;

Line: 136

/* Select notification Id from the item key and user name */
select notification_id
into   l_nid
from WF_ITEM_ACTIVITY_STATUSES
where ASSIGNED_USER = p_user_name
and ITEM_TYPE = l_itemType
and ITEM_KEY = l_itemkey
and activity_status ='NOTIFIED';

Line: 154

PROCEDURE UPDATE_NOTIF_ONLINE (p_auction_header_id number,
                                    p_user_name varchar2,
                                    p_result varchar2,
                                    p_note_to_buyer varchar2,
                                    p_resultOut out nocopy number) is
l_itemKey varchar2(240);

Line: 171

   select user_id
     into l_user_id
     from fnd_user
    where user_name = p_user_name;

Line: 178

select neg.approver_flag, neg.approval_status, auc.approval_status,
       wf_approval_item_key
into l_user_approval,l_user_status,l_auction_status,l_itemKey
from pon_neg_team_members neg, pon_auction_headers_all auc
where auc.auction_header_id = p_auction_header_id
and neg.auction_header_id= auc.auction_header_id
and neg.user_id = l_user_id;

Line: 188

/* Select notification Id from the item key and user name */
begin
select notification_id
into   l_nid
from WF_ITEM_ACTIVITY_STATUSES
where ASSIGNED_USER = p_user_name
and ITEM_TYPE = l_itemType
and ITEM_KEY = l_itemkey
and activity_status ='NOTIFIED';

Line: 213

end UPDATE_NOTIF_ONLINE;

Line: 217

   select activity_label, item_key,notification_id
   from wf_item_activity_statuses_v
   where item_type = p_item_type
   and item_key like p_parent_item_key || '_%'
   and activity_status_code = 'NOTIFIED';

Line: 236

PROCEDURE UPDATE_DOC_TO_CANCELLED ( itemtype in varchar2,
                                Itemkey		in varchar2,
                                actid	        in number,
                                uncmode		in varchar2,
                                resultout	out nocopy varchar2) is

l_auction_header_id NUMBER;

Line: 247

update pon_auction_headers_all
  set auction_status = 'CANCELLED'
  where auction_header_id=l_auction_header_id;

Line: 251

end UPDATE_DOC_TO_CANCELLED;

Line: 301

select fnd.user_name, fnd.user_id, pon.close_bidding_date,
       pon.auction_title,
       decode(nvl(pon.open_auction_now_flag,'N'),'Y',to_date(null),pon.open_bidding_date) open_bidding_date,
       nvl(pon.open_auction_now_flag,'N') open_auction_now_flag,
       pon.document_number, trading_partner_contact_id, trading_partner_name, trading_partner_contact_name,
       open_bidding_date, close_bidding_date, nvl(auction_round_number, 1),
       nvl(amendment_number, 0), auction_header_id_orig_amend, view_by_date
from fnd_user fnd,pon_auction_headers_all pon
where fnd.person_party_id = pon.trading_partner_contact_id and
      pon.auction_header_id = p_auction_header_id and
      rownum=1;

Line: 351

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

Line: 441

            select dt.doctype_group_name
            into l_doctype_group_name
            from pon_auction_headers_all auh, pon_auc_doctypes dt
            where auh.auction_header_id = p_auction_header_id
            and auh.doctype_id = dt.doctype_id;

Line: 553

select document_number
into   l_orig_document_number
from   pon_auction_headers_all
where  auction_header_id = l_auction_header_id_orig_amend;

Line: 562

   select emp.full_name into l_creator_full_name from
    per_all_people_f emp,
    fnd_user fnd
    where fnd.employee_id=emp.person_id and
       fnd.user_id = l_creator_user_id and
       trunc(sysdate) between emp.effective_start_date and emp.effective_end_date;

Line: 582

   select user_id
     into l_submit_user_id
     from fnd_user
    where user_name = p_submit_user_name;

Line: 600

/* Update Headers table */
UPDATE pon_auction_headers_all set
       wf_approval_item_key = l_itemKey,
       approval_status = 'INPROCESS'
WHERE auction_header_id = p_auction_header_id;

Line: 656

select u.user_name user_name,
       u.user_id,
       auc.close_bidding_date close_bidding_date,
       auc.auction_title,
       decode(nvl(auc.open_auction_now_flag,'N'),'Y',to_date(null),auc.open_bidding_date) open_bidding_date,
       decode(nvl(auc.publish_auction_now_flag,'N'),'Y',to_date(null),auc.view_by_date) view_by_date,
       auc.document_number doc_number,
       trading_partner_contact_id auction_contact_id,
       nvl(auc.publish_auction_now_flag,'N') publish_auction_now_flag,
       nvl(auc.open_auction_now_flag,'N') open_auction_now_flag,
       auc.auction_header_id_orig_amend
from pon_neg_team_members neg, pon_auction_headers_all auc, fnd_user u
where neg.auction_header_id = auc.auction_header_id and
      auc.auction_header_id = p_auction_header_id
      and neg.APPROVER_FLAG ='Y'
      and u.user_id = neg.user_id;

Line: 810

select sysdate+((l_reminder_date - sysdate) * l_timeout_factor)
into l_reminder_date
from dual;

Line: 995

select document_number
into   l_orig_document_number
from   pon_auction_headers_all
where  auction_header_id = r1.auction_header_id_orig_amend;

Line: 1023

  select 'Y'
  into l_result
  from pon_auction_headers_all auc,
       pon_neg_team_members neg
  where auc.auction_header_id = neg.auction_header_id
  and auc.auction_header_id = p_auction_header_id
  and auc.approval_status = 'INPROCESS'
  and neg.user_id = p_user_id
  and neg.approver_flag = 'Y'
  and neg.approval_status is null;

Line: 1089

/* Insert a row into history table */
UPD_AUCTION_STATUSHISTORY(l_auction_header_id, l_result,
                         l_notes,l_user_id,'USER');

Line: 1113

/* Insert a row into history table */
UPD_AUCTION_STATUSHISTORY(p_auctionHeaderId, p_decision,
                         p_notes, p_user_id,'USER');

Line: 1118

Select wf_approval_item_key
into   l_top_process_itemKey
from pon_auction_headers_all
where auction_header_id = p_auctionHeaderId;

Line: 1186

/*Update PON_NEG_TEAM_MEMEBERS APPROVAL_STATUS field */
/* Insert a row into history table */
UPD_AUCTION_STATUSHISTORY(l_auction_header_id, l_result,
                          l_notes,l_user_id,'USER');

Line: 1238

 SELECT max(action_date)
 INTO l_approve_date
 FROM pon_action_history
 WHERE object_id = l_auction_header_id
 and object_type_code = 'NEGOTIATION'
 and action_type = 'APPROVE';

Line: 1337

 SELECT max(action_date)
 INTO l_reject_date
 FROM pon_action_history
 WHERE object_id = l_auction_header_id
 and object_type_code = 'NEGOTIATION'
 and action_type = 'REJECT';

Line: 1427

 Update pon_neg_team_members
    set approval_status = decode(p_status,'APPROVE','APPROVED','REJECT','REJECTED',p_status)
    where auction_header_id = p_auction_header_id
  and user_id = p_user_id;

Line: 1432

 insert into pon_action_history
   (object_id,
   object_id2,
   object_type_code,
   sequence_num,
   action_type,
   action_date,
   action_user_id,
   action_note)
 values (
   p_auction_header_id,
   p_auction_header_id,
   'NEGOTIATION',
   0,
   decode(p_status,'APPROVED','APPROVE','REJECTED','REJECT',p_status),
   sysdate,
   p_user_id,
   p_notes
   );

Line: 1460

    update pon_auction_headers_all
    set approval_status = p_status,
    draft_locked = 'N',
    draft_locked_by = null,
    draft_locked_by_contact_id = null,
    draft_locked_date = null
    where auction_header_id = p_auction_header_id;