add

About Me

My photo
Oracle Apps - Techno Functional consultant

Monday, February 18

Oracle Apps FAQS

Inventory
1.      Write a common  column between  MTL_PARAMTER AND MTL_SECONDARY_INVENTORY?
Ans:  Oragnisation_Id=Oraganisation_Id

2.Difference between MTL_PARAMETER and ORG_ORGANIZATION_DEFINITIONS?
Ans: MTL_parameter is a table
org_organisation_definition is a view and it is created based on hr_operating_units, gl_ledgers, Mtl_parameters, hr_oraganization_definitions

3.What is the KFF name and UNIQUE_ID Column and structure column for Stock Locator KFF?
ans: stock locator  is kff name
unique_id column  is inv_location _id
structure column is organization_id

4.MTL Stands For?
ans:material

5.What is the Navigation To Create Sub-Inventory OR  Secondary Inventory Or store Inventory?
ans: inventory vision operations : - setup – organization –subinventory

6. write a sql query to display master inventory organizations?
ans: select * from mtl_parameters where organization_id=master_organization_id

7.write a sql query to display child inventory organizations?
ans: select * from mtl_parameter where organization_id<>master_oraganization_id

8.write a sql query to display child inventory organizations of the master v1?
ans: select * from mtl_parameters where master_organization_id 204,8229=
(select organization_id from mtl_parameters where organization_code=’v1’)
and organization_id<>master_organization_id.


organization_id                            organization_code         master_organization_id
204                                                            v1                                           204
207                                                            m1                                           204     
209                                                            m2                                           204
8229                                              hm                                          8229

9. what is the primary key in msib table ?
ans: it is composit primary key based on inventory_id and organization_id

10. link between msib and mtl_item_catgories?
ans: inventory_item_id = inventory_item_id and organization_id = organization_id

11. link between msib and mtl_onhand_quantities?
ans: msib.inventory_item_id =moq.inventory_item_id
         and msib.organization_id=moq.organization_id

12.link between msib and mtl_parameters?
ans: organisation_id = organisation_id

13. what is the navigation to assign template to the item?
ans:  go to item windows(items- master items)
           go to tools – copy from – select templates – click on applay and done.

14.what is abc analysis?
ans: logical grouping of inventory items based on cost and quantity.

15.difference between _b,_tl,_vl objects?
ans: _b for base table
       _tl for translated language
      _vl View Language

16. what is the navigation to create template?
ans: inventory,vision operations(usa)  - setup  - item  - templates
         select v1
        ok
        click on new
       enter template name à his purchase
        and click attributes
query: select * from   mtl_item_templates where template_name=’his purchase’

17. what is commodity code?
ans: logical grouping of items based on similar properties
        NAVIGATION:  SETUP – ITEMS – CUSTOMER ITEM COMMODITY CODE
       CLICK ON NEW BUTTON  ENTER  COMMODITY_CODE  NAME AS àHIS RADIO ACTIVE
QUERY:
SELECT * FROM MTL_COMMODITY_CODES WHERE COMMODITY_CODE =’HIS RADIO ACTIVE’

18. what is rank or preference number?
ANS: ALTERNATE OR SUBSTITUTE INVENTORY ITEM FOR A CUSTOMER ITEM

19. link between mtl_system_item_ b and mtl_customer_items?
ANS:   TABELSàMSIB,MCI,MCIX
            MCI.CUSTOMER_ITEM_ID=CUSTOMER_ITEM_ID
            MSIB.INVENTORY_ITEM_ID=MCIX.INVENTORY_ITEM_ID
   AND MSIB.ORGANIZATION_ID = MCIX.MASTER_ORGANIZATION_ID

20. WRITE A SQL QUERY TO DISPLAY ORGANIZATION_ID FOR ORGANIZATION CODE M1
ANS: TABLESà MP,MSIB
       MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID 

21. WRITE  A SQL QUERY TO DISPLAY ON_HAND_QUANTITY FOR THE ITEM ACER LAPTOP
ANS:  SELECT  SUM(MOQ.TRANSACTION_QUANTITY)
           FROM    MTL_SYSTEM_ITEMS_B  MSIB, MTL_ONHAND_QUANTITY  MOQ
           WHERE  MSIB.ORGANIZATION_I= MOQ.ORGANIZATION_ID
                 AND  MSIB.INVENTORY_ITEM_ID= MOQ.INVENTORY_ITEM_ID
                AND   MSIB.SEGMENT1=’ACER LAPTOP’

22. WRITE A SQL QUERY TO DISPLAY RESERVATION QUANTITY FOR THE ITEM ACER LAPTOP?
ANS: SELECT SUM(MR.RESERVATION_QUANTIYT)
       FROM            MTL_SYSTEM_ITEMS_B MSIB,
                  MTL_RESERVATION     MR
     WHERE  MSIB.ORGANIZATION_ID =MR.ORGANIZATION_ID
     AND       MSIB.INVENTORY_ITEM_ID =MR.INVENTORY_ITEM_ID
    AND     MSIB.SEGMENT1=’ACER LAPTOP’

23.WRITE A SQL QUERY TO DISPLAY ON_HAND_QUANTITY FOR THE ITEM ACER LAPTOP AND ORGANISATION CODE
ANS: TABLESà MSIB,MOQ,MP
         SELECT SUM(MOQ.TRANSACTION_QUANTITY)
         FROM    MTL_SYSTEM_ITEM_B MSIB
                     MTL_ONHAND_QUANTITY  MOQ
                        MTL_PARAMETER         MP
         WHERE MSIB.ORGANISATION_ID = MOQ.ORGANISATION_ID AND
                       MSIB.INVENTORY_ITEM_ID = MOQ. INVENTORY_ITEM_ID AND
                  MOQ.ORGANIZATION_ID = MP.ORGANISATION_ID  AND
                  MSIB.SEGMENT1=’ACER LAPTOP’   AND
                  MP.ORGASATION_CODE=’M1’

24. WRITE SQL QUERY TO DISPLAY MASTER ORGANISATION CODE FOR THE  ORGANISATION CODE M1
ANS:   SELECT ORGANISATION_CODE
            FROM MTL_PARAMETERS WHERE
                    ORGANISATION_ID=(
                                                      SELECT mASTER_ORGANIZATION_ID FROM MTL_PARAMETERS
                                                                 WHERE ORGANISATION_CODE=’M1`)

25. WRITE Q SQL QUERY TO DISPLAY CUSTOMER_ITEM_NUMBER FOR THE ITEM ACER LAPTOP
ANS:   SELECT MCI.CUSTOMER_ITEM_NUMBER
            FROM  MTL_SYSTEM_ITEMS_B MSIB,
                        MTL_CUSTOMER_ITEM_XREFS MCIX
                      MTL_CUSTOMER_ITEMS MCI
      WHERE             MCI.CUSTOMER_ITEM_ID=MCIX.CUSTOMER_ITEM_ID AND
                      MSIB.INVENTORY_ITEM_ID=MCIX.INVENTORY_ITM_ID AND
                         MSIB.ORGANISATION_ID=MCIX.MASTER_ORGANISATION_ID AND
                     MSIB.SEGMENT1=’ACER LAPTOP’

26. WRITE A SQL QUERY TO DISPLAY INVENTORY ITEM,RANK FOR THE CUSTOMER ITEM ACER ABC LAPTOP?
ANS: SELECT   MSIB.SEGMENT1,
                         MCIX.PREFERENCE_NUMBER
         FROM    MTL_CUSTOMER_ITEMS MCI,
                        MTL_CUSTOMER_ITEM_XREFS MCI
                        MTL_SYSTEM_ITEMS_B MSIB
         WHERE MCI.CUSTOMER_ITEM_ID=MCIX.CUSTOMER_ITEM_ID  AND
                        MCIX.MASTER_ORGANISATION_ID=MSIB.ORGANISATION_ID  AND
                        MCI.CUSTOMER_ITEM_NUMBER=’ACER ABC LAPTOP’

27.WRITE A SQL QUERY TO DISPLAY ORGANISATION_CODE,ORGANISATION_ID,
MASTER_ORGANISATION_CODE,MASTER_ORGANISATION_ID?
ANS: SELECT C.ORGANISATION_CODE,
                       C.ORGANISATION_ID,
                       M.ORGANISATION_CODE,
                  C.MASTER_ORGANISATION_ID
      FROM   MTL_PARAMETERS C,
                   MTL_PARAMETERS M
     WHERE C.MASTER_ORGANIZATION_ID=M.ORGANISATION_ID

28. WRITE A SQL QUERY TO DISPLAY MASTER ORGANIZATION CODE VALUE AS ITSELF WHEN IT IS MASTER INVENTORY ORGANISATION OTHERWISE DISPLAY MASTER ORGANISATION CODE?
ANS: SELECT DECODE
(C.ORGANISATION_ID,C.MASTER_ORGANISATION_ID,’ITSELF’,C.ORGANISATION_CODE)
FROM MTL_PARAMETER C,MTL_PARAMETER M
WHERE C.MASTER_ORGANIZATION_ID=M.ORGANISATION_ID

29.WRITE A SQL QUERY TO DISPLAY ITEMS WHICH EXISTING IN M1 AND M2 ORGANISATIONS
ANS: SELECT M1.SEGMENT1
           FROM MTL_SYSTEM_ITEMS_B M1,
                        MTL_PARAMETER MP
        WHERE  ORGANISATION_ID=MP.ORGANISATION_ID AND
                        MP.ORGANISATION_CODE=’M1’
      INTERSECT
                  SELECT M2.SEGMENT1
                      FROM   MTL_SYSTEM_ITEMS_B  M2,
                                    MTL_PARAMETER  MP
                      WHERE M2.ORGANISATION_ID = MP.ORGANISATION_ID
                       AND MP.ORGANISATION_CODE =’M2’
                             
                      
 

                 
oM
1.link between oe_order_headers_all and wsh_delivery_details?
ans:header_id = source_header_id

2.how to get trx number basedon sales order number?
ans: go to sales order window  àf11àenter sales order numberàcick on cntrl+f11à
click on line itemsàclick on actions butttonsàselect additional line informationà
okàclick on invoice/credit  memoànumber is nothing but trx number.

3.link between om and ar modules?
ans: ooha.order_number=rcta.interface_header_attribute1

4.how to transfer data from cm to ar?
ans: by running concurrent programme “work flow background process”

5.what is  navigation to cancel the sales order?
ans: goto sales order windowàf11àenter sales order number(cntrl+f11)àclick on actionsà select actions as cancel.

6. what is navigation to get delivery name based on sales order number?
ans: go to sales order windowàf11àenter order numberàcntrl+f11àclick on line itemsàclick on actions buttonàselect additional line informationàclick on delivery tabàdelivery  is nothin but delivery name.

7.link between om and inventory?
ans:mtl_system_items_b , oe_order_lines_all
  inventory_item_id=inventory_item_id and organisation_id=ship_from_org_id

8.what is back order?
ans:when there is no avialable stock ,automatically release sales order programme will create back order.

9.what are the programs will fire when create release sales order?
ans: pick slip report, pick selection generation, shipping exception report.

10. what is dropshipment?
ans: we transfor the goods from supplier to customer without maintaing  any records in oracle applications

11.how to get sales order number based on requistion number?
ans:purchasingàrequistionàrequisition summaryàenter requisition numberàclick on find button and click on lines button.

12.how to get reqistion number based on sales order number?
ans: go to sales orderàf11àenter order numberàcntrl+f11àclick on line itemsàclick on actions&select additional informationàclick on “dropship tab”

13. what are the tables get hit when enter the sales order?
ans: oe_order_header_all and oe_order_lines_all

14.interface tables for sales order?
ans: oe_headers_iface_all and oe_lines_iface_all
15.wms,tms,oe stands for?
ans:wmsàwarehouse management system
         tmsàtransport management system
         oeàorder entry

16. write a sql query to display the delivery name for the sales order?
ans:  select wda.delivery_id “delivery_name”
      from oe_order_headers_all ooha,
                  wsh_delivery_details wdd,
                  wsh_delivery_assignments wda
      where ooha.header_id=wdd.source_header_id
                 and   wda.delivery_detail_id = wdd.delivery_detail_id
                and    ooha.order_number=67506

17.write sql query to display item name for order number 67506?
ans:select msib.segment1 item
      from mtl_system_items_b msib,
                  oe_order_header_all ooha,
                  oe_order_lines_all oola
      where msib.organisation_id=oola.ship_from_order_id and
                  msib.inventory_item_id=oola.inventory_item_id and
                  ooha.order_number_number=67506
18. write a sql to query trx number fro the sales order number 67506?
ans: select rcta.trx_number
      from ra_customer_trx_all rcta,
                  oe_order_headers_all ooha
      where rcta.interface_header_attribute1=to_char(ooha.order_number)
      and ooha.order_number=67506.

19. how to create new sales order based on existing sales order?
ans: goto sales  order window àf11àenter sales order windowàcntrl+F11àclick on actions &select copyàclick on okàcopy orderàclick on continue

20.write a sql query to display the organisation code for the order number 67506
ans: select  mp.organisation_code
        from mtl_parameters mp,
           oe_order_headers_all ooha,
         oe_order_lines_all oola
    where mp.organisation_id=oola.ship_from_org_id
    and   ooha.header_id=oola.header_id
  and    ooha.order_number=67506

22.atp  stands for  available to programme
      gpo stands for global promising orders

23.how to get top name or application short name based on application name
ans:   application developeràapplicationàregisteràf11àenter application nameàcntrl+f11

24. what are the referenced  types when we create rma sales order?
ans: sales order,trx number,sales number,customer po number.
           
ar
1.what are the status of ar invoice or ar transaction?
ans: goto transaction windows àclick on more tabàsee status open,closed,pending,void(cancel)

2.what are the account will generate when create ar invoice?
ans:  receivable,revenue,frieght,tax

3.what is the tables get hit when click on complete button?
ans: ar_payment_schedules_all

4.what are tha tables get hit when create transaction?
ans:ra_customer_trx_all,
ra_cust_trx_line_gl_dist_all,ra_cust_trx_line_salesreps_all

5.line between ra_customer_trx_all and hz_parties?
ans: hz_parties,hz_cust_accounts_all,ra_customer_trx_all
        hp.party_id=hcaa.party_id,hcaa.cust_account_id=rcta.bill_to_customer_id   

6.link between ra_customer_trx_all and ar_payment_schedules_all?
ans:customer_trx_id=customer_trx_id

7.link between ra_customer_trx_all and ar_cash_receipts_all?
ans: we need extra table ar_receivable_applications_all
  rcta.customer_trx_id=araa.applied_customer_trx_id
  acra.cash_receipt_id=araa.cash_receipt_id

8.link between om and ar module?
ans:ooha,rcta
        order_number=interface_header_attribute1

9.how to check account entries in ar transactions window?
ans: step1   goto transactions windowàf11àenter trx numberàclick on cntrl f11
                         toolsàcreate accountàok
     step2       click on toolsàview accountingàenter username and password
                       debit                                           credit
                    ==========                                 ===========
                receivable                                  frieght,revenue,tax

10. what is the information in ar_cash_receipt_history_all?
ans: select * from ar_cash_receipt_history_all
cleared or applied,  reversed or un upplied, conformed, remitted

11.what is the open interface table for autoinvoice?                                                                                                       ans:ra_interface_lines_all

12.what is the error table name auto invoice?
ans:ra_interface_errors

13.what are the tables  get hit when create receipt?
ans:ar_cash_receipts_all,ar_cash_receipt_history_all,ar_receivable_applications_all

14.tca stands for?
ans: trading community architecture

15.difference between party and customer?
ans: customer: 1. minimum one account is required hz_cust_acc_all
                            2.transactions are required om and ar module
        party: 1. do not required any account
  1. there are no transactions in om and ar module
  2.  party is applicable in crm,vendors,suppliers,bank accounts

16. write a sql query to display the customere name for the transaction number 12378
ans:                     select hp.party_name custmer_name
                  from hz_parties,
            hz_cust_accounts_all,
                             ra_customer_trx_all
                         where  hp.party_id=hcaa.party_id and
                                        hcaa.cust_account_id = rcta.bill_to_customer_id

17.write a sql query to display the receipt number for the trx number 12378?
ans:   ra_customer_trx_all,ar_cash_receipt_all,ar_receivable_applications_all
          <select acra.receipt_number>
 
rcta.cust_trx_id=acti.applied_customer_transaction_id
 acra.cust_receipt_id=araa.cust_receipt_id

18. write a sql  to query to display ship_to_address for the trx number 12378
ans: select  hp.party_name,
                hcaa.account_number,
                hl.address1,
                hl.address2,
                hl.address3,
                         hl.address4,
                         hl.city,
                hl.postal_code
               hl.country
from    hz_parties hp,
      hz_cust_accounts_all hcaa,
      hz_cust_acct_sites_all  hcas,
      hz_cust_site_uses_all hcsua,
      hz_party_sites hps,
      hz_locations hl
      ra_customer_trx_all rcta
where hp.party_id=hcaa.party_id
and   hcaa.cust_account_id=hcsa.cust_account_id
and hcsa.cust_acct_site_id=hcsua.cust_acct_site_id
and hcsua.site_use_id=rcta.ship_to_site_use_id
and rcta.bill_to_customer_id=hcaa.cust_account_id
and hcsa.party_site_id=hps.party_site_id
and hl.location_id=hps.location_id
and hcsua.site_use_code=’ship_to’
and rcta.trx_number=’12378’

         
       
po
1.what is navigation to cancel to po?
ans:purchase ordersàpurchase order summaryàenter po numberàclick on findàgoto toolsàcontroleàselect actions as cancel poàclick ok

2.what are the tables create get hit when purchase order?
ans:po_action_history,po_headers_all,po_lines_all

3.what is the table get hit when click on approve button?
ans:po_action_history

4.link between requisition and po tables?
ans: tablesàprha,prla,prda,pha,pla,plla,pdd
          prda.distribution_id=pdd.distribution_id

5.link between po and receipt tables?
ans: tablesàpha,pla,plla,pda,rsh,rsl
          plla.po_line_location_id=rsl.line_location_id and
          plla.ship_to_organization_id=rsl.to_orgainisation_id
          pda.po_distibution_id=rsl.po_distribution_id

6.what are the concurrent programms will fire when applay the receipt?
ans:ads,pay  on receipt auto invoice

7.what are the required fields to create blanket agreement ?
ans:t&C, goods&services

8.difference between planned and blanket?
ans:planned: long-term,ßàno expire dateßàwe can create schedule releaseßàrequired fields are t&C,G&s, Price and Quantity.
Blanket:  short term agrement,ßàthere is experation dateßàwe can create blanket releaseßàrequired fields are t&C,G&s

9.rcv stands for ?
ans: Rcv stands for Receiving.

10. what is match approval level?
ans: 2-way:Po                   ----                   Apinvoice
         3-way:po                    receipt        apinvoice
         4-way:po                    receipt        inspection required apinvoice

11. link between po_headers_all and po_agent?
ans:agent_id=agent_id

12.what are the receipt routing types?
ans:direct,standard,inspection required.

13.link between po and inventory module?
ans: tables: msib,pla,plla
         msib.inventory_item_id=pla.item_id
         msib.organisation_id=plla.ship_to_organisation_id
         pla.po_line_id=plla.po_line_id

14.link between po_headers_all and po_release_all
ans:po_header_id=po_header_id

15.link between hr_locations and po tables
ans:hr_locations.location_id=plla.ship_to_location_id

16.what is the navigation for invoice match option in po window?


ap
1.what are the tables get hit when create ap invoice?
ans:ap_invoice_all,ap_invoice_lines_all,ap_invoices_distributions_all,ap_payment_schedules_all,ap_holds_all       

2.link between ap invoice and suppliers?
ans:venoder_id=vendor_id

3.link between  apinvoice and checks?
ans: tablesàap_invoice_all,ap_checks_all,ap_invoice_payments_all
aca.check_id=aipa.check_id
aia.invoice_id=aipa.invoice_id

4.payment types?
ans:manual,quick,refund
5.payment methods?
ans:check,wire,clearing,electronics

6.link between ap invoice and payment methods?
ans: tablesàap_invoice_all,ap_invoice_lines_all,po_headers_all,po_lines_all,po_line_locations_all,
po_distributions_all
aila.po_distribution_id=pda.po_distribution_id

7.link between ap_invoice_all and ap_payment_schedule_all?
ans:invoice_id=invoice_id

8.what are the accounts will be generated when creates ap invoice?
ans:charge account(dr) and accural account(Cr)

9.what are the accounts will be generated when make a payment?
ans:liability(dr),cash clearing(cr)

10.difference between debit memo and credit memo?
ans:debit memoàthe negative transaction which is created by customer.
        credit memoàthe negative transaction which is created by supplier.

11.what are the new changes in ap module in r12?
ans:bank tables, supplier tables po to ap, added lines concept in ap invoice.

12.iby stands for?
ans: iby means payment.

13.what is prepayment invoice?application
ans: advance payment
          application developerèapplicationèregister

14.what are the open interface tables for ap invoice import/interface?
ans:ap_invoices_interface,ap_invoices_lines_interface

15.what is error table name for ap invoice?
ans:ap_interface_rejections.

GL
1.difference between entered and accounting?
ans: suspense account will be created by posting when debit is not equal to credit.

2.what is suspense account?
ans:it is used to generate code combinations dynamically in combination table and foreign key tables.

3.what is ledger?
ans:it is the collection of 4c’s

4.what is cost centre?
ans:from where the profit or loss is generating

5.what is flexfield qualifier?
ans:it is used to identify certain segments for specific purpose.
example: balancing segment,intercompany segment,cost cnter,natural account,secondary tracking.

6.what is kff name,structure column,unique id column,accounting flexfield?
ans:name: accounting flexfield
        structure column :chart_of_account_id
        code_combination_id

7.link between gl_ledgers and gl_je_headers?
ans:ledger_id=ledger_id

8.link between gl_code_combinations and gl_je_headers?
ans:gl_code_combination,gl_je_headers,gl_je_lines
       gjh.Je_header_id=gjl.je_header_id
       gcc.code_combination_id=gjl.code_combination_id

9.link between fnd_id_flex_structures and GL_ledgers?
ans:fifs.id_fle_num=gl.coa

10.what are the table get hitwhen enter journal?
ans:gl_je_batches
        gl_je_headers
        gl_je_lines

11.what is the tablel name,column name to know the posting status?
ans:gl_je_headers.status=u
         gl_je_headers.status=p

12.what is the table for code_combinations?
ans:gl_code_combinations

13.je stands for?
ans:journal entry

14.what is the opne  interface table for gl_interface?
ans:gl_interface

15.what is the seeded programme name gl?
ans:journal import 

No comments: