ORACLE FUSION CLOUD
Monday, March 31, 2025
Drilldown from GL to Inventory Transactions, Inv to gl Link Query R12,Inventory to General ledger links, r12 ,Inv to gl drill down
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