Custom Search
Friday, 7 December 2012
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
Monday, 2 April 2012
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
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
Thursday, 16 February 2012
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.
Print This Post
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
Tuesday, 27 September 2011
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
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
Wednesday, 6 April 2011
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
"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
Thursday, 13 January 2011
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
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
Tuesday, 11 January 2011
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
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
Monday, 20 September 2010
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
sqlplus usr/pwd @wfchitt.sql
Please refer to Oracle Administrator guide for further information on this.
Print This Post
Monday, 2 August 2010
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
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
Wednesday, 14 July 2010
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'
Saturday, 5 June 2010
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.
Friday, 21 May 2010
Concurrent Program download without Valuesets and Values
Good news that Oracle had modified the lct file afcpprog.lct to ignore the Valuesets and Valueset Values, Parameter P_VSET_DOWNLOAD_CHILDREN had been included and it should be set to 'N' while downloading the ldt file for Concurrent programs as below,
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name" P_VSET_DOWNLOAD_CHILDREN="N"
Setting the value of P_VSET_DOWNLOAD_CHILDREN to 'N' instructs the lct file to ignore the Valuesets and its values.
Print This Post
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name" P_VSET_DOWNLOAD_CHILDREN="N"
Setting the value of P_VSET_DOWNLOAD_CHILDREN to 'N' instructs the lct file to ignore the Valuesets and its values.
Print This Post
Wednesday, 12 May 2010
Close Other Forms Checkbox Updation
Sometimes in a responsibility we might need to open more than one form to view, copy data between the forms, and this is restricted by the administrator for a few responsibilities according to the business Requirement/Security.
This functionality is controlled by a pre-defined Function ‘Navigator: Disable Multiform’.
So If you want to enable access to Multiple forms in a responsibility then you got to exclude the function ‘Navigator: Disable Multiform’ Function in the Responsibility Definition (System Administrator -> Security -> Responsibility -> Define) Page if the corresponding Menu is associated with ‘Navigator: Disable Multiform’ Function
Print This Post
This functionality is controlled by a pre-defined Function ‘Navigator: Disable Multiform’.
So If you want to enable access to Multiple forms in a responsibility then you got to exclude the function ‘Navigator: Disable Multiform’ Function in the Responsibility Definition (System Administrator -> Security -> Responsibility -> Define) Page if the corresponding Menu is associated with ‘Navigator: Disable Multiform’ Function
Print This Post
Friday, 9 April 2010
Valueset used in different concurrent programs
Provided the Valueset name the following query retrieves its usage in different concurrent programs
SELECT fcp.user_concurrent_program_name, fdfc.end_user_column_name, fdfc.application_column_name--*--descriptive_flexfield_name
FROM FND_DESCR_FLEX_COLUMN_USAGES fdfc,
FND_CONCURRENT_PROGRAMS_VL fcp
WHERE fdfc.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
AND flex_value_set_id in ( select flex_value_set_id from fnd_flex_value_sets
where flex_value_set_name = '&FLEX_VALUE_SETNAME');
Print This Post
SELECT fcp.user_concurrent_program_name, fdfc.end_user_column_name, fdfc.application_column_name--*--descriptive_flexfield_name
FROM FND_DESCR_FLEX_COLUMN_USAGES fdfc,
FND_CONCURRENT_PROGRAMS_VL fcp
WHERE fdfc.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
AND flex_value_set_id in ( select flex_value_set_id from fnd_flex_value_sets
where flex_value_set_name = '&FLEX_VALUE_SETNAME');
Print This Post
Workflow Owned By restriction
Sometimes Workflow Owned By is restricted in the Status Monitor Tab of Workflow Administrator responsibility to the Owning User, the reason for this is lack of Workflow System Administrator Privileges for the user to view the Workflows submitted by all the users.
To make sure that the user able to view the Workflows owned by everyone is to identify which responsibility holds the Workflow System Administrator responsibilities and assign to themselves the corresponding responsibility.
Following query identifies who has been asssigned the Workflow System Administrator Role,
SELECT rs.text,r.display_name
FROM wf_resources rs, wf_local_roles r
WHERE rs.name='WF_ADMIN_ROLE'
AND rs.text=r.name;
If role found above is a responsibility role, then assign that responsibility to the user to be able to query any workflow item in the Status Monitor or update the Workflow System Administrator to the one found above as displayed below,
Also you can set by updating the table WF_RESOURCES as below, assigning the Workflow System Administrator Role to the 'System Administrator' responsibility,
UPDATE wf_resources
SET text='FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD'
WHERE name='WF_ADMIN_ROLE';
COMMIT;
Print This Post
To make sure that the user able to view the Workflows owned by everyone is to identify which responsibility holds the Workflow System Administrator responsibilities and assign to themselves the corresponding responsibility.
Following query identifies who has been asssigned the Workflow System Administrator Role,
SELECT rs.text,r.display_name
FROM wf_resources rs, wf_local_roles r
WHERE rs.name='WF_ADMIN_ROLE'
AND rs.text=r.name;
If role found above is a responsibility role, then assign that responsibility to the user to be able to query any workflow item in the Status Monitor or update the Workflow System Administrator to the one found above as displayed below,
Also you can set by updating the table WF_RESOURCES as below, assigning the Workflow System Administrator Role to the 'System Administrator' responsibility,
UPDATE wf_resources
SET text='FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD'
WHERE name='WF_ADMIN_ROLE';
COMMIT;
Print This Post
Subscribe to:
Posts (Atom)