The query will list Party Name, Number, Customer Number and there Bill To and Ship Addresses........................................................................................................................3 Request here is the query to list concurrent program name with its parameter, values set and default value/type.....................................................................................................4 Relation between AR Invoice and Sales Order tables.............................................................4 Query to get Customer Related information for a Sales Order...............................................5 Item Import (Item Conversion) ...............................................................................................6 Below is the query that can help in getting onhand quantity at given date.............................7 Link Purchase Order and Requisition......................................................................................8 Query to find locked objects in Oracle....................................................................................9 want to forcefully kill any session...........................................................................................9 Find Appln Id, Appl Name, Table Name and Column Name.................................................9 Query to List all the responsibilities attached to a User........................................................10 Convert Date into Week Rage ..............................................................................................10 Sales Order Line Status Flow and Meaning .........................................................................11 Concurrent Program Name with Parameter, Value set.........................................................12 Query to find out the shipper info..........................................................................................13 Query to find out shipper detail info......................................................................................14 Query to find out order and line hold information................................................................14 Sample Code for oe_order_pub.Process_Order....................................................................15 Query to find price discounts and surcharges on order lines...............................................20 Query to find freight charges on order lines.........................................................................20 Query to find out order and line hold information................................................................21 Query to find freight related info of order viz: freight carrier, ship method and service level .......................................................................................................................................22 Query to find out the shipper info..........................................................................................22 Query to find out shipper detail info......................................................................................23 Query to find out Move order line details.............................................................................24 Query to find Bill of Lading info of the Delivery.................................................................25 Query to find delivery leg and pick up stop info...................................................................25 Query to find Requisition header info...................................................................................26 Query to find Requisition details info....................................................................................27 Query to find an PO details....................................................................................................29 Query to find receipts against a PO shipment line................................................................31 Query to find PO returns........................................................................................................32 Query to find PO corrections.................................................................................................32 Query to find first level components of an Assemby............................................................33 Query to find all level components of an Assembly.............................................................34 Query to find latest execution of the Concurrent Program...................................................35 Querying order_number and po_number through receipt_num............................................35 1 Drop Ship order receiving......................................................................................................35 Order,Shipping and Invoiced Details.....................................................................................37 Purchase Order, Receipt and invoice Details........................................................................38 Migrate DFF from Instance1 to Instance2.............................................................................40 Find locks with Oracle...........................................................................................................42 KILL THE LOCKED OBJECT.............................................................................................42 LOB Query.............................................................................................................................42 Query to delete duplicate records..........................................................................................42 Locks......................................................................................................................................42 Forms Environmental Variable..............................................................................................43 Forms Compile.......................................................................................................................43 CUSTOM.pll Complie...........................................................................................................43 LDT command for Lookups..................................................................................................43 LDT command for Profile......................................................................................................43 LDT Command for Concurrent Prog.....................................................................................44 Adding Conc Program to Request Group..............................................................................44 Optio44 IMP Creation of Soft Link.....................................................................................................44 To Identify the Version of a File............................................................................................44 Running Concurrent Program via CONCSUB......................................................................44 Providing permission to $XX_TOP Directories....................................................................45 Copy A Table From One Instance To Another Instance.......................................................45 FUNCTION CF_LAST_BALFormula RETURN NUMBER IS v_onhand_qty NUMBER; v_last_qty NUMBER; v_target_qty NUMBER; BEGIN SELECT SUM(transaction_quantity) INTO v_onhand_qty FROM mtl_onhand_quantities_detail WHERE inventory_item_id = :inventory_item_id AND organization_id = :organization_id AND subinventory_code = :Subinventory; SELECT NVL(SUM(Transaction_quantity),0) INTO v_last_qty FROM mtl_material_transactions WHERE inventory_item_id = :inventory_item_id AND organization_id = :organization_id AND subinventory_code = :Subinventory AND trunc(NVL(transaction_date,creation_date))>= TRUNC(TO_DATE(:p_last_date,'RRRR/MM/DD'));......................................................46 v_target_qty:=(v_onhand_qty)-(v_last_qty);....................................................................46 2 The query will list Party Name, Number, Customer Number and there Bill To and Ship Addresses. SELECT hp.party_name , hp.party_number , hca.account_number , hca.cust_account_id , hp.party_id , hps.party_site_id , hps.location_id , hl.address1 , hl.address2 , hl.address3 , hl.city , hl.state , hl.country , hl.postal_code , hcsu.site_use_code , hcsu.site_use_id , hcsa.bill_to_flag FROM hz_parties hp , hz_party_sites hps , hz_locations hl , hz_cust_accounts_all hca , hz_cust_acct_sites_all hcsa , hz_cust_site_uses_all hcsu WHERE hp.party_id = hps.party_id 3 AND AND AND AND AND AND hps.location_id = hl.location_id hp.party_id = hca.party_id hcsa.party_site_id = hps.party_site_id hcsu.cust_acct_site_id = hcsa.cust_acct_site_id hca.cust_account_id = hcsa.cust_account_id hca.account_number = :customer_number Request here is the query to list concurrent program name with its parameter, values set and default value/type SELECT fcpl.user_concurrent_program_name , fcp.concurrent_program_name , par.column_seq_num , par.end_user_column_name , par.form_left_prompt prompt , par.enabled_flag , par.required_flag , par.display_flag , par.flex_value_set_id , ffvs.flex_value_set_name , flv.meaning default_type , par.DEFAULT_VALUE FROM fnd_concurrent_programs fcp , fnd_concurrent_programs_tl fcpl , fnd_descr_flex_col_usage_vl par , fnd_flex_value_sets ffvs , fnd_lookup_values flv WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id AND fcpl.user_concurrent_program_name = :conc_prg_name AND fcpl.LANGUAGE = 'US' AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name AND ffvs.flex_value_set_id = par.flex_value_set_id AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE' AND flv.lookup_code(+) = par.default_type AND flv.LANGUAGE(+) = USERENV ('LANG') ORDER BY par.column_seq_num Relation between AR Invoice and Sales Order tables SELECT ooha.order_number , oola.line_number so_line_number , oola.ordered_item , oola.ordered_quantity * oola.unit_selling_price so_extended_price , rcta.trx_number invoice_number 4 , rcta.trx_date , rctla.line_number inv_line_number , rctla.unit_selling_price inv_unit_selling_price FROM oe_order_headers_all ooha , oe_order_lines_all oola , ra_customer_trx_all rcta , ra_customer_trx_lines_all rctla WHERE ooha.header_id = oola.header_id AND rcta.customer_trx_id = rctla.customer_trx_id AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id) AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number) AND order_number = :p_order_number Query to get Customer Related information for a Sales Order SELECT ooh.order_number , hp_bill.party_name , hl_ship.address1 ||Decode(hl_ship.address2,NULL,'',chr(10)) ||hl_ship.address2||Decode(hl_ship.address3,NULL,'',chr(10)) ||hl_ship.address3||Decode(hl_ship.address4,NULL,'',chr(10)) ||hl_ship.address4||Decode(hl_ship.city,NULL,'',chr(10)) ||hl_ship.city ||Decode(hl_ship.state,NULL,'',',') ||hl_ship.state ||Decode(hl_ship.postal_code,'',',') ||hl_ship.postal_code ship_to_address , hl_bill.address1 ||Decode(hl_bill.address2,NULL,'',chr(10)) ||hl_bill.address2||Decode(hl_bill.address3,NULL,'',chr(10)) ||hl_bill.address3||Decode(hl_bill.address4,NULL,'',chr(10)) ||hl_bill.address4||Decode(hl_bill.city,NULL,'',chr(10)) ||hl_bill.city ||Decode(hl_bill.state,NULL,'',',') ||hl_bill.state ||Decode(hl_bill.postal_code,'',',') ||hl_bill.postal_code bill_to_address , ooh.transactional_curr_code currency_code , mp.organization_code , ooh.fob_point_code , ooh.freight_terms_code , ooh.cust_po_number FROM oe_order_headers_all ooh , hz_cust_site_uses_all hcs_ship , hz_cust_acct_sites_all hca_ship , hz_party_sites hps_ship , hz_parties hp_ship , hz_locations hl_ship , hz_cust_site_uses_all hcs_bill , hz_cust_acct_sites_all hca_bill , hz_party_sites hps_bill , hz_parties hp_bill 5 , hz_locations hl_bill , mtl_parameters mp WHERE 1 = 1 AND header_id = :p_header_id AND ooh.ship_to_org_id = hcs_ship.site_use_id AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id AND hca_ship.party_site_id = hps_ship.party_site_id AND hps_ship.party_id = hp_ship.party_id AND hps_ship.location_id = hl_ship.location_id AND ooh.invoice_to_org_id = hcs_bill.site_use_id AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id AND hca_bill.party_site_id = hps_bill.party_site_id AND hps_bill.party_id = hp_bill.party_id AND hps_bill.location_id = hl_bill.location_id AND mp.organization_id(+) = ooh.ship_from_org_id Item Import (Item Conversion) The Item import Interface(IOI) reads data from following tables for importing items and item details. The MTL_SYSTEMS_ITEM_INTERFACE table is used for new item numbers and all item attributes. This is the main item interface table, and can be the only table used to import items. MTL_ITEM_REVISIONS_INTERFACE is used if Item revisions history is also loaded with items. Item categories can be imported using MTL_ITEM_CATEGORIES_INTERFACE. The import error can be tracked using MTL_INTERFACE_ERRORS table. The transaction_id and request_id populated by the import program can be used to link interface table and error table. Required columns in MTL_SYSTEM_ITEMS_INTERFACE PROCESS_FLAG = 1 (The column is used to identify status of record) TRANSACTION_TYPE = 'CREATE' or 'UPDATE' SET_PROCESS_ID = any numeric value (This is not a required column but for performance it is advised to use this column and then run import program for the value entered here) ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org. DESCRIPTION = 'Description of the item' ITEM_NUMBER and/or SEGMENT(n) = If using item_number then each segment value should be entered concatenated by segment seperator. If Item revisions history is also being loaded then Item_number should be populated. LIST_PRICE_PER_UNIT = If material cost is to be populated for an item along with item import. Required columns in MTL_ITEM_REVISIONS_INTERFACE table. The table is only used if Item revision is to be loaded in the same run with IOI. If this table is not used then items are created with the default revision setup for an organization. PROCESS_FLAG = 1 6 TRANSACTION_TYPE = 'CREATE' SET_PROCESS_ID = any numeric value (Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table) ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org. REVISION EFFECTIVITY_DATE IMPLEMENTATION_DATE ITEM_NUMBER = Same as item_number in mtl_system_items_interface table. Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order. Required columns for MTL_ITEM_CATEGORIES_INTERFACE table. TRANSACTION_TYPE = 'CREATE' SET_PROCESS_ID = any numeric value (Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table) ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org ITEM_NUMBER/INVENTORY_ITEM_ID or both CATEGORY_SET_NAME or CATEGORY_SET_NAME or both CATEGORY_ID or CATEGORY_NAME or both For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import (IOI) program can be run in parallel if separate set_process_ids are passed while submitting. The IOI automatically separates Master records from Child, and processes Master records first. However, as one IOI process is not aware of other IOI processes running in parallel, do not split a given item's separate Organization records into two different SET_PROCESS_IDs that are being run in parallel. Item import program can be run in 2 modes INSERT & UPDATE. The method to update Item attribute columns to NULL is to use the following values: · for Numeric fields: insert -999999 · for Character fields: insert '!' Below is the query that can help in getting onhand quantity at given date SELECT SUM (target_qty) , item_id FROM (SELECT moqv.subinventory_code subinv , moqv.inventory_item_id item_id , SUM (transaction_quantity) target_qty FROM mtl_onhand_qty_cost_v moqv WHERE moqv.organization_id = :org_id AND moqv.inventory_item_id = :item_id 7 GROUP BY moqv.subinventory_code , moqv.inventory_item_id , moqv.item_cost UNION SELECT mmt.subinventory_code subinv , mmt.inventory_item_id item_id , -SUM (primary_quantity) target_qty FROM mtl_material_transactions mmt , mtl_txn_source_types mtst WHERE mmt.organization_id = :org_id AND transaction_date >= TO_DATE (:hist_date) + 1 AND mmt.transaction_source_type_id = mtst.transaction_source_type_id AND mmt.inventory_item_id = :item_id GROUP BY mmt.subinventory_code , mmt.inventory_item_id) oq GROUP BY oq.item_id Link Purchase Order and Requisition SELECT prh.segment1 req_number ,prh.authorization_status ,prl.line_num req_line_num ,prl.item_description req_item_description ,prl.unit_price req_unit_price ,prl.quantity req_quantity ,pd.req_header_reference_num ,pd.req_line_reference_num ,pl.line_num ,pl.item_description ,pl.quantity ,pl.amount ,ph.segment1 po_number ,prd.distribution_id ,pd.req_distribution_id FROM po_requisition_headers_all prh ,po_requisition_lines_all prl ,po_req_distributions_all prd ,po_distributions_all pd ,po_line_locations_all pll ,po_lines_all pl ,po_headers_all ph WHERE prh.requisition_header_id = prl.requisition_header_id and prh.org_id = prl.org_id and prl.requisition_line_id = prd.requisition_line_id and prl.org_id = prd.org_id and prd.distribution_id = pd.req_distribution_id(+) 8 and prd.org_id = pd.org_id(+) and pd.line_location_id = pll.line_location_id(+) and pd.org_id = pll.org_id(+) and pll.po_line_id = pl.po_line_id(+) and pll.org_id = pl.org_id(+) and pl.po_header_id = ph.po_header_id(+) and pl.org_id = ph.org_id(+) Query to find locked objects in Oracle SELECT c.owner , c.object_name , c.object_type , b.SID , b.serial# , b.status , b.osuser , b.machine , b.program , b.module , b.action FROM v$locked_object a , v$session b , dba_objects c WHERE b.SID = a.session_id AND a.object_id = c.object_id ORDER BY module want to forcefully kill any session alter system kill session 'sid,serial#' e.g. altery system kill session '123,5325' Find Appln Id, Appl Name, Table Name and Column Name SELECT fa.application_id , fa.application_short_name , fat.application_name , table_name , column_name , ft.description table_description 9 , fc.description column_description FROM fnd_tables ft , fnd_columns fc , fnd_application_tl fat , fnd_application fa WHERE ft.table_id = fc.table_id AND AND AND AND fc.column_name = :column_name fat.application_id = ft.application_id fat.LANGUAGE = USERENV ('LANG') fa.application_id = fat.application_id Query to List all the responsibilities attached to a User select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr where fu.user_user_name = :user_name and furg.user_id = fu.user_id and furg.responsibility_id = fr.responsibility_id and fr.language = userenv('LANG') Convert Date into Week Rage Below is an example of how to display dates into week Range in Oracle For E.g. 22-Nov-08 is in the date range 17-Nov-08 to 23-Nov-08 Firstly lets see how to get week of the year. Following query can be used to get this SELECT to_char(sysdate,'WW') FROM Dual; Now lets get the week range SELECT TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW') - 1) * 7), 'DD-MON-RR') || ' to ' 10 || TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW')) * 7)1, 'DD-MON-RR') FROM Dual; The output of above query for date 29-Nov-2008 is 24-NOV-08 to 30-NOV08 Sales Order Line Status Flow and Meaning Below are some of the different statuses of Sales Order Line with brief explanation OM = Order Management Sales order form SE = Shipping Transactions or execution form 1) Entered (OM): Order is saved but not booked 2) Booked (OM): Order is Booked. 3) Awaiting Shipping (OM): Order is booked but lines are not yet picked. Navigating to Shipping Execution, the delivery line status flow is: 4) Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used 5) Released to Warehouse (SE): Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a twostep pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place. 6) Ready to Release (SE): Order Line is booked and passed to shipping execution. The line is now eligible to pick Release. 7) Backordered(SE): The status of Backorderd is assigned to a line under the following circumstances. The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity. • 11 At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity. • • The user manually Backorders the entire delivery. 8) Shipped (SE): The delivery line is shipped confirmed. 9) Confirmed (SE): The delivery line is shipped or backordered and the trip stops are open. 10) Picked (OM): Pick release is complete, both allocations and pick confirm 11) Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred 12) Interfaced (SE): The delivery line is shipped and Inventory interface concurrent process is complete. 13) Awaiting Fulfillment (OM): When fulfillment set is used, Not all shippable lines in a fulfillment set or a configuration are fulfilled 14) Fulfilled (OM): All lines in a fulfillment set are fulfilled. 15) Interfaced to Receivables (OM): The order is linked with Receivables and the invoice is created. 16) Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue. 17) Closed (OM): Closed indicates that the line is closed. 18) Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line. Concurrent Program Name with Parameter, Value set SELECT fcpl.user_concurrent_program_name , fcp.concurrent_program_name , par.column_seq_num , par.end_user_column_name , par.form_left_prompt prompt , par.enabled_flag , par.required_flag , par.display_flag , par.flex_value_set_id , ffvs.flex_value_set_name 12 , flv.meaning default_type , par.DEFAULT_VALUE FROM fnd_concurrent_programs fcp , fnd_concurrent_programs_tl fcpl , fnd_descr_flex_col_usage_vl par , fnd_flex_value_sets ffvs , fnd_lookup_values flv WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id AND fcpl.user_concurrent_program_name = :conc_prg_name AND fcpl.LANGUAGE = 'US' AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name AND ffvs.flex_value_set_id = par.flex_value_set_id AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE' AND flv.lookup_code(+) = par.default_type AND flv.LANGUAGE(+) = USERENV ('LANG') ORDER BY par.column_seq_num Query to find out the shipper info select wnd.delivery_id delivery_id, substrb(party.party_name,1,50) customer, wpb.name batch_name, wsh_util_core.get_location_description( wnd.INITIAL_PICKUP_LOCATION_ID, 'NEW UI CODE') ship_from, wsh_util_core.get_location_description( wnd.ULTIMATE_DROPOFF_LOCATION_ID, 'NEW UI CODE') ship_to, wnd.INITIAL_PICKUP_DATE pickup_date, wnd.ULTIMATE_DROPOFF_DATE dropoff_date, lv.meaning ship_method, wnd.WAYBILL waybill, wnd.GROSS_WEIGHT gross_weight, wnd.WEIGHT_UOM_CODE uom, wnd.status_code, we.message from wsh_new_deliveries wnd, wsh_picking_batches wpb, wsh_exceptions we, fnd_lookup_values_vl lv, hz_cust_accounts cust_acct, hz_parties party where wnd.delivery_id = 12814 and wpb.batch_id = wnd.batch_id and we.delivery_id(+) = wnd.delivery_id and we.exception_name(+) = 'WSH_BATCH_MESSAGE' and lv.lookup_code(+) = wpb.ship_method_code and lv.lookup_type(+) = 'SHIP_METHOD' and lv.view_application_id(+) = 3 13 and and cust_acct.cust_account_id (+)=wnd.customer_id party.party_id(+) = cust_acct.party_id Query to find out shipper detail info SELECT wnd.delivery_id, wnd.name delivery_name, wdd.source_header_number so_order_number, oola.line_number so_line_number, wdd.source_header_id so_header_id, wdd.source_line_id so_line_id, wdd.shipping_instructions, wdd.inventory_item_id, wdd.requested_quantity_uom, msi.description item_description, msi.revision_qty_control_code , wdd.ship_method_code carrier, wdd.shipment_priority_code priority, wdd.organization_id, wnd.initial_pickup_location_id, wdd.released_status, wdd.source_code FROM mtl_system_items_vl msi, oe_order_lines_all oola, wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_new_deliveries wnd WHERE wnd.delivery_id =18910 AND wda.delivery_id = wnd.delivery_id(+) AND wdd.delivery_detail_id = wda.delivery_detail_id AND wdd.inventory_item_id = msi.inventory_item_id(+) AND wdd.organization_id = msi.organization_id(+) AND wdd.source_line_id = oola.line_id AND wdd.source_header_id = oola.header_id Query to find out order and line hold information. select distinct ohd.name ,ooh.hold_until_date ,ooh.hold_comment ,h.order_number ,l.item_identifier_type ,l.inventory_item_id ,l.ordered_item ,oh.header_id ,oh.line_id ,oh.order_hold_id from oe_hold_definitions ohd, oe_hold_sources_all ooh, 14 oe_order_headers_all h, oe_order_lines_all l, oe_order_holds_all oh where ohd.hold_id = ooh.hold_id and oh.hold_source_id = ooh.hold_source_id and oh.header_id = h.header_id and h.header_id=l.header_id and l.open_flag='Y' and h.open_flag='Y' order by ohd.name,h.order_number; Sample Code for oe_order_pub.Process_Order. Got sample code to create Sales Order in Oracle Order Management .Please note that this is not my code , one of my friend has forwaded this .But I have tested it throughly and succesfully created Sales Orders. create or replace procedure createsalesorder (p_org_id NUMBER, p_user_id NUMBER, p_resp_id NUMBER, p_appl_id NUMBER, p_order_type_id NUMBER, p_sold_to_org_id NUMBER, p_ship_to_org_id NUMBER, p_price_list_id NUMBER, p_curr_code VARCHAR2, p_flow_status_code VARCHAR2, p_po_num VARCHAR2, p_order_source_id NUMBER, p_inventory_item_id NUMBER, p_ordered_quantity NUMBER, p_tax_code VARCHAR2) IS l_api_version_number NUMBER := 1; l_return_status VARCHAR2(2000); l_msg_count NUMBER; l_msg_data VARCHAR2(2000); /*****************PARAMETERS****************************************** **********/ l_debug_level number := 1; -- OM DEBUG LEVEL (MAX 5) l_org number := p_org_id;--204; -- OPERATING UNIT l_user number := p_user_id;--1318; -- USER 15 l_resp number := p_resp_id;--21623; -- RESPONSIBLILTY l_appl number := p_appl_id;--660; -- ORDER MANAGEMENT /***INPUT VARIABLES FOR PROCESS_ORDER API*************************/ l_header_rec oe_order_pub.header_rec_type; l_line_tbl oe_order_pub.line_tbl_type; l_action_request_tbl oe_order_pub.Request_Tbl_Type; /***OUT VARIABLES FOR PROCESS_ORDER API***************************/ l_header_rec_out oe_order_pub.header_rec_type; l_header_val_rec_out oe_order_pub.header_val_rec_type; l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type; l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type; l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type; l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type; l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type; l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type; l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type; l_line_tbl_out oe_order_pub.line_tbl_type; l_line_val_tbl_out oe_order_pub.line_val_tbl_type; l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type; l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type; l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type; l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type; l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type; l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type; l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type; l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type; l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type; l_action_request_tbl_out oe_order_pub.request_tbl_type; l_msg_index NUMBER; l_data VARCHAR2(2000); l_loop_count NUMBER; l_debug_file VARCHAR2(200); -- book API vars b_return_status VARCHAR2(200); b_msg_count NUMBER; b_msg_data VARCHAR2(2000); BEGIN dbms_application_info.set_client_info(l_org); /*****************INITIALIZE DEBUG INFO*************************************/ if (l_debug_level > 0) then l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode('FILE'); oe_debug_pub.initialize; 16 oe_debug_pub.setdebuglevel(l_debug_level); Oe_Msg_Pub.initialize; end if; /*****************INITIALIZE ENVIRONMENT*************************************/ fnd_global.apps_initialize(l_user, l_resp, l_appl); -- pass in user_id, responsibility_id, and application_id /*****************INITIALIZE HEADER RECORD******************************/ l_header_rec := oe_order_pub.G_MISS_HEADER_REC; /***********POPULATE REQUIRED ATTRIBUTES **********************************/ l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE; l_header_rec.order_type_id := p_order_type_id;--1430; l_header_rec.sold_to_org_id := p_sold_to_org_id;--1006; l_header_rec.ship_to_org_id := p_ship_to_org_id;--1026; l_header_rec.price_list_id := p_price_list_id;--1000; l_header_rec.pricing_date := SYSDATE; l_header_rec.transactional_curr_code := p_curr_code;--'USD'; l_header_rec.flow_status_code := p_flow_status_code;--'ENTERED'; l_header_rec.cust_po_number := p_po_num;--'06112009-08'; l_header_rec.order_source_id := p_order_source_id;--0 ; --l_header_rec.attribute1 := 'ABC'; /*******INITIALIZE ACTION REQUEST RECORD*************************************/ l_action_request_tbl(1) := oe_order_pub.G_MISS_REQUEST_REC; l_action_request_tbl(1).request_type := oe_globals.g_book_order; l_action_request_tbl(1).entity_code := oe_globals.g_entity_header; /*****************INITIALIZE LINE RECORD********************************/ l_line_tbl(1) := oe_order_pub.G_MISS_LINE_REC; l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE; l_line_tbl(1).inventory_item_id := p_inventory_item_id;--149 ; l_line_tbl(1).ordered_quantity := p_ordered_quantity;--1; l_line_tbl(1).ship_to_org_id := p_ship_to_org_id;--1026 ; l_line_tbl(1).tax_code := p_tax_code;--'Location' ; /*****************CALLTO PROCESS ORDER API*********************************/ dbms_output.put_line('Calling API'); oe_order_pub.Process_Order( p_api_version_number => l_api_version_number, p_header_rec => l_header_rec, p_line_tbl => l_line_tbl, p_action_request_tbl => l_action_request_tbl, --OUT variables x_header_rec => l_header_rec_out, 17 x_header_val_rec => l_header_val_rec_out, x_header_adj_tbl => l_header_adj_tbl_out, x_header_adj_val_tbl => l_header_adj_val_tbl_out, x_header_price_att_tbl => l_header_price_att_tbl_out, x_header_adj_att_tbl => l_header_adj_att_tbl_out, x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out, x_header_scredit_tbl => l_header_scredit_tbl_out, x_header_scredit_val_tbl => l_header_scredit_val_tbl_out, x_line_tbl => l_line_tbl_out, x_line_val_tbl => l_line_val_tbl_out, x_line_adj_tbl => l_line_adj_tbl_out, x_line_adj_val_tbl => l_line_adj_val_tbl_out, x_line_price_att_tbl => l_line_price_att_tbl_out, x_line_adj_att_tbl => l_line_adj_att_tbl_out, x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out, x_line_scredit_tbl => l_line_scredit_tbl_out, x_line_scredit_val_tbl => l_line_scredit_val_tbl_out, x_lot_serial_tbl => l_lot_serial_tbl_out, x_lot_serial_val_tbl => l_lot_serial_val_tbl_out, x_action_request_tbl => l_action_request_tbl_out, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data); /*****************CHECK RETURN STATUS***********************************/ if l_return_status = FND_API.G_RET_STS_SUCCESS then dbms_output.put_line('Return status is success '); dbms_output.put_line('debug level ' l_debug_level); if (l_debug_level > 0) then dbms_output.put_line('success'); end if; commit; else dbms_output.put_line('Return status failure '); if (l_debug_level > 0) then dbms_output.put_line('failure'); end if; rollback; end if; /*****************DISPLAY RETURN STATUS FLAGS******************************/ if (l_debug_level > 0) then DBMS_OUTPUT.PUT_LINE('process ORDER ret status IS: ' 18 l_return_status); DBMS_OUTPUT.PUT_LINE('process ORDER msg data IS: ' l_msg_data); DBMS_OUTPUT.PUT_LINE('process ORDER msg COUNT IS: ' l_msg_count); DBMS_OUTPUT.PUT_LINE('header.order_number IS: ' to_char(l_header_rec_out.order_number)); DBMS_OUTPUT.PUT_LINE('header.return_status IS: ' l_header_rec_out.return_status); DBMS_OUTPUT.PUT_LINE('header.booked_flag IS: ' l_header_rec_out.booked_flag); DBMS_OUTPUT.PUT_LINE('header.header_id IS: ' l_header_rec_out.header_id); DBMS_OUTPUT.PUT_LINE('header.order_source_id IS: ' l_header_rec_out.order_source_id); DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: ' l_header_rec_out.flow_status_code); end if; /*****************DISPLAY ERROR MSGS*************************************/ if (l_debug_level > 0) then FOR i IN 1 .. l_msg_count LOOP Oe_Msg_Pub.get( p_msg_index => i ,p_encoded => Fnd_Api.G_FALSE ,p_data => l_data ,p_msg_index_out => l_msg_index); DBMS_OUTPUT.PUT_LINE('message is: ' l_data); DBMS_OUTPUT.PUT_LINE('message index is: ' l_msg_index); END LOOP; end if; if (l_debug_level > 0) then DBMS_OUTPUT.PUT_LINE('Debug = ' OE_DEBUG_PUB.G_DEBUG); DBMS_OUTPUT.PUT_LINE('Debug Level = ' to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL)); DBMS_OUTPUT.PUT_LINE('Debug File = ' OE_DEBUG_PUB.G_DIR'/'OE_DEBUG_PUB.G_FILE); DBMS_OUTPUT.PUT_LINE('*********************************************** *****'); OE_DEBUG_PUB.DEBUG_OFF; end if; --END; end createsalesorder; 19 Query to find price discounts and surcharges on order lines col list_line_type_code form a12 col arithmetic_operator form a12 Select h.order_number, l.line_number, pa.list_line_type_code, pa.arithmetic_operator, pa.operand, DECODE(PA.MODIFIER_LEVEL_CODE,'ORDER', L.UNIT_LIST_PRICE*L.ORDERED_QUANTITY *PA.OPERAND * SIGN(PA.ADJUSTED_AMOUNT)/100, (PA.ADJUSTED_AMOUNT* NVL(L.ORDERED_QUANTITY,0) )) DISCOUNT_AMT From qp_list_headers_vl lh, oe_price_adjustments pa, oe_order_lines_all l, oe_order_headers_all h Where h.order_number = 14463 and h.header_id = l.header_id and h.org_id = l.org_id and h.header_id = pa.header_id and l.line_id = pa.line_id(+) and pa.list_header_id = lh.list_header_id AND ( PA.LIST_LINE_TYPE_CODE = 'DIS' OR PA.LIST_LINE_TYPE_CODE = 'SUR' OR PA.LIST_LINE_TYPE_CODE = 'PBH' ) AND PA.APPLIED_FLAG='Y' AND NOT EXISTS (SELECT 'X' FROM OE_PRICE_ADJ_ASSOCS PAS, OE_PRICE_ADJUSTMENTS PA1 WHERE PAS.RLTD_PRICE_ADJ_ID = PA.PRICE_ADJUSTMENT_ID AND PA1.PRICE_ADJUSTMENT_ID= PAS.PRICE_ADJUSTMENT_ID AND PA1.LIST_LINE_TYPE_CODE ='PBH') Order by l.line_id / a: Qp_list_headers_vl is view based on qp_list_headers_b and qp_list_headers_tl tables. Query to find freight charges on order lines col charge_name form a24 col source_system_code form a12 select HEADER_ID LINE_ID CHARGE_ID , , , 20 CHARGE_NAME , CHARGE_AMOUNT , CURRENCY_CODE , INVOICED_FLAG , INTERCO_INVOICED_FLAG , ORG_ID , SOURCE_SYSTEM_CODE , ESTIMATED_FLAG , INVOICED_AMOUNT from OE_CHARGE_LINES_V where header_id= (select header_id from oe_order_headers_all where order_number=14463) order by line_id / a: The OE_CHARGE_LINES_V view is based on oe_price_adjustments, oe_order_headers_all and oe_order_lines_all for FREIGHT CHARGES. Query to find out order and line hold information col ordered_item form a32 col hold_name form a24 col hold_comment form a32 select ho.name hold_name, hs.hold_until_date, hs.hold_comment, h.order_number, oh.header_id, oh.line_id, oh.order_hold_id, l.item_identifier_type, l.inventory_item_id, l.ordered_item from oe_order_holds_all oh, oe_order_lines_all l, oe_order_headers_all h, oe_hold_definitions ho, oe_hold_sources_all hs where h.order_number= 14463 and oh.header_id = h.header_id and (h.cancelled_flag is null or h.cancelled_flag = 'N') and h.open_flag='Y' and oh.hold_source_id = hs.hold_source_id and hs.hold_id = ho.hold_id and h.header_id = l.header_id(+) and l.open_flag = 'Y' and l.line_id = nvl(oh.line_id,l.line_id) 21 and l.service_reference_line_id is null and oh.hold_release_id is null and nvl(h.org_id,0) = 204 and nvl(l.org_id,0) = nvl(h.org_id,0) order by ho.name,h.order_number Query to find freight related info of order viz: freight carrier, ship method and service level col shipping_method_code form a32 col carrier_name form a24 select h.order_number, h.shipping_method_code, wc.carrier_name, wcsm.SERVICE_LEVEL , wcsm.freight_code from wsh_carrier_ship_methods_v wcsm, wsh_carriers_v wc, oe_order_headers_all h where h.order_number= 14463 and h.org_id = 204 and h.shipping_method_code = wcsm.ship_method_code(+) and nvl(wcsm.organization_id(+),0) = 204 --Master Organization and wcsm.freight_code = wc.freight_code(+) order by h.order_number / Query to find out the shipper info select wnd.delivery_id delivery_id, substrb(party.party_name,1,50) customer, wpb.name batch_name, wsh_util_core.get_location_description( wnd.INITIAL_PICKUP_LOCATION_ID, 'NEW UI CODE') ship_from, wsh_util_core.get_location_description( wnd.ULTIMATE_DROPOFF_LOCATION_ID, 'NEW UI CODE') ship_to, wnd.INITIAL_PICKUP_DATE pickup_date, wnd.ULTIMATE_DROPOFF_DATE dropoff_date, lv.meaning ship_method, wnd.WAYBILL waybill, 22 wnd.GROSS_WEIGHT gross_weight, wnd.WEIGHT_UOM_CODE uom, wnd.status_code, we.message from wsh_new_deliveries wnd, wsh_picking_batches wpb, wsh_exceptions we, fnd_lookup_values_vl lv, hz_cust_accounts cust_acct, hz_parties party where wnd.delivery_id = 12814 and wpb.batch_id = wnd.batch_id and we.delivery_id(+) = wnd.delivery_id and we.exception_name(+) = 'WSH_BATCH_MESSAGE' and lv.lookup_code(+) = wpb.ship_method_code and lv.lookup_type(+) = 'SHIP_METHOD' and lv.view_application_id(+) = 3 and cust_acct.cust_account_id (+)=wnd.customer_id and party.party_id(+) = cust_acct.party_id Query to find out shipper detail info SELECT wnd.delivery_id, wnd.name delivery_name, wdd.source_header_number so_order_number, oola.line_number so_line_number, wdd.source_header_id so_header_id, wdd.source_line_id so_line_id, wdd.shipping_instructions, wdd.inventory_item_id, wdd.requested_quantity_uom, msi.description item_description, msi.revision_qty_control_code , wdd.ship_method_code carrier, wdd.shipment_priority_code priority, wdd.organization_id, wnd.initial_pickup_location_id, wdd.released_status, wdd.source_code FROM mtl_system_items_vl msi, oe_order_lines_all oola, wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_new_deliveries wnd WHERE wnd.delivery_id =18910 AND wda.delivery_id = wnd.delivery_id(+) AND wdd.delivery_detail_id = wda.delivery_detail_id 23 AND wdd.inventory_item_id = msi.inventory_item_id(+) AND wdd.organization_id = msi.organization_id(+) AND wdd.source_line_id = oola.line_id AND wdd.source_header_id = oola.header_id / Query to find out Move order line details SELECT wnd.delivery_id, wnd.name delivery_name, wnd.initial_pickup_location_id, mtrh.request_number mo_number, mtrl.line_number mo_line_number, mtrl.line_id mo_line_id, mtrl.from_subinventory_code, mtrl.to_subinventory_code, mtrl.lot_number, mtrl.serial_number_start, mtrl.serial_number_end, mtrl.uom_code, mtrl.quantity, mtrl.quantity_delivered, mtrl.quantity_detailed, wdd.source_header_number so_order_number, oola.line_number so_line_number, wdd.source_header_id so_header_id, wdd.source_line_id so_line_id, wdd.shipping_instructions, wdd.inventory_item_id, wdd.requested_quantity_uom, msi.description item_description, msi.revision_qty_control_code , wdd.ship_method_code carrier, wdd.shipment_priority_code priority, wdd.organization_id, wdd.released_status, wdd.source_code FROM mtl_system_items_vl msi, oe_order_lines_all oola, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_new_deliveries wnd WHERE wnd.delivery_id =18910 24 AND wda.delivery_id = wnd.delivery_id(+) AND wdd.delivery_detail_id = wda.delivery_detail_id AND wdd.move_order_line_id = mtrl.line_id AND mtrl.header_id = mtrh.header_id AND wdd.inventory_item_id = msi.inventory_item_id(+) AND wdd.organization_id = msi.organization_id(+) AND wdd.source_line_id = oola.line_id AND wdd.source_header_id = oola.header_id / Query to find Bill of Lading info of the Delivery select wnd.delivery_id delivery_id, wdi.sequence_number bol_number, wdi.bol_notify_party, wdi.port_of_loading, wdi.port_of_discharge, wnd.WAYBILL waybill, wnd.GROSS_WEIGHT gross_weight, wnd.WEIGHT_UOM_CODE uom, wnd.status_code from wsh_new_deliveries wnd, wsh_delivery_legs wdl, wsh_document_instances wdi where wnd.delivery_id =12784 and wnd.delivery_id = wdl.delivery_id (+) and wdi.entity_id (+) = wdl.delivery_leg_id AND wdi.entity_name (+) = 'WSH_DELIVERY_LEGS' AND wdi.document_type (+) = 'BOL' AND wdi.status (+) 'CANCELLED' / Query to find delivery leg and pick up stop info SELECT wt.trip_id, wt.name, wt.STATUS_CODE, wt.VEHICLE_ITEM_ID, wt.VEHICLE_NUMBER, wt.CARRIER_ID, wt.SHIP_METHOD_CODE, wts.STOP_ID, wts.STOP_LOCATION_ID, 25 wts.STATUS_CODE, wts.STOP_SEQUENCE_NUMBER, wts.PLANNED_ARRIVAL_DATE, wts.PLANNED_DEPARTURE_DATE, wts.ACTUAL_ARRIVAL_DATE, wts.ACTUAL_DEPARTURE_DATE, wts.DEPARTURE_NET_WEIGHT, wts.WEIGHT_UOM_CODE, wdl.DELIVERY_LEG_ID, wdl.DELIVERY_ID, wdl.PICK_UP_STOP_ID, wdl.DROP_OFF_STOP_ID, wdl.SEQUENCE_NUMBER, wdl.LOADING_ORDER_FLAG, wdl.SHIPPER_TITLE, wdl.SHIPPER_PHONE FROM wsh_trips wt ,wsh_trip_stops wts ,wsh_delivery_legs wdl WHERE wdl.delivery_id =12814 AND wts.stop_id = wdl.pick_up_stop_id AND wts.trip_id = wt.trip_id; Query to find Requisition header info set lines 150 set pages 150 execute fnd_client_info.set_org_context('204'); col col col col col col Description form a40 Req_type form a26 type_lookup_code form a16 PREPARER form a30 APPROVER form a30 NOTE_TO_APPROVER form a40 SELECT prh.segment1 Requisition , psp.manual_req_num_type req_num_type , ppf.full_name Preparer , prh.creation_date Creation_Date , prh.type_lookup_code , ppf1.full_name Approver , t.type_name Req_type , prh.description Description , pah.note Note_To_Approver , prh.requisition_header_id Req_header FROM po_requisition_headers prh , per_people_f ppf1 26 , per_people_f ppf , po_action_history pah , po_system_parameters psp , PO_DOCUMENT_TYPES_ALL_TL T , PO_DOCUMENT_TYPES_ALL_B B WHERE prh.REQUISITION_HEADER_ID=11675 and NVL(PRH.contractor_requisition_flag, 'N') 'Y' AND prh.preparer_id = ppf.person_id AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0) from financials_system_parameters fsp) AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE', 'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN') --AND prh.segment1 = P_req_num_from AND EXISTS (SELECT null FROM po_requisition_lines prl WHERE prl.requisition_header_id = prh.requisition_header_id AND nvl(prl.modified_by_agent_flag,'N') = 'N' AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED') AND pah.object_id = prh.requisition_header_id AND pah.employee_id = ppf1.person_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code AND pah.sequence_num = (SELECT max(sequence_num) FROM po_action_history pah WHERE pah.object_id = prh.requisition_header_id AND pah.object_type_code = 'REQUISITION' AND pah.object_sub_type_code = prh.type_lookup_code) and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE AND b.document_type_code = 'REQUISITION' AND b.document_subtype = prh.type_lookup_code AND NVL(B.ORG_ID, -99) = NVL(T.ORG_ID, -99) AND NVL(B.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1),' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) AND T.LANGUAGE = USERENV('LANG') / Query to find Requisition details info set lines 150 set pages 150 27 execute fnd_client_info.set_org_context('204'); col Justification form a24 col Item_Description form a36 col Source form a56 col Source_Type form a12 col Requestor form a20 col Line_Type form a12 col Item form a16 SELECT prl.line_num Line , plt.line_type Line_Type , prl.item_id prl_item_id , msi.segment1 Item , prl.item_revision Rev , prl.need_by_date Need_By_Date , prl.unit_meas_lookup_code Unit , round(prl.quantity,2) Quantity_Amount , prl.unit_price Unit_Price , DECODE (PRL.order_type_lookup_code, /* */ 'FIXED PRICE', PRL.amount, 'RATE', PRL.amount, NVL(PRL.quantity, 1) * PRL.unit_price) C_AMOUNT , ppf.full_name Requestor , plc.displayed_field Source_Type , decode(prl.source_type_code,'INVENTORY',ood.organization_name||' - '|| prl.source_subinventory,'VENDOR',prh.segment1||' - '|| prl.suggested_vendor_name||' - '||prl.suggested_vendor_location||' - '|| prl.suggested_vendor_contact||' - '|| prl.suggested_buyer_id,null) Source , prl.item_description Item_Description , prd.req_line_quantity Distributions , prl.justification Justification , prl.requisition_header_id , prl.requisition_line_id FROM po_requisition_headers prh , po_requisition_lines prl , po_req_distributions prd , po_line_types plt , per_people_f ppf , org_organization_definitions ood , po_lookup_codes plc , mtl_system_items msi , mtl_categories mca , gl_code_combinations gcc, financials_system_parameters fsp , po_system_parameters psp WHERE prh.segment1 = '1713' AND prl.requisition_line_id = prd.requisition_line_id AND prl.requisition_header_id = prh.requisition_header_id 28 AND prl.line_type_id = plt.line_type_id AND prl.to_person_id = ppf.person_id (+) AND prl.source_organization_id = ood.organization_id(+) AND plc.lookup_type = 'REQUISITION SOURCE TYPE' AND plc.lookup_code = prl.source_type_code AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp) AND trunc(sysdate) BETWEEN nvl(ppf.effective_start_date, trunc(sysdate)) AND nvl(ppf.effective_end_date, trunc(sysdate)) AND prl.item_id = msi.inventory_item_id(+) AND msi.organization_id = 204 AND prl.category_id = mca.category_id AND prd.code_combination_id = gcc.code_combination_id AND nvl(prl.modified_by_agent_flag,'N') = 'N' AND nvl(prl.cancel_flag,'N') != 'Y' AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED' ORDER BY prl.line_num / Query to find an PO details set lines 150 set pages 150 execute fnd_client_info.set_org_context('204'); col PO_Number_Release form a16 col Vendor form a28 col Description form a24 col Unit_Price form a18 col unit form a8 SELECT decode(por.release_num,NULL, poh.segment1, poh.segment1 ||'-'|| por.release_num) PO_Number_Release , pol.line_num Line , pov.vendor_name Vendor , pol.item_revision Rev , pol.item_description Description , pll.shipment_num , pod.distribution_num Distribution , decode(plt.order_type_lookup_code, 'AMOUNT',NULL,pll.price_override) Unit_Price , pll.promised_date Promised_Date , pol.unit_meas_lookup_code Unit , DECODE (POL.order_type_lookup_code, 'RATE', POD.amount_ordered, 29 , , , 0)) / 'FIXED PRICE', POD.amount_ordered, POD.quantity_ordered) Quantity_Amount_Ordered DECODE (POL.order_type_lookup_code, 'RATE', POD.amount_billed, 'FIXED PRICE', POD.amount_billed, POD.quantity_billed) Quantity_Amount_Billed DECODE (POL.order_type_lookup_code, 'RATE', POD.amount_delivered, 'FIXED PRICE', POD.amount_delivered, POD.quantity_delivered) Qty_Amount_Delivered DECODE (POL.order_type_lookup_code, 'RATE', (NVL(POD.amount_ordered, 0) - NVL(POD.amount_billed, DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered), 'FIXED PRICE', (NVL(POD.amount_ordered, 0) NVL(POD.amount_billed, 0)) / DECODE (NVL(POD.amount_ordered, 0), 0, 1, POD.amount_ordered), (NVL(POD.quantity_ordered, 0) - NVL(POD.quantity_billed, 0)) / DECODE (NVL(POD.quantity_ordered, 0), 0, 1, POD.quantity_ordered)) * 100 Percent_Unbilled , DECODE (POL.order_type_lookup_code, 'RATE', POD.amount_ordered - NVL(POD.amount_cancelled, 0)NVL(POD.amount_billed, 0), 'FIXED PRICE', POD.amount_ordered NVL(POD.amount_cancelled, 0)- NVL(POD.amount_billed, 0), (POD.quantity_ordered - NVL(POD.quantity_cancelled, 0)NVL(POD.quantity_billed, 0)) * PLL.price_override) C_AMOUNT_OPEN_INV , poh.po_header_id , pol.po_line_id , por.release_num , poh.currency_code C_CURRENCY , nvl(por.po_release_id,-1) release_id FROM po_distributions pod , mtl_system_items msi , po_line_locations pll , po_lines pol , po_releases por , po_headers poh , po_vendors pov , financials_system_parameters fsp , po_line_types plt WHERE poh.segment1='804' AND poh.po_header_id = pol.po_header_id AND pol.po_line_id = pll.po_line_id AND pll.line_location_id = pod.line_location_id AND pol.item_id = msi.inventory_item_id (+) AND msi.organization_id = fsp.inventory_organization_id AND poh.vendor_id = pov.vendor_id (+) 30 AND pll.po_release_id = por.po_release_id (+) AND pol.line_type_id = plt.line_type_id AND pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED') AND nvl(pol.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED') AND nvl(pll.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED') AND nvl(poh.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED') AND nvl(por.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED') AND nvl(poh.cancel_flag,'N') = 'N' AND nvl(por.cancel_flag,'N') = 'N' AND nvl(pol.cancel_flag,'N') = 'N' AND nvl(pll.cancel_flag,'N') = 'N' ORDER BY pll.line_location_id / Query to find receipts against a PO shipment line set lines 150 set pages 150 execute fnd_client_info.set_org_context('204'); SELECT pol.po_header_id, pol.po_line_id, pll.line_location_id, pll.quantity, rsh. shipment_header_id, rsh. receipt_source_code, rsh. vendor_id, rsh. vendor_site_id, rsh. organization_id, rsh. shipment_num, rsh. receipt_num, rsh. ship_to_location_id, rsh. bill_of_lading, rsl.shipment_line_id, rsl.QUANTITY_SHIPPED, rsl.QUANTITY_RECEIVED , rct.transaction_type, rct.transaction_id, decode(pol.order_type_lookup_code,'RATE',nvl(rct.amount,0),'FIXED PRICE',nvl(rct.amount,0), nvl(rct.source_doc_quantity,0) ) transaction_qty from rcv_transactions rct , rcv_shipment_headers rsh , rcv_shipment_lines rsl , po_lines pol , po_line_locations pll where rct.po_line_location_id = 28302 31 and rct.po_line_location_id = pll.line_location_id and rct.po_line_id = pol.po_line_id and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE') and rct.shipment_line_id=rsl.shipment_line_id and rsl.shipment_header_id=rsh.shipment_header_id order by rct.transaction_id Query to find PO returns set lines 150 set pages 150 execute fnd_client_info.set_org_context('204'); SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id , sum ( (nvl(rct.source_doc_quantity,0)) ) Qty_returned from rcv_transactions rct , po_lines pol , po_line_locations pll where rct.transaction_type = 'RETURN TO VENDOR' and rct.po_line_location_id = pll.line_location_id and rct.po_line_id = pol.po_line_id and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE') group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id union all SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id , sum ( (nvl(rct.amount,0)) ) Qty_returned from rcv_transactions rct , po_lines pol , po_line_locations pll where rct.transaction_type = 'RETURN TO VENDOR' and rct.po_line_location_id = pll.line_location_id and rct.po_line_id = pol.po_line_id and nvl(pol.order_type_lookup_code,'QUANTITY') IN ('RATE','FIXED PRICE') group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id / Query to find PO corrections set lines 150 set pages 150 execute fnd_client_info.set_org_context('204'); SELECT pol.po_header_id,pol.po_line_id, rct.po_line_location_id Line_location_id , sum (nvl(rct1.source_doc_quantity,0) ) Qty_corrected 32 from rcv_transactions rct , rcv_transactions rct1 , po_lines pol , po_line_locations pll where rct.transaction_type in ( 'RECEIVE' ,'MATCH') and rct.po_line_location_id = pll.line_location_id and rct1.transaction_type = 'CORRECT' and rct1.parent_transaction_id = rct.transaction_id and rct1.po_line_location_id = pll.line_location_id and rct.po_line_id = pol.po_line_id and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE') group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id union all SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id , sum (nvl(rct1.amount,0) ) Qty_corrected from rcv_transactions rct , rcv_transactions rct1 , po_lines pol , po_line_locations pll where rct.transaction_type in ( 'RECEIVE' ,'MATCH') and rct.po_line_location_id = pll.line_location_id and rct1.transaction_type = 'CORRECT' and rct1.parent_transaction_id = rct.transaction_id and rct1.po_line_location_id = pll.line_location_id and rct.po_line_id = pol.po_line_id and nvl(pol.order_type_lookup_code,'QUANTITY') IN ('RATE','FIXED PRICE') group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id Query to find first level components of an Assemby set lines 150 set pages 150 col segment1 form a20 select bom.assembly_item_id, bic.component_sequence_id , bic.bill_sequence_id , --parent_bill_seq_id , bic.operation_seq_num , bic.component_item_id , msi.segment1, --bic.bom_item_type , bic.item_num , bic.component_quantity from bom_inventory_components bic, mtl_system_items msi, 33 bom_bill_of_materials bom where bom.assembly_item_id=149 and bom.organization_id=207 and bom.bill_sequence_id=bic.bill_sequence_id and bic.component_item_id=msi.inventory_item_id and msi.organization_id=207 order by 1,2 / Query to find all level components of an Assembly set lines 150 set pages 150 col parent_item form a20 col child_item form a20 break on parent_item select (select msi.segment1 from mtl_system_items msi where msi.inventory_item_id=bom.assembly_item_id and msi.organization_id=207) parent_item, bom.assembly_item_id, lpad(' ',2*(level-1),' ')|| (select msi.segment1 from mtl_system_items msi where msi.inventory_item_id=bic.component_item_id and msi.organization_id=207) child_item, bic.component_item_id child_item_id, --bic.component_sequence_id , bic.bill_sequence_id , --parent_bill_seq_id , bic.operation_seq_num , --bic.bom_item_type , --bic.item_num , level, bic.component_quantity from bom_inventory_components bic, (select * from bom_bill_of_materials where organization_id=207) bom where bom.bill_sequence_id=bic.bill_sequence_id start with bom.assembly_item_id=149 connect by prior bic.component_item_id=bom.assembly_item_id --order by level, bom.assembly_item_id / (Oracle uses BOM explosion package for this, which takes care of various conditions like Model and Option class etc) 34 Query to find latest execution of the Concurrent Program select fcp.concurrent_program_name, fcp.user_concurrent_program_name, fcr.requested_by Executed_By, count(fcr.concurrent_program_id) Exec_Count, max(fcr.request_date) Last_Run_Date from FND_CONCURRENT_REQUESTS fcr,FND_CONCURRENT_PROGRAMS_VL FCP where 1=1 AND upper(fcp.concurrent_program_name)='QL_INV_TOT_REV' AND fcr.PROGRAM_APPLICATION_ID= fcp.application_id and fcp.concurrent_program_id = fcr.concurrent_program_id group by fcp.concurrent_program_name,fcp.user_concurrent_program_name,fcr.request ed_by Querying order_number and po_number through receipt_num select rsh.receipt_num, ooha.order_number, pha.segment1 po_number from oe_order_headers_all ooha , oe_order_lines_all oola , oe_drop_ship_sources odss , po_headers_all pha , po_lines_all pla ,rcv_shipment_headers rsh ,rcv_shipment_lines rsl where oola.header_id =ooha.header_id AND odss.header_id =ooha.header_id AND odss.line_id =oola.line_id AND odss.po_header_id =pha.po_header_id AND odss.po_line_id =pla.po_line_id AND rsl.po_header_id =pha.po_header_id AND rsl.shipment_header_id=rsh.shipment_header_id AND rsh.receipt_num='8000030674'; Drop Ship order receiving select ooha.order_number , ooha.order_type_id 35 from where ,ooha.created_by , fnd.user_name ,oola.flow_status_code ,oola.ordered_quantity ,oola.shipped_quantity ,oola.fulfilled_quantity ,oola.shipping_quantity , ottt.name order_type ,otta.sales_document_type_code , prha.segment1 requisition_num ,prla.line_num requisition_line_num ,pha.creation_date "po date" ,pha.segment1 "po number" ,pha.type_lookup_code "po type" ,pda.quantity_ordered , pda.quantity_delivered , pla.line_num "po line num" ,pha.authorization_status po_status , rsh.receipt_num ,rsl.line_num shipment_line_num oe_order_headers_all ooha ,oe_order_lines_all oola , oe_drop_ship_sources odss , po_requisition_headers_all prha , po_requisition_lines_all prla , po_headers_all pha ,po_lines_all pla ,po_distributions_all pda , rcv_shipment_headers rsh ,rcv_shipment_lines rsl ,oe_transaction_types_tl ottt , oe_transaction_types_all otta ,fnd_user fnd oola.header_id =ooha.header_id AND ottt.transaction_type_id =otta.transaction_type_id AND otta.transaction_type_id =ooha.order_type_id AND fnd.user_id =ooha.created_by AND odss.line_id =oola.line_id AND odss.header_id =ooha.header_id AND odss.requisition_line_id =prla.requisition_line_id AND prla.requisition_header_id =prha.requisition_header_id AND pla.po_header_id =pha.po_header_id AND odss.po_line_id =pla.po_line_id AND odss.po_header_id =pha.po_header_id AND pla.po_line_id =pda.po_line_id AND rsl.po_header_id =pha.po_header_id 36 AND rsl.shipment_header_id =rsh.shipment_header_id AND ooha.order_number=100013652; Order,Shipping and Invoiced Details SELECT ooha.order_number ,shp_hp.party_name customer_name ,shp_hca.account_number customer_number ,shp_hp.party_name ship_to_customer_name ,shp_hca.account_number ship_to_customer_number ,shp_hl.address1 shp_address ,shp_hl.address2 ,shp_hl.address3 ,shp_hl.city||' , '||shp_hl.state||' , '||shp_hl.postal_code|| ' , '||shp_hl.country ship_to_address ,bll_hp.party_name bill_to_customer_name ,bll_hca.account_number bill_to_customer_number ,bll_hl.address1 billing_address , bll_hl.address2 ,bll_hl.address3 ,bll_hl.city||' , '||bll_hl.state|| ' , '||bll_hl.postal_code|| ' , '||bll_hl.country bll_to_address ,oola.line_number ,msi.segment1 item ,msi.description ,oola.ordered_quantity ,wdd.unit_price ,wdd.unit_price * wdd.SHIPPED_QUANTITY line_amount ,wnd.delivery_id ,rct.trx_number ,rct.trx_date ,rctl.line_number ,rctl.quantity_invoiced --,quantity_ordered ,rctl.unit_selling_price * rctl.QUANTITY_INVOICED invoice_line_amount FROM oe_order_headers_all ooha, oe_order_lines_all oola, hz_parties shp_hp, hz_party_sites shp_hps, hz_cust_accounts shp_hca, 37 hz_cust_acct_sites_all shp_hcasa, hz_cust_site_uses_all shp_hcsua, hz_locations shp_hl, hz_parties bll_hp, hz_party_sites bll_hps, hz_cust_accounts bll_hca, hz_cust_acct_sites_all bll_hcasa, hz_cust_site_uses_all bll_hcsua, hz_locations bll_hl, mtl_system_items_b msi, wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_new_deliveries wnd, ra_customer_trx_all rct, ra_customer_trx_lines_all rctl WHERE oola.header_id=ooha.header_id AND shp_hps.party_id=shp_hp.party_id AND shp_hca.party_id=shp_hp.party_id AND shp_hcasa.party_site_id=shp_hps.party_site_id AND shp_hcasa.cust_account_id=shp_hca.cust_account_id AND shp_hcsua.site_use_id=ooha.ship_to_org_id AND shp_hcsua.cust_acct_site_id=shp_hcasa.cust_acct_site_id AND shp_hl.location_id=shp_hps.location_id AND bll_hps.party_id=bll_hp.party_id AND bll_hca.party_id=bll_hp.party_id AND bll_hcasa.party_site_id=bll_hps.party_site_id AND bll_hcasa.cust_account_id=bll_hca.cust_account_id AND bll_hcsua.cust_acct_site_id=bll_hcasa.cust_acct_site_id AND bll_hcsua.site_use_id=ooha.invoice_to_org_id AND bll_hl.location_id=bll_hps.location_id AND msi.inventory_item_id=oola.inventory_item_id AND msi.organization_id=oola.ship_from_org_id AND wdd.source_line_id=oola.line_id AND wda.delivery_detail_id=wdd.delivery_detail_id AND wda.delivery_id=wnd.delivery_id AND rctl.customer_trx_id=rct.customer_trx_id AND rctl.interface_line_attribute6=oola.line_id AND ooha.order_number=100013627 ; Purchase Order, Receipt and invoice Details SELECT pha.segment1 po_number ,pv.vendor_id 38 ,pv.vendor_name ,shp_hl.location_code ship_to ,pvs.vendor_site_code ,pla.line_num ,plt.line_type ,pla.item_description ,pla.quantity ,pla.unit_price ,pla.quantity * pla.unit_price line_amount ,pll.invoice_close_tolerance ,pll.match_option ,pll.receive_close_tolerance ,rsl.line_num shipment_line_num ,rsl.to_organization_id ,rsl.quantity_shipped ,pda.distribution_num ,pda.destination_type_code ,pda.destination_subinventory ,pda.quantity_delivered ,rsh.receipt_num ,aia.invoice_num ,aia.invoice_amount ,aia.invoice_date FROM po_headers_all pha , po_lines_all pla , po_vendors pv , po_vendor_sites_all pvs , po_line_types plt , po_line_locations_all pll , hr_locations shp_hl --, hr_locations bll_hl ,rcv_shipment_lines rsl , po_distributions_all pda , rcv_shipment_headers rsh , rcv_transactions rt , ap_invoices_all aia , ap_invoice_distributions_all aid WHERE pha.po_header_id =pla.po_header_id AND pha.vendor_id =pv.vendor_id AND pv.vendor_id =pvs.vendor_id and pha.org_id = pvs.org_id AND pla.line_type_id =plt.line_type_id AND pla.po_header_id =pll.po_header_id AND pla.po_line_id =pll.po_line_id AND shp_hl.ship_to_location_id =pll.ship_to_location_id AND pha.po_header_id =rsl.po_header_id 39 AND rsh.shipment_header_id =rsl.shipment_header_id AND rsl.po_distribution_id =pda.po_distribution_id AND aia.invoice_id =aid.invoice_id --AND pda.po_distribution_id =aid.po_distribution_id AND aid.rcv_transaction_id =rt.transaction_id AND rsh.shipment_header_id =rt.shipment_header_id AND pha.segment1 ='Testing001'; Migrate DFF from Instance1 to Instance2 /*Download description flexfield*/ Run the below download command in home directory of Instance1, the ldt file descript_flex.ldt is created in the same directory. FNDLOAD apps/ 0 Y DOWNLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt desc_flex_application DESCRIPTIVE_FLEXFIELD_NAME=description_flexfield_name desc_flex_application - is the shortname of the Application of the DFF. description_flexfield_name - This is not title of the DFF. To get description_flexfield_name: Open the DFF in Application DeveloperFlexfieldDescriptiveSegments 40 click help->diagnostics->examine-> block=table, field=DESCRIPTIVE_FLEXFIELD_NAME. The value in the field “Value” is the description_flexfield_name. For Example, The description_flexfield_name of the DFF “Additional Information” in the above screenshot is: CS_INCIDENTS_ALL_B_EXT. Application is: Service. Short name of Service is: CS And the download command is: FNDLOAD apps/ 0 Y DOWNLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt CS DESCRIPTIVE_FLEXFIELD_NAME=CS_INCIDENTS_ALL_B_EXT /*Upload description flexfield*/ Transfer the ldt file descript_flex.ldt from Instance1 to the home directory of the Instance2 and run the below upload command in the same directory. FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt 41 /*Compiling description flexfield*/ After migration, run the below compilation command to compile the flexfield in the Instance2. fdfcmp apps/ 0 Y D desc_flex_application description_flexfield_name doskey = set ksh -o vi Find locks with Oracle SELECT c.owner,c.object_name,c.object_type,b.sid, b.serial#,b.status,b.osuser,b.machine FROM v$locked_object a ,v$session b,dba_objects c WHERE b.sid = a.session_id AND a.object_id = c.object_id; KILL THE LOCKED OBJECT alter system kill session 'sid,serial#'; ex:- alter system kill session '7,36'; LOB Query SELECT msi.inventory_item_id,msi.segment1,mic.category_id,mcs.category_set_id,mc.segment20 lob FROM mtl_system_items msi,mtl_item_categories mic,mtl_category_sets mcs,mtl_categories mc WHERE msi.inventory_item_id = 52986 AND msi.organization_id = 5760 AND mic.inventory_item_id = msi.inventory_item_id AND mic.organization_id = msi.organization_id AND mic.category_set_id = mcs.category_set_id AND mcs.category_set_name = 'PRODUCT_LINE' AND mc.category_id = mic.category_id; Query to delete duplicate records Delete from emp where ROWID not in ( select max(rowid) from emp group by empno); Locks select * from dba_locks where blocking_others = 'Blocking'; select * from dba_ddl_locks where name like '%QL_RMA4_WARR_INHERIT_PKG%'; 42 alter system kill session '28,6316'; Forms Environmental Variable FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US export FORMS60_PATH echo $FORMS60_PATH (or) . ./APPSORA.env Forms Compile f60gen ABCDEFG.fmb username/password CUSTOM.pll Complie f60gen Module=CUSTOM.pll Userid=apps/apps Module_Type=LIBRARY Output_File=/proj4/proj4appl/au/11.5.0/resource/CUSTOM.plx Compile_All=Yes After compiling the CUSTOM.pll we have to relogin the Application session LDT command for Lookups FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct STI_SERIAL_FORMAT_LOV.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="INV" LOOKUP_TYPE="STI_SERIAL_FORMAT_LOV" FNDLOAD apps/apps1571 O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct STI_SERIAL_FORMAT_LOV.ldt LDT command for Profile FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct serial_number_validation_profile_erp2.ldt PROFILE PROFILE_NAME='QL:SERIAL_VALIDATIONS_OVERRIDE_ERP2' APPLICATION_SHORT_NAME='QL' 43 FNDLOAD apps/apps1571 O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct serial_number_validation_profile_erp2.ldt LDT Command for Concurrent Prog FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct xyz.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name" FNDLOAD apps/apps1571 O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct xyz.ldt Adding Conc Program to Request Group FNDLOAD apps/simple4u 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct NAP_ACK_R1.ldt REQUEST_GROUP REQUEST_GROUP_UNIT UNIT_TYPE='P' UNIT_APP='XBOL' UNIT_NAME='NAPP_OEXOEACK' REQUEST_GROUP_NAME='NAPP OM Concurrent Programs' APPLICATION_SHORT_NAME='ONT' FNDLOAD apps/simple4u 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct NAP_ACK_R1.ldt Optio USERS.DAT --> $FND_TOP/bin -- ASCII Format QL_PRINT --> $FND_TOP/bin -- ASCII Format *.dcl --> /usr/odcs636/doc -- Binary Format IMP Creation of Soft Link >> Should run the command from "QL_TOP/bin" to create the softlink >> EX:- "ln -s $FND_TOP/bin/fndcpesr LOAD_HYPERION " ( Actual file name LOAD_HYPERION.prog ) To Identify the Version of a File adident Header POXPORRA.rdf Running Concurrent Program via CONCSUB For a concurrent program defined via “Define Concurrent Program” form but submitted via CONCSUB, the following parameters can be used by CONCSUB : 44 PRINTER= NUMBER_OF_COPIES= PRINT_STYLE= LANGUAGE= Sample: CONCSUB apps/apps SYSADMIN “System Administrator” SYSADMIN \ WAIT=Y CONCURRENT FND FNDSCARU LANGUAGE=FRENCH \ PRINTER=hplj4l NUMBER_OF_COPIES=1 \ PRINT_STYLE=LANDSCAPE Note: the others parameters which can be used with CONCSUB are described in the “System Administration User Guide” as follow : $ CONCSUB / \ \ \ \ [WAIT=N|Y|] \ CONCURRENT \ \ \ [PROGRAM_NAME=] \ [REPEAT_TIME=] \ [REPEAT_INTERVAL= ] \ [REPEAT_INTERVAL_UNIT=< resubmission unit>] \ [REPEAT_INTERVAL_TYPE=< resubmission type>] \ [REPEAT_END=] \ [START=] \ [IMPLICIT=< type of concurrent request> \ [ ... ] For parameters that follow the CONCURRENT parameter and include spaces, enclose the parameter argument in double quotes, then again in single quotes. Providing permission to $XX_TOP Directories chmod -R 777 $PO_TOP Copy A Table From One Instance To Another Instance COPY FROM apps/apps@proj10 TO apps/apps1574@ps4 create sam USING select * from sam; 45 1. Find out the On Hand Balance as on Day using the function Recentlry i have worked on one report, The main purpose of the report is , based on item, oraganizarion and subiventory it need to display the onhand qty for a gievn date. i used below code in formula column of report to calculate onhand qty on a given perticular date FUNCTION CF_LAST_BALFormula RETURN NUMBER IS v_onhand_qty NUMBER; v_last_qty NUMBER; v_target_qty NUMBER; BEGIN SELECT SUM(transaction_quantity) INTO v_onhand_qty FROM mtl_onhand_quantities_detail WHERE inventory_item_id = :inventory_item_id AND organization_id = :organization_id AND subinventory_code = :Subinventory; SELECT NVL(SUM(Transaction_quantity),0) INTO v_last_qty FROM mtl_material_transactions WHERE inventory_item_id = :inventory_item_id AND organization_id = :organization_id AND subinventory_code = :Subinventory AND trunc(NVL(transaction_date,creation_date))>= TRUNC(TO_DATE(:p_last_date,'RRRR/MM/DD')); v_target_qty:=(v_onhand_qty)-(v_last_qty); RETURN(v_target_qty); EXCEPTION WHEN OTHERS THEN RETURN 0; END; select sum(transaction_quantity) from mtl_material_transactions where inventory_item_id = 27628 and organization_id = 110 and subinventory_code 'Staging' and trunc(transaction_date)