ORACLE FUSION CLOUD
Saturday, May 4, 2024
Types of animations
How to convert normal video to animation video
Thursday, March 30, 2023
How to improve blog performance
Sunday, October 9, 2022
Query to Get Profile value at any level
Query to Get Profile value at any level
SELECT
distinct pot.user_profile_option_name Profile,
DECODE (
a.profile_option_value, '1', '1 (may be "Yes")',
'2', '2 (may be "No")', a.profile_option_value
) Value,
DECODE (
a.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User',
'????'
) Level_identifier,
DECODE (
a.level_id, 10002, e.application_name,
10003, c.responsibility_name, 10004,
d.user_name, '-'
) Level_Name
FROM
applsys.fnd_application_tl e,
applsys.fnd_user d,
applsys.fnd_responsibility_tl c,
applsys.fnd_profile_option_values a,
applsys.fnd_profile_options b,
applsys.fnd_profile_options_tl pot
WHERE
1 = 1
AND UPPER (pot.user_profile_option_name) LIKE UPPER ('%&v_profile%')
AND pot.profile_option_name = b.profile_option_name
AND pot.language = userenv('lang')
AND b.application_id = a.application_id(+)
AND b.profile_option_id = a.profile_option_id(+)
AND a.level_value = c.responsibility_id(+)
AND a.level_value = d.user_id(+)
AND a.level_value = e.application_id(+)
AND e.language(+) = userenv('lang')
AND (
UPPER (e.application_name) LIKE UPPER ('%&appname_respname_username%')
OR UPPER (c.responsibility_name) LIKE UPPER (
'%&&appname_respname_username%'
)
OR UPPER (d.user_name) LIKE UPPER (
'%&&appname_respname_username%'
)
)
ORDER BY
Profile,
Level_identifier,
Level_name,
Value
Bom Explosion Query
Below query can be handy to explode and Query BOM Details
SELECT
DISTINCT LPAD (' ', LEVEL * 2) || LEVEL order_level,
msib.segment1 assembly_item,
msib.description assembly_description,
msib.inventory_item_status_code assembly_item_status,
SYS_CONNECT_BY_PATH (msib2.segment1, '/') PATH,
msib2.segment1 AS component_item,
msib2.description component_item_description,
msib2.inventory_item_status_code component_item_status,
bic.item_num,
bic.operation_seq_num,
bic.component_quantity
FROM
bom.bom_components_b bic,
bom.bom_structures_b bom,
inv.mtl_system_items_b msib,
inv.mtl_system_items_b msib2,
mtl_parameters mp
WHERE
1 = 1
AND bic.bill_sequence_id = bom.bill_sequence_id
AND SYSDATE BETWEEN bic.effectivity_date
AND Nvl(bic.disable_date, SYSDATE)
AND bom.assembly_item_id = msib.inventory_item_id
AND bom.organization_id = msib.organization_id
AND bic.component_item_id = msib2.inventory_item_id
AND bom.organization_id = msib2.organization_id
AND mp.organization_id = msib.organization_id
AND mp.organization_code = : p_org_code
/* organization here */
AND bom.alternate_bom_designator IS NULL START WITH msib.segment1 = : p_item_number
/* component item to be used here */
CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id
ORDER BY
PATH
Oracle ERP - Hierarchical BOM Query
The query below shows the use of hierarchical query syntax to list the parts of an assembly in a typical bill of material (BOM). The parts are setup in a hierarchical (assembly - component/part) or indented manner. |
SELECT
CONNECT_BY_ROOT q_bom.assembly_num root_assembly,
q_bom.assembly_num,
q_bom.component_num,
q_bom.qty,
SUBSTR(
SYS_CONNECT_BY_PATH(q_bom.assembly_Num, ' <-- '),
5
) assembly_path,
LEVEL bom_level,
CONNECT_BY_ISCYCLE is_cycle
FROM
(
SELECT
mb1.segment1 assembly_num,
mb2.segment1 component_num,
bc.component_quantity qty
FROM
bom.bom_components_b bc,
bom.bom_structures_b bs,
inv.mtl_system_items_b mb1,
inv.mtl_system_items_b mb2
WHERE
bs.assembly_item_id = mb1.inventory_item_id
AND bc.component_item_id = mb2.inventory_item_id
AND bc.bill_sequence_id = bs.bill_sequence_id
AND mb1.organization_id = mb2.organization_id
AND bs.organization_id = mb2.organization_id
AND bc.disable_date Is Null
AND bs.alternate_bom_designator IS NULL
AND mb1.organization_id = 90101110109
) q_bom START WITH q_bom.assembly_Num = '&i_comp' CONNECT BY NOCYCLE PRIOR q_bom.component_num = q_bom.assembly_num ORDER SIBLINGS BY q_bom.assembly_Num;
Converting rows into columns using pivot function
we can convert rows into columns using the pivot function
let us consider the following data as sample data
DEPTNO ENAME JOB
10 ALLEN ANALYST
10 JONES CLERK
10 FORD MANAGER
10 ABCD PRESIDENT
10 CLARK SALESMAN
20 MILLER ANALYST
20 SMITH CLERK
20 WARD MANAGER
20 efgh PRESIDENT
20 MARTIN SALESMAN
30 SCOTT ANALYST
30 TURNER CLERK
30 ADAMS MANAGER
30 BLAKE PRESIDENT
30 KING SALESMAN
now create a table as emp_jobs with this data
using pivot function
we are converting rows into columns
select * from emp_jobs pivot( min(ename) for job in ('PRESIDENT','MANAGER','CLERK','ANALYST','SALESMAN'));
the output is in the form
DEPTNO PRESIDENT' MANAGER' CLERK' ANALYST' SALESMAN'
20 efgh WARD SMITH MILLER MARTIN
10 ABCD FORD JONES ALLEN CLARK
30 BLAKE ADAMS TURNER SCOTT KING
but while using pivot function aggregate function should be used
we can also achieve this conversion without using pivot function using this sql query
select
a1.deptno,
a1.ename AS PRESIDENT,
a2.ename AS MANAGER,
a3.ename AS CLERK
from
emp_jobs a1,
emp_jobs a2,
emp_jobs a3
where
a1.deptno=a2.deptno
and a2.deptno=a3.deptno
and a3.deptno=a1.deptno
and a1.job = 'PRESIDENT'
and a2.job = 'MANAGER'
and a3.job ='CLERK';
Types of animations
Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...
-
Approval Management Engine FAQ 1. AME – What is it? i. AME is a simple to use Rules Engine for Defining Approval Policy ii. AME is ...
-
Register Custom Tables in Oracle Apps Say you have a custom table called “ERPS_EMPL...
-
OU & ORG ID & ORG CODE & ORG NAME ================================== select OPERATING_UNIT,ORGANIZATION_ID,ORGANIZATION_CODE...