, , , , ,

What is Query for getting customers having multiple accounts?

SELECT hca.account_number,rc.customer_name, hp.party_type,hp.party_name
 FROM hz_parties hp, hz_Cust_Accounts hca,ar.ra_customers rc
WHERE hp.party_id = hca.party_id
and rc.customer_id=hca.cust_Account_id
  AND hca.cust_account_id IN (
         SELECT   customer_id
             FROM ap_bank_account_uses_all bauses,
                  ap_bank_accounts_all bacct
            WHERE bauses.external_bank_account_id = bacct.bank_account_id
              AND primary_flag = ‘Y’
         GROUP BY customer_id)
          HAVING COUNT (DISTINCT (external_bank_account_id)) > 1)
/by
, , , , , ,

Payables Open Interface for Project based Invoices

Following scripts used to test Payable open Interface for Invoices contains Project Data:

 Query for to get PO details:
 –select * from po_headers_all  where  PO_HEADER_ID=63845
–AND segment1=’NEX060378-002′
 –select * from po_lines_all where po_header_id=63845
–select * from po_vendors where vendor_id=11813
 –select * from po_line_locations_all where po_header_id=63845
  –select *  from PO_DISTRIBUTIONS_ALL
 –select * from ap_invoice_lines_interface
 –commit
Query to Set Org_id: 
–begin
–dbms_application_info.set_client_info(‘169’);
–end;

–select * from ap_invoices_interface where invoice_num like ‘COL%’
–select * from ap_invoice_lines_interface
— select * from  AP_TERMS
–select * from PO_DISTRIBUTIONS_ALL

DECLARE
    p_invoice_id       NUMBER;
    i                  NUMBER;
BEGIN

Query to Generate invoice_id —

    select AP_INVOICES_INTERFACE_S.nextval
    into p_invoice_id
    from dual;

Query to  Insert an invoice header —
 insert into ap_invoices_interface(INVOICE_ID,
                 INVOICE_NUM,
                   INVOICE_TYPE_LOOKUP_CODE,
                  INVOICE_DATE,
                 PO_NUMBER,
                 VENDOR_ID,
                 VENDOR_SITE_ID,
                   INVOICE_AMOUNT,
                 INVOICE_CURRENCY_CODE,
                  –TERMS_ID,
                   SOURCE,
                   GROUP_ID,
                PAYMENT_METHOD_LOOKUP_CODE,
                ORG_ID)
        VALUES  (p_invoice_id,         –INVOICE_ID,
                  ‘COL012’,              –INVOICE_NUM,
                  ‘STANDARD’,            –INVOICE_TYPE_LOOKUP_CODE,
                  sysdate,              –INVOICE_DATE,
                 ‘NEX060378-002’,                    –v_PO_NUMBER,
                11934,                 –v_VENDOR_ID, 11790
                14667,                 –v_VENDOR_SITE_ID, 14506
                10,                       –v_INVOICE_AMOUNT,  (Sanjiv Wrong Amt)10
               ‘USD’,                  — v_INVOICE_CURRENCY_CODE, usd
                –10011,                  –v_TERMS_ID, (Sanjiv)
                ‘CONVERSION’,–‘INVOICE GATEWAY’,       –v_SOURCE,
                 ‘USM’,                  — v_GROUP_ID,
               ”,                     — v_PAYMENT_METHOD_LOOKUP_CODE, CHECK
               169);                   –v_ORG_ID
    — Insert invoice line —

    for i in 1..1 loop
INSERT into ap_invoice_lines_interface (INVOICE_ID,
                                INVOICE_LINE_ID,
                                LINE_NUMBER,
                                LINE_TYPE_LOOKUP_CODE,
                                AMOUNT,
                                ACCOUNTING_DATE,
                                FINAL_MATCH_FLAG,
                                PO_HEADER_ID,
                                PO_NUMBER,
                                 PO_LINE_ID,
                                 PO_LINE_NUMBER,
                                PO_LINE_LOCATION_ID,
                                PO_SHIPMENT_NUM,
                                INVENTORY_ITEM_ID,
                                ITEM_DESCRIPTION,
                                QUANTITY_INVOICED,
                                SHIP_TO_LOCATION_CODE,
                                UNIT_PRICE,
                                PO_RELEASE_ID,
                                RELEASE_NUM,
                                PRICE_CORRECTION_FLAG,
                                RECEIPT_NUMBER,
                                MATCH_OPTION,
                                RCV_TRANSACTION_ID,
                                DIST_CODE_COMBINATION_ID,
                                PO_DISTRIBUTION_ID,
                                PO_DISTRIBUTION_NUM,
                                PROJECT_ID,
                                TASK_ID,
                                EXPENDITURE_TYPE,
                                EXPENDITURE_ORGANIZATION_ID,
                                PA_QUANTITY,
                                EXPENDITURE_ITEM_DATE
                                )
                        VALUES (
                                 p_invoice_id,     –v_INVOICE_ID,
                                 AP_INVOICE_LINES_INTERFACE_S.nextval,       –v_INVOICE_LINE_ID,
                                     i,               –v_LINE_NUMBER,  1
                                  ‘ITEM’,           — v_LINE_TYPE_LOOKUP_CODE,
                                   10,            –v_AMOUNT, (Sanjiv Wrong Amt, PO Line=82.27) 10
                                  sysdate,           — v_ACCOUNTING_DATE,
                                  ”,                –v_FINAL_MATCH_FLAG,
                                  63845,                –v_PO_HEADER_ID,
                                 ‘NEX060378-002’,             — v_PO_NUMBER,
                                 63846,              –v_PO_LINE_ID, (Sanjiv Wrong POLineID) 47245
                                  1,               –v_PO_LINE_NUMBER,
                                  63846,           –v_PO_LINE_LOCATION_ID, (Sanjiv Wrong POLineLocID)
                                    ”,                –v_PO_SHIPMENT_NUM,
                                  ”,                –v_INVENTORY_ITEM_ID,
                                  ”,                –v_ITEM_DESCRIPTION,
                                  1,                –v_QUANTITY_INVOICED,
                                  ”,                –v_SHIP_TO_LOCATION_CODE,
                                 ”,                 –v_UNIT_PRICE,
                                ”,                 –v_PO_RELEASE_ID,
                                ”,                 –v_RELEASE_NUM,
                                   ”,                 –v_PRICE_CORRECTION_FLAG,
                                ”,                 –v_RECEIPT_NUMBER,
                                   ”,                –v_MATCH_OPTION,
                                   ”,                 –v_RCV_TRANSACTION_ID
                                ”,           –v_DIST_CODE_COMBINATION_ID
                                64302,            –PO_DISTRIBUTION_ID = 64302
                                1,                  –PO_DISTRIBUTION_NUM
                                ”,–1087,               –PROJECT_ID, (Sanjiv Wrong ProjID) 1172 1087
                                ”,–1551,                –TASK_ID,
                                ”,–‘Awrd Ovation Point Redemption’,–EXPENDITURE_TYPE,
                                 ”,–169,                –EXPENDITURE_ORGANIZATION_ID,
                                 ”,–”,             –PA_QUANTITY, (Sanjiv Wrong Qty)10
                              ” );– sysdate );           –EXPENDITURE_ITEM_DATE
      end loop;

 commit;
END;

Queries to debugged and trace Interface errors: 

–select * from ap_invoices_interface where invoice_id=13634
–select * from ap_invoice_lines_interface where invoice_id=13634
–select * from user_errors where name like’USM%’;
— select * from pa_projects_all where project_id=1172 –1087
–select * from pa_projects_all where project_id in (select project_id from pa_tasks where task_number=’2.4′)
–10437
–select * from usm_po_codes
–select * from pa_projects_all where segment1 =’10437′

/by
, , , ,

How to Kill The Toad / Oracle session?

Whenever we Procedure taking too much time to compile and we want to close the session or cancle the compilation we need to kill the session.

If we need the PC Name Based Query ::

select * from   v$session where TERMINAL =’Name of user’s Computer’

Query for when we know the Object Name and we need to close the session based upon Object::

SELECT * FROM v$session WHERE SID IN (SELECT UNIQUE SID FROM v$access WHERE OBJECT=’&OBJECT_NAME’);

OR you may use below query and output of below query will give ready made statements to kill these locks and you can directly use it…

SELECT ‘alter system kill session ”’||SID||’,’||serial#||”’;’
FROM v$session
WHERE TERMINAL =’prashku4′
and SID IN (SELECT UNIQUE SID
FROM v$access)
WHERE OBJECT=’&OBJECT_NAME’);

select * from  v$access

Example:
alter system kill session ‘8542,2456258’;

/by
, , , , , ,

What is Subinventory Quantities report?

Form: INVIRSIQ
Use the Subinventory Quantities Report to show inventory item quantities by subinventory.
Report Submission
Use the On-hand Quantity Reports or Submit Requests window and enter Subinventory quantities report in the Name field to submit the report.
Report Parameters
Item Range
Choose one of the following options:
Full listing   :    Report all subinventories.
Partial list by :  Report only those subinventories for a inventory item  partialrange of items.
Specific          :  Report only the subinventory you subinventory  specify.
Items From/To
Enter a beginning and ending item to restrict the report to a range of items. You can enter an item here only if you enter Partial list by inventory item in the Item Range field.
Subinventory
Enter a subinventory for which to report on-hand quantity. You can enter a value here only if you enter Specific subinventory in the Item
Range field.
The original report has the following columns:
1.Item
2.Description
3.Rev
4.Locator
5.UOM
6.Quantity
Customization:
–Add the price of every item.
–Calculate the price*qty=total
–Add the subinventory total
This would give us the value of the sub inventory.
Tables Used:
1.MTL_SYSTEM_ITEMS (msi)
2.MTL_ONHAND_QUANTITIES_DETAIL (moqd)
3.MTL_SECONDARY_INVENTORIES (si)
4.MTL_ITEM_LOCATIONS (mil)
The tables added:
1.CST_ITEMS_COST (cs)
MTL_SYSTEM_ITEMS:
MTL_SYSTEM_ITEMS_B is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. You can specify item-related information in fields such as: Bill of Material, Costing, Purchasing, Receiving, Inventory,
Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing. You can set up the item with multiple segments, since it is implemented as a flexfield.
Use the standard ’System Items’ flexfield that is shipped with the product to configure your item flexfield. The flexfield code is MSTK. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Each item is initially defined in an item master organization. The user then assigns the item to other organizations that need to recognize this item; a row is inserted for each new organization the item is assigned to.
Many columns such as MTL_TRANSACTIONS_ENABLED_ FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in the MTL_ITEM_ATTRIBUTES table. The attributes that are available to the user depend on which Oracle applications are installed. The table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between item attributes and Oracle applications. Two unit of measure columns are stored in MTL_SYSTEM_ITEMS table.
PRIMARY_UOM_CODE is the 3-character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25-character unit that is used throughout Oracle Purchasing. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL.
Translations table (MTL_SYSTEM_ITEMS_TL) holds item descriptions in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only.
MTL_ONHAND_QUANTITIES_DETAIL
MTL_ONHAND_QUANTITIES stores quantity on hand information
by control level and location.
MTL_ONHAND_QUANTITIES is maintained as a stack of receipt
records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria. Note that any transactions which are
committed to the table MTL_MATERIAL_TRANSACTIONS_TEMP are
considered to be played out as far as quantity on hand is concerned in Inventory transaction forms. All our Inquiry forms and ABC compile are only based on MTL_ONHAND_QUANTITIES.MTL_ONHAND_QUANTITIES has two columns, CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_IDs to join to MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID the
transactions that created the row and the transaction that last updated a row.
MTL_SECONDARY_INVENTORIES
MTL_SECONDARY_INVENTORIES is the definition table for the
subinventory. A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this
item will physically exist in inventory.
Other attributes include general ledger accounts, demand picking order, locator type, availability type, and reservable type. You can also specify whether this subinventory is an expense or asset subinventory (ASSET_INVENTORY), or whether quantities are tracked
(QUANTITY_TRACKED).
MTL_ITEM_LOCATIONS
MTL_ITEM_LOCATIONS is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.
The locator is a key flexfield. The Flexfield Code is MTLL.
CST_ITEMS_COST
CST_ITEM_COSTS stores item cost control information by cost type.
For standard costing organizations, the item cost control information for the Frozen cost type is created when you enter a new item. For average cost organizations, item cost control information is created when you transact the item for the first time.
You can use the Item Costs window to enter cost control information.
Where clause of the original report:
1.si.secondary_inventory_name = moqd.subinventory_code(+)
2.si.organization_id = moqd.organization_id (+)
3.moqd.organization_id = msi.organization_id(+)
4.moqd.inventory_item_id = msi.inventory_item_id (+)
5.moqd.locator_id = mil.inventory_location_id(+)
6.moqd.organization_id = mil.organization_id(+)
Additional where clause added for the customized report:
1.cs.inventory_item_id = msi.inventory_item_id
2.cs.organization_id = msi.organization_id
/by
, , , , ,

Tips of Oracle SQL Performance Tuning

These are the tips that needs to be followed to performance tune your SQL scripts;

·    Never do a calculation on an indexed column (e.g., WHERE salary*5 > :myvalue)

·    Whenever possible, use the UNION statement instead of OR conditions

·    Avoid the use of NOT IN or HAVING in the WHERE clause. Instead, use the NOT EXISTS clause

·    Always specify numeric values in numeric form and character values in character form (e.g., WHERE emp_number = 565, WHERE emp_name = ?Jones?)

·    Avoid specifying NULL in an indexed column

·    Avoid the LIKE parameter if = will suffice. Using any Oracle function will invalidate the index, causing a full-table scan

·    Never mix data types in Oracle queries, as it will invalidate the index. If the column is numeric, remember not to use quotes (e.g., salary = 50000). For char index columns, always use single quotes (e.g., name = ?BURLESON?)

·    Remember that Oracle’s rule-based optimizer looks at the order of table names in the FROM clause to determine the driving table. Always make sure that the last table specified in the FROM clause is the table that will return the smallest number of rows. In other words, specify multiple tables with the largest result set table specified first in the FROM clause

·    Avoid using sub-queries when a JOIN will do the job

·    Use the Oracle decode function to minimize the number of times a table has to be selected

·    To turn off an index you do not want to use (only with a cost-based optimizer), concatenate a null string to the index column name (e.g., name||’) or add zero to a numeric column name (e.g., salary+0). With the rule-based optimizer, this allows you to manually choose the most selective index to service your query

·    If your query will return more than 20 percent of the rows in the table, use a full-table scan rather than an index scan

·    Always use table aliases when referencing columns
·    Understand the data. Look around table structures and data. Get a feel for the data model and how to navigate it.
·    If a view joins 3 extra tables to retrieve data that you do not need, don’t use the view!
·    When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!
·    Avoid multiple layers of view. For example, look for queries based on views that are themselves views. It may be desirable to encapsulate from a development point of view. But from a performance point of view, you loose control and understanding of exactly how much task loading your query will generate for the system.
·    Look for tables/views that add no value to the query. Try to remove table joins by getting the data from another table in the join.
·    WHERE EXISTS sub-queries can be better than join if can you reduce drastically the number of records in driver query. Otherwise, join is better.
·    WHERE EXISTS can be better than join when driving from parent records and want to make sure that at least on child exists. Optimizer knows to bail out as soon as finds one record. Join would get all records and then distinct them!
·    In reports, most of the time fewer queries will work faster. Each query results in a cursor that Reports has to open and fetch. See Reports Ref Manual for exceptions.
·    Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0.
·    Avoid writing where project_category is not null. Nulls can prevent the optimizer from using an index.
·    Consider using IN or UNION in place of OR on indexed columns. OR’s on indexed columns causes the optimizer to perform a full table scan.
·    Avoid calculations on indexed columns. Write WHERE approved_amt > 26000/3 instead of WHERE approved_amt/3 > 26000.
·    Avoid this: SUBSTR(haou.attribute1,1,LENGTH(‘:p_otc’)) = :p_otc). Consider this: WHERE  haou.attribute1 like :p_otc||’%’
·    Talk to your DBA. If you think that a column used in a WHERE clause should have an index, don’t assume that an index was defined. Check and talk to your DBA if you don’t find any.
·    Consider replacing outer joins on indexed columns with UNION. A nested loop outer takes more time than a nested loop un-joined with another table access by index.
·    Consider adding small frequently accessed columns (not frequently updated) to an existing index. This will enable some queries to work only with the index, not the table.
·    Consider NOT EXISTS instead of NOT IN.
·    If a query is going to read most of the records in a table (more than 60%), use a full table scan.
·    Try to group multiple sub queries into one.
·    If you want to actually understand what you are doing, here are a few things that you need to start playing with:
·    Get into EXPLAIN_PLAN. There are multiple way of doing this. The less user friendly is to simply issue this in SQL*Plus: explain plan set statement_id = ‘HDD1’ for ;
·    Look at the trace from Oracle Reports. It tells you how much time it spends on each query.
·    Use the SQL Trace by issuing an alter session set sql_trace=true; then look at it with TKPROF .trc
·    Do not use functions on indexed columns in WHERE clauses Unless specifically using a function based index.

·    Beware of implicit datatype conversions occuring on indexed columns (e.g. comparing a character column with a number expression will invoke an implicit
TO_NUMBER operation on the indexed column, preventing use of the index – in this case it would be better to us an explicit TO_CHAR operation around thenumber expression being compared against

·    Make sure that you have the correct indexes on the tables:-
·    Ensure that the first column of the index is selective ( e.g. more than around 15 distinct values with a near uniform distribution
·    Avoid using insufficiently selective indexes (i.e. each index value applies to at least 15% of the rows).  In this case, a full table scan will be more efficient.
·    Aim to have no more than 4 indexes per table. ( each insert into the table has to also modify data in the indexes )

·    To use compound indexes ensure that the WHERE clause includes conditions on  the leading column(s) of the index

·    NEVER use SELECT * FROM. in application code

·    Try to avoid use of the DISTINCT clause as it always requires a sort operation.  Excessive use of DISTINCT clause may point to an underlying data model problem (e.g a missing table).

/by