ORACLE FUSION CLOUD
Thursday, March 13, 2025
If your Chrome browser is opening new tabs with random content
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;
If your Chrome browser is opening new tabs with random content
If your Chrome browser is opening new tabs with random content, it may be due to adware, a browser hijacker, or an extension that you didn...
-
The following query lists all the applications related information. This query can be used to find the APPLICATION_SHORT_NAME ...
-
AR - Receipt Creation in Receivables R12 API to be used : 1. AR_RECEIPT_API_PUB Required Tables : 1. ar_receipt_methods ...
-
oracle pl sql interview questions for 3+ years experience 1. Tell me about yourself I am XXX i am postgraduate and having 3 ...