Interfaces and Conversions in Oracle Applications

Note : For More Information About This Article Please Download the Below Link

http://www.ziddu.com/download/15527264/Interfaces_and_Conversions_in_Oracle_Applications.pdf.html

Thanks,
Raju

How To Determine If A Package/Procedure/Function Currently Is In Use

Subject: How To Determine If A Package/Procedure/Function Currently Is In Use

goal: How to determine if a package or stored procedure/function currently is in use
goal: How to identify the user who is currently executing a specific
package or stored procedure/function
fact: Oracle Server - Enterprise Edition
fact: Oracle Server - Standard Edition

fix:
Prior to recompiling a package or stored procedure/function, or to modifying
underlying objects, it can be useful to find out if the object is currently
being executed.

The following query is an example of how to obtain this information. Run the
query in SQL*Plus connected as a user with DBA privileges.

&OBJECT_NAME is to be replaced by the name of the package or stored
procedure/function.

-------------------------------------------

COLUMN TO_NAME FORMAT A14 heading "Object name"
COLUMN USERNAME FORMAT A14 heading "User running"
COLUMN TO_OWNER FORMAT A14 heading "Object owner"

SELECT DISTINCT
o.to_name,
v.username,
o.to_owner,
s.users_executing "Number"
FROM v$object_dependency o,
v$sql s,
v$session v
WHERE o.to_name='&OBJECT_NAME'
AND o.from_address=s.address
AND o.from_hash=s.hash_value
AND o.from_address=v.sql_address
AND o.from_hash=v.sql_hash_value;

RELEASE 12 UPGRADE

Functional Upgrade Impacts Document for Oracle Payments (FINANCIALS)

Note : For More Information please download the below link

http://www.ziddu.com/download/15527101/RELEASE12UPGRADE.pdf.html

Thanks,
Raju

Oracle Alerts - For the beginners only

Contents

• What is Oracle Alert?
• What are the difference types of Alert?
• How to set up an Alert?
(Different components of Alert)
• How Alert Works?
• Limitations of Oracle Alert?

What is Oracle Alert?
• Complete exception control solution
• Easy electronic emailing
• Execution of process
• Schedule in regular interval
• Replacement of Database Trigger
• Take predefined actions
• Take actions depends on user response.

Alerts
• Periodic Alert • Event Alert
– Variable frequency – All possible events
• Customized Alert Frequency
• Customized Alert Actions
(Details / Summary action, No exception, History, Duplicate Checking
• Action Escalation
• Response Processing

Creating a Periodic Alert
• Set Frequency (On Demand, On day of …)
• Set time / interval
• Enter / import select statement
• Verify / Run select statement

Note : For More Information please download the below link

http://www.ziddu.com/download/15525611/OracleAlerts2.pdf.html

Thanks,
Raju

Oradcle Alerts

Oracle Alerts

Contents

Chapter 1 : Overview of Oracle Alerts

Chapter 2 : Defining Alerts

Chapter 3 : Type of Alerts

Chapter 4 : Check the Alerts

OverView of Oracle Alerts:

• Keep you informed of critical activity in your database

• Deliver key information from your applications, in the format you choose

• Provide you with regular reports on your database information

• Automate system maintenance, and routine online tasks

Overview:

Oracle Alert is your complete exception control solution.

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want. You can define one of two types of alerts: an event alert or a periodic alert.

An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following:

• A database event that you want to monitor, that is, an insert and/or an update to a specific database table.

• A SQL Select statement that retrieves specific database information as a result of the database event.

• Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

A periodic alert, on the other hand, checks the database for information according to a schedule you define. When you create a periodic alert, you specify the following:

• A SQL Select statement that retrieves specific database information.

• The frequency that you want the periodic alert to run the SQL statement.

• Actions that you want Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a

• SQL statement script.
  

Note : For More Information please download the below link

http://www.ziddu.com/download/15525590/OracleAlerts1.pdf.html

Thanks,
Raju

Single Query For P To P Cycle

SELECT a.org_id "ORG ID", e.vendor_name "VENDOR NAME",

       UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",

       f.vendor_site_code "VENDOR SITE", f.address_line1 "ADDRESS",

       f.city "CITY", f.country "COUNTRY",

       TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUMBER",

       d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",

       c.quantity_cancelled "QTY CANCALLED",

       g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",

         (NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)

         )

       * NVL (g.unit_price, 0) "PO Line Amount",

       (SELECT DECODE (ph.approved_flag, 'Y', 'Approved')

          FROM po.po_headers_all ph

         WHERE ph.po_header_id = d.po_header_id) "PO STATUS",

       a.invoice_type_lookup_code "INVOICE TYPE",

       a.invoice_amount "INVOICE AMOUNT",

       TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",

       a.invoice_num "INVOICE NUMBER",

       (SELECT DECODE (x.match_status_flag,

                       'A', 'Approved'

                      )

          FROM ap.ap_invoice_distributions_all x

         WHERE x.invoice_distribution_id = b.invoice_distribution_id)

                                                          "Invoice Approved?",

       a.amount_paid, h.amount, i.check_number "CHEQUE NUMBER",

       TO_CHAR (TRUNC (i.check_date)) "PAYMENT DATE"

  FROM ap.ap_invoices_all a,

       ap.ap_invoice_distributions_all b,

       po.po_distributions_all c,

       po.po_headers_all d,

       po.po_vendors e,

       po.po_vendor_sites_all f,

       po.po_lines_all g,

       ap.ap_invoice_payments_all h,

       ap.ap_checks_all i

 WHERE a.invoice_id = b.invoice_id

   AND b.po_distribution_id = c.po_distribution_id(+)

   AND c.po_header_id = d.po_header_id(+)

   AND e.vendor_id(+) = d.vendor_id

   AND f.vendor_site_id(+) = d.vendor_site_id

   AND d.po_header_id = g.po_header_id

   AND c.po_line_id = g.po_line_id

   AND a.invoice_id = h.invoice_id

   AND h.check_id = i.check_id

   AND f.vendor_site_id = i.vendor_site_id

   AND c.po_header_id IS NOT NULL

   AND a.payment_status_flag = 'Y'

   AND d.type_lookup_code != 'BLANKET';

Which User is Locking Table Query

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 ('&tabname_blank4all') || '%'
AND NVL (vs.status, 'XX') != 'KILLED';

To Get Concurrent Program Prameters Query

SELECT
fcpl.user_concurrent_program_name,
fcp.concurrent_program_name,
par.end_user_column_name,
par.form_left_prompt prompt,
par.enabled_flag,
par.required_flag,
par.display_flag

FROM fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl par

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;

Submitting Job Query

DECLARE
l_job number;
BEGIN
DBMS_JOB.SUBMIT(l_job,'DBMS_MVIEW.REFRESH(''VIEW_LICENSE_TS_MAIN_IB_NEW_MV'');');
DBMS_OUTPUT.PUT_LINE('JOB : '||l_job);
COMMIT;
END;

=========================

SELECT * FROM USER_JOBS WHERE JOB= 63362

=====================
Job locks
====================
SELECT SID, TYPE, ID1, ID2
FROM V$LOCK
WHERE TYPE = 'JQ';

Setting Org ID for the Current Session Query From Backend

BEGIN
dbms_application_info.set_client_info(539);
END;

begin
fnd_client_info.set_org_context('543');
end;

Purchase Requisition to Purchase Order Query

SELECT DISTINCT u.description "Requestor", porh.segment1 AS "Req Number",

                TRUNC (porh.creation_date) "Created On", pord.last_updated_by,

                porh.authorization_status "Status",

                porh.description "Description", poh.segment1 "PO Number",

                TRUNC (poh.creation_date) "PO Creation Date",

                poh.authorization_status "PO Status",

                TRUNC (poh.approved_date) "Approved Date"

           FROM apps.po_headers_all poh,

                apps.po_distributions_all pod,

                apps.po_req_distributions_all pord,

                apps.po_requisition_lines_all porl,

                apps.po_requisition_headers_all porh,

                apps.fnd_user u

          WHERE porh.requisition_header_id = porl.requisition_header_id

            AND porl.requisition_line_id = pord.requisition_line_id

            AND pord.distribution_id = pod.req_distribution_id(+)

            AND pod.po_header_id = poh.po_header_id(+)

            AND porh.created_by = u.user_id

            AND poh.segment1 = 'I-41174'

       ORDER BY 2