APPS.PON_AUCTION_APPROVAL_PKG SQL Statements

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

Line: 30

FUNCTION Get_Emd_Update_Url (pn_aunction_header_id NUMBER)
RETURN VARCHAR2
IS
l_ext_fwk_agent     VARCHAR2(240);

Line: 35

l_emd_update_link   VARCHAR2(4000);

Line: 61

    l_emd_update_link := l_ext_fwk_agent || '?'|| 'page=/oracle/apps/pon/emd/creation/webui/ponEmdUpdatePG'
     || '&' ||'akRegionApplicationId=396' || '&' ||'OAHP=PON_EMD_ADMIN_HOME'||'&'||'OASF=PON_EMD_UPDATE'
     ||'&'||'OAPB=PON_SOURCING_BRAND'||'&'|| 'notificationId=&' || '#NID'  ||'&'||'language_code=' || fnd_global.current_language;

Line: 67

   l_emd_update_link :=  '';

Line: 70

  Return l_emd_update_link;

Line: 72

END Get_Emd_Update_Url;

Line: 79

  SELECT item_type,
         item_key
  FROM   wf_item_activity_statuses
  WHERE  notification_id  = pn_notification_id;

Line: 84

  SELECT SUBSTR(context,1,INSTR(context,':',1)-1),
         SUBSTR(context,INSTR(context,':')+1,
                       (INSTR(context,':',1,2) - INSTR(context,':')-1)),
         message_name
  FROM   wf_notifications
  WHERE  notification_id   = pn_notification_id;

Line: 134

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
--FOR ERIC TEST ONLY,BEGIN
---------------------------------------------------------
--and approver_flag='Y';

Line: 158

      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: 206

   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: 240

   select user_id
     into l_user_id
     from fnd_user
    where user_name = p_user_name;

Line: 248

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: 257

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

Line: 262

/* 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: 281

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

Line: 303

   select user_id
     into l_user_id
     from fnd_user
    where user_name = p_user_name;

Line: 310

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: 320

/* 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: 342

SELECT wf_approval_item_key
INTO l_item_key_main_process
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;

Line: 359

end UPDATE_NOTIF_ONLINE;

Line: 363

   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: 387

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: 398

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

Line: 402

end UPDATE_DOC_TO_CANCELLED;

Line: 407

    insert into vhk_dummy values(msg);

Line: 467

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: 517

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

Line: 618

            select dt.doctype_group_name,auh.doctype_id
            into l_doctype_group_name,l_doctype_id
            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: 742

select document_number
into   l_orig_document_number
from   pon_auction_headers_all
where  auction_header_id = l_auction_header_id_orig_amend;

Line: 751

   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: 784

   select user_id
     into l_submit_user_id
     from fnd_user
    where user_name = p_submit_user_name;

Line: 806

/* 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: 870

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 neg.MENU_NAME <> 'EMD_ADMIN' --FOR ERIC TEST ONLY
      and u.user_id = neg.user_id;

Line: 1043

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

Line: 1250

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

Line: 1287

      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.MENU_NAME = 'EMD_ADMIN'
         and neg.approver_flag = 'Y'
         AND neg.approval_status IS NULL;

Line: 1303

      	--INSERT INTO ERIC_LOG VALUES ('EMD Is_Valid_Response error : '||ls_error, 'l_result = '|| l_result,'p_user_id ='||p_user_id,'p_auction_header_id = '||p_auction_header_id,'','','','');--for eric test only

Line: 1311

     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.MENU_NAME <> 'EMD_ADMIN' --FOR ERIC TEST ONLY
     and neg.approver_flag = 'Y'
     and neg.approval_status is null;

Line: 1420

/* Insert a row into history table */
UPD_AUCTION_STATUSHISTORY(l_auction_header_id,
                          l_result,
                          l_notes,
                          l_user_id,
                          'USER',
                          l_signed_date);-- Adding by amundhra for Federal CLM, signed date

Line: 1451

/* Insert a row into history table */
UPD_AUCTION_STATUSHISTORY(p_auctionHeaderId, p_decision,
                         p_notes, p_user_id,'USER',NULL);-- Adding by amundhra for Federal CLM, signed date will be null here

Line: 1457

Select wf_approval_item_key
into   l_top_process_itemKey
from pon_auction_headers_all
where auction_header_id = p_auctionHeaderId;

Line: 1561

/*Update PON_NEG_TEAM_MEMEBERS APPROVAL_STATUS field */
/* Insert a row into history table */

--changed by amundhra for Federal CLM
--added l_signed_date in the IN PARAM
UPD_AUCTION_STATUSHISTORY(l_auction_header_id,
                          l_result,
                          l_notes,
                          l_user_id,
                          'USER',
                          l_signed_date); ---- Adding by amundhra for Federal CLM

Line: 1588

SELECT effective_date
INTO l_effective_date
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;

Line: 1602

UPDATE  pon_auction_headers_all
SET effective_date = p_signed_date,
    signed_date = p_signed_date
WHERE auction_header_id = p_auction_header_id;

Line: 1609

UPDATE pon_auction_headers_all
SET signed_date = p_signed_date
WHERE auction_header_id = p_auction_header_id;

Line: 1666

 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: 1791

 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: 1908

	--INSERT INTO ERIC_LOG VALUES ('ENTER USER Branch','','','','','','','');  --for eric test only

Line: 1910

 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
  AND approver_flag = 'Y'                   -- for eric test only
  AND MENU_NAME<>'EMD_ADMIN';  -- for eric test only

Line: 1921

 insert into pon_action_history
   (object_id,
   object_id2,
   object_type_code,
   sequence_num,
   action_type,
   action_date,
   action_user_id,
   action_note,
   signed_date)   --Added by amundhra for Federal CLM, inserting into the action_history table
 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,
   p_signed_date  --added by amundhra for Federal CLM, digned date needs to be stored in action history
   );

Line: 1952

    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
       AND MENU_NAME='EMD_ADMIN'
       AND approver_flag = 'Y';

Line: 1969

  INSERT INTO pon_action_history
  	(object_id
  	,object_id2
  	,object_type_code
  	,sequence_num
  	,action_type
  	,action_date
  	,action_user_id
  	,action_note,
    signed_date)
  VALUES
  	(p_auction_header_id
  	,p_auction_header_id
  	,'NEGOTIATION-EMD'
  	,0
  	,decode(p_status
               ,'APPROVED'
               ,'APPROVE'
               ,'REJECTED'
               ,'REJECT'
               ,p_status
               )
  	,SYSDATE
  	,p_user_id
  	,p_notes
    ,p_signed_date);  -- added signed date , will be null here

Line: 2009

    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;

Line: 2161

	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 MENU_NAME='EMD_ADMIN'
	  AND approver_flag = 'Y' --for eric test only
	  AND u.user_id = neg.user_id;

Line: 2329

      SELECT SYSDATE + ((l_reminder_date - SYSDATE) * l_timeout_factor)
        INTO l_reminder_date
        FROM dual;

Line: 2418

      l_review_emd_changes_url :=  Get_Emd_Update_Url (l_auction_header_id ); --FOR ERIC TEST ONLY

Line: 2516

      SELECT document_number
        INTO l_orig_document_number
        FROM pon_auction_headers_all
       WHERE auction_header_id = r1.auction_header_id_orig_amend;

Line: 2609

    /* Insert a row into history table */
    UPD_AUCTION_STATUSHISTORY(l_auction_header_id
    			 ,l_result
    			 ,l_notes
    			 ,l_user_id
    			 ,'EMD'
           ,NULL);  -- Adding by amundhra for Federal CLM, signed date will be null here

Line: 2697

    /*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
    			 , 'EMD'
    			 ,NULL); -- Adding by amundhra for Federal CLM, signed date will be null here

Line: 2905

	select wf_approval_item_key
	into l_itemKey
	from pon_auction_headers_all
	where auction_header_id = p_auction_header_id;

Line: 2927

	select document_number
	into x_amend_doc_number
	from pon_auction_headers_all
	where
	auction_header_id = p_auction_header_id;

Line: 2973

    select wf_approval_item_key
    into l_itemKey
    from pon_auction_headers_all
    where auction_header_id = p_auction_header_id;

Line: 2999

    select nvl(cancel_amendment,'N')
    into resultout
    from pon_auction_headers_all
    where
    auction_header_id = l_auction_header_id;

Line: 3039

	PON_NEG_UPDATE_PKG.CANCEL_FEDERAL_NEGOTIATION(l_auction_header_id,
						      'Y',
						      l_cancel_note_to_supplier,
						      fnd_global.user_id,
						      l_document_number,
						      l_error_code);

Line: 3047

	        select pon1.auction_header_id,pon1.document_number,pon1.revision
		into l_auction_id_prev_doc,l_document_number,l_revision
		from pon_auction_headers_all pon1
		where
		pon1.auction_header_id = (select pon2.auction_header_id_prev_amend
				          from pon_auction_headers_all pon2
				          where pon2.auction_header_id = l_auction_header_id);

Line: 3109

	select wf_approval_item_key
	into l_itemKey
	from pon_auction_headers_all
	where auction_header_id = p_auction_header_id;

Line: 3127

	select document_number
	into x_amend_doc_number
	from pon_auction_headers_all
	where
	auction_header_id = p_auction_header_id;