Fnd_log.STRING Usage

Fnd_Log.STRING(log_level => Fnd_Log.LEVEL_STATEMENT,MODULE =>xxt.apps.custompackage name
                  ,message => 'your message');

FND: Debug Log Level   Codes

   LEVEL_UNEXPECTED     :  6
   LEVEL_ERROR          :  5
   LEVEL_EXCEPTION      :  4
   LEVEL_EVENT          :  3
   LEVEL_PROCEDURE      :  2
   LEVEL_STATEMENT      : 1
--------------------------------------------

FND: Debug Log Enabled : Yes

FND: Debug Log Module    Set this to %


Step 2
Login to the application and reproduce the problem.

Step 3
SELECT *
FROM fnd_log_messages
WHERE user_id = <id>
ORDER BY log_sequence DESC 




Why to set the profile option to statement level?
This profile option has following main levels.-
Error
Warning
Procedure
Statement

select * from fnd_log_messages where user_id = <id>and LOG_LEVEL =<log level>   



To purge data in production:

You can run concurrent program “Purge Debug Log and System Alerts”.


I have written a pl/sql concurrent process to interface Purchase Orders from 3rd Party System. How will add debug messages?
    fnd_log.STRING(log_level => fnd_log.level_statement
                  ,module    => '<module >'
                  ,message   => '<message >');

 Note:

fnd_log.string eventually calls procedure  FND_LOG.STRING_UNCHECKED_INTERNAL2. This procedure uses pragma AUTONOMOUS_TRANSACTION with a commit.

How to check send_email_flag for customer contact?

SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID,ROLE_ACCT.cust_account_id,party.party_type,party.party_name Contact_Name,
rel.relationship_code,rel.relationship_id, ACCT_ROLE.attribute10 send_email_flag,
party.party_id,rel_party.party_id,rel.subject_id,rel.object_id, rel.party_id,
SUBSTRB(PARTY.PERSON_FIRST_NAME,1,40) FIRST_NAME ,
SUBSTRB(PARTY.PERSON_LAST_NAME,1,50) LAST_NAME ,
PARTY.CUSTOMER_KEY CONTACT_KEY ,
REL_PARTY.EMAIL_ADDRESS ,
PARTY.PARTY_ID ,
ORG_CONT.ORG_CONTACT_ID ,
CONT_POINT.CONTACT_POINT_ID ,
ORG_CONT.CONTACT_NUMBER
FROM HZ_CONTACT_POINTS CONT_POINT,
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_PARTIES REL_PARTY,
HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT ,
HZ_CUST_ACCOUNTS ROLE_ACCT
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL_PARTY.PARTY_ID = REL.PARTY_ID
AND CONT_POINT.OWNER_TABLE_ID(+) = REL_PARTY.PARTY_ID
AND CONT_POINT.PRIMARY_FLAG = 'Y'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ROLE_ACCT.CUST_ACCOUNT_ID
AND ROLE_ACCT.PARTY_ID = REL.OBJECT_ID
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
and ROLE_ACCT.cust_account_id = &Enter_Account_id
and rel.object_id = &<PARTY_ID>
AND rel.subject_table_name ='HZ_PARTIES'
AND rel.object_table_name ='HZ_PARTIES'
and party.status ='A'
and rel_party.status ='A'
and rel.status ='A'
and party.party_name = '<CONTACT_NAME>'
and CONT_POINT.CREATED_BY_MODULE = 'USER REGISTRATION';

Delete pull sequence and Kanban cards --- usefull scripts

--
PROCEDURE Delete_Pull_Sequence
(x_return_status  Out NOCOPY Varchar2,
 p_kanban_plan_id     Number,
 pull_sequence_id   NUMBER)

IS

Cursor Get_Pull_Sequences IS
Select pull_sequence_id
From mtl_kanban_pull_sequences
Where kanban_plan_id = p_kanban_plan_id;

l_return_status      Varchar2(1) := FND_API.G_RET_STS_SUCCESS;

    l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
Begin
      If p_kanban_plan_id = INV_kanban_PVT.G_Current_Plan
        then
                For pull_sequences in get_pull_sequences
                loop
                        If Ok_To_Delete_Pull_Sequence(pull_sequences.pull_sequence_id)
                        then
                                INV_PullSequence_Pkg.delete_Row(l_return_status,pull_sequences.pull_sequence_id);
                        Else
                                Raise FND_API.G_EXC_ERROR;
                        end if;
                end loop;
        Else
                Delete from Mtl_kanban_pull_sequences
                Where kanban_plan_id = p_kanban_plan_id;
        end if;
        x_return_status := l_return_status;

Exception

    WHEN FND_API.G_EXC_ERROR THEN

       x_return_status := FND_API.G_RET_STS_ERROR;

    WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN

       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

    WHEN OTHERS THEN

        IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
        THEN
            FND_MSG_PUB.Add_Exc_Msg
            (   G_PKG_NAME
            ,   'Validate_Pull_Sequence'
            );
        END IF;
       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

End Delete_Pull_Sequence;

Calling Procedure:

INV_PullSequence_PKG.Delete_Row(x_return_status,uas_upd_rec.pull_sequence_id);

WF_MAILER not sending emails to users - Troubleshooting

Check user preference Email Notification style. 

SELECT EMAIL_ADDRESS, NVL(apps.WF_PREF.GET_PREF(NAME, 'MAILTYPE'),NOTIFICATION_PREFERENCE)
FROM apps.WF_ROLES
WHERE name = upper('<ROLE>');

Notification not sent waiting to be mailed ?

SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS

where status = ‘OPEN’ and mail_status = ‘MAIL’;

To debug the notification id ?

$FND_TOP/sql

run wfmlrdbg.sql

If Workflow mailer is down?

SELECT COMPONENT_NAME, COMPONENT_STATUS
FROM apps.FND_SVC_COMPONENTS
WHERE component_type = 'WF_MAILER';

Notification deffered?

SELECT * FROM APPS.WF_NOTIFICATIONS WHERE SUBJECT LIKE '%Parts%Online%Order%Entered%'
AND  MAIL_STATUS = 'SENT'
--AND TO_USER = 'ISTORE_DAN'
and recipient_role = 'ISTORE_DAN'
order by BEGIN_DATE desc;

select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= '<notif id>;

select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
WF.USER_DATA.ERROR_STACK ERROR_STACK, WF.USER_DATA.ERROR_MESSAGE ERROR_MSG
from apps.wf_error wf where wf.user_data.event_key = '<notif id>';

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

1. Workflow: version

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

$FND_TOP/sql/wfver.sql

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

2. check workflow status.

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

select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS,fcq.last_update_date

from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc

where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)

order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

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

3. check if workflow is used by only one instance

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

select p.parameter_id,

p.parameter_name,

v.parameter_value value

from apps.fnd_svc_comp_param_vals_v v,

apps.fnd_svc_comp_params_b p,

apps.fnd_svc_components c

where c.component_type = 'WF_MAILER'

and v.component_id = c.component_id

and v.parameter_id = p.parameter_id

and p.parameter_name in ( 'INBOUND_SERVER','ACCOUNT', 'REPLYTO')

order by p.parameter_name;

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

4. check if processor_read_timeout_close is set to 'Y'

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

set pagesize 100

set linesize 132

set feedback off

set verify off

col value format a35

col component_name format a30 

select c.component_id, c.component_name, p.parameter_id, p.parameter_name, v.parameter_value value

from apps.fnd_svc_comp_param_vals_v v, apps.fnd_svc_comp_params_b p, apps.fnd_svc_components c

where c.component_type = 'WF_MAILER'

and v.component_id = c.component_id

and v.parameter_id = p.parameter_id

and p.parameter_name = 'PROCESSOR_READ_TIMEOUT_CLOSE' 

order by c.component_name,p.parameter_name; 

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

5. check for bad e-mail address

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

If below SQL statement is returning rows you need to correct the email addresses for associated users:

set linesize 170

col name format a40 

col email_address format a80 

select name, email_address from apps.wf_local_roles where email_address like '% %';

select name, email_address from apps.wf_local_roles where email_address like '%%';

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

6. How to know mail sent to a user with details:

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

select name, display_name, notification_preference, email_address from wf_local_roles where name = '';

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

7. How to know whether it is set to correct url from porfile options:

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

set linesize 155;

set pagesize 200;

set verify off;

col Profile format a50;

col Value format a50;

select t.PROFILE_OPTION_ID ID, z.USER_PROFILE_OPTION_NAME Profile,

nvl(v.PROFILE_OPTION_VALUE,'Replace with non-virtual URL') Value

from apps.fnd_profile_options t, apps.fnd_profile_option_values v, apps.fnd_profile_options_tl z

where (v.PROFILE_OPTION_ID (+) = t.PROFILE_OPTION_ID)

and (z.PROFILE_OPTION_NAME = t.PROFILE_OPTION_NAME)

and (t.PROFILE_OPTION_NAME in ('WF_MAIL_WEB_AGENT'));

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

8. How to know reqid, process id, sid..

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

select request_id,phase_code,status_code,hold_flag from fnd_concurrent_requests where REQUEST_ID=20422815;

select ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from fnd_concurrent_requests where REQUEST_ID=20422815;

select a.session_id,

b.owner,

b.object_type,

b.object_name,

a.oracle_username,

a.os_user_name,

a.process,

a.locked_mode

from v$locked_object a, dba_objects b

where b.object_id = a.object_id

and a.session_id='3383';

select name, display_name, notification_preference, email_address from wf_local_roles where name = 'SYSADMIN';

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

9. Workflow: To see failed, open notifications

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

SELECT message_type, COUNT(1)

FROM apps.wf_notifications

WHERE 1 = 1 AND mail_status = 'FAILED' AND status = 'OPEN'

GROUP BY message_type;

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

10. To check if email address, notification preference, display_name

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

select DISPLAY_NAME, NOTIFICATION_PREFERENCE from wf_users where EMAIL_ADDRESS = 'h@h.com' and STATUS = 'ACTIVE' and DISPLAY_NAME = 'xxxxx';

select DISPLAY_NAME, NOTIFICATION_PREFERENCE, EMAIL_ADDRESS from wf_users where NOTIFICATION_PREFERENCE='MAILTEXT' and STATUS = 'ACTIVE';

select distinct(count(*)) from wf_users where NOTIFICATION_PREFERENCE='MAILTEXT' and STATUS = 'ACTIVE' and EMAIL_ADDRESS = 'mmmmm@yahoo.com';

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

11. How to know workflow responsibility from backend:

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

select wes.status, wes.phase, wes.rule_function, wes.on_error_code from wf_events we, wf_event_subscriptions wes 

where we.name='oracle.apps.fnd.wf.ds.userRole.updated' and we.guid=wes.event_filter_guid;

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

12. Steps to drop and recreate WF_CONTROL queue:

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

a. Shut down the concurrent managers.

b. Connect to sqlplus session as APPS user:

Execute: (For Workflow Embedded within Apps)

SQL>exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'APPLSYS.WF_CONTROL', force =>TRUE);

SQL> commit;

Execute: (For Standalone Workflow)

sqlplus / @wfctlqec.sql 

c. Execute wfjmsqc2.sql for creating all Advanced Queues with JMS Text structures.

Usage:

sqlplus / @wfjmsqc2.sql 

Both of the script are under $FND_TOP/patch/115/sql or $FND_TOP/sql directories.

d.Run afwfqgnt.sql to recreate grants/synonyms for Workflow Advanced Queue tables

Usage:

sqlplus apps/apps @$FND_TOP/patch/115/sql/afwfqgnt.sql APPS APPLSYS 

Additional Information: For Rebuilding all other WF Queue refer Note 754468.1 Title: How to Rebuild Workflow Queues ,based on your application version.

Workflow Notifications Status 'FAILED' checking status

1. Check workflow mailer service current status

  sqlplus apps/<apps password>
  select running_processes
    from apps.fnd_concurrent_queues
   where concurrent_queue_name = 'WFMLRSVC';

  Number of running processes should be greater than 0

2. Find current mailer status

 
  sqlplus apps/<apps password>
  select component_status
    from apps.fnd_svc_components
   where component_id = 
        (select component_id
           from apps.fnd_svc_components
          where component_name = 'Workflow Notification Mailer');

  Possible values:
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM
 
  sqlplus apps/<apps password>
  declare
       p_retcode number;
       p_errbuf varchar2(100);
       m_mailerid fnd_svc_components.component_id%TYPE;
  begin
       -- Find mailer Id
       -----------------
       select component_id
         into m_mailerid
         from fnd_svc_components
        where component_name = 'Workflow Notification Mailer';
       --------------
       -- Start Mailer
       --------------
       fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
       commit;
  end;
  /

A workflow notification send event (notification email) can fail at several different points, so monitoring it using one method usually is not going to give you a complete picture.Additionally, you have to keep in mind that the process is dynamic, meaning that as transactions are created into the queues they are also mailed out; so a 
count of data is at best only a snapshot of a particular moment in time.

1. Here is a more robust script for monitoring the wf_notifications table:
select message_type, mail_status, count(*) from wf_notifications
where status = 'OPEN'
GROUP BY MESSAGE_TYPE, MAIL_STATUS
messages in 'FAILED' status can be resent using the concurrent request 'resend failed workflow notificaitons'
messages which are OPEN but where mail_status is null have a missing email address for the recipient, but the notification preference is 'send me mail'

2. Some messages like alerts don't get a record in wf_notifications table so you have to watch the WF_NOTIFICATION_OUT queue.

select corr_id, retry_count, msg_state, count(*)
from applsys.aq$wf_notification_out
group by corr_id, msg_state, retry_count
order by count(*) desc;
Messages with a high retry count have been cycling through the queue and are not passed to smtp service.Messages which are 'expired' can be rebuilt using the wfntfqup.sql

Orders created from the Spares Management Queries for alerts

Orders created from the Spares Management Queries

SELECT B.requirement_header_id,a.ORDER_LINE_ID,  a.ORDER_BY_DATE, a.* FROM CSP_REQUIREMENT_LINES A , CSP_REQUIREMENT_HEADERS B  WHERE
A.REQUIREMENT_HEADER_ID = B.REQUIREMENT_HEADER_ID
AND B.ORDER_TYPE_ID = <XXX>
order by   a.ORDER_BY_DATE desc;

select OH.ORIG_SYS_DOCUMENT_REF

       ,OH.ORDER_NUMBER

       ,OH.CREATION_DATE

       ,PPF.FULL_NAME

       ,FU.EMPLOYEE_ID

from OE_ORDER_HEADERS_ALL OH

     ,PO_REQUISITION_HEADERS_ALL POH

     ,FND_USER FU

     ,PER_ALL_PEOPLE_F PPF

     where 1                    =1

and OH.ORDER_TYPE_ID       =xxx0

and OH.SOURCE_DOCUMENT_ID  = POH.REQUISITION_HEADER_ID

and POH.TYPE_LOOKUP_CODE   = 'INTERNAL'

and POH.INTERFACE_SOURCE_CODE   = 'CSP'

and FU.USER_ID                  =OH.CREATED_BY

and FU.EMPLOYEE_ID              =PPF.PERSON_ID

and TRUNC(sysdate) between TRUNC(PPF.EFFECTIVE_START_DATE) and TRUNC(PPF.EFFECTIVE_END_DATE)

and TRUNC(OH.CREATION_DATE)     = TRUNC(sysdate)

inv_item_sub_default_pkg.insert_upd_item_sub_defaults Update Default ubinventory in Order Management tab : Item Update conversion

begin
 ip_trx_from_id   IN       NUMBER,
      ip_trx_to_id     IN       NUMBER

   IS
      --update assignment variables
      CURSOR cur_eur_upd
      IS
          SELECT   *
             FROM XX_EUR_ITEM_SHIP_SUBINV_STG
            where 1 = 1
              and STATUS_STG = 'V'
              and TRANSACTION_ID_STG between ip_trx_from_id and ip_trx_to_id
         ORDER BY transaction_id_stg;
   x_return_status   VARCHAR2 (100);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (2000);
   p_commit          VARCHAR2 (100)  DEFAULT fnd_api.g_false;
   l_msg_dummy       VARCHAR2 (2000);
   x_output          VARCHAR2 (2000);
   l_resp_id NUMBER;
   l_resp_appl_id NUMBER;
    l_user_id          NUMBER                         := xxx;
   BEGIN
      BEGIN
           select RESPONSIBILITY_ID, APPLICATION_ID
           INTO l_resp_id, l_resp_appl_id
           from FND_RESPONSIBILITY_TL
          WHERE responsibility_name  =  'Inventory';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
                 XX_asa.g_error_message :=
               'Entered into Exception when getting the responsibiliy id and applicationid';
                  END;
      fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
/*====================================================================
           updating  assignments DFF
 ==============================================================*/
      FOR item_rec IN cur_eur_upd
      LOOP
         BEGIN
         
            inv_item_sub_default_pkg.insert_upd_item_sub_defaults
                          (x_return_status          => x_return_status,
                           x_msg_count              => x_msg_count,
                           x_msg_data               => x_msg_data,
                           p_organization_id        => item_rec.ORGANIZATION_ID,
                           p_inventory_item_id      => item_rec.INVENTORY_ITEM_ID,
                           p_subinventory_code      => 'FG_xxbujh',
                           --shipping_subinv2,
                           p_default_type           => 1,
                           p_creation_date          => SYSDATE,
                           p_created_by             => xxx,
                           p_last_update_date       => SYSDATE,
                           p_last_updated_by        => xxxx,
                           p_process_code           => 'SYNC',
                           p_commit                 => p_commit
                          );
      IF x_msg_count > 0
      THEN
         FOR j IN 1 .. x_msg_count
         LOOP
            fnd_msg_pub.get (j, fnd_api.g_false, x_msg_data, l_msg_dummy);
            x_output := ('Msg' || TO_CHAR (j) || ': ' || x_msg_data);
            FND_FILE.put_line(FND_FILE.output,SUBSTR (x_output, 1, 255));
         END LOOP;
      END IF;
           
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               PRINT ('When Others Error - Update shipping subinventory in EUR' || SQLERRM);
                        END;

CUSTOM.pll

1. Take the back up of the CUSTOM.pll present in path "/djak/uat/app/apps/apps_st/appl/au/12.0.0/resource" to CUSTOM.pll08Oct2012

2. Port the attached CUSTOM.pll to above path


3. Compile the CUSTOM.pll from above path using below command(please provide apps database username/password)

frmcmp_batch module=CUSTOM.pll userid=apps/<pwd> output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

Query ORG_ID vs ORGANIZATION_ID

select FSP.INVENTORY_ORGANIZATION_ID,
       FSP.ORG_ID ,
       HOU.name   ,
       mp.organization_code
      
from FINANCIALS_SYSTEM_PARAMS_ALL  FSP,
     HR_OPERATING_UNITS HOU      ,
     mtl_parameters mp
where 1=1
and   FSP.ORG_ID                    =  HOU.ORGANIZATION_ID
and   FSP.INVENTORY_ORGANIZATION_ID =  MP.ORGANIZATION_ID(+);
and   mp.organization_code = 'XXX';

Query to retrieve active Alert information

SELECT aa.alert_name, aa.start_date_active, al.meaning "FREQUENCY_TYPE",
DECODE (frequency_type,
'B', days_between_checks,
'W', weekly_check_day,
'M', monthly_check_day_num,
'C', days_between_checks,
NULL
) "Days",
aav.to_recipients, aav.cc_recipients, aav.bcc_recipients, aav.subject,
aav.msg_body
FROM alr_actions_v aav, alr_alerts aa, alr_lookups al
WHERE 1 = 1
-- AND UPPER (aa.alert_name) LIKE UPPER ('%HR%')
AND aav.alert_id = aa.alert_id
AND aa.enabled_flag = 'Y'
AND aa.frequency_type = al.lookup_code

Oracle Alert History

SELECT
    alv . *
    FROM ALR_ALERTS al ,
    ALR_ACTION_HISTORY aah ,
    ALR_OUTPUT_HISTORY aoh ,
    ALR_ACTIONS_V alv ,
    alr_alert_historY_view aahv
    WHERE al . alert_name = <name of alert > --'XPEP_PENDING_APPROVALS'
    AND al . alert_id = aah . alert_id
    AND aah . check_id = aoh . check_id
    AND alv . ALERT_ID = aah . alert_id
    AND aahv . alert_name = al . alert_name;

Qury to find Related Items for an existing Item

declare
a varchar2(1000):= null;
b varchar2(1000):= null;
CURSOR c_cur IS
 select
ito.segment1 item,
--ito.description,
ITR.SEGMENT1 RELATED_ITEM,
--itr.description,
ML.MEANING RELATION
--ri.reciprocal_flag
from
mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
where ri.inventory_item_id=ito.inventory_item_id
and ri.organization_id=ito.organization_id
and ri.related_item_id=itr.inventory_item_id
and ri.organization_id=itr.organization_id
and ri.relationship_type_id=ml.lookup_code
AND ML.LOOKUP_TYPE(+)='MTL_RELATIONSHIP_TYPES'
AND ITO.SEGMENT1  ='XXXX';
begin
a:=  'Related Items exists for the Item '  ||'. Please enter Related Item from below list'|| CHR(10);
a := a||rpad('Related Item',19,' ')||rpad('Relation',20,' ')|| CHR(10)||rpad('*************',22,' ')||rpad('******************',23,' ')|| CHR(10);
FOR r_line IN c_cur
    LOOP
     b:=b||rPAD(R_LINE.RELATED_ITEM,25,' ')||rPAD(R_LINE.RELATION,20,' ')||CHR(10);
    end LOOP;
   dbms_output.put_line( a||b);
    EXCEPTION
when OTHERS then
dbms_output.put_line( 'No data found');
end;

Query to find User who is locking the table

SELECT objects.owner,
       objects.object_name,
       objects.object_type,
       user1.user_name         locking_fnd_user_name,
       login.start_time        locking_fnd_user_login_time,
       vs.module,
       vs.machine,
       vs.osuser,
       vlocked.oracle_username,
       vs.sid,
       vp.pid,
       vp.spid                 os_process,
       vs.serial#,
       vs.status,
       vs.saddr,
       vs.audsid,
       vs.process
  FROM fnd_logins      login,
       fnd_user        user1,
       v$locked_object vlocked,
       v$process       vp,
       v$session       vs,
       dba_objects     objects
 WHERE vs.sid = vlocked.session_id
   AND vlocked.object_id = objects.object_id
   AND vs.paddr = vp.addr
   AND vp.spid = login.process_spid(+)
   AND vp.pid = login.pid(+)
   AND login.user_id = user1.user_id(+)
--change the table name below

   AND objects.object_name LIKE '%' || upper('PO_HEADERS_ALL') || '%'
   AND nvl(vs.status,
           'XX') != 'KILLED';

Discoverer Universal Query

SELECT DISTINCT d.ba_name business_area
               ,c.obj_name folder_name
               ,decode(c.obj_type, 'SOBJ','From Database', 'CUO', 'Custom Query', 'COBJ', 'Complex Folder') obj_type_desc
               ,c.sobj_ext_table view_name
               ,disco_docs.doc_name "Discoverer Workbook",
                TRUNC (disco_docs.doc_created_date) "Workbook Create Date",
                CASE
                   WHEN INSTR
                          (disco_docs.doc_created_by,
                           '#'
                          ) = 0
                      THEN disco_docs.doc_created_by
                   WHEN INSTR (disco_docs.doc_created_by, '#') > 0
                   AND INSTR (disco_docs.doc_created_by, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                      SUBSTR (disco_docs.doc_created_by, 2, 5))
                   ELSE NULL
                END "Workbook Owner/Creator",
                disco_users.eu_username,
                CASE
                   WHEN INSTR
                          (disco_users.eu_username,
                           '#'
                          ) = 0
                      THEN disco_users.eu_username
                   WHEN INSTR (disco_users.eu_username, '#') > 0
                   AND INSTR (disco_users.eu_username, '#', 2) = 0
                      THEN (SELECT fu.user_name
                              FROM fnd_user fu
                             WHERE fu.user_id =
                                        SUBSTR (disco_users.eu_username, 2, 5))
                   ELSE (SELECT resp.responsibility_name
                           FROM fnd_responsibility_tl resp
                          WHERE resp.responsibility_id =
                                        SUBSTR (disco_users.eu_username, 2, 5))
                END AS "Shared Name / Responsibility"
              
              
              
           FROM biseul_us.eul5_documents disco_docs,
                biseul_us.eul5_access_privs disco_shares,
                biseul_us.eul5_eul_users disco_users,
                --
                biseul_us.eul5_qpp_stats  a,
                biseul_us.eul5_bas d,
                biseul_us.eul5_objs c,
                biseul_us.eul5_ba_obj_links f
               
                
          WHERE 1=1
            AND a.qs_doc_name= disco_docs.doc_name
            AND c.obj_id = SUBSTR (a.qs_object_use_key, 1, 6)
            AND c.obj_id = f.bol_obj_id
            AND d.ba_id = f.bol_ba_id
            AND disco_docs.doc_id = disco_shares.gd_doc_id
            AND disco_users.eu_username(+) NOT IN ('EUL5', 'PUBLIC')
            AND DISCO_USERS.EU_ID(+) = DISCO_SHARES.AP_EU_ID
            AND upper(disco_docs.doc_name) LIKE upper('% :Report sheet name here%');

Adding Concurrent Program to Request Group(Backend)

begin
FND_PROGRAM.ADD_TO_GROUP(PROGRAM_SHORT_NAME=>'HOLXORGNTNS',
program_application=>'Inventory',
request_group=>'All Inclusive GUI',
group_application=>'Inventory');
Commit;
Exception
when OTHERS then
DBMS_OUTPUT.PUT_LINE('Exception while adding'||SQLERRM);
End;
/

Delete Reservations based on Item , Organization Code and Sales Order number

CREATE OR REPLACE
PROCEDURE XXT_RelieveReservation(p_segment1 IN VARCHAR2, p_organization_code IN VARCHAR2, p_order_number IN VARCHAR2)
AS
        -- Common Declarations
        l_api_version   NUMBER      := 1.0;
        l_init_msg_list   VARCHAR2(2) := FND_API.G_TRUE;
        x_return_status   VARCHAR2(2);
        x_msg_count   NUMBER      := 0;
        x_msg_data               VARCHAR2(255);
        x_error_code             NUMBER      := 0;
   
        -- WHO columns
 l_user_id  NUMBER := -1;
 l_resp_id  NUMBER := -1;
 l_application_id NUMBER := -1;
        l_row_cnt  NUMBER := 1;
        l_user_name  VARCHAR2(30) := 'XXXUSER_NAME';
        l_resp_name  VARCHAR2(30) := 'XXXXesponsibility_key';  
       
        -- API specific declarations
        l_rsv_rec                   INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
        l_serial_number             INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
        l_primary_relieved_qty      NUMBER := 0;
        l_validation_flag           VARCHAR2(2) := FND_API.G_TRUE; 
        x_mtl_reservation_tbl       INV_RESERVATION_GLOBAL.MTL_RESERVATION_TBL_TYPE;
        x_mtl_reservation_tbl_count NUMBER := 0;
        x_primary_relieved_qty      NUMBER := 0;
        x_primary_remain_qty        NUMBER := 0;
        -- Get the reservation to be relieved       
        CURSOR c_item_reservations IS
        SELECT msi.organization_id, msi.inventory_item_id, msi.segment1, msi.primary_uom_code, res.reservation_id
        FROM mtl_system_items_b msi, mtl_parameters mp, mtl_reservations res, mtl_Sales_orders mso
        WHERE msi.segment1 = p_segment1
        AND msi.organization_id = mp.organization_id
        AND mp.organization_code = p_organization_code
        AND res.organization_id = msi.organization_id
        AND res.inventory_item_id = msi.inventory_item_id
  AND res.demand_source_header_id=mso.sales_order_id
        AND mso.segment1              =p_order_number ;
       
       
BEGIN

 -- Get the user_id
 SELECT user_id
 INTO l_user_id
 FROM fnd_user
 WHERE user_name = l_user_name;
 -- Get the application_id and responsibility_id
 SELECT application_id, responsibility_id
 INTO l_application_id, l_resp_id
 FROM fnd_responsibility
 WHERE responsibility_key = l_resp_name;
 FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); 
 dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
       
        -- Get the first row
        FOR ir IN c_item_reservations LOOP
          l_rsv_rec.organization_id := ir.organization_id;
          l_rsv_rec.inventory_item_id := ir.inventory_item_id;
    l_rsv_rec.reservation_id := ir.reservation_id;
    DBMS_OUTPUT.PUT_LINE('c_item_reservations'||':  '||ir.reservation_id);
          --EXIT;
       
       
        -- Get all reservations that exist for this item
        -- call API to get all the reservations for this item
        DBMS_OUTPUT.PUT_LINE('=======================================================');
        DBMS_OUTPUT.PUT_LINE('Calling INV_RESERVATION_PUB.Query_Reservation');        
        INV_RESERVATION_PUB.QUERY_RESERVATION(
                  P_API_VERSION_NUMBER    => l_api_version
                , P_INIT_MSG_LST    => l_init_msg_list
                , X_RETURN_STATUS    => x_return_status
                , X_MSG_COUNT     => x_msg_count
                , X_MSG_DATA     => x_msg_data
                , P_QUERY_INPUT     => l_rsv_rec
                , P_LOCK_RECORDS    => FND_API.G_FALSE
                , P_SORT_BY_REQ_DATE    => INV_RESERVATION_GLOBAL.G_QUERY_NO_SORT
                , P_CANCEL_ORDER_MODE    => INV_RESERVATION_GLOBAL.G_CANCEL_ORDER_NO
                , X_MTL_RESERVATION_TBL    => x_mtl_reservation_tbl
                , X_MTL_RESERVATION_TBL_COUNT   => x_mtl_reservation_tbl_count
                , X_ERROR_CODE     => x_error_code 
         );
        
         DBMS_OUTPUT.PUT_LINE('=======================================================');
         DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);
 
         IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
            DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);
         END IF;
        
         IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
            FOR i IN 1..x_mtl_reservation_tbl_count LOOP
                DBMS_OUTPUT.PUT_LINE('=======================================================');
                dbms_output.put_line('reservation_id              : '|| TO_CHAR(x_mtl_reservation_tbl(i).reservation_id));
                dbms_output.put_line('requirement_date            : '|| TO_CHAR(x_mtl_reservation_tbl(i).requirement_date, 'YYYY/MM/DD'));
                dbms_output.put_line('organization_id             : '|| TO_CHAR(x_mtl_reservation_tbl(i).organization_id));
                dbms_output.put_line('inventory_item_id           : '|| TO_CHAR(x_mtl_reservation_tbl(i).inventory_item_id));
                dbms_output.put_line('demand_source_type_id       : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_type_id));
                dbms_output.put_line('demand_source_name          : '|| x_mtl_reservation_tbl(i).demand_source_name);
                dbms_output.put_line('demand_source_header_id     : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_header_id));
                dbms_output.put_line('demand_source_line_id       : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_line_id));
                dbms_output.put_line('demand_source_line_detail   : '|| TO_CHAR(x_mtl_reservation_tbl(i).demand_source_line_detail));
                dbms_output.put_line('primary_uom_code            : '|| x_mtl_reservation_tbl(i).primary_uom_code);
                dbms_output.put_line('reservation_uom_code        : '|| x_mtl_reservation_tbl(i).reservation_uom_code);
                dbms_output.put_line('reservation_quantity        : '|| TO_CHAR(x_mtl_reservation_tbl(i).reservation_quantity));
                dbms_output.put_line('primary_reservation_quantity: '|| TO_CHAR(x_mtl_reservation_tbl(i).primary_reservation_quantity));
                dbms_output.put_line('detailed_quantity           : '|| TO_CHAR(x_mtl_reservation_tbl(i).detailed_quantity));
                dbms_output.put_line('supply_source_type_id       : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_type_id));
                dbms_output.put_line('supply_source_header_id     : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_header_id));
                dbms_output.put_line('supply_source_line_id       : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_line_id));
                dbms_output.put_line('supply_source_name          : '|| (x_mtl_reservation_tbl(i).supply_source_name));
                dbms_output.put_line('supply_source_line_detail   : '|| TO_CHAR(x_mtl_reservation_tbl(i).supply_source_line_detail));
                dbms_output.put_line('subinventory_code           : '|| x_mtl_reservation_tbl(i).subinventory_code);
                dbms_output.put_line('ship_ready_flag             : '|| TO_CHAR(x_mtl_reservation_tbl(i).ship_ready_flag));
                dbms_output.put_line('staged_flag                 : '|| x_mtl_reservation_tbl(i).staged_flag);
                DBMS_OUTPUT.PUT_LINE('=======================================================');
             END LOOP;  
           END IF;
        -- call API to relieve all the queried reservations for this item
        DBMS_OUTPUT.PUT_LINE('=======================================================');
        DBMS_OUTPUT.PUT_LINE('Calling INV_RESERVATION_PUB.Relieve_Reservation');         
       FOR i IN 1..x_mtl_reservation_tbl_count LOOP
       
                l_primary_relieved_qty := x_mtl_reservation_tbl(i).reservation_quantity; --- Passing whole quantity to relieve
             
              -- Call the API to relieve reservations for the provided serial numbers
              INV_RESERVATION_PUB.RELIEVE_RESERVATION(
                  P_API_VERSION_NUMBER   =>  l_api_version
                  , P_INIT_MSG_LST   =>  l_init_msg_list
                  , X_RETURN_STATUS   =>  x_return_status
                  ,  X_MSG_COUNT   =>  x_msg_count    
                  ,  X_MSG_DATA    =>  x_msg_data     
                  ,  P_RSV_REC    =>  x_mtl_reservation_tbl(i)      
                  ,  P_PRIMARY_RELIEVED_QUANTITY =>  l_primary_relieved_qty
                  ,  P_RELIEVE_ALL   =>  FND_API.G_true      ---- True for Quantity Relieves to Zero
                  ,  P_ORIGINAL_SERIAL_NUMBER  =>  l_serial_number
                  ,  P_VALIDATION_FLAG   =>  l_validation_flag
                  ,  X_PRIMARY_RELIEVED_QUANTITY =>  x_primary_relieved_qty
                  ,  X_PRIMARY_REMAIN_QUANTITY  =>  x_primary_remain_qty
              );
             
      
              DBMS_OUTPUT.PUT_LINE('=======================================================');
              DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);
       
              IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
                 DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);
              END IF;
             
              IF (X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS) THEN
         DBMS_OUTPUT.PUT_LINE('Item reserved quantity relieve details');
     DBMS_OUTPUT.PUT_LINE('Reservation ID:'||x_mtl_reservation_tbl(i).reservation_id);
     DBMS_OUTPUT.PUT_LINE('Item ID:'||x_mtl_reservation_tbl(i).inventory_item_id);
     DBMS_OUTPUT.PUT_LINE('Organization ID:'||x_mtl_reservation_tbl(i).organization_id);
     DBMS_OUTPUT.PUT_LINE('Primary Relieved Quantity: '||X_PRIMARY_RELIEVED_QTY);
         DBMS_OUTPUT.PUT_LINE('Primary Remain Quantity: '||x_primary_remain_qty); 
              END IF;
              END LOOP;
   END LOOP;
EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
          DBMS_OUTPUT.PUT_LINE('=======================================================');
END;
/
begin
XXT_RelieveReservation('<Item>','<Organization>','<Order>');-- Item , Organization Code, Sales Order number respectively
END;
/
SELECT msi.organization_id, msi.inventory_item_id, msi.segment1, msi.primary_uom_code,res.reservation_id,mso.segment1
        FROM mtl_system_items_b msi, mtl_parameters mp, mtl_reservations res, mtl_Sales_orders mso
        WHERE msi.segment1 = 'B6-M'
        AND msi.organization_id = mp.organization_id
        AND mp.organization_code = 'TB1'
        AND res.organization_id = msi.organization_id
        AND res.inventory_item_id = msi.inventory_item_id
      AND res.demand_source_header_id=mso.sales_order_id
        AND mso.segment1              ='413098' ;

/

This provides the Customer Address of an Order

Modules Involved : OM
Purpose    : This provides the Customer Address of an Order
Description      : We can get the BILL_TO and SHIP_TO addresses of a given order.
Needs two parmeters(ORDER_NUMBER,ORG_ID) and It provides Bill_To and Ship_To address in two different lines
****************************************************************** */
SELECT OOHA.ORDER_NUMBER
, HP.PARTY_NAME
, HCSUA.SITE_USE_CODE
, HCSUA.location
, hl.ADDRESS1||','||hl.ADDRESS2||' '||hl.ADDRESS3||' '||hl.ADDRESS4||' '||hl.CITY||','||hl.STATE||','
  ||hl.POSTAL_CODE||','||hl.country ADDRESS
FROM OE_ORDER_HEADERS_ALL OOHA
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCT_SITES_ALL HCASA
, HZ_CUST_SITE_USES_ALL  HCSUA
, HZ_LOCATIONS HL
WHERE 1=1
and ORDER_NUMBER = :ORDER_NUMBER
and OOHA.ORG_ID = :ORG_ID
and (OOHA.INVOICE_TO_ORG_ID = HCSUA.SITE_USE_ID or OOHA.SHIP_TO_ORG_ID = HCSUA.SITE_USE_ID)
--AND OOHA.ORG_ID = HCSUA.ORG_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HP.PARTY_ID = HPS.PARTY_ID
and HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
and HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
and HCSUA.SITE_USE_CODE in ('BILL_TO', 'SHIP_TO')
--AND HCSUA.PRIMARY_FLAG = 'Y'
and HPS.LOCATION_ID  = HL.LOCATION_ID;

To get the Employee's Potential Life Event Details

Modules Involved : HRMS, OAB
Purpose    : To get the Employee's Potential Life Event Details
Description      : It gives the Life Event details for an employee, the Status,the date the life event was Started,
       the date the life event was closed (Processed), as well as Void or Back Out Dates.
Needs two parameters (Person_Id, Business Group ID)
****************************************************************** */
SELECT  PPF.PERSON_ID
       , PPF.FULL_NAME
       , bplp.lf_evt_ocrd_dt                  eventDate
       , BL.NAME || ' (' || BL.LER_ID  || ')' LIFEEVENT
       , BPLP.PTNL_LER_FOR_PER_STAT_CD  STATUS
       , bplp.NTFN_DT                         notified
       , bplp.DTCTD_DT                        detected
       , bplp.UNPROCD_DT                      unprocessed
       , bplp.PROCD_DT                        processed
       , BPLP.VOIDD_DT                        VOIDED
       , bplp.lf_evt_ocrd_dt                  Occured
      FROM
         ben_ptnl_ler_for_per bplp
       , BEN_LER_F            BL
       , PER_ALL_PEOPLE_F     PPF
    WHERE sysdate between ppf.effective_start_date and ppf.effective_end_date
        AND ppf.person_id = bplp.person_id
        AND sysdate between bl.effective_start_date and bl.effective_end_date
        AND bplp.ler_id = bl.ler_id
        and PPF.BUSINESS_GROUP_ID = :BUSINESS_GROUP_ID
        AND PPF.PERSON_ID = :Person_ID
      ORDER BY 1 desc;

To get the Employee's assignment Information

Modules Involved : Human Resource Management System (HRMS)
Purpose    : To get the Employee's assignment Information
Description      : We need to pass the employee full_name. It retrieves Employee's Business Group,Hire Date,Job,Dept
                   ,Division,Manager etc..
Note: Department and Division is vary from client to client depends on the company segments.
      Here Valueset Name for the Department is hard-coded.
****************************************************************** */
SELECT    PAPF.PERSON_ID
,         PAPF.FULL_NAME
,         PBG.NAME
,         PAPF.ORIGINAL_DATE_OF_HIRE HIRE_DATE
,         PPOS.DATE_START LATEST_START_DATE
,         PPOS.ADJUSTED_SVC_DATE
,         SUBSTR(PPG.GROUP_NAME,1,INSTR(PPG.GROUP_NAME,'.')-1) DIVISION
,         JOB.NAME "JOB TITLE"
,         GCC.SEGMENT2 "DEPT NUMBER"
,         FVT.DESCRIPTION  "DEPT NAME"
,         SUP.FULL_NAME MANAGER
,         PG.NAME GRADE
,         LOC.LOCATION_CODE  "WORK LOCATION"
FROM      PER_ALL_PEOPLE_F PAPF
,         PER_ALL_PEOPLE_F SUP
,         PER_BUSINESS_GROUPS PBG
,         PER_PERIODS_OF_SERVICE PPOS
,         PER_ALL_ASSIGNMENTS_F PAAF
,         PER_JOBS_TL JOB
,         PER_GRADES_TL PG
,         PAY_PEOPLE_GROUPS   PPG
,         HR_LOCATIONS_ALL_TL LOC
,         GL_CODE_COMBINATIONS    GCC
,         FND_FLEX_VALUE_SETS      FVS       
,         FND_FLEX_VALUES FV
,         FND_FLEX_VALUES_TL FVT
where     1=1
and       PAPF.FULL_NAME = :EMPLOYEE_FULL_NAME
and       PAPF.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
and       PAAF.PERSON_ID  =   PAPF.PERSON_ID
and       PAAF.ASSIGNMENT_TYPE = 'E'
AND       PAAF.PERIOD_OF_SERVICE_ID   =   PPOS.PERIOD_OF_SERVICE_ID
AND       TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE)
AND       TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
AND       TRUNC(SYSDATE) BETWEEN TRUNC(SUP.EFFECTIVE_START_DATE) AND TRUNC(SUP.EFFECTIVE_END_DATE)
AND       PAAF.JOB_ID = JOB.JOB_ID
AND       PAAF.SUPERVISOR_ID = SUP.PERSON_ID
AND       PAAF.GRADE_ID   =   PG.GRADE_ID(+)
AND       PAAF.PEOPLE_GROUP_ID   =   PPG.PEOPLE_GROUP_ID(+)
AND       PAAF.LOCATION_ID  =   LOC.LOCATION_ID(+)
AND       GCC.CODE_COMBINATION_ID = PAAF.DEFAULT_CODE_COMB_ID
AND       FVS.FLEX_VALUE_SET_NAME  =  'SSFT Cost Center'
AND       FVS.FLEX_VALUE_SET_ID  =  FV.FLEX_VALUE_SET_ID
AND       FV.FLEX_VALUE_ID  =  FVT.FLEX_VALUE_ID
and          FV.FLEX_VALUE = GCC.SEGMENT2

order by 3

To get the Suppliers Bank details

Modules Involved : Accounts Payables (AP)
Purpose    : To get the Suppliers Bank details
Description      : We can get the Supplier, Supplier site details, bank and branch details of the Supplier
Note: We need to pass the vendor_id as parameter.
****************************************************************** */
SELECT DISTINCT pv.vendor_id,
pv.vendor_name vendor_name,
pv.segment1 vendor_number,
pv.vendor_type_lookup_code vendor_type_lookup_code,
pvs.vendor_site_id vendor_site_id,
pvs.vendor_site_code vendor_site_Name,
pvs.address_line1 Site_Address_Line1,
pvs.address_line2 Site_Address_Line2,
PVS.CITY,
PVS.ZIP,
aba.org_id,
abau.bank_account_uses_id bank_account_uses_id,
abau.end_date end_date,
abau.external_bank_account_id external_bank_account_id,
abau.primary_flag primary_flag,
abau.start_date start_date,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aba.bank_account_type bank_account_type,
aba.account_type account_type,
aba.currency_code currency_code,
aba.description bank_account_description,
aba.check_digits check_digits,
aba.multi_currency_flag multi_currency_flag,
abb.bank_name bank_name,
abb.bank_name_alt bank_name_alt,
abb.bank_number bank_number,
abb.bank_branch_name bank_branch_name,
abb.bank_branch_name_alt bank_branch_name_alt,
abb.bank_num bank_num, abb.institution_type institution_type,
abb.bank_branch_type bank_branch_type,
abb.end_date branch_end_date,
aba.inactive_date acct_inactive_date
FROM ap_bank_account_uses_all abau,
ap_bank_accounts_all aba,
ap_bank_branches abb,
po_vendors pv,
PO_VENDOR_SITES_ALL PVS
where 1=1
and pv.vendor_id = :Vendor_Id
and abau.external_bank_account_id = aba.bank_account_id
AND aba.bank_branch_id = abb.bank_branch_id
AND abau.vendor_id = pv.vendor_id
AND abau.vendor_id = pvs.vendor_id(+)
and ABAU.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
;