Query for Customer Receipt Details

SELECT   acra.cash_receipt_id,
         DECODE (acra.TYPE,
                 'cash', 'cash receipt receipt',
                 'misc', 'miscellaneous',
                 acra.TYPE
                ) receipt_type,
         acra.currency_code, acra.doc_sequence_value receipt_number,
         acra.receipt_number reference_number,
         TRUNC (acra.receipt_date) receipt_date, hp.party_name received_from,
         acra.misc_payment_source, hca.account_number customer_no,
         NVL (acra.amount, 0) entered_amount,
         NVL (acra.amount, 0) * NVL (acra.exchange_rate, 1) functional_amount,
         arm.NAME payment_method, abaa.bank_account_num bank_acc_num,
         abb.bank_name, abb.bank_branch_name, acra.comments description
    FROM ar_cash_receipts_all acra,
         ar_receipt_methods arm,
         ap_bank_accounts_all abaa,
         ap_bank_branches abb,
         hz_cust_accounts hca,
         hz_parties hp
   WHERE acra.pay_from_customer = hca.cust_account_id(+)
     AND acra.org_id = abaa.org_id(+)
     AND hca.party_id = hp.party_id(+)
     AND acra.receipt_method_id = arm.receipt_method_id
     AND acra.remittance_bank_account_id = abaa.bank_account_id
     AND abaa.bank_branch_id = abb.bank_branch_id
ORDER BY TRUNC (acra.receipt_date), acra.doc_sequence_value;

Query for Supplier Bank Details

SELECT DISTINCT pv.vendor_name vendor_name, pv.segment1 vendor_number,
                pvs.vendor_site_code vendor_site_code,
                aba.bank_account_name bank_account_name,
                aba.bank_account_num bank_account_num,
                aba.currency_code currency_code,
                abau.primary_flag primary_flag, abb.bank_name bank_name,
                abb.bank_number bank_number,
                abb.bank_branch_name bank_branch_name, abb.bank_num bank_num
           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 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(+)

Query for Customer Address Details

SELECT DISTINCT hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
           FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
          WHERE hp.party_id = hps.party_id(+)
            AND hp.party_id = hca.party_id(+)
            AND hcasa1.party_site_id(+) = hps.party_site_id
            AND hcasa2.party_site_id(+) = hps.party_site_id
            AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
            AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
            AND hcsua1.site_use_code(+) = 'bill_to'
            AND hcsua2.site_use_code(+) = 'ship_to'
            AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
            AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
            AND hps.location_id = hl.location_id
            AND hl.country = ftt.territory_code
            AND ftt.LANGUAGE = USERENV ('lang')
       ORDER BY customer_number;

Query for Sales Order Details

SELECT   ooha.header_id order_header_id, ottt.NAME order_type_name,
         ooha.order_number, ooha.ordered_date,
         ooha.transactional_curr_code order_currency, hp.party_id,
         hp.party_number, hp.party_name customer_name,
         hca.cust_account_id customer_id, hca.account_number customer_number,
         oola.line_id order_line_id, oola.line_number, oola.inventory_item_id,
         msib.segment1 item_number, msib.description item_desc,
         oola.attribute15 superseded_item, oola.order_quantity_uom,
         oola.ordered_quantity, oola.unit_selling_price
    FROM oe_order_headers_all ooha,
         oe_order_lines_all oola,
         oe_transaction_types_tl ottt,
         mtl_system_items_b msib,
         mtl_parameters mp,
         org_organization_definitions ood,
         hz_parties hp,
         hz_cust_accounts hca
   WHERE ooha.header_id = oola.header_id
     AND ottt.transaction_type_id(+) = ooha.order_type_id
     AND ottt.LANGUAGE = USERENV ('LANG')
     AND hca.cust_account_id(+) = ooha.sold_to_org_id
     AND hp.party_id = hca.party_id
     AND ooha.org_id = oola.org_id(+)
     AND msib.inventory_item_id = oola.inventory_item_id
     AND msib.organization_id = mp.master_organization_id
     AND mp.organization_id = ood.organization_id
     AND mp.master_organization_id = mp.organization_id
     AND ood.operating_unit = fnd_profile.VALUE ('ORG_ID')
     AND ooha.order_number = :sales_order_number
ORDER BY ottt.NAME, ooha.order_number, oola.line_number;

Query to get Request Group Details Responsibility wise:

SELECT   frg.request_group_name, fat1.application_name, frg.description,
         DECODE (frgu.request_unit_type,
                 'P', 'Program',
                 'S', 'Set',
                 'A', 'Application',
                 frgu.request_unit_type
                ) TYPE,
         DECODE (frgu.request_unit_type,
                 'P', fcpt.user_concurrent_program_name,
                 'S', frst.user_request_set_name,
                 'A', fat3.application_name,
                 frgu.request_unit_type
                ) NAME,
         fat2.application_name
    FROM fnd_request_groups frg,
         fnd_request_group_units frgu,
         fnd_concurrent_programs_tl fcpt,
         fnd_application_tl fat1,
         fnd_application_tl fat2,
         fnd_application_tl fat3,
         fnd_request_sets_tl frst
   WHERE frg.request_group_id = frgu.request_group_id
     AND frgu.request_unit_id = fcpt.concurrent_program_id(+)
     AND fcpt.LANGUAGE(+) = USERENV ('LANG')
     AND frg.application_id = fat1.application_id
     AND fat1.LANGUAGE(+) = USERENV ('LANG')
     AND frgu.unit_application_id = fat2.application_id
     AND fat2.LANGUAGE(+) = USERENV ('LANG')
     AND frgu.unit_application_id = fcpt.application_id(+)
     AND frgu.request_unit_id = frst.request_set_id(+)
     AND frst.LANGUAGE(+) = USERENV ('LANG')
     AND frgu.request_unit_id = fat3.application_id(+)
     AND fat3.LANGUAGE(+) = USERENV ('LANG')
     AND frgu.unit_application_id = frst.application_id(+)
     AND upper(fat1.application_name) = upper(:application_name)
ORDER BY request_group_name, frgu.request_unit_type, frgu.request_unit_id;

Query to find who and when update an Oracle Application user's profile

SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

Make the concurrent program to complete with warning

To make the program to complete with warning. Through sql

l_submit_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL);

How to use % before the like operator in the query

SELECT *  
FROM all_source |
  WHERE owner       = 'APPS'
    AND UPPER(type) = UPPER(p_type)
    AND UPPER(name) = UPPER(p_name)
    AND UPPER(text) like UPPER (''||'%'||:p_text||'%'||'');

Which User is Locking the table

Which User is Locking the table

I am new to the blogging world and before I really get into it, I am trying to post some of the queries that I already have .. so may be few of my initial articles may have just queries.
Here is another query that can sometime be very useful. This will list the name of user that is locking a table. The object name is taken as an input parameter.

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

Thanks & Regards,

Anto Joe Natesh I

Database and Application Information

Below are some of the queries that can be used to get the database and Application information.

1) Get Product Version

SELECT product
     , VERSION
     , status
FROM   product_component_version

The other way to get this information is by using following query

select * from v$version;

2) Get Applications Version and Patch Information

SELECT   SUBSTR (a.application_name, 1, 60) Application_Name
       , SUBSTR (i.product_version, 1, 4) Version
       , i.patch_level 
       , i.application_id 
       , i.last_update_date 
FROM     apps.fnd_product_installations i
       , apps.fnd_application_all_view a
WHERE    i.application_id = a.application_id
ORDER BY a.application_name

3) Patch Information AD_APPLIED_PATCHES table stores information about all the patches installed in the system.

SELECT applied_patch_id
     , patch_name
     , patch_type
     , source_code
     , creation_date
     , last_update_date
FROM   ad_applied_patches

4) Check if the application is setup for Multi-Org

SELECT multi_org_flag
FROM   fnd_product_groups

Thanks & Regards,

Anto Joe Natesh I

Different Types of Table in Oracle Apps

Different Types of Table in Oracle Apps

In oracle applications there are tables that ends with similar suffixes. Here I am trying to list the meaning of those, please provide your suggestion and help me if I have missed anything.

_B	The Main base tables
_ALL	Contains multi org data. There will be similar table without _ALL. Before querying this data the environment variable needs to be set. Dbms_application_info.set_client_info('org_id'), or apps_initialize can be used to set the environment variable.common column.
_V	View created on base table. Mostly forms are created based on this views
_TL	Tables that support multi language.
_VL	View created on multi language tables. The view generally uses the base table and _tl table
_F	This indicates that these are the date tracking tables. These tables are generally seen for HRMS and contain 2 common columns effective_start_date and effective_end_date
_S	sequence related tables
_DFV /_KFV	The DFF/KFF table created on the base table. This is the best way to get the concatenated value of DFF/KFF. Also using this table the values can be queried based on the DFF/KFF name and not attributes column.
_X	Current information table..there is no date tracking .

 

Link Purchase Order and Requisition

Link Purchase Order and Requisition

You have a purchase order and you have a requisition, but wait how do you know how this purchase order is linked with requisition. Here is the query thats answers this. Use this query find linked Purchase order and Requisition.
This could be really a helpful one.

SELECT prh.segment1 req_number
      ,prh.authorization_status
      ,prl.line_num req_line_num
      ,prl.item_description req_item_description
      ,prl.unit_price req_unit_price
      ,prl.quantity req_quantity
      ,pd.req_header_reference_num
      ,pd.req_line_reference_num
      ,pl.line_num
      ,pl.item_description
      ,pl.quantity
      ,pl.amount
      ,ph.segment1 po_number
      ,prd.distribution_id
      ,pd.req_distribution_id
  FROM po_requisition_headers_all prh
      ,po_requisition_lines_all   prl
      ,po_req_distributions_all   prd
      ,po_distributions_all       pd
      ,po_line_locations_all      pll
      ,po_lines_all           pl
      ,po_headers_all             ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   and prh.org_id = prl.org_id
   and prl.requisition_line_id = prd.requisition_line_id
   and prl.org_id = prd.org_id
   and prd.distribution_id = pd.req_distribution_id(+)
   and prd.org_id = pd.org_id(+)
   and pd.line_location_id = pll.line_location_id(+)
   and pd.org_id = pll.org_id(+)
   and pll.po_line_id = pl.po_line_id(+)
   and pll.org_id = pl.org_id(+)
   and pl.po_header_id = ph.po_header_id(+)
   and pl.org_id = ph.org_id(+)

Thanks & Regards,

Anto Joe Natesh I

Onhand Quantity at given date

Below is the query that can help in getting onhand quantity at given date. The query inputs the Item ID, organization ID and date.

SELECT   SUM (target_qty)
       , item_id
FROM     (SELECT   moqv.subinventory_code subinv
                 , moqv.inventory_item_id item_id
                 , SUM (transaction_quantity) target_qty
          FROM     mtl_onhand_qty_cost_v moqv
          WHERE    moqv.organization_id = :org_id
          AND      moqv.inventory_item_id = :item_id
          GROUP BY moqv.subinventory_code
                 , moqv.inventory_item_id
                 , moqv.item_cost
          UNION
          SELECT   mmt.subinventory_code subinv
                 , mmt.inventory_item_id item_id
                 , -SUM (primary_quantity) target_qty
          FROM     mtl_material_transactions mmt
                 , mtl_txn_source_types mtst
          WHERE    mmt.organization_id = :org_id
          AND      transaction_date >= TO_DATE (:hist_date) + 1
          AND      mmt.transaction_source_type_id =
                                               mtst.transaction_source_type_id
          AND      mmt.inventory_item_id = :item_id
          GROUP BY mmt.subinventory_code
                 , mmt.inventory_item_id) oq
GROUP BY oq.item_id

Calculating Weekdays between two dates

Create this function:

CREATE OR REPLACE FUNCTION totworkdays (fromdate DATE, todate DATE)
   RETURN NUMBER IS
   totalsundays     NUMBER;
   totalsaturdays   NUMBER;
begin
   totalsundays
        := NEXT_DAY (todate - 7, 'sunday')
           - NEXT_DAY (fromdate - 1, 'sunday');
   totalsaturdays
      :=   NEXT_DAY (todate - 7, 'saturday')
         - NEXT_DAY (fromdate - 1, 'saturday');

   RETURN (todate - fromdate - (totalsundays + totalsaturdays) / 7 - 1);
END totworkdays;

Call this function as follows:


declare
lv_tot_work_days number;
begin
lv_tot_work_days := totworkdays ('01-jan-2009', '31-jan-2009');
dbms_output.put_line('Total Work Days: '||lv_tot_work_days);
end;

Sending EMail Unix Code

## Parameter passed to Oracle Apps is copied into a local variable

P_PARAM=$1

echo "Parameters passed by Apps to the shell script"

echo $P_PARAM

##The parameters as supplied by User, after removing the default Oracle Apps Parameters.

P_USER_PARAM=`echo $P_PARAM|cut -d" " -f 9-`

echo "\nParameters supplied by the user"

echo $P_USER_PARAM

##Extracting indivdual parameters from the user supplied params, using " as the delimiter

P_TO_ID=`echo $P_USER_PARAM|cut -d'"' -f 2`

P_CC_ID=`echo $P_USER_PARAM|cut -d'"' -f 4`

P_SUBJECT=`echo $P_USER_PARAM|cut -d'"' -f 6`

P_BODY_1=`echo $P_USER_PARAM|cut -d'"' -f 8`

P_BODY_2=`echo $P_USER_PARAM|cut -d'"' -f 10`

P_FILE_PATH=`echo $P_USER_PARAM|cut -d'"' -f 12`

P_FILE_NAME=`echo $P_USER_PARAM|cut -d'"' -f 14`

P_FILE_DISP=`echo $P_USER_PARAM|cut -d'"' -f 16`

##Echoing user supplied params

echo "\nindivudal User Supplied Parameters"

echo "TO Mail Id:"$P_TO_ID

echo "CC Email Id:"$P_CC_ID

echo "Subject:"$P_SUBJECT

echo "Body1:"$P_BODY_1

echo "Body2:"$P_BODY_2

echo "File Attachment Path:"$P_FILE_PATH

echo "File Name:"$P_FILE_NAME

echo "File Display Name:"$P_FILE_DISP

P_BODY=$P_BODY_1$P_BODY_2

echo "\n Complete Body"

echo $P_BODY

## IF there is no file attachment

if [ -z "$P_FILE_PATH" ]

then

  echo "\nFile attachment not specified"

  ## IF there is no CC ID

  if [ -z "$P_CC_ID" ]

  then

     echo "\nCC Id is NULL"

     (echo $P_BODY) | mailx -s "${P_SUBJECT}" $P_TO_ID

  else

     echo "\nCC Id is NOT NULL"

     (echo $P_BODY) | mailx -s "${P_SUBJECT}" -c $P_CC_ID $P_TO_ID

  fi

fi

## If file attachment is present

if [ -n "$P_FILE_PATH" ]

then

  echo "\nFile attachment is specified"

  P_FILE_PATH=`echo $P_FILE_PATH|sed 's/\/$//'`

  echo "\nFile Path after removing trailing /"

  echo $P_FILE_PATH

  P_FILE_PATH=`eval echo $P_FILE_PATH`

  echo "\nFile Path after evaluating any Environment Variables"

  echo $P_FILE_PATH

  ## cd command is given only to check that the file path is a valid file path.

  ## If it is invalid, shell script will error out.

  cd $P_FILE_PATH

  P_FILE_ATTACH=$P_FILE_PATH/$P_FILE_NAME

  echo "\nComplete file path with attachment name"

  echo $P_FILE_ATTACH

  ## IF there is no CC ID

  if [ -z "$P_CC_ID" ]

  then

     echo "\nCC Id is NULL"

     ((echo $P_BODY);uuencode "${P_FILE_ATTACH}" "${P_FILE_DISP}") | mailx -s "${P_SUBJECT}" $P_TO_ID

  else

     echo "\nCC Id is NOT NULL"

     ((echo $P_BODY);uuencode "${P_FILE_ATTACH}" "${P_FILE_DISP}") | mailx -s "${P_SUBJECT}" -c $P_CC_ID $P_TO_ID

  fi

fi

if [ $? -eq 1 ]

then

    echo 'ERROR while sending email.  Please check log file'

    exit 1

fi

###### End of Shell Script ######

Sending EMail After Report Code

  function AfterReport return boolean is

      l_mail_reqid  NUMBER;

      l_to_mail_id  VARCHAR2(200);

      l_cc_mail_id  VARCHAR2(200);

      l_subject     VARCHAR2(225);    

      l_body        VARCHAR2(225);

      l_file_path   VARCHAR2(100);

      l_file_name   VARCHAR2(100);

      l_file_disp   VARCHAR2(100);

   BEGIN      

      l_to_mail_id := 'anto.natesh@abc.com; -- To Email Id

      l_cc_mail_id := 'anto.natesh@gmail.com'; -- CC Mail 

        l_subject    := 'XXX Process is completed at '||SYSDATE;

        l_body       := 'Hi,\\\\nThe process XXX has completed and '|| 'attached is the Summary Report\\\\nThanks,\\\\nAnto';

        l_file_path  := '$APPLCSF/$APPLOUT'; 

        l_file_name  := 'o'||:p_conc_request_id||'.out';      

        l_file_disp  := 'RFQ_Report.pdf';

        srw.message('1', 'id:'||:p_conc_request_id);

   

      -- Submit the request

      l_mail_reqid := FND_REQUEST.SUBMIT_REQUEST( 'XXCUST', 

                                                  'SEND_MAIL', 

                                                  NULL, -- Description (Optional)

                                                  NULL, -- Start Time (Optional)

                                                  FALSE, -- TRUE if this is to be child request

                                                  l_to_mail_id, -- Comma separated TO Email Ids

                                                  l_cc_mail_id, -- Comma separated CC Email Ids

                                                  l_subject,    -- Subject of the mail

                                                  l_body,       -- Body of the mail

                                                  NULL,         -- Second part of body, if more than 225 Characters

                                                  l_file_path,  -- File path 

                                                  l_file_name,  -- File to be attached

                                                  l_file_disp,  -- Display name of the file

                                                  CHR(0),

                                                  '','','','','','','','', '', '', '','','','','','','','','',

                                                  '','','','','','','','','','','','','','','','','','','','',

                                                  '','','','','','','','','','','','','','','','','','','','',

                                                  '','','','','','','','','','','','','','','','','','','','',

                                                  '','','','','','','','','','','','');

      IF l_mail_reqid <> 0 THEN

         COMMIT;         

      ELSE

        srw.message('1005','Concurrent Request Submission for sending email failed!!!');

      END IF;

    RETURN TRUE;  

END;

Registering Custom Table

To register your custom table under FND. 


1. Input is your custom table name. Execute these  4 queries 
2. Spool the records
3. Execute the spooled records in apps
4. Commit;




select 'EXEC '||'AD_DD.REGISTER_TABLE(''XXCUST'', '''||TABLE_NAME||''',''T'',8,10,90);' from all_tables
where table_name = :TABLE_NAME
/
select 'EXEC '||'AD_DD.REGISTER_COLUMN(''XXCUST'', '''||TABLE_NAME||''','''||COLUMN_NAME||''','||COLUMN_ID||','''||DATA_TYPE||''','||DATA_LENGTH||','''||NULLABLE||''',''N'');' from all_tab_columns
where table_name = :TABLE_NAME
ORDER BY COLUMN_ID
/
select 'EXEC '||'AD_DD.REGISTER_PRIMARY_KEY(''XXCUST'','''||INDEX_NAME||''','''||TABLE_NAME||''','''||ITYP_NAME||''',''S'',''Y'',''Y'');'
FROM ALL_INDEXES
WHERE table_name = :TABLE_NAME 
and uniqueness = 'UNIQUE'
/
select 'EXEC '||'AD_DD.REGISTER_PRIMARY_KEY_COLUMN(''XXCUST'','''||A.INDEX_NAME||''','''||A.TABLE_NAME||''','''||A.COLUMN_NAME||''','||A.COLUMN_POSITION||');'
FROM ALL_IND_COLUMNS A, ALL_INDEXES B
WHERE A.TABLE_NAME = :TABLE_NAME
AND A.INDEX_NAME = B.INDEX_NAME
AND B.UNIQUENESS = 'UNIQUE'
/

To delete the registered Tables, columns

select 'EXEC '||'AD_DD.DELETE_TABLE(''XXCUST'', '''||TABLE_NAME);' from all_tables

where table_name = :TABLE_NAME

/

select 'EXEC '||'AD_DD.REGISTER_COLUMN(''XXCUST'', '''||TABLE_NAME||''','''||COLUMN_NAME);' from all_tab_columns

where table_name = :TABLE_NAME

ORDER BY COLUMN_ID

/