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.
Monday, April 13, 2015
How to use plsql collections in select statment
Step 1 : Declare Collection type Globally
For Example:
Test_Coll can be declared globally as "TYPE Test_Coll IS TABLE OF INTEGER"
Step 2 :
DECLARE
test_coll_tab Test_Coll;
MyName VARCHAR2(100);
BEGIN
test_coll_tab = Test_Coll();
test_coll_tab.extend;
test_coll_tab(1):=1;
test_coll_tab.extend;
test_coll_tab(2):=2;
test_coll_tab.extend;
test_coll_tab(3):=3;
test_coll_tab.extend;
test_coll_tab(4):=4;
test_coll_tab.extend;
test_coll_tab(5):=5;
SELECT Name
INTO MyName
FROM Item
WHERE ItemId NOT IN (select * from table(test_coll_tab));
END;
For Example:
Test_Coll can be declared globally as "TYPE Test_Coll IS TABLE OF INTEGER"
Step 2 :
DECLARE
test_coll_tab Test_Coll;
MyName VARCHAR2(100);
BEGIN
test_coll_tab = Test_Coll();
test_coll_tab.extend;
test_coll_tab(1):=1;
test_coll_tab.extend;
test_coll_tab(2):=2;
test_coll_tab.extend;
test_coll_tab(3):=3;
test_coll_tab.extend;
test_coll_tab(4):=4;
test_coll_tab.extend;
test_coll_tab(5):=5;
SELECT Name
INTO MyName
FROM Item
WHERE ItemId NOT IN (select * from table(test_coll_tab));
END;
Friday, April 3, 2015
How to send Concurrent Program output as an Email
There are two Methods to send Concurrent Program Output as Email
Steps for Method 1
Step 1: Define/Query Printer Type: Define a new Printer Type or Query for an existing one. Responsibility: System Administrator Navigation: Install->Printer->Types
Step 2: Define Printer Style: Click on the ‘Style’ button in the Printer Types form (as seen in the previous screenshot). Create a new style and save it.
Step 3: Define Printer Driver: Click on ‘ Driver’ button in the Printer Types form. Specify Driver method as command. Specify the following in the arguments section: $XXCUST_TOP/bin/EMAIL.sh $PROFILES$.CONC_REQUEST_ID 30
Step 4: Mapping: Map the Printer Style and Printer Driver to the Printer Type.
Step 5: Install the Printer and map the Printer Type : Create a new printer and map the Printer Type created in the previous step.
Step 6: Attach the defined printer to the concurrent program: Navigation: Concurrent > Program > Define .Specify printer in Output section
Step 7: Running the Concurrent Program: Before clicking on the Submit button in the SRS window, specify non-zero value of no. of copies by clicking on the Options button.
This will invoke Printer.
Step 8: The sample shell script (email_conc_prog_output.sh) that is executed after the completion of the above steps is attached with the article.
Steps for Method 2
This feature is available in the new button ‘Delivery Opts’ as seen in the screenshot below.
On clicking the ‘Delivery Opts’ Button, a new form will pop up which will allow users to specific email addresses of the sender, recipient and carbon copy recipient (multiple emails can be specified separated by commas), subject of the email. This can be seen in the screenshot below.
The Email tab (as seen in the screenshot above) utilizes SMTP to communicate to a mail host which in turn sends the email to the recipients. The hostname and port of the mail host is configured via the profile values:
FND: SMTP Host and FND: SMTP Port
Method 1:
- The printer styles have to be configured to execute a shell script instead of sending the output to a physical printer.
- The shell script then emails the concurrent program output to the intended recipient.
Method 2: (Applicable R12.1.3 onwards)
With the R12.1.3 release, a hook to the BI Publisher Delivery Manager is available on the SRS window to send the output to IPP Printers, Email, Fax and FTP destinations.Steps for Method 1
Step 1: Define/Query Printer Type: Define a new Printer Type or Query for an existing one. Responsibility: System Administrator Navigation: Install->Printer->Types
Step 2: Define Printer Style: Click on the ‘Style’ button in the Printer Types form (as seen in the previous screenshot). Create a new style and save it.
Step 3: Define Printer Driver: Click on ‘ Driver’ button in the Printer Types form. Specify Driver method as command. Specify the following in the arguments section: $XXCUST_TOP/bin/EMAIL.sh $PROFILES$.CONC_REQUEST_ID 30
Step 4: Mapping: Map the Printer Style and Printer Driver to the Printer Type.
Step 5: Install the Printer and map the Printer Type : Create a new printer and map the Printer Type created in the previous step.
Step 6: Attach the defined printer to the concurrent program: Navigation: Concurrent > Program > Define .Specify printer in Output section
Step 7: Running the Concurrent Program: Before clicking on the Submit button in the SRS window, specify non-zero value of no. of copies by clicking on the Options button.
This will invoke Printer.
Step 8: The sample shell script (email_conc_prog_output.sh) that is executed after the completion of the above steps is attached with the article.
Steps for Method 2
This feature is available in the new button ‘Delivery Opts’ as seen in the screenshot below.
On clicking the ‘Delivery Opts’ Button, a new form will pop up which will allow users to specific email addresses of the sender, recipient and carbon copy recipient (multiple emails can be specified separated by commas), subject of the email. This can be seen in the screenshot below.
The Email tab (as seen in the screenshot above) utilizes SMTP to communicate to a mail host which in turn sends the email to the recipients. The hostname and port of the mail host is configured via the profile values:
FND: SMTP Host and FND: SMTP Port
Friday, March 27, 2015
Oracle Apps R12 Query to Find Payment against Invoice
SELECT b.segment1 vendor_number,
b.vendor_name
vendor_name,
c.vendor_site_code,
c.pay_group_lookup_code,
a.invoice_num
invoice_number,
a.invoice_date,
a.gl_date,
d.due_date,
a.invoice_currency_code,
a.invoice_amount,
a.amount_paid,
a.pay_group_lookup_code,
d.payment_priority,
(SELECT MAX (check_date)
FROM ap_checks_all aca, ap_invoice_payments_all aip
WHERE aca.CHECK_ID = aip.CHECK_ID AND aip.invoice_id = a.invoice_id)
"Last Payment Made on",
a.cancelled_date
FROM apps.ap_invoices_all a,
apps.ap_suppliers
b,
apps.ap_supplier_sites_all
c,
apps.ap_payment_schedules_all
d,
apps.ap_invoice_payments_all
ap,
ap_checks_all ac
WHERE a.vendor_id = b.vendor_id
AND a.vendor_site_id = c.vendor_site_id
AND b.vendor_id = c.vendor_id
AND a.invoice_id = d.invoice_id
AND ap.invoice_id = a.invoice_id
AND ac.CHECK_ID = ap.CHECK_ID
and ac.STATUS_LOOKUP_CODE <> 'VOIDED'
AND a.org_id = 89
and a.invoice_id= 1234
AND a.pay_group_lookup_code IN ('DISTRIBUTOR')
AND ac.check_date BETWEEN TO_DATE ('01-Apr-2014', 'DD-MON-YYYY') AND TO_DATE ('30-Jun-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
Subscribe to:
Posts (Atom)