Table space Space availability

Below is the query to identify the available table space's space in MB, if in case you tend to use the Tablespace in creating database objects.

SELECT b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb, c.totalusedspace UsedMB
  FROM (SELECT tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, sum(bytes)/1024/1024 as tbs_size
          FROM dba_data_files
         GROUP BY tablespace_name
         UNION
        SELECT tablespace_name, sum(bytes)/1024/1024 tbs_size
          FROM dba_temp_files
         GROUP BY tablespace_name) b,
        (SELECT tablespace_name, round(sum(bytes)/1024/1024, 2) as totalusedspace
          FROM dba_segments
         GROUP BY tablespace_name) c
 WHERE a.tablespace_name(+) = b.tablespace_name
   AND a.tablespace_name    = c.tablespace_name;

Print This Post

Monthly Payroll Elements and associated Pay Details

Just want to share the query I have worked on to extract all the Employee Payroll Elements and Pay Elements generated dynamically for an Employee on a single Pay Period June'2012:

*******************************************************************************************************

SELECT person_id, employee_number, assignment_id, full_name,   position_number, position_name, sex, ethnicity, years, employment_category
 --   ,actual_fte
      ,fte
      ,actual_hours, reporting_name, team, service, MAX(NVL(BASIC_PAY  , 0)) "BASIC_PAY",SUM(NVL(ADDITIONAL_PAY, 0)) "ADDITIONAL_PAY"
      ,MAX(NVL(BASIC_PAY  , 0)) + SUM(NVL(ADDITIONAL_PAY, 0)) "GROSS_PAY"
      ,MAX(GROSS_PAY) "NET_PAY"
 FROM (
SELECT papf.person_id, papf.employee_number, paaf.assignment_id, papf.full_name, SUBSTR(pap.name, 1, INSTR(pap.name, '.')-1) "POSITION_NUMBER"
      ,SUBSTR(pap.name, INSTR(pap.name, '.') + 1) "POSITION_NAME", papf.sex, flv.meaning "ETHNICITY"
      ,trunc((to_date('01-JUN-2012', 'DD-MON-RRRR') - papf.date_of_birth)/365) "YEARS"
      ,flv1.meaning "EMPLOYMENT_CATEGORY"
--      ,wcev.fte "ACTUAL_FTE"
      ,pabf.value "FTE"   
      ,DECODE(NVL(paaf.normal_hours, 0) , 0 , NVL(otlh.total_hours, 0) * 4.34, paaf.normal_hours) "ACTUAL_HOURS"
      ,NVL(wpbc.basic_salary, 0) "BASIC_PAY"
      ,NVL(wpbc1.basic_salary, 0) "ADDITIONAL_PAY"
      ,wpbc1.reporting_name       "REPORTING_NAME"
      ,NVL(bp1.gross_pay, 0) "GROSS_PAY"
      ,DECODE(pap.position_id, 10297, 'CE Office', whg.name) "TEAM"
      ,DECODE(pap.position_id, 10297, WBC_SERVICE_UNIT_NAME(83), whg.name) "SERVICE"
  FROM per_all_people_f               papf
      ,per_all_assignments_f          paaf
      ,per_all_positions              pap
      ,fnd_lookup_types               flt
      ,fnd_lookup_values              flv
      ,fnd_lookup_types               flt1
      ,fnd_lookup_values              flv1
      ,wbc_otl_hours_v                otlh
      ,per_assignment_budget_values_f pabf
--    ,wdc_current_establishment_v wcev
      ,wbc_hr_org_information_mv   whg
      ,(SELECT prb1.assignment_id, MAX( NVL( DECODE(defined_balance_id, 163, NVL(balance_value, 0), 0), 0) ) "GROSS_PAY"
          FROM pay_run_balances   prb1
              ,gl_period_statuses gps1
         WHERE /*prb1.defined_balance_id in (163, 166, 238)
           AND */gps1.application_id = 101
           AND prb1.effective_date = gps1.end_date
           AND gps1.period_name = 'Jun-12'
         GROUP BY prb1.assignment_id) bp1
      ,(SELECT pasa.assignment_id  "ASSIGNMENT_ID"
             , petf.element_name   "ELEMENT_NAME"
             , petf.reporting_name "REPORTING_NAME"
             , petf.description    "DESCRIPTION"
             , pivf.name           "NAME"
             , SUM(prrv.result_value)   "BASIC_SALARY"
          FROM pay_run_types_f        prt
              ,pay_run_results        prrt
              ,pay_run_result_values  prrv
              ,pay_input_values_f     pivf
              ,pay_payroll_actions    papa
              ,pay_assignment_actions pasa
              ,pay_element_types_f    petf
         WHERE prrt.assignment_action_id = pasa.assignment_action_id
           and prrt.element_type_id = petf.element_type_id
           and pasa.run_type_id = prt.run_type_id
           and prrv.run_result_id = prrt.run_result_id  
           and prrv.input_value_id = pivf.input_value_id
           and trunc(papa.effective_date) between trunc(pivf.effective_start_date) and trunc(pivf.effective_end_date)
           and trunc(papa.effective_date) between trunc(petf.effective_start_date) and trunc(petf.effective_end_date)
           and papa.business_group_id = petf.business_group_id
           and papa.business_group_id = pivf.business_group_id
           and to_char(papa.effective_date, 'DD-MON-RRRR') = '30-JUN-2012'
           and papa.business_group_id = &business_group_id
           and papa.payroll_id = &Payroll_id
           and papa.action_status = 'C'
           and papa.action_type = 'R'  
           and pasa.payroll_action_id = papa.payroll_action_id
           and pivf.name = 'Pay Value'
           and petf.reporting_name = 'Basic Salary'
         GROUP BY assignment_id
             , petf.element_name
             , petf.reporting_name
             , petf.description
             , pivf.name ) wpbc
      ,(SELECT pasa.assignment_id  "ASSIGNMENT_ID"
             , petf.element_name   "ELEMENT_NAME"
             , petf.reporting_name "REPORTING_NAME"
             , petf.description    "DESCRIPTION"
             , pivf.name           "NAME"
             , prrv.result_value   "BASIC_SALARY"
          FROM pay_run_types_f        prt
              ,pay_run_results        prrt
              ,pay_run_result_values  prrv
              ,pay_input_values_f     pivf
              ,pay_payroll_actions    papa
              ,pay_assignment_actions pasa
              ,pay_element_types_f    petf
              ,pay_element_classifications pecl
         WHERE prrt.assignment_action_id = pasa.assignment_action_id
           and prrt.element_type_id = petf.element_type_id
           and pasa.run_type_id = prt.run_type_id
           and prrv.run_result_id = prrt.run_result_id  
           and prrv.input_value_id = pivf.input_value_id
           and trunc(papa.effective_date) between trunc(pivf.effective_start_date) and trunc(pivf.effective_end_date)
           and trunc(papa.effective_date) between trunc(petf.effective_start_date) and trunc(petf.effective_end_date)
           and papa.business_group_id = petf.business_group_id
           and papa.business_group_id = pivf.business_group_id
           and to_char(papa.effective_date, 'DD-MON-RRRR') = '30-JUN-2012'
           and papa.business_group_id = &business_group_id
           and papa.payroll_id = &Payroll_id
           and papa.action_status = 'C'
           and papa.action_type = 'R'  
           and pasa.payroll_action_id = papa.payroll_action_id
           and pivf.name = 'Pay Value'
           and petf.classification_id = pecl.classification_id
           and pecl.classification_name = 'Earnings'
           and pecl.legislation_code = 'GB'
           and petf.reporting_name <> 'Basic Salary') wpbc1
       --,wbc_user_assignments wua          
  WHERE /*papf.person_id = wua.person_id
    AND wua.assignment_id = wpbc.assignment_id(+)
    and */paaf.assignment_id = wpbc.assignment_id(+)
    AND paaf.assignment_id = wpbc1.assignment_id(+)
    AND papf.person_id = otlh.resource_id(+)
    AND trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(papf.effective_start_date) AND trunc(NVL(papf.effective_end_date, sysdate))
    AND trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(paaf.effective_start_date) AND trunc(NVL(paaf.effective_end_date, sysdate))
    AND papf.person_id = paaf.person_id   
    AND paaf.position_id = pap.position_id
--  and wcev.person_id = papf.person_id
--  and wcev.assignment_id = paaf.assignment_id
    and paaf.organization_id = pap.organization_id   
    and trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(pap.date_effective) and trunc(NVL(pap.date_end, sysdate))
    and paaf.job_id = pap.job_id
    and paaf.business_group_id = pap.business_group_id
    and flv.lookup_code = papf.per_information1
    and flt.lookup_type = 'ETH_TYPE'
    and flt.lookup_type = flv.lookup_type
    and flv1.lookup_code = paaf.employment_category
    and flt1.lookup_type = 'EMP_CAT'
    and flt1.lookup_type = flv1.lookup_type
    and paaf.assignment_id = bp1.assignment_id(+)
    and paaf.organization_id = whg.org_id
    and paaf.assignment_id = pabf.assignment_id
    and pabf.unit = 'FTE'
    and trunc(to_date('30-JUN-2012', 'DD-MON-RRRR')) between trunc(pabf.effective_start_date) and trunc(pabf.effective_end_date)
    )
GROUP BY person_id, employee_number, assignment_id, full_name,   position_number, position_name, sex, ethnicity, years, employment_category
--      ,actual_fte
        ,fte
        ,actual_hours ,reporting_name , team, service)

*******************************************************************************************************

Print This Post

Oracle Apps User Password

Being an Oracle consultant, Sometimes you might need the Oracle Applications passwords having provided only the database access and you want to login through the common user credentials to check a few screens in a test instance.

Below is the simple way of executing a decrypt java function.

-- Package Spec

CREATE OR REPLACE PACKAGE get_pwd
AS
FUNCTION decrypt (
KEY IN VARCHAR2
,VALUE IN VARCHAR2
)
RETURN VARCHAR2;
END get_pwd;
/

-- Package Body

CREATE OR REPLACE PACKAGE BODY get_pwd
AS
FUNCTION decrypt (
KEY IN VARCHAR2
,VALUE IN VARCHAR2
)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

/** Run Login Apps User on Toad/Oracle-- Will get apps password */
SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL));

--Run this on toad will get all the usernames and passwords
SELECT usertable.user_name
, (SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE
UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))))
,usertable.encrypted_user_password)
FROM DUAL) AS encrypted_user_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ('&username');

Creating a read only Responsibility

Here I am going to show you 2 ways to create a read only resposibility:

1. Form Function Method
2. custom.pll

Form Function Method:
1. Identify the menu attached to the responsibility that you wish to make it a Read only Responsibility
2. Identify the form functions that are attached to this menu.
3. Define a new form function exactly similar to the existing one but with the option QUERY_ONLY="YES" at the location "Application"-->"function"-->"Form"-->"Parameter".
4. The Parameter sets the function in Query Only mode.
5. Final step is to create a custom menu with the new read only functions created and attach the menu to the  new responsibility.

CUSTOM.pll (Reference metalink note: 363298.1)
1.Open the CUSTOM.pll in the form builder
2.Add the following sample code in the procedure event for the event ‘WHEN-NEW-FORM-INSTANCE’

BEGIN

IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
   IF FND_PROFILE.VALUE('USER_NAME')='<USER_NAME>' THEN

      BEGIN -- Begin Query Only Mode

      COPY('Entering app_form.query_only_mode.','global.frd_debug');
      COPY('YES', 'PARAMETER.QUERY_ONLY');
      APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
      APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
      formname := NAME_IN('system.current_form');
      blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);

      WHILE (blockname is not null) LOOP

        IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN
           SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
           SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
           SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);
        END IF;

        blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);
      END LOOP;

    END; -- End Query Only Mode

   END IF;
END IF;

END;

3. Compile the Custom.pll and place it under the directory $AU_TOP/resource



Print This Post

Emailing a CLOB Document

The below code snippet is quite useful for emailing a CLOB document, which I have used for emailing a list of lines can't accommodate to a LONG object.
This can as well be used to write the output of a concurrent program to the CLOB and emailing it.

*********************************************************
DECLARE
lc_conn utl_smtp.connection; 
lc_sender             VARCHAR2(200) := 'abc@gmail.com';
lc_output_email   VARCHAR2(200) := 'xyz@gmail.com';
lc_filedata            VARCHAR2(32767);
crlf                        VARCHAR2(2)  := chr(13)||chr(10);
lc_subject             VARCHAR2(2000);
l_boundary           VARCHAR2(50) := '----=*#abc1234321cba#*=';
lc_filedata            VARCHAR2(32767);
lc_data                 CLOB := '';

BEGIN
lc_conn := utl_smtp.Open_Connection('127.0.0.1', 25);
utl_smtp.Helo(lc_conn, '127.0.0.1');
utl_smtp.Mail(lc_conn, lc_sender);
utl_smtp.rcpt(lc_conn, lc_output_email); 
utl_smtp.open_data(lc_conn);
lc_filedata := lc_filedata || 'Date:' || TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') || crlf;
lc_filedata := lc_filedata || 'To: ' || lc_output_email || crlf;
lc_filedata := lc_filedata || 'From: ' || lc_sender || crlf;
lc_filedata := lc_filedata || 'Subject: ' || lc_subject || crlf;
lc_filedata := lc_filedata || 'MIME-Version: 1.0' ||  crlf;  
lc_filedata := lc_filedata || 'Content-Type: multipart/mixed; boundary=' ||chr(34)||l_boundary|| chr(34)||crlf;
lc_filedata := lc_filedata || '--'||l_boundary|| crlf;
lc_filedata := lc_filedata || 'Content-Type: text/plain;'|| crlf ||
                                           'Content-Transfer_Encoding: 7bit'|| crlf ||
                                           crlf || lc_body || crlf || crlf ||
                                           '--'||l_boundary|| crlf ||
                                           'Content-Type: text/plain;'|| crlf ||
                                           ' name="'||lc_filename||'"'|| crlf ||
                                           'Content-Transfer_Encoding: 8bit'|| crlf ||
                                          'Content-Disposition: attachment;'|| crlf ||
                                          ' filename="'||lc_filename||'"'|| crlf || crlf;

  -- Headers from lc_filedata
dbms_lob.createtemporary(lc_data, false, 10);
dbms_lob.write(lc_data, length(lc_filedata), 1, lc_filedata);

ln_temp := dbms_lob.getlength(lc_data) + 1;
 
-- Looping to the Table of lines to be emailed
FOR j in 1..lg_i LOOP
    ln_temp := dbms_lob.getlength(lc_data) + 1;
    IF dbms_lob.getlength(lg_varchar2(j)) > 0 THEN
       dbms_lob.write(lc_data, length(lg_varchar2(j)), ln_temp, lg_varchar2(j));
    END IF;
END LOOP;
 
ln_temp := dbms_lob.getlength(lc_data) + 1;
 
ln_temp := 1;
ln_amount := 1900;
 
WHILE ln_temp < dbms_lob.getlength(lc_data) LOOP
   utl_smtp.write_data(g_Conn, dbms_lob.substr(lc_data, ln_amount, ln_temp));
   ln_temp := ln_temp + ln_amount;
   ln_amount := least(1900, dbms_lob.getlength(lc_data) - ln_amount);
END LOOP;
 
utl_smtp.write_data(lc_Conn, crlf||crlf);
utl_smtp.close_data(lc_conn);
utl_smtp.quit(lc_conn);

*********************************************************

you can also refer to the link for different ways to email from pl/sql code.

  Print This Post

IE8, Firefox on Windows 7 with Jinitiator 1.3.1.21

I had real hard time to understand the reason why IE8.0, Firefox keep crashing in compatibility with Jinitiator 1.3.1.21 and finally found that its the jvm.dll and Browser Plugin configurations impacting them, Please follow the following guidelines to sort this out,


1.Firstly Install JInitiator version '1.3.1.21', you can download this with the link oracle redirects you while opening the Orale Applications 11i

2.Please confirm the Java version is 1.6(build 1.6.0_07-b06).

3. Move the jvm.dll in 'Java\jre1.6.0_07\bin\client' to JVM.DLL in 'JInitiator 1.3.1.21\bin\hotspot' directory(Take a backup before moving)

Firefox:
Make sure that Enable Java checkbox is Unchecked and Restart firefox and it should work perfectly fine for Firefox

IE 8.0
Make sure the following settings are corrected in IE8.0:
Tools -> Options -> Advanced -> Uncheck "Enable third-party browser extensions"
Restart IE 8.0 and it should work perfectly fine for Firefox


Print This Post

Schedule a Concurrent Program to run every week day and Working Hours

Oracle Provided much flexibility in scheduling the Concurrent Program Requests, but still at times some of the customers require their Programs to run only on Certain Hours and Certain days.
Consider a program that should be run only on Weekdays and only on Working Hours, herein are the steps to run the Program on Working hours on Weekdays:

1. Define Workshift with the Required Times and Days
    Sysadmin -> Concurrent -> Manager -> Workshifts
2. Define a Concurrent Manager and associate a Program for a Particular Manager
    Sysadmin -> Concurrent -> Manager -> Define
3. Associate the Specialization Rules with the program to be associated to the Concurrent Manager
    Sysadmin -> Concurrent -> Manager -> Define -> Specialization Rules
    Include the Program.

4. Now you can schedule the Program the while submitting the Concurrent Request and the associated Concurrent Manager will work only for the associated Work Shift defined. However if the associated Manager is not available then any Standard manager that exists in the Oracle instance will try to run the Job. To stop them from running the Job it will be necessary to add an Exclusion Rule to those Standard Managers to explicitly tell them not to run the Jobs. However in the exclusion hours of the Work Shifts the Programs Phase/status will be of Inactive/No Manager.

Print This Post

Billing at the Top Task Level

Oracle Project Billing Error:
Please fund only at the task level for this project as the customer at the top task or the invoice method at Top Task or the Invoice Method at Top Task billing option is enabled.

This error occurs when the user tries to allocate funds at the Project Level rather than Task level by enabling the Top Task Level Billing option for the Project.

You can avoid this error by disabling the Customer at the Top Task Flag in the Billing Setup Form of the Project. Oracle Projects generally while generating/updating the Agreements for the Project it checks that if no Task is provided then the Project should not be setup to Bill at the Top Task.


Print This Post

PO Line Cancel

Po Line which the organizations no longer wanted to receive an Invoice from the Supplier should be cancelled to avoid any open Commitments to the Project PO Line is associated with, and In cancelling the PO line it could raise an error as below,

"Line # Shipment # Quantity Billed # greated than Quantity received #".

The problem could be that the PO Line is Matched ina 3-Way Functionality, and inroder to cancel the line that has been billed and not received, the line should have match approval level set to "2-Way" so that it does not expect any receipt by setting the receipt_required_flag='N'.

If Match Approval Level is 3-way or 4-way, application checks whether any of the shipments on PO or Release is having billed quantity greater than received or delivered quantity. If so, user is restricted from canceling the PO or the Release.

Process to Cancel the PO Line,

1. Query the PO in PO Summary Form.
2. Query the Line to Cancel
3. Go to Tools --> Control --> Cancel the PO Line.

Print This Post

Transaction Controls in Projects

This section briefs about the Transaction Controls that can be used for a Project. We have 2 types of Transaction Controls to be implemented to a Project as listed below,

Inclusive Transaction Controls: Inclusive transaction controls limit charges to only the transaction controls entered; Oracle Projects then rejects any charges that are not listed as chargeable in the transaction controls. You make your transaction controls inclusive by checking the Limit to Transaction Controls box on the Transaction Controls window. Herein is the example screenshot where we allow only Labour Charges for the Project ‘P1’ and Task ‘T1’.




Exclusive transaction controls: Exclusive transaction controls allow all charges except those that are specified as non-chargeable in the transaction controls. Oracle Projects
defaults to exclusive transaction controls, and Limit to Transaction Controls is Unchecked as below,




Print This Post

My Useful Queries

I use the following queries very frequently on my use of Oracle E-Business suite 11i,

1. Identify Buyers without a Position assigned if using Position Hierarchy

SELECT paf.person_id, ppf.full_name
FROM per_all_assignments_f paf
,po_agents poa
,per_people_f ppf
,per_assignment_status_types past
WHERE trunc(sysdate) between TRUNC(paf.effective_start_date) AND TRUNC(paf.effective_end_date)
AND paf.person_id = poa.agent_id
AND paf.person_id = ppf.person_id
AND TRUNC(sysdate) between TRUNC(ppf.effective_start_date) and TRUNC(ppf.effective_end_date)
AND paf.position_id is null
AND paf.job_id is not null
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND past.user_status = 'Active Assignment'
AND TRUNC(sysdate) between TRUNC(poa.start_date_active) AND TRUNC(NVL(poa.end_date_active, sysdate))
ORDER BY paf.person_id;

2. Retrive Timecard details with Resource, and Timecard Start time and End Time

SELECT DISTINCT PP.SEGMENT1 "Project Number", PT.TASK_NUMBER "Task number", HTA.ATTRIBUTE3, DAY.START_TIME, DET.MEASURE, DET.APPROVAL_STATUS, DET.COMMENT_TEXT
FROM
(SELECT TIME_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_ID ,
PARENT_BUILDING_BLOCK_OVN ,
OBJECT_VERSION_NUMBER ,
MEASURE ,
RESOURCE_ID ,
APPROVAL_STATUS ,
COMMENT_TEXT, TRANSLATION_DISPLAY_KEY
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL'
) DET ,
(SELECT TIME_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_ID ,
PARENT_BUILDING_BLOCK_OVN ,
OBJECT_VERSION_NUMBER ,
START_TIME ,
APPROVAL_STATUS, RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DAY'
) DAY ,
(SELECT TIME_BUILDING_BLOCK_ID,
START_TIME ,
APPROVAL_STATUS ,
OBJECT_VERSION_NUMBER ,
COMMENT_TEXT, RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD'
) TC
,HXC_TIME_ATTRIBUTE_USAGES HTAU
,HXC_TIME_ATTRIBUTES HTA
,PA_PROJECTS_ALL PP
,PA_TASKS PT
WHERE DAY.PARENT_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
AND DAY.PARENT_BUILDING_BLOCK_OVN = TC.OBJECT_VERSION_NUMBER
AND DET.PARENT_BUILDING_BLOCK_ID = DAY.TIME_BUILDING_BLOCK_ID
AND DET.PARENT_BUILDING_BLOCK_OVN = DAY.OBJECT_VERSION_NUMBER
AND TRUNC(DAY.START_TIME) BETWEEN &START_TIME AND &END_TIME
AND DAY.RESOURCE_ID = &resource_id
AND TC.OBJECT_VERSION_NUMBER =
(SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD'
AND TIME_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
)
AND DET.OBJECT_VERSION_NUMBER =
(SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL'
AND TIME_BUILDING_BLOCK_ID = DET.TIME_BUILDING_BLOCK_ID
)
AND DET.TIME_BUILDING_BLOCK_ID = HTAU.TIME_BUILDING_BLOCK_ID
AND DET.OBJECT_VERSION_NUMBER = HTAU.TIME_BUILDING_BLOCK_OVN
AND HTAU.TIME_ATTRIBUTE_ID = HTA.TIME_ATTRIBUTE_ID
AND HTA.ATTRIBUTE1 = TO_CHAR(PP.PROJECT_ID)
AND HTA.ATTRIBUTE2 = TO_CHAR(PT.TASK_ID)
AND HTA.ATTRIBUTE5 = 'ST'
ORDER BY 4 ;

3. Retrive the Jobs logged by the user for a particulat Period

SELECT wpe.wip_entity_id, wpe.wip_entity_name, wpt.transaction_date, wpt.transaction_id
             , hou.name "Organization"
             ,ppf.full_name ,pp.segment1, pt.task_number, wpt.transaction_quantity
             ,wpt.primary_quantity, wpt.actual_resource_rate
             ,ppf.employee_number, ppf.full_name, wpt.attribute_category, wpt.attribute15
  FROM wip_transactions wpt
             ,wip_entities wpe
             ,pa_projects_all pp
             ,pa_tasks pt
             ,hr_all_organization_units hou
             ,per_all_people_f ppf
WHERE wpt.employee_id = &resource_id
  AND TRUNC(wpt.transaction_date) between TRUNC(TO_DATE(&from_date,'DD-MON-RRRR'))
  AND TRUNC(TO_DATE(&to_date,'DD-MON-RRRR'))
  AND wpt.wip_entity_id = wpe.wip_entity_id
  AND wpt.organization_id = hou.organization_id
  AND ppf.person_id = wpt.employee_id
  AND TRUNC(SYSDATE) between TRUNC(ppf.effective_start_date) and    TRUNC(ppf.effective_end_date)
  AND pp.project_id = wpt.project_id
  AND pt.task_id = wpt.task_id
ORDER BY wpt.transaction_id;

4.Identify Timecards not transferred to Projects:


SELECT DISTINCT ppf.employee_number "Employee Number"
      ,ppf.full_name "Full name"
      ,day.start_time "Day Worked"
      ,det.measure "Hours"
      ,tc.time_building_block_id "Timecard id"
      ,det.comment_text "Comments"
  FROM hxc_time_building_blocks TC
      ,hxc_time_building_blocks DAY
      ,hxc_time_building_blocks DET
      ,hxc_time_attribute_usages HTAU
      ,hxc_time_attributes HTA
      ,hxc_timecard_summary HTS
      ,per_all_people_f ppf
--      ,pa_projects_all pp
--      ,pa_tasks pt
 WHERE tc.scope = 'TIMECARD' 
   AND tc.approval_status = 'SUBMITTED'
   AND day.parent_building_block_id = tc.time_building_block_id
   AND day.scope = 'DAY' 
   AND day.approval_status = 'SUBMITTED'
   AND DET.parent_building_block_id = day.time_building_block_id
   AND det.scope = 'DETAIL' 
   AND det.approval_status = 'SUBMITTED'
   AND HTAU.time_building_block_id = tc.time_building_block_id
   AND HTAU.time_building_block_ovn = tc.object_version_number
   AND HTAU.time_attribute_id = hta.time_attribute_id
   AND hta.attribute_category(+) = 'SECURITY'
   AND hts.timecard_id = tc.time_building_block_id
   AND hts.resource_id = ppf.person_id
   AND TRUNC(SYSDATE) between TRUNC(NVL(ppf.effective_start_date, SYSDATE))
                         AND  TRUNC(NVL(ppf.effective_end_date, SYSDATE))
   AND TC.OBJECT_VERSION_NUMBER =
        (SELECT MAX(OBJECT_VERSION_NUMBER)
           FROM HXC_TIME_BUILDING_BLOCKS
          WHERE SCOPE = 'TIMECARD'
            AND TIME_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
         )
   AND DET.OBJECT_VERSION_NUMBER =
        (SELECT MAX(OBJECT_VERSION_NUMBER)
           FROM HXC_TIME_BUILDING_BLOCKS
          WHERE SCOPE = 'DETAIL'
            AND TIME_BUILDING_BLOCK_ID = DET.TIME_BUILDING_BLOCK_ID
        )
   AND DET.PARENT_BUILDING_BLOCK_OVN =
       (SELECT MAX(OBJECT_VERSION_NUMBER)
          FROM HXC_TIME_BUILDING_BLOCKS
         WHERE SCOPE = 'DAY'
           AND TIME_BUILDING_BLOCK_ID = DAY.TIME_BUILDING_BLOCK_ID   
       )     
   AND NOT EXISTS
       ( SELECT 'X'
           FROM PA_EXPENDITURE_ITEMS_ALL
          WHERE ORIG_TRANSACTION_REFERENCE = DET.TIME_BUILDING_BLOCK_ID||':'||DET.OBJECT_VERSION_NUMBER)
ORDER BY 1;



5. Concurrent Programs submitted today:

select user_concurrent_program_name, program, requestor, actual_start_date, actual_completion_date, completion_text, has_sub_request
       ,flv.meaning "Status", flv1.meaning "Phase"
  from FND_CONC_REQ_SUMMARY_V fcrs
      ,fnd_lookup_values flv
      ,fnd_lookup_values flv1
 where flv.lookup_type = 'CP_STATUS_CODE'
   and flv.lookup_code = fcrs.status_code
   and flv1.lookup_type = 'CP_PHASE_CODE'
   and flv1.lookup_code = fcrs.phase_code
   and trunc(actual_start_date) = trunc(sysdate)
 order by actual_start_date asc;

6. Scheduled Concurrent Programs:

select fap.application_name "Application name"
       ,NVL(fcr.description, fcp.user_concurrent_program_name) "Program name"
       ,fcr.request_id,fcr.resubmit_interval||' '||fcr.resubmit_interval_unit_code "Resubmit Interval"
       ,fcr.resubmit_time "RESUBMIT_TIME"
       ,fcr.resubmitted "RESUBMITTED"
       ,flv.meaning "PHASE"
       ,flv1.meaning "STATUS"
       ,fcr.argument_text "ARGUMENTS"
       ,fcr.request_type
  from apps.fnd_concurrent_requests fcr
      ,apps.fnd_lookup_values flv
      ,apps.fnd_lookup_values flv1
      ,apps.fnd_concurrent_programs_vl fcp
      ,apps.fnd_application_vl fap
 where fcr.phase_code = flv.lookup_code
   and flv.language = 'US'
   and flv.lookup_type = 'CP_PHASE_CODE'
   and fcr.status_code = flv1.lookup_code
   and flv1.language = 'US'
   and flv1.lookup_type = 'CP_STATUS_CODE'
   and flv.view_application_id = 0
   and flv1.view_application_id = 0
   and fcp.concurrent_program_id = fcr.concurrent_program_id
   and fcr.phase_code = 'P' -- 'Pending'
   and fcr.status_code in ('I', 'Q')
   and fap.application_id = fcr.program_application_id
  order by request_id desc;

7. Release 11i Bank, Bank branches and Bank Accounts:

SELECT apba.org_id,hou.name,apbb.bank_name, apbb.bank_number, apbb.bank_num,apbb.institution_type
      ,apbb.bank_branch_name ,apba.bank_account_name, apba.bank_account_num,  apba.set_of_books_id
      ,apbb.address_line1 ,apbb.address_line2 ,apbb.address_line3 ,apbb.city, apbb.state, apbb.zip, apbb.country, apbb.province
      ,apbb.end_date, apbb.active_date
      ,apba.currency_code,apba.iban_number, apba.inactive_date,apbu.start_date, apbu.end_date, apbu.primary_flag, apba.bank_branch_id
  FROM ap_bank_account_uses_all apbu
      ,ap_bank_accounts_all apba
      ,ap_bank_branches apbb
      ,hr_all_organization_units hou  
 WHERE apbu.external_bank_account_id = apba.bank_account_id
   AND apba.bank_branch_id = apbb.bank_branch_id
   AND trunc(nvl(apba.inactive_date, sysdate+1)) > trunc(sysdate)
   AND trunc(sysdate) between trunc(nvl(apbu.start_date, sysdate)) and trunc(nvl(apbu.end_date, sysdate))
   AND trunc(nvl(apbb.end_date, sysdate+1)) > trunc(sysdate)
   AND apbu.org_id = apba.org_id

   AND hou.organization_id = apbu.org_id;


8. Release 11i Supplier, Sites and Bank Extract having invoices from the last 15 Months:

SELECT DISTINCT *
FROM (
SELECT POV.VENDOR_NAME
      ,POV.VENDOR_ID
      ,POV.VENDOR_NAME_ALT
      ,POV.SEGMENT1
      ,POV.ENABLED_FLAG
      ,POV.VENDOR_TYPE_LOOKUP_CODE
      ,POV.PAY_DATE_BASIS_LOOKUP_CODE
      ,POV.PAYMENT_PRIORITY
      ,POV.PAYMENT_METHOD_LOOKUP_CODE
      ,POV.TERMS_DATE_BASIS
      ,POV.QTY_RCV_TOLERANCE
      ,POV.QTY_RCV_EXCEPTION_CODE
      ,POV.ENFORCE_SHIP_TO_LOCATION_CODE
      ,POV.DAYS_EARLY_RECEIPT_ALLOWED      
      ,POV.DAYS_LATE_RECEIPT_ALLOWED        
      ,POV.RECEIPT_DAYS_EXCEPTION_CODE      
      ,POV.RECEIVING_ROUTING_ID            
      ,POV.ALLOW_SUBSTITUTE_RECEIPTS_FLAG  
      ,POV.ALLOW_UNORDERED_RECEIPTS_FLAG    
      ,POV.VAT_REGISTRATION_NUM
      ,POV.BANK_CHARGE_BEARER
      ,POV.MATCH_OPTION      
      ,NULL ECE_TP_LOCATION_CODE
      ,POV.ALLOW_AWT_FLAG
      ,POV.FEDERAL_REPORTABLE_FLAG
      ,POV.OFFSET_TAX_FLAG
      ,POVS.VENDOR_SITE_CODE
      ,POVS.VENDOR_SITE_CODE_ALT
      ,POVS.ADDRESS_LINE1
      ,POVS.ADDRESS_LINE2
      ,POVS.ADDRESS_LINE3
      ,NULL ADDRESS_LINE4
      ,POVS.CITY
      ,POVS.STATE
      ,POVS.ZIP
      ,POVS.PROVINCE
      ,POVS.COUNTRY
      ,POVS.AREA_CODE
      ,POVS.PHONE
      ,POVS.FAX_AREA_CODE
      ,POVS.FAX
      ,POVS.TELEX
      ,POVS.PURCHASING_SITE_FLAG
      ,POVS.PAY_SITE_FLAG                    
      ,POVS.PRIMARY_PAY_SITE_FLAG            
      ,POVS.SHIP_TO_LOCATION_ID              
      ,NULL "SHIP_TO_LOCATION_CODE"            
      ,POVS.BILL_TO_LOCATION_ID              
      ,NULL "BILL_TO_LOCATION_CODE"            
      ,POVS.PAYMENT_METHOD_LOOKUP_CODE "SS_PAYMENT_METHOD_LOOKUP_CODE"
      ,POVS.TERMS_DATE_BASIS "SS_TERMS_DATE_BASIS"
      ,POVS.VAT_CODE            
      ,POVS.PAY_GROUP_LOOKUP_CODE            
      ,POVS.PAYMENT_PRIORITY "SS_PAYMENT_PRIORITY"
      ,POVS.TERMS_ID                        
      ,NULL "TERMS_NAME"                      
      ,POVS.INVOICE_AMOUNT_LIMIT            
      ,POVS.PAY_DATE_BASIS_LOOKUP_CODE "SS_PAY_DATE_BASIS_LOOKUP_CODE"
      ,POVS.ALWAYS_TAKE_DISC_FLAG            
      ,POVS.INVOICE_CURRENCY_CODE            
      ,POVS.PAYMENT_CURRENCY_CODE            
      ,POVS.HOLD_ALL_PAYMENTS_FLAG          
      ,POVS.TAX_REPORTING_SITE_FLAG          
      ,POVS.ORG_ID                          
      ,hou.name "OPERATING_UNIT_NAME"              
      ,POVS.DEFAULT_PAY_SITE_ID              
      ,POVS.MATCH_OPTION "SS_MATCH_OPTION"                  
      ,POVS.TOLERANCE_ID                    
      ,NULL "TOLERANCE_NAME"
      ,NULL "SERVICES_TOLERANCE_ID"
      ,NULL "SERVICES_TOLERANCE_NAME"
      ,NULL "PAYMENT_METHOD_CODE"              
      ,POVS.SUPPLIER_NOTIF_METHOD            
      ,POVS.EMAIL_ADDRESS                    
      ,POVS.REMITTANCE_EMAIL
      ,POVS.ALLOW_AWT_FLAG "SS_ALLOW_AWT_FLAG"
      ,POVS.AWT_GROUP_ID
      ,POVS.AUTO_TAX_CALC_FLAG
      ,POVS.VAT_REGISTRATION_NUM "SS_VAT_REGISTRATION_NUM"
      ,POVS.OFFSET_TAX_FLAG "SS_OFFSET_TAX_FLAG"
      ,POVS.AP_TAX_ROUNDING_RULE
      ,POVS.AMOUNT_INCLUDES_TAX_FLAG
      ,POVS.AUTO_TAX_CALC_OVERRIDE
      ,hou.attribute13 "ORG_COUNTRY"
      ,apbb.bank_name
      ,apbb.bank_number
      ,apbb.institution_type
      ,NULL "BANK_PARTY_ID"
      ,apbb.bank_branch_name "BRANCH_NAME"
      ,NULL "BRANCH_NUMBER"
      ,NULL "BRANCH_TYPE"
      ,NULL "BIC"
      ,NULL "BRANCH_CODE"
      ,apbb.address_line1 "SITE_ADDRESS_LINE1"
      ,apbb.address_line2 "SITE_ADDRESS_LINE2"
      ,apbb.address_line3 "SITE_ADDRESS_LINE3"
      , NULL              "SITE_ADDRESS_LINE4"
      ,apbb.city          "SITE_CITY"
      ,apbb.state         "SITE_STATE"
      ,apbb.county        "SITE_COUNTY"
      ,apbb.zip           "SITE_ZIP"
      ,apbb.province      "SITE_PROVINCE"
      ,apba.bank_account_name "BANK_ACCOUNT_NAME"
      ,apba.bank_account_num "BANK_ACCOUNT_NUM"
      ,NULL                  "CHECK_DIGITS"
      ,apba.currency_code "CURRENCY"
      ,apba.iban_number      "IBAN"
      ,NULL "SECONDARY_ACCOUNT_REFERENCE"
      ,NULL "PRIMARY_PAY_FLAG"
      ,apbu.primary_flag "PRIMARY ACCOUNT"
  from apps.po_vendors pov
      ,apps.po_vendor_sites_all povs
      ,apps.hr_all_organization_units hou
      ,apps.ap_bank_account_uses_all apbu
      ,apps.ap_bank_accounts_all apba
      ,apps.ap_bank_branches apbb
 where povs.org_id = hou.organization_id
   and pov.vendor_id = povs.vendor_id
   and pov.enabled_flag = 'Y'
   and NVL(pov.one_time_flag, 'N') <> 'Y' -- with one time flag
   and trunc( NVL(povs.inactive_date, sysdate)) >= trunc(sysdate)
   and trunc(sysdate) between trunc(hou.date_from) and trunc(nvl(hou.date_to, sysdate))
   and (povs.vendor_id, povs.vendor_site_id) in ( select distinct vendor_id, vendor_site_id --, vendor_site_id
                                                    from apps.ap_invoices_all
                                                   where trunc(creation_date) >= trunc(add_months(sysdate, -15)) ) -- Have invoices for the last 15 Months
   and apbu.vendor_id = povs.vendor_id
   and apbu.vendor_site_id = povs.vendor_site_id
   and apbu.external_bank_account_id = apba.bank_account_id
   and apba.bank_branch_id = apbb.bank_branch_id
   and trunc(nvl(apba.inactive_date, sysdate+1)) > trunc(sysdate)
   and trunc(sysdate) between trunc(nvl(apbu.start_date, sysdate)) and trunc(nvl(apbu.end_date, sysdate))
   and trunc(nvl(apbb.end_date, sysdate+1)) > trunc(sysdate)
   and apbu.org_id = apba.org_id
   and hou.organization_id = apbu.org_id                                            
UNION
SELECT POV.VENDOR_NAME
      ,POV.VENDOR_ID
      ,POV.VENDOR_NAME_ALT
      ,POV.SEGMENT1
      ,POV.ENABLED_FLAG
      ,POV.VENDOR_TYPE_LOOKUP_CODE
      ,POV.PAY_DATE_BASIS_LOOKUP_CODE
      ,POV.PAYMENT_PRIORITY
      ,POV.PAYMENT_METHOD_LOOKUP_CODE
      ,POV.TERMS_DATE_BASIS
      ,POV.QTY_RCV_TOLERANCE
      ,POV.QTY_RCV_EXCEPTION_CODE
      ,POV.ENFORCE_SHIP_TO_LOCATION_CODE
      ,POV.DAYS_EARLY_RECEIPT_ALLOWED      
      ,POV.DAYS_LATE_RECEIPT_ALLOWED        
      ,POV.RECEIPT_DAYS_EXCEPTION_CODE      
      ,POV.RECEIVING_ROUTING_ID            
      ,POV.ALLOW_SUBSTITUTE_RECEIPTS_FLAG  
      ,POV.ALLOW_UNORDERED_RECEIPTS_FLAG    
      ,POV.VAT_REGISTRATION_NUM
      ,POV.BANK_CHARGE_BEARER
      ,POV.MATCH_OPTION      
      ,NULL ECE_TP_LOCATION_CODE
      ,POV.ALLOW_AWT_FLAG
      ,POV.FEDERAL_REPORTABLE_FLAG
      ,POV.OFFSET_TAX_FLAG
      ,POVS.VENDOR_SITE_CODE
      ,POVS.VENDOR_SITE_CODE_ALT
      ,POVS.ADDRESS_LINE1
      ,POVS.ADDRESS_LINE2
      ,POVS.ADDRESS_LINE3
      ,NULL "ADDRESS_LINE4"
      ,POVS.CITY
      ,POVS.STATE
      ,POVS.ZIP
      ,POVS.PROVINCE
      ,POVS.COUNTRY
      ,POVS.AREA_CODE
      ,POVS.PHONE
      ,POVS.FAX_AREA_CODE
      ,POVS.FAX
      ,POVS.TELEX
      ,POVS.PURCHASING_SITE_FLAG
      ,POVS.PAY_SITE_FLAG                    
      ,POVS.PRIMARY_PAY_SITE_FLAG            
      ,POVS.SHIP_TO_LOCATION_ID              
      ,NULL "SHIP_TO_LOCATION_CODE"            
      ,POVS.BILL_TO_LOCATION_ID              
      ,NULL "BILL_TO_LOCATION_CODE"            
      ,POVS.PAYMENT_METHOD_LOOKUP_CODE "SS_PAYMENT_METHOD_LOOKUP_CODE"
      ,POVS.TERMS_DATE_BASIS "SS_TERMS_DATE_BASIS"
      ,POVS.VAT_CODE            
      ,POVS.PAY_GROUP_LOOKUP_CODE            
      ,POVS.PAYMENT_PRIORITY "SS_PAYMENT_PRIORITY"
      ,POVS.TERMS_ID                        
      ,NULL "TERMS_NAME"                      
      ,POVS.INVOICE_AMOUNT_LIMIT            
      ,POVS.PAY_DATE_BASIS_LOOKUP_CODE "SS_PAY_DATE_BASIS_LOOKUP_CODE"
      ,POVS.ALWAYS_TAKE_DISC_FLAG            
      ,POVS.INVOICE_CURRENCY_CODE            
      ,POVS.PAYMENT_CURRENCY_CODE            
      ,POVS.HOLD_ALL_PAYMENTS_FLAG          
      ,POVS.TAX_REPORTING_SITE_FLAG          
      ,POVS.ORG_ID                          
      ,hou.name "OPERATING_UNIT_NAME"              
      ,POVS.DEFAULT_PAY_SITE_ID              
      ,POVS.MATCH_OPTION "SS_MATCH_OPTION"                  
      ,POVS.TOLERANCE_ID                    
      ,NULL "TOLERANCE_NAME"
      ,NULL "SERVICES_TOLERANCE_ID"
      ,NULL "SERVICES_TOLERANCE_NAME"    
      ,NULL "PAYMENT_METHOD_CODE"              
      ,POVS.SUPPLIER_NOTIF_METHOD            
      ,POVS.EMAIL_ADDRESS                    
      ,POVS.REMITTANCE_EMAIL
      ,POVS.ALLOW_AWT_FLAG "SS_ALLOW_AWT_FLAG"
      ,POVS.AWT_GROUP_ID
      ,POVS.AUTO_TAX_CALC_FLAG
      ,POVS.VAT_REGISTRATION_NUM "SS_VAT_REGISTRATION_NUM"
      ,POVS.OFFSET_TAX_FLAG "SS_OFFSET_TAX_FLAG"
      ,POVS.AP_TAX_ROUNDING_RULE
      ,POVS.AMOUNT_INCLUDES_TAX_FLAG
      ,POVS.AUTO_TAX_CALC_OVERRIDE
      ,hou.attribute13 "ORG_COUNTRY"
      ,NULL "BANK_NAME"
      ,NULL "BANK_NUMBER"
      ,NULL "INSTITUTION_TYPE"
      ,NULL "BANK_PARTY_ID"
      ,NULL "BRANCH_NAME"
      ,NULL "BRANCH_NUMBER"
      ,NULL "BRANCH_TYPE"
      ,NULL "BIC"
      ,NULL "BRANCH_CODE"
      ,NULL "SITE_ADDRESS_LINE1"
      ,NULL "SITE_ADDRESS_LINE2"
      ,NULL "SITE_ADDRESS_LINE3"
      ,NULL "SITE_ADDRESS_LINE4"
      ,NULL "SITE_CITY"
      ,NULL "SITE_STATE"
      ,NULL "SITE_COUNTY"
      ,NULL "SITE_ZIP"
      ,NULL "SITE_PROVINCE"
      ,NULL "BANK_ACCOUNT_NAME"
      ,NULL "BANK_ACCOUNT_NUM"
      ,NULL "CHECK_DIGITS"
      ,NULL "CURRENCY"
      ,NULL "IBAN"
      ,NULL "SECONDARY_ACCOUNT_REFERENCE"
      ,NULL "PRIMARY_PAY_FLAG"    
      ,NULL "PRIMARY ACCOUNT"
  from apps.po_vendors pov
      ,apps.po_vendor_sites_all povs
      ,apps.hr_all_organization_units hou
 where povs.org_id = hou.organization_id
   and pov.vendor_id = povs.vendor_id
   and pov.enabled_flag = 'Y'
   and NVL(pov.one_time_flag, 'N') <> 'Y' -- With One Time Flag
   and trunc( NVL(povs.inactive_date, sysdate)) >= trunc(sysdate)
   and trunc(sysdate) between trunc(hou.date_from) and trunc(nvl(hou.date_to, sysdate))
   and (povs.vendor_id, povs.vendor_site_id) in ( select distinct vendor_id, vendor_site_id --, vendor_site_id
                                                    from apps.ap_invoices_all
                                                   where trunc(creation_date) >= trunc(add_months(sysdate, -15)) ) -- Have invoices for the last 15 Months
   and NOT EXISTS ( select 1
                      from apps.ap_bank_account_uses_all apbau1
                          ,apps.ap_bank_accounts_all apb1
                     where apbau1.vendor_id = povs.vendor_id
                       and apbau1.vendor_site_id = povs.vendor_site_id
                       and apbau1.external_bank_account_id = apb1.bank_account_id
                       and apbau1.org_id = apb1.org_id
                       and apb1.org_id = povs.org_id
                       and trunc(sysdate) between trunc(nvl(apbau1.start_date, sysdate)) and trunc(nvl(apbau1.end_date, sysdate)))
)

order by segment1;


9. Entries Unposted to GL from SLA:

select gll.name, faa.application_name, faa.application_id, xah.*, gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6, xal.*
  from apps.xla_ae_headers xah
      ,apps.xla_ae_lines xal
      ,apps.gl_ledgers gll
      ,apps.fnd_application_vl faa
      ,apps.gl_code_combinations gcc
 where period_name = &Period_name
   and application_id = &appl_id -- Applicationid
   and xah.ae_header_id = xal.ae_header_id
   and xah.ledger_id = gll.ledger_id
   and xah.application_id = faa.application_id
   and gll.ledger_id = &Ledgerid
   and gl_transfer_status_code = 'N'
   and gcc.code_combination_id = xal.code_combination_id

  order by faa.application_id, xal.ae_header_id, xal.ae_line_num;


10. Tablespace usage details:

SELECT df.tablespace_name "Tablespace", totalusedspace "USED MB", (df.totalspace - tu.totalusedspace) "FREE MB"
      ,df.totalspace "TOTAL MB", ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "PCT FREE"
  FROM (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace
          FROM dba_data_files
         GROUP BY tablespace_name) df,
       (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace, tablespace_name
          FROM dba_segments
         GROUP BY tablespace_name) tu
 WHERE df.tablespace_name = tu.tablespace_name;


Scheduled Concurrent Programs and their Timings:

SELECT fcr.request_id
     , fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
     , fu.user_name requestor
     , fu.description requested_by
     , fu.email_address
     , frt.responsibility_name requested_by_resp
     , trim(fl.meaning) status
     , fcr.phase_code
     , fcr.status_code
     , fcr.argument_text "PARAMETERS"
      , TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
     , TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start 
     , TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
     , DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
     , CASE
          WHEN fcr.hold_flag = 'Y'
             Then Substr(
                    fu.description
                  , 0
                  , 40
                 )
       END last_update_by
     , CASE
          WHEN fcr.hold_flag = 'Y'
             THEN fcr.last_update_date
       END last_update_date
     , fcr.increment_dates
     , CASE WHEN fcrc.CLASS_INFO IS NULL THEN
        'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
       ELSE
        'n/a'
       END run_once
     , CASE WHEN fcrc.class_type = 'P' THEN
        'Repeat every ' ||
        substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
               'N', ' minutes',
               'M', ' months',
               'H', ' hours',
               'D', ' days') ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
               'S', ' from the start of the prior run',
               'C', ' from the completion of the prior run')
       ELSE
         'n/a'
       END set_days_of_week
       , CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
          'Days of week: ' ||
                  decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
                  decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
                  decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
                  decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
                  decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
                  decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
                  decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
         ELSE
           'n/a'
         end  days_of_week
  FROM fnd_concurrent_requests fcr
     , fnd_user fu
     , fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpt
     , fnd_printer_styles_tl fpst
     , fnd_conc_release_classes fcrc
     , fnd_responsibility_tl frt
     , fnd_lookups fl
 WHERE fcp.application_id = fcpt.application_id
   AND fcr.requested_by = fu.user_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND fcr.concurrent_program_id = fcpt.concurrent_program_id
   AND fcr.responsibility_id = frt.responsibility_id
   AND fcr.print_style = fpst.printer_style_name(+)
   AND fcr.release_class_id = fcrc.release_class_id(+)
   AND fcr.status_code = fl.lookup_code
   AND fl.lookup_type = 'CP_STATUS_CODE'
   AND fcr.phase_code = 'P'

Order By Fu.Description, Fcr.Requested_Start_Date Asc;

AR Memo Lines List:

SELECT hou.name operating_unit,
       t.NAME "Memo Line Name",
       t.description,
       b.line_type,
       b.tax_code "Tax Classification Code",
       b.tax_product_category "Tax Product Category",
       b.uom_code "Unit of Measure",
       gcc.concatenated_segments "Revenue Account",
       b.start_date "Start date",
       b.end_date "End Date"     
  FROM hr_operating_units hou
      ,ar_memo_lines_all_tl t
      ,ar_memo_lines_all_b b
      ,gl_code_combinations_kfv gcc
 WHERE hou.organization_id = b.org_id
   AND b.memo_line_id = t.memo_line_id
   AND b.gl_id_rev = gcc.code_combination_id
 ORDER BY hou.name, t.NAME;

Add list of Responsibilities and do a Password reset:

set serveroutput on
DECLARE
CURSOR c1 IS
select appl.application_short_name "APPLICATION_SHORT_NAME"
      ,resp.responsibility_key "RESPONSIBILITY_KEY"
      ,UPPER(fdg.data_group_name) "DATA_GROUP_NAME"
  from fnd_responsibility_vl resp
      ,fnd_application_vl appl
      ,fnd_data_groups fdg
 where resp.application_id = appl.application_id
   and resp.data_group_id = fdg.data_group_id
   and resp.responsibility_name in ('Application Developer',
'Functional Administrator',
'User Management',
'Workflow Administrator Web (New)',
'Application Diagnostics',
'System Administrator');

type l_rec is table of C1%ROWTYPE;
c boolean;
                       
lc_rec l_rec;
j number := 0;

begin

c:=fnd_user_pkg.ChangePassword('USERNAME','Password');
if c = true then
dbms_output.put_line('success');
commit;
else
dbms_output.put_line('FAIL');
end if;

OPEN C1;
FETCH C1 BULK COLLECT INTO lc_rec;
CLOSE C1;

for i in 1..lc_rec.COUNT LOOP
fnd_user_pkg.addresp
     (   username           => 'USERNAME',
        resp_app             => lc_rec(i).application_short_name,
        resp_key             => lc_rec(i).responsibility_key,
        security_group  => lc_rec(i).data_group_name,
        description         => NULL,
        start_date           => sysdate,
        end_date            => NULL
    );
end loop;
commit; 
dbms_output.put_line('Normal Completion');
exception
when others then
dbms_output.put_line('Exception:'||SQLERRM);
end
/


List of Locked Objects:

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;

SET PAGESIZE 14

-- Search for locked objects
-- To be executed under the SYSTEM account
-- Compatible with Oracle10.1.x and higher

select
distinct to_name object_locked
from
v$object_dependency
where
to_address in
(
select /*+ ordered */
w.kgllkhdl address
from
dba_kgllock w,
dba_kgllock h,
v$session w1,
v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
)
/


Print This Post

Changing an Internal Name of a Workflow Item Type

For changing a Workflow Internal name of an Item Type, use wfchitt.sql located at $FND_TOP/sql and the syntax is as below,

sqlplus usr/pwd @wfchitt.sql old_item_type new_item_type

Please refer to Oracle Administrator guide for further information on this.

Print This Post

Concurrent Program Responsibility

The below query provides the Responsibility and REquest group associated with the provided concurrent program,

SELECT frg.request_group_name, frpt.responsibility_name
FROM FND_CONCURRENT_PROGRAMS_TL fcpl
,FND_REQUEST_GROUP_UNITS frgu
,FND_REQUEST_GROUPS frg
,FND_RESPONSIBILITY frp
,FND_RESPONSIBILITY_TL frpt
WHERE fcpl.user_concurrent_program_name like 'Positions - Outstanding Receivables / Payables Report'
AND fcpl.language = USERENV('LANG')
AND fcpl.concurrent_program_id = frgu.request_unit_id
AND frgu.request_group_id = frg.request_group_id
AND frp.request_group_id = frg.request_group_id
AND frp.responsibility_id = frpt.responsibility_id
AND frpt.language = USERENV('LANG')
AND NVL(frp.end_date, SYSDATE) >= SYSDATE;

Print This Post

Invoice By Bill Transaction Currency Checkbox update with 'FRM-40200: Field is protected against update'

We will be able to modify the 'Invoice By Bill Transaction Currency' Checkbox unless there is baselined funding on the Project and following conditions represent whether this flag is updateable, with the error 'FRM-40200: Field is protected against update',

Non-zero baselined funding amount exists for the project

SELECT sum(nvl(allocated_amount,0))

FROM PA_PROJECT_FUNDINGS

WHERE PROJECT_ID = &p_project_id

AND budget_type_code = 'BASELINE';

Draft funding lines exist for the project

SELECT 'Y'

FROM PA_PROJECT_FUNDINGS

WHERE PROJECT_ID = &p_project_id

AND budget_type_code = 'DRAFT';

PA events exist on the project

SELECT 'Y'

FROM dual

WHERE exists( SELECT project_id

FROM pa_events

WHERE project_id = &p_project_id);

Any expenditure items exist on the project

SELECT 'Y'

FROM dual

WHERE exists( SELECT T.project_id

FROM pa_expenditure_items_all E

, pa_tasks T

WHERE T.project_id = &p_project_id

AND E.task_id = T.task_id);

The project type is flagged for use in InterCompany Billing ( cc_prvdr_flag = 'Y') . This columns is found in pa_project_types_all

SELECT pt.cc_prvdr_flag

FROM pa_projects_all p

, pa_project_types_all

WHERE p.project_id = &p_project_id

AND pt.project_type = p.project_type

AND nvl(pt.org_id, -999) = nvl(p.org_id, -999);

And sometimes there could be a draft invoice being generated in the other than the transaction currency and we want to generate the draft invoice in the transaction currency, then we need to undo the Draft invoice update the 'Invoice By Bill Transaction Currency' Checkbox with the following query,

UPDATE pa_projects_all

SET INV_BY_BILL_TRANS_CURR_FLAG = 'N'

WHERE project_id = &p_project_id;

Print This Post

OA Framework Errors

oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_NO_REGION_DATA. Tokens: REGIONCODE = /xxc/oracle/apps/ak/employee/webui/EmployeePG; at oracle.apps.fnd.framework.webui.JRAD2AKMapper.getRootMElement(JRAD2AKMapper.java:529) at oracle.apps.fnd.framework.webui.OAWebBeanFactoryImpl.getWebBeanTypeDataFromJRAD(OAWebBeanFactoryImpl.java:3719) at oracle.apps.fnd.framework.webui.OAWebBeanFactoryImpl.getRootApplicationModuleClass(OAWebBeanFactoryImpl.java:3452) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1006) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:509) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:430) at _oa__html._OA._jspService(_OA.java:84) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187) at _oa__html._RF._jspService(_RF.java:102) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187) at _oa__html._OA._jspService(_OA.java:94) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187) at _oa__html._OA._jspService(_OA.java:94) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456) at org.apache.jserv.JServConnection.run(JServConnection.java:294) at java.lang.Thread.run(Thread.java:619) ## Detail 0 ##

Exception: oracle.adf.mds.MetadataDefException: Unable to find component with absolute reference = /xxc/oracle/apps/ak/employee/webui/EmployeePG, XML Path = null. Please verify that the reference is valid and the definition of the component exists either on the File System or in the MDS Repository. at oracle.adf.mds.internal.MetadataManagerBase.findElement(MetadataManagerBase.java:1350) at oracle.adf.mds.MElement.findElement(MElement.java:97) at oracle.apps.fnd.framework.webui.JRAD2AKMapper.getRootMElement(JRAD2AKMapper.java:503) at oracle.apps.fnd.framework.webui.OAWebBeanFactoryImpl.getWebBeanTypeDataFromJRAD(OAWebBeanFactoryImpl.java:3719) at oracle.apps.fnd.framework.webui.OAWebBeanFactoryImpl.getRootApplicationModuleClass(OAWebBeanFactoryImpl.java:3452) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1006) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:509) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:430) at _oa__html._OA._jspService(_OA.java:84) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187) at _oa__html._RF._jspService(_RF.java:102) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187) at _oa__html._OA._jspService(_OA.java:94) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187) at _oa__html._OA._jspService(_OA.java:94) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456) at org.apache.jserv.JServConnection.run(JServConnection.java:294) at java.lang.Thread.run(Thread.java:619)

Exception: oracle.adf.mds.MetadataDefException: Unable to find component with absolute reference = /xxc/oracle/apps/ak/employee/webui/EmployeePG, XML Path = null. Please verify that the reference is valid and the definition of the component exists either on the File System or in the MDS Repository. at oracle.adf.mds.internal.MetadataManagerBase.findElement(MetadataManagerBase.java:1350) at oracle.adf.mds.MElement.findElement(MElement.java:97) at oracle.apps.fnd.framework.webui.JRAD2AKMapper.getRootMElement(JRAD2AKMapper.java:503) at oracle.apps.fnd.framework.webui.OAWebBeanFactoryImpl.getWebBeanTypeDataFromJRAD(OAWebBeanFactoryImpl.java:3719) at oracle.apps.fnd.framework.webui.OAWebBeanFactoryImpl.getRootApplicationModuleClass(OAWebBeanFactoryImpl.java:3452) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1006) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:509) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:430) at _oa__html._OA._jspService(_OA.java:84) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187) at _oa__html._RF._jspService(_RF.java:102) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187) at _oa__html._OA._jspService(_OA.java:94) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162) at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187) at _oa__html._OA._jspService(_OA.java:94) at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119) at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417) at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267) at oracle.jsp.JspServlet.internalService(JspServlet.java:186) at oracle.jsp.JspServlet.service(JspServlet.java:156) at javax.servlet.http.HttpServlet.service(HttpServlet.java:588) at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456) at org.apache.jserv.JServConnection.run(JServConnection.java:294) at java.lang.Thread.run(Thread.java:619)

This is basically due to unavailability of Page in the MDS, please check the existence of the Page as below,

set serveroutput on size 10000 ;

DECLARE

BEGIN

jdr_utils.listdocuments('/xxc/oracle/apps/ak/employee/webui/', TRUE);

END;

/

DECLARE

BEGIN

jdr_utils.printdocument

(

p_document => '/xxc/oracle/apps/ak/employee/webui/EmployeePG');

END;

/

And if you get the below error,

nonymous block completed

Error: Could not find path /xxc/oracle/apps/ak/employee/webui/

anonymous block completed

Error: Could not find document /xxc/oracle/apps/ak/employee/webui/EmployeePG

Bounce the Apache as below, logging with applmgr privilege,

$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop

$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start

Few More Errors:

oracle.apps.fnd.framework.OAException: Application: ICX, Message Name: Could not find the specified responsibility.

at oracle.apps.fnd.framework.CreateIcxSession.getEncryptedSessId(CreateIcxSession.java:158)

at oracle.apps.fnd.framework.CreateIcxSession.createSession(CreateIcxSession.java:80)

at _test__fwktutorial._jspService(test_fwktutorial.jsp:40)

at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:139)

at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)

at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)

at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)

at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)

at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)

at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)

at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)

at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:797)

at java.lang.Thread.run(Thread.java:534)

The reason for this error is when the Project created in Jdev does not have the correct Reponsibility details.