OAF : IExpense Page Controller Extension Personalization setup

Page: GeneralInformationPG

Customization List

a)      Employee Grade added along with Employee Name

b)     NoteTOApprover Field:- Made Read-Only 

Personalization Setup

1)         Controller Class :- HeaderCO

Navigation:-

a)      Click on Personalize link from the Page -> Select “Complete view” in Personalization Structure-> Click on Personalize “pencil” as shown below

 

a)      Enter the Extended Controller Name at site level as shown below and click on Apply Button

Controller Class:-

Original Definition :- oracle.apps.ap.oie.webui.HeaderCO

Site level :- oracle.apps.ap.oie.webui.XXExtHeaderCO

 

EBS :GL Query : Query to get Ledger details (Oracle)

 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');

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

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

EBS : FND LOAD Scripts, LDT File (Oracle APPS)

FNDLOAD

The FNDLOAD loader program can move the EBS data between database and text file representations.
This is an Oracle utility provided by Oracle for the transfer of a wide range of Oracle Foundation (FND) data from one instance to another instance. This can be defined as a concurrent program that can move Oracle Apps data between database and text file or this can be defined as FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database very easily.

Its help to move the setup from once instance to another instance like Concurrent Program, Alerts, Lookup, Personalization,Data Definitions ,Template,Function etc.

How FNDLOAD Works

The Conversion between database format and text file format is specified by a configuration file in Oracle Apps. Oracle Apps does provide a confirmation file and it is important to just pass the configuration file name and then just call the loader and leave everything.

List of .lct file (Configuration File)

afcpprog.lct : Concurrent Program Definitions

afcprset.lct : Concurrent Request Sets

afffload.lct : Flexfields (KFF, DFF, value sets, value sets data)

aflvmlu.lct : Lookup types and codes

afscprof.lct : Profiles and Profile Values

afscursp.lct : Security data (users, responsibilities, etc)

afsload.lct : Menu / Function Security Data•Lookup Types

afattach.lct : Load attachments setup data

afcppstl.lct: : Concurrent Program Printer Styles

afcpreqg.lct : Concurrent Request Groups

Steps of Creating LDT file:-

1)Login to Application Server using WInscp -> Navigate to path like /home directory where you want to download ldt file.

2).ldt file shall always create in the directory where the fnd_load command will execute

2) Once Downloaded move to local directory and then move to target directory where its need to be uploaded.

----------Concurrent Program------------

Download:-

$FND_TOP/bin/FNDLOAD apps/alam  O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXX_GST_APXINRIR.ldt 

Upload:-

$FND_TOP/bin/FNDLOAD apps/alam64 O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXPROMISE_DATE_CALC_CP.ldt

------Personalization-------

Download:-

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXHIL_PO_UPD_BPA.ldt FND_FORM_CUSTOM_RULES function_name='PO_POXPOEPO' RULE_KEY='XXO_UPD_BPA'

Upload:-

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PROMISE_DT.ldt

----------Valueset download----------------------------------------------------------------

Download:-

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXSANCTION_DISC_TYPE.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME ='XXSANCTION_DISC_TYPE'

Upload:-

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXSANCTION_DISC_TYPE_VL.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME='XXOM_SANCTION_DISC_TYPE'

-------DATA DEFINITIONS---------------

$FND_TOP/bin/FNDLOAD apps/clone 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_GST_CASH_REGS_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME="XX"  DATA_SOURCE_CODE="XX_GST_CASH_REGS" TMPL_APP_SHORT_NAME="XX" TEMPLATE_CODE="XX_GST_CASH_REGS"

-------DataDefintions and Template---

Download:-

Upload:-

FNDLOAD apps/techhards 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct $XXX_TOP/XXX_GST_APXINRIR_DD.ldt

---Upload RTF---------------------------------------------

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD B6gggggg -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=auohsitutxd09.oracleoutsourcing.com)(PORT=16710))(CONNECT_DATA=(SERVICE_NAME=DITU6I)))' -LOB_TYPE TEMPLATE_SOURCE -APPS_SHORT_NAME XXX -LOB_CODE XXX_GST_APXINRIR -LANGUAGE en -TERRITORY IN -XDO_FILE_TYPE RTF -FILE_NAME $XXX_TOP/XXX_GST_APXINRIR.rtf -OWNER apps -CUSTOM_MODE FORCE

--------Upload Bursting--------

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD B6zOC8qb -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=9192.168.0.1)(PORT=167154)(CONNECT_DATA=(SERVICE_NAME=1111I)))' -LOB_TYPE BURSTING_FILE -LOB_CODE XXX_GST_BIP_POXPRPOP -XDO_FILE_TYPE XML -FILE_NAME $XXX_TOP/XXX_GST_PO_PRINT_BURST_CONTROL2.xml -APPS_SHORT_NAME XXX -NLS_LANG 00 -TERRITORY 00 -LOG_FILE $LOG_FILE_NAME

-------Valueset-----------

Download:-

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_SANC_DISC_TYPE.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME ='XX_SANC_DISC_TYPE'

Upload:-

---------Oracle Form-----

Download:-

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct FABIL003_form.ldt FORM APPLICATION_SHORT_NAME="XX" FORM_NAME="FABIL003"

Upload:-

--------Oracle Form Func-----

$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXHIL_PO_REQ_IMP_INTG.ldt FUNCTION FUNC_APP_SHORT_NAME="XXHIL" FUNCTION_NAME="XXHIL_PO_REQ_IMP_INTG"

--Oracle Function other than forms-

Download:-

$FND_TOP/bin/FNDLOAD apps/TECHHARDS 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct TECHHARDS_PO_REQ_IMP_INTG_FUNC.ldt FUNCTION FUNCTION_NAME="TECHHARDS_PO_REQ_IMP_INTG"

Upload:-

*******Workflow******************

Download:-

WFLOAD apps/apps  0 Y DOWNLOAD XXTECHHARDS_POWFRQAG.wft POWFRQAG

Upload:-

WFLOAD apps/apps 0 Y FORCE XXTECHHARDS_POWFPOAG.wft

-------Lookup-----------

Download:-

$FND_TOP/bin/FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_OM_DEV_REMARKS.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME='ONT' LOOKUP_TYPE='XX_OM_DEV_REMARKS'

Upload:-

---Profile Options----------------------------------------------------------

Download:-

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME='' APPLICATION_SHORT_NAME=”FND”

Upload:-

$FND_TOP/bin/FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXHIL_OM_DOM_MKTG_ROLE_PRF.ldt PROFILE PROFILE_NAME="XXHIL_OM_DOM_MKTG_ROLE" APPLICATION_SHORT_NAME="ONT"

###################################################################################WEBADI LDT File download/upload scripts

--################################################################################--

Below are the different components of WebADI the can be migrated from one instance to another

•     Integrators
•     Contents
•     Layouts
•     Mappings
•     Parameter List
•     Components

Integrator:-

  Download:-

FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XXFAHINTEGRATOR_LDT_FILE.ldt BNE_INTEGRATORS INTEGRATOR_ASN=”XXFAHCUST” INTEGRATOR_CODE=”XXFAHINTEGRATOR_CODE”

  Upload :-

FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XXFAHINTEGRATOR_LDT_FILE.ldt 

-------Contents----------

Download:-

FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecont.lct XXFAHCONTENT_LDT_FILE.ldt BNE_CONTENTS CONTENT_ASN=”XXFAHCUST” CONTENT_CODE=”XXFAHCONTENT_CODE” 

Upload:-

FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bnecont.lct XXFAHCONTENT_LDT_FILE.ldt 

-------Layout-------

Download Layout:

 FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnelay.lct XXFAHLAYOUT_LDT_FILE.ldt BNE_LAYOUTS LAYOUT_ASN=”XXFAHCUST” LAYOUT_CODE=”XXFAHLAYOUT_CODE” 

Upload :-

FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bnelay.lct XXFAHLAYOUT_LDT_FILE.ldt 

-------Mappings-------

  Download :-

FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnemap.lct XXFAHMAPPINGS_LDT_FILE.ldt BNE_MAPPINGS MAPPING_ASN=”XXFAHCUST” MAPPING_CODE=”XXFAHMAPPINGS_CODE” 

Upload :-

FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bnemap.lct XXFAHMAPPINGS_LDT_FILE.ldt 

Parameter Lists

  Download Parameter List

    FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneparamlist.lct XXFAHPARAM_LIST_LDT_FILE.ldt BNE_PARAM_LISTS PARAM_LIST_ASN=”XXFAHCUST” PARAM_LIST_CODE=”XXFAHPARAM_LIST_CODE” 

  Upload Parameter List

    FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bneparamlist.lct XXFAHPARAM_LIST_LDT_FILE.ldt 

Components

  Download Component

    FNDLOAD apps/alam 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecomp.lct XXFAHCOMPONENT_LDT_FILE.ldt BNE_COMPONENTS COMPONENT_ASN=”XXOM” COMPONENT_CODE=”XXFAHCOMPONENT_CODE” 

  Upload Component

    FNDLOAD apps/alam 0 Y UPLOAD $BNE_TOP/patch/115/import/bnecomp.lct XXFAHCOMPONENT_LDT_FILE.ldt 

--################################################################################--

#FNDLOAD #Oracle Apps #Migration Utility #.lct #.ldt #EBS

Download / Upload LDT File

Download / Upload WFT File

EBS : Sql Query: OE : How to get open Supply Demand Query for Internal Sales Order

How to get open Supply Demand Query for Internal Sales Order

/********* Internal Sales Order & Sale Order Query***/

SELECT  TO_CHAR (ooha.order_number) order_num,ml.meaning supply_demand_type,d.requirement_date required_date,      oola.line_id, oola.request_date, wda.delivery_id,

       NVL (  -1 * (  d.primary_uom_quantity - d.total_reservation_quantity    - d.completed_quantity ), 0 ) qty,

  FROM mtl_parameters p,

       mtl_system_items_b i,

       bom_calendar_dates c,

       mrp_demand_om_reservations_v d,

       oe_order_headers_all ooha,

       oe_order_lines_all oola,

       wsh_delivery_assignments wda,

       wsh_delivery_details wdd,

       mfg_lookups ml,

       (SELECT DECODE (demand_source_type,

                       2, DECODE (reservation_type, 1, 2, 3, 23, 9),

                       8, DECODE (reservation_type, 1, 21, 22),

                       demand_source_type

                      ) supply_demand_source_type,

               demand_id

          FROM mrp_demand_om_reservations_v) dx

 WHERE d.open_flag = 'Y'

   AND ml.lookup_type = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'

   AND ml.lookup_code = dx.supply_demand_source_type

   AND d.demand_id = dx.demand_id

   AND ooha.header_id = oola.header_id

   AND oola.line_id = d.demand_id

   AND wdd.source_line_id(+) = oola.line_id

   AND wdd.source_header_id(+) = oola.header_id

   AND wdd.delivery_detail_id = wda.delivery_detail_id(+)

   AND d.reservation_type != 2

   AND d.organization_id = :p_org_id

   AND d.primary_uom_quantity >

                        (d.total_reservation_quantity + d.completed_quantity

                        )

   AND d.inventory_item_id = :p_inventory_item_id

   AND (   d.visible_demand_flag = 'Y'

        OR (    NVL (d.visible_demand_flag, 'N') = 'N'

            AND d.ato_line_id IS NOT NULL

            AND NOT EXISTS (

                   SELECT 1

                     FROM oe_order_lines_all ool, mtl_demand md

                    WHERE TO_CHAR (ool.line_id) = md.demand_source_line

                      AND ool.ato_line_id = d.ato_line_id

                      AND ool.item_type_code = 'CONFIG'

                      AND md.reservation_type IN (2, 3))

           )

       )

   AND d.reservation_type != -1

   AND d.reservation_type != -1

   AND d.demand_source_type != -1

   AND d.demand_source_type != -1

   AND TRUNC (d.requirement_date) <= :p_to_date

   AND (   d.subinventory IS NULL

        OR d.subinventory IN (

              SELECT s.secondary_inventory_name

                FROM mtl_secondary_inventories s

               WHERE s.organization_id = d.organization_id

                 AND s.inventory_atp_code = 1

                 AND s.attribute1 = 'FG')

       )

   AND i.organization_id = d.organization_id

   AND i.inventory_item_id = d.inventory_item_id

   AND p.organization_id = d.organization_id

   AND p.calendar_code = c.calendar_code

   AND p.calendar_exception_set_id = c.exception_set_id

   AND c.calendar_date = TRUNC (d.requirement_date)

   AND d.inventory_item_id =

          DECODE (d.reservation_type,

                  1, DECODE (d.parent_demand_id,

                             NULL, d.inventory_item_id,

                             -1

                            ),

                  2, d.inventory_item_id,

                  3, d.inventory_item_id,

                  -1

                 )