Skip to main content

Hi,

I would like to share some useful SQL codes of mine. You can share yours in the comments. 

 

  • Remove 
DECLARE
    info_ VARCHAR2(2000);
BEGIN
    ifsapp.language_sys.set_language('en');
    FOR rec_ IN (SELECT * FROM /*Table*/ p /*WHERE p.*/)
    LOOP
        ifsapp./*Api*/.remove__(info_, rec_.objid, rec_.objversion, 'DO');
    END LOOP;
END;
  • New 
DECLARE
    info_       VARCHAR2(32000) := NULL;
    objid_      VARCHAR2(32000) := NULL;
    objversion_ VARCHAR2(32000) := NULL;
    attr_       VARCHAR2(32000) := '';
BEGIN
    ifsapp.language_sys.set_language('en');
    FOR rec IN (SELECT * FROM /*Table*/ t /*where*/) LOOP
        BEGIN
            attr_ := 'ORDER_NO' || chr(31) || rec.order_no || chr(30); --Fill the attr 
            ifsapp./*Api*/.new__(info_, objid_, objversion_, attr_, 'DO');
        END;
    END LOOP;
END;
  • Modify
DECLARE
    order_no_     VARCHAR2(32000) := 'I1510';
    line_no_      VARCHAR2(32000) := '1';
    rel_no_       VARCHAR2(32000) := '1';
    line_item_no_ VARCHAR2(32000) := '0';
    new_price_    VARCHAR2(32000) := '1';
    attr_         VARCHAR2(32000) := '';
BEGIN
    ifsapp.language_sys.set_language('en');
    attr_ := 'SALE_UNIT_PRICE' || chr(31) || new_price_ || chr(30);
    ifsapp.customer_order_line_api.modify(attr_, order_no_, line_no_, rel_no_, line_item_no_);
END;
  • Insert History Record To Customer Order
DECLARE
	order_no_   VARCHAR(32000) := 'D1647';
	info_       VARCHAR2(32000) := NULL;
	objid_      VARCHAR2(32000) := NULL;
	objversion_ VARCHAR2(32000) := NULL;
	attr_       VARCHAR2(32000) := '';
	mesaj_      VARCHAR2(1000) := 'Message';
BEGIN
	FOR rec IN (SELECT * FROM customer_order WHERE order_no = order_no_) LOOP
		BEGIN
			attr_ := 'ORDER_NO' || chr(31) || rec.order_no || chr(30) || 'DATE_ENTERED' || chr(31) || to_char(SYSDATE, 'YYYY-MM-DD-HH24.MI.SS') || chr(30) ||
					 'USERID' || chr(31) || ifsapp.fnd_session_api.get_fnd_user() || chr(30) || 'MESSAGE_TEXT' || chr(31) || mesaj_ || chr(30) || 'HIST_STATE' ||
                     chr(31) || rec.objstate || chr(30);
            ifsapp.customer_order_history_api.new__(info_, objid_, objversion_, attr_, 'DO');
        END;
    END LOOP;
END;
  • Send Stream To All Users
DECLARE
    attr_       VARCHAR2(2000);
    info_       VARCHAR2(2000);
    objid_      VARCHAR2(20);
    objversion_ VARCHAR2(100);

    CURSOR openoracleusers IS
        SELECT username
          FROM oracle_account
         WHERE /*profile <> 'IFS_INTERNAL'
               AND */account_status = 'OPEN';

BEGIN
    FOR rec_ IN openoracleusers LOOP
        client_sys.clear_attr(attr_);
        client_sys.add_to_attr('TO_USER', rec_.username, attr_);
        client_sys.add_to_attr('MESSAGE',
                               'Your Message!',
                               attr_);
        client_sys.add_to_attr('STREAM_TYPE', 'Event', attr_);
        client_sys.add_to_attr('VISIBLE', fnd_boolean_api.decode('TRUE'), attr_);
        client_sys.add_to_attr('READ', fnd_boolean_api.decode('FALSE'), attr_);
        ifsapp.fnd_stream_api.new__(info_, objid_, objversion_, attr_, 'DO');
        --COMMIT;
    END LOOP;
END;
  • Copy Approval Routing To Customer Order
DECLARE
    order_no_       VARCHAR2(32000) := 'D1647';
    lu_name_        VARCHAR2(32000) := 'CustomerOrder';
    result_         VARCHAR2(32000);
    newaccessperson VARCHAR2(32000);
    newaccessgroup  VARCHAR2(32000);
BEGIN
    ifsapp.approval_routing_api.check_exist(result_, lu_name_, ifsapp.client_sys.get_key_reference(lu_name_, 'ORDER_NO', order_no_));

    IF (result_ = 'FALSE') THEN
        ifsapp.approval_routing_api.copy_app_profile(newaccessperson, newaccessgroup, lu_name_,
                                                     ifsapp.client_sys.get_key_reference('CustomerOrder', 'ORDER_NO', order_no_), 'App-Template');
    END IF;
END;

 

DECLARE
 
  info_       VARCHAR2(32000) := NULL;
  objid_      VARCHAR2(32000) := NULL;
  objversion_ VARCHAR2(32000) := NULL;
  attr_       VARCHAR2(32000) := NULL;
  action_     VARCHAR2(32000) := 'DO';
 
  CURSOR new_forecast_item IS

    select &FORECAST_ID forecast_id,
           A.project_id,
           A.activity_seq,
           Activity_API.Get_Sub_Project_Id(A.activity_seq) sub_project_id,
           Activity_API.Get_Activity_No(A.activity_seq) activity_no,
           A.control_category project_cost_element,
           PROJECT_COST_ELEMENT_API.Get_Element_Type_Db(Project_API.Get_Company(A.project_id),
                                                        control_category) item_type_db,
           Company_Finance_API.Get_Currency_Code(Project_API.Get_Company(A.project_id)) currency_code
      from PROJ_CONN_DETAILS_ALL A
     where A.control_category is not null
       and A.project_id =
           PROJECT_FORECAST_API.Get_Project_Id(&FORECAST_ID)
       and ((ABS(NVL(A.estimated, 0)) + ABS(NVL(A.planned, 0)) +
           ABS(NVL(A.planned_committed, 0)) + ABS(NVL(A.committed, 0)) +
           ABS(NVL(A.used, 0)) + ABS(NVL(A.actual, 0)) +
           ABS(NVL(A.estimated_revenue, 0)) +
           ABS(NVL(A.planned_revenue, 0)) + ABS(NVL(A.posted_revenue, 0)) +
           ABS(NVL(A.actual_revenue, 0))) > 0)
       and (A.activity_seq, A.control_category) not in
           (select activity_seq, project_cost_element
              from IFSAPP.PROJECT_FORECAST_ITEM
             where forecast_id = &FORECAST_ID
               and activity_seq is not null)
     group by A.project_id, A.activity_seq, A.control_category;
 
BEGIN
  FOR new_rec_ IN new_forecast_item LOOP
    Client_SYS.Clear_Attr(info_);
    Client_SYS.Clear_Attr(objid_);
    Client_SYS.Clear_Attr(objversion_);
    Client_SYS.Clear_Attr(attr_);
    Client_SYS.Add_To_Attr('FORECAST_ID', new_rec_.forecast_id, attr_);
    Client_SYS.Add_To_Attr('PROJECT_ID', new_rec_.project_id, attr_);
    Client_SYS.Add_To_Attr('SUB_PROJECT_ID', new_rec_.sub_project_id, attr_);
    Client_SYS.Add_To_Attr('ACTIVITY_SEQ', new_rec_.activity_seq, attr_);
    Client_SYS.Add_To_Attr('ACTIVITY_NO', new_rec_.activity_no, attr_);
    Client_SYS.Add_To_Attr('PROJECT_COST_ELEMENT', new_rec_.project_cost_element, attr_);
    Client_SYS.Add_To_Attr('CURRENCY_CODE', new_rec_.currency_code, attr_);
    Client_SYS.Add_To_Attr('ETC', '0', attr_);
    Client_SYS.Add_To_Attr('SPREAD_TYPE_DB', 'MANUAL', attr_);
    Client_SYS.Add_To_Attr('ITEM_TYPE_DB', new_rec_.item_type_db, attr_);
    Client_SYS.Add_To_Attr('EXECUTE_MATCHING', 'FALSE', attr_);
    Client_SYS.Add_To_Attr('USER_MODIFIED', 'TRUE', attr_);

    Project_Forecast_Item_API.New__(info_,objid_,objversion_,attr_,action_);

  END LOOP;
  COMMIT;
END;

Add all non forecasted line items on a project forecast activity and set ETC to ‘0’

SELECT substr(parent_subproj,2,INSTR(parent_subproj,'^',2)-2)
FROM
(
SELECT a.activity_seq,a.activity_no,a.sub_project_id,ifsapp.sub_project_api.get_total_key_path_(project_id, sub_project_id) parent_subproj
FROM ifsapp.activity a
WHERE a.activity_seq = :activity_seq
)

Get main subproject ID of an activity to collect data per project phase

Terms & ConditionsCookie settings