Definition Install Base
- Life cycle tracking application for an item (enterprise-wide)
- Capable of tracking serialized and non-serialized item instances including:
- Tangible items e.g.. manufactured, purchased, and shipped items
- Intangible items e.g.. software, services, licenses, and agreements
- Tangible assets e.g.. fixed, movable, and leased assets
- Fully Integrated with Oracle E-Business Suite
Features and Functionality
- Item instance maintenance
- Support for Inventory Transactions
- Centralized repository information
- Item instance consolidated transactions history
- Item instance time stamp and version label history
- History of Oracle E-Business Suite transactions affecting Oracle Install Base
- Instance relationships with multiple party/accounts
- Configuration support and instance-to-instance relationships
- Open Interface and Quick Edit capabilities:
- Mass load of item instances by an open interface
- Quick online edit of attributes for a group of item instances
- System grouping of item instances
- Extended Attributes
- Life Cycle Tracking in Oracle Install Base
When does Install Base instances of IB trackable & shippable items get created ?
- When an item is received into inventory
- Through miscellaneous receipts
- Purchase order receipt, etc
Item Life Cycle Tracking
- Install Base Public Packages
- CSI_DATASTRUCTURES_PUB: Contains all public datastructures exposed to Install Base API users.
- CSI_ITEM_INSTANCE_PUB: Contains all APIs for item instance management.
- CSI_INSTANCE_RELATIONSHIP_PUB: Contains all APIs to manage instance-to-instance relationships (instance configuration).
- CSI_SYSTEMS_PUB: Contains APIs for creating, updating and deleting the systems.
Bom Explosion Query
Below query can be handy to explode and Query BOM Details
/* Formatted on 7/30/2014 7:01:32 PM (QP5 v5.115.810.9015) */
SELECT DISTINCT
LPAD (' ', LEVEL * 2) || LEVEL order_level,
msib.segment1 assembly_item,
msib.description assembly_description,
msib.inventory_item_status_code assembly_item_status,
SYS_CONNECT_BY_PATH (msib2.segment1, '/') PATH,
msib2.segment1 AS component_item,
msib2.description component_item_description,
msib2.inventory_item_status_code component_item_status,
bic.item_num,
bic.operation_seq_num,
bic.component_quantity
FROM bom.bom_components_b bic,
bom.bom_structures_b bom,
inv.mtl_system_items_b msib,
inv.mtl_system_items_b msib2,
mtl_parameters mp
WHERE 1 = 1 AND bic.bill_sequence_id = bom.bill_sequence_id
AND SYSDATE BETWEEN bic.effectivity_date
AND NVL (bic.disable_date, SYSDATE)
AND bom.assembly_item_id = msib.inventory_item_id
AND bom.organization_id = msib.organization_id
AND bic.component_item_id = msib2.inventory_item_id
AND bom.organization_id = msib2.organization_id
AND mp.organization_id = msib.organization_id
AND mp.organization_code = :p_org_code /* organization here */
AND bom.alternate_bom_designator IS NULL
START WITH msib.segment1 = :p_item_number /* component item to be used here */
CONNECT BY NOCYCLE prioR bic.component_item_id = msib.inventory_item_id
ORDER BY PATH
you can use API bompxinq.export_bom to explode bom for a particular item. This will explode the bom and populate in table variable bom_export_tab Let me know if you need any further help.
BOM EXPLOSION
SELECT
/* ORDERED FIRST_ROWS INDEX(MSI MTL_SYSTEM_ITEMS_U1) INDEX(BOM
BOM_BILL_OF_MATERIALS_U2) INDEX(LU10 MFG_LOOKUPS_U1) INDEX(FCL
FND_COMMON_LOOKUPS_U1) */
rowidtochar(bet.rowid) row_id,
bet.top_bill_sequence_id,
bet.bill_sequence_id,
bet.component_sequence_id,
bet.component_item_id,
bet.organization_id,
bet.operation_seq_num,
bet.plan_level,
bet.extended_quantity,
bet.item_num,
bet.sort_order,
bet.group_id,
bet.item_cost,
NVL(msi.reservable_type, 2) reservable_type,
bet.extend_cost_flag,
bet.top_alternate_designator,
bet.top_item_id,
bet.effectivity_date,
bet.disable_date,
bet.implementation_date,
DECODE(bet.implementation_date, NULL, 2, 1) implemented_flag,
bet.optional,
bet.planning_factor,
bet.component_yield_factor,
bet.include_in_rollup_flag,
bet.wip_supply_type wip_supply_type,
ml2.meaning supply_type,
bet.supply_subinventory,
bet.supply_locator_id,
bet.locator,
bet.component_remarks,
bet.change_notice,
bet.component_quantity,
bet.operation_lead_time_percent,
bet.operation_offset,
bet.mutually_exclusive_options,
bet.check_atp,
bet.required_to_ship,
lu10.meaning so_basis,
bet.required_for_revenue,
bet.include_on_ship_docs,
bet.low_quantity minimum_quantity,
bet.high_quantity maximum_quantity,
msi.primary_uom_code unit_of_measure,
msi.item_type item_type,
fcl.meaning item_type_description,
msi.INVENTORY_ITEM_STATUS_CODE item_status,
msi.description,
msi.eng_item_flag,
msi.***_manufacturing_lead_time,
msi.cumulative_total_lead_time,
msi.full_lead_time manufacturing_lead_time,
bom.assembly_item_id parent_item_id,
bom.alternate_bom_designator parent_alternate,
DECODE(bom.alternate_bom_designator, NULL, 2, 1) parent_alternate_flag,
bom.assembly_type eng_bill,
LPAD(bet.PLAN_LEVEL, LEAST(bet.PLAN_LEVEL, 8), '.') DISPLAY_PLAN_LEVEL,
TO_CHAR(BET.EFFECTIVITY_DATE, 'HH24:MI') EFFECTIVITY_TIME,
TO_CHAR(BET.DISABLE_DATE, 'HH24:MI') DISABLE_TIME,
BET.EXTENDED_QUANTITY EXTENDED_QUANTITY6,
DECODE(BET.EXTEND_COST_FLAG, 1, BET.ITEM_COST * BET.EXTENDED_QUANTITY, 2, 0,
NULL) EXTENDED_COST,
BET.OPERATION_SEQ_NUM OPERATION_SEQ_NUM7,
MSI.BOM_ITEM_TYPE bom_item_type,
MSI.REPLENISH_TO_ORDER_FLAG,
MSI.PICK_COMPONENTS_FLAG,
MSI.ATP_COMPONENTS_FLAG,
MSI.ATP_FLAG,
MSI.SHIPPABLE_ITEM_FLAG,
MSI.MRP_PLANNING_CODE,
MSI.SO_TRANSACTIONS_FLAG,
MSI.CUSTOMER_ORDER_ENABLED_FLAG,
MSI.INVOICE_ENABLED_FLAG,
MSI.INVENTORY_ASSET_FLAG,
MSI.SEGMENT1,
MSI.SEGMENT1,
BET.CURRENT_REVISION,
MSI.GLOBAL_ATTRIBUTE6 ITEM_SPECIFICATION,
BIC.ATTRIBUTE2 ITEM_SVC,
BIC.ATTRIBUTE3 ITEM_SF,
BIC.ATTRIBUTE10 ITEM_DPC,
BIC.ATTRIBUTE12 RECV_DEPT_CD,
BD.DEPARTMENT_CODE ROU_RECV_DEPARTMENT,
msi.description ENG_DESC,
msi.global_attribute6 ENG_SPEC,
msi.primary_uom_code ENG_UOM,
bet.component_quantity ENG_QTY,
msi.item_type ENG_UIT,
ml2.meaning ENG_SUPPLY_TYPE,
bet.extend_cost_flag ENG_COST_FLAG,
BIC.INCLUDE_IN_COST_ROLLUP INCLUDE_IN_COST_ROLLUP,
msi.planning_make_buy_code,
bet.Attribute13,
bet.Attribute14,
MSI.GLOBAL_ATTRIBUTE9,
msi.segment9 AI_TYPE
FROM
MFG_LOOKUPS ML2,
MFG_LOOKUPS LU10,
FND_COMMON_LOOKUPS FCL,
BOM_BILL_OF_MATERIALS BOM,
MTL_SYSTEM_ITEMS MSI,
BOM_SMALL_EXPL_TEMP BET,
BOM_INVENTORY_COMPONENTS BIC,
BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS,
BOM_DEPARTMENTS BD
WHERE
bet.component_item_id = msi.inventory_item_id
AND bet.bill_sequence_id = bom.bill_sequence_id
AND bet.organization_id = msi.organization_id
AND lu10.lookup_code = bet.so_basis
AND bic.component_sequence_id(+) = bet.component_sequence_id
AND lu10.lookup_type = 'BOM_SO_BASIS'
AND fcl.lookup_code = msi.item_type
AND fcl.lookup_type = 'ITEM_TYPE'
AND fcl.application_id = 401
AND ml2.lookup_code = bet.wip_supply_type
AND ml2.lookup_type = 'WIP_SUPPLY'
AND bet.assembly_item_id = bor.assembly_item_id(+)
AND bet.organization_id = bor.organization_id(+)
AND bor.alternate_routing_designator(+) IS NULL
AND bor.routing_sequence_id = bos.routing_sequence_id(+)
AND 10 = bos.operation_seq_num(+)
AND bos.effectivity_date(+) <= sysdate
AND sysdate < NVL(bos.disable_date(+),sysdate + 1)
AND bos.department_id = bd.department_id(+) ;
SELECT
bic.component_item_id Component ,
msi.segment1 Name,
msi.description Description,
bic.component_quantity Quantity,
lpad( ' ', level*2 )
|| level
FROM
Bom_structures_b bbm ,
Bom_components_b bic ,
mtl_system_items msi
WHERE
bbm.bill_Sequence_id = bic.bill_sequence_id
AND msi.inventory_item_id = bic.component_item_id
AND msi.organization_id = bbm.organization_id
START WITH bbm.assembly_item_id = 3704887
AND bbm.organization_id = 102
CONNECT BY prior bic.component_item_id = bbm.assembly_item_id;
No comments:
Post a Comment