with wdj00 as
( select /*+ materialize */ wdj.wip_entity_id,min(wt.transaction_date) transaction_date
from wip_discrete_jobs wdj,
org_acct_periods oap,
mtl_parameters mp,
wip_accounting_classes wac,
wip_transactions wt
where wdj.class_code = wac.class_code
and wdj.organization_id = wac.organization_id
and wac.class_type in (1,3,5)
and oap.organization_id = wdj.organization_id
and mp.organization_id = wdj.organization_id
and wt.wip_entity_id=wdj.wip_entity_id
and wt.resource_id is not null
and wt.transaction_date < oap.schedule_close_date + 1
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and 2=2 -- p_org_code
and 3=3 -- p_assembly_number
and 4=4 -- p_period_name, p_wip_job, wip_status, p_wip_class_code
group by wdj.wip_entity_id
),
wdj0 as
(select /*+ materialize */ wdj.wip_entity_id,
wdj.organization_id,
wdj.class_code,
wdj.creation_date,
wdj.scheduled_start_date,
wdj.date_released,
wdj.date_completed,
wdj.date_closed,
wdj.last_update_date,
wdj.primary_item_id,
wdj.lot_number,
wdj.status_type,
wdj.start_quantity,
wdj.net_quantity,
wdj.project_id,
wdj.material_account,
-- Revision for version 1.22
wdj.resource_account,
wdj.outside_processing_account,
-- End revision for version 1.22
wdj.material_overhead_account,
wdj.overhead_account,
wdj.quantity_completed,
wdj.quantity_scrapped,
oap.period_start_date,
oap.schedule_close_date,
oap.period_name,
-- Revision for version 1.22
(case when wdj.date_closed >= oap.period_start_date and wdj.date_closed < oap.schedule_close_date + 1
then 'Variance'
-- the job is open
when wdj.date_closed is null and((wdj.creation_date < oap.schedule_close_date + 1 and not exists(select null from wip_transactions wt where wt.wip_entity_id=wdj.wip_entity_id and wt.resource_id is not null))
or(wdj00.transaction_date<oap.schedule_close_date + 1))
then 'Valuation'
-- the job is closed and ...the job was closed after the accounting period
when wdj.date_closed is not null and wdj.date_closed >= oap.schedule_close_date + 1
then 'Valuation'
end
) Report_Type,
-- End revision for version 1.22
-- Revision for version 1.10
oap.acct_period_id,
mp.primary_cost_method,
mp.organization_code,
wac.class_type
from wip_discrete_jobs wdj,
org_acct_periods oap,
mtl_parameters mp,
wip_accounting_classes wac,
wdj00
where wdj.class_code = wac.class_code
and wdj.organization_id = wac.organization_id
and wac.class_type in (1,3,5)
and wdj.wip_entity_id=wdj00.wip_entity_id(+)
and oap.organization_id = wdj.organization_id
and mp.organization_id = wdj.organization_id
-- find jobs that were open or closed during or after the report period
-- the job is open or opened before the period close date
and ( (wdj.date_closed is null -- the job is open
and((wdj.creation_date < oap.schedule_close_date + 1 and not exists (select null from wip_transactions wt where wt.wip_entity_id=wdj.wip_entity_id and wt.resource_id is not null))
or(wdj00.transaction_date<oap.schedule_close_date + 1)
)
and :p_report_option in ('Open jobs', 'All jobs') -- p_report_option
)
or -- the job is closed and ...the job was closed after the accounting period
(wdj.date_closed is not null
and wdj.date_closed >= oap.schedule_close_date + 1
and((wdj.creation_date < oap.schedule_close_date + 1 and not exists (select null from wip_transactions wt where wt.wip_entity_id=wdj.wip_entity_id and wt.resource_id is not null))
or(wdj00.transaction_date<oap.schedule_close_date + 1)
)
and :p_report_option in ('Open jobs', 'All jobs') -- p_report_option
)
or -- find jobs that were closed during the report period
(wdj.date_closed >= oap.period_start_date
and wdj.date_closed < oap.schedule_close_date + 1
and :p_report_option in ('Closed jobs', 'All jobs') -- p_report_option
)
)
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and 2=2 -- p_org_code
and 3=3 -- p_assembly_number
and 4=4 -- p_period_name, p_wip_job, wip_status, p_wip_class_code
),
wdj as
(select wdjsum.wip_entity_id,
wdjsum.organization_id,
wdjsum.class_code,
wdjsum.creation_date,
wdjsum.scheduled_start_date,
wdjsum.date_released,
wdjsum.date_closed,
wdjsum.date_completed,
wdjsum.last_update_date,
wdjsum.primary_item_id,
wdjsum.lot_number,
wdjsum.status_type,
wdjsum.start_quantity,
wdjsum.net_quantity,
wdjsum.project_id,
wdjsum.material_account,
-- Revision for version 1.22
wdjsum.resource_account,
wdjsum.outside_processing_account,
wdjsum.material_overhead_account,
wdjsum.overhead_account,
-- End revision for version 1.22
wdjsum.period_start_date,
wdjsum.schedule_close_date,
wdjsum.period_name,
-- Revision for version 1.22
wdjsum.report_type,
-- Revision for version 1.10
wdjsum.acct_period_id,
wdjsum.primary_cost_method,
wdjsum.organization_code,
wdjsum.class_type,
sum (wdjsum.quantity_completed) quantity_completed,
sum (wdjsum.quantity_scrapped) quantity_scrapped,
-- Revision for version 1.1, if scrap is not financially recorded do not include in component requirements
sum(decode(:p_include_scrap, 'N', 0, wdjsum.quantity_scrapped)) adj_quantity_scrapped
from (select wdj0.*
from wdj0
union all
select wdj0.wip_entity_id,
wdj0.organization_id,
wdj0.class_code,
wdj0.creation_date,
wdj0.scheduled_start_date,
wdj0.date_released,
wdj0.date_completed,
wdj0.date_closed,
wdj0.last_update_date,
wdj0.primary_item_id,
wdj0.lot_number,
wdj0.status_type,
wdj0.start_quantity,
wdj0.net_quantity,
wdj0.project_id,
wdj0.material_account,
-- Revision for version 1.22
wdj0.resource_account,
wdj0.outside_processing_account,
wdj0.material_overhead_account,
wdj0.overhead_account,
-- End revision for version 1.22
decode(mmt.transaction_type_id,
90, 0, -- scrap assemblies from wip
91, 0, -- return assemblies scrapped from wip
44, -1 * mmt.primary_quantity, -- wip completion
17, mmt.primary_quantity -- wip completion return
) quantity_completed,
decode(mmt.transaction_type_id,
90, mmt.primary_quantity, -- scrap assemblies from wip
91, -1 * mmt.primary_quantity, -- return assemblies scrapped from wip
44, 0, -- wip completion
17, 0 -- wip completion return
) quantity_scrapped,
wdj0.period_start_date,
wdj0.schedule_close_date,
wdj0.period_name,
-- Revision for version 1.22
wdj0.report_type,
-- Revision for version 1.10
wdj0.acct_period_id,
wdj0.primary_cost_method,
wdj0.organization_code,
wdj0.class_type
from wdj0,
mtl_material_transactions mmt
where mmt.transaction_source_type_id = 5
and mmt.transaction_source_id = wdj0.wip_entity_id
and mmt.transaction_date >= wdj0.schedule_close_date + 1
and wdj0.organization_id = mmt.organization_id
) wdjsum
group by
wdjsum.wip_entity_id,
wdjsum.organization_id,
wdjsum.class_code,
wdjsum.creation_date,
wdjsum.scheduled_start_date,
wdjsum.date_released,
wdjsum.date_completed,
wdjsum.date_closed,
wdjsum.last_update_date,
wdjsum.primary_item_id,
wdjsum.lot_number,
wdjsum.status_type,
wdjsum.start_quantity,
wdjsum.net_quantity,
wdjsum.project_id,
wdjsum.material_account,
-- Revision for version 1.22
wdjsum.resource_account,
wdjsum.outside_processing_account,
-- End revision for version 1.22
wdjsum.material_overhead_account,
wdjsum.overhead_account,
wdjsum.period_start_date,
wdjsum.schedule_close_date,
wdjsum.period_name,
-- Revision for version 1.22
wdjsum.report_type,
-- Revision for version 1.10
wdjsum.acct_period_id,
wdjsum.primary_cost_method,
wdjsum.organization_code,
wdjsum.class_type
),
-- Revision for version 1.22
wdj_assys as (select distinct wdj.primary_item_id, wdj.organization_id, wdj.primary_cost_method from wdj),
wdj_wip_trxn as
(
select
max(wt.transaction_date) transaction_date,
wt.transaction_type,
wt.resource_id,
wt.operation_seq_num,
wt.resource_seq_num,
wt.wip_entity_id
from wip_transactions wt,
wdj00 wdj
where wt.wip_entity_id=wdj.wip_entity_id and
wt.resource_id is not null
group by
wt.resource_id,
wt.operation_seq_num,
wt.resource_seq_num,
wt.wip_entity_id,
wt.transaction_type
)
----------------main query starts here--------------
select res_sum.report_type Report_Type,
nvl(gl.short_name, gl.name) Ledger,
haou2.name Operating_Unit,
res_sum.organization_code Org_Code,
res_sum.period_name Period_Name,
&segment_columns
res_sum.class_code WIP_Class,
ml1.meaning Class_Type,
we.wip_entity_name WIP_Job,
(select ppa.segment1
from pa_projects_all ppa
where ppa.project_id = res_sum.project_id) Project_Number,
ml2.meaning Job_Status,
-- Revision for version 1.21
res_sum.creation_date Creation_Date,
res_sum.scheduled_start_date Scheduled_Start_Date,
-- End revision for version 1.21
res_sum.date_released Date_Released,
res_sum.date_completed Date_Completed,
res_sum.date_closed Date_Closed,
res_sum.last_update_date Last_Update_Date,
muomv.uom_code UOM_Code,
msiv_fg.std_lot_size Item_Std_Lot_Size,
-- Revision for version 1.22
cic.cost_type Lot_Size_Cost_Type,
nvl(cic.lot_size, 1) Costing_Lot_Size,
res_sum.start_quantity Start_Quantity,
res_sum.quantity_completed Assembly_Quantity_Completed,
res_sum.quantity_scrapped Assembly_Quantity_Scrapped,
res_sum.fg_total_qty Total_Assembly_Quantity,
msiv_fg.concatenated_segments Assembly,
msiv_fg.description Assembly_Description,
-- Revision for version 1.22
fl.meaning Rolled_Up,
cic.last_rollup_date Last_Cost_Rollup,
-- End revision for version 1.22
fcl.meaning Item_Type,
misv.inventory_item_status_code Item_Status,
ml4.meaning Make_Buy_Code,
&category_columns
-- Revision for version 1.22
res_sum.lot_number Lot_Number,
msiv_osp.concatenated_segments Outside_Processing_Item,
msiv_osp.description OSP_Description,
(select poh.segment1
from po_headers_all poh
where poh.po_header_id = res_sum.po_header_id) PO_Number,
decode(res_sum.line_num, 0, null, res_sum.line_num) Line_Number,
decode(res_sum.release_num, 0, null, res_sum.release_num) PO_Release,
bd.department_code Department,
res_sum.operation_seq_num Operation_Seq_Number,
res_sum.operation_code,
res_sum.resource_seq_num Resource_Seq_Number,
res_sum.resource_code Resource_Code,
-- Revision for version 1.22
res_sum.description Resource_Description,
xxen_util.meaning(res_sum.transaction_type,'WIP_TRANSACTION_TYPE',700) transaction_type_name,
-- Revision for version 1.25
ml5.meaning Auto_Charge,
ml6.meaning Std_Rate,
-- End revision for version 1.25
ml3.meaning Basis_Type,
-- Revision for version 1.25
res_sum.po_currency_code PO_Currency_Code,
-- Revision for version 1.17
decode(res_sum.line_num, 0, null, res_sum.po_unit_price) PO_Unit_Price,
res_sum.cost_type Resource_Cost_Type,
-- Revision for version 1.21
nvl(xxen_util.meaning(res_sum.cost_element_id ,'CST_COST_CODE_TYPE',700),'No Cost') cost_element_type,
gl.currency_code Currency_Code,
res_sum.resource_rate Resource_Rate,
res_sum.res_unit_of_measure Resource_UOM_Code,
res_sum.usage_rate_or_amount Quantity_Per_Assembly,
res_sum.total_req_quantity Total_Required_Quantity,
res_sum.applied_resource_units Total_Units_Applied,
-- Revision for version 1.17
-- res_sum.qty_variance Quantity_Variance,
round(res_sum.applied_resource_units - res_sum.total_req_quantity,6) Quantity_Variance,
res_sum.std_resource_value Standard_Resource_Value,
round(res_sum.applied_resource_value,2) Applied_Resource_Value,
-- res_sum.res_efficiency_variance Resource_Efficiency_Variance,
round(res_sum.applied_resource_value - res_sum.std_resource_value,2) Resource_Efficiency_Variance
from mtl_system_items_vl msiv_fg,
mtl_system_items_vl msiv_osp,
mtl_units_of_measure_vl muomv,
mtl_item_status_vl misv,
bom_departments bd,
wip_entities we,
mfg_lookups ml1, -- WIP Class
mfg_lookups ml2, -- WIP Status
mfg_lookups ml3, -- Basis Type
mfg_lookups ml4, -- Planning Make Buy
-- Revision for version 1.25
mfg_lookups ml5, -- Autocharge Type
mfg_lookups ml6, -- Standard Rate
-- End revision for version 1.25
-- Revision for version 1.22
fnd_lookups fl, -- Rolled Up
fnd_common_lookups fcl, -- Item Type
gl_code_combinations gcc, -- wip job accounts
hr_organization_information hoi,
hr_all_organization_units haou,
hr_all_organization_units haou2,
gl_ledgers gl,
-- Revision for version 1.22
-- cst_item_costs cic,
-- Get the assembly cost type, lot size and cost rollup status
(select cic.organization_id,
cic.inventory_item_id,
cct.cost_type,
cic.lot_size,
case
when sum(case
when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then 1
else 0
end) > 0 then 'Y'
else 'N'
end rolled_up,
max(case
when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then cicd.creation_date
else null
end) last_rollup_date
from cst_item_costs cic,
cst_item_cost_details cicd,
cst_cost_types cct,
-- Limit to assemblies on WIP jobs
wdj_assys
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 cic.inventory_item_id = wdj_assys.primary_item_id
and cic.organization_id = wdj_assys.organization_id
and cct.cost_type_id = cic.cost_type_id
-- Revision for version 1.22 and 1.23
and cct.cost_type = decode(:p_cost_type,
null, (select cct.cost_type
from dual
where cct.cost_type_id = wdj_assys.primary_cost_method
),
:p_cost_type
)
group by
cic.organization_id,
cic.inventory_item_id,
cct.cost_type,
cic.cost_type_id,
cic.lot_size
union all
select cic.organization_id,
cic.inventory_item_id,
cct.cost_type,
cic.lot_size,
case
when sum(case
when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then 1
else 0
end) > 0 then 'Y'
else 'N'
end rolled_up,
max(case
when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then cicd.creation_date
else null
end) last_rollup_date
from cst_item_costs cic,
cst_item_cost_details cicd,
cst_cost_types cct,
-- Limit to assemblies on WIP jobs
wdj_assys
where cic.cost_type_id = cicd.cost_type_id (+)
and cic.inventory_item_id = cicd.inventory_item_id (+)
and cic.organization_id = cicd.organization_id (+)
and cic.inventory_item_id = wdj_assys.primary_item_id
and cic.organization_id = wdj_assys.organization_id
and cic.cost_type_id = wdj_assys.primary_cost_method -- this gets the Frozen Costs
and cct.cost_type_id <> wdj_assys.primary_cost_method -- this avoids getting the Frozen costs twice
-- Revision for version 1.22 and 1.23
and cct.cost_type = decode(:p_cost_type,
null, (select cct.cost_type
from dual
where cct.cost_type_id = wdj_assys.primary_cost_method
),
:p_cost_type
)
-- ====================================
-- Find all the Frozen costs not in the
-- Pending or unimplemented cost type
-- ====================================
and not exists (
select 'x'
from cst_item_costs cic2
where cic2.organization_id = cic.organization_id
and cic2.inventory_item_id = cic.inventory_item_id
and cic2.cost_type_id = cct.cost_type_id)
group by
cic.organization_id,
cic.inventory_item_id,
cct.cost_type,
cic.cost_type_id,
cic.lot_size
) cic,
-- End revision for version 1.22
-- ========================================================
-- Get the WIP Resource Information in a three part union
-- which is then condensed into a summary data set
-- ========================================================
-- ========================================================
-- Section I Condense into a summary data set.
-- =======================================================
(select res.report_type,
res.period_name,
res.organization_code,
res.organization_id,
res.primary_cost_method,
res.account,
res.class_code,
res.class_type,
res.wip_entity_id,
res.project_id,
res.status_type,
res.primary_item_id,
-- Revision for version 1.22
res.lot_number,
-- Revision for version 1.21
res.creation_date,
res.scheduled_start_date,
-- End revision for version 1.21
res.date_released,
res.date_completed,
res.date_closed,
res.last_update_date,
res.start_quantity,
res.quantity_completed,
res.quantity_scrapped,
res.fg_total_qty,
max(res.po_header_id) po_header_id,
max(res.line_num) line_num,
max(res.release_num) release_num,
max(res.purchase_item_id) purchase_item_id,
min(res.transaction_type)transaction_type,
res.department_id,
-- Revision for version 1.22
1 level_num,
res.operation_seq_num,
-- Revision for version 1.25
res.autocharge_type,
res.standard_rate_flag,
res.po_currency_code,
-- End revision for version 1.25
res.resource_seq_num,
res.resource_code,
-- Revision for version 1.22
res.description,
res.basis_type,
res.res_unit_of_measure,
max(res.po_unit_price) po_unit_price,
res.cost_type,
res.cost_element_id,
res.operation_code,
res.resource_rate,
sum(res.usage_rate_or_amount) usage_rate_or_amount,
sum(res.total_req_quantity) total_req_quantity,
sum(res.applied_resource_units) applied_resource_units,
-- Revision for version 1.17
-- sum(res.qty_variance) qty_variance,
-- Revision for version 1.17
sum(res.std_resource_value) std_resource_value,
sum(res.applied_resource_value) applied_resource_value
from -- ========================================================
-- Section II.A.OSP
-- First get the OSP resource information with the related
-- PO number and unit price information
-- =======================================================
-- Revision for version 1.22
(select 'II.A' section,
wdj.report_type,
wdj.period_name,
wdj.organization_code,
wdj.organization_id,
-- Revision for version 1.22
-- cct.cost_type primary_cost_type,
wdj.primary_cost_method,
-- End revision for version 1.22
--wdj.outside_processing_account account,
br.cost_element_id,
bso.operation_code,
decode(br.cost_element_id,
1, wdj.material_account,
2, wdj.material_overhead_account,
3, wdj.resource_account,
4, wdj.outside_processing_account,
5, wdj.overhead_account,
wdj.outside_processing_account
)account,
wdj.class_code,
wdj.class_type,
wdj.wip_entity_id,
wdj.project_id,
wdj.status_type,
wdj.primary_item_id,
-- Revision for version 1.22
wdj.lot_number,
-- Revision for version 1.21
wdj.creation_date,
wdj.scheduled_start_date,
-- End revision for version 1.21
wdj.date_released,
wdj.date_completed,
wdj.date_closed,
wdj.last_update_date,
wdj.start_quantity,
wdj.quantity_completed,
wdj.quantity_scrapped,
nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0) fg_total_qty,
poh.po_header_id,
pol.line_num,
pr.release_num,
br.purchase_item_id,
wo.department_id,
wo.operation_seq_num,
wor.resource_seq_num,
br.resource_code,
-- Revision for version 1.22
br.description,
wor.basis_type,
-- Revision for version 1.25
wor.autocharge_type,
wor.standard_rate_flag,
wt.transaction_type,
poh.currency_code po_currency_code,
-- End revision for version 1.25
br.unit_of_measure res_unit_of_measure,
nvl(pll.price_override, pol.unit_price) po_unit_price,
crc.cost_type cost_type,
nvl(crc.resource_rate,0) resource_rate,
nvl(wor.usage_rate_or_amount,0) usage_rate_or_amount,
-- Revision for version 1.22
-- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
-- use the completions plus scrap quantities unless for lot-based jobs
nvl(round(case when wdj.status_type in (4,5,7,12,14,15) then
decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
-- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity
* decode(wor.repetitive_schedule_id,
null,
wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)),
wrs.daily_production_rate * wrs.processing_work_days
)
) else
-- else use the start quantity times the usage rate or amount
decode(nvl(:p_use_completion_qtys,'N'),
'Y', decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
* decode(wdj.class_type,
5, nvl(wdj.quantity_completed, 0),
nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0))
)
),
-- Revision for version 1.24
'N', decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
-- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity
* decode(wor.repetitive_schedule_id,
null,
wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)),
wrs.daily_production_rate * wrs.processing_work_days
)
)
) end
,6),0) total_req_quantity,
nvl(applied_resource_units,0) applied_resource_units,
-- Revision for version 1.17 and 1.22
-- If the job status is "Complete" then use the completions plus
-- scrap quantities else use the planned required quantities; and
-- use the completions plus scrap quantities unless for lot-based jobs
-- Get the total required quantity
nvl(round(case when wdj.status_type in (4,5,7,12,14,15) then
decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
-- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity
* decode(wor.repetitive_schedule_id,
null,
wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)),
wrs.daily_production_rate * wrs.processing_work_days
)
) else
-- else use the start quantity times the usage rate or amount
decode(nvl(:p_use_completion_qtys,'N'),
'Y', decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
* decode(wdj.class_type,
5, nvl(wdj.quantity_completed, 0),
nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0))
)
),
-- Revision for version 1.24
'N', decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
-- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity
* decode(wor.repetitive_schedule_id,
null,
wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)),
wrs.daily_production_rate * wrs.processing_work_days
)
)
) end
,6),0) -- total_req_quantity
-- And multiply by the AvgRate or Frozen standard costs
* nvl(crc.resource_rate,0) std_resource_value,
-- End revision for version 1.17
nvl(wor.applied_resource_value,0) applied_resource_value
from
-- Revision for version 1.22
-- wip_accounting_classes wac,
-- org_acct_periods oap
-- mtl_parameters mp,
-- cst_cost_types cct,
wdj,
-- End revision for version 1.22
wip_operations wo,
wip_operation_resources wor,
wip_repetitive_schedules wrs,
bom_resources br,
bom_standard_operations bso,
wdj_wip_trxn wt,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_releases_all pr,
po_distributions_all pod,
-- Revision for version 1.22
-- Get the Resource Cost Type, Cost Basis Type and Resource Rates
(select crc.resource_id,
crc.organization_id,
crc.last_update_date,
crc.cost_type_id,
cct.cost_type,
crc.resource_rate resource_rate
from cst_resource_costs crc,
cst_cost_types cct,
mtl_parameters mp
where crc.cost_type_id = decode(cct.cost_type_id,
2, mp.avg_rates_cost_type_id, -- Average Costing
5, mp.avg_rates_cost_type_id, -- FIFO Costing
6, mp.avg_rates_cost_type_id, -- LIFO Costing
cct.cost_type_id)
and crc.organization_id = mp.organization_id
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and 2=2 -- p_org_code
and cct.cost_type = decode(:p_cost_type,
null, (select cct.cost_type
from dual
where cct.cost_type_id = mp.primary_cost_method
),
:p_cost_type
)
group by
crc.resource_id,
crc.organization_id,
crc.last_update_date,
crc.cost_type_id,
cct.cost_type,
crc.resource_rate
union all
-- If missing from the above query, get the Frozen or AvgRates Resource Costs
select crc.resource_id,
crc.organization_id,
crc.last_update_date,
crc.cost_type_id,
cct.cost_type,
crc.resource_rate resource_rate
from cst_resource_costs crc,
cst_cost_types cct,
mtl_parameters mp
where crc.organization_id = mp.organization_id
and crc.cost_type_id = decode(mp.primary_cost_method,
1, 1, -- Standard Costing, Frozen Cost Type
2, mp.avg_rates_cost_type_id, -- Average Costing
3, -99, -- Periodic Average
4, -99, -- Periodic Incremental LIFO
5, mp.avg_rates_cost_type_id, -- FIFO Costing
6, mp.avg_rates_cost_type_id -- LIFO Costing
)
-- Don't get the Frozen or AvgRates resource costs twice
and cct.cost_type_id <> decode(mp.primary_cost_method,
1, 1, -- Standard Costing, Frozen Cost Type
2, mp.avg_rates_cost_type_id, -- Average Costing
3, -99, -- Periodic Average
4, -99, -- Periodic Incremental LIFO
5, mp.avg_rates_cost_type_id, -- FIFO Costing
6, mp.avg_rates_cost_type_id -- LIFO Costing
)
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and 2=2 -- p_org_code
and cct.cost_type = decode(:p_cost_type,
null, (select cct.cost_type
from dual
where cct.cost_type_id = mp.primary_cost_method
),
:p_cost_type
)
-- ====================================
-- Find all the resource costs not in the
-- Pending or unimplemented cost type
-- ====================================
and not exists
(select 'x'
from cst_resource_costs crc2
where crc2.organization_id = crc.organization_id
and crc2.resource_id = crc.resource_id
and crc2.cost_type_id = case
when mp.primary_cost_method = 1 then cct.cost_type_id
when mp.primary_cost_method = 2 and cct.cost_type_id <> 2 then cct.cost_type_id
when mp.primary_cost_method = 2 and cct.cost_type_id = 2 then mp.avg_rates_cost_type_id
when mp.primary_cost_method = 3 then -99
when mp.primary_cost_method = 4 then -99
when mp.primary_cost_method = 5 and cct.cost_type_id <> 5 then cct.cost_type_id
when mp.primary_cost_method = 5 and cct.cost_type_id = 5 then mp.avg_rates_cost_type_id
when mp.primary_cost_method = 6 and cct.cost_type_id <> 6 then cct.cost_type_id
when mp.primary_cost_method = 6 and cct.cost_type_id = 6 then mp.avg_rates_cost_type_id
else cct.cost_type_id
end
)
group by
crc.resource_id,
crc.organization_id,
crc.last_update_date,
crc.cost_type_id,
cct.cost_type,
crc.resource_rate
) crc
-- End revision for version 1.22
-- ===========================================
-- WIP_Job Entity, Class and Period joins
-- ===========================================
where wo.wip_entity_id = wdj.wip_entity_id
and wo.organization_id = wdj.organization_id
and wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and wor.resource_id = br.resource_id
-- Revision for version 1.22
-- and cct.cost_type_id = wdj.primary_cost_method
-- ===========================================
-- PO Table Joins for version 1.8
-- ===========================================
and poh.po_header_id = pod.po_header_id
and pol.po_line_id = pod.po_line_id
and pll.line_location_id = pod.line_location_id
and pod.po_release_id = pr.po_release_id (+)
and wor.wip_entity_id = pod.wip_entity_id
and wor.resource_id = pod.bom_resource_id
and wor.resource_seq_num = pod.wip_resource_seq_num
and wor.operation_seq_num = pod.wip_operation_seq_num
and wor.organization_id = pod.destination_organization_id
-- ===========================================
-- Cost Table Joins for version 1.22
-- ===========================================
and wor.resource_id = crc.resource_id (+)
and wor.organization_id = crc.organization_id (+)
-- ===========================================
-- Use the joins in wip_operation_resources_v as the same is used in Oracle forms
-- ===========================================
and wrs.organization_id(+)= wor.organization_id
and wrs.wip_entity_id(+)= wor.wip_entity_id
and wrs.repetitive_schedule_id(+)= wor.repetitive_schedule_id
and wor.wip_entity_id=wt.wip_entity_id(+)
and wor.resource_id=wt.resource_id(+)
and wor.operation_seq_num=wt.operation_seq_num(+)
and wor.resource_seq_num=wt.resource_seq_num(+)
and case when wt.wip_entity_id=wor.wip_entity_id and wt.transaction_date<wdj.schedule_close_date+1 then 'Y'
when not exists(select null from wip_transactions wt1 where wt1.wip_entity_id=wdj.wip_entity_id) then 'Y'
end='Y'
and bso.standard_operation_id(+)=wo.standard_operation_id
and nvl(bso.operation_type, 1)=1
and bso.line_id is null
and 8=8 -- p_resource_code
union all
-- =======================================================
-- Section II.B. Non-OSP
-- Now get the non-OSP resource information
-- =======================================================
-- Revision for version 1.22
select 'II.B' section,
wdj.report_type,
wdj.period_name,
wdj.organization_code,
wdj.organization_id,
-- Revision for version 1.22
-- cct.cost_type primary_cost_type,
wdj.primary_cost_method,
-- End revision for version 1.22
--wdj.resource_account account,
br.cost_element_id,
bso.operation_code,
decode(br.cost_element_id,
1, wdj.material_account,
2, wdj.material_overhead_account,
3, wdj.resource_account,
4, wdj.outside_processing_account,
5, wdj.overhead_account,
wdj.resource_account
)account,
wdj.class_code,
wdj.class_type,
wdj.wip_entity_id,
wdj.project_id,
wdj.status_type,
wdj.primary_item_id,
-- Revision for version 1.22
wdj.lot_number,
-- Revision for version 1.21
wdj.creation_date,
wdj.scheduled_start_date,
-- End revision for version 1.21
wdj.date_released,
wdj.date_completed,
wdj.date_closed,
wdj.last_update_date,
wdj.start_quantity,
wdj.quantity_completed,
wdj.quantity_scrapped,
nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0) fg_total_qty,
0 purchase_item_id,
0 po_header_id,
0 line_num,
0 release_num,
wo.department_id,
wo.operation_seq_num,
wor.resource_seq_num,
br.resource_code,
-- Revision for version 1.22
br.description,
wor.basis_type,
-- Revision for version 1.25
wor.autocharge_type,
wor.standard_rate_flag,
wt.transaction_type,
null po_currency_code,
-- End revision for version 1.25
br.unit_of_measure res_unit_of_measure,
0 po_unit_price,
-- Revision for version 1.22
crc.cost_type,
nvl(crc.resource_rate,0) resource_rate,
-- End revision for version 1.22
nvl(wor.usage_rate_or_amount,0) usage_rate_or_amount,
-- Revision for version 1.22
-- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
-- use the completions plus scrap quantities unless for lot-based jobs
nvl(round(case when wdj.status_type in (4,5,7,12,14,15) then
decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
-- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity
* decode(wor.repetitive_schedule_id,
null,
wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)),
wrs.daily_production_rate * wrs.processing_work_days
)
) else
-- else use the start quantity times the usage rate or amount
decode(nvl(:p_use_completion_qtys,'N'),
'Y', decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
* decode(wdj.class_type,
5, nvl(wdj.quantity_completed, 0),
nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0))
)
),
-- Revision for version 1.24
'N', decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
-- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity
* decode(wor.repetitive_schedule_id,
null,
wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)),
wrs.daily_production_rate * wrs.processing_work_days
)
)
) end
,6),0) total_req_quantity,
-- End revision for version 1.22
nvl(applied_resource_units,0) applied_resource_units,
-- Revision for version 1.17 and 1.22
-- If the job status is "Complete" then use the completions plus
-- scrap quantities else use the planned required quantities; and
-- use the completions plus scrap quantities unless for lot-based jobs
-- Get the total required quantity
nvl(round(case when wdj.status_type in (4,5,7,12,14,15) then
decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
-- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity
* decode(wor.repetitive_schedule_id,
null,
wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)),
wrs.daily_production_rate * wrs.processing_work_days
)
) else
-- else use the start quantity times the usage rate or amount
decode(nvl(:p_use_completion_qtys,'N'),
'Y', decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
* decode(wdj.class_type,
5, nvl(wdj.quantity_completed, 0),
nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0))
)
),
-- Revision for version 1.24
'N', decode(wor.basis_type,
2, nvl(wor.usage_rate_or_amount,0), -- Lot
nvl(wor.usage_rate_or_amount,0) -- Any other basis
-- Use the logic in wip_operation_resources_v as the same is used in Oracle forms to derive total_required_quantity
* decode(wor.repetitive_schedule_id,
null,
wdj.start_quantity - decode(:p_include_scrap, 'N', 0, null, 0, nvl(wo.cumulative_scrap_quantity, 0)),
wrs.daily_production_rate * wrs.processing_work_days
)
)
) end
,6),0) -- total_req_quantity
-- And multiply by the AvgRate or Frozen standard costs
* nvl(crc.resource_rate,0) std_resource_value,
-- End revision for version 1.17 and 1.22
nvl(wor.applied_resource_value,0) applied_resource_value
from -- Revision for version 1.22
-- wip_accounting_classes wac,
-- org_acct_periods oap
-- mtl_parameters mp,
-- cst_cost_types cct,
wdj,
wip_operations wo,
wip_operation_resources wor,
wip_repetitive_schedules wrs,
wdj_wip_trxn wt,
bom_standard_operations bso,
bom_resources br,
-- Revision for version 1.22
-- Get the Resource Cost Type, Cost Basis Type and Resource Rates
(select crc.resource_id,
crc.organization_id,
crc.last_update_date,
crc.cost_type_id,
cct.cost_type,
crc.resource_rate resource_rate
from cst_resource_costs crc,
cst_cost_types cct,
mtl_parameters mp
where crc.cost_type_id = decode(cct.cost_type_id,
2, mp.avg_rates_cost_type_id, -- Average Costing
5, mp.avg_rates_cost_type_id, -- FIFO Costing
6, mp.avg_rates_cost_type_id, -- LIFO Costing
cct.cost_type_id)
and crc.organization_id = mp.organization_id
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and 2=2 -- p_org_code
and cct.cost_type = decode(:p_cost_type,
null, (select cct.cost_type
from dual
where cct.cost_type_id = mp.primary_cost_method
),
:p_cost_type
)
group by
crc.resource_id,
crc.organization_id,
crc.last_update_date,
crc.cost_type_id,
cct.cost_type,
crc.resource_rate
union all
-- If missing from the above query, get the Frozen or AvgRates Resource Costs
select crc.resource_id,
crc.organization_id,
crc.last_update_date,
crc.cost_type_id,
cct.cost_type,
crc.resource_rate resource_rate
from cst_resource_costs crc,
cst_cost_types cct,
mtl_parameters mp
where crc.organization_id = mp.organization_id
and crc.cost_type_id = decode(mp.primary_cost_method,
1, 1, -- Standard Costing, Frozen Cost Type
2, mp.avg_rates_cost_type_id, -- Average Costing
3, -99, -- Periodic Average
4, -99, -- Periodic Incremental LIFO
5, mp.avg_rates_cost_type_id, -- FIFO Costing
6, mp.avg_rates_cost_type_id -- LIFO Costing
)
-- Don't get the Frozen or AvgRates resource costs twice
and cct.cost_type_id <> decode(mp.primary_cost_method,
1, 1, -- Standard Costing, Frozen Cost Type
2, mp.avg_rates_cost_type_id, -- Average Costing
3, -99, -- Periodic Average
4, -99, -- Periodic Incremental LIFO
5, mp.avg_rates_cost_type_id, -- FIFO Costing
6, mp.avg_rates_cost_type_id -- LIFO Costing
)
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
and 2=2 -- p_org_code
and cct.cost_type = decode(:p_cost_type,
null, (select cct.cost_type
from dual
where cct.cost_type_id = mp.primary_cost_method
),
:p_cost_type
)
-- ====================================
-- Find all the resource costs not in the
-- Pending or unimplemented cost type
-- ====================================
and not exists
(select 'x'
from cst_resource_costs crc2
where crc2.organization_id = crc.organization_id
and crc2.resource_id = crc.resource_id
and crc2.cost_type_id = case
when mp.primary_cost_method = 1 then cct.cost_type_id
when mp.primary_cost_method = 2 and cct.cost_type_id <> 2 then cct.cost_type_id
when mp.primary_cost_method = 2 and cct.cost_type_id = 2 then mp.avg_rates_cost_type_id
when mp.primary_cost_method = 3 then -99
when mp.primary_cost_method = 4 then -99
when mp.primary_cost_method = 5 and cct.cost_type_id <> 5 then cct.cost_type_id
when mp.primary_cost_method = 5 and cct.cost_type_id = 5 then mp.avg_rates_cost_type_id
when mp.primary_cost_method = 6 and cct.cost_type_id <> 6 then cct.cost_type_id
when mp.primary_cost_method = 6 and cct.cost_type_id = 6 then mp.avg_rates_cost_type_id
else cct.cost_type_id
end
)
group by
crc.resource_id,
crc.organization_id,
crc.last_update_date,
crc.cost_type_id,
cct.cost_type,
crc.resource_rate
) crc
-- End revision for version 1.22
-- ===========================================
-- WIP_Job Entity, Class and Period joins
-- ===========================================
where wo.wip_entity_id = wdj.wip_entity_id
and wo.organization_id = wdj.organization_id
and wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and br.resource_id = wor.resource_id
-- Only select non-OSP resources
and br.purchase_item_id is null
-- Revision for version 1.22
-- and cct.cost_type_id = mp.primary_cost_method
-- ===========================================
-- Cost Table Joins for version 1.22
-- ===========================================
and wor.resource_id = crc.resource_id (+)
and wor.organization_id = crc.organization_id (+)
-- ===========================================
-- Use the joins in wip_operation_resources_v as the same is used in Oracle forms
-- ===========================================
and wrs.organization_id(+)= wor.organization_id
and wrs.wip_entity_id(+)= wor.wip_entity_id
and wrs.repetitive_schedule_id(+)= wor.repetitive_schedule_id
and wor.wip_entity_id=wt.wip_entity_id(+)
and wor.resource_id=wt.resource_id(+)
and wor.operation_seq_num=wt.operation_seq_num(+)
and wor.resource_seq_num=wt.resource_seq_num(+)
and case when wt.wip_entity_id=wor.wip_entity_id and wt.transaction_date<wdj.schedule_close_date+1 then 'Y'
when not exists(select null from wip_transactions wt1 where wt1.wip_entity_id=wdj.wip_entity_id) then 'Y'
end='Y'
and bso.standard_operation_id(+)=wo.standard_operation_id
and nvl(bso.operation_type, 1)=1
and bso.line_id is null
and 8=8 -- p_resource_code
) res
group by
res.report_type,
res.period_name,
res.organization_code,
res.organization_id,
res.primary_cost_method,
res.cost_element_id,
res.operation_code,
res.account,
res.class_code,
res.class_type,
res.wip_entity_id,
res.project_id,
res.status_type,
res.primary_item_id,
-- Revision for version 1.22
res.lot_number,
-- Revision for version 1.21
res.creation_date,
res.scheduled_start_date,
-- End revision for version 1.21
res.date_released,
res.date_completed,
res.date_closed,
res.last_update_date,
res.start_quantity,
res.quantity_completed,
res.quantity_scrapped,
res.fg_total_qty,
res.department_id,
-- Revision for version 1.22
1, -- level_num
res.operation_seq_num,
-- Revision for version 1.25
res.autocharge_type,
res.standard_rate_flag,
res.po_currency_code,
-- End revision for version 1.25
res.resource_seq_num,
res.resource_code,
-- Revision for version 1.22
res.description,
res.basis_type,
res.res_unit_of_measure,
-- Revision for version 1.22
res.cost_type,
res.resource_rate
) res_sum
-- ===========================================
-- Account, cost and department joins
-- ===========================================
where we.wip_entity_id = res_sum.wip_entity_id
and msiv_fg.organization_id = res_sum.organization_id
and msiv_fg.inventory_item_id = res_sum.primary_item_id -- FG assembly item
and msiv_osp.organization_id (+) = res_sum.organization_id
and msiv_osp.inventory_item_id (+) = res_sum.purchase_item_id -- OSP item
and muomv.uom_code = msiv_fg.primary_uom_code
and misv.inventory_item_status_code = msiv_fg.inventory_item_status_code
and bd.department_id = res_sum.department_id
and cic.organization_id = res_sum.organization_id
and cic.inventory_item_id = res_sum.primary_item_id
-- Revision for version 1.22
-- and cic.cost_type_id = res_sum.primary_cost_method
and gcc.code_combination_id (+) = res_sum.account
-- ===========================================
-- Lookup Codes
-- ===========================================
and ml1.lookup_type = 'WIP_CLASS_TYPE'
and ml1.lookup_code = res_sum.class_type
and ml2.lookup_type = 'WIP_JOB_STATUS'
and ml2.lookup_code = res_sum.status_type
and ml3.lookup_type = 'CST_BASIS'
and ml3.lookup_code = res_sum.basis_type
and ml4.lookup_type = 'MTL_PLANNING_MAKE_BUY'
and ml4.lookup_code = msiv_fg.planning_make_buy_code
-- Revision for version 1.25
and ml5.lookup_type (+) = 'BOM_AUTOCHARGE_TYPE'
and ml5.lookup_code (+) = res_sum.autocharge_type
and ml6.lookup_type = 'SYS_YES_NO'
and ml6.lookup_code = res_sum.standard_rate_flag
-- End revision for version 1.25
and fcl.lookup_type (+) = 'ITEM_TYPE'
and fcl.lookup_code (+) = msiv_fg.item_type
-- Revision for version 1.8
and fl.lookup_type = 'YES_NO'
and fl.lookup_code = cic.rolled_up
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
and hoi.org_information_context = 'Accounting Information'
and hoi.organization_id = res_sum.organization_id
and hoi.organization_id = haou.organization_id -- this gets the organization name
and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id
and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id
and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and 1=1 -- p_osp_item, p_operating_unit, p_ledger --
-- order by Report Type, Ledger, Operating_Unit, Org_Code, Period_Name, Accounts, WIP_Class, WIP_Job,
order by
res_sum.report_type,
nvl(gl.short_name, gl.name),
haou2.name, -- Operating Unit
res_sum.organization_code,
&segment_columns_grp
res_sum.class_code,
we.wip_entity_name,
msiv_osp.concatenated_segments,
(select poh.segment1
from po_headers_all poh
where poh.po_header_id = res_sum.po_header_id), -- PO Number
decode(res_sum.line_num, 0, null, res_sum.line_num), -- Line Number
decode(res_sum.release_num, 0, null, res_sum.release_num), -- PO Release
res_sum.operation_seq_num,
res_sum.resource_seq_num,
res_sum.resource_code |
