FNDLOAD script for value set with values

DOWNLOAD
=========
FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct filename.ldt VALUE_SET FLEX_VALUE_SET_NAME="valueset name"

UPLOAD
======
FNDLOAD apps/appsdev 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct filename.ldt

PLSQL block with bulk collect and limit

DECLARE
CURSOR get_item_det_cur (
p_in_num_from_itemid IN NUMBER,
p_in_num_to_itemid IN NUMBER
)
IS
SELECT msib.inventory_item_id, msib.organization_id
FROM mtl_system_items_b msib
WHERE msib.segment2 = msib.attribute5
AND msib.organization_id = 81
AND msib.inventory_item_id BETWEEN p_in_num_from_itemid
AND p_in_num_to_itemid;

TYPE item_det_tbl IS TABLE OF get_item_det_cur%ROWTYPE
INDEX BY BINARY_INTEGER;

l_item_det_tbl item_det_tbl;
l_item_id NUMBER;
l_org_id NUMBER;
l_cnt NUMBER := 0;
BEGIN
OPEN get_item_det_cur (1174281, 1174500);

LOOP
l_cnt := l_cnt + 1;
DBMS_OUTPUT.put_line ('l_cnt==>' || l_cnt);

FETCH get_item_det_cur
BULK COLLECT INTO l_item_det_tbl LIMIT 10;

FOR idx IN l_item_det_tbl.FIRST .. l_item_det_tbl.LAST
LOOP
l_item_id := l_item_det_tbl (idx).inventory_item_id;
l_org_id := l_item_det_tbl (idx).organization_id;
DBMS_OUTPUT.put_line ('l_item_id==>' || l_item_id);
DBMS_OUTPUT.put_line ('l_org_id==>' || l_org_id);
END LOOP;
END LOOP;

CLOSE get_item_det_cur;
END;

SQL to find which query is running while in the concurrent request

SELECT vst.sql_text, SYSDATE, fcr.request_id, fcp.concurrent_program_name,
fcr.oracle_process_id traceid, fcr.requested_start_date,
fcr.actual_start_date, fcr.actual_completion_date,
fcr.status_code status,
ROUND ( ( NVL (fcr.actual_completion_date, SYSDATE)
- fcr.actual_start_date
)
* 24
* 60,
2
) run_time,
vst.sql_text, vs.SID, vs.serial#, vs.status, fcr.parent_request_id,
fcr.os_process_id
FROM v$sqlarea vst,
v$session vs,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_requests fcr
WHERE vst.address(+) = vs.sql_address
AND vs.process(+) = fcr.os_process_id
AND fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcr.request_id = --Request ID--;

Concurrent Program Specific SQL Queries

To fetch responsibility assigned to a concurrent program
===========================================================
select resp.responsibility_name
from fnd_responsibility_vl resp, fnd_request_group_units rgu, fnd_concurrent_programs_vl prog
where resp.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = prog.concurrent_program_id
AND rgu.unit_application_id = prog.application_id
AND prog.user_concurrent_program_name = ;


To fetch parameters assigned to a concurrent program
=====================================================

select cols.end_user_column_name,
cols.form_left_prompt,
fvs.flex_value_set_name,
cols.default_value
from fnd_descr_flex_col_usage_vl cols,
fnd_flex_value_sets fvs,
fnd_concurrent_programs_vl prog
where cols.flex_value_set_id = fvs.flex_value_set_id
AND cols.descriptive_flexfield_name = '$SRS$.' || prog.concurrent_program_name
And prog.user_concurrent_program_name like ;


To fetch details of executables and concurrent programs
===========================================================

select
prog.user_concurrent_program_name "program name",
prog.concurrent_program_name "program short name",
appl.application_name "program application name",
prog.description "program description",
exe.executable_name "executable name",
exe.execution_file_name "executable file name",
exe.description "executable description",
decode( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader', exe.execution_method_code) "execution method"
from
fnd_executables exe,
fnd_application_tl appl,
fnd_concurrent_programs_vl prog
where prog.application_id = appl.application_id
AND exe.executable_id = prog.executable_id
AND prog.user_concurrent_program_name =
AND prog.concurrent_program_name =
AND exe.execution_file_name =

Find a concurrent program's trace file

SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
FROM
fnd_concurrent_requests req
,v$session ses
,v$process proc
,v$parameter dest
,v$parameter dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
AND req.request_id = &request
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id

How to make synonym status from invalid to valid

ALTER SYNONYM <> COMPILE;

Address Formatting using HZ_LOCATIONS.LOCATION_ID

HZ_FORMAT_PUB.format_address (
-- input parameters
p_location_id IN NUMBER,
p_style_code IN VARCHAR2,
p_style_format_code IN VARCHAR2,
p_line_break IN VARCHAR2,
p_space_replace IN VARCHAR2,
-- optional context parameters
p_to_language_code IN VARCHAR2,
p_country_name_lang IN VARCHAR2,
p_from_territory_code IN VARCHAR2,
-- output parameters
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_formatted_address OUT NOCOPY VARCHAR2,
x_formatted_lines_cnt OUT NOCOPY NUMBER,
x_formatted_address_tbl OUT NOCOPY string_tbl_type
);
Pass LOCATION_ID to this procedure and it returns formatted address through OUT parameter X_FORMATTED_ADDRESS. You may also pass line break character CHR(10) so that different address lines are returned with carriage return at appropriate places.