Yearly Archives: 2021

On-Hand Quantity with Serial Number and Locator details

The query gives the onhand quantity details with serial number and locator details select ohd.item,ohd.item_desc,ohd.org_code,ohd.org_name,ohd.sub_inventory,ohd.locator,msn.serial_number,ohd.item_status,ohd.uom,NVL2(msn.serial_number,1,ohd.total_onhand_qty) onhand_qty,ohd.total_onhand_qty,ohd.total_reserve_qty,ohd.total_transact_qty,mmt.transaction_date stock_in_date,mst.transaction_source_type_name stock_in_typefrom (SELECT msi.inventory_item_id , (select max(mq.create_transaction_id) from mtl_onhand_quantities mqwhere 1=1and mq.organization_id = moq.organization_idand…

Read More

Sales Order Dispatch Details – Query

The query provides the item dispatch details information of the sales order SELECToola.line_number,oola.line_id,ooha.orig_sys_document_ref ,ooha.order_source_id,ooha.order_number sale_order_no,trunc(ooha.ordered_date) sale_order_date,hp_ship.party_name customer_name,–hl_ship.address1,hcs_ship.location customer_site,ooha.cust_po_number customer_po_ref,rct.trx_number customer_invoice_num,rct.trx_date invoice_date,msib.segment1 item_code,msib.description item_desc,oola.shipping_quantity qty,oola.order_quantity_uom uom,ms.serial_number serial_num,mth.request_number move_order_ref,trunc(actual_shipment_date) shipment_dateFROMoe_order_headers_all ooha,oe_order_lines_all oola,mtl_system_items_b…

Read More

Finished Good – BOM (Bill Of Materials) explode for all levels

This package will explode BOM details at all levels of a Finished Good and write the data to a file in the Directory CREATE OR REPLACE PACKAGE XX_FINISHED_GOOD_BOM_PKG AS PROCEDURE…

Read More

Query to fetch Level-1 BOM Details for an Inventory Item

SELECT msi.segment1 “Parent material coding”,msi.inventory_item_id,msi.description “Parent item description”,msi1.segment1 “Sub item code”,msi1.inventory_item_id sub_item_id,msi1.description “Sub item description”FROM mtl_system_items_b msi,bom_bill_of_materials bom,bom_inventory_components bic,mtl_system_items_b msi1WHERE 1 = 1AND msi.organization_id = :organization_idAND msi.inventory_item_id = bom.assembly_item_idAND msi.organization_id…

Read More

Custom Form Development – Steps to follow

The below are the steps to follow to develop a basic Custom form in EBS. Download the Resource folder from the server and place in local directory. Right Click on…

Read More

Customer Profile Update Issue – Query to fetch Customers with No TAX Profile

When the tax profile is missing for a customer, we cannot edit the customer profile details from the Customer dashboard. So to identify such customers where we may face the…

Read More

Query for Payments on Hold

The below is the query to get all the payments on hold for a particular operating unit. SELECTpo.comments po_notes,pf1.full_name buyer,ah.hold_flag paymnet_hold_flag,ah.amount_remaining,(SELECTuser_nameFROMapps.fnd_userWHEREuser_id = rh.created_by) requisition_created_by,(SELECTfull_nameFROMapps.per_all_people_fWHEREperson_id = rh.preparer_idAND ROWNUM = 1) preparer,rh.segment1…

Read More

Query for AP Invoices on Hold

The below is the query to get all the invoices on hold for a particular operating unit. SELECTpo.comments po_notes,pf1.full_name buyer,ah.hold_date,ah.hold_lookup_code,(SELECTuser_nameFROMapps.fnd_userWHEREuser_id = rh.created_by) requisition_created_by,(SELECTfull_nameFROMapps.per_all_people_fWHEREperson_id = rh.preparer_idAND ROWNUM = 1) preparer,rh.segment1 requisition_num,po.segment1…

Read More

ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option in Oracle 12C

  Details of error are as follows. ALTER INDEX MSDBA.TEST_INDEX DISABLE Error at line 1 ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED…

Read More

FULL TABLE SCAN VS INDEX SCAN PERFORMACE in Oracle 12C

Let’s take the employees database, and slightly modify the employees tables:       mysql> ALTER TABLE employees ADD INDEX idx_first (first_name),ENGINE=InnoDB;   1 SELECT * FROM employees ORDER BY…

Read More