ORACLE FUSION CLOUD
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';
Query to get Purchase Order and requisition in Oracle Fusion
SELECT
POH.PO_HEADER_ID,
POH.SEGMENT1,
PRHA.REQUISITION_NUMBER
FROM
PO_HEADERS_ALL POH,
PO_DISTRIBUTIONS_ALL PDA,
POR_REQ_DISTRIBUTIONS_ALL PRDA,
POR_REQUISITION_LINES_ALL PRLA,
POR_REQUISITION_HEADERS_ALL PRHA
WHERE
1 = 1
AND POH.PO_HEADER_ID = PDA.PO_HEADER_ID
AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND PRDA.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID
AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID
AND PRHA.REQUISITION_NUMBER = < Requsition_number >
Query to get AP Invoice lines Tax details in Oracle Fusion
Query to get AP Invoice lines Tax details in Oracle Fusion
SELECT
aia.invoice_num,
xep.name Legal_Entity_Name,
psv.vendor_name party,
aila.line_number Line_Ref,
aila.line_type_lookup_code,
gcc.segment1 Distribution_entity,
zl.tax_amt Tax_amount,
zl.tax_regime_code Tax_regime,
zl.tax_jurisdiction_code Tax_Juridiction,
zl.taxable_amt TaxableAmt,
ZE.exception_reason_code Exemption_Reason,
ZE.tax_rate_code Exemption_Rate,
zrb.country_code Country_of_taxation,
zl.tax_line_number Tax_Line_Ref,
zl.tax,
zl.tax_rate_code,
zl.tax_rate
FROM
ap_invoices_all aia,
poz_suppliers_v psv,
xle_entity_profiles xep,
ap_invoice_lines_all aila,
gl_code_combinations gcc,
ap_invoice_distributions_all aida,
zx_lines zl,
zx_status_b zsb,
zx_exceptions ze,
zx_regimes_b zrb
WHERE
1 = 1
AND psv.vendor_id = aia.vendor_id
AND aia.legal_entity_id = xep.legal_entity_id(+)
AND aia.invoice_id = aila.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aia.invoice_id = zl.trx_id(+)
AND aila.line_number = zl.trx_line_number(+)
AND zl.application_id(+) = 200
AND ze.tax_exception_id(+) = zl.tax_exception_id
AND zsb.tax_status_id = zl.tax_status_id
AND zl.tax_regime_id = zrb.tax_regime_id
AND aida.dist_code_combination_id = gcc.code_combination_id
AND (
aida.line_type_lookup_code = 'MISCELLANEOUS'
OR aida.line_type_lookup_code = 'FREIGHT'
OR aida.line_type_lookup_code = 'ITEM'
)
AND zsb.tax_status_code = 'EXEMPT'
How to improve blog performance
Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...
-
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...
-
AR to HZ Table Link SELECT HP.PARTY_NAME CUSTOMER_NAME FROM HZ_PARTIES HP, RA_CUSTOMER_TRX_ALL RCTA, HZ_C...