Tuesday 20 December 2011

Customer Tables

   HZ_PARTIES
   HZ_CUST_ACCOUNTS
   HZ_PARTY_SITES
   HZ_CUST_ACCT_SITES_ALL
   HZ_CUST_SITE_USES_ALL
   HZ_LOCATIONS
   HZ_PARTY_RELATIONSHIPS
   HZ_ORGANIZATION_PROFILES
   HZ_CONTACT_POINTS
   HZ_CUST_ACCOUNT_ROLES
   HZ_PERSON_PROFILES
   HZ_ORG_CONTACTS

Monday 5 December 2011

Script to get all the Concurrent Request Details in a Particular Day

select    request_id,
            parent_request_id,
            fcpt.user_concurrent_program_name Request_Name,
            fcpt.user_concurrent_program_name program_name,
            DECODE(fcr.phase_code,
                            'C','Completed',
                            'I','Incactive',
                            'P','Pending',
                            'R','Running') phase,
            DECODE(fcr.status_code,
                             'D','Cancelled',
                             'U','Disabled',
                             'E','Error',
                             'M','No Manager',
                             'R','Normal',
                             'I','Normal',
                            'C','Normal',
                            'H','On Hold',
                            'W','Paused',
                            'B','Resuming',
                            'P','Scheduled',
                            'Q','Standby',
                            'S','Suspended',
                            'X','Terminated',
                            'T','Terminating',
                            'A','Waiting',
                            'Z','Waiting',
                            'G','Warning','N/A') status,
    round((fcr.actual_completion_date - fcr.actual_start_date),3) * 1440 as Run_Time,
    round(avg(round(to_number(actual_start_date - fcr.requested_start_date),3) * 1440),2) wait_time,
    fu.User_Name Requestor,
    fcr.argument_text parameters,
    to_char (fcr.requested_start_date, 'MM/DD HH24:mi:SS') requested_start,
    to_char(actual_start_date, 'MM/DD/YY HH24:mi:SS') ACT_START,
    to_char(actual_completion_date, 'MM/DD/YY HH24:mi:SS') ACT_COMP,
    fcr.completion_text
From apps.fnd_concurrent_requests fcr,
     apps.fnd_concurrent_programs fcp,
     apps.fnd_concurrent_programs_tl fcpt,
     apps.fnd_user fu
Where 1=1
  and fcr.concurrent_program_id = fcp.concurrent_program_id
  and fcp.concurrent_program_id = fcpt.concurrent_program_id
  and fcr.program_application_id = fcp.application_id
  and fcp.application_id = fcpt.application_id
  and fcr.requested_by = fu.user_id
  and fcpt.language = 'US'
  and fcr.actual_start_date like sysdate   
GROUP BY
    request_id,
    parent_request_id,
    fcpt.user_concurrent_program_name,
    fcr.requested_start_date,
    fu.User_Name,
    fcr.argument_text,
    fcr.actual_completion_date,
    fcr.actual_start_date,
    fcr.phase_code,
    fcr.status_code,
    fcr.resubmit_interval,
    fcr.completion_text,
    fcr.resubmit_interval,
    fcr.resubmit_interval_unit_code,
    fcr.description
Order by 1 desc;

Which FND_USER is locking that table

Which FND_USER is locking that table 
This is a quick note to share a SQL that will tell you the FND_USER.USER_NAME of the person that has locked a given table in Oracle APPS.

The column named "module" will tell you the name of the Form Function or the Concurrent Program Short name which has acquired a lock onto that table.


SELECT  c.owner,
                c.object_name,
                c.object_type,
                fu.user_name locking_fnd_user_name,
                fl.start_time locking_fnd_user_login_time,
                vs.module,
                vs.machine,
                vs.osuser,
                vlocked.oracle_username,
                vs.sid,
                vp.pid,
                vp.spid AS os_process,
                vs.serial#,
                vs.status,
                vs.saddr,
                vs.audsid,
                vs.process
FROM    fnd_logins fl,
               fnd_user   fu,
               v$locked_object vlocked,
               v$process       vp,
               v$session       vs,
               dba_objects     c
WHERE  vs.sid = vlocked.session_id
AND       vlocked.object_id = c.object_id
AND       vs.paddr = vp.addr
AND       vp.spid = fl.process_spid(+)
AND       vp.pid = fl.pid(+)
AND       fl.user_id = fu.user_id(+)
AND       c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND       nvl(vs.status,'XX') != 'KILLED';

CONCURRENT PROCESSING IN ORACLE APPS.

Definitions
What is a Concurrent Program ?

            An instance of an execution file, along with parameter definitions and incompatibilities. Several concurrent programs may use the same execution file to perform their specific tasks, each having different parameter defaults and incompatibilites.

What is a Concurrent Program Executable ?

            An executable file that performs a specific task. The file may be a program written in a standard language, a reporting tool or an operating system language.

What is a Concurrent Request ?

             request to run a concurrent program as a concurrent process.

What is a Concurrent Process ?

            n instance of a running concurrent program that runs simultaneously with other concurrent processes.

What is a Concurrent Manager ?

             program that processes user’s requests and runs concurrent programs. System Administrators define concurrent managers to run different kinds of requests.

What is a Concurrent Queue ?

            ist of concurrent requests awaiting processing by a concurrent manager.

What is a Spawned Concurrent program ?

             Concurrent program that runs in a separate process than that of the concurrent manager that starts it. L/SQL stored procedures run in the same process as the concurrent manager; use them when spawned concurrent programs are not feasible.



LIFE CYCLE OF CONCURRENT REQUESTS
           
What are the phases and statuses through which a concurrent prequest runs through?

A concurrent request proceeds through three, possibly four, life cycle stages or phases: 

Pending                                                Request is waiting to be run
Running                                                Request is running
Completed                                            Request has finished
Inactive                                                Request cannot be run
            Within each phase, a request's condition or status may change.  Below appears a listing of each phase and the various states that a concurrent request can go through. 

Concurrent Request Phase and Status   

Phase                         Status           Description
PENDING                  Normal          Request is waiting for the next available manager.
                                   Standby          Program to run request is incompatible with other program(s)
                                                         currently running.
                                   Scheduled      Request is scheduled to start at a future time or date.
                                   Waiting          A child request is waiting for its Parent request to mark it ready
                                                        to run.a report in a report set that runs sequentially must wait for
                                                        a prior report to complete.
RUNNING                Normal          Request is running normally.
                                  Paused           Parent request pauses for all its child requests to complete. For 
                                                        example, a report set pauses for all reports in the set to complete.
                                  Resuming        All requests submitted by the same parent request have completed 
                                                         running. The Parent request is waiting to be restarted.
                                  Terminating      Running request is terminated, by selecting Terminate in the Status 
                                                         field  of the Request Details Zone. 

COMPLETED           Normal           Request completes normally.
                                  Error               Request failed to complete successfully.
                                  Warning           Request completes with warnings.  For example, a report is generated 
                                                         successfully but fails to print.
                                 Cancelled         Pending or Inactive request is cancelled, by selecting Cancel in the
                                                         Status
                                 Terminated       Running request is terminated, by selecting Terminate in  the Status 
                                                        field  of the Request Details zone.

INACTIVE               Disabled          Program to run request is not enabled. Contact your system
                                                        administrator.
                                  On Hold         Pending request is placed on hold, by selecting Hold in the Status field
                                                        of the Request Details zone.
                                   No Manager  No manager is defined to run the request.  Check with your system
                                                         administrator.
                                                    

What is the difference between Request group and request set ?

REQUESTS GROUPS AND REQUEST SETS

            Reports and concurrent programs can be assembled into request groups and request sets.

1.      A request group is a collection of reports or concurrent programs. A System Administrator defines report groups in order to control user access to reports and concurrent programs.  Only a System Administrator can create a request group.

2.      Request sets define run and print options, and possibly, parameter values, for a collection of reports or concurrent program.  End users and System Administrators can define request sets.  A System Administrator has request set privileges beyond those of an end user. 

            Standard Request Submission and Request Groups

            Standard Request Submission is an Oracle Applications feature that allows you to select and run all your reports and other concurrent programs from a single, standard form.  The standard submission form is called Submit Requests, although it can be customized to display a different title. 

3.      The reports and concurrent programs that may be selected from the Submit Requests form belong to a request security group, which is a request group assigned to a responsibility. 

4.      The reports and concurrent programs that may be selected from a customized Submit Requests form belong to a request group that uses a code. 

            In summary, request groups can be used to control access to reports and concurrent programs in two ways; according to a user's responsibility, or according to a customized standard submission (Run Requests) form.

APPS TABLES(PO,APS,GL,OM,INV,AR)

Purchase Order(PO):
---------------------
1.po_requisition_headers_all   -- requisition header info
2.po_requisition_lines_all        -- Requisition Lines info
3.po_req_distributions_all      -- Requisition Distribution info
4.po_headers_all                   -- PO Header Info
5.po_lines_all                         -- PO Line Info
6.po_line_locations_all           -- PO Line Shipment info
7.po_distributions_all             -- PO Distribution info
8.rcv_shipment_headers         -- Receiving header info
9.rcv_shipment_lines              -- Receiving Lines info
10.rcv_transactions                -- Receiving transationd info
11.po_vendors                      -- Supplier Header info
12.po_vendor_sites_all          -- Supplier Site info
13.po_vendor_site_contacts  -- Supplier Site Contact info
14.hr_locations                      -- Supplier Site Address

Order Management (OM):
---------------------------
1.oe_order_headers_all             -- Order Header info
2.oe_order_lines_all                  -- Order line info
3.oe_transaction_types_tl          -- Order type info
4.oe_order_holds                      -- Order Hold info
4.oe_holds_all                           -- Order Hold info
5.oe_hold_sources                    -- Order Hold source info
6.oe_hold_releases                    -- Hold Release info
7.wsh_delivery_details               -- Delivery Detial Info
8.wsh_new_deliveries                -- Delivery Header info
9.wsh_delivery_Assignments      -- Delivery Assignments info
10.wsh_trip_stops                      -- Delivery trips info
11.hz_cust_accounts                  -- Customer info
12.hz_parties                              -- Party info
13.hz_cust_site_uses_all             -- Customer site use info
14.hz_cust_acct_sites_all           -- Customer Site Acct info
15.hz_party_sites                       -- Party site info
16.hz_locations                          -- Customer Site Adderess
17.wsh_lookups                        -- Shipping lookup info


Accounts Payable(AP):
-----------------------
1.ap_invoices_all                               -- Invoice Header info
2.ap_invoice_distributions_all            -- Invoice Line info
3.ap_checks_all                                -- Check info
4.ap_invoice_payments_all                -- Invoice Payment info
5.ap_payment_schedules_all             -- Payment Schedule info
6.ap_holds_all                                  -- Invoice Holds info
7.ap_lookup_codes                          -- Payable lookup info
8.po_vendors                                   -- Supplier Header info
9.po_vendor_sites_all                       -- Supplier Site info
10.po_vendor_site_contacts             -- Supplier Site Contact info
11.hr_locations                                -- Supplier Site Address
12.ap_banks                                   -- Bank Info
13.ap_bank_branches                     -- Bank Branch info
14.ap_ae_headers_all                     -- Accounitng header info
15.ap_ae_lines_all                          -- Accounting Lines info
16.ap_ae_accounting_evets            -- Accounting events info
17.ap_terms                                   -- Payment Terms

Accounts Receivables(AR):
---------------------------
1.ra_customer_trx_all                   -- Receivable transaction info
2.ra_customer_trx_lines_all          -- Transaction lines info
3.ra_cust_trx_line_gl_dist_all       -- Transaction distribution info
4.ar_receivable_applications_all   -- Receiving application info
5.ar_cash_Receipts_all                -- Cash Receipt info
6.ar_terms                                   -- Receivable Terms
7.hz_cust_accounts                     -- Customer info
8.hz_parties                                -- Party info
9.hz_cust_site_uses_all               -- Customer site use info
10.hz_cust_acct_sites_all            -- Customer Site Acct info
11.hz_party_sites                        -- Party site info
12.hz_locations                   -- Customer Site Adderess

InvenvtoryModule(INV):
-------------------------
1.mtl_System_items_b              -- Master item info
2.mtl_onhand_quanitties            -- Item onhand qty info
3.mtl_reservations                     -- Item Reservation info
4.mtl_material_transactions       -- Item Transaction info
5.mtl_item_locations                 -- Item location info
6,mtl_Categeries                      -- Item Category info
7.mtl_item_categories              -- Invemtry Categry
8.mtl_secondary_inventories    -- Subinventories info
9.org_organization_definitions  -- Organizaition info
10.mtl_transaction_Accounts   -- Item Transaction info
11.mtl_txn_source_types         -- Item Transaction sources
12.mtl_parameters                  -- Inventory Parameters

General Ledger(GL) :
--------------------

1.gl_je_headers               -- Journal Header info
2.gl_je_lines                    -- Journal Line info
3.gl_je_batches               -- Journal Batch info
4.gl_Sets_of_books        -- set of books info
5.gl_chart_of_accoutns   -- chart of accounts info
6.gl_code_combinations  -- Code combination info
7.gl_je_sources               -- Journal Source info
8.gl_je_categories           -- Journal Cateogiy info
9.fnd_currencies             -- Currency info
10.gl_balances               -- Journal Balances