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