GL
-------Query to get GL Ledger details-------------
select name,currency_code,CHART_OF_ACCOUNTS_ID,
gl_flexfields_pkg.get_coa_name(CHART_OF_ACCOUNTS_ID) COA
from gl_ledgers where name in ('XXG18_500_USD');
Sunday, 25 December 2022
Saturday, 3 December 2022
EBS: GL Encumbrance API
GL Encumbrance API
gl_funds_available_pkg.calc_funds
('YTDE',
---X_AMOUNT_TYPE /*YTDE,QTDE , PTD, PJTD*/
i.ccid,
NULL,
-- 'A',--x_account_type,
NULL, --x_template_id,
l_ledger_id,
l_currency_code,
'Y', --x_po_install_flag,
l_accounted_period_type,
l_period_set_name,
l_period_name,
l_period_num,
l_quarter_num,
l_period_year,
l_closing_status,
l_budget_version_id,
l_encumbrance_type_id,
NULL,
--x_req_encumbrance_id,
NULL, --x_po_encumbrance_id,
x_budget,
x_encumbrance,
x_actual,
x_funds_available,
x_req_encumbrance_amount,
x_po_encumbrance_amount,
x_other_encumbrance_amount
);
EBS: GL Encumbrance Query
Query to get GL Encumbrance Details for Source REQ, PO and Invoice
SELECT '1REQ' TYPE, prh.requisition_header_id header_id,
prh.segment1 num, prd.budget_account_id ccid,
TO_CHAR (prd.last_update_date, 'YYYYMMDDHH24MISS'),
prd.last_update_date last_update_date,
SUM (NVL (prd.encumbered_amount, 0)) amount,prh.authorization_status status
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd
WHERE prh.requisition_header_id =prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND prh.authorization_status in ('APPROVED','IN PROCESS')
AND NVL (prh.cancel_flag, 'N') = 'N'
---- AND prd.budget_account_id = v_code_combination_id
AND prd.budget_account_id IN (SELECT detail_code_combination_id
FROM gl_account_hierarchies
WHERE summary_code_combination_id = v_code_combination_id
AND ledger_id = 2041
UNION
SELECT v_code_combination_id FROM DUAL)
AND prd.org_id = v_org_id
GROUP BY prh.requisition_header_id,
prh.segment1,
prd.budget_account_id,
TO_CHAR (prd.last_update_date, 'YYYYMMDDHH24MISS'),
prd.last_update_date,prh.authorization_status
UNION ALL
SELECT '2PO' TYPE, poh.po_header_id header_id,
poh.segment1 num, pda.budget_account_id,
TO_CHAR (pda.last_update_date, 'YYYYMMDDHH24MISS'),
pda.last_update_date last_update_date,
SUM (NVL (pda.encumbered_amount, 0)) amount,poh.authorization_status status
FROM po_headers_all poh, po_distributions_all pda
WHERE poh.po_header_id = pda.po_header_id
AND pda.org_id = v_org_id
AND poh.authorization_status in ('APPROVED','IN PROCESS')
AND NVL (poh.cancel_flag, 'N') = 'N'
----- AND pda.budget_account_id = v_code_combination_id
AND pda.budget_account_id IN (SELECT detail_code_combination_id
FROM gl_account_hierarchies
WHERE summary_code_combination_id = v_code_combination_id
AND ledger_id = 2041
UNION
SELECT v_code_combination_id FROM DUAL)
AND NOT EXISTS ( --added to exclude the PO match Inovice --
SELECT 1
FROM ap_invoices_all aia, ap_invoice_lines_all apia
WHERE aia.invoice_id = apia.invoice_id
AND aia.org_id = apia.org_id
AND apia.po_header_id = pda.po_header_id
AND apia.org_id = pda.org_id
AND ap_invoices_utility_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) = 'APPROVED'
union --added on 31mar17 to exclude po prepay invoice --
SELECT 1
FROM ap_invoices_all aia
where aia.quick_po_header_id=pda.po_header_id
and aia.org_id=pda.org_id
)
GROUP BY poh.po_header_id,
poh.segment1,
pda.budget_account_id,
TO_CHAR (pda.last_update_date, 'YYYYMMDDHH24MISS'),
pda.last_update_date,poh.authorization_status
UNION ALL
SELECT '3INV' TYPE, aia.invoice_id header_id,
aia.invoice_num num,
aid.dist_code_combination_id ccid,
TO_CHAR (aid.last_update_date, 'YYYYMMDDHH24MISS'),
aid.last_update_date last_update_date,
round(SUM(NVL ((aid.amount*nvl(aia.EXCHANGE_RATE,1)), 0)),2) amount,
'APPROVED' status
FROM ap_invoices_all aia,
ap_invoice_distributions_all aid
WHERE aia.invoice_id = aid.invoice_id
AND aia.org_id = aid.org_id
---AND aid.dist_code_combination_id = v_code_combination_id
AND aid.dist_code_combination_id IN (SELECT detail_code_combination_id
FROM gl_account_hierarchies
WHERE summary_code_combination_id = v_code_combination_id
AND ledger_id = 2041
UNION
SELECT v_code_combination_id FROM DUAL)
AND aia.org_id = v_org_id
AND aia.cancelled_date IS NULL
and aid.invoice_id not in ( 504841)------added on 29AUG16. For Special Case Journal has been made against invoice---
AND apps.ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) IN ('APPROVED', 'FULL', 'UNPAID','AVAILABLE')
GROUP BY aia.invoice_id,
aia.invoice_num,
aid.dist_code_combination_id,
TO_CHAR (aid.last_update_date, 'YYYYMMDDHH24MISS'),
aid.last_update_date,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code) main
WHERE 1 = 1
--AND main.header_id NOT IN v_header_id
AND TO_CHAR (last_update_date, 'YYYYMMDDHH24MISS') <=v_last_date
group by main.status
Thursday, 24 November 2022
EBS: PA: AP to PA integration Technical Functional Flow (Oracle)
AP to PA Integration Technical Functional Flow
1) Enter AP Invoice Distributions.
2) Run : Supplier Cost Interface - This will pick the eligible AP Inv Dist , it check PA_ADDITION column. This insert data into pa_transactions_interface.
3) Run : PRC Transaction Imports :- This will pick data from pa_transactions_interface and insert into Expenditure base table.
4) In Expenditure Table we have 3 types of Invoice Source:
a) AP INVOICE
b) AP ERV
c) AP DISCOUNT
Link between PA Expenditure and AP Invoice
pa_expenditure_item.document_header_id = Invoice_id
pa_expenditure_item.document_distribution_id = invoice_distribution_id
Main Table of PA Expenditure:-
PA_EXPENDITURE_ALL
PA_EXPENDITURE_ITEMS_ALL
Saturday, 12 November 2022
EBS : PA Invoice Billing Steps
PA Invoice Billing Steps:-
1)Go to Billing-> Events-> Create Invoice Events
2) Run PRC: Generate Draft Invoice for Single Project. This will generate Draft Invoice
3) Go to Billling-> Invoice review > Enter project and search.
4) Approved and release Draft Invoice.
5) Run PRC: Interface to Receivables - This will populate data into RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL to create AR INVOICE.
6) Run "Auto Import Master Program" - This will pick the interface table and create AR Invoice. AR Invoice will be created with Batch Source as "PROJECT INVOICES". Project Number gets stored in column INTERFACE_HEADER_ATTRIBUTE1 or INTERFACE_LINE_ATTRIBUTE1
Sunday, 6 November 2022
OAF : How to find DBC File ( Oracle Apps)
How to locate correct DBC file
Navigate to System Administrator -> Concurrent -> Requests, Click on "Submit a New Request" and submit
"Generate concurrent processing environment information" CP and let it complete.
Click on "View Output" button and locate "FND_SECURE" environment variable to find dbc file.
DBC file can be found in $FND_SECURE directory or $FND_SECURE/<SID> directory with name <SID>.dbc
--------------------------------------------------------------------------------------------------------------------------------
How to get the DBC file for a particular instance
#Oracle Apps #OAF
OAF : Important Code
How to retrieve Current row value
ü using Row Reference
//Getting selected Emp Id-----------------------------------
OAApplicationModule am= pageContext.getApplicationModule(webBean);
OAViewObject vo3 = (OAViewObject)am.findViewObject("DataEntryVO1");
//Get Row Refrence
String rowReference = pageContext.getParameter(EVENT_SOURCE_ROW_REFERENCE);
//Get current Row using Row Reference
OARow currRow = (OARow)am.findRowByRef(rowReference);
//Get attribute value from current row
String lv_emp_id = (String)currRow.getAttribute("EmpId").toString();
throw new OAException(" Selected Emp id is : !!!"+lv_emp_id ,OAException.CONFIRMATION);
// end-----------------------------------
ü using VO.currentRecord
vo3.last(); --retrieving last row
String lv_emp_id =vo3.currRow().getAttribute("EmpId").toString();
ü using OAMessageStyle Bean
//CustomerNameTxt
OAMessageStyledTextBean CustomerNameTxt =
(OAMessageStyledTextBean)webBean.findIndexedChildRecursive("CustomerNameTxt");
System.out.println("CustomerNameTxt" +
CustomerNameTxt.getValue(pageContext));
How to Button/fields value using PageContext.getParameter
(pageContext.getParameter("CreatePage")!=null)
How to print Message
System.out.println("Process Request Starts****");
pageContext.writeDiagnostics(this, "Unable to get DeliveryId" + e.getMessage().toString(), 6);
How to use Link button
Create an item and change the following property
|
Item Style |
Link |
|
Destination URI |
Link
of the calling page OA.jsp?page=/pk/oracle/apps/fnd/dataentry/server/EntryPG Passing
Parameter
|
|
Target Frame |
_blank Note:-
It will Open the Target Page in New Window Page |
How to use ComparetoIgnore method
if ("Order Confirmation".compareToIgnoreCase(pageContext.getParameter("ProgramShortName").toString()) ==
0) {
System.out.println("Order Confirmation is chsen to be run");
//Run Order Confirmation Report
getOutput(pageContext, webBean, "XXMOMRPT55555OAF");
}
How to use Date Format and get Current date
SimpleDateFormat format =
new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");
oracle.jbo.domain.Date convertDate =
am3.getOADBTransaction().getCurrentDBDate();
java.util.Date date = null;
try {
date = format.parse(convertDate.toString());
} catch (ParseException e) {
// TODO
}
SimpleDateFormat formatDate =
new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String convStr = formatDate.format(date);
System.out.println(convStr);
//2015/03/13 00:00:00
SimpleDateFormat formatDate2 =
new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
String currentDate = formatDate.format(date);
System.out.println(currentDate);
OAF : Migration Scripts
----------------------------------------------Export command from unix---------------------------
java oracle.jrad.tools.xml.exporter.XMLExporter /oracle/apps/per/perimage/webui/PerImgViewPG -rootdir $JAVA_TOP -username apps -password apps -dbconnection "(description = (address_list= (address= (community = tcp.world)(protocol=tcp) (host=192.168.1.35) (port = 1523))) (connect_data=(sid=TEST)))"
----------------------------------------------Import command from unix----------------------------
java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/pk/oracle/apps/fnd/insertdemo/webui/InsertPG.xml -username apps -password apps -dbconnection "(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.35) (PORT=1523)) (CONNECT_DATA=(SID=TEST)))" -rootdir $JAVA_TOP
-------------------------------------------------IMPORT COMMAND from desktop-----------
C:\A_OA\jdevhome\jdev\myprojects\pk\oracle\apps\fnd\insertdemo\webui\InsertPG.xml -rootdir C:\A_OA\jdevhome\jdev\myprojects -username apps -password apps -dbconnection "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.35)(PORT =1523)))(CONNECT_DATA = (SERVER = DEDICATED) (SID =TEST)))"
-----------------------------scripts to check file uploaded or not----------------------------
DECLARE
BEGIN
jdr_utils.printDocument('/pk/oracle/apps/fnd/insertdemo/webui/InsertPG',1000);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
SQLSERVER : How to Login SQLSERVER
HOW TO LOGIN SQLSERVER
1) OPEN CMD
2) TYPE BELOW COMMAND:-
mysql -u root -p
3) enter password
(Note : if u dont know password pls reset it usin update commmand
update mysql.user set password ='new pasword1'
where user='root';
)
4) if u dont know the password just change default int file..which located in BIN folder.
------------------------How to change password of root user---------
1)mysqladmin -u root -p password
2) enter password (Note :- skip and plz press enter)
3) new password (Note :- plz enter your new password)
4) confirm password (Note :- plz enter your confirm password)
Saturday, 22 October 2022
EBS : HRMS : SQL Query
HRMS SQL Query
---Employee Tab
select * from per_all_people_f where EMPLOYEE_NUMBER ='12'
---Employee Assignment Tab
select * from per_assignments_f where person_id=35347
---Payroll Entries Tab
select * from pay_element_entries_f where ASSIGNMENT_ID= 3539 and ELEMENT_TYPE_ID=6523
select nvl(pet1.retro_summ_ele_id, pet1.element_type_id) from pay_element_types_f pet1 where pet1.element_type_id = 647;
Important Table
pay_process_events;
PAY_RETRO_ASSIGNMENTS
PAY_RETRO_ENTRIES
Important Standard Program
pay_in_tax_utils.get_balance_value
PAY_RETRO_PKG
EBS : IExpense : How IEXPENSE page is registered in Oracle Apps
How IEXPENSE page is registered in Oracle Apps
Function Register for “Expense Home”
OA.jsp?OAHP=OIE_EXPENSES_APPLICATION&OAFunc=OIEHOMEPAGE
Subscribe to:
Posts (Atom)