Query to get item on hand by org wise

 Hello Below is the query to get the on hand by org wise.

Sample Output: 

-----------------------------------------------------------------------------------------------------------------------------

SELECT iop.organization_code,
esi.item_number,
sum(ioqd.TRANSACTION_QUANTITY) on_hand,
esi.description,
ESI.PRIMARY_UOM_CODE UOM
FROM egp_system_items_vl esi,
inv_org_parameters IOP,
inv_onhand_quantities_detail ioqd
WHERE 1 = 1
AND ESI.organization_id = IOP.organization_id
AND ioqd.inventory_item_id = esi.INVENTORY_ITEM_ID
AND ioqd.ORGANIZATION_ID = esi.ORGANIZATION_ID
AND esi.item_number = nvl(:p_item, esi.item_number)
AND iop.organization_code = nvl(:p_org, iop.organization_code)
GROUP BY iop.organization_code,
esi.item_number,
esi.description,
ESI.PRIMARY_UOM_CODE  
ORDER BY iop.organization_code,
esi.item_number

-----------------------------------------------------------------------------------------------------------------------------

                                                                    ** Thank You **

Query to get Item on hand & Item Cost (Cost Accounting)

 Hello Below is the query to get the item on hand quantity & item cost from the Cost Accounting.

Sample Output:

-----------------------------------------------------------------------------------------------------------------------------

WITH costing_details
     AS (SELECT *
         FROM   (SELECT Trunc(cpc.unit_cost_average, 4) unit_cost_average,
                        cpc.uom_code                    AS uom,
                        cpc.currency_code               AS curr,
                        vub.val_unit_id,
                        vub.val_unit_code,
                        cicp.inventory_item_id          item_id,
                        ccio.inv_org_id,
                        Rank()
                          OVER (
                            partition BY cicp.cost_org_id, cicp.cost_book_id,
                          cpc.inventory_item_id,
                          cpc.val_unit_id
                            ORDER BY cpc.cost_date DESC, cpc.eff_date DESC,
                          cpc.transaction_id
                          DESC )
                                                        row_num,
                        iop.organization_code           org_name,
                        cco.cost_org_name
                 FROM   cst_cost_inv_orgs ccio,
                        cst_item_cost_profiles cicp,
                        cst_cost_profiles_b ccp,
                        cst_val_units_b vub,
                        cst_val_structures_b vsb,
                        cst_val_unit_details vud,
                        cst_val_unit_combinations vuc,
                        cst_perpavg_cost cpc,
                        cst_cost_orgs_v cco,
                        inv_org_parameters iop,
                        cst_transactions ct
                 WHERE  1 = 1
                        AND ct.cost_org_id = cpc.cost_org_id
                        AND ct.cost_book_id = cpc.cost_book_id
                        AND ct.val_unit_id = cpc.val_unit_id
                        AND ct.transaction_id = cpc.transaction_id
                        AND ccio.cost_org_id = cicp.cost_org_id
                        AND ccp.cost_profile_id = cicp.asset_cost_profile_id
                        AND vub.cost_org_id = cicp.cost_org_id
                        AND vub.cost_book_id = cicp.cost_book_id
                        AND vub.val_structure_id = ccp.val_structure_id
                        AND vub.val_unit_id = vud.val_unit_id
                        AND vud.val_unit_combination_id =
                            vuc.val_unit_combination_id
                        AND ccp.val_structure_id = vsb.val_structure_id
                        AND vsb.structure_instance_number =
                            vuc.structure_instance_number
                        AND vub.cost_org_id = cco.cost_org_id
                        AND iop.organization_id = ccio.inv_org_id
                        AND vuc.cost_org_code = cco.cost_org_code
                        AND vub.val_structure_id = ccp.val_structure_id
                        AND vub.val_unit_id = vud.val_unit_id
                        AND vub.val_structure_id = vud.val_structure_id
                        AND vub.cost_book_id = vud.cost_book_id
                        AND vud.val_unit_combination_id =
                            vuc.val_unit_combination_id
                        AND Nvl(vuc.inv_org_code, iop.organization_code) =
                            iop.organization_code
                        AND cpc.cost_org_id = cicp.cost_org_id
                        AND cpc.cost_book_id = cicp.cost_book_id
                        AND cpc.inventory_item_id = cicp.inventory_item_id
                        AND cpc.val_unit_id = vub.val_unit_id
                        AND Trunc(cpc.cost_date) <= Trunc(ct.cost_date)
                        AND cpc.cost_book_id = (SELECT ccb.cost_book_id
                                                FROM   cst_cost_org_books ccb
                                                WHERE
                            cicp.cost_org_id = ccb.cost_org_id
                            AND ccb.primary_book_flag = 'Y')
                        AND vsb.val_structure_type_code = 'ASSET'
                --AND CCP.COST_PROFILE_CODE = 'FS Asset Cost Profile'
                )
         WHERE  row_num = 1)
SELECT costing_details.*,
       esi.item_number,
       Nvl((SELECT Sum(ioqd.transaction_quantity)
            FROM   inv_onhand_quantities_detail ioqd
            WHERE  ioqd.inventory_item_id = esi.inventory_item_id
                   AND ioqd.organization_id = esi.organization_id), 0) on_hand,
       IOP.organization_code,
       esi.description
FROM   costing_details,
       egp_system_items_vl esi,
       inv_org_parameters IOP
WHERE  esi.inventory_item_id = costing_details.item_id
       AND esi.organization_id = costing_details.inv_org_id
       AND ESI.organization_id = IOP.organization_id
       -- AND esi.inventory_item_id = 100000004434739
       AND esi.item_number = Nvl(:p_item, esi.item_number)
       AND iop.organization_code = Nvl(:p_org, iop.organization_code)
       AND esi.item_number = Nvl(:p_item, esi.item_number)
ORDER  BY esi.item_number,
          org_name 

-----------------------------------------------------------------------------------------------------------------------------

                                                    ** Thank You **

Query to ger Rrequisition Approval History

Below is the query used to get the latest Requisition Approved details

 --------------------------------------------------------------------------------------------

SELECT POA.ACTION_CODE,
PANF.FULL_NAME,
PRH.Requisition_Number
FROM POR_REQUISITION_HEADERS_ALL PRH,
PO_ACTION_HISTORY POA,
PER_PERSON_NAMES_F panf
WHERE 1 = 1
AND POA.OBJECT_ID = PRH.REQUISITION_HEADER_ID
AND POA.ACTION_CODE = 'APPROVE'
AND POA.PERFORMER_ID <> - 1
AND POA.SEQUENCE_NUM = (
SELECT MAX(SEQUENCE_NUM)
FROM PO_ACTION_HISTORY PAH
WHERE 1 = 1
AND PAH.ACTION_CODE = 'APPROVE'
AND PAH.PERFORMER_ID <> - 1
AND PAH.OBJECT_ID = POA.OBJECT_ID
)
AND POA.PERFORMER_ID = PANF.PERSON_ID
AND SYSDATE BETWEEN panf.effective_start_date
AND panf.effective_end_date
AND panf.NAME_TYPE = 'GLOBAL'
AND PRH.Requisition_Number IN ('REQ1105738','REQ1308571')

 --------------------------------------------------------------------------------------------

                                                           **Thank You**

Query to Get PO Approval Document History & Latest Approver

Hello,

if you are looking for a query to fetch the PO Document History, you can use the below query to get the details and query will get the data from the below-attached screen 

**************************************************************

The query for the Approval History:

SELECT DISTINCT 
/* poa.ACTION_CODE,
poa.ACTION_DATE,
poa.IDENTIFICATION_KEY,
poa.OBJECT_ID,
poa.OBJECT_TYPE_CODE,
NVL(poa.PERFORMER_ID, - 1) PERFORMER_ID,
poa.SEQUENCE_NUM,
panf.LIST_NAME */
poa.SEQUENCE_NUM,
pha.segment1 po_number,
pha.ATTRIBUTE1 PN,
(
select description
from pjf_projects_all_vl
where 
segment1=pha.ATTRIBUTE1
and rownum=1
) PDESC,
pha.TYPE_LOOKUP_CODE,
fbu.bu_name procurement_bu,
TO_CHAR(pha.creation_date, 'DD-MM-YYYY') po_cdate,
TO_CHAR(pha.creation_date, 'DD-MM-YYYY') po_sdate,
pha.comments po_desc,
pha.currency_code,
pha.document_status po_status,
pha.revision_num,
rbu.bu_name req_bu,
psv.vendor_name supplier,
poa.ACTION_CODE,
poa.ACTION_DATE,
poa.ACTION_DATE ASSIGNMENT_DATE,
-- PU.USERNAME,
pha.po_header_id,
PSS.vendor_site_code,
'https:' || (
SELECT external_virtual_host
FROM fusion.ask_deployed_domains ad
WHERE ad.deployed_domain_name = 'FADomain'
) || '/xmlpserver/Custom/Procurement/Purchasing/PO Author Detail Report.xdo?&_paramsp_header_id=' || pha.po_header_id || '&_xt=Output&_xf=pdf&_xautorun=true&_linkToReport=true' url,
(
SELECT DISPLAY_NAME
FROM PER_PERSON_NAMES_F
WHERE PERSON_ID = PHA.AGENT_ID
AND NAME_TYPE = 'GLOBAL'
AND ROWNUM = 1
) BUYER,
poa.ROLE_CODE ROLE,
panf.DISPLAY_NAME USERNAME,
poa.PERFORMER_ID,
poa.IDENTIFICATION_KEY,
nvl((select to_char(USER_COMMENT)
from POR_APPROVAL_TASK_COMMENTS patc
where 1=1
and patc.OBJECT_ID=poa.OBJECT_ID
and patc.IDENTIFICATION_KEY=poa.IDENTIFICATION_KEY
and patc.PERFORMER_ID=poa.PERFORMER_ID
and rownum=1 ),POA.NOTE) AI
FROM PO_ACTION_HISTORY poa,
PO_HEADERS_ALL pha,
PER_PERSON_NAMES_F panf,
PO_VERSIONS pv,
fun_names_business_units_v fbu,
fun_names_business_units_v rbu,
poz_suppliers_v psv,
poz_supplier_sites_v PSS
WHERE 1 = 1
AND pha.PO_HEADER_ID = poa.OBJECT_ID
AND poa.OBJECT_TYPE_CODE = 'PO'
AND poa.ACTION_CODE IN (
'SUBMIT',
'SUBMIT CHANGE',
'APPROVE',
'ACCEPT',
'REJECT',
'WITHDRAW',
'CANCEL',
'CANCEL CHANGE ORDER',
'APPROVAL_ERROR',
'APPROVAL_EXPIRATION',
'REQUEST ACKNOWLEDGEMENT',
'IMPLEMENT EXTERNAL CO'
)
AND poa.PERFORMER_ID = panf.PERSON_ID(+)
AND panf.NAME_TYPE = 'GLOBAL'
AND poa.PO_VERSION_ID = pv.VERSION_ID(+)
AND pha.prc_bu_id = fbu.bu_id
AND pha.REQ_BU_ID = rbu.bu_id
AND pha.vendor_id = psv.vendor_id
AND PSS.vendor_id = PSV.vendor_id
AND PSS.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
-- AND pha.segment1 = 'PO-1097'
AND pha.po_header_id = :p_header_id
ORDER BY poa.SEQUENCE_NUM

**************************************************************

The Query to get the Latest Approver:

WITH po_amt

AS (

SELECT (pla.quantity * pla.list_price) po_amount,

pla.po_header_id

FROM po_lines_all pla

)

SELECT DISTINCT pha.po_header_id,

pha.segment1 po_num,

pha.ATTRIBUTE1 project_num,

(

SELECT description

FROM pjf_projects_all_vl

WHERE segment1 = pha.ATTRIBUTE1

AND rownum = 1

) project_name,

(

SELECT sum(po_amount)

FROM po_amt

WHERE po_amt.po_header_id = pha.po_header_id

) po_amount,

pha.type_lookup_code po_type,

TO_CHAR(pha.creation_date, 'DD-MM-YYYY') po_cdate,

TO_CHAR(pha.creation_date, 'DD-MM-YYYY') po_sdate,

nvl((

SELECT to_char(ASSIGNEDDATE, 'DD-MM-YYYY')

FROM fa_fusion_soainfra.wftask

WHERE compositeinstanceid = pov.APPROVAL_INSTANCE_ID

AND ASSIGNEESDISPLAYNAME IS NOT NULL

), TO_CHAR(pah.ACTION_DATE, 'DD-MM-YYYY')) app_Date,

pha.comments po_desc,

pha.currency_code,

pha.document_status po_status,

fbu.bu_name procurement_bu,

Rbu.bu_name REQ_bu,

pha.revision_num,

psv.vendor_name supplier,

ppn.DISPLAY_NAME buyer_name,

pah.po_Version_id,

pha.agent_id,

pov.approval_instance_id,

pov.revision_num POV_revision_num,

(

SELECT max(pah1.ACTION_DATE)

FROM po_versions pov1,

po_action_history pah1

WHERE pov1.Version_id = pah1.po_Version_id

AND NVL(pov1.revision_num, '99999') = NVL(pov.revision_num, '99999')

AND pah1.ACTION_CODE = 'APPROVE'

AND pah1.object_id = pha.po_header_id

AND PAH1.PERFORMER_ID = pha.AGENT_ID

) latest_Approve_Date,

(

SELECT ASSIGNEESDISPLAYNAME

FROM fa_fusion_soainfra.wftask

WHERE compositeinstanceid = pov.APPROVAL_INSTANCE_ID

AND ASSIGNEESDISPLAYNAME IS NOT NULL

AND ROWNUM = 1

) APPROVER,

abs(trunc(nvl((

SELECT ASSIGNEDDATE

FROM fa_fusion_soainfra.wftask

WHERE compositeinstanceid = pov.APPROVAL_INSTANCE_ID

AND ASSIGNEESDISPLAYNAME IS NOT NULL

), pah.ACTION_DATE) - sysdate)) + 1 DAYS,

pha.prc_bu_id,

ppn.person_id,

(

'https://' || (

SELECT external_virtual_host

FROM fusion.ask_deployed_domains ad

WHERE ad.deployed_domain_name = 'FADomain'

) || ':443/xmlpserver/Custom/Procurement/PO Author Approval Report.xdo?_xiasynch=&_xpf=&_xpt=0&_dFlag=false&_edIndex=0&_dIndex=0&_rToken=&_ranDiag=false&_xmode=3&_paramsp_header_id=' || pha.po_header_id || '&_xt=Output&_xf=pdf&_xautorun=true&_linkToReport=true'

) DETAIL_URL

FROM po_headers_all pha,

-- po_lines_all pla,

fun_names_business_units_v fbu,

fun_names_business_units_v rbu,

poz_suppliers_v psv,

per_person_names_f ppn,

po_versions pov,

po_action_history pah

WHERE 1 = 1

--  AND PHA.SEGMENT1 = 'PO-1102'

AND pha.prc_bu_id = fbu.bu_id

AND pha.REQ_BU_ID = rbu.bu_id

AND pha.vendor_id = psv.vendor_id

AND pha.agent_id = ppn.person_id

AND ppn.name_type = 'GLOBAL'

AND pov.Version_id = pah.po_Version_id

AND pha.po_header_id = pah.object_id

AND pha.document_status IN ('PENDING APPROVAL')

AND pha.prc_bu_id IN (:P_BU_ID)

AND (

pha.REQ_BU_ID IN (:P_REQ_BU_ID)

OR LEAST(:P_REQ_BU_ID) IS NULL

)

**************************************************************

                                    Thank You !!

Contract Party Resources having Contract Admin Role in Oracel Fusion

If you are looking for to get contract party resources having a contact role please refer to the below query to get those details

Reference:

Contract Party Resources having Contract Admin Role (Doc ID 2869009.1)

***********************************************************************************************************************************

SELECT ch.contract_number,
ch.major_version,
ch.start_Date,
ch.end_Date,
ch.creation_Date,
ch.sts_code,
ch.currency_code,
ch.date_approved,
cc.CRO_CODE Party_contact_role,
hp.party_name contract_admin,
horg.name contract_org,
ch.ORG_ID,
PAPF.PERSON_NUMBER,
(
SELECT PHONE_NUMBER
FROM PER_PHONES PP
WHERE PP.PERSON_ID = PAPF.PERSON_ID
AND PP.PHONE_TYPE = 'WF'
) FAX,
(
SELECT PHONE_NUMBER
FROM PER_PHONES PP
WHERE PP.PERSON_ID = PAPF.PERSON_ID
AND PP.PHONE_TYPE = 'W1'
) PHONE
FROM OKC_K_HEADERS_ALL_B ch,
OKC_CONTACTS cc,
hz_parties hp,
hr_organization_units horg,
PER_ALL_PEOPLE_F PAPF
WHERE hp.party_id = cc.OBJECT1_ID1
AND cc.JTOT_OBJECT1_CODE = 'OKX_RESOURCE'
AND cc.DNZ_CHR_ID = ch.id
AND ch.major_version = cc.major_version
-- AND cc.CRO_CODE = 'CONTRACT_ADMIN'
AND ch.OWNING_ORG_ID = horg.organization_id
AND contract_number = 'Professional Contract-99'
AND PAPF.PERSON_ID = HP.ORIG_SYSTEM_REFERENCE
AND TRUNC(SYSDATE) BETWEEN NVL(papf.effective_start_date, SYSDATE)
AND NVL(papf.effective_end_date, SYSDATE)

***********************************************************************************************************************************

Query to get scheduled ESS Jobs and its frequency and current status

 Hello All,

By using the below query we can get the list of the ess jobs has been scheduled and what is the current status and frequency etc...

**********************************************************************************

SELECT r.requestid,
r.username,
(
SELECT value
FROM ess_request_property v
WHERE v.requestid = r.requestid
AND v.name = 'EXT_PortletContainerWebModule'
) MODULE,
To_char(r.scheduled, 'yyyy-mm-dd hh24:mi:ss') SCHEDULED,
(
SELECT meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'BEN_ESS_REQ_STATE'
AND lookup_code = r.STATE
) STATE,
(
SELECT Substr(DEFINITION, - Instr(Reverse(DEFINITION), '/') + 1)
FROM request_history r1
WHERE r1.DEFINITION = r.DEFINITION
AND r.requestid = r1.requestid
) name
FROM ess_request_history r
WHERE STATE IN (
1,
6
)
-- and r.SCHEDULED is not null 
AND r.username NOT IN (
'FUSION_APPS_SEARCH_APPID',
'FUSION_APPS_CRM_ESS_APPID',
'FUSION_APPS_HCM_ADF_LDAP_APPID'
)
AND r.submitter NOT IN (
'FUSION_APPS_SEARCH_APPID',
'FUSION_APPS_CRM_ESS_APPID',
'FUSION_APPS_HCM_ADF_LDAP_APPID'
)
ORDER BY 6,
1 DESC

**********************************************************************************

Oracle Business Intelligence Explorer Test Q&A

 Hello
If you are trying to give the BIP Explorer test,
Below are the questions asked and answers

Explorer Name: Business Intelligence Explorer

Exam Link:
https://mylearn.oracle.com/learning-path/business-intelligence-explorer/80390

---------------------------------------------------------------------------------------------------------------------------------------

1.Which Oracle Transactional Business Intelligence (OTBI) tools can be used to create OTBI analyses? (Choose two)

ANS:

Report Editor

Report Creator

---------------------------------------------------------------------------------------------------------------------------------------

2.Which is the correct order of steps to create a BI Publisher report using the Guide Me wizard?

ANS:

Select Data > Select Layout > Create Chart > Create Table > Save Report

---------------------------------------------------------------------------------------------------------------------------------------

3.Which statement is true about BI Catalog?

ANS:

It is a repository of OTBI, BI Publisher, and Financial Reporting objects.

---------------------------------------------------------------------------------------------------------------------------------------

4.Which are the components of a BI Publisher report? (Choose three)

ANS:

Layout

Template

Data Model

---------------------------------------------------------------------------------------------------------------------------------------

5.What is bursting?

ANS:

The process of generating multiple documents from a report and delivering them to different destinations

---------------------------------------------------------------------------------------------------------------------------------------

6.You can embed an analysis, webpage, or document in a dashboard.

ANS:

True

---------------------------------------------------------------------------------------------------------------------------------------

7.How are the columns in a subject area's folder or subfolder arranged?

ANS:

Alphabetically

---------------------------------------------------------------------------------------------------------------------------------------

8.Which views can you select to display information in Oracle Transactional Business Intelligence? (Choose three)

ANS:

Graphs

Table

Tile

---------------------------------------------------------------------------------------------------------------------------------------

9.Which process options are available while running a report on the Scheduled Processes page? (Choose three)

ANS:

Language

Currency

Number Format

---------------------------------------------------------------------------------------------------------------------------------------

10.What is a data model?

ANS:

It is a report component that contains SQL queries to retrieve data and other elements like parameters and bursting definitions.

---------------------------------------------------------------------------------------------------------------------------------------

11.Which reporting objects can you create using Oracle Transactional Business Intelligence? (Choose three)

ANS:

Analyses

Data Models

Dashboards

---------------------------------------------------------------------------------------------------------------------------------------

12.What is the function of a translation in a BI Publisher report?

ANS:

Allows you to generate a report’s output in multiple languages at run time

---------------------------------------------------------------------------------------------------------------------------------------

13.The BI Answers tool is only used to create Oracle Transactional Business Intelligence analyses for ERP applications like Expenses.

ANS:

False

---------------------------------------------------------------------------------------------------------------------------------------

14.Multiple versions of a report can be created in a single run by selecting different layouts in the Output option of the Schedule Process page.

ANS:

True

---------------------------------------------------------------------------------------------------------------------------------------

15.Which tabs are available in the BI Answers work area? (Choose four)

ANS:

Results

Prompts

Views

Criteria

---------------------------------------------------------------------------------------------------------------------------------------

16.What is a subject area?

ANS:

It’s a collection of columns available to the report writer for creating reports.

---------------------------------------------------------------------------------------------------------------------------------------

17.Which icon in the BI Catalog toolbar do you use to view the owner or the last modified time of an object?

ANS:

Change-List View Type

---------------------------------------------------------------------------------------------------------------------------------------

18.Which tasks can be performed while editing an Oracle Transactional Business Intelligence analysis? (Choose three)

ANS:

Add or remove columns.

Define prompts.

Configure column properties.

---------------------------------------------------------------------------------------------------------------------------------------

19.Where are predefined reporting objects stored?

ANS:

Shared Folders

---------------------------------------------------------------------------------------------------------------------------------------

20.Which options are available in the Create drop-down list of the Reports and Analytics work area? (Choose two)

ANS:

Report

Analysis

---------------------------------------------------------------------------------------------------------------------------------------

Query to get Assets SLA TO GL Entries in Fusion

 The below query provides you the details about asset journal entires of SLA with GL

SELECT GJH.description                                     DESCRIPTION,
       GJH.name                                            Journal_NAME,
       GJH.je_source,
       GJH.period_name,
       GCC.segment1,
       GCC.segment2,
       GCC.segment3,
       GCC.segment4,
       GCC.segment5,
       GCC.segment6,
       GCC.segment7,
       ( xal.entered_cr )                                  Credit,
       ( xal.entered_dr )                                  Debit,
       Nvl(xal.accounted_dr, 0) - Nvl(xal.accounted_cr, 0) Net,
       To_char(gjl.effective_date, 'mm/dd/yyyy')           GL_DATE,
       Nvl(fa.asset_number, (SELECT FAV.asset_number
                             FROM   fa_additions_vl FAV
                             WHERE  FAV.asset_id = XTE.source_id_int_1
                                    AND ROWNUM = 1))
       INVOICE_RECEIPT_PAYMENT_NUMBER_ref,
       gl.name                                             ledger_name,
       gjh.je_category
FROM   gl_je_headers GJH,
       gl_je_lines GJL,
       gl_code_combinations GCC,
       gl_import_references GIR,
       xla_ae_lines XAL,
       xla_ae_headers XAH,
       xla_events xe,
       xla_transaction_entities xte,
       gl_ledgers gl,
       fa_transaction_headers FTH,
       fa_additions_vl fa
WHERE  1 = 1
       AND GJH.je_header_id = GJL.je_header_id
       AND GJL.code_combination_id = GCC.code_combination_id
       AND GJH.je_header_id = GIR.je_header_id
       AND GJL.je_line_num = GIR.je_line_num
       AND GIR.gl_sl_link_id = XAL.gl_sl_link_id
       AND GIR.gl_sl_link_table = XAL.gl_sl_link_table
       AND XAL.ae_header_id = XAH.ae_header_id
       AND xah.application_id = xe.application_id
       AND xah.application_id = xe.application_id
       AND xah.event_id = xe.event_id
       AND xe.application_id = xte.application_id
       AND xe.entity_id = xte.entity_id
       -- AND xte.entity_code = 'ADJUSTMENTS'
       -- AND xte.source_id_int_1 = aaa.adjustment_id
       AND GJL.status = 'P'
       AND GJH.je_source = 'Assets'
       AND XTE.source_id_int_1 = FTH.transaction_header_id(+)
       AND FTH.asset_id = fa.asset_id(+)
       AND gl.name = Nvl(:p_le, gl.name)
       AND gjh.period_name = Nvl(:P_PERIOD, gjh.period_name)