--Redundant noetix_sys.xxk_sys_item
--noetix_sys.invg0_inv_acl_map_base is replaced by haou and ood
--Sharon Kaye amendment 2020-01-08 - Check effective dates for SLA mapping on table xla_mapping_set_values
--
select
x.organization_code,
x.a$organization_name,
x.item$sv$item,
x.item_description,
x.locator$stock_locator,
x.lot_creation_date,
case when x.schedule_close_date-x.lot_creation_date<31
then 'Current'
when x.schedule_close_date-x.lot_creation_date>=31 and x.schedule_close_date-x.lot_creation_date<61
then '30 Days'
when x.schedule_close_date-x.lot_creation_date>=61 and x.schedule_close_date-x.lot_creation_date<91
then '60 Days'
when x.schedule_close_date-x.lot_creation_date>=91 and x.schedule_close_date-x.lot_creation_date<121
then '90 Days'
when x.schedule_close_date-x.lot_creation_date>=121
then '120 Days +'
end age,
null at_risk, --blank green column , no formula(column added in between)
x.lot_expiration_date,
round((x.lot_expiration_date-x.schedule_close_date)/30.4,0) months_to_expiry,
null priority_expiry, --IFERROR(VLOOKUP(J8,Expiry,2,FALSE),"A-0 Expired") vlookup not possible currently
x.lot_number,
x.lot_status_code,
x.period_close_quantity,
x.subinventory,
x.subinv_descr,
x.unit_cost_amount,
(x.unit_cost_amount*x.period_close_quantity)valuation,
null top_sellers, --=IFERROR(VLOOKUP(D8,TOP_80,2,FALSE),"Bottom20%")vlookup not possible currently
x.item_type_a9,
x.item_type_a9_descr,
case when x.item_type_a9 is not null then x.item_type_a9||'|'||x.item_type_a9_descr end item_type,
x.gl_seg4_inv_val,
case when x.gl_seg4_inv_val is not null then x.gl_seg4_inv_val||'|'||xxen_util.segment_description(x.gl_seg4_inv_val,'SEGMENT4',x.chart_of_accounts_id) end gl_account,
x.item_division_a21,
x.brand_a22,
x.brand_code,
case when x.brand_a22 is not null and to_number(x.brand_a22)=0 then '' when x.brand_a22 is not null then x.brand_a22||'|'||x.brand_code end brand,
x.unit_weight_g
from
(
select
ood.organization_code,
ood.organization_name a$organization_name,
(gpb.accounted_value - gpb.intransit_accounted_value) accounted_onhand_value,
gpb.accounted_value accounted_transaction_value,
oap.period_set_name calendar_name,
gl.chart_of_accounts_id chart_of_accounts_id,
ccg.cost_group cost_group_name,
gper.entered_period_name entered_gl_period_name,
oap.period_name gl_period_name,
msib.inventory_item_id,
msib.organization_id,
msib.segment1 item$sv$item,
msib.description item_description,
mil.segment1 locator$stock_locator,
mln.expiration_date lot_expiration_date,
mln.creation_date lot_creation_date,
mgtl.grade_code lot_grade_code,
mln.lot_number lot_number,
mmstl.status_code lot_status_code,
gpb.primary_quantity period_close_quantity,
oap.period_close_date,
oap.period_start_date,
oap.schedule_close_date,
gper.period_type period_type,
oap.period_year period_year,
msi.secondary_inventory_name subinventory,
msi.description subinv_descr,
--nvl(xxnao_opm_wrapper_pkg.get_standard_cost(
--gpb.inventory_item_id,
--gpb.organization_id,
--oap.schedule_close_date,
--gl.ledger_id
--),0) unit_cost_amount,
0 unit_cost_amount,
msib.attribute9 item_type_a9,
msib.attribute21 item_division_a21,
msib.attribute22 brand_a22,
xxen_util.segment_description(msib.attribute22,'SEGMENT5',gl.chart_of_accounts_id)brand_code,
msib.unit_weight unit_weight_g,
ffv_it.description item_type_a9_descr,
xitm.gl_seg4_inv_val
from
gmf_period_balances gpb,
mtl_lot_numbers mln,
mtl_item_locations mil,
mtl_grades_tl mgtl,
mtl_material_statuses_tl mmstl,
gl_periods gper,
cst_cost_groups ccg,
mtl_secondary_inventories msi,
org_acct_periods oap,
mtl_system_items_b msib,
org_organization_definitions ood,
hr_all_organization_units haou,
hr_organization_information hoi,
gl_ledgers gl,
------ Value Set-----XYAI_ITEM_TYPE
(
select
ffvv.flex_value,
ffvv.flex_value_meaning,
ffvv.description
from
apps.fnd_flex_values_vl ffvv,
apps.fnd_flex_value_sets ffvs
where ffvs.flex_value_set_id=ffvv.flex_value_set_id and
ffvs.flex_value_set_name='XYAI INV Item Type'
) ffv_it,
------ Inventory_valuation_account ----- from XYAI_ITEM_TYPE
(
select
gcc.segment4 gl_seg4_inv_val,
xmsv.input_value_constant,
xmsv.effective_date_from,
xmsv.effective_date_to
from
xla_mapping_set_values xmsv,
gl_code_combinations gcc
where
xmsv.mapping_set_code = 'PH_INV_VALUATION' and
xmsv.value_code_combination_id = gcc.code_combination_id
) xitm
where 1=1 and
ood.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
ood.organization_id = gpb.organization_id and
msib.organization_id = gpb.organization_id and
msib.organization_id = ood.organization_id and
msib.inventory_item_id = gpb.inventory_item_id and
oap.acct_period_id = gpb.acct_period_id and
oap.organization_id = gpb.organization_id and
nvl(xitm.effective_date_to,oap.period_start_date+1) >= oap.period_start_date and
nvl(xitm.effective_date_from,oap.period_start_date) <= oap.period_start_date and
msi.organization_id(+) = gpb.organization_id and
msi.secondary_inventory_name(+) = gpb.subinventory_code and
ccg.cost_group_id(+) = gpb.cost_group_id and
gper.period_set_name = oap.period_set_name and
gper.period_name = oap.period_name and
mln.lot_number(+) = gpb.lot_number and
mln.inventory_item_id(+) = gpb.inventory_item_id and
mln.organization_id(+) = gpb.organization_id and
mgtl.grade_code(+)=mln.grade_code and
mmstl.status_id(+)=mln.status_id and
mmstl.language(+)=userenv('lang') and
mil.inventory_location_id(+) = gpb.locator_id and
msib.attribute9=ffv_it.flex_value(+) and
msib.attribute9=xitm.input_value_constant(+)and
hoi.org_information_context= 'Accounting Information' and
hoi.organization_id=ood.organization_id and
haou.organization_id=to_number(hoi.org_information3) and -- this gets the operating unit id
gl.ledger_id=to_number(hoi.org_information1) -- get the ledger_id
)x
where 2=2 |
