Dynamically ordering of rows
Step 1: Create a tabular form with the query and add a null column at last Step 2: Report attributes à NULL column à HTML Expression <img id=”down_#ROWNUM#” style=”cursor:pointer;” onclick=”f_row_down(this)”…
Read MoreModal Dialog in Apex 4.2
Requirement: A modal dialog has to open on button click in Oracle Apex 4.2. Step 1: Create a page Step 2: Create a HTML static Region and create a…
Read MoreFunction to convert Item quantity from one UOM to another UOM
Oracle Standard function APPS.INV_CONVERT.INV_UM_CONVERT can be used to convert quantity from one UOM to another UOM. Eg: To convert from primary UOM to Base UOM DECLARE v_primary_uom VARCHAR2…
Read MoreQuery to get transactions of inventory items with available onhand quantity
SELECT recs.doc_type, recs.item_number, recs.doc_no, recs.po_number, recs.organization_id, recs.inventory_item_id, recs.primary_quantity, recs.transaction_date FROM ( /* receipt transactions*/ …
Read MoreQuery to get current onhand quantity of inventory Items.
SELECT ALL mtl.organization_id, orgs.NAME organization_name, mtl.inventory_item_id, mtl.segment1 item_number, mtl.description item_description, mtl.item_type, mtl.inventory_item_status_code, mtl.primary_uom_code, …
Read MorePayroll Employer Costs Extract
SELECT SUM(peevf.screen_entry_value) FROM pay_element_types_f petf ,pay_element_entry_values_f peevf ,pay_element_entries_f peef ,per_all_assignments_f paaf ,pay_input_values_f pivf WHERE paaf.assignment_id = p_assignment_id AND p_effective_date between paaf.effective_start_date and paaf.effective_end_date AND peef.assignment_id = paaf.assignment_id AND peef.effective_start_date between…
Read MoreQuery to extract iProc Smart Forms
SELECT DISTINCT STORE.NAME store_name, smartforms.template_name request_name, pas.commodity_id, pas.vendor_name supplier, (SELECT NAME FROM apps.hr_operating_units WHERE organization_id = smartforms.org_id) operating_unit, (SELECT segment1 || ‘.’ || segment2 FROM apps.mtl_categories_b WHERE category_id = smartforms.category_id)…
Read MoreAP Supplier Banks Payments Query
SELECT pvs.org_id org_id, iep.default_payment_method_code, alc.lookup_code, hou.NAME org_name, pv.vendor_id supplier_id, pv.segment1 supplier_number, plc.description supplier_type, pv.vendor_name supplier_name, …
Read MoreOracle EBS Table Suffixes
You have lot of tables ending with different suffixes in Oracle Apps database. Did you ever wonder what these denote? Listed below are the different table suffixes and what they…
Read MoreHow to programmatically close PO using PO_ACTIONS API
How to programmatically close PO using PO_ACTIONS API PO_ACTIONS.CLOSE_PO is the API used to programmatically close/final close Purchase Order. We have to pass P_ACTION parameter value as ‘CLOSE’/’FINALLY CLOSE’ depending…
Read More