<ROOT>
 <APPS_INITIALIZE_DATA>
  <USER_NAME>ENGINATICS</USER_NAME>
  <RESPONSIBILITY_KEY>SYSTEM_ADMINISTRATOR</RESPONSIBILITY_KEY>
  <APPLICATION_SHORT_NAME>SYSADMIN</APPLICATION_SHORT_NAME>
 </APPS_INITIALIZE_DATA>
<REPORTS>
<!-- loader xml for Enginatics Blitz Report: CAC ICP PII WIP Material Usage Variance -->
 <REPORTS_ROW>
  <GUID>D7325FBF288B553DE0530100007F4ECD</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>with j as (
select /*+ materialize*/
nvl(gl.short_name,gl.name) ledger,
haouv_ou.name operating_unit,
mp.organization_code,
gl.currency_code,
oap.period_name,
oap.period_start_date,
oap.schedule_close_date,
case when wdj.date_closed&gt;=oap.period_start_date and wdj.date_closed&lt;oap.schedule_close_date+1 then &apos;Variance&apos; else &apos;Valuation&apos; end report_type,
wdj.organization_id,
wdj.wip_entity_id,
wdj.class_code,
wac.class_type,
wdj.status_type,
wdj.project_id,
wdj.primary_item_id,
wdj.lot_number,
wdj.creation_date,
wdj.scheduled_start_date,
wdj.date_released,
wdj.date_completed,
trunc(wdj.date_closed) date_closed,
wdj.last_update_date,
wdj.start_quantity,
wdj.material_account,
wdj.quantity_completed-nvl((select sum(mmt.primary_quantity) from mtl_material_transactions mmt where wdj.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wdj.organization_id=mmt.organization_id and mmt.transaction_type_id in (17,44) and mmt.transaction_date&gt;=oap.schedule_close_date+1),0) quantity_completed,
wdj.quantity_scrapped-nvl((select sum(mmt.primary_quantity) from mtl_material_transactions mmt where wdj.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wdj.organization_id=mmt.organization_id and mmt.transaction_type_id in (90,91) and mmt.transaction_date&gt;=oap.schedule_close_date+1),0) quantity_scrapped,
cct.cost_type_id,
cct.cost_type,
mp.primary_cost_method
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units haou,
hr_all_organization_units_vl haouv_ou,
gl_ledgers gl,
cst_cost_types cct,
org_acct_periods oap,
wip_discrete_jobs wdj,
wip_accounting_classes wac
where
1=1 and
2=2 and
3=3 and
4=4 and
mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;) and
mp.organization_id=hoi.organization_id and
hoi.org_information_context=&apos;Accounting Information&apos; and
hoi.organization_id=haou.organization_id and
to_number(hoi.org_information3)=haouv_ou.organization_id and
to_number(hoi.org_information1)=gl.ledger_id and
cct.cost_type_id=nvl((select cct2.cost_type_id from cst_cost_types cct2 where cct2.cost_type=:p_cost_type),mp.primary_cost_method) and
mp.organization_id=oap.organization_id and
mp.organization_id=wdj.organization_id and
wdj.class_code=wac.class_code and
wdj.organization_id=wac.organization_id and
wac.class_type in (1,3,5) and
(
wdj.date_closed&gt;=oap.period_start_date and wdj.date_closed&lt;oap.schedule_close_date+1 and :p_report_option in (&apos;Closed jobs&apos;,&apos;All jobs&apos;) or
(wdj.date_closed is null or wdj.date_closed&gt;=oap.schedule_close_date+1) and wdj.creation_date&lt;oap.schedule_close_date+1 and :p_report_option in (&apos;Open jobs&apos;,&apos;All jobs&apos;)
)
),
c as (
select /*+ materialize*/
j.wip_entity_id,
wro.operation_seq_num,
wro.inventory_item_id,
wro.wip_supply_type,
nvl(wro.basis_type,1) basis_type,
decode(nvl(wro.basis_type,1),2,nvl(wro.required_quantity,1),nvl(wro.quantity_per_assembly,0)/nvl(wro.component_yield_factor,1)) quantity_per_assembly,
round(case
when j.status_type in (4,5,7,12,14,15) or :p_use_completion_qtys=&apos;Y&apos; then decode(nvl(wro.basis_type,1),2,nvl(wro.quantity_per_assembly,0)*case when nvl(j.quantity_completed,0)+decode(:p_include_scrap,&apos;Y&apos;,nvl(j.quantity_scrapped,0),0)&gt;0 then 1 else 0 end,nvl(wro.quantity_per_assembly,0)/nvl(wro.component_yield_factor,1)*decode(j.class_type,5,nvl(j.quantity_completed,0),nvl(j.quantity_completed,0)+decode(:p_include_scrap,&apos;Y&apos;,nvl(j.quantity_scrapped,0),0)))
else decode(nvl(wro.basis_type,1),2,nvl(wro.quantity_per_assembly,0),nvl(wro.quantity_per_assembly,0)*j.start_quantity/nvl(wro.component_yield_factor,1))
end,6) total_req_quantity,
nvl(wro.quantity_issued,0)+nvl((select sum(mmt.primary_quantity) from mtl_material_transactions mmt where wro.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wro.organization_id=mmt.organization_id and wro.operation_seq_num=mmt.operation_seq_num and wro.inventory_item_id=mmt.inventory_item_id and mmt.transaction_type_id in (35,43) and mmt.transaction_date&gt;=j.schedule_close_date+1),0) quantity_issued,
(select max(mmt.transaction_date) from mtl_material_transactions mmt where wro.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wro.organization_id=mmt.organization_id and wro.inventory_item_id=mmt.inventory_item_id and mmt.transaction_date&lt;j.schedule_close_date+1) last_transaction_date,
(select wo.department_id from wip_operations wo where wro.wip_entity_id=wo.wip_entity_id and wro.organization_id=wo.organization_id and wro.operation_seq_num=wo.operation_seq_num) department_id,
nvl((select /*+ no_unnest*/ max(cic.item_cost) keep (dense_rank first order by decode(cic.cost_type_id,j.cost_type_id,1,2)) from cst_item_costs cic where wro.inventory_item_id=cic.inventory_item_id and wro.organization_id=cic.organization_id and cic.cost_type_id in (j.cost_type_id,j.primary_cost_method)),0) item_cost,
(select /*+ no_unnest*/ sum(cicd.item_cost) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where wro.inventory_item_id=cicd.inventory_item_id and wro.organization_id=cicd.organization_id and cicd.cost_type_id=cct.cost_type_id and cicd.resource_id=br.resource_id and 7=7) pii_item_cost
from
j,
wip_requirement_operations wro
where
6=6 and
j.wip_entity_id=wro.wip_entity_id and
j.organization_id=wro.organization_id and
wro.wip_supply_type&lt;&gt;6 and
wro.operation_seq_num&gt;0
)
select
x.report_type,
x.ledger,
x.operating_unit,
x.organization_code org_code,
x.period_name,
&amp;segment_columns
x.class_code wip_class,
xxen_util.meaning(x.class_type,&apos;WIP_CLASS_TYPE&apos;,700) class_type,
we.wip_entity_name wip_job,
(select ppa.segment1 from pa_projects_all ppa where x.project_id=ppa.project_id) project_number,
xxen_util.meaning(x.status_type,&apos;WIP_JOB_STATUS&apos;,700) job_status,
x.creation_date,
x.scheduled_start_date,
x.date_released,
x.date_completed,
x.date_closed,
x.last_update_date,
msiv.primary_uom_code uom_code,
msiv.std_lot_size item_std_lot_size,
nvl2(x.costing_lot_size,x.cost_type,null) lot_size_cost_type,
x.costing_lot_size,
x.start_quantity,
x.quantity_completed assembly_quantity_completed,
x.quantity_scrapped assembly_quantity_scrapped,
x.quantity_completed+x.quantity_scrapped total_assembly_quantity,
msiv.concatenated_segments assembly,
msiv.description assembly_description,
xxen_util.meaning(msiv.item_type,&apos;ITEM_TYPE&apos;,3) item_type,
msiv.inventory_item_status_code item_status,
xxen_util.meaning(msiv.planning_make_buy_code,&apos;MTL_PLANNING_MAKE_BUY&apos;,700) make_buy_code,
&amp;category_columns
x.lot_number,
x.operation_seq_num operation_seq_number,
bd.department_code department,
msiv_comp.concatenated_segments component_number,
msiv_comp.description component_description,
xxen_util.meaning(msiv_comp.item_type,&apos;ITEM_TYPE&apos;,3) component_item_type,
msiv_comp.inventory_item_status_code component_status_code,
xxen_util.meaning(msiv_comp.planning_make_buy_code,&apos;MTL_PLANNING_MAKE_BUY&apos;,700) component_make_buy_code,
xxen_util.meaning(x.wip_supply_type,&apos;WIP_SUPPLY&apos;,700) wip_supply_type,
xxen_util.meaning(x.basis_type,&apos;CST_BASIS&apos;,700) component_basis_type,
x.cost_type,
nvl2(x.pii_item_cost,:p_pii_cost_type,null) pii_cost_type,
x.currency_code,
x.item_cost gross_item_cost,
nvl(x.pii_item_cost,0) pii_item_cost,
msiv_comp.primary_uom_code uom_code,
x.quantity_per_assembly,
round(x.total_req_quantity,3) total_required_quantity,
x.last_transaction_date,
round(x.quantity_issued,3) quantity_issued,
round(x.quantity_issued-x.total_req_quantity,3) quantity_left_in_wip,
x.total_req_quantity*x.item_cost wip_standard_component_value,
round(x.quantity_issued*x.item_cost,2) applied_component_value,
case when x.quantity_completed+x.quantity_scrapped=0 and round(x.quantity_issued*x.item_cost,2)=0 then 0 else round(x.quantity_issued*x.item_cost-x.total_req_quantity*x.item_cost,2) end material_usage_variance,
round(x.quantity_issued-x.total_req_quantity,3)*decode(x.report_type,&apos;Variance&apos;,1,decode(x.quantity_issued,0,0,1)) adjusted_quantity_in_wip,
round(round(x.quantity_issued-x.total_req_quantity,3)*decode(x.report_type,&apos;Variance&apos;,1,decode(x.quantity_issued,0,0,1))*nvl(x.pii_item_cost,0),2) pii_in_wip,
xxen_util.yes(nvl2(x.last_rollup_date,&apos;Y&apos;,null)) rolled_up,
x.last_rollup_date last_cost_rollup
from
(
select
j.*,
c.operation_seq_num,
c.inventory_item_id,
c.wip_supply_type,
c.basis_type,
c.quantity_per_assembly,
c.total_req_quantity,
c.quantity_issued,
c.last_transaction_date,
c.department_id,
c.item_cost,
c.pii_item_cost,
a.lot_size costing_lot_size,
a.last_rollup_date
from
j,
c,
(
select
z.organization_id,
z.inventory_item_id,
max(z.lot_size) keep (dense_rank first order by z.rnk) lot_size,
max(z.last_rollup_date) keep (dense_rank first order by z.rnk) last_rollup_date
from
(
select
cic.organization_id,
cic.inventory_item_id,
decode(cic.cost_type_id,j.cost_type_id,1,2) rnk,
nvl(cic.lot_size,1) lot_size,
decode(cic.based_on_rollup_flag,1,(select max(cicd.creation_date) from cst_item_cost_details cicd where cic.organization_id=cicd.organization_id and cic.inventory_item_id=cicd.inventory_item_id and cic.cost_type_id=cicd.cost_type_id and cicd.rollup_source_type=3 and cicd.attribute15 is null)) last_rollup_date
from
(select distinct j.organization_id, j.primary_item_id, j.cost_type_id, j.primary_cost_method from j) j,
cst_item_costs cic
where
j.organization_id=cic.organization_id and
j.primary_item_id=cic.inventory_item_id and
cic.cost_type_id in (j.cost_type_id,j.primary_cost_method)
) z
group by
z.organization_id,
z.inventory_item_id
) a
where
j.wip_entity_id=c.wip_entity_id and
j.organization_id=a.organization_id(+) and
j.primary_item_id=a.inventory_item_id(+)
) x,
wip_entities we,
mtl_system_items_vl msiv,
mtl_system_items_vl msiv_comp,
bom_departments bd,
gl_code_combinations gcc
where
5=5 and
x.quantity_per_assembly+round(x.quantity_issued,3)&lt;&gt;0 and
x.wip_entity_id=we.wip_entity_id and
x.organization_id=msiv.organization_id and
x.primary_item_id=msiv.inventory_item_id and
x.organization_id=msiv_comp.organization_id and
x.inventory_item_id=msiv_comp.inventory_item_id and
x.department_id=bd.department_id(+) and
x.material_account=gcc.code_combination_id(+)
order by
x.report_type,
x.ledger,
x.operating_unit,
x.organization_code,
&amp;segment_columns_grp
x.class_code,
we.wip_entity_name,
x.operation_seq_num,
component_number,
department</SQL_TEXT>
  <VERSION_COMMENTS>Rebuilt to the Blitz Report SQL standards: assembly quantities completed and scrapped and component quantities issued as of the period end use the correct signs for completions, scrap and component returns after the period, open jobs created after the period end are no longer reported, jobs closed after the period end are reported as Valuation instead of Variance, rows of assemblies without a cost are no longer dropped, the Component Yield Factor Column parameter, which had no effect, is removed. Organization security as before.</VERSION_COMMENTS>
  <NUMBER_FORMAT>#,##0.00000;[Red](#,##0.00000)</NUMBER_FORMAT>
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>CAC ICP PII WIP Material Usage Variance</REPORT_NAME>
    <DESCRIPTION>For your open and closed WIP jobs use this report to calculate the amount of profit in inventory (PII) in WIP as well as the amount of PII in the WIP variances for the month.

Report your material usage variances for your open and closed WIP jobs.  If the job is open the Report Type column displays &quot;Valuation&quot;, as this WIP job and potential material usage variance is still in your WIP inventory balances.  If the job has been closed during the reporting period, the Report Type column displays &quot;Variance&quot;, as this WIP job was written off on a WIP Job Close Variance transaction during the reporting period.  You can report prior periods and this report will automatically adjust the assembly completion quantities and component issue quantities to reflect what it was for the specified reported accounting period.  And by specifying the profit in inventory (PII) cost type you can determine how much PII or ICP (intercompany profit) was either remaining on your balance sheet or how much was recorded as part of your WIP job close variances.

Closed, Pending Close, Cancelled, Complete and Complete No Charges WIP job statuses use the completion quantities.  All other WIP jobs use the parameter &quot;Use Completion Quantities&quot;. 

Rules:  If component issue quantity = zero, and the WIP job is open, there is no PII as the components are in onhand inventory.  But if the WIP job is closed, PII is calculated in order to correct the WIP variance.

/* +=============================================================================+
-- |  Copyright 2009 - 2022 Douglas Volz Consulting, Inc. 
-- |  All rights reserved. 
-- |  Version Modified on Modified  by   Description
-- |  ======= =========== =============== =========================================
-- |  1.26     20 Mar 2022 Douglas Volz   Adjust PII, if the wip job is closed, even if there are no issued quantities, still calculate PII in order to correct the job close WIP variance.
-- |  1.27    24 Oct 2026 Andy Haack    Rebuilt to the Blitz Report SQL standards.
</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
  </REPORT_TRANSLATIONS>
  <CATEGORY_ASSIGNMENTS>
   <CATEGORY_ASSIGNMENTS_ROW>
    <CATEGORY>Enginatics</CATEGORY>
   </CATEGORY_ASSIGNMENTS_ROW>
  </CATEGORY_ASSIGNMENTS>
  <ANCHORS>
   <ANCHORS_ROW>
    <ANCHOR>&amp;category_columns</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;segment_columns</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;segment_columns_grp</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>1=1</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>2=2</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>3=3</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>4=4</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>5=5</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>6=6</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>7=7</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:p_cost_type</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:p_include_scrap</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:p_pii_cost_type</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:p_report_option</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:p_use_completion_qtys</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>-20</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;segment_columns</ANCHOR>
    <SQL_TEXT>select
&apos;gcc.&apos;||lower(fifsv.application_column_name)||&apos; &quot;&apos;||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||&apos;&quot;,&apos; text
from
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
fifsv.application_id=101 and
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.enabled_flag=&apos;Y&apos; and
fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code=&apos;GL#&apos; and fifsv0.id_flex_structure_name=:chart_of_accounts)
order by
fifsv.segment_num</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>GL Chart of Accounts (inv org related)</LOV_NAME>
    <LOV_GUID>8E2FF36EDEC879D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
fifsv.id_flex_structure_name value,
fifsv.id_flex_structure_code description
from
fnd_id_flex_structures_vl fifsv
where
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.application_id=101 and
fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) &gt; sysdate)
order by
fifsv.id_flex_structure_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select
fifsv.id_flex_structure_name
from
fnd_id_flex_structures_vl fifsv
where
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.application_id=101 and
fifsv.id_flex_num=(select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id=fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;))</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Chart of Accounts</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>2</SORT_ORDER>
    <ANCHOR>&amp;segment_columns_grp</ANCHOR>
    <SQL_TEXT>select
&apos;gcc.&apos;||lower(fifsv.application_column_name)||&apos;,&apos; text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.application_id=101 and
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.enabled_flag=&apos;Y&apos; and
fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code=&apos;GL#&apos; and fifsv0.id_flex_structure_name=:chart_of_accounts)
order by
fifsv.segment_num</SQL_TEXT>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Chart of Accounts</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>3</SORT_ORDER>
    <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
    <ANCHOR>:p_report_option</ANCHOR>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>CST WIP Report Option</LOV_NAME>
    <LOV_GUID>D5CA808540E0FA18E05369FB090580A4</LOV_GUID>
    <LOV_QUERY_DSP>select &apos;Open jobs&apos; value, 
&apos;Report only open jobs&apos; description
from
dual
union all
select &apos;All jobs&apos; value, 
&apos;Report all jobs&apos; description
from
dual
union all
select &apos;Closed jobs&apos; value, 
&apos;Report only closed jobs&apos; description
from
dual
order by 1</LOV_QUERY_DSP>
    <DEFAULT_VALUE>All jobs</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Report Option</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>4</SORT_ORDER>
    <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
    <ANCHOR>4=4</ANCHOR>
    <SQL_TEXT>oap.period_name=:p_period_name</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Period</LOV_NAME>
    <LOV_GUID>8E2FF36EDF2279D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
oap.period_name value,
max(oap.period_year||&apos;-&apos;||oap.period_num||&apos;, &apos;||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,&apos;P&apos;,2,&apos;N&apos;,decode(oap.summarized_flag,&apos;N&apos;,65,66),4),3),&apos;MTL_ACCT_PERIOD_STATUS&apos;,700)||&apos; (&apos;||oap.period_start_date||&apos; - &apos;||oap.schedule_close_date||&apos;)&apos;) over (partition by oap.period_name) description,
max(oap.period_start_date) over (partition by oap.period_name) period_start_date,
max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num
from
gl_ledgers gl,
org_organization_definitions ood,
mtl_parameters mp,
(select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap
where
oap.period_start_date&lt;=sysdate and
(:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)=&apos;Y&apos;) and
(:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)=&apos;Y&apos;) and
ood.organization_id=mp.organization_id and
mp.organization_id&lt;&gt;mp.master_organization_id and 
gl.period_set_name=oap.period_set_name and
gl.ledger_id=ood.set_of_books_id and
ood.organization_id=oap.organization_id
order by
period_start_date desc,
effective_period_num desc,
oap.period_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select distinct oap.period_name value
from org_acct_periods oap,
 (select max(oap.schedule_close_date) default_date
  from org_acct_periods oap,
  org_organization_definitions ood
  where ood.organization_id = oap.organization_id
  and nvl(ood.disable_date, sysdate + 1) &gt; sysdate) max
where oap.schedule_close_date = max.default_date
and rownum = 1
</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Period Name</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>5</SORT_ORDER>
    <DISPLAY_SEQUENCE>30</DISPLAY_SEQUENCE>
    <ANCHOR>:p_cost_type</ANCHOR>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>CST Cost Type (No AvgRates No PII)</LOV_NAME>
    <LOV_GUID>A486BD05719A2A82E053BB6B6358FC8E</LOV_GUID>
    <LOV_QUERY_DSP>select
cct.cost_type value,
cct.description
from
cst_cost_types cct
where
nvl(cct.disable_date,sysdate)&gt;=sysdate and
cct.organization_id is null and
(cct.cost_type not like &apos;%PII%&apos; or cct.cost_type like &apos;%ICP%&apos;) and
cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null)
order by
cct.cost_type</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select
cct.cost_type value,
cct.description
from
cst_cost_types cct,
mtl_parameters mp
where
nvl(cct.disable_date,sysdate + 1) &gt; sysdate and
cct.organization_id is null and
cct.cost_type_id = mp.primary_cost_method and
(mp.organization_id=fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;)
 or
 (fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) is null and cct.cost_type_id = mp.primary_cost_method)
) and
rownum &lt; 2
group by
cct.cost_type, cct.description
order by
cct.cost_type</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Cost Type</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>6</SORT_ORDER>
    <DISPLAY_SEQUENCE>40</DISPLAY_SEQUENCE>
    <ANCHOR>7=7</ANCHOR>
    <SQL_TEXT>cct.cost_type=:p_pii_cost_type</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>CST Cost Type (No AvgRates)</LOV_NAME>
    <LOV_GUID>91D3167A335B1204E053BB6B63585EE3</LOV_GUID>
    <LOV_QUERY_DSP>select
cct.cost_type value,
cct.description
from
cst_cost_types cct
where
nvl(cct.disable_date,sysdate)&gt;=sysdate and
cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null)
order by cct.cost_type desc</LOV_QUERY_DSP>
    <DEFAULT_VALUE>PII</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>PII Cost Type</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>7</SORT_ORDER>
    <DISPLAY_SEQUENCE>50</DISPLAY_SEQUENCE>
    <ANCHOR>7=7</ANCHOR>
    <SQL_TEXT>br.resource_code=:p_pii_sub_element</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>CST PII Sub-Elements</LOV_NAME>
    <LOV_GUID>E234C6573B214FDAE05369FB090533D7</LOV_GUID>
    <LOV_QUERY_DSP>select
br.resource_code value,
br.description
from
bom_resources br
where
br.allow_costs_flag&lt;&gt;2 and
(br.resource_code like &apos;%PII%&apos; or br.resource_code like &apos;%ICP%&apos;) and
(:$flex$.organization_code is null or br.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)=&apos;Y&apos;)) and
nvl(br.disable_date,sysdate)&gt;=sysdate
order by
br.resource_code</LOV_QUERY_DSP>
    <DEFAULT_VALUE>PII</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>PII Sub-Element</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>8</SORT_ORDER>
    <DISPLAY_SEQUENCE>60</DISPLAY_SEQUENCE>
    <ANCHOR>:p_include_scrap</ANCHOR>
    <PARAMETER_TYPE_DSP>LOV Oracle</PARAMETER_TYPE_DSP>
    <LOV_NAME>Yes_No</LOV_NAME>
    <LOV_QUERY_DSP>select
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type=&apos;YES_NO&apos;
order by value,description</LOV_QUERY_DSP>
    <DEFAULT_VALUE>Y</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Include Scrap Quantities</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>9</SORT_ORDER>
    <DISPLAY_SEQUENCE>70</DISPLAY_SEQUENCE>
    <ANCHOR>4=4</ANCHOR>
    <SQL_TEXT>wdj.status_type&lt;&gt;1</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV Oracle</PARAMETER_TYPE_DSP>
    <LOV_NAME>Yes_No</LOV_NAME>
    <LOV_QUERY_DSP>select
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type=&apos;YES_NO&apos;
order by value,description</LOV_QUERY_DSP>
    <MATCHING_VALUE>N</MATCHING_VALUE>
    <DEFAULT_VALUE>N</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Include Unreleased Jobs</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>10</SORT_ORDER>
    <DISPLAY_SEQUENCE>80</DISPLAY_SEQUENCE>
    <ANCHOR>6=6</ANCHOR>
    <SQL_TEXT>nvl(wro.wip_supply_type,1)&lt;&gt;4</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV Oracle</PARAMETER_TYPE_DSP>
    <LOV_NAME>Yes_No</LOV_NAME>
    <LOV_QUERY_DSP>select
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type=&apos;YES_NO&apos;
order by value,description</LOV_QUERY_DSP>
    <MATCHING_VALUE>N</MATCHING_VALUE>
    <DEFAULT_VALUE>N</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Include Bulk Supply Items</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>11</SORT_ORDER>
    <DISPLAY_SEQUENCE>90</DISPLAY_SEQUENCE>
    <ANCHOR>:p_use_completion_qtys</ANCHOR>
    <PARAMETER_TYPE_DSP>LOV Oracle</PARAMETER_TYPE_DSP>
    <LOV_NAME>Yes_No</LOV_NAME>
    <LOV_QUERY_DSP>select
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type=&apos;YES_NO&apos;
order by value,description</LOV_QUERY_DSP>
    <DEFAULT_VALUE>Y</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Use Completion Quantities</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>12</SORT_ORDER>
    <DISPLAY_SEQUENCE>100</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;category_columns</ANCHOR>
    <SQL_TEXT>select xxen_util.item_category_columns(p_category_set_name=&gt;&apos;&lt;parameter_value&gt;&apos;, p_table_alias=&gt;&apos;x&apos;, p_item_id_column=&gt;&apos;primary_item_id&apos;) sql_text from dual</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Category Set</LOV_NAME>
    <LOV_GUID>8E2FF36EDECA79D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
mcsv.category_set_name value,
mcsv.description
from
mtl_category_sets_vl mcsv
where
:$flex$.organization_code is null or
mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)=&apos;Y&apos; and mp.organization_id=mic.organization_id)
order by
mcsv.category_set_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11))</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Category Set 1</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>13</SORT_ORDER>
    <DISPLAY_SEQUENCE>110</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;category_columns</ANCHOR>
    <SQL_TEXT>select xxen_util.item_category_columns(p_category_set_name=&gt;&apos;&lt;parameter_value&gt;&apos;, p_table_alias=&gt;&apos;x&apos;, p_item_id_column=&gt;&apos;primary_item_id&apos;) sql_text from dual</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Category Set</LOV_NAME>
    <LOV_GUID>8E2FF36EDECA79D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
mcsv.category_set_name value,
mcsv.description
from
mtl_category_sets_vl mcsv
where
:$flex$.organization_code is null or
mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)=&apos;Y&apos; and mp.organization_id=mic.organization_id)
order by
mcsv.category_set_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5))</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Category Set 2</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>14</SORT_ORDER>
    <DISPLAY_SEQUENCE>120</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;category_columns</ANCHOR>
    <SQL_TEXT>select xxen_util.item_category_columns(p_category_set_name=&gt;&apos;&lt;parameter_value&gt;&apos;, p_table_alias=&gt;&apos;x&apos;, p_item_id_column=&gt;&apos;primary_item_id&apos;) sql_text from dual</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Category Set</LOV_NAME>
    <LOV_GUID>8E2FF36EDECA79D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
mcsv.category_set_name value,
mcsv.description
from
mtl_category_sets_vl mcsv
where
:$flex$.organization_code is null or
mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)=&apos;Y&apos; and mp.organization_id=mic.organization_id)
order by
mcsv.category_set_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>xxen_util.previous_parameter_value(:parameter_id)</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Category Set 3</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>15</SORT_ORDER>
    <DISPLAY_SEQUENCE>130</DISPLAY_SEQUENCE>
    <ANCHOR>2=2</ANCHOR>
    <SQL_TEXT>mp.organization_code=:p_org_code</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV custom</PARAMETER_TYPE_DSP>
    <LOV_QUERY_DSP>select
ood.organization_code value,
ood.organization_name description
from
org_organization_definitions ood
where
ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and
ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;) and
(:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)=&apos;Y&apos;)) and
(:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)=&apos;Y&apos;)) and
nvl(ood.disable_date,sysdate)&gt;=sysdate
order by
ood.organization_code</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;)</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Organization Code</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>16</SORT_ORDER>
    <DISPLAY_SEQUENCE>140</DISPLAY_SEQUENCE>
    <ANCHOR>4=4</ANCHOR>
    <SQL_TEXT>wdj.class_code=:p_class_code</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>WIP Class Code (org dependent)</LOV_NAME>
    <LOV_GUID>E7CCB27F39561272E0530100007F62B4</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
wac.class_code value,
wac.description
from
mtl_parameters mp,
wip_accounting_classes wac
where
(:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)=&apos;Y&apos;) and
mp.organization_id=wac.organization_id
order by
wac.class_code</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Class Code</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>17</SORT_ORDER>
    <DISPLAY_SEQUENCE>150</DISPLAY_SEQUENCE>
    <ANCHOR>4=4</ANCHOR>
    <SQL_TEXT>wdj.status_type=xxen_util.lookup_code(:p_job_status,&apos;WIP_JOB_STATUS&apos;,700)</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>WIP Job Status</LOV_NAME>
    <LOV_GUID>8E2FF36EDE9579D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
ml.meaning value,
ml.description
from
mfg_lookups ml
where
ml.lookup_type=&apos;WIP_JOB_STATUS&apos;
order by
ml.meaning</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Job Status</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>18</SORT_ORDER>
    <DISPLAY_SEQUENCE>160</DISPLAY_SEQUENCE>
    <ANCHOR>4=4</ANCHOR>
    <SQL_TEXT>wdj.wip_entity_id in (select we.wip_entity_id from wip_entities we where we.wip_entity_name=:p_wip_job)</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>WIP Job</LOV_NAME>
    <LOV_GUID>8E2FF36EDEC379D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
we.wip_entity_name value,
xxen_util.meaning(we.entity_type,&apos;WIP_ENTITY&apos;,700)||&apos; (&apos;||ood.organization_code||&apos;)&apos;||nvl2(we.description,&apos;: &apos;||we.description,null) description
from
wip_entities we,
org_organization_definitions ood
where
(:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)=&apos;Y&apos;)) and
(
:$flex$.organization_code is null and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;) or
xxen_util.contains(:$flex$.organization_code,ood.organization_code)=&apos;Y&apos;
) and
we.organization_id=ood.organization_id and
nvl(ood.disable_date,sysdate)&gt;=sysdate and
we.entity_type in (1,3,5,8) --(Discrete job, Closed discrete job, Lot based job, Closed lot based job)
order by
we.creation_date desc</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>WIP Job</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>19</SORT_ORDER>
    <DISPLAY_SEQUENCE>170</DISPLAY_SEQUENCE>
    <ANCHOR>5=5</ANCHOR>
    <SQL_TEXT>msiv_comp.concatenated_segments=:p_component_number</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Item (master)</LOV_NAME>
    <LOV_GUID>8E2FF36EDEF379D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
msiv.concatenated_segments value,
msiv.description
from
mtl_system_items_vl msiv
where
msiv.organization_id=(
select
max(mp.master_organization_id) master_organization_id
from
mtl_parameters mp
where
mp.organization_id=fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) or
fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) is null and
mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;)
)
order by
msiv.concatenated_segments</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Component Number</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>20</SORT_ORDER>
    <DISPLAY_SEQUENCE>180</DISPLAY_SEQUENCE>
    <ANCHOR>3=3</ANCHOR>
    <SQL_TEXT>wdj.primary_item_id in (select msiv.inventory_item_id from mtl_system_items_vl msiv where msiv.concatenated_segments=:p_assembly_number)</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Item (master, costing enabled only)</LOV_NAME>
    <LOV_GUID>91D022B15B12FFB9E053BB6B63587F0B</LOV_GUID>
    <LOV_QUERY_DSP>select
msiv.concatenated_segments value,
msiv.description
from
mtl_system_items_vl msiv
where
msiv.organization_id=(
select
max(mp.master_organization_id) master_organization_id
from
mtl_parameters mp
where
mp.organization_id=fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) or
fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) is null and
mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;)
) and
msiv.costing_enabled_flag=&apos;Y&apos;
order by
msiv.concatenated_segments</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Assembly Number</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>21</SORT_ORDER>
    <DISPLAY_SEQUENCE>190</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>haouv_ou.name=:p_operating_unit</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Operating Unit</LOV_NAME>
    <LOV_GUID>9B9627743E84607DE053BB6B635805FB</LOV_GUID>
    <LOV_QUERY_DSP>select
haouv.name value,
&apos;Operating Unit&apos; description
from
hr_all_organization_units_vl haouv
where
haouv.organization_id in
(
select
to_number(hoi.org_information3)
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units haou
where
mp.organization_id&lt;&gt;mp.master_organization_id and
mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;) and
mp.organization_id=hoi.organization_id and
hoi.org_information_context=&apos;Accounting Information&apos; and
(:$flex$.ledger is null or to_number(hoi.org_information1) in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)=&apos;Y&apos;)) and
hoi.organization_id=haou.organization_id and
nvl(haou.date_to,sysdate)&gt;=sysdate
)
order by 1</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Operating Unit</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>22</SORT_ORDER>
    <DISPLAY_SEQUENCE>200</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>gl.name=:p_ledger</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>GL Ledger (inv org related)</LOV_NAME>
    <LOV_GUID>8E2FF36EDF3A79D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
gl.name value,
fifsv.id_flex_structure_name||&apos;: &apos;||gl.description description
from
gl_ledgers gl,
fnd_id_flex_structures_vl fifsv
where
gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)&gt;=sysdate and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;)) and
(:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)=&apos;Y&apos;) and
gl.chart_of_accounts_id=fifsv.id_flex_num and
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.application_id=101
order by
fifsv.id_flex_structure_name,
gl.name</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Ledger</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
  </PARAMETERS>
  <TEMPLATES>
  </TEMPLATES>
  <DEFAULT_TEMPLATES>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>
</REPORTS>
</ROOT>
