Fusion PIM – Indented BOM Extract Query

Most of the businesses have requirement to get Indented BOM (Bill of Material) export of the assembly into the excel to validate the bill of material/item structure for the Engineering Change Request/Change Order changes, impacted Work orders/Sales orders etc., This is much needed for ETO (Engineer to Order) and CTO (Configure to Order) model items. … Read more

Fusion Item Extract Query – Item Analysis and FBDI Updates

Here is the Item extract SQL which i used for the item data analysis and item updates thru FBDI. SELECT ESI.INVENTORY_ITEM_ID,ESI.ORGANIZATION_ID,ESI.ITEM_NUMBER,IOP.ORGANIZATION_CODE INV_ORG,— EIC.ITEM_CLASS_ID,EIC.ITEM_CLASS_NAME ITEM_CLASS,ESI.ITEM_TYPE ITEM_TYPE,(SELECT MEANINGFROM FND_LOOKUP_VALUES_VLWHERE LOOKUP_TYPE = ‘EGP_ITEM_TYPE’AND lookup_code = ESI.ITEM_TYPE) User_Item_type,ESI.PRIMARY_UOM_CODE PRIMARY_UNIT_OF_MEASURE,— ESI.WIP_SUPPLY_TYPE BUILD_IN_WIP,ESI.BUILD_IN_WIP_FLAG BUILD_IN_WIP,ESI.COSTING_ENABLED_FLAG COSTING_ENABLED,ESI.INVENTORY_ASSET_FLAG INVENTORY_ASSET_VALUE,DECODE (ESI.WIP_SUPPLY_TYPE,1, ‘Push’,2, ‘Assembly Pull’,3, ‘Operation Pull’,4, ‘Bulk’,5, ‘Vendor’,6, ‘Phantom’) PIM_SUPPLY_TYPE,(SELECT MEANINGFROM FND_LOOKUP_VALUES_VLWHERE LOOKUP_TYPE = ‘EGP_SERIAL_NUMBER_CONTROL_TYPE’AND … Read more

Oracle Fusion 24A Upgrade – GOP didn’t honor all the sourcing assignments while scheduling the Sales Orders lines other than the Global sourcing assignment

Problem: For the Global Order Promising Assignment Set, We have Item and Organization, Category and Organization, Category & Global level sourcing assignments. The global level sourcing rule was setup to source from multiple suppliers based on the priority. Some reason sales orders were processed as drop ship orders when the warehouse wasn’t entered at the … Read more

Commonly used SQLs/Tables in Fusion SCM Cloud

Here are the some commonly used SQLs/Tables in the Fusion SCM Cloud applications for implementation and support resources. This will help to understand the enterprise structure, setting up custom lookup and base tables in the SCM modules etc., 1. Business Units: Business Unit is nothing but operating unit in Fusion applications — Business UnitsSELECT * … Read more

Bills of Material Level1 Items SQL

Use the below SQL to list Level1 Components/Sub-Assemblies for the Assembly. select a.segment1 COMP_Item, a.description Comp_Descr, a.inventory_item_status_code Comp_Status, a.item_type,d.segment1 ASSY_ITEM, d.description Assy_Descr, d.inventory_item_status_code Assy_Statusfrom mtl_system_items_b a,bom_components_b b,bom_structures_b c,mtl_system_items_b dwhere 1 = 1and a.inventory_item_id = b.component_item_idand a.organization_id = c.organization_idand b.disable_date is nulland b.bill_sequence_id = c.common_bill_sequence_idand d.inventory_item_id = c.assembly_item_idand d.organization_id = c.organization_idand d.segment1 = ‘SB68415’and d.organization_id = … Read more

Categories EBS

PO/Requisition Approval Limits Query

Purchase Order or Requisition approvals are setup based on the Employee-Supervisor hierarchy or position hierarchy. Most of the companies use employee-supervisor hierarchy which is easier to setup. Approval Limits are assigned at the Job Level. The HR provides direction on the approval limits for each of the job, especially requisition approvals. Procurement decides approval limits … Read more

Categories EBS

iSupplier Portal Active Users List SQL

Business has a common requirement to get supplier accounts using iSupplier Portal. Below is the SQL to get iSupplier Portal Active Users. This is needed from procurement and Oracle licensing prospective. SELECT hou.name Operating_Unit,pv.segment1 Supplier_Number, pv.vendor_name Supplier_Name, pvs.vendor_site_code Supplier_Site,fu.user_name Supplier_UserName, fu.EMAIL_ADDRESS Supplier_Email,fu.end_date User_Effective_End_DateFROM apps.AK_WEB_USER_SEC_ATTR_VALUES attr, apps.fnd_user fu, apps.ap_suppliers pv, apps.ap_supplier_sites_all pvs, apps.hr_operating_units houWHERE attr.attribute_code = … Read more

Categories EBS

Oracle Fusion Pricing Cloud – Price List Export SQL

Here is another SQL Which I extensively used to validate the price list conversions in the OM Cloud implementations and getting the price list(s) export from the Fusion SCM Applications. SELECT qplt.name “Price List”,qplt.price_list_type_code,qplt.currency_code Currency,qplt.start_date,qplt.end_date,qplt.description,qplt.org_id Business_Unit_ID,qplt.status_code Status,— qpli.price_list_item_id, qpli.price_list_id,qpli.item_level_code,(SELECT item_number FROM fusion.egp_system_items esi WHERE esi.inventory_item_id = qpli.item_id AND esi.organization_id=300000007099064) “Item”, — Master Org Id(SELECT unit_of_measure … Read more

R12.2.10 – Country of Origin for On-hand Inventory

Most of the businesses have a requirement to capture Country Of Origin (COO) while receiving the goods and track the item’s on-hand by Country of Origin. This functionality isn’t available with Oracle EBS R12.2.9 & lower versions including EBS R12.1. Unfortunately there is no good custom solution also to implement this type of business requirement. … Read more

Categories EBS

E-Business Suite Releases and support timelines

Oracle E-Business Suite 12.1 release will move into Oracle Sustaining Support in January 2022 and you will no longer receive new fixes, updates, or security patches. Release 12.1 premium support ends in Dec’2021. Sustaining Support poses a high risk for business-critical applications. Oracle and industry experts advise against running mission-critical applications on software that is … Read more

Categories EBS

Blockchain tables in Oracle Database 20c

Blockchain is a very popular and new data paradigm. Oracle makes it easy for applications to use Blockchain technology to help identify and prevent fraud. In Database 20c, new type of table called Blockchain table. This looks like normal table except as the rows are inserted into Blockchain Table, there are cryptographically chained together. This … Read more

Oracle Fusion Order Mgmt Cloud – SQL Script to get the Sales Orders export

Below is the SQL to get sales order export from Fusion Apps Order Mgmt Cloud. Please use Oracle Transaction Business Intelligence (OTBI) Data model (SQL) and Report to create the report. I have used the script extensively to validated the converted Sales Orders. SELECT (SELECT name FROM fusion.hr_operating_units where organization_id = h.org_id) BU_NAME,h.order_number,h.SOURCE_REVISION_NUMBER revision_NUM, — … Read more