select b.name,b.segment1,a.project_id,a.PERSON_ID,c.FULL_NAME from PA_CREDIT_RECEIVERS a,pa_projects_all b,per_all_people_f c where a.project_id=b.project_id
and a.person_id=c.person_id
select b.name,b.segment1,a.project_id,a.PERSON_ID,c.FULL_NAME,d.CUSTOMER_NAME
from PA_CREDIT_RECEIVERS a,pa_projects_all b,per_all_people_f c,PA_PROJECT_CUSTOMERS_V d
where a.project_id=b.project_id
and a.project_id=d.PROJECT_ID
and a.person_id=c.person_id
select distinct(a.project_id),a.RESOURCE_NAME,b.segment1,a.RAW_COST from PA_BUDGET_LINES_V a,pa_projects_all b where a.project_id=b.PROJECT_ID and resource_name='Labour'
/* Formatted on 2010/08/31 10:02 (Formatter Plus v4.8.0) */
SELECT b.segment1,a.agreement_num,a.project_unbaselined_amount,
a.project_baselined_amount,a.project_revenue,
a.project_billed_amount
FROM pa_summary_project_fundings_v a,PA_PROJECTS_ALL b
where b.project_ID = a.Project_ID
Employee Information
SELECT papf.national_identifier groupcode,
papf.employee_number empno,to_char(papf.effective_start_date,'DD-MON-YYYY') dateofjoin,
papf.FULL_NAMe from per_all_people_f papf
LTRIM - when unable to update any fields
DECLARE
CURSOR c1
IS
SELECT *
FROM agg_project;
BEGIN
FOR r1 IN c1
LOOP
UPDATE pa_agreements_all
SET agreement_id = LTRIM (RTRIM (r1.agreement_id)),
DECLARE
CURSOR c1
IS
SELECT *
FROM agg_project;
BEGIN
FOR r1 IN c1
LOOP
UPDATE pa_agreements_all
SET agreement_id = LTRIM (RTRIM (r1.agreement_id)),
customer_id = LTRIM (RTRIM (r1.customer_id)),
agreement_num = LTRIM (RTRIM (r1.agreement_num)),
agreement_type = LTRIM (RTRIM (r1.agreement_type)),
-- LAST_UPDATE_DATE =ltrim(rtrim(r1.AGREEMENT_ID)),
-- LAST_UPDATED_BY=ltrim(rtrim(r1.AGREEMENT_ID)),
-- CREATION_DATE=ltrim(rtrim(r1.AGREEMENT_ID)),
-- CREATED_BY=ltrim(rtrim(r1.AGREEMENT_ID)),
-- LAST_UPDATE_LOGIN NUMBER(15) NOT NULL,
owned_by_person_id = LTRIM (RTRIM (r1.owned_by_person_id)),
term_id = LTRIM (RTRIM (r1.term_id)),
revenue_limit_flag = LTRIM (RTRIM (r1.revenue_limit_flag)),
amount = LTRIM (RTRIM (r1.amount)),
description = LTRIM (RTRIM (r1.description)),
-- EXPIRATION_DATE DATE,
-- ATTRIBUTE_CATEGORY VARCHAR2(30 BYTE),
-- ATTRIBUTE1 VARCHAR2(150 BYTE),
-- ATTRIBUTE2 VARCHAR2(150 BYTE),
-- ATTRIBUTE3 VARCHAR2(150 BYTE),
-- ATTRIBUTE4 VARCHAR2(150 BYTE),
-- ATTRIBUTE5 VARCHAR2(150 BYTE),
-- ATTRIBUTE6 VARCHAR2(150 BYTE),
-- ATTRIBUTE7 VARCHAR2(150 BYTE),
-- ATTRIBUTE8 VARCHAR2(150 BYTE),
-- ATTRIBUTE9 VARCHAR2(150 BYTE),
-- ATTRIBUTE10 VARCHAR2(150 BYTE),
-- TEMPLATE_FLAG VARCHAR2(1 BYTE),
org_id = LTRIM (RTRIM (r1.org_id)),
pm_agreement_reference =
LTRIM (RTRIM (r1.pm_agreement_reference)),
pm_product_code = LTRIM (RTRIM (r1.pm_product_code)),
--OWNING_ORGANIZATION_ID NUMBER(15),
agreement_currency_code =
LTRIM (RTRIM (r1.agreement_currency_code)),
invoice_limit_flag = LTRIM (RTRIM (r1.invoice_limit_flag))
WHERE agreement_num = LTRIM (RTRIM (r1.agreement_num));
END LOOP;
END;
**********************************************************************************************
drop table agg_project
Create table agg_project as select * from pa_agreements_all where agreement_num ='RF/Q/AK/230/10'
*********************************************************************************************
Update fields
update pa_agreements_all set agreement_num ='RF/Q/AK/230/10' where agreement_id='1721'
select * from pa_agreements_all where agreement_num like 'RF/Q/AK/230/10%'
Autoaccounting Rule: Intermediate Value
SELECT CONCAT (a1.order_type, a1.project_status) FROM (SELECT (DECODE (ppc.class_code,'Quote', 'Quote','Warranty', 'Warranty','Other Order Type')) order_type, CONCAT ('-',DECODE (pps.project_status_name,'Revenue Recognized', 'Revenue Recognized','Other Status' )) project_status FROM pa_project_classes ppc, pa_projects_all ppa, pa_project_statuses pps WHERE ppc.project_id = :1 AND ppc.class_category = 'Order Type' AND ppa.project_status_code = pps.project_status_code AND ppc.project_id = ppa.project_id ) a1
Rule Parameter:
Project ID
For Miscellanious Tran
"SELECT CONCAT (a1.order_type, a1.project_status)||exp_type FROM (SELECT (DECODE (ppc.class_code, 'Quote', 'Quote','Warranty', 'Warranty','Other Order Type')) order_type, CONCAT ('-',DECODE (pps.project_status_name,'Revenue Recognized', 'Revenue Recognized','Other Status')) project_status, CONCAT ('-',DECODE (expenditure_type,'Accrual', 'Accrual','Accrual Clearance', 'Accrual Clearance',
'Accrual Adjustment', 'Accrual Adjustment', 'WIP Mig: Labor MFG', 'WIP Mig: Labor MFG','WIP Mig: Labor ENG', 'WIP Mig: Labor ENG','WIP Mig: Labor R&D', 'WIP Mig: Labor R&D','WIP Mig: Mat. Special Items', 'WIP Mig: Mat. Special Items','WIP Mig: Mat. Drawings', 'WIP Mig: Mat. Drawings', 'WIP Mig: MFG Outsourcing', 'WIP Mig: MFG Outsourcing','WIP Mig: Mat. OE', 'WIP Mig: Mat. OE', 'WIP Mig: Mat. AM', 'WIP Mig: Mat. AM','WIP Mig: Packing', 'WIP Mig: Packing','WIP Mig: Travel Exp', 'WIP Mig: Travel Exp','WIP Mig: Other Costs', 'WIP Mig: Other Costs','WIP Mig: Other Rev. Charge', 'WIP Mig: Other Rev. Charge','WIP Mig: Labor on Stock Items', 'WIP Mig: Labor on Stock Items','WIP Mig: Mat. Stock', 'WIP Mig: Mat. Stock','WIP Mig: Goods in transit', 'WIP Mig: Goods in transit','Other Exp Type')) exp_type FROM pa_project_classes ppc, pa_projects_all ppa,pa_project_statuses pps,pa_expenditure_items_all peia WHERE ppc.project_id = :1 AND ppc.class_category = 'Order Type' AND ppa.project_status_code = pps.project_status_code AND ppc.project_id = ppa.project_id AND peia.project_id = ppa.project_id AND expenditure_item_id = :2) a1"
Rule Parameter:
Project ID
Responsibility Level Profile Options And Values
Select fpv.user_profile_option_name,fpo.profile_option_value,frt.responsibility_name
From fnd_responsibility_tl frt,
fnd_profile_option_values fpo,
fnd_profile_options_vl fpv
Where fpo.profile_option_id=fpv.profile_option_id
And fpo.application_id=fpv.application_id
And frt.language='US'
And fpo.level_id=10003
And frt.responsibility_id=to_number(fpo.level_value)
And (frt.responsibility_name like 'AT FPD%' or
frt.responsibility_name like 'BRN%')
And fpo.profile_option_value IS NOT NULL
And start_date_active <= SYSDATE
And NVL(end_date_active,SYSDATE) >= SYSDATE
order by fpv.user_profile_option_name
**********************************************************************************************************************************************
PA Invoices Reversal with Credit Memo -
Byju,
1) Here is the script we need to run, to backup the PA Invoices before we run AutoInvoice.
create table xxs_dc_ra_intf_lns_v01
as
select inf_lns.*
from apps.ra_interface_lines_all inf_lns
where 1=1
and inf_lns.batch_source_name = 'FNW_PROJECTS_INVOICES'
2) Run AutoInvoice
3) Once AutoInvoice has been successfully run, we use the backup table to re-populate the Interface table, to reverse the Projects Invoices.
Do do this, we need to clear cust_trx_type_id, and populate cust_trx_type_name= FNW PROJECTS CR MEMO.
We probably need to update the related columns with the Invoices imported
RELATED_BATCH_SOURCE_NAME
RELATED_TRX_NUMBER
RELATED_CUSTOMER_TRX_ID
Kam
********************************************************************************************************************************************
Find User for Responsibility Find User for Responsibility
select user_name from fnd_user where user_id in (
SELECT a.user_id, b.User_name,a.end_date FROM FND_USER_RESP_GROUPS_DIRECT a, fnd_user b
WHERE a.user_id=b.user_id and RESPONSIBILITY_ID = '55748' SELECT * FROM FND_USER_RESP_GROUPS_DIRECT WHERE RESPONSIBILITY_ID = '54004'
SELECT * FROM FND_USER_RESP_GROUPS_DIRECT WHERE RESPONSIBILITY_ID = '54004'
select * from FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_ID = '54004'
select * from FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_NAME = 'BRN Purchasing Administrator'
*******************************************************************************************************************************************
AUTO ACCOUNTING LOOKUPS
SELECT ROWID,SEGMENT_VALUE_LOOKUP,SEGMENT_VALUE,SEGMENT_VALUE_LOOKUP_SET_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN FROM PA_SEGMENT_VALUE_LOOKUPS
USER RESPONSIBILITY ASSIGNMENT LIST
SELECT a.user_id, c.user_name, b.RESPONSIBILITY_NAME FROM FND_USER_RESP_GROUPS_DIRECT a, FND_RESPONSIBILITY_VL b, FND_USER c
WHERE a.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
AND a.user_id = c.user_id
and b.RESPONSIBILITY_NAME like 'KAW %'
FIND APPLIED PATCH
select
* from apps.ad_bugs
where bug_number in
'9817760' order by bug_number;
FIND PATCH FILE VERSIONS
select a.FILENAME,b.FILE_VERSION_ID,b.FILE_ID,b.VERSION,b.CREATION_DATE,b.LAST_UPDATE_DATE,a.SUBDIR
from apps.AD_FILES a, apps.AD_FILE_VERSIONS b
where a.file_id = b.file_id
and a.filename like 'pav1356%';