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
Custom Search
Wednesday, 6 April 2011
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
Sunday, 21 February 2010
SQL Loader
Sometimes when you upload an Excel sheet using SQL* Loader, you want Excel sheet to ignore the Heading of the Excel sheet and want to uplaod from the second record, So you can consider the option of <> in the SQL Statement as below,
OPTIONS (ERRORS=1, SKIP=1)
LOAD DATA
REPLACE
INTO TABLE XXX.TEST
WHEN Inv_It_Ctgry <> 'Inventory Item category'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( Inv_It_Ctgry CHAR(120)
,SERV CHAR(8)
,CREATED_BY DECIMAL EXTERNAL
,CREATION_DATE DATE(11) "DD-MON-YYYY"
)
And you do not want the Program to give a WARNING on ignoring the First record, for that you add the OPTIONS clause to ignore in case of 1 Record fail, and Skip the Error.
Thursday, 14 January 2010
Approvals Management Engine
In this section I would be covering,
1. What is AME
2. AME prior to AME.B and post AME.B and how AME is enabled
What is AME:
I think everyone of us know AME mean Approvals Management Engine, but if you are new to this then its good to know the architecture of AME.
Approvals Management Engine(AME) is a self service web application which lets users define business rules governing who should approve the transaction that originate in other oracle applications like SSHR.
AME prior to AME.B, post AME.B and how AME is enabled:
AME Responsibilities in AME.A are assigned directly to the users. However in 11i.AME.B or R12 and higher, AME responsibilities are assigned indirectly to users through roles. The roles are assigned to the users by the SYSADMIN user using the User Management Responsibility. Once the roles are assigned, the AME responsibilities are automatically available to the users without specifically assigning the AME responsibilities to the users.
here are the steps to assign the roles,
1. Login as SYSADMIN user ( or a user that has full User Management Functionality).
2. Switch the Responsibility to User Management and Navigate to the Users function as below,
3. Query for the user ‘SYSADMIN’ in our case, and click on the Update action,
4. In the Update User page click on the Assign Roles button,
5. In the Search Window, Query for ‘Approval%’, and following five roles will be displayed,
- Approvals Management Administrator
- Approvals Management Business Analyst
- Approvals Management System Viewer
- Approvals Management System Administrator
- Approvals Management Process Owner
6. Select only the “Approvals Management Administrator” and “Approvals Management Business Analyst” roles.
7. After selecting both the roles, provide the justification for the role and the effective date as below,
8. After completing this action, you can query back the user and verify list of roles being attached to the user.
9. Now switch the responsibility to Functional Administrator.
Functional Administrator --> Home
10. Click on Create Grant under the Grants tab
11. Enter a Grant Name and provide an effective date as below,
12. Navigate to Security Context region and select the Grant Type as ‘Specific User’
13. In the Grantee field select the user associated.
14. In the Data Security Field select ‘AME Transaction Types’ as the Object.
15. Clicking ‘Next’ will take you to the ‘Select object Data Context’ page.
16. You have a Default Value of ‘All Rows’ in the Data Context Type, leave it as it is, and navigate further.
17. In the ‘Object Parameter and Select Set’ Page, enter ‘AME Calling Applications’ as the Value in the Set field.
18. Clicking Next will take you to the Review Page.
19. Verify the Information and Submit the Transaction.
20. Now switch the Responsibility to System Administrator and Navigate to System Profiles.
21. Query for the Profile ‘AME: Installed’ at the required level and set the value to ‘Yes’ as below
Save the Data.
Friday, 8 January 2010
Oracle Framework Learnings
Invoke AM method using Parameters:
OAApplicationModule am = pageContext.getApplicationModule(webBean);
Serializable[] parameters = {userName, password};
am.invokeMethod("initDetails", parameters);
OAApplicationModule am = pageContext.getApplicationModule(webBean);
Serializable[] parameters = {userName, password};
am.invokeMethod("initDetails", parameters);
Passing different types of parameters to Application Modules(AM):
OAApplicationModule am = pageContext.getApplicationModule(webBean);
class[] paramTypes = { String.class, Boolean.class};
am.invokeMethod("Test", paramValues, paramTypes);
To execute Query and set Where Clause:
setWhereClause("userName = :1 AND password = :2");
setWhereClauseParams(null);
setWhereClauseparam(0, pUserName);
setWhereClause(1, password);
executeQuery();
Disabling a Button or any Item:
We have various bean for the items that we wish to act upon either disabling or modifying any property like rendered property to TRUE or FALSE. Herein is an example,
OASubmitButtonBean saveT = (OASubmitButtonBean)webBean.childRecursive("SaveBtn");
-- SaveBtn is the ID of the submitButton item.
SaveBtn.setDisabled(TRUE);
Forward immediately to the Current Page:
oaPageContext.forwardImmediatelyToCurrentPage(null, TRUE, null);
Disabling OAFooter and Privacy Statement in the OAF page:
Generally sometimes client would like to replace Oracle Privacy ststement with his own and we can personalize according to client needs of modifying/Disabling the OAFooter and Privacy statement as below,
For this First, make sure that the Personalize Page Link exist on top of all OA Pages, if not please set the following profile options to enable it,
Personalize%Self%Serv%Def% : Yes
Now, after doing that we shall proceed to Disable/Modify the OA Footer:
1. Open an OA Page, where would you like to Disable/Modify the OA Footer.
If you need to disable/Modify for all the OA Pages, open any existing OA Page as below
2. Click on the Personalize Page button at the top of the Page, and choose the context of the Personalization as below,
Check the corresponding options if you want to implement the change at the Responsibility or Site level. Here we have considered at the Site Level
3. Now, you could see the highlighted copyright and a pencil icon across it, clicking on it we can personalize the OAFooter/Copyright content as needed, please click on it
4. Modify the Rendered Property to False to Disable and modify the Additional Text for modifying the Content.
If you are representing at the site level, then make sure that the property is Inherit at the Responsibility Level, though it Disbaled at the Site level, if its enabled at the Responsibility level, the OA Footer can still be visible.
Click on Apply to make the Changes and Return to the Application to view the changes as below,
the same can be done for Diabling the Privacy statement.
Disabling OAFooter and Privacy Statement in the OAF page:
Generally sometimes client would like to replace Oracle Privacy ststement with his own and we can personalize according to client needs of modifying/Disabling the OAFooter and Privacy statement as below,
For this First, make sure that the Personalize Page Link exist on top of all OA Pages, if not please set the following profile options to enable it,
Personalize%Self%Serv%Def% : Yes
Now, after doing that we shall proceed to Disable/Modify the OA Footer:
1. Open an OA Page, where would you like to Disable/Modify the OA Footer.
If you need to disable/Modify for all the OA Pages, open any existing OA Page as below
2. Click on the Personalize Page button at the top of the Page, and choose the context of the Personalization as below,
Check the corresponding options if you want to implement the change at the Responsibility or Site level. Here we have considered at the Site Level
3. Now, you could see the highlighted copyright and a pencil icon across it, clicking on it we can personalize the OAFooter/Copyright content as needed, please click on it
4. Modify the Rendered Property to False to Disable and modify the Additional Text for modifying the Content.
If you are representing at the site level, then make sure that the property is Inherit at the Responsibility Level, though it Disbaled at the Site level, if its enabled at the Responsibility level, the OA Footer can still be visible.
Click on Apply to make the Changes and Return to the Application to view the changes as below,
the same can be done for Diabling the Privacy statement.Wednesday, 6 January 2010
Oracle Forms FAQ
FORMS60_PATH setup:
Generally at client side, there could be a big hassle extractingthe forms executable file fmx from the form, due to wrong pointer of FORMS60_PATH. The FORMS60_PATH should be pointing to $AU_TOP/forms/US; $AU_TOP/resource as below
FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US:$AU_TOP/resource
export $FORMS60_PATH
And generate the fmx with the following command,
f60gen module=XXXXXXX.fmb userid=apps/apps module_type=FORM batch=NO compile_all=YES
Print This Post
Generally at client side, there could be a big hassle extractingthe forms executable file fmx from the form, due to wrong pointer of FORMS60_PATH. The FORMS60_PATH should be pointing to $AU_TOP/forms/US; $AU_TOP/resource as below
FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US:$AU_TOP/resource
export $FORMS60_PATH
And generate the fmx with the following command,
f60gen module=XXXXXXX.fmb userid=apps/apps module_type=FORM batch=NO compile_all=YES
Print This Post
Subscribe to:
Posts (Atom)