Steps to find pwd for an application user in oracle apps

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

2. Create get_pwd package body
CREATE OR REPLACE PACKAGE BODY get_pwd AS FUNCTION decrypt ( KEY IN VARCHAR2 ,VALUE IN VARCHAR2 ) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; END get_pwd;
/
3. Query to get password for apps user.

SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD')) FROM DUAL)), usertable.encrypted_foundation_password) FROM DUAL) AS apps_password FROM fnd_user usertable WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD') ,1 , INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/') - 1 ) FROM DUAL))
4. Query to get password for any application user.

SELECT usertable.user_name , (SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD')) FROM DUAL)), usertable.encrypted_foundation_password) FROM DUAL) AS apps_password FROM fnd_user usertable WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD') ,1 , INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/') - 1 ) FROM DUAL)))) ,usertable.encrypted_user_password) FROM DUAL) AS encrypted_user_password FROM fnd_user usertable WHERE usertable.user_name LIKE UPPER ('&username')
Posted by Prem Kumar at 12:10 PM 0 comments
Labels: Steps to find password for an application user in oracle applications
Thursday, May 21, 2009
How to change an Oracle password temporarily
In Oracle it is possible to change a password temporarily. This can be useful for DBA which act as a different user.
SQL> select username,password from dba_users where username='GEORGE';
USERNAME PASSWORD
-------- ----------------
GEORGE F894844C34402B67
SQL> alter user george identified by welcome123;

Query to find Open invoce and customer information

SELECT aps.*
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
ra_cust_trx_line_gl_dist_all rct
WHERE 1 = 1
AND ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = 'Y'
AND rl.line_type IN ('FREIGHT', 'LINE')
AND ra.cust_trx_type_id = rt.cust_trx_type_id
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = 'A'
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = 'BILL_TO'
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND hcasa_bill.status = 'A'
AND hcsua_bill.status = 'A'
AND aps.amount_due_remaining <> 0
AND aps.status = 'OP'
AND hc.cust_account_id = 21924 --- Here you can give ths customer for whom you want open invoices to be retrieved

Query to get revenue distribution lines for Invoice

SELECT DISTINCT c.customer_name, c.customer_number, c.customer_id,
t.customer_trx_id, t.trx_number, ct.NAME invoice_type,
l.line_number, t.org_id, cc.segment1, cc.segment2,
cc.segment3, cc.segment4, cc.segment5, cc.segment6, d.gl_date,
d.cust_trx_line_gl_dist_id, d.code_combination_id,
d.account_class
FROM ra_cust_trx_types_all ct,
ra_customers c,
ra_customer_trx_all t,
ra_customer_trx_lines_all l,
gl_code_combinations cc,
ra_cust_trx_line_gl_dist_all d
WHERE 1 = 1
AND t.cust_trx_type_id = ct.cust_trx_type_id
AND t.bill_to_customer_id = c.customer_id
AND d.customer_trx_id = t.customer_trx_id
AND d.customer_trx_line_id = l.customer_trx_line_id(+)
AND d.code_combination_id = cc.code_combination_id
AND TRUNC (d.gl_date) >= TO_DATE ('01-01-2009', 'DD-MM-YYYY')
AND d.posting_control_id = -3
AND d.account_set_flag = 'N'
AND d.account_class = 'REV'

Script to create customer,Party site,account site,Org,Phone email and fax contact point creation

R12 AR Person Creation
*****************DECLARE
DECLARE
l_party_id NUMBER;
l_party_number VARCHAR2 (20);
l_profile_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_create_person_rec hz_party_v2pub.person_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_create_person_rec.person_last_name := 'Rohith';
l_create_person_rec.person_first_name := 'Kannur';
l_create_person_rec.created_by_module := 'TCA_MIGRATION';
hz_party_v2pub.create_person
(p_init_msg_list => 'T',
p_person_rec => l_create_person_rec,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_profile_id => l_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' l_return_status);
IF l_return_status <> 'S'
THEN
DBMS_OUTPUT.put_line ('l_msg_count ' l_msg_count);
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) ': ' l_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error mesg' l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_id ' l_party_id);
DBMS_OUTPUT.put_line ('l_party_number ' l_party_number);
DBMS_OUTPUT.put_line ('l_profile_id ' l_profile_id);
END IF;
END;
R12 AR Party site Creation
*****************DECLARE
DECLARE
l_party_site_id NUMBER;
l_party_site_number NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_party_site_rec.party_id := 308013;
l_party_site_rec.location_id := 25254;
l_party_site_rec.created_by_module := 'TCA_MIGRATION';
hz_party_site_v2pub.create_party_site
(p_init_msg_list => 'T',
p_party_site_rec => l_party_site_rec,
x_party_site_id => l_party_site_id,
x_party_site_number => l_party_site_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) ': ' l_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error mesg' l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_site_id ' l_party_site_id);
DBMS_OUTPUT.put_line ('l_party_site_number ' l_party_site_number);
END IF;
END;
R12 Customer acct information
*****************DECLARE
DECLARE
l_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
l_organization_rec hz_party_v2pub.organization_rec_type;
l_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
l_party_id NUMBER;
l_party_number VARCHAR2 (240);
l_profile_id NUMBER;
l_cust_account_id NUMBER;
l_account_number VARCHAR2 (240);
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_cust_account_rec.created_by_module := 'TCA_MIGRATION';
l_organization_rec.organization_name := 'Test 2';
hz_cust_account_v2pub.create_cust_account
(p_init_msg_list => 'T',
p_cust_account_rec => l_cust_account_rec,
p_organization_rec => l_organization_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile_amt => 'T',
x_cust_account_id => l_cust_account_id,
x_account_number => l_account_number,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_profile_id => l_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) ': ' l_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error mesg' l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_id ' l_party_id);
DBMS_OUTPUT.put_line ('l_cust_account_id ' l_cust_account_id);
DBMS_OUTPUT.put_line ('l_profile_id ' l_profile_id);
END IF;
END;
R12 customer Organization information
*****************DECLARE
DECLARE
l_organization_rec hz_party_v2pub.organization_rec_type;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_party_id NUMBER;
l_party_number VARCHAR2 (240);
l_profile_id NUMBER;
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_organization_rec.organization_name := 'Test Org';
l_organization_rec.created_by_module := 'TCA_MIGRATION';
hz_party_v2pub.create_organization
(p_init_msg_list => 'T',
p_organization_rec => l_organization_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_profile_id => l_profile_id
);
IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) ': ' l_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error mesg' l_output);
ELSE
DBMS_OUTPUT.put_line ('l_party_id ' l_party_id);
DBMS_OUTPUT.put_line ('l_party_number ' l_party_number);
DBMS_OUTPUT.put_line ('l_profile_id ' l_profile_id);
END IF;
END;
R12 Customer acct site information
*****************DECLARE
DECLARE
l_cust_acct_site_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
mo_global.init ('AR');
mo_global.set_policy_context ('S', '85');
l_cust_acct_site_rec.cust_account_id := 96682;
l_cust_acct_site_rec.party_site_id := 179420;
l_cust_acct_site_rec.created_by_module := 'TCA_MIGRATION';
hz_cust_account_site_v2pub.create_cust_acct_site
(p_init_msg_list => 'T',
p_cust_acct_site_rec => l_cust_acct_site_rec,
x_cust_acct_site_id => l_cust_acct_site_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) ': ' l_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error mesg' l_output);
ELSE
DBMS_OUTPUT.put_line ('l_cust_acct_site_id ' l_cust_acct_site_id);
END IF;
END;
R12 AR Org contact information
*****************DECLARE
DECLARE
l_org_contact_id NUMBER;
l_party_rel_id_contactrel NUMBER;
l_party_id NUMBER;
l_party_number VARCHAR2 (20);
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
---THIS IS THE SUBJECT ID (PERSON INFO)
l_org_contact_rec.party_rel_rec.subject_id := 308026;
l_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
l_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
---THIS IS THE OBJECT ID (ORGANIZATION INFO)
l_org_contact_rec.party_rel_rec.object_id := 308013;
l_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
l_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
l_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
l_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
l_org_contact_rec.created_by_module := 'TCA_MIGRATION';
hz_party_contact_v2pub.create_org_contact
(p_init_msg_list => 'T',
p_org_contact_rec => l_org_contact_rec,
x_org_contact_id => l_org_contact_id,
x_party_rel_id => l_party_rel_id_contactrel,
x_party_id => l_party_id,
x_party_number => l_party_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' l_return_status);
IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) ': ' l_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error mesg' l_output);
ELSE
DBMS_OUTPUT.put_line ('l_org_contact_id ' l_org_contact_id);
DBMS_OUTPUT.put_line ( 'l_party_rel_id_contactrel '
l_party_rel_id_contactrel
);
DBMS_OUTPUT.put_line ('l_party_id ' l_party_id);
DBMS_OUTPUT.put_line ('l_party_number ' l_party_number);
END IF;
END;
R12 AR Phone Contact point Creation
*****************DECLARE
DECLARE
l_contact_point_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
l_email_rec hz_contact_point_v2pub.email_rec_type;
l_phone_rec hz_contact_point_v2pub.phone_rec_type;
l_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
l_web_rec_type hz_contact_point_v2pub.web_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_contact_point_rec.contact_point_type := 'PHONE';
l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
l_contact_point_rec.created_by_module := 'TCA_MIGRATION';
l_contact_point_rec.owner_table_id := 308013;
l_phone_rec.phone_area_code := '703';
l_phone_rec.phone_number := '8441213';
l_phone_rec.phone_line_type := 'GEN';
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => 'T',
p_contact_point_rec => l_contact_point_rec,
p_edi_rec => l_edi_rec_type,
p_email_rec => l_email_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec_type,
p_web_rec => l_web_rec_type,
x_contact_point_id => l_contact_point_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' l_return_status);
IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) ': ' l_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error mesg' l_output);
ELSE
DBMS_OUTPUT.put_line ('l_contact_point_id ' l_contact_point_id);
END IF;
END;
R12 AR Email Contact pointn Creation
*****************DECLARE
DECLARE
l_contact_point_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
l_email_rec hz_contact_point_v2pub.email_rec_type;
l_phone_rec hz_contact_point_v2pub.phone_rec_type;
l_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
l_web_rec_type hz_contact_point_v2pub.web_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_contact_point_rec.contact_point_type := 'EMAIL';
l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
l_contact_point_rec.created_by_module := 'TCA_MIGRATION';
l_contact_point_rec.owner_table_id := 308013;
l_email_rec.email_address := 'test@xxx.com';
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => 'T',
p_contact_point_rec => l_contact_point_rec,
p_edi_rec => l_edi_rec_type,
p_email_rec => l_email_rec,
p_phone_rec => l_phone_rec,
p_telex_rec => l_telex_rec_type,
p_web_rec => l_web_rec_type,
x_contact_point_id => l_contact_point_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' l_return_status);
IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) ': ' l_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error mesg' l_output);
ELSE
DBMS_OUTPUT.put_line ('l_contact_point_id ' l_contact_point_id);
END IF;
END;
R12 AR Fax Contact point Creation
*****************DECLARE
DECLARE
l_contact_point_id NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_output VARCHAR2 (1000);
l_msg_dummy VARCHAR2 (1000);
l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
l_edi_rec_type hz_contact_point_v2pub.edi_rec_type;
l_email_rec hz_contact_point_v2pub.email_rec_type;
l_fax_rec hz_contact_point_v2pub.phone_rec_type;
l_telex_rec_type hz_contact_point_v2pub.telex_rec_type;
l_web_rec_type hz_contact_point_v2pub.web_rec_type;
BEGIN
fnd_global.apps_initialize(user_id in number,resp_id in number,resp_appl_id in number);
l_contact_point_rec.contact_point_type := 'PHONE';
l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
l_contact_point_rec.created_by_module := 'TCA_MIGRATION';
l_contact_point_rec.owner_table_id := 308013;
l_fax_rec.phone_area_code := '104';
l_fax_rec.phone_number := '234-565';
l_fax_rec.phone_line_type := 'FAX';
hz_contact_point_v2pub.create_contact_point
(p_init_msg_list => 'T',
p_contact_point_rec => l_contact_point_rec,
p_edi_rec => l_edi_rec_type,
p_email_rec => l_email_rec,
p_phone_rec => l_fax_rec,
p_telex_rec => l_telex_rec_type,
p_web_rec => l_web_rec_type,
x_contact_point_id => l_contact_point_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
DBMS_OUTPUT.put_line ('l_return_status ' l_return_status);
IF l_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (i) ': ' l_msg_data);
END LOOP;
DBMS_OUTPUT.put_line ('Error mesg' l_output);
ELSE
DBMS_OUTPUT.put_line ('l_contact_point_id ' l_contact_point_id);
END IF;
END;

Query to retrive the PO and AP invoice information

SELECT
A.ORG_ID “ORG ID”,
E.VENDOR_NAME “VENDOR NAME”,
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) “VENDOR TYPE”,
F.VENDOR_SITE_CODE “VENDOR SITE”,
F.ADDRESS_LINE1 “ADDRESS”,
F.CITY “CITY”,
F.COUNTRY “COUNTRY”,
TO_CHAR(TRUNC(D.CREATION_DATE)) “PO DATE”,
D.SEGMENT1 “PO NUMBER”,
D.TYPE_LOOKUP_CODE “PO TYPE”,
C.QUANTITY_ORDERED “QTY ORDERED”,
C.QUANTITY_CANCELLED “QTY CANCALLED”,
G.ITEM_DESCRIPTION “ITEM DESCRIPTION”,
G.UNIT_PRICE “UNIT PRICE”,
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) “PO Line Amount”,
(SELECT
DECODE(PH.APPROVED_FLAG, ‘Y’, ‘Approved’)
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) “PO STATUS”,
A.INVOICE_TYPE_LOOKUP_CODE “INVOICE TYPE”,
A.INVOICE_AMOUNT “INVOICE AMOUNT”,
TO_CHAR(TRUNC(A.INVOICE_DATE)) “INVOICE DATE”,
A.INVOICE_NUM “INVOICE NUMBER”,
(SELECT
DECODE(X.MATCH_STATUS_FLAG, ‘A’, ‘Approved’)
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)”Invoice Approved?”,
A.AMOUNT_PAID,
H.AMOUNT,
I.CHECK_NUMBER “CHEQUE NUMBER”,
TO_CHAR(TRUNC(I.CHECK_DATE)) “PAYMENT DATE”
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = ‘Y’
AND D.TYPE_LOOKUP_CODE != ‘BLANKET’

Query to Identify All Requistions errror out in WF

SELECT prh.segment1, prh.org_id, prh.authorization_status,
ac.display_name activity, ias.activity_result_code RESULT,
ias.error_name error_name, ias.error_message error_message,
ias.error_stack error_stack
FROM po_requisition_headers_all prh,
wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'REQAPPRV'
AND ias.item_key = prh.wf_item_key
AND ias.item_type = prh.wf_item_type
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = 'REQAPPRV'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date
ORDER BY prh.segment1, ias.execution_time;

Query To Identify All Purchase Orders errored out in WF

SELECT pha.segment1, pha.org_id, pha.authorization_status,
ac.display_name activity, ias.activity_result_code RESULT,
ias.error_name error_name, ias.error_message error_message,
ias.error_stack error_stackfrom
FROM po_headers_all pha,
wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = 'POAPPRV'
AND ias.item_key = pha.wf_item_key
AND ias.item_type = pha.wf_item_type
AND pha.authorization_status IN ('IN PROCESS', 'PRE-APPROVED')
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = 'POAPPRV'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date
ORDER BY pha.segment1, ias.execution_time

Query to find requisition number from PO number

SELECT segment1
FROM po_requisition_headers_all
WHERE requisition_header_id IN
(SELECT requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id IN
(SELECT requisition_line_id
FROM po_req_distributions_all
WHERE distribution_id IN
(SELECT req_distribution_id
FROM po_distributions_all
WHERE po_header_id IN
(SELECT po_header_id
FROM po_headers_all
WHERE segment1 = Give Po Number)
)
)
)

PO Requisitions and Project Details

select hou.name ope_unit
,prh.segment1 req_number
,pol.currency_code
,pol.quantity
,pol.quantity_received
,pol.quantity*pol.unit_price amount
,ppa.segment1 project
,pat.task_number
,pod.expenditure_type
,(select name from apps.hr_all_organization_units where organization_id=pod.expenditure_organization_id) EXPENDITURE_ORGANIZATION
,(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pod.code_combination_id ) charge_accounts
,(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pod.accrual_account_id ) accrual_accounts
,trunc(prh.creation_date) requ_cre_date
,povs.PAY_GROUP_LOOKUP_CODE
,povs.PAYMENT_METHOD_LOOKUP_CODE
,pov.vendor_name
,povs.vendor_site_code
,polt.line_type
,mc.segment1'.'mc.segment2
from
po_requisition_headers_all prh,
po_requisition_lines_all pol,
po_vendors pov,
po_vendor_sites_all povs,
po_line_types polt,
po_req_distributions_all pod,
mtl_categories mc,
pa_projects_all ppa,
pa_tasks pat,
hr_all_organization_units hou
where 1=1--prh.segment1='800034786'
and prh.requisition_header_id = pol.requisition_header_id
and pol.vendor_id = pov.vendor_id
and pol.vendor_site_id = povs.vendor_site_id
and pol.requisition_line_id = pod.requisition_line_id
and pol.line_type_id = polt.line_type_id
and pol.category_id = mc.category_id
and pod.project_id = ppa.project_id
and pod.task_id = pat.task_id
and prh.org_id = hou.organization_id
--and trunc(prh.creation_date) >= to_date('22-SEP-2009','DD-MON-YYYY')
order by 1,2

Purchase Orders with Project Details

SELECT
hou.name Operating_unit,
ph.segment1 po_num,
ph.currency_code,
pla.quantity quantity_ordered,
pla.quantity_received,
pl.line_num,
pl.quantity Line_quantity,
pl.unit_price*pl.quantity Amt,
ppa.segment1 project_number,
pt.task_number ,
pda.expenditure_type,
--api.invoice_num,
(select name from apps.hr_all_organization_units where organization_id=pda.expenditure_organization_id) EXPENDITURE_ORGANIZATION,
(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pda.code_combination_id ) charge_accounts ,
(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pda.accrual_account_id ) accrual_accounts ,
trunc(ph.creation_date),
povs.PAY_GROUP_LOOKUP_CODE,
povs.PAYMENT_METHOD_LOOKUP_CODE,
vendor_name,
vendor_site_code,
povs.attribute1
from apps.po_headers_all ph,
apps.po_lines_all pl,
apps.po_line_locations_all pla,
apps.hr_operating_units hou,
apps.po_line_types plt,
apps.pa_projects_all ppa,
apps.po_distributions_all pda,
apps.pa_tasks pt,
apps.gl_code_combinations_kfv gcc,
apps.hr_locations hl,
apps.po_vendors pov,
apps.po_vendor_sites_all povs
--ap_invoices_all api
where 1=1
--and ph.segment1='TU672620'
and ph.po_header_id=pl.po_header_id
and pl.po_line_id=pla.po_line_id
and pl.line_type_id=plt.line_type_id
and ph.po_header_id=pda.po_header_id
and pda.line_location_id=pla.line_location_id
and pda.project_id=ppa.project_id
and pda.task_id=pt.task_id(+)
--and (pda.project_id is null or pda.task_id is null)
and gcc.code_combination_id =pda.code_combination_id
and ph.AUTHORIZATION_STATUS ='APPROVED'
--AND trunc(ph.creation_date) >= to_date(:p_date) -- MM/DD/YYYY
--and ph.APPROVED_FLAG ='Y'
and hl.location_id=ph.ship_to_location_id
and hou.organization_id=pda.org_id
and ph.vendor_id = pov.vendor_id
and ph.vendor_site_id = povs.vendor_site_id
--and api.po_header_id=ph.po_header_id
--and api.project_id=ppa.project_id
--and api.task_id=pt.task_id

Employee Extraction

SELECT papf.attribute2 legacy_employee_number, papf.last_name,
papf.first_name, papf.title, papf.pre_name_adjunct, papf.suffix,
papf.middle_names, hl_sex.meaning gender, pptt.user_person_type,
papf.employee_number, papf.national_identifier,
papf.effective_start_date, papf.effective_end_date,
papf.start_date original_hire_date, papf.date_of_birth,
papf.town_of_birth, papf.region_of_birth, papf.country_of_birth,
TRUNC (MONTHS_BETWEEN (SYSDATE, papf.date_of_birth) / 12) age,
papf.nationality, hl_dis.meaning registered_disabled,
hl_vet.meaning veteran_menaing, papf.office_number,
papf.internal_location, papf.mailstop, papf.email_address,
papf.known_as, papf.previous_last_name, papf.correspondence_language,
ppos.adjusted_svc_date, hl_mlt.meaning mail_to,
hl_eth.meaning ethnic_origin, papf.per_information2 i9_status,
papf.per_information3 i9_expiration_date,
hl_mar.meaning marital_status, papf.attribute1 nt_login,
pa.address_line1, pa.address_line2, pa.address_line3, pa.town_or_city,
pa.region_2 state, pa.region_1 county, pa.postal_code, pa.country,
pa.telephone_number_1, pa.telephone_number_2, pa.primary_flag,
pa.date_from, pa.date_to, pa.style address_style,
hou.NAME organization_name, pjt.NAME job_name, pgt.NAME grade_name,
hla.location_code, sup_papf.full_name supervisor_full_name,
sup_papf.employee_number supervisor_employee_number, paaf.normal_hours,
paaf.frequency, hl_cr.meaning change_reason, paaf.manager_flag,
pap.payroll_name, past.user_status assignment_status,
hl_ac.meaning assignment_category, ppg.group_name, gre.NAME gre,
paaf.ass_attribute4 ttp_yr, ppp.change_date, ppp.proposed_salary_n,
ppp.proposal_reason, ppp.approved, ppp.next_sal_review_date,
hl_pay.meaning salary_basis_meaning, hl_pb.meaning pay_basis_meaning,
ppb.pay_annualization_factor, paaf.ass_attribute1 shift,
paaf.ass_attribute2 hrs_per_day, pp.phone_number home_phone_number,
pp1.phone_number pager_number
FROM per_all_people_f papf,
per_all_assignments_f paaf,
per_addresses pa,
per_pay_proposals ppp,
per_person_types_tl pptt,
pay_people_groups ppg,
hr_lookups hl_sex,
apps.hr_lookups hl_dis,
apps.hr_lookups hl_vet,
apps.hr_lookups hl_eth,
apps.hr_lookups hl_mar,
apps.hr_lookups hl_mlt,
apps.hr_lookups hl_cr,
apps.hr_lookups hl_ac,
apps.hr_lookups hl_pay,
apps.hr_lookups hl_pb,
hr.per_periods_of_service ppos,
apps.hr_organization_units hou,
hr.per_jobs_tl pjt,
hr.per_grades_tl pgt,
hr.hr_locations_all hla,
pay_all_payrolls_f pap,
hr.per_pay_bases ppb,
per_assignment_status_types_tl past,
hr_soft_coding_keyflex hsc,
holx_emp_conv_stg stg,
hr.per_all_people_f sup_papf,
per_phones pp,
per_phones pp1,
hr_organization_units gre
WHERE papf.person_id = stg.new_person_id
AND pptt.person_type_id(+) = papf.person_type_id
AND hsc.segment1 = gre.organization_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id(+)
AND past.LANGUAGE = 'US'
AND ppg.people_group_id(+) = paaf.people_group_id
AND hou.organization_id(+) = paaf.organization_id
AND pjt.job_id(+) = paaf.job_id
AND pjt.LANGUAGE(+) = USERENV ('LANG')
AND pgt.grade_id(+) = paaf.grade_id
AND pgt.LANGUAGE(+) = USERENV ('LANG')
AND hla.location_id(+) = paaf.location_id
-- AND hla.business_group_id(+) = paaf.business_group_id
AND pap.payroll_id(+) = paaf.payroll_id
AND pptt.LANGUAGE(+) = USERENV ('LANG')
AND hl_sex.lookup_code(+) = papf.sex
AND hl_sex.lookup_type(+) = 'SEX'
AND hl_pay.lookup_code(+) = ppp.proposal_reason
AND hl_pay.lookup_type(+) = 'PROPOSAL_REASON'
AND ppb.pay_basis_id(+) = paaf.pay_basis_id
AND hl_pb.lookup_code(+) = ppb.pay_basis
AND hl_pb.lookup_type(+) = 'PAY_BASIS'
AND hl_ac.lookup_code(+) = paaf.employment_category
AND hl_ac.lookup_type(+) = 'EMP_CAT'
AND hl_dis.lookup_code(+) = papf.registered_disabled_flag
AND hl_dis.lookup_type(+) = 'REGISTERED_DISABLED'
AND hl_vet.lookup_code(+) = papf.per_information5
AND hl_vet.lookup_type(+) = 'US_VETERAN_STATUS'
AND hl_eth.lookup_code(+) = papf.per_information1
AND hl_eth.lookup_type(+) = 'US_ETHNIC_GROUP'
AND hl_mar.lookup_code(+) = papf.marital_status
AND hl_mar.lookup_type(+) = 'MAR_STATUS'
AND hl_mlt.lookup_code(+) = papf.expense_check_send_to_address
AND hl_mlt.lookup_type(+) = 'HOME_OFFICE'
AND hl_cr.lookup_code(+) = paaf.change_reason
AND hl_cr.lookup_type(+) = 'EMP_ASSIGN_REASON'
AND ppos.person_id(+) = papf.person_id
AND stg.status_stg LIKE 'S%'
AND paaf.person_id = papf.person_id
AND paaf.assignment_id = stg.assignment_id
AND paaf.person_id = pa.person_id
AND papf.person_id = pa.person_id
AND pa.address_id = stg.address_id
AND ppp.assignment_id = paaf.assignment_id
AND hsc.soft_coding_keyflex_id(+) = paaf.soft_coding_keyflex_id
AND papf.effective_end_date > SYSDATE
AND paaf.effective_end_date > SYSDATE
AND sup_papf.person_id(+) = paaf.supervisor_id
AND sup_papf.effective_end_date(+) > TRUNC (SYSDATE)
AND pp.parent_id(+) = papf.person_id
AND pp.phone_type(+) = 'H1'
AND pp1.parent_id(+) = papf.person_id
AND pp1.phone_type(+) = 'P'

Query to find Employee Contact information

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type

Check Provider and receiver projects for a project

SELECT org_name,
segment1 "Project_number",
name,
start_date,
project_status_name,
country,
rece_project,
provider_project,
concatenated_segments "account",
source
FROM
(
select distinct hou.name org_name,ppa.segment1,
ppa.name,
ppa.start_date,
pps.project_status_name,
raa.country,
(select ppa1.segment1
from apps.pa_projects_all ppa1,
apps.pa_tasks pat1
where ppa1.project_id = pat1.project_id
and pat1.task_id = ppc.receiver_task_id
) rece_project,
prov_project.project provider_project,
gcc.concatenated_segments,
'expend' source
from
apps.pa_projects_all ppa,
apps.pa_project_customers ppc,
apps.ra_addresses_all raa,
apps.pa_project_statuses pps,
apps.pa_expenditure_items_all pei,
apps.pa_cost_distribution_lines_all pcd,
apps.gl_code_combinations_kfv gcc,
apps.hr_operating_units hou,
(SELECT
replace(substr(max(sys_connect_by_path(prov_proj,',')),2),' ','') project,project_id,rec_proj
FROM
(select ppa1.segment1 prov_proj,
ppa2.project_id,
ppa2.segment1 rec_proj,
row_number() over (partition by ppa2.project_id order by ppa1.segment1) rn
from apps.pa_projects_all ppa1
,apps.pa_project_customers ppc
,apps.pa_tasks pat1
,apps.pa_projects_all ppa2
where 1=1
and ppc.project_id = ppa1.project_id
and receiver_task_id = pat1.task_id
and to_char(ppa2.creation_date,'YYYY') = '2009'
--and ppa2.project_id = 28490
and pat1.project_id = ppa2.project_id
) start with rn=1 connect by prior rn = rn - 1 and
prior project_id = project_id
group by project_id,rec_proj) prov_project
where ppa.project_id = ppc.project_id
and ppc.ship_to_address_id = raa.address_id
and ppa.project_status_code = pps.project_status_code
and ppa.project_id = pei.project_id
and pei.expenditure_item_id = pcd.expenditure_item_id
and pcd.dr_code_combination_id = gcc.code_combination_id
and hou.organization_id = ppa.org_id
and ppa.project_id = prov_project.project_id(+)
and to_char(ppa.creation_date,'YYYY') = '2009'
--and ppa.project_id = prov_project.project_id
--and ppa.org_id = 192
--and ppa.project_id in(28490,118671)
--order by 1
UNION
select distinct hou.name org_name,ppa.segment1,
ppa.name,
ppa.start_date,
pps.project_status_name,
raa.country,
(select ppa1.segment1
from apps.pa_projects_all ppa1,
apps.pa_tasks pat1
where ppa1.project_id = pat1.project_id
and pat1.task_id = ppc.receiver_task_id
) rece_project,
prov_project.project provider_project,
gcc.concatenated_segments,
'event' source
from
apps.pa_projects_all ppa,
apps.pa_project_customers ppc,
apps.ra_addresses_all raa,
apps.pa_project_statuses pps,
apps.pa_events pe,
apps.pa_cust_event_rdl_all pcd,
apps.gl_code_combinations_kfv gcc,
apps.hr_operating_units hou,
(SELECT
replace(substr(max(sys_connect_by_path(prov_proj,',')),2),' ','') project,project_id,rec_proj
FROM
(select ppa1.segment1 prov_proj,
ppa2.project_id,
ppa2.segment1 rec_proj,
row_number() over (partition by ppa2.project_id order by ppa1.segment1) rn
from apps.pa_projects_all ppa1
,apps.pa_project_customers ppc
,apps.pa_tasks pat1
,apps.pa_projects_all ppa2
where 1=1
and ppc.project_id = ppa1.project_id
and receiver_task_id = pat1.task_id
and to_char(ppa2.creation_date,'YYYY') = '2009'
--and ppa2.project_id = 28490
and pat1.project_id = ppa2.project_id
) start with rn=1 connect by prior rn = rn - 1 and
prior project_id = project_id
group by project_id,rec_proj) prov_project
where ppa.project_id = ppc.project_id
and ppc.ship_to_address_id = raa.address_id
and ppa.project_status_code = pps.project_status_code
and ppa.project_id = pe.project_id
and pe.project_id = pcd.project_id
and pe.event_num = pcd.event_num
and pcd.code_combination_id = gcc.code_combination_id
and hou.organization_id = ppa.org_id
and ppa.project_id = prov_project.project_id(+)
and to_char(ppa.creation_date,'YYYY') = '2009'
--and ppa.project_id = prov_project.project_id
--and ppa.org_id = 192
--and ppa.project_id in(28490,118671)
)
order by 1,2

Query to find project, classfication and max trans date happend on exp/Rev/Inv

SELECT DISTINCT hou.NAME operating_unit,
pa.segment1 project_number,
pa.NAME project_name,
pa.creation_date proj_creation_date,
haou.NAME project_organization,
pef.full_name manager,
pa.description project_description,
ps.project_status_name project_status,
pc1.class_code Test,
rc.country,
rac.customer_number,
rac.customer_name,
activity.last_activity_date,
activity.LE LE_account,
rece.segment1 receiver_project,
rece.country receiver_country,
rece.name receiver_OU,
rece.class_code receiver_class,
tem.segment1 created_from_project,
tem.name created_from_project_name
FROM apps.pa_projects_all pa,
apps.hr_operating_units hou,
apps.hr_all_organization_units haou,
apps.pa_project_classes pc1,
apps.pa_project_players pp,
apps.per_all_people_f pef,
apps.pa_project_statuses ps,
apps.pa_project_customers ppc,
apps.ra_addresses_all rc,
apps.ra_customers rac,
apps.pa_projects_all tem,
(
select max(creation_date) last_activity_date,max(segment1) LE,project_id
from
(SELECT invoice_date creation_date,gcc.segment1,pdia.project_id
FROM apps.pa_draft_invoices_all pdia,
apps.RA_CUSTOMER_TRX_ALL r,
apps.ra_cust_trx_line_gl_dist_all CTLGD,
apps.ra_customer_trx_lines_all CTL,
apps.gl_code_combinations gcc
where ra_invoice_number = r.trx_number(+)
and R.customer_trx_id = CTL.customer_trx_id(+)
AND CTLGD.customer_trx_line_id(+) = CTL.customer_trx_line_id
AND gcc.code_combination_id(+)=CTLGD.CODE_COMBINATION_ID
AND ctl.line_type(+) = 'LINE'
--and project_id = 553
and invoice_date = (
SELECT max(invoice_date)
FROM apps.pa_draft_invoices_all
where project_id = pdia.project_id
)
UNION
SELECT creation_date,gcc.segment1,pdra.project_id
FROM apps.pa_draft_revenues_all pdra,
apps.gl_code_combinations gcc
where 1=1
AND gcc.code_combination_id(+)=pdra.UNEARNED_CODE_COMBINATION_ID
--and project_id = 553
and creation_date = (
SELECT max(creation_date)
FROM apps.pa_draft_revenues_all
where project_id = pdra.project_id
)
UNION
select expenditure_item_date creation_date,gcc.segment1,pei.project_id
from apps.pa_expenditure_items_all pei,
apps.PA_COST_DIST_LINES_V pcd,
apps.gl_code_combinations gcc
WHERE pei.expenditure_item_id = pcd.expenditure_item_id(+)
and pcd.DR_CODE_COMBINATION_ID = gcc.code_combination_id(+)
--and pei.project_id = 553
AND expenditure_item_date = (
SELECT max(expenditure_item_date) creation_date
FROM apps.pa_expenditure_items_all
where project_id = pei.project_id
)
) group by project_id
) activity,
(
select ppa.segment1,raa.country,hou.name,ppcc.class_code,pt.task_id
FROM
apps.pa_projects_all ppa,
apps.pa_project_customers ppc,
apps.ra_addresses_all raa,
apps.pa_tasks pt,
apps.pa_project_classes ppcc,
apps.hr_operating_units hou
where 1=1
and ppa.project_id = ppc.project_id
AND ppc.ship_to_address_id = raa.address_id
AND PROJECT_RELATIONSHIP_CODE = 'PRIMARY'
and ppa.project_id = pt.project_id
--AND pt.task_id = 146747
AND ppa.project_id = ppcc.project_id(+)
AND ppcc.class_category(+) = 'Rec Class'
AND ppa.org_id = hou.organization_id
) rece
WHERE pa.org_id=hou.organization_id AND
pa.carrying_out_organization_id=haou.organization_id AND
pa.project_id=ppc.project_id AND
ppc.PROJECT_RELATIONSHIP_CODE='PRIMARY' AND
ppc.ship_to_address_id=rc.address_id AND
pa.project_id=pc1.project_id(+) AND
pc1.class_category(+)= 'Test Class' AND
pp.project_id=pa.project_id AND pp.project_role_type='PROJECT MANAGER' AND
pp.person_id=pef.person_id AND
NVL(pef.effective_end_date,SYSDATE+1)>SYSDATE AND
pa.project_status_code=ps.project_status_code AND
ppc.customer_id=rac.customer_id and
ps.status_type = 'PROJECT' AND
pa.template_flag = 'N' AND
pa.project_id = activity.project_id(+) and
pa.created_from_project_id = tem.project_id AND
ppc.receiver_task_id = rece.task_id(+) AND
--pa.project_id in(1780507,146747) and
--trunc(pa.creation_date) >= to_date(:p_date) AND -- MM/DD/YYYY
ORDER BY 1, 2

AP Invoice and PA Exp Details

select distinct pei.EXPENDITURE_ITEM_ID, pei.EXPENDITURE_ID,pei.TASK_ID,pei.project_id,ppa.segment1 PROJECT_NUMBER,ppa.name Project_name,
pei.EXPENDITURE_TYPE,pei.QUANTITY,RAW_COST,PEI.ATTRIBUTE1,TRANSACTION_SOURCE,PEI.ORG_ID,pea.ORIG_EXP_TXN_REFERENCE1 INVOICE_ID,
aih.invoice_num,poh.segment1 PO_NUMBER,PA_PERIOD_NAME
from apps.pa_expenditure_items_all pei,
apps.pa_expenditures_all pea,
apps.pa_projects_all ppa,
apps.AP_INVOICE_DISTRIBUTIONS_ALL aid,
apps.ap_invoices_all aih,
apps.po_distributions_all pd,
apps.po_headers_all poh,
apps.PA_COST_DIST_LINES_V pcdl
where pei.org_id = 170 --IN (171,170)
AND ppa.project_id = pei.project_id
AND pei.expenditure_id = pea.expenditure_id
AND pei.org_id = pea.org_id
and aid.po_distribution_id = pd.po_distribution_id
and pd.po_header_id = poh.po_header_id
and aid.invoice_id = pea.ORIG_EXP_TXN_REFERENCE1
AND aid.invoice_id = aih.INVOICE_ID
AND pcdl.EXPENDITURE_ITEM_ID = pei.EXPENDITURE_ITEM_ID
AND pei.org_id = pcdl.org_id
and TRANSACTION_SOURCE = 'AP INVOICE'
AND pcdl.pa_period_name = 'OCT-08'
--and aih.invoice_num = 'UI901156'
--and expenditure_item_id = 687492

PA Revnue extract for exp and events

(select -- Query for exp revenues
hou.name "OU_NAME",
ppa.segment1 "Proj_num",
ppa.name Project_name,
ppa.creation_date "Proj_creation_date",
pef.full_name Project_Manager,
pdra.draft_revenue_num,
perd.draft_revenue_item_line_num,
pdra.creation_date revenue_creation_date,
(
SELECT MEANING
FROM PA_LOOKUPS LK
WHERE
LK.LOOKUP_TYPE = 'INVOICE/REVENUE STATUS' AND
LK.LOOKUP_CODE = DECODE(PDRA.GENERATION_ERROR_FLAG, 'Y', 'GENERATION ERROR', DECODE(PDRA.RELEASED_DATE, NULL, 'UNRELEASED', DECODE(PDRA.TRANSFER_STATUS_CODE, 'P', 'RELEASED', 'X', 'REJECTED IN TRANSFER', 'T', 'TRANSFERRED', 'A', 'ACCEPTED', 'R', 'REJECTED', 'RECEIVED')))
) REVENUE_STATUS,
PDRA.TRANSFER_REJECTION_REASON,
perd.expenditure_item_id "Transaction Id",
pat.task_number,
pat.task_name,
haou.name expenditure_org,
pei.expenditure_type,
NULL "Event Num",
amount ,
abs(pdra.unearned_revenue_cr) "Unearned Revenue amount",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where code_combination_id = unearned_code_combination_id ) "Unearned Revenue Account",
pdra.unbilled_receivable_dr "Unbilled Receivables amount",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where code_combination_id = unbilled_code_combination_id ) "Unbilled Receivables Account",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where code_combination_id = perd.code_combination_id ) "Expnd/Event Revenue",
perd.project_currency_code
from
pa_draft_revenues_all pdra,
pa_cust_rev_dist_lines_all perd,
pa_expenditure_items_all pei,
apps.hr_all_organization_units haou,
apps.pa_tasks pat,
apps.pa_projects_all ppa,
apps.hr_operating_units hou ,
apps.pa_project_players pp,
apps.per_all_people_f pef
where 1=1--pdra.project_id = 147587
and pdra.project_id = perd.project_id
and pdra.DRAFT_REVENUE_NUM = perd.DRAFT_REVENUE_NUM
and ppa.project_id = pdra.project_id
and ppa.project_id = perd.project_id
and ppa.org_id = hou.organization_id
and
--trunc(ppa.creation_date) >= to_date(:p_date) AND -- MM/DD/YYYY
ppa.project_id=pp.project_id AND
pp.project_role_type='PROJECT MANAGER' AND
pp.person_id=pef.person_id AND
SYSDATE BETWEEN pef.effective_start_date AND NVL(pef.effective_end_date,SYSDATE) AND
SYSDATE BETWEEN pp.start_date_active AND NVL(pp.end_date_active,SYSDATE) AND
perd.expenditure_item_id = pei.expenditure_item_id AND
ppa.project_id = pei.project_id AND
pat.task_id = pei.task_id AND
pat.project_id = pei.project_id AND
haou.organization_id = pei.cc_recvr_organization_id
UNION
select -- -- Query for exp revenures
hou.name "OU_NAME",
ppa.segment1 "Proj_num",
ppa.name Project_name,
ppa.creation_date "Proj_creation_date",
pef.full_name Project_Manager,
pdra.draft_revenue_num,
perd.draft_revenue_item_line_num,
pdra.creation_date revenue_creation_date,
(
SELECT MEANING
FROM PA_LOOKUPS LK
WHERE
LK.LOOKUP_TYPE = 'INVOICE/REVENUE STATUS' AND
LK.LOOKUP_CODE = DECODE(PDRA.GENERATION_ERROR_FLAG, 'Y', 'GENERATION ERROR', DECODE(PDRA.RELEASED_DATE, NULL, 'UNRELEASED', DECODE(PDRA.TRANSFER_STATUS_CODE, 'P', 'RELEASED', 'X', 'REJECTED IN TRANSFER', 'T', 'TRANSFERRED', 'A', 'ACCEPTED', 'R', 'REJECTED', 'RECEIVED')))
) REVENUE_STATUS,
PDRA.TRANSFER_REJECTION_REASON,
NULL "Transaction Id",
NULL task_number,
NULL task_name,
NULL expenditure_org,
NULL expenditure_type,
event_num "Event Num",
amount ,
abs(pdra.unearned_revenue_cr) "Unearned Revenue amount",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where CODE_COMBINATION_ID = UNEARNED_CODE_COMBINATION_ID ) "Unearned Revenue Account",
pdra.unbilled_receivable_dr "Unbilled Receivables amount",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where CODE_COMBINATION_ID = UNBILLED_CODE_COMBINATION_ID ) "Unbilled Receivables Account",
(select gcccr.segment1'-'gcccr.segment2'-'gcccr.segment3'-'gcccr.segment4'-'gcccr.segment5'-'gcccr.segment6'-'gcccr.segment7'-'gcccr.segment8
from apps.gl_code_combinations gcccr
where CODE_COMBINATION_ID = perd.CODE_COMBINATION_ID ) "Expnd/Event Revenue",
perd.PROJECT_CURRENCY_CODE
from
pa_draft_revenues_all pdra,
pa_cust_event_rdl_all perd,
apps.pa_projects_all ppa,
apps.hr_operating_units hou,
apps.pa_project_players pp,
apps.per_all_people_f pef
where 1=1--pdra.project_id = 147587
and pdra.project_id = perd.project_id
and pdra.DRAFT_REVENUE_NUM = perd.DRAFT_REVENUE_NUM
and ppa.project_id = pdra.project_id
and ppa.project_id = perd.project_id
and ppa.org_id = hou.organization_id
and
--trunc(ppa.creation_date) >= to_date(:p_date) AND -- MM/DD/YYYY
ppa.project_id=pp.project_id AND
pp.project_role_type='PROJECT MANAGER' AND
pp.person_id=pef.person_id AND
SYSDATE BETWEEN pef.effective_start_date AND NVL(pef.effective_end_date,SYSDATE) AND
SYSDATE BETWEEN pp.start_date_active AND NVL(pp.end_date_active,SYSDATE)
) order by 1,2

Being Yogendra: Meebo Chat Gadget for iGoogle

Being Yogendra: Meebo Chat Gadget for iGoogle

Find Customer information(bill&ship) for a Slaes Order

SELECT ooh.order_number
, hp_bill.party_name
, hl_ship.address1 Decode(hl_ship.address2,NULL,'',chr(10))
hl_ship.address2Decode(hl_ship.address3,NULL,'',chr(10))
hl_ship.address3Decode(hl_ship.address4,NULL,'',chr(10))
hl_ship.address4Decode(hl_ship.city,NULL,'',chr(10))
hl_ship.city Decode(hl_ship.state,NULL,'',',')
hl_ship.state Decode(hl_ship.postal_code,'',',')
hl_ship.postal_code ship_to_address
, hl_bill.address1 Decode(hl_bill.address2,NULL,'',chr(10))
hl_bill.address2Decode(hl_bill.address3,NULL,'',chr(10))
hl_bill.address3Decode(hl_bill.address4,NULL,'',chr(10))
hl_bill.address4Decode(hl_bill.city,NULL,'',chr(10))
hl_bill.city Decode(hl_bill.state,NULL,'',',')
hl_bill.state Decode(hl_bill.postal_code,'',',')
hl_bill.postal_code bill_to_address
, ooh.transactional_curr_code currency_code
, mp.organization_code
, ooh.fob_point_code
, ooh.freight_terms_code
, ooh.cust_po_number
FROM oe_order_headers_all ooh
, hz_cust_site_uses_all hcs_ship
, hz_cust_acct_sites_all hca_ship
, hz_party_sites hps_ship
, hz_parties hp_ship
, hz_locations hl_ship
, hz_cust_site_uses_all hcs_bill
, hz_cust_acct_sites_all hca_bill
, hz_party_sites hps_bill
, hz_parties hp_bill
, hz_locations hl_bill
, mtl_parameters mp
WHERE 1 = 1
AND header_id = :p_header_id
AND ooh.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND ooh.invoice_to_org_id = hcs_bill.site_use_id
AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND hca_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.party_id = hp_bill.party_id
AND hps_bill.location_id = hl_bill.location_id
AND mp.organization_id(+) = ooh.ship_from_org_id

Query to retrieve the line information of the Sales Order

SELECT oola.line_number "LINE NUMBER", oola.ordered_item "ORDERED ITEM",
oola.ordered_quantity "QTY", oola.order_quantity_uom "UOM",
oola.unit_selling_price "UNIT SELLING PRICE",
oola.cancelled_quantity "QTY CANCELLED",
oola.shipped_quantity "QTY SHIPPED", oola.tax_code "TAX CODE",
ott.NAME "LINE TYPE",
DECODE (opa.line_id,
NULL, DECODE (opa.credit_or_charge_flag,
'C', (-1) * opa.operand,
opa.operand
),
DECODE (opa.credit_or_charge_flag,
'C', DECODE (opa.arithmetic_operator,
'LUMPSUM', (-1) * (opa.operand),
(-1)
* ( oola.ordered_quantity
* opa.adjusted_amount
)
),
DECODE (opa.arithmetic_operator,
'LUMPSUM', opa.operand,
(oola.ordered_quantity * opa.adjusted_amount
)
)
)
) "LINE_CHARGES",
ol.meaning "CALCULATE PRICE FLAG", oola.pricing_quantity,
oola.unit_selling_price, oola.unit_list_price, oola.tax_value,
(oola.shipped_quantity) * (oola.unit_selling_price) "LINE TOTAL"
((oola.shipped_quantity) * (oola.unit_selling_price)
)
+ (DECODE (opa.line_id,
NULL, DECODE (opa.credit_or_charge_flag,
'C', (-1) * opa.operand,
opa.operand
),
DECODE (opa.credit_or_charge_flag,
'C', DECODE (opa.arithmetic_operator,
'LUMPSUM', (-1) * (opa.operand),
(-1)
* ( oola.ordered_quantity
* opa.adjusted_amount
)
),
DECODE (opa.arithmetic_operator,
'LUMPSUM', opa.operand,
(oola.ordered_quantity * opa.adjusted_amount
)
)
)
)
) "ORDER TOTAL"
FROM oe_order_lines_all oola,
oe_transaction_types_tl ott,
oe_price_adjustments opa,
oe_order_headers_all ooha,
oe_lookups ol
WHERE 1 = 1
AND oola.line_type_id = ott.transaction_type_id
AND opa.header_id = ooha.header_id
AND opa.line_id = oola.line_id(+)
AND opa.list_line_type_code = 'FREIGHT_CHARGE'
AND opa.applied_flag = 'Y'
AND ott.LANGUAGE = USERENV ('LANG')
AND oola.header_id = 1547
AND ol.lookup_type = 'CALCULATE_PRICE_FLAG'
AND oola.calculate_price_flag = ol.lookup_code

Query to retrive the header information of the Sales Order

SELECT ooha.header_id, ooha.order_number, ott.NAME "ORDER TYPE",
hp.party_name "CUSTOMER", hca.account_number "CUSTOMER NUMBER",
ooha.ordered_date "DATE ORDERED", qh.NAME "PRICE LIST",
ooha.transactional_curr_code "CURRENCY",
ooha.cust_po_number "CUSTOMER PO",
ooha.freight_carrier_code "SHIPPING METHOD",
ooha.flow_status_code "STATUS", rtt.NAME "PAYMENT TERMS",
mp.organization_code "WARE HOUSE", ol.meaning "FREIGHT TERMS",
ol1.meaning "SHIPMENT PRIORITY", al.meaning "FOB",
rsa.NAME "SALESPERSON",
hcsua.LOCATION
','
hl.address2
','
hl.city
','
hl.state
','
hl.postal_code
','
hl.county "BILL TO LOCATION",
hcsua1.LOCATION
','
hl1.address2
','
hl1.city
','
hl1.state
','
hl1.postal_code
','
hl1.county "SHIP TO LOCATION"
FROM oe_order_headers_all ooha,
oe_transaction_types_tl ott,
qp_list_headers qh,
ra_terms_tl rtt,
mtl_parameters mp,
ra_salesreps_all rsa,
hz_cust_accounts hca,
hz_parties hp,
hz_parties hp1,
hz_locations hl,
hz_locations hl1,
hz_cust_acct_sites_all hcasa,
hz_cust_acct_sites_all hcasa1,
hz_cust_site_uses_all hcsua,
hz_cust_site_uses_all hcsua1,
hz_party_sites hps,
hz_party_sites hps1,
oe_lookups ol,
oe_lookups ol1,
ar_lookups al
WHERE 1 = 1
AND ooha.order_number = 10265
AND ooha.sold_to_org_id = hca.cust_account_id
AND ooha.order_type_id = ott.transaction_type_id
AND ott.LANGUAGE = USERENV ('LANG')
AND rtt.LANGUAGE = USERENV ('LANG')
AND rtt.term_id = ooha.payment_term_id
AND qh.list_header_id = ooha.price_list_id
AND mp.organization_id = ooha.ship_from_org_id
AND ooha.salesrep_id = rsa.salesrep_id
AND hca.party_id = hp.party_id
AND hca.party_id = hp1.party_id
AND ooha.invoice_to_org_id = hcsua.site_use_id(+)
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id(+)
AND hcasa.party_site_id = hps.party_site_id(+)
AND hl.location_id(+) = hps.location_id
AND ooha.ship_to_org_id = hcsua1.site_use_id(+)
AND hcsua1.cust_acct_site_id = hcasa1.cust_acct_site_id(+)
AND hcasa1.party_site_id = hps1.party_site_id(+)
AND hl1.location_id(+) = hps1.location_id
AND ooha.freight_terms_code = ol.lookup_code
AND ooha.shipment_priority_code = ol1.lookup_code
AND al.lookup_code = ooha.fob_point_code;

OM Flow and Table Level information

OM Flow and table level Information
Steps in Order Cycle:
1) Order Entry
2) Booking
3) Pick release :
For this we have to go to
Shipping Responsibilty Release sales order
Here In this form , In the ORDER tab, we have to enter ORDER Number
And delete the Scheduled shipped Dates To & Requested Dates To.
In SHIPPING tab, set AUTO CREATE DELIVERY to YES. In INVENTORY tab enter WAREHOUSE, set AUTO ALLOCATE to YES and AUTO PICK CONFIRM to YES. IF we set AUTO PICK CONFIRM to NO, then We have to go for the following steps
1. go to Inventory Resp
Move order à Transact Move Order then it will ask for
warehouse information. Give the same name as before [M2]
In this form, In the HEADER tab, enter the BATCH
NUMBER of the order that is picked .Then Click FIND
Button. Click on VIEW/UPDATE Allocation, then
Click TRANSACT button. Then Transact button will be
deactivated then just close it and go to next step.
4) Shipping :
For this we need to go to Shipping Transaction Give the order Number, and click find
Then we can see the order status.
Then we have to click DELIVERY Tab Button, in the Action LOV
We have to choose, SHIP CONFIRM.
Then four concurrent program will run in the background.
Such As::
1.) INTERFACE TRIP Stop
2.) Commercial Invoice
3.) Packing Slip Report
4.) Bill of Lading
After this concurrent program will complete successfully, we have to run
One more WORKFLOW BACKGROUND PROGRAM.
· If we don’t want to ship all the items, that are PICKED, then we have to click LINE/LPN tab , then click DETAIL button .
Now, in that form , in the SHIPPING field, we have to enter how Much quantity of items, we want to ship . The rest remain quantity, that are Ordered will become backorder quantity .
5) Interfacing with AR :
After WORKFLOW BACKGROUND PROGRAM
Concurrent program will complete successfully, we have to run
AUTO INVOICE MASTER PROGRAM from
RECEIVABLE RESPONSIBILTY. After this program
will complete successfully , we can the invoice details in
RECEIVALE à TRANSACTIONS à TRANSACTIONS. Here in
This Form, we have to give our order number in reference field
And query for the invoice details .Then we can see the invoice details.
Table Level Information:
==========================
Order Entry
• At the header level a record gets inserted into the header table
OE_ORDER_HEADERS_ALL.
• At the line level, record(s) get inserted into the Line table
OE_ORDER_LINES_ALL.
Order Booking
• This will update FLOW_STATUS_CODE value in the table
OE_ORDER_HEADERS_ALL to “BOOKED”
• The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will change to
AWAITING_SHIPPING.
• Record(s) will be created into the table WSH_DELIVERY_DETAILS with
RELEASED_STATUS=’R’ (Ready to Release)
OE_INTERFACED_FLAG=’N’ (Not interfaced to OM)
INV_INTERFACED_FLAG=’N’ (Not interfaced to Inv)
• Record(s) will be created into WSH_DELIVERY_ASSIGNMENTS but with
DELIVERY_ID null.
Pick Release
------------------
IF “Autocreate Delivery” option = “Yes” THEN
• ) Create a record into the table WSH_NEW_DELIVERIES
• ) Update WSH_DELIVERY_ASSIGNMENTS with DELIVERY_ID, thus
• ) Update WSH_DELIVERY_DETAILS with RELEASED_STATUS=’Y
Auto Invoicing
----------------------
Before running “Autoinvoice Program”, record(s) will exist into the table
RA_INTERFACE_LINES_ALL with
INTERFACE_LINE_CONTEXT = ’ORDER ENTRY’
INTERFACE_LINE_ATTRIBUTE1 = &Order_number
INTERFACE_LINE_ATTRIBUTE3 = &Delivery_id
SALES_ORDER = &Order_number
After running the “Auto invoice Program” for the order:
Records will be deleted from the table RA_INTERFACE_LINES_ALL and new details will be created into the following RA transaction tables.
>RA_CUSTOMER_TRX_ALL with
INTERFACE_HEADER_ATTRIBUTE1=&Order_number
RA_CUSTOMER_TRX_LINES_ALL with
INTERFACE_LINE_ATTRIBUTE1 = &Order_number
SALES_ORDER = &Order_number

Query to fetch all request groups attached to responsibility

SELECT
responsibility_name responsibility,
request_group_name,
frg.description
FROM
fnd_request_groups frg,
fnd_responsibility_vl frv
WHERE
frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

Kill Locked Session

SELECT a.object_name, b.process, b.session_id
FROM all_objects a, v$locked_object b
WHERE a.object_id = b.object_id
SELECT SID, serial#
FROM v$session
WHERE SID = '[SESSION_ID]'
ALTER SYSTEM KILL SESSION '[SID],[SERIAL#]'

To get all Conc Programs attached to a Reponsibility

SELECT
responsibility_name,
frg.request_group_name,
fcpv.user_concurrent_program_name,
fcpv.description
FROM
fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE
frgu.request_unit_type = 'P' AND
frgu.request_group_id = frg.request_group_id AND
frgu.request_unit_id = fcpv.concurrent_program_id AND
frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

To Derive Module wise conc program count

SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method, COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1

Calculate Request Time

SELECT
f.request_id ,
pt.user_concurrent_program_name user_concurrent_program_name ,
f.actual_start_date actual_start_date ,
f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600) ' HOURS ' floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) ' MINUTES ' round((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 - (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) ' SECS ' time_difference ,
DECODE(p.concurrent_program_name,
'ALECDC',
p.concurrent_program_name'['f.description']',
p.concurrent_program_name) concurrent_program_name ,
decode(f.phase_code,
'R',
'Running',
'C',
'Complete',
f.phase_code) Phase ,
f.status_code
FROM
apps.fnd_concurrent_programs p ,
apps.fnd_concurrent_programs_tl pt ,
apps.fnd_concurrent_requests f
WHERE
f.concurrent_program_id = p.concurrent_program_id and
f.program_application_id = p.application_id and
f.concurrent_program_id = pt.concurrent_program_id and
f.program_application_id = pt.application_id AND
pt.language = USERENV('Lang') and
f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc

List of responsibilities assigned to specific user

SELECT
UNIQUE u.user_id,
u.user_name,
r.responsibility_name,
a.application_name
FROM
fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE
g.user_id(+) = u.user_id AND
g.responsibility_application_id = a.application_id AND
a.application_id = r.application_id AND
g.responsibility_id = r.responsibility_id and
a.application_name like 'Projects'
ORDER BY SUBSTR (user_name, 1, 30)
, SUBSTR (a.application_name, 1, 50)
, SUBSTR (r.responsibility_name, 1, 60)

Finding Oracle Apps Version

SELECT
a.application_short_name,
t.application_name,
p.product_version version
FROM
fnd_application a,
fnd_application_tl t,
fnd_product_installations p
WHERE
a.application_id = p.application_id AND
a.application_id = t.application_id AND
t.language = USERENV('LANG')

Query to find list of parameters used in Concurrent Program

SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, par.end_user_column_name
, par.form_left_prompt prompt
, par.enabled_flag
, par.required_flag
, par.display_flag
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl par
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :p_conc_prg_name
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' fcp.concurrent_program_name

FNDLOAD(Ldt) Scripts

1) Concurrent Program --> afcpprog.lct
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct FNDLOAD_CONC_PRG_NAME.ldt PROGRAM APPLICATION_SHORT_NAME='XXXX' CONCURRENT_PROGRAM_NAME='CONC_PRG_NAME'
2) Value Sets --> afffload.lct
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt VALUE_SET FLEX_VALUE_SET_NAME='XXXX'
3) Menus --> afsload.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
4) Lookups --> aflvmlu.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME='XXXX' LOOKUP_TYPE='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt
5) Flexfield --> afffload.lct
Descriptive Flexfield
Download
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX APPLICATION_SHORT_NAME='XXXX' DESCRIPTIVE_FLEXFIELD_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
Key Flexfield
Download
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX APPLICATION_SHORT_NAME='XXXX' DESCRIPTIVE_FLEXFIELD_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
6) Profile Options --> afscprof.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME='XXXX' APPLICATION_SHORT_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt
7) Responsibility --> afscursp.lct
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt
8) Request Groups --> afcpreqg.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME='XXXX' REQUEST_SET_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt
9) Menus --> afsload.lct
Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME='XXXX'
Upload
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
10) Forms Personalization --> affrmcus.lct
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES FUNCTION_NAME='XXXX'
Upload
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt

Assign and Delete Responsibility to User from backend

begin
fnd_user_pkg.addresp(
username VARCHAR2,
resp_app VARCHAR2,
resp_key VARCHAR2,
security_group VARCHAR2,
description VARCHAR2,
start_date DATE
end_date DATE);
commit;
exception
when others then
dbms_output.put_line(SQLERRM);
end;
Ex:-
---
begin
fnd_user_pkg.addresp(
username => 'SUN',
resp_app => 'XDO',
resp_key => 'XDO_ADMINISTRATION',
security_group => 'STANDARD',
description => 'Oracle XML Publisher Administrator responsibility',
start_date => trunc(sysdate),
end_date => NULL);
commit;
exception
when others then
dbms_output.put_line(SQLERRM);
end;
Ex:-
---
begin
fnd_user_pkg.DelResp(username => 'SUN',
resp_app => 'XDO',
resp_key => 'XDO_ADMINISTRATION',
security_group => 'STANDARD'
);
dbms_output.put_line('Success');
commit;
exception
when others then
dbms_output.put_line(SQLERRM);
end;

Assign Concurrent Prog to Reuest Group

BEGIN
FND_PROGRAM.add_to_group(program_short_name => 'XXXX',
program_application => 'PA',
request_group => 'All Projet Programs',
group_application => 'PA'
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END ;

Change User passwrd in oracle application

DECLARE
CURSOR c_users IS
SELECT user_name FROM fnd_user WHERE user_name = 'SUN';
v_user c_users%ROWTYPE;
v_success BOOLEAN;
BEGIN
OPEN c_users;
FETCH c_users
INTO v_user;
WHILE c_users%FOUND
LOOP
v_success := apps.fnd_user_pkg.changepassword(v_user.user_name,
'WELCOME123');
COMMIT;
IF v_success != TRUE
THEN
dbms_output.put_line('Failed to set password for user '
v_user.user_name);
END IF;
FETCH c_users
INTO v_user;
END LOOP;
CLOSE c_users;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line('Error');
END;

Sample query to build calendar

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week)

Derive business days

CREATE OR REPLACE FUNCTION F_BUSINESS_DAYS
(V_START_DATE IN DATE, V_END_DATE IN DATE)
RETURN NUMBER IS DAY_COUNT NUMBER := 0;
CURR_DATE DATE;
BEGIN -- loop through and update
CURR_DATE := V_START_DATE;
WHILE CURR_DATE <= V_END_DATE
LOOP
IF TO_CHAR(CURR_DATE,'DY') NOT IN ('SAT','SUN')
THEN DAY_COUNT := DAY_COUNT + 1;
END IF;
CURR_DATE := CURR_DATE + 1;
END LOOP;
RETURN DAY_COUNT;
END F_BUSINESS_DAYS;

Derive Business Days Count

SELECT count(business_date) business_day
FROM (SELECT TO_DATE (:TO_DATE, 'DD-MON-YYYY')
+ ROWNUM
- 1 business_date
FROM all_objects
WHERE ROWNUM <=
TO_DATE (:FROM_DATE, 'DD-MON-YYYY')
- TO_DATE (:TO_DATE, 'DD-MON-YYYY')
+ 1)
WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN'

Query to fetch Error workflow details

SELECT ias.begin_date, ias.item_key, ac.NAME activity,
ias.activity_result_code RESULT, ias.error_name error_name,
ias.error_message error_message
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities ac,
wf_activities ap,
wf_items i
WHERE ias.item_type = 'HRSSA'
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.NAME
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.NAME
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.VERSION
AND i.item_type = :p_item_type
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < NVL (ac.end_date, i.begin_date + 1)
AND TRUNC (i.begin_date) BETWEEN TO_DATE (:begin_date) AND TRUNC (:end_date)
ORDER BY ias.begin_date DESC

Quickest way to generate the columns used in the ctl file

Select decode (column_id, 1, ' ', ' , ')
rpad (column_name, 33, ' ')
decode (data_type,
'VARCHAR2', 'CHAR NULLIF ('column_name'=BLANKS)',
'FLOAT', 'DECIMAL EXTERNAL NULLIF('column_name'=BLANKS)',
'NUMBER', decode (data_precision, 0,
'INTEGER EXTERNAL NULLIF ('column_name
'=BLANKS)', decode (data_scale, 0,
'INTEGER EXTERNAL NULLIF ('
column_name'=BLANKS)',
'DECIMAL EXTERNAL NULLIF ('
column_name'=BLANKS)')),
'DATE', 'DATE "mm/dd/yy" NULLIF ('
column_name'=BLANKS)', null)
from all_tab_columns
where table_name = upper ('$LOADTABLE')
and owner = upper ('$SCHEMA')
order by column_id;

Query to find all functions in a Menu

SELECT DISTINCT fmep.menu_id,
DECODE (fmep.function_id,
NULL, DECODE (fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, 'No Func',
fmec1.function_id
),
fmec.function_id
),
fmep.function_id
) funcid,
fff.user_function_name, fff.description
FROM fnd_form_functions_tl fff,
fnd_menu_entries fmec1,
fnd_menu_entries fmec,
fnd_menu_entries fmep
WHERE fmep.menu_id =
(SELECT menu_id
FROM fnd_menus
WHERE menu_name =
'INV_NAVIGATE'
--Change the menu according to your requirement
AND ROWNUM = 1)
AND fmep.sub_menu_id = fmec.menu_id(+)
AND fmec.sub_menu_id = fmec1.menu_id(+)
AND fff.function_id =
DECODE (fmep.function_id,
NULL, DECODE (fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, -999,
fmec1.function_id
),
fmec.function_id
),
fmep.function_id
)
ORDER BY DECODE (fmep.function_id,
NULL, DECODE (fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, 'No Func',
fmec1.function_id
),
fmec.function_id
),
fmep.function_id
)

fetch the values from a string separated by commas in Oracle

SELECT * FROM (
SELECT TRIM( SUBSTR ( txt
, INSTR (txt, ',', 1, level ) + 1
, INSTR (txt, ',', 1, level+1
)
- INSTR (txt, ',', 1, level) -1 ) )
AS Result
FROM ( SELECT ',':in_string',' AS txt
FROM dual )
CONNECT BY level <=
LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1
)
Ex:
:in_string := '1,q,a,z,2,w,s,x';
Result
--------------------------
1
q
a
z
2
w
s
x

Active alert information

SELECT aa.alert_name, aa.start_date_active, al.meaning "FREQUENCY_TYPE",
DECODE (frequency_type,
'B', days_between_checks,
'W', weekly_check_day,
'M', monthly_check_day_num,
'C', days_between_checks,
NULL
) "Days",
aav.to_recipients, aav.cc_recipients, aav.bcc_recipients, aav.subject,
aav.msg_body
FROM alr_actions_v aav, alr_alerts aa, alr_lookups al
WHERE 1 = 1
-- AND UPPER (aa.alert_name) LIKE UPPER ('%HR%')
AND aav.alert_id = aa.alert_id
AND aa.enabled_flag = 'Y'
AND aa.frequency_type = al.lookup_code
AND al.lookup_type = 'ALERT_FREQUENCY_TYPE'

Steps to upload GIF, XLS, PDF, JPG and DOC files into oracle database

Step 1
======
Create or replace directory my_files as '/usr/temp';
Create table demo(sno number, filedata blob);
Copy any file (jpg,gif,pdf,xls....) into /usr/temp
Execute the below code. Change the file name according to your requirement

Step 2
======
declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 3, empty_blob() )
returning filedata into l_blob;
l_bfile := bfilename( 'MY_FILES', 'water.jpg' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
commit;
end;
Step 3
======
Check the file length to ensure whether file loaded successfully or not
Select sno,dbms_lob.getlength(filedata) from demo
Reference: http://www.sql.ru/forum/actualthread.aspx?tid=202867

Reading and printing attachments in Oracle apps Text files and HTML files only

DECLARE
l_blob BLOB;
l_blob_length NUMBER;
l_amount BINARY_INTEGER := 10000;
l_offset INTEGER := 1;
l_buffer RAW(20000);
l_text_buffer VARCHAR2(32767);
l_break_at PLS_INTEGER := 250;
BEGIN
-- Get the BLOB from the APPLSYS.FND_LOBS table.
/* SELECT file_data
INTO l_blob
FROM fnd_lobs
WHERE file_id= 331001; --221148;
*/
select file_data into l_blob
from fnd_lobs
where file_id in (
SELECT media_id
FROM FND_ATTACHED_DOCS_FORM_VL
where function_name='CIM_PA_PAXPREPR_PROJECT'
and attached_document_id in (
SELECT attached_document_id
FROM FND_ATTACHED_DOCUMENTS
where pk1_value=5465 -- project id
and entity_name='PA_PROJECTS'
)
) and file_name='Mail from Chanakya K.txt';
-- Figure out how long the BLOB is.
l_blob_length := DBMS_LOB.GETLENGTH(l_blob);
-- loop through the BLOB as many times as necessary to
-- get all its data.
FOR i IN 1..CEIL(l_blob_length/l_amount) LOOP
-- Read in the given chunk of the BLOB.
DBMS_LOB.READ(l_blob,
l_amount,
l_offset,
l_buffer
);
-- The DBMS_LOB.READ procedure dictates that its output be RAW.
-- This next procedure converts that RAW data to character data.
l_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(l_buffer);
-- DBMS_OUTPUT.PUT_LINE('==================================');
-- DBMS_OUTPUT.PUT_LINE('Text Buffer: 'length(l_text_buffer));
-- DBMS_OUTPUT.PUT_LINE('==================================');
-- Now that we have character data (up to l_amount characters long),
-- chunk it out so that we can do whatever we need to with it.
FOR j IN 1..CEIL(LENGTH(l_text_buffer) / l_break_at) LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(l_text_buffer
,(((j - 1) * l_break_at) + 1)
,LEAST(LENGTH(l_text_buffer)-((j - 1) * l_break_at),l_break_at
)
)
);
END LOOP;
-- For the next iteration through the BLOB, bump up your offset
-- location, where you start reading from
l_offset := l_offset + l_amount;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: ' SUBSTR(SQLERRM,1,247));
RAISE;
END;
/

Query to Check List of Form personalizations

SELECT ID,
DECODE (level_id,
20, 'Site',
30, 'Responsibility',
40, 'User'
) level1,
(SELECT fr.responsibility_name
FROM fnd_responsibility_vl fr
WHERE fr.responsibility_id(+) = b.level_value
AND b.level_id = 30) resp,
(SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id = b.level_value AND b.level_id = 40) user_name,
SEQUENCE, function_name, form_name, a.description, trigger_event,
trigger_object, condition, rule_type
FROM apps.fnd_form_custom_rules a, apps.fnd_form_custom_scopes b
WHERE 1 = 1
AND b.rule_id = a.ID
AND form_name = :p_form_name
AND a.enabled = 'Y'
ORDER BY SEQUENCE, function_name, ID