QUERY TO FIND THE ISO ORDER AGAINST SHIP DISCREPANCY QTY

SELECT   (SELECT   nvl(SUM (quantity),0)
            FROM   rcv_shipment_headers rsh,
                   rcv_shipment_lines rsl,
                   rcv_transactions rt
           WHERE       rt.shipment_header_id = rsh.shipment_header_id
                   AND rsh.shipment_header_id = rsl.shipment_header_id
                   AND rt.shipment_line_id = rsl.shipment_line_id
                   AND rsh.shipment_num = :shipment_num
                   AND rt.transaction_type = 'RECEIVE')
            rcv_qty,                           /* rcv transaction interface */
         (SELECT   nvl(SUM (quantity),0)
            FROM   rcv_transactions_interface rt, rcv_shipment_headers rsh
           WHERE   rsh.shipment_header_id = rt.shipment_header_id
                   AND rsh.shipment_num = :shipment_num)
            interface_qty,
         (SELECT   NVL (SUM (SHIPPED_QUANTITY), 0)
            FROM   wsh_delivery_details wdd, wsh_delivery_assignments wda
           WHERE       wdd.delivery_detail_id = wda.delivery_detail_id
                   AND wdd.released_status = 'C'
                   AND wdd.INV_INTERFACED_FLAG = 'Y'
                   AND to_char(wda.delivery_id) =:shipment_num)
            order_qty
  FROM   DUAL

QUERY FOR STOCK TRANSFER

/* Formatted on 6/30/2012 3:38:35 PM (QP5 v5.115.810.9015) */
--SELECT   a.*, operand current_rate
--  FROM   (

CREATE TABLE xxhmi_stock_txn
AS
   (SELECT   hou.NAME,
             (SELECT   org.organization_name
                FROM   org_organization_definitions org
               WHERE   jowl.organization_id = org.organization_id)
                inventory_org,
             hp.party_name dealer_name,
             hca.account_number dealer_number,
             (   hl_bill.address1
              || ','
              || hl_bill.address2
              || ','
              || hl_bill.address3
              || ','
              || hl_bill.address4)
                dealer_address,
             hl_bill.city dealer_city,
             hl_bill.state dealer_state,
             ft_bill.nls_territory dealer_country,
             otl.NAME sales_order_type,
             oh.order_number sales_order_number,
             ol.line_number sales_order_line_no,
             oh.ordered_date sales_order_date,
             ' ' ar_invoice_number,
             ' ' ar_invoice_date,
             (SELECT   MAX (jis.item_tariff)
                FROM   jai_inv_itm_setups jis
               WHERE   jis.inventory_item_id = ol.inventory_item_id
                       AND jis.organization_id = ol.ship_from_org_id)
                excise_tariff_no,
             ' ' excise_invoice_num,
             wnd.attribute2 challan_no,
             jowl.EXCISE_INVOICE_DATE excise_invoice_date,
             jowl.vat_invoice_no,
             wnd.attribute6 sale_invoice_no,
             msi.segment1 part_no,
             msi.description descripition_of_part,
             jowl.quantity qty,
             0 current_mrp_price,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type = 'Excise'
                       AND delivery_detail_id = jowl.delivery_detail_id)
                excise,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type = 'EXCISE_EDUCATION_CESS'
                       AND delivery_detail_id = jowl.delivery_detail_id)
                ed_cess,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type = 'EXCISE_SH_EDU_CESS'
                       AND delivery_detail_id = jowl.delivery_detail_id)
                she_cess,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type IN ('Addl. Excise', 'CVD')
                       AND delivery_detail_id = jowl.delivery_detail_id)
                addl_excise_cvd,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type = ('CST')
                       AND delivery_detail_id = jowl.delivery_detail_id)
                cst,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type = ('VALUE ADDED TAX')
                       AND delivery_detail_id = jowl.delivery_detail_id)
                vat,
             (jowl.quantity * ol.unit_list_price) base_value,
             oh.transactional_curr_code,
             ' ' exchange_rate,
             wnd.NAME delivery_no,
             wnd.ultimate_dropoff_date transporter_ship_to_date,
             ol.attribute10 dealer_po_number,
             ol.attribute11 dealer_po_line_number,
             (SELECT   booking_number
                FROM   wsh_delivery_legs
               ---WSH_BOLS_DB_V --- ADDED
               WHERE   delivery_id = jowl.delivery_id)
                booking_number,
             (SELECT   service_contract
                FROM   wsh_delivery_legs
               ---WSH_BOLS_DB_V --- ADDED
               WHERE   delivery_id = jowl.delivery_id)
                service_contract,
             (SELECT   aetc_number
                FROM   wsh_delivery_legs
               ---WSH_BOLS_DB_V --- ADDED
               WHERE   delivery_id = jowl.delivery_id)
                aetc_number,
             (SELECT   wc.freight_code
                FROM   wsh_carriers wc
               WHERE   wnd.carrier_id = wc.carrier_id)
                transporter_name,
             (SELECT   ROUND (item_cost, 2)
                FROM   cst_item_costs
               WHERE       inventory_item_id = ol.inventory_item_id
                       AND organization_id = ol.ship_from_org_id
                       AND cost_type_id = 2)
                average_cost_per_unit,
             ' ' average_trn_value,
             (SELECT   mck1.concatenated_segments
                FROM   mtl_item_categories mic1,
                       mtl_categories_b mcb1,
                       mtl_categories_kfv mck1
               WHERE       mic1.category_set_id = 1100000044
                       AND mic1.category_id = mcb1.category_id
                       AND mcb1.structure_id = 50351
                       AND mcb1.structure_id = mck1.structure_id
                       AND mic1.category_id = mck1.category_id
                       AND ol.inventory_item_id = mic1.inventory_item_id
                       AND ol.ship_from_org_id = mic1.organization_id)
                basic_set,
             (SELECT   msit.long_description
                FROM   mtl_system_items_tl msit
               WHERE   msit.inventory_item_id = ol.inventory_item_id
                       AND msit.organization_id = ol.ship_from_org_id)
                model_code,
             SUBSTR (hca.customer_class_code, 1, 2) customer_classification,
             oh.creation_date order_upload_date,
             (SELECT   TRUNC (xwoa1.po_creation_date)
                FROM   xxhmi_wave_order_all xwoa1
               WHERE       hca.cust_account_id = xwoa1.customer_id
                       AND ol.inventory_item_id = xwoa1.inventory_item_id
                       AND ROWNUM = 1)
                dlr_po_date,
             (SELECT   mck.concatenated_segments
                FROM   mtl_categories_kfv mck,
                       mtl_item_categories mic,
                       mtl_categories_b mcb
               WHERE       mcb.structure_id = mck.structure_id              --
                       AND mic.category_id = mck.category_id                --
                       AND ol.inventory_item_id = mic.inventory_item_id
                       AND ROWNUM = 1)
                inventory_set,
             'FOC' inv_type,
             ( ( (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                    FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
                   WHERE   jowst.tax_id = jct.tax_id
                           AND jct.tax_type NOT IN
                                    ('EXCISE_EDUCATION_CESS',
                                     'EXCISE_SH_EDU_CESS',
                                     'Excise',
                                     'VALUE ADDED TAX',
                                     'CST',
                                     'Freight',
                                     'Insurance')
                           AND delivery_detail_id = jowl.delivery_detail_id)
                + DECODE (
                     oh.transactional_curr_code,
                     'INR',
                     ( (SELECT   ABS (NVL (SUM (o.adjusted_amount), 0))
                          FROM   oe_price_adjustments o
                         --OE_PRICE_ADJUSTMENTS_V o
                         WHERE       header_id = jowl.order_header_id
                                 AND o.line_id = jowl.order_line_id
                                 AND o.list_line_type_code = 'SUR')
                      * jowl.quantity),
                     ( (SELECT   ABS (NVL (SUM (o.adjusted_amount), 0))
                          FROM   oe_price_adjustments o
                         --OE_PRICE_ADJUSTMENTS_V o
                         WHERE       header_id = jowl.order_header_id
                                 AND o.line_id = jowl.order_line_id
                                 AND o.list_line_type_code = 'SUR')
                      * jowl.quantity)
                     * NVL (
                          (SELECT   conversion_rate
                             FROM   gl_daily_rates
                            WHERE   from_currency =
                                       oh.transactional_curr_code
                                    AND to_currency = 'INR'
                                    AND conversion_type =
                                          oh.conversion_type_code
                                    AND conversion_date =
                                          TO_CHAR (wnd.ultimate_dropoff_date,
                                                   'DD-Mon-YYYY')),
                          0
                       )
                  )))
                others_tax,
             ol.inventory_item_id,
             oh.ORDERED_DATE,
             jowl.ASSESSABLE_VALUE mrp_per_unit
      FROM   oe_order_headers_all oh,
             oe_transaction_types_tl otl,
             oe_order_lines_all ol,
             jai_om_wsh_lines_all jowl,
             wsh_new_deliveries wnd,
             hz_cust_accounts hca,
             hz_parties hp,
             hz_party_sites hps,
             hz_cust_acct_sites_all hcas_bill,
             hz_cust_site_uses_all hcsu_bill,
             hz_locations hl_bill,
             fnd_territories ft_bill,
             hr_operating_units hou,
             mtl_system_items_b msi
     WHERE       oh.header_id = ol.header_id
             AND hou.organization_id = oh.org_id
             AND oh.org_id = ol.org_id
             AND oh.order_source_id = 10
--             AND otl.transaction_type_id IN
--                      (1149, 1068, 1249, 1169, 1018, 1192, 1098, 1083, 1054)
             AND otl.transaction_type_id = oh.order_type_id
             AND msi.inventory_item_id = ol.inventory_item_id
             AND msi.organization_id = ol.ship_from_org_id
             AND wnd.delivery_id = jowl.delivery_id
             AND oh.header_id = jowl.order_header_id
             AND jowl.order_line_id = ol.line_id
             AND jowl.order_header_id = ol.header_id
             AND hp.party_id = hca.party_id
             AND hps.party_id = hp.party_id
             AND hcas_bill.cust_account_id = hca.cust_account_id
             AND hcsu_bill.cust_acct_site_id = hcas_bill.cust_acct_site_id
             AND hcsu_bill.site_use_id = oh.invoice_to_org_id
             AND hcsu_bill.site_use_code = 'BILL_TO'
             AND hl_bill.location_id = hps.location_id
             AND hcas_bill.party_site_id = hps.party_site_id
             AND hcsu_bill.org_id = ol.org_id
             AND hca.cust_account_id = ol.sold_to_org_id
             AND hl_bill.country = ft_bill.territory_code
--                      AND oh.order_number = '1210006057' --                   AND msi.segment1 = '83510KYJ900'
             AND jowl.organization_id = 81)
             AND TRUNC (jowl.EXCISE_INVOICE_DATE) BETWEEN '01-APR-2012'
                                                      AND  '27-JUN-2012')



--
/*                                                  ) a,
         (SELECT   qpa.product_attr_value,
                   NVL (qll.operand, 0) operand,
                   start_date_active,
                   end_date_active
            FROM   qp_list_lines qll, qp_pricing_attributes qpa
           WHERE   qll.list_line_id = qpa.list_line_id
                   AND qll.list_header_id = 11015) qpl
 WHERE       qpl.product_attr_value(+) = a.inventory_item_id
         AND a.ORDERED_DATE>= qpl.start_date_active(+)
         AND a.ORDERED_DATE <= qpl.end_date_active(+)*/










--                         AND ( (p_organization_id IS NOT NULL
--                                AND jowl.organization_id = p_organization_id)
--                              OR p_organization_id IS NULL)
--                         AND ( (p_org_id IS NOT NULL
--                                AND hou.organization_id = p_org_id)
--                              OR p_org_id IS NULL)
--                         AND ( (p_part_no IS NOT NULL
--                                AND msi.segment1 = p_part_no)
--                              OR p_part_no IS NULL)
--                         AND ( (p_dealer_code IS NOT NULL
--                                AND hca.account_number = p_dealer_code)
--                              OR p_dealer_code IS NULL)
--                         AND ( (p_business_line IS NOT NULL
--                                AND SUBSTR (hca.customer_class_code, 1, 2) =
--                                      p_business_line)
--                              OR p_business_line IS NULL)
--                         AND TRUNC (wnd.confirm_date) BETWEEN :p_from_date
--                                                          AND  :p_to_date
--                                                          ) y;







select a.*, operand  from xxhmi_stock_txn a,(SELECT   qpa.product_attr_value,
                   NVL (qll.operand, 0) operand,
                   start_date_active,
                   end_date_active
            FROM   qp_list_lines qll, qp_pricing_attributes qpa
           WHERE   qll.list_line_id = qpa.list_line_id
                   AND qll.list_header_id = 11015) qpl
 WHERE       qpl.product_attr_value(+) = a.inventory_item_id
         AND a.EXCISE_INVOICE_DATE>= qpl.start_date_active(+)
         AND a.EXCISE_INVOICE_DATE<= qpl.end_date_active(+)

ITEM CATEGORY QUERY

select a.segment1 ITEM_ID,
       a.INVENTORY_ITEM_STATUS_CODE ,
     --  a.attribute20 NPC,
       b.CONCATENATED_SEGMENTS CATEGORY
  from mtl_system_items_b a,
       mtl_categories_kfv b,
       mtl_item_categories c
       where c.inventory_item_id = a.inventory_item_id
       and c.category_id = b.category_id
      -- and a.attribute20 = 'GNU'
      and a.organization_id =c.organization_id
     and a.organization_id = 86
     and b.CONCATENATED_SEGMENTS like '%PP%'
    and a.INVENTORY_ITEM_STATUS_CODE = 'Not 4 Sale'

RECEIPT FOR THE PURCHASE ORDER AND INVENTORY

SELECT   rsh.receipt_num,
           SUM (rt.quantity) qty,
           rt.transaction_type,
           poh.segment1 po_number,
           mmt.LOCATOR_ID,
           mmt.subinventory_code
    FROM   rcv_transactions rt,
           rcv_shipment_lines rsl,
           rcv_shipment_headers rsh,
           po_headers_all poh,
           mtl_material_transactions mmt
   WHERE       transaction_type = 'DELIVER'
           AND rt.shipment_header_id = rsh.shipment_header_id
           AND rt.shipment_line_id = rsl.shipment_line_id
           AND rsh.shipment_header_id = rsl.shipment_header_id
           AND rsl.item_id = 17099
           AND rt.PO_HEADER_ID = poh.PO_HEADER_ID
           AND mmt.RCV_TRANSACTION_ID = rt.transaction_id
GROUP BY   rsh.receipt_num,
           poh.segment1,
           rt.transaction_type,
           mmt.LOCATOR_ID,
           mmt.subinventory_code

TO RESET PASSWORD FOR THE USER IN ORACLE APPLICATIONS

/* Formatted on 2012/07/14 12:43 (Formatter Plus v4.8.8) */
DECLARE
   v_user_name      VARCHAR2 (30);
   v_new_password   VARCHAR2 (30) := 'welcome1';
   v_status         BOOLEAN;

   CURSOR c1
   IS
      SELECT user_name
        FROM fnd_user
       WHERE user_name BETWEEN 'PMGNU021' AND 'PMGNU099';
BEGIN
   FOR i IN c1
   LOOP
      DBMS_OUTPUT.put_line ('enrtry');
      v_user_name := i.user_name;
      v_status :=
         fnd_user_pkg.changepassword (username         => v_user_name,
                                      newpassword      => v_new_password
                                     );
      v_user_name := NULL;

      IF v_status = TRUE
      THEN
         DBMS_OUTPUT.put_line
                          (   'The password reset successfully for the User:'
                           || i.user_name
                          );
         COMMIT;
      ELSE
         DBMS_OUTPUT.put_line (   'Unable to reset password due to'
                               || SQLCODE
                               || ' '
                               || SUBSTR (SQLERRM, 1, 100)
                              );
         ROLLBACK;
      END IF;
   END LOOP;
END;

CREATING THE INDEX

CREATE INDEX APPS.XXHMI_MTL_MAT_TRANS_N4 ON INV.MTL_MATERIAL_TRANSACTIONS
(transaction_type_id ,TRUNC (TRANSACTION_DATE), INVENTORY_ITEM_ID )
LOGGING
TABLESPACE APPS_TS_TX_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

QUERY FOR KILLING THE SESSION(TABLE_LOCK)

SELECT   c.object_name,
         b.sid,
         b.serial#,
         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

and object_name  like 'HMI%Inventory%'--_INV_MAS_PKG_T_EXC%'-- ='MTL_SYSTEM_ITEMS_INTERFACE';



OBJECT_NAME,               SID,SERIAL#,MACHINE

XXHMI_INVENTORY_MASTER_PKG_P2.main

 ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

ALTER SYSTEM KILL SESSION '2433,10806' IMMEDIATE;

ALTER SYSTEM KILL SESSION  '2433,10806' --'sid,serial#';


select inst_id,sid,serial# from gv$session where username='APPS';

--Solution:-

SELECT * FROM V$SESSION s
 WHERE
(
EXISTS (SELECT 1 FROM V$SESSION xxx WHERE s.USERNAME = xxx.USERNAME AND s.PROGRAM = xxx.PROGRAM AND s.SID <> xxx.SID AND s.SERIAL# <> xxx.SERIAL# )
)

alter system kill session '10806,2433' Immediate;


alter system kill session '130,620,@1';