WIP Account Distribution

Description
Categories: Enginatics
Repository: Github
Detail WIP report that lists resource transaction account distributions.

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
gl.name ledger,
haouv.name operating_unit,
ood.organization_code,
wta.transaction_date,
we.wip_entity_name job_schedule,
msiv.concatenated_segments assembly,
msiv.description assembly_description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
wl.line_code line,
wta.transaction_id,
wt.operation_seq_num operation_seq,
bd.department_code department,
wt.resource_seq_num resource_seq,
br.resource_code,
br.description resource_description,
muot.unit_of_measure_tl uom,
xxen_util.meaning(wta.basis_type,'CST_BASIS',700) basis,
wta.primary_quantity quantity,
wta.rate_or_amount unit_cost,
xxen_util.meaning(br.cost_code_type,'CST_COST_CODE_TYPE',700) cost_type,
xxen_util.meaning(wta.accounting_line_type,'CST_ACCOUNTING_LINE_TYPE',700) accounting_line_type,
gcck.concatenated_segments account,
wta.base_transaction_value value,
gl.currency_code,
wta.transaction_value orig_value,
decode(wta.currency_code,gl.currency_code,null,wta.currency_code) orig_currency,
decode(wta.currency_code,gl.currency_code,null,wta.currency_conversion_rate) conversion_rate,
decode(wta.currency_code,gl.currency_code,null,wta.currency_conversion_date) conversion_date,
xxen_util.meaning(wt.transaction_type,'WIP_TRANSACTION_TYPE',700) transaction_type,
ca.activity,
xxen_util.meaning(wt.standard_rate_flag,'SYS_YES_NO',700) standard_rate,
xxen_util.segments_description(wta.reference_account,gl.chart_of_accounts_id) account_description,
&gl_account_segments
nvl(wri.class_code,wdj.class_code) accounting_class,
xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) type,
xxen_util.meaning(wdj.job_type,'WIP_DISCRETE_JOB',700) job_type,
xxen_util.meaning(wdj.wip_supply_type,'WIP_SUPPLY',700) wip_supply_type,
mck.concatenated_segments item_category,
nvl(ppx.npw_number,ppx.employee_number) employee_num,
ppx.full_name employee,
poh.segment1 po_number,
wta.gl_batch_id gl_batch,
ogb.gl_batch_date,
ogb.description gl_batch_description,
wta.wip_entity_id
from
gl_ledgers gl,
hr_all_organization_units_vl haouv,
org_organization_definitions ood,
wip_transaction_accounts wta,
wip_transactions wt,
wip_entities we,
wip_discrete_jobs wdj,
gl_code_combinations_kfv gcck,
wip_lines wl,
mtl_item_categories mic,
mtl_categories_kfv mck,
mtl_system_items_vl msiv,
wip_repetitive_items wri,
bom_departments bd,
bom_resources br,
mtl_units_of_measure_tl muot,
cst_activities ca,
org_gl_batches ogb,
per_people_x ppx,
po_headers_all poh
where
1=1 and
&account_where_clause
wta.organization_id=ood.organization_id and
ood.set_of_books_id=gl.ledger_id and
ood.operating_unit=haouv.organization_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
haouv.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
wta.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
wta.accounting_line_type<>15 and
wta.transaction_id=wt.transaction_id and
wta.wip_entity_id=we.wip_entity_id and
wta.wip_entity_id=wdj.wip_entity_id(+) and
wta.organization_id=wdj.organization_id(+) and
wta.reference_account=gcck.code_combination_id and
wt.line_id=wl.line_id(+) and
wt.organization_id=wl.organization_id(+) and
we.organization_id=mic.organization_id(+) and
we.primary_item_id=mic.inventory_item_id(+) and
mic.category_set_id(+)=:category_set_id and
mic.category_id=mck.category_id(+) and
we.organization_id=msiv.organization_id(+) and
we.primary_item_id=msiv.inventory_item_id(+) and
wt.organization_id=wri.organization_id(+) and
wt.wip_entity_id=wri.wip_entity_id(+) and
wt.line_id=wri.line_id(+) and
wt.department_id=bd.department_id(+) and
wta.resource_id=br.resource_id(+) and
br.unit_of_measure=muot.uom_code(+) and
muot.language(+)=userenv('lang') and
wta.activity_id=ca.activity_id(+) and
wta.organization_id=ogb.organization_id(+) and
wta.gl_batch_id=ogb.gl_batch_id(+) and
wt.employee_id=ppx.person_id(+) and
wt.po_header_id=poh.po_header_id(+)
order by
ood.organization_code,
ood.organization_name,
wta.transaction_date desc,
wta.transaction_id desc,
cost_type,
accounting_line_type
Parameter Name SQL text Validation
Ledger
gl.name=:ledger and 
haouv.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)
LOV
Operating Unit
haouv.name=:operating_unit
LOV
Organization Code
ood.organization_code=:organization_code
LOV
Date From
wta.transaction_date>=:date_from
DateTime
Date To
wta.transaction_date<:date_to+1
DateTime
Account
select
'gcck.'||lower(fifsv.application_column_name)||'=:account and
' sql_text
from
fnd_id_flex_segments_vl fifsv
where
fifsv.application_id=101 and
fifsv.id_flex_code='GL#' and
fifsv.id_flex_num=(
select
gl.chart_of_accounts_id
from
org_organization_definitions ood,
gl_ledgers gl,
hr_all_organization_units_vl haouv
where
(
:ledger is null 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(+)) or
xxen_util.contains(:ledger,gl.name)='Y'
) and
(
:operating_unit is null and haouv.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) or
xxen_util.contains(:operating_unit,haouv.name)='Y'
) and
(
:organization_code is null and ood.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) or 
xxen_util.contains(:organization_code,ood.organization_code)='Y' 
) and
ood.set_of_books_id=gl.ledger_id and
ood.operating_unit=haouv.organization_id and
rownum=1
) and
(fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_ACCOUNT' and fsav.attribute_value='Y')
LOV
Job
we.wip_entity_name=:job_name
LOV
Line
wt.line_id=:line_id
LOV Oracle
Item
msiv.concatenated_segments like :item
LOV
Transaction Type
wt.transaction_type=:transaction_type
LOV Oracle
Department
wt.department_id=:department_id
LOV Oracle
Resource
br.resource_code=:resource_code
LOV Oracle
Activity
wta.activity_id=:activity_id
LOV Oracle
Class
nvl(wri.class_code,wdj.class_code)=:class_code
LOV
Currency
wta.currency_code=:currency_code
LOV Oracle
Category Set
 
LOV Oracle
Category
(msiv.organization_id, msiv.inventory_item_id) in
(
select
mic.organization_id,
mic.inventory_item_id
from
mtl_item_categories mic,
mtl_category_sets mcs,
mtl_categories_kfv mck
where
mck.concatenated_segments=:category and
mck.structure_id=mcs.structure_id and
mck.category_id=mic.category_id and
mcs.category_set_id=mic.category_set_id
)
LOV
Project Id
 
LOV Oracle
Employee Number
nvl(ppx.npw_number,ppx.employee_number)=:employee_num
LOV
PO Number
poh.segment1=:po_number
Char