Chitika Add

Friday, October 14

How to use regexp_substr

regexp_substr cabe used to transpose the column values as rows
In the below example PRODUCT_TAB is a table ename and product are columns

PRODUCT_TAB
===========
ename        product
B               1,2,3


Product column has data like that only separated by commas

Output should be like this.

Ename     Product
A             1
A             2
A             3
B             1
B             2
B             3
 
If you want the above desired output please use the below solution

SELECT DISTINCT EMP,regexp_substr (product, '[^,]+', 1, level)
product
FROM product_tab
    CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (product, '[^,]+'))  + 1
       ORDER BY 1;

Wednesday, October 5

SELECT fcr.request_id,
DECODE (fcr.phase_code,
'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', fl_pend.meaning),
fl_pend.meaning
) phase,
DECODE (fcr.phase_code,
'P', DECODE (fcr.hold_flag,
'Y', 'On Hold',
DECODE (SIGN (fcr.requested_start_date - SYSDATE),
1, 'Scheduled',
fl_stat.meaning
)
),
fl_stat.meaning
) status,
fcpt.user_concurrent_program_name, fcr.increment_dates,
fcr.resubmit_interval, fcr.resubmit_interval_unit_code,
fcr.resubmit_interval_type_code, parent_request_id,
fcr.requested_start_date, fu.user_name requested_by
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcpt,
fnd_lookups fl_pend,
fnd_lookups fl_stat,
fnd_user fu
WHERE 1 = 1
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcpt.LANGUAGE = USERENV ('LANG')
AND fcr.phase_code = fl_pend.lookup_code
AND fl_pend.lookup_type = 'CP_PHASE_CODE'
AND fcr.status_code = fl_stat.lookup_code
AND fl_stat.lookup_type = 'CP_STATUS_CODE'
AND fl_pend.meaning != 'Completed'
AND fu.user_id = fcr.requested_by
ORDER BY fcr.request_id DESC

Script to find Application Login URL from backend

select home_url from icx_parameters;

Creating Install Base for all the Sales Order (for which it is missing)

CREATE OR REPLACE PACKAGE alloracletech_ib_pkg
IS
PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER);
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2);
END alloracletech_ib_pkg;
/

+++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE OR REPLACE PACKAGE BODY alloracletech_ib_pkg
IS

PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER)
IS
/************************************************************************
Purpose : This procedure creates IB for all the order which are missed.
*************************************************************************/

l_header_id NUMBER;

l_mtl_txn_id NUMBER;
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;

CURSOR c_ib
IS
SELECT -- oeh.order_number,
oel1.line_id
-- oeh.creation_date,
-- msib1.inventory_item_id,
-- msib1.segment1
FROM oe_order_lines_all oel1,
mtl_system_items_b msib1,
oe_order_headers_all oeh
WHERE oel1.ordered_item = msib1.segment1
AND msib1.comms_nl_trackable_flag = 'Y'
AND msib1.shippable_item_flag = 'Y'
AND msib1.organization_id = 22
AND oeh.header_id = oel1.header_id
AND oel1.flow_status_code = 'CLOSED'
AND oel1.line_category_code = 'ORDER'
AND oeh.order_type_id = 1008
-- AND oel1.line_id IN (5599900, 5742086)
-- AND oeh.order_number IN ( )
AND oeh.shipping_method_code IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM csi.csi_item_instances cii
WHERE cii.last_oe_order_line_id = oel1.line_id);
BEGIN
FOR i IN c_ib
LOOP
BEGIN
SELECT transaction_id
INTO l_mtl_txn_id
FROM mtl_material_transactions
WHERE trx_source_line_id = i.line_id
AND transaction_type_id = 33;

-- dbms_output.put_line(i.order_number||' '||i.line_id);
csi_inv_txn_hook_pkg.posttransaction
(p_header_id => l_header_id,
p_transaction_id => l_mtl_txn_id,
x_return_status => l_return_status
);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
WRITE ('L', 'No Transaction ID for the Line ID : ' || i.line_id );
WHEN TOO_MANY_ROWS THEN
WRITE ('L','More then one Transaction ID for the Line ID : ' || i.line_id );
WHEN OTHERS THEN
WRITE ('L',
'Error in LineID :' || i.line_id || CHR (10) || SQLCODE || ' : ' || SQLERRM
);
END;
END LOOP;
END create_install_base;

/************************************************************************/
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2) IS
/************************************************************************
Purpose : This procedure writes to the output file or log file depending
on the parameter p_type passed.
*************************************************************************/
BEGIN
IF p_type = 'L'
THEN
fnd_file.put_line (fnd_file.LOG, p_message);
ELSIF p_type = 'O'
THEN
fnd_file.put_line (fnd_file.output, p_message);
END IF;
END WRITE;
END alloracletech_ib_pkg;
/

Use the following to Execute the Procedure.
begin
alloracletech_ib_pkg.create_install_base;
end;
/

The following query can be used to find out whether Install Base (IB) created or not for the Order line.

select * from csi.csi_item_instances
where last_oe_order_line_id IN (Your Order Line ID);

Script to find the list of Forms used in application.

SELECT DISTINCT
D.USER_NAME,
E.RESPONSIBILITY_NAME,
TO_CHAR(A.START_TIME,'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(A.END_TIME,'DD-MON-YYYY HH24:MI:SS'),
G.USER_FORM_NAME,
TO_CHAR(F.START_TIME,'DD-MON-YYYY HH24:MI:SS') FORM_START_TIME,
TO_CHAR(F.END_TIME,'DD-MON-YYYY HH24:MI:SS') FORM_END_TIME,
F.START_TIME FORM_START_TIME_ORDER
FROM
FND_LOGIN_RESPONSIBILITIES A,
FND_RESPONSIBILITY B,
FND_LOGINS C,
FND_USER D,
FND_RESPONSIBILITY_TL E,
FND_LOGIN_RESP_FORMS F,
FND_FORM_TL G
WHERE
1 =1
AND A.RESPONSIBILITY_ID = B.RESPONSIBILITY_ID
AND A.RESPONSIBILITY_ID = E.RESPONSIBILITY_ID
AND B.RESPONSIBILITY_ID = E.RESPONSIBILITY_ID
AND A.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND C.LOGIN_ID = A.LOGIN_ID
AND A.LOGIN_ID = F.LOGIN_ID
AND C.LOGIN_ID = F.LOGIN_ID
AND C.USER_ID = D.USER_ID
AND G.FORM_ID = F.FORM_ID
AND E.LANGUAGE = 'US'
AND G.LANGUAGE = 'US'
AND B.APPLICATION_ID = G.APPLICATION_ID
AND E.RESPONSIBILITY_NAME LIKE '%'
AND D.USER_NAME LIKE 'USERNAME_TO_BE_CHECKED'
AND A.START_TIME > SYSDATE - 90
ORDER BY
FORM_START_TIME_ORDER DESC /

Script to find Scheduled concurrent programs and request sets

SELECT
A.REQUEST_ID,
B.USER_NAME,
C.USER_CONCURRENT_PROGRAM_NAME,
C.ARGUMENT_TEXT
FROM
FND_CONCURRENT_REQUESTS A,
FND_USER B,
FND_CONC_REQ_SUMMARY_V C
WHERE
B.USER_ID = A.REQUESTED_BY
AND A.REQUEST_ID = C.REQUEST_ID
AND A.REQUESTED_START_DATE > SYSDATE
AND A.HOLD_FLAG = 'N'
AND A.STATUS_CODE IN ('Q','I')
AND B.USER_NAME LIKE '%'
AND A.DESCRIPTION LIKE '%'
ORDER BY
A.REQUEST_ID