SELECT hca.account_number,
hcsu.site_use_id,
hca.cust_account_id
INTO l_customer_number,
l_cust_site_use_id,
l_cust_account_id
FROM hz_cust_accounts_all hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
IBY_ACCOUNT_OWNERS IAO,
IBY_EXT_BANK_ACCOUNTS IEBA
WHERE hca.cust_account_id = hcas.cust_account_id
AND hca.party_id = iao.account_owner_party_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.org_id =
AND hca.status = 'A'
AND hcas.status = 'A'
AND hcsu.status = 'A'
AND hcas.bill_to_flag = 'P'
AND NVL (IAO.end_date, SYSDATE + 1) > SYSDATE
AND ieba.bank_account_num =
AND iao.ext_bank_account_id = ieba.ext_bank_account_id;
Friday, August 7, 2015
Tuesday, June 23, 2015
Oracle Apps : Steps to Implement Employee/Supervisor Approval Hierarchy
Purchasing-> Setup-> Organization-> Financial Options
Uncheck “Use Approval Hierarchies”
Purchasing-> Setup-> Purchasing-> Document Types
Click the update icon for specific document as in our case “Standard Purchase Order”
“Owner Can Approve” should be Unchecked.
“Default Hierarchy” should be left blank.
HRMS Manager-> People-> Enter and Maintain-> Find->Assignments
Select Supervisor Tab and select Supervisor
Purchasing-> View-> Request-> ”Fill Employee Hierarchy”
Purchasing-> Setup-> Purchasing-> Document Types
Click the update icon for specific document as in our case “Standard Purchase Order”
“Default Hierarchy” should be left blank.
Oracle Apps : Steps to Implement Employee/Supervisor Approval Hierarchy
Reviewed by Anurag Jainon Jul 07 1995
Rating: Your-Rating
Reviewed by Anurag Jainon Jul 07 1995
Rating: Your-Rating
Monday, June 22, 2015
Oracle Apps : Steps to implement Position Hierarchies
Create a User
System Administrator -> Security -> UserAssign Purchasing and HRMS Responsibility
Create Employee
Click Assignment Button to Assign Job and Position
Assign Job and Position
Assign Employee to User
Go to System Administrator -> Security - > UserIf Employee is not a buyer, We will get a pop-up as below
Press “Add Buyer” Button to make an employee a buyer
Go to Purchasing->Setup->Personnel-> BuyerTo remove an employee as buyer Query the employee name and End Date it
Enter the PO Details
Assigned User’s employee will be defaulted as BuyerPress “Approve” Button to approve the PO
Document Types in Purchasing
Purchasing-> Setup-> Purchasing-> Document Types“Owner Can Approve” Option if checked and employee is assigned to a Approval Group which has no limits to approve the PO created then created PO will get approved.
Setup Steps
Purchasing-> Setup-> Purchasing -> Document Types
Assign the Default Hierarchy that needs to be followed in Document Types under Standard Purchase Order
Purchasing-> Setup-> Purchasing-> Document Types
Go to Financial Option and check “Use Approval Hierarchy”
Purchasing-> Setup-> Organization-> Financial Options
Position Hierarchy in HRMS Manager should have a correct assignments as required.
HRMS Manager ->Work Structures ->Position ->Hierarchy
Superiors approval Limit should be good enough to approve the Purchase Order created.
Defining Position Hierarchy
- Purchasing-> Setup-> Purchasing->Document Types
- “Owner Can Approve” Option Uncheck “Default Hierarchy” should be assigned
- “Forward Method” if it should directly go to the next level employee who has the authority to approve the select “Direct” else select “Hierarchy”
Purchasing-> Setup-> Organization-> Financial Options
“Use Approval Hierarchy” box should be checked
Tuesday, June 9, 2015
Oracle Apps : Apply AP Invoice Hold using API
The hold codes can be extracted from query below.
SELECT *
FROM ap_hold_codes
WHERE hold_type = 'INVOICE HOLD REASON';
Below is the API to apply hold using Standard Oracle API :
PROCEDURE inv_hold (p_invoice_id IN NUMBER,
p_reject_code IN VARCHAR2,
p_hold_val_status IN VARCHAR2,
x_status OUT VARCHAR2)
IS
l_hold_invoice_id NUMBER := NULL;
l_hold_err_msg VARCHAR2 (2000) := NULL;
BEGIN
l_hold_invoice_id := p_invoice_id;
IF l_hold_invoice_id IS NOT NULL
AND p_reject_code IS NOT NULL
AND p_hold_val_status = 'Y'
THEN
BEGIN
ap_holds_pkg.insert_single_hold (
X_invoice_id => l_hold_invoice_id,
X_hold_lookup_code => p_reject_code);
x_status := 'S';
EXCEPTION
WHEN OTHERS
THEN
l_hold_err_msg :=
'Unknow Exception Occured in Hold API call.. Exception Code : '
|| SQLCODE
|| ' Exception Details : '
|| SQLERRM;
DBMS_OUTPUT.put_line (l_hold_err_msg);
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END inv_hold;
SELECT *
FROM ap_hold_codes
WHERE hold_type = 'INVOICE HOLD REASON';
Below is the API to apply hold using Standard Oracle API :
PROCEDURE inv_hold (p_invoice_id IN NUMBER,
p_reject_code IN VARCHAR2,
p_hold_val_status IN VARCHAR2,
x_status OUT VARCHAR2)
IS
l_hold_invoice_id NUMBER := NULL;
l_hold_err_msg VARCHAR2 (2000) := NULL;
BEGIN
l_hold_invoice_id := p_invoice_id;
IF l_hold_invoice_id IS NOT NULL
AND p_reject_code IS NOT NULL
AND p_hold_val_status = 'Y'
THEN
BEGIN
ap_holds_pkg.insert_single_hold (
X_invoice_id => l_hold_invoice_id,
X_hold_lookup_code => p_reject_code);
x_status := 'S';
EXCEPTION
WHEN OTHERS
THEN
l_hold_err_msg :=
'Unknow Exception Occured in Hold API call.. Exception Code : '
|| SQLCODE
|| ' Exception Details : '
|| SQLERRM;
DBMS_OUTPUT.put_line (l_hold_err_msg);
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END inv_hold;
Thursday, June 4, 2015
Query to Find Attached Template Name from Template Code
SELECT template_code, file_name
FROM xdo_templates_b a, xdo_lobs b
WHERE b.lob_code = a.template_code
AND b.xdo_file_type = 'RTF'
AND a.template_code = 'XXXBITEMPLATE';
Tuesday, May 26, 2015
Query to drill down from PO header to shipment
SELECT poll.*
FROM po_headers_all poh,
po_releases_all pora,
po_lines_all pla,
po_line_locations_all poll
WHERE poh.po_header_id = pora.po_header_id
AND pla.po_line_id = poll.po_line_id
AND poll.po_release_id = pora.po_release_id
AND poh.segment1 = :po_number
AND pora.release_num = :po_release_num
AND pla.line_num = :po_line_num
AND poh.org_id = :org_id;
Thursday, May 21, 2015
How To Disable/End Date/ Delete Responsibilities of a User from Backend/API
Below is the query.
Please not Responsibilities cannot be deleted in Oracle Apps from a User. It can only be Disabled or End Dated.
BEGIN
FOR I
IN (SELECT fu.user_id,
fu.user_name,
furg.RESPONSIBILITY_APPLICATION_ID,
frv.RESPONSIBILITY_NAME,
frv.RESPONSIBILITY_KEY,
fa.application_short_name,
frg.security_group_key
FROM FND_USER fu,
FND_USER_RESP_GROUPS furg,
FND_RESPONSIBILITY_VL frv,
fnd_application fa,
fnd_security_groups frg
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frv.responsibility_id
AND frv.application_id = fa.application_id
AND frv.data_group_id = frg.security_group_id
AND fu.user_name = 'ABC'
AND frv.RESPONSIBILITY_NAME IN ('Receivables Support'))
LOOP
BEGIN
DBMS_OUTPUT.put_line (
i.user_name
|| i.application_short_name
|| i.responsibility_key
|| i.security_group_key);
fnd_user_pkg.delresp (username => i.user_name,
resp_app => i.application_short_name,
resp_key => i.responsibility_key,
security_group => i.security_group_key);
DBMS_OUTPUT.put_line (
i.RESPONSIBILITY_NAME || ' End Dated Successfully ');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error encountered while deleting responsibilty from the user and the error is '
|| SQLERRM);
END;
/
Tuesday, May 19, 2015
Wednesday, May 13, 2015
FND DEBUG / FND LOG MESSAGES
What is FND Debug Log ?
- It helps you pinpoint the cause of error in standard Oracle Code, by making debug messages to appear in a centralized table named FND_LOG_MESSAGES.
- You can design and build your custom extensions in a manner that can easily be debugged. This can be done by calling Oracle delivered API’s in your custom code.
A program written in any technology, either form, report, PL/SQL, java concurrent program, OAF has their debug message stored in FND_LOG_MESSAGES.
How does this work?
How to use FND debug Log to pinpoint the error?Step 1: Set up profiles for the User / Responsibility to be used to reproduce the issue
FND: Debug Log Enabled -> YES This turns the debugging feature on
FND: Debug Log Filename -> NULL Use when you want debug messages to get stored to a file FND: Debug Log Level -> STATEMENT
FND: Debug log module -> %
If the error pertains to a specific module use the application short name.
Ex: For receivable use FND: Debug log module -> ar%
Log into the Oracle application
Step 2: Since the debugging routine will start writing messages to the table, we want to know which messages pertain to our test. If you are tracking the debug messages for a concurrent request, note down the Concurrent Request id. Otherwise, note down current max value of log sequence retrieved as follows :
SELECT MAX(LOG_SEQUENCE) FROM FND_LOG_MESSAGE
Step 3: Run the test case, avoiding extraneous steps to avoid stacking the table.
Step 4:
a) For a concurrent process:
SELECT LOG.module, LOG.MESSAGE_TEXT MESSAGE
FROM fnd_log_messages LOG, fnd_log_transaction_context con
WHERE request_id = <Conc Req ID>
AND con.transaction_type = 'REQUEST'
AND con.transaction_context_id = LOG.transaction_context_id
ORDER BY LOG.log_sequence;
b) For any other debugging:
SELECT module, MESSAGE_TEXT
FROM fnd_log_messages
WHERE log_sequence = (SELECT MAX (LOG_SEQUENCE) FROM FND_LOG_MESSAGE);
Obtaining the log messages via Oracle Application:
1) Query Profile: %FND%DEBUG%
2) Set the FND: Debug Log Enabled: Yes
3) System Administrator > Help > Debug Logging > View
5) Then navigate to the above screen and download the complete fnd log messages report by exporting as a CSV.
Tuesday, April 28, 2015
Debug : javax.servlet.http.httpservletrequest cannot be resolved
You can do either of the following to resolve this error.
OR
you can simply copy the servlet-api.jar present in ...\apache-tomcat-7.0.61\lib\ and copy that jar files into lib folder, which is in WEB-INF. then just clean and built your project, your errors will be solved.
- Right-click the project tab, click Properties.
- Choose Build Path -> Java Build Path
- Choose Add External JARs
- Browse to find and select servlet-api.jar which should be present in ...\apache-tomcat-7.0.61\lib\
- Click OK to update the build path.
OR
you can simply copy the servlet-api.jar present in ...\apache-tomcat-7.0.61\lib\ and copy that jar files into lib folder, which is in WEB-INF. then just clean and built your project, your errors will be solved.
Subscribe to:
Posts (Atom)