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';

QUERY TO FIND THE INVOICE AGAINTS GL ACCOUNT POSTING

/* Formatted on 2013/03/18 10:34 (Formatter Plus v4.8.8) */
SELECT gjb.NAME, gjh.je_source, gjh.period_name, gjl.je_line_num,
       xal.accounted_dr, xal.accounted_cr, supp.vendor_name, inv.invoice_num,
       pla.lease_num, pla.NAME lease_name,
       TO_CHAR (plda.lease_commencement_date,
                'DD-MON-YYYY'
               ) lease_commencement_date,
       TO_CHAR (plda.lease_termination_date, 'DD-MON-YYYY') lease_expiry_date
  FROM gl_import_references gir,
       gl.gl_je_batches gjb,
       gl.gl_je_headers gjh,
       gl.gl_je_lines gjl,
       xla.xla_ae_lines xal,
       xla.xla_ae_headers xah,
       xla.xla_events xe,
       xla.xla_transaction_entities xte,
       gl.gl_code_combinations gcc,
       pn.pn_payment_items_all ppia,
       pn.pn_payment_terms_all ppta,
       pn.pn_leases_all pla,
       ap_invoices_all inv,
       ap_suppliers supp,
       po_vendor_sites_all pvsa,
       pn_lease_details_all plda
 WHERE 1 = 1
   AND gjh.je_source = 'Payables'
   AND gjb.status = 'P'
   AND gjb.je_batch_id = gjh.je_batch_id
   AND gjh.je_header_id = gjl.je_header_id
   AND gjh.ledger_id = gjl.ledger_id
   AND gir.je_batch_id = gjb.je_batch_id
   AND xal.gl_sl_link_id = gir.gl_sl_link_id
   AND xal.gl_sl_link_table = gir.gl_sl_link_table
   AND gcc.code_combination_id = xal.code_combination_id
   AND gjl.code_combination_id = xal.code_combination_id
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.application_id = xah.application_id
   AND xah.event_id = xe.event_id
   AND xah.application_id = xe.application_id
   AND xe.entity_id = xte.entity_id
   AND xah.entity_id = xte.entity_id
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gjb.chart_of_accounts_id = gcc.chart_of_accounts_id
   AND ppia.ap_invoice_num(+) = xte.transaction_number
   AND ppia.payment_term_id = ppta.payment_term_id(+)
   AND ppta.lease_id = pla.lease_id(+)
   AND xte.source_id_int_1 = inv.invoice_id(+)
   AND inv.vendor_id = supp.vendor_id(+)
   AND pla.lease_id = plda.lease_id(+)
   AND pvsa.vendor_site_id(+) = ppta.vendor_site_id
   AND inv.invoice_id = 617917

TO FIND THE RESPONSIBILITY IN ORACLE APPLICATIONS

/* Formatted on 04-Jan-12 5:24:10 PM (QP5 v5.115.810.9015) */
select * from FND_RESPONSIBILITY_VL       where request_group_id in  (SELECT   request_group_id
  FROM   fnd_request_groups
 WHERE   request_group_id IN
               (SELECT   request_group_id
                  FROM   fnd_request_group_units
                 WHERE   request_unit_id =
                            (SELECT   concurrent_program_id
                               FROM   fnd_concurrent_programs_tl
                              WHERE   user_concurrent_program_name =
                                         'HMI Binning Pendency Report New')))
                                        
                                        
                                         
select * from FND_RESPONSIBILITY_TL       where application_id in(401,20003,385)    

select * from FND_RESPONSIBILITY_VL       where request_group_id in (1481 ,1370,1398)              

select * from all_objects where object_name like 'FND%RESPONS%'

TO FIND THE HOURS MIN AND SECOND IN ORACLE APPLICATIONS

/* Formatted on 2013/03/18 10:29 (Formatter Plus v4.8.8) */
SELECT    TO_CHAR (TRUNC (  (  (actual_completion_date - actual_start_date)
                             * 24
                             * 60
                             * 60
                            )
                          / 60
                          / 60
                         ),
                   '09'
                  )
       || 'hrs'
       || TO_CHAR (TRUNC (  MOD ((  (actual_completion_date
                                     - actual_start_date
                                    )
                                  * 24
                                  * 60
                                  * 60
                                 ),
                                 3600
                                )
                          / 60
                         ),
                   '09'
                  )
       || 'mins'
       || TO_CHAR (MOD (MOD ((  (actual_completion_date - actual_start_date)
                              * 24
                              * 60
                              * 60
                             ),
                             3600
                            ),
                        60
                       ),
                   '09'
                  )
       || 'secs' difference
  FROM fnd_concurrent_requests