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

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

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

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 OA.jsp?page=/xxmj/oracle/apps/ibe/customer/delivery/webui/CustDeliveryDetailsPG&retainAM=Y&DeliveryId={@DeliveryNumber}   OA.jsp?page=/xxmj/oracle/apps/ibe/customer/reports/webui/DeliveryReportPG&retainAM=Y&DeliveryId={@DeliveryNumber}&ProgramShortName={@DynamicUrl}&OrderNum={@OrderNumber}
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)

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

    

EBS :Project Billing - Navigation Flow

 How to open Period

Expenditure Inquiry

Invoice Review Inquiry

Revenue Review Inquiry

EBS : OM Query (Sales Order)

Sales Order Query 

--Query to get quote number in Sales Order Table

select quote_number,flow_status_code,a.* from oe_order_headers_all a

where 1=1 and order_number=4800785

ORDER BY CREATION_DATE DESC

--Line Table

select flow_status_code, a.LINE_CATEGORY_CODE, a.* from oe_order_lines_all a

where header_id=42137275

--Delivery Detail Table

select a.RELEASED_STATUS,a.* from wsh_delivery_details a

where source_header_id=213727

--Delivery assignment Table

select * from wsh_delivery_assignments

where DELIVERY_DETAIL_ID =43978725

--Delivery Table

select * from wsh_new_deliveries

where source_header_id=42137275

--MTL Demand Table

select * from mtl_demand

where DEMAND_SOURCE_line_ID=418307

--Reservation Tab

select * from mtl_reservations

where DEMAND_SOURCE_line_ID =541830743 

--Query toget Order WF Details

SELECT   h.header_id, l.line_id, l.org_id,h.order_number

    FROM oe_order_headers_all h,

         oe_order_lines_all x,

         wf_item_activity_statuses wias,

         wf_process_activities wpa

   WHERE h.header_id = l.header_id

     AND h.org_id = l.org_id

     AND NVL (h.transaction_phase_code, 'F') = 'F'

     AND h.open_flag = 'Y'

     AND l.open_flag = 'Y'     

     AND l.line_category_code <> 'RETURN'    

    AND l.item_type_code <> 'SERVICE'

     AND l.source_type_code <> 'EXTERNAL'

     AND h.order_number >= :order_number_low

     AND h.order_number <= :order_number_high     

     AND wias.item_type = 'OEOL'

     AND wias.process_activity = wpa.instance_id     

     AND wpa.activity_item_type = 'OEOL'     

     AND wpa.activity_name = 'SCHEDULING_ELIGIBLE'

     AND wias.activity_status = 'NOTIFIED'     

     AND wias.item_key = TO_CHAR (l.line_id)

ORDER BY l.org_id, l.top_model_line_id, l.line_id

--Transaction Account from SO Link

SELECT * FROM mtl_transaction_accounts mtaWHERE transaction_id IN (  SELECT transaction_id  FROM mtl_material_transactions 

WHERE trx_source_line_id IN (SELECT line_id FROM oe_order_lines_all WHERE header_id =1333 ))

--AR Invoice Header Table

select * from ra_customer_trx_all a