How to terminate 'Concurrent program' in backend

Enable trace for Concurrent Requests

To enable trace for concurrent requests, the main reason for this is to troubleshooting performance problems. Here is a simple guide how to enable oracle session tracing for the request.

1. Login as sysadmin 
2. Go to -> System Administrator -> Concurrent : Program -> Define and Press F11 
3. Enter the Concurrent Program Name (Eg. Journal Import) -> Press Ctrl + F11 
4. Enable the check box "Enable Trace" above "Copy To" button. Click on Save and close the window. 
5. Select Requests -> Run->Submit the concurrent request! 
6. The trace file will be created in the Database Server under the location of "udump" 
sql>select value from v$parameter where name like '%user_dump_dest%';

Supplier Bank Account Details Query

Bank Account can be associated with Supplier at following levels

1. Supplier

2. Supplier site

3. Party Site

4. Party Site + Org

This query can find bank accounts associated with a given supplier at any level.

SELECT 'Bank Account At Supplier Site Level' Bank_Account_Level

     , sup.segment1

     , sup.vendor_name

     , epa.org_id

     , ss.vendor_site_code

     , NULL   Party_Site_Code

     , eba.bank_account_num

     , piu.order_of_preference priority

     , eba.ext_bank_account_id

  FROM ap_suppliers sup

     , ap_supplier_sites_all       ss

     , iby_external_payees_all epa

     , iby_pmt_instr_uses_all  piu

     , iby_ext_bank_accounts   eba

 WHERE sup.vendor_id     = ss.vendor_id

   AND ss.vendor_site_id = epa.supplier_site_id

   AND epa.ext_payee_id  = piu.ext_pmt_party_id      

   AND piu.instrument_id = eba.ext_bank_account_id

   AND sup.segment1      = '702393'

UNION

SELECT 'Bank Account at Supplier Level'

     , sup.segment1

     , sup.vendor_name

     , epa.org_id

     , NULL

     , NULL

     , eba.bank_account_num

     , piu.order_of_preference priority

     , eba.ext_bank_account_id

  FROM ap_suppliers sup

     , iby_external_payees_all epa

     , iby_pmt_instr_uses_all  piu

     , iby_ext_bank_accounts   eba

 WHERE sup.party_id        = epa.payee_party_id

   AND epa.ext_payee_id    = piu.ext_pmt_party_id      

   AND piu.instrument_id   = eba.ext_bank_account_id

   AND sup.segment1        = '702393'

   AND supplier_site_id    IS NULL

   AND party_site_id       IS NULL

UNION

SELECT 'Bank Account at Address + Opearting Unit Level'

     , sup.segment1

     , sup.vendor_name

     , epa.org_id

     , NULL

     , psite.party_site_name

     , eba.bank_account_num

     , piu.order_of_preference priority

     , eba.ext_bank_account_id

  FROM ap_suppliers            sup

     , hz_party_sites          psite

     , iby_external_payees_all epa

     , iby_pmt_instr_uses_all  piu

     , iby_ext_bank_accounts   eba

 WHERE sup.party_id        = psite.party_id

   AND psite.party_site_id = epa.party_site_id

   AND epa.ext_payee_id    = piu.ext_pmt_party_id      

   AND piu.instrument_id   = eba.ext_bank_account_id

   AND sup.segment1        = '702393'

   AND supplier_site_id    IS NULL

   AND epa.org_id          IS NOT NULL

UNION

SELECT 'Bank Account at Address Level'

     , sup.segment1

     , sup.vendor_name

     , epa.org_id

     , NULL

     , psite.party_site_name

     , eba.bank_account_num

     , piu.order_of_preference priority

     , eba.ext_bank_account_id

  FROM ap_suppliers            sup

     , hz_party_sites          psite

     , iby_external_payees_all epa

     , iby_pmt_instr_uses_all  piu

     , iby_ext_bank_accounts   eba

 WHERE sup.party_id        = psite.party_id

   AND psite.party_site_id = epa.party_site_id

   AND epa.ext_payee_id    = piu.ext_pmt_party_id      

   AND piu.instrument_id   = eba.ext_bank_account_id

   AND sup.segment1        = '702393'

   AND supplier_site_id    IS NULL

   AND epa.org_id          IS NULL

 ORDER BY bank_account_num

COALESCE function in Oracle

Syntax

The syntax for the COALESCE function is:

COALESCE( expr1, expr2, ... expr_n )

The COALESCE function can be used in Oracle/PLSQL.

You could use the coalesce function in a SQL statement as follows:

SELECT COALESCE( address1, address2, address3 ) result

FROM suppliers;

The above COALESCE function is equivalent to the following IF-THEN-ELSE statement:

IF address1 is not null THEN

   result := address1;

ELSIF address2 is not null THEN

   result := address2;

ELSIF address3 is not null THEN

   result := address3;

ELSE

   result := null;

END IF;

How to check the Clone date of an Oracle instance

SELECT resetlogs_time FROM v$database;

Get DDL for view

select text from ALL_VIEWS where upper(view_name) like upper('<View name>');

Query to list customers and their sites information

/**********************************************************
*PURPOSE: To list customers and their sites information *
*AUTHOR: RAG *
**********************************************************/
SELECT
  ---------------------- --Customer Information ----------------------
  hp.party_id,
  hp.party_name "CUSTOMER_NAME",
  hca.cust_account_id,
  hca.account_number,
  hcas.org_id, 
  --------------------------- --Customer Site Information ---------------------------
  hcas.cust_acct_site_id,
  hps.party_site_number,
  hcsu.site_use_code, 
  ----------------------- --Customer Site Address -----------------------
  hl.address1,
  hl.address2,
  hl.address3,
  hl.address4,
  hl.city,
  hl.postal_code,
  hl.state,
  hl.province,
  hl.county,
  hl.country,
  hl.address_style
FROM hz_parties hp,
  hz_party_sites hps,
  hz_cust_accounts_all hca,
  hz_cust_acct_sites_all hcas,
  hz_cust_site_uses_all hcsu,
  hz_locations hl
WHERE 1                    =1
AND hp.party_id            = hca.party_id
AND hca.cust_account_id    = hcas.cust_account_id(+)
AND hps.party_site_id(+)   = hcas.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
  --
AND hps.location_id = hl.location_id(+)
  --
AND hp.party_type = 'ORGANIZATION' -- only ORGANIZATION Party types
AND hp.STATUS     = 'A'            -- only Active Parties/Customers
ORDER BY to_number(hp.party_number),
  hp.party_name,
  hca.account_number;

Oracle sequence starting with max value from a table

DECLARE
  ln_max_count NUMBER;
  v_sql        VARCHAR2(250);
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting');
  SELECT 'CREATE SEQUENCE a MINVALUE 0 START WITH '
    ||MAX(attribute17)
    ||' INCREMENT BY 1 CACHE 20'
  INTO v_sql
  FROM HZ_CUST_ACCT_SITES_ALL;
  DBMS_OUTPUT.PUT_LINE('v_sql - '||v_sql);
  EXECUTE IMMEDIATE v_sql;
  DBMS_OUTPUT.PUT_LINE('End');
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error - '||SQLERRM);
END;

Datatype Limits

Datatypes	Limit	Comments
BFILE	Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments	The maximum number of BFILEs is limited by the value of theSESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1 ).
CHAR	Maximum size: 2000 bytes	None
CHAR VARYING	Maximum size: 4000 bytes	None
CLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
Literals (characters or numbers in SQL or PL/SQL)	Maximum size: 4000 characters	None
LONG	Maximum size: 2 GB - 1	Only one LONG column is allowed per table.
NCHAR	Maximum size: 2000 bytes	None
NCHAR VARYING	Maximum size: 4000 bytes	None
NCLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZEinitialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
NUMBER	999...(38 9's) x10125 maximum value -999...(38 9's) x10125 minimum value	Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
RAW	Maximum size: 2000 bytes	None
VARCHAR	Maximum size: 4000 bytes	None
VARCHAR2	Maximum size: 4000 bytes	None

Footnote 1 The absolute maximum number of columns in a table is 1000. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF type), Oracle maps the columns of the user-defined types to relational columns, creating in effect hidden columns that count toward the 1000-column limit. For details on how Oracle calculates the total number of columns in such a table, refer to Oracle Database Administrator's Guide.

for reference - 

https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm

How to Use FNDLOAD?

LDT (Data Loader Files)- This files are Used to upload & download setup data on different instances.
LCT (Data Config Files)- This files are used to create LDT Files. There are difference lct files for different sets of data.
Eg: Request Set- afcpreqg.lct, Concurrent Program- afcpprog.lct, Value Set- afffload.lct..etc..
** Now lets take a simple example where you want to migrate 1 concurrent program from Development (DEV) instance to Production (PROD).
Step 1: Login to Unix Development server and change the directory using following Command where you can store your LDT files:cd $CUST_TOP/ patch/115/import/US/ (where CUST_TOP is your custom top on server)
Step 2: Run the following command by supplying the parameters given in {} as per your needFNDLOAD {userid/password@connect_string} O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct {file_name.ldt} PROGRAM APPLICATION_SHORT_NAME="{your_application_short_name}" CONCURRENT_PROGRAM_NAME="{your_concurrent_name}"The above command will generate ldt file in your custom top directory ($CUST_TOP/ patch/115/import/US/ )
Step 3: Copy the LDT file from Development Server to your local desktop
Step 4: Login to Unix Production Server and copy the LDT files from local desktop to Production Server on following path:$CUST_TOP/ patch/115/import/US/ {file_name.ldt}
Step 5: Finally Run the following command by supplying all parameters in {} to upload concurrent program definition on production application server.$ FNDLOAD {userid/password@connect_string} 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $CUST_TOP/patch/115/import/US/{file_name}.ldt
Note: 1) All above FNDLOAD commands are single line commands.2) All above steps will remain exactly same for all kind of setup data (Eg: Value Set, CP, Req.group, DFF…etc) except the name of LCT file (.lct). LCT file will be different for every different component because the setup data is different.

Generate table DDL

set heading off;

set echo off;

Set pages 999;

set long 90000;

 

spool ddl_list.sql

 

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

 

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

 

spool off;

Update Contingent Workers Assignment/Criteria

--
declare
   --
   l_mode                      varchar2(10) := 'CRITERIA'; --'ASG'
   --- DECLARE variables for HR_ASSIGNMENT_API.update_emp_asg
   --- IN variables
   l_assignment_id             number := 123456;
   l_normal_hours              number := 37;
   --per_assignment_status_types.assignment_status_type_id%TYPE;
   l_assignment_status_type_id number := 1;
   l_sob_id                    number := 1;
   l_default_expense_ccid      number := 1234;

   --- OUT variables
   l_org_now_no_manager_warning   boolean := false;
   l_no_managers_warning          boolean := false;
   l_other_manager_warning        boolean := false;
   l_hourly_salaried_warning      boolean := false;
   l_comment_id                   number;
   l_effective_start_date         date;
   l_effective_end_date           date;
   l_soft_coding_keyflex_id       number;
   l_concatenated_segments        varchar2(100);

   --- DECLARE variables for HR_ASSIGNMENT_API.update_emp_asg_criteria
   --- IN variables
   l_payroll_id                    number := 125;
   l_organization_id               number := 5678;
   l_position_id                   number := 9904567;
   l_job_id                        number := 3456;
   l_location_id                   number := 12345;
   l_grade_id                      number := 12;

   --- OUT variables
   l_spp_delete_warning           boolean := false;
   l_entries_changed_warning      varchar2(100) default null;
   l_tax_district_changed_warning boolean := false;
   l_people_group_id              number;
   l_people_group_name            varchar2(100);
   --
begin
    --
    if l_mode = 'ASG' then

       /* This API updates contingent worker assignment.
       * The assignment must exist as of the effective date, and must be a contingent
       * worker assignment.
       * updating the record. You must set to either UPDATE, CORRECTION,
       * UPDATE_OVERRIDE or UPDATE_CHANGE_INSERT.
       */
      
       hr_assignment_api.update_cwk_asg
                      (p_validate                     => false --in boolean  default false
                      ,p_effective_date               => trunc(sysdate)            
                      ,p_datetrack_update_mode        => 'CORRECTION'      
                      ,p_assignment_id                => l_assignment_id     
                      ,p_assignment_status_type_id    => l_assignment_status_type_id
                      ,p_default_code_comb_id         => l_default_expense_ccid 
                      ,p_set_of_books_id              => l_sob_id 
                      ,p_normal_hours                 => l_normal_hours                         
                       --In/Out
                      ,p_object_version_number  => l_asg_object_version_number
                       --Out
                      ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
                      ,p_effective_start_date        => l_effective_start_date      
                      ,p_effective_end_date          => l_effective_end_date        
                      ,p_comment_id                  => l_comment_id                
                      ,p_no_managers_warning         => l_no_managers_warning       
                      ,p_other_manager_warning       => l_other_manager_warning     
                      ,p_soft_coding_keyflex_id      => l_soft_coding_keyflex_id    
                      ,p_concatenated_segments       => l_concatenated_segments     
                      ,p_hourly_salaried_warning     => l_hourly_salaried_warning   
                      );  
      
       if (l_org_now_no_manager_warning= true or l_no_managers_warning = true  or l_other_manager_warning = true or l_hourly_salaried_warning = true) then
           dbms_output.put_line('Warning validating API: hr_assignment_api.update_cwk_asg');
           rollback;
       else
          dbms_output.put_line('Success ');
          commit;
       end if;
      
    elsif l_mode = 'CRITERIA' then
    
       /* This API updates attributes of a contingent worker assignment that may
       * affect the entitlement to element entries - currently Contingent Workers may
       * not have element entries so this API is reserved for future use.
       * The API updates the attributes of assignment.
       */
       hr_assignment_api.update_cwk_asg_criteria
                            ( p_effective_date                =>  trunc(sysdate)
                            , p_datetrack_update_mode         =>  'CORRECTION'
                            , p_assignment_id                 =>  l_assignment_id
                            , p_validate                      =>  false --lb_validate_mode
                            , p_called_from_mass_update       =>  false
                            , p_position_id                   =>  l_position_id
                            , p_job_id                        =>  l_job_id
                            , p_location_id                   =>  l_location_id
                            , p_organization_id               =>  l_organization_id
                            -- In/Out
                            , p_object_version_number =>  l_asg_object_version_number
                            -- Out
                            ,p_people_group_name            => l_people_group_name        
                            ,p_effective_start_date         => l_effective_start_date     
                            ,p_effective_end_date           => l_effective_end_date       
                            ,p_people_group_id              => l_people_group_id          
                            ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
                            ,p_other_manager_warning        => l_other_manager_warning    
                            ,p_spp_delete_warning           => l_spp_delete_warning       
                            ,p_entries_changed_warning      => l_entries_changed_warning  
                            ,p_tax_district_changed_warning => l_tax_district_changed_warning
                           );
      
       if (l_org_now_no_manager_warning= true or l_other_manager_warning = true or l_spp_delete_warning = true
          or l_tax_district_changed_warning= true or l_entries_changed_warning is not null) then
          dbms_output.put_line('Warning validating API: hr_assignment_api.update_cwk_asg_criteria');
          rollback;
       else
          dbms_output.put_line('Success ');
          commit;
       end if;
    
    end if ;
        
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);                          
end;
--