Sharing Useful Scripts-Snippets-Queries

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;