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 

1DECLARE
2    info_ VARCHAR2(2000);
3BEGIN
4    ifsapp.language_sys.set_language('en');
5    FOR rec_ IN (SELECT * FROM /*Table*/ p /*WHERE p.*/)
6    LOOP
7        ifsapp./*Api*/.remove__(info_, rec_.objid, rec_.objversion, 'DO');
8    END LOOP;
9END;

• New 

1DECLARE
2    info_       VARCHAR2(32000) := NULL;
3    objid_      VARCHAR2(32000) := NULL;
4    objversion_ VARCHAR2(32000) := NULL;
5    attr_       VARCHAR2(32000) := '';
6BEGIN
7    ifsapp.language_sys.set_language('en');
8    FOR rec IN (SELECT * FROM /*Table*/ t /*where*/) LOOP
9        BEGIN
10            attr_ := 'ORDER_NO' || chr(31) || rec.order_no || chr(30); --Fill the attr 
11            ifsapp./*Api*/.new__(info_, objid_, objversion_, attr_, 'DO');
12        END;
13    END LOOP;
14END;

• Modify

1DECLARE
2    order_no_     VARCHAR2(32000) := 'I1510';
3    line_no_      VARCHAR2(32000) := '1';
4    rel_no_       VARCHAR2(32000) := '1';
5    line_item_no_ VARCHAR2(32000) := '0';
6    new_price_    VARCHAR2(32000) := '1';
7    attr_         VARCHAR2(32000) := '';
8BEGIN
9    ifsapp.language_sys.set_language('en');
10    attr_ := 'SALE_UNIT_PRICE' || chr(31) || new_price_ || chr(30);
11    ifsapp.customer_order_line_api.modify(attr_, order_no_, line_no_, rel_no_, line_item_no_);
12END;

• Insert History Record To Customer Order

1DECLARE
2	order_no_   VARCHAR(32000) := 'D1647';
3	info_       VARCHAR2(32000) := NULL;
4	objid_      VARCHAR2(32000) := NULL;
5	objversion_ VARCHAR2(32000) := NULL;
6	attr_       VARCHAR2(32000) := '';
7	mesaj_      VARCHAR2(1000) := 'Message';
8BEGIN
9	FOR rec IN (SELECT * FROM customer_order WHERE order_no = order_no_) LOOP
10		BEGIN
11			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) ||
12					 'USERID' || chr(31) || ifsapp.fnd_session_api.get_fnd_user() || chr(30) || 'MESSAGE_TEXT' || chr(31) || mesaj_ || chr(30) || 'HIST_STATE' ||
13                     chr(31) || rec.objstate || chr(30);
14            ifsapp.customer_order_history_api.new__(info_, objid_, objversion_, attr_, 'DO');
15        END;
16    END LOOP;
17END;

• Send Stream To All Users

1DECLARE
2    attr_       VARCHAR2(2000);
3    info_       VARCHAR2(2000);
4    objid_      VARCHAR2(20);
5    objversion_ VARCHAR2(100);
6
7    CURSOR openoracleusers IS
8        SELECT username
9          FROM oracle_account
10         WHERE /*profile <> 'IFS_INTERNAL'
11               AND */account_status = 'OPEN';
12
13BEGIN
14    FOR rec_ IN openoracleusers LOOP
15        client_sys.clear_attr(attr_);
16        client_sys.add_to_attr('TO_USER', rec_.username, attr_);
17        client_sys.add_to_attr('MESSAGE',
18                               'Your Message!',
19                               attr_);
20        client_sys.add_to_attr('STREAM_TYPE', 'Event', attr_);
21        client_sys.add_to_attr('VISIBLE', fnd_boolean_api.decode('TRUE'), attr_);
22        client_sys.add_to_attr('READ', fnd_boolean_api.decode('FALSE'), attr_);
23        ifsapp.fnd_stream_api.new__(info_, objid_, objversion_, attr_, 'DO');
24        --COMMIT;
25    END LOOP;
26END;

• Copy Approval Routing To Customer Order

1DECLARE
2    order_no_       VARCHAR2(32000) := 'D1647';
3    lu_name_        VARCHAR2(32000) := 'CustomerOrder';
4    result_         VARCHAR2(32000);
5    newaccessperson VARCHAR2(32000);
6    newaccessgroup  VARCHAR2(32000);
7BEGIN
8    ifsapp.approval_routing_api.check_exist(result_, lu_name_, ifsapp.client_sys.get_key_reference(lu_name_, 'ORDER_NO', order_no_));
9
10    IF (result_ = 'FALSE') THEN
11        ifsapp.approval_routing_api.copy_app_profile(newaccessperson, newaccessgroup, lu_name_,
12                                                     ifsapp.client_sys.get_key_reference('CustomerOrder', 'ORDER_NO', order_no_), 'App-Template');
13    END IF;
14END;