Monday, December 27, 2010

Procure to Pay Cycle in Oracle Application



Procure to Pay Cycle  

Different steps involved in P to P cycle they are `

S.No
Step
Defined At
1
Define items
Inventory
2
Enter item information
Purchase Order
3
 Raise requisition
Purchase Order
4
Request For Quotation(RFQ) raised
Purchase Order
5
Quotation received
Purchase Order
6
Quote analysis
Purchase Order
7
Raise purchase order
Purchase Order
8
Receive materials
Inventory
9
Enter invoice
Accounts payable
10
Payment process
Accounts payable
11
Transfer to general ledger
General ledger

Generating a purchase order
Purchasing order can be generated in two ways
1) Auto generation
2) Procedural generation
Auto generation: The various step that are involved in auto generation of purchase order is as under
1) Click on Switch responsibility and select purchasing module and press enter
2) Click on Supply Base-->  Suppliers, a new form opens
3) Enter Supplier name and click on sites, a new form opens
4) Enter all the required information and also see that Purchasing and Pay options are enabled
5) Click on contacts and enter the required information, save the form and close it.
6) Click on Suppliers list –to create a supplier list and add the suppliers that we have created.
7) Save the form and close it.
Creating a Requisition
1)     Click on Requisition-->  Requisitions a new form opens
2)     Enter all the required information and save.
3)     The approve button is enabled .click on approve button a new form opens.
4)     Enter the hierarchal information and click on OK and close the form.
Raising a Purchase order directly
1) Click on the ‘Auto create’ a new form opens enter the requisition number and click on ‘Find’.
 2) A new form opens. Check the item line and Select the document type as ‘Standard po’                and click on ‘Automatic’ tab.
3) A new form opens and click on ‘Create’ tab.
4) A window with the message with your purchase order number is displayed click ‘ok’ on it
5) Now click on “tools” and then on “copy Document” a new form opens,
6) click on ‘ok’ tab. And a new window with purchase order appears.
7) A new form opens Click on Approve button on this form
8) Enter the required information (real time) and click on ‘ok’ (submit approval)
 9) Click ‘ok’ on new window that appears
 10) And now we can see the status as ‘approved’ on the purchase order form.
2) Procedural generation The various step that are involved in auto generation of purchase order is as under
1) Click on Switch responsibility and select purchasing module and press enter
2) Click on Supply Base-->  Suppliers, a new form opens
3) Enter Supplier name and click on sites, a new form opens
4) Enter all the required information and also see that Purchasing and Pay options are enabled
5) Click on contacts and enter the required information, save the form and close it.
6) Click on Suppliers list –to create a supplier list and add the suppliers that we have created.
7) Save the form and close it.
Creating a Requisition
1) Click on Requisition
2) Enter all the required information and save.
3) The approve button is enabled .click on approve button a new form opens.
4) Enter the hierarchal information and click on OK and close the form.
Raising A RFQ
1) A new form opens. Select the document type as RFQ and click on ‘Automatic’ tab.
2) A new form opens and clicks on ‘Create’ tab.
3) A new form opens change the status as “Active” and click on “Suppliers” tab a new from opens, enter the required information and save the form and close it.
4) Click on tools and select “copy document” a new from opens
5) Enter the required information and click on “OK” a new form opens
Approving the Quotation
1) A new from opens
2) Change as status as”active” click on “Type” and then on “approve a new form opens
3) Enter the require information and click on “ok” and a message with text ‘quotation has been approved’ will  appear, now click on ‘ok’ and close the form
4)  Now click on “tools” and then on “copy Document” a new form opens, click ‘ok’ on the document where your purchase order will be created with a number..
5) Click ‘ok’ on the document a new form opens
Raising a Purchase Order
1) A new form opens, Click on Approve button on this form
2) Enter the required information (real time)and click on ‘ok’(submit approval)
3) Click ‘ok’ on new window that appears
4) And now we can see the status as ‘approved’ on the purchase order form.
To view Summary of the purchase order
1) Close all the existing opened forms and click on ‘notification summary’ In the main menu.
2) A new window opens in the internet explorer
3) Enter the user name and password provided by the system admin and login
4) A new window opens with all the purchase order details.
5) Click on the exact purchase order which is created by you. this can be done only if you remember your purchase order number.
Releasing the Order
The step by step process of releasing the order is as under:
1) Click on Purchase Order          Releases –A new form opens
2) Enter the Purchase Order number and other required information and click on ‘Save’
3) ‘Approve’ button gets enabled now, hence click on it.
4) A new form opens, enter the required information and click on ‘ok’
5) Now click on ‘ok’ tab that is seen on message window.
6) Now the status changes to ‘Approved’
7) Now go to ‘Notification Summary’ window
8) You can see the ‘Blanket Purchase release document’ approved.
9) Click on it to view the detailed summary.
Receiving the Order
The step by step process of receiving the order is as under:
1) Click on Receiving        Receipts –A new form opens select the organisation and click on ‘ok’ simultaneously a new form opens
2) Enter the P.O number in that form and click on ‘find’
3) ‘Receipt Header’ form opens-click on new receipt radio button and close the form
4) A new form opens, check box the item that is available there and save the form. Close it.
5) Click on ‘receiving transactions’, a new form opens.
6) Enter the P.O number and click on ‘Find’, a new form opens.
7) Check box the item that is available and select ‘Stores’ as sub inventory .save and close the form.

PLSQL Block to attach concurrent program to request group


BEGIN
fnd_program.add_to_group ('xxxtestconprog'--concurrent program short name
                                            ,'xxx' --Concurrent Program Application Short Name
                                            ,'All Reports' --Request Group Name
                                            ,'xxx' --Request Group Application Short Name
                                            );
END;

FND API : Code for attaching request set to request group

Code for attaching request set to request group

BEGIN
-- Add Request Set to request group.
BEGIN
fnd_set.add_set_to_group (request_set => 'XXX_TEST_REQUEST_SET',
set_application => 'XXX', --REQUEST SET APPLICATION SHORT NAME
request_group => 'KNOWORACLE',---REQUEST GROUP NAME
group_application => 'XXX'--REQUEST GROUP APPLICATION SHORT NAME
);
DBMS_OUTPUT.PUT_LINE ('"XXX_TEST_REQUEST_SET" attached to request group Succesfully ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error in attaching "XXX_TEST_REQUEST_SET" to Request Group ' || SQLERRM);
END;

COMMIT;

END;
/

FND API :CODE FOR UPDATING USER PASSWORD IN ORACLE

FND API :CODE FOR UPDATING USER PASSWORD IN ORACLE

BEGIN
FND_USER_PKG.UPDATEUSER(x_user_name       => 'KNOWORACLE'
                                                        ,x_owner                => 'SEED'
                                                        ,x_unencrypted_password => 'knoworacle@351'
                                                        ,x_password_date        => SYSDATE + 90);

END;

Query to find out Concurrent programs attached to request group

Query to find out Concurrent programs attached to request group
SELECT request_group_name,
       DECODE (request_unit_type,
               'A', 'Application',
               'P', 'Program',
               'S', 'Request Set',
               'no'
              ) request_unit_type,
       user_concurrent_program_name program_name
  FROM fnd_request_groups a,
       fnd_request_group_units b,
       fnd_concurrent_programs_vl c
 WHERE a.request_group_id = b.request_group_id
   AND b.request_unit_id = c.concurrent_program_id
   AND request_group_name = :request_group_name
   AND request_unit_type = 'P'
UNION
SELECT request_group_name,
       DECODE (request_unit_type,
               'A', 'Application',
               'P', 'Program',
               'S', 'Request Set',
               'no'
              ) request_unit_type,
       application_name program_name
  FROM fnd_request_groups a, fnd_request_group_units b, fnd_application_tl c
 WHERE a.request_group_id = b.request_group_id
   AND b.request_unit_id = c.application_id
   AND request_group_name = :request_group_name
   AND request_unit_type = 'A'
   AND LANGUAGE = 'US'
UNION
SELECT request_group_name,
       DECODE (request_unit_type,
               'A', 'Application',
               'P', 'Program',
               'S', 'Request Set',
               'no'
              ) request_unit_type,
       request_set_name program_name
  FROM fnd_request_groups a, fnd_request_group_units b, fnd_request_sets c
 WHERE a.request_group_id = b.request_group_id
   AND b.request_unit_id = c.request_set_id
   AND request_group_name = :request_group_name
   AND request_unit_type = 'S';

Query to find out responsibility and request group for concurrent program

Query to find out responsibility and request group for concurrent program: 
SELECT DISTINCT
  FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
  APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
          FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE :conc_prg_name
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';

Concurrent Program with request group details

Concurrent Program with request group details:
 
SELECT   frg.request_group_name
       , fav1.APPLICATION_SHORT_NAME "RequestGroup Appln Shortname"
       , fav1.APPLICATION_NAME "RequestGroup Application Name"
       , fav1.application_id "RequestGroup Application ID"
       , fcp.concurrent_program_name
       , fcpl.user_concurrent_program_name
       , fav.APPLICATION_SHORT_NAME "Con Prog Application Shortname"
       , fav.APPLICATION_NAME "Con Prog Application Name"
       , fav.application_id "Con Prog Application ID"
       , fe.execution_file_name
       , fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpl,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application_vl fav,
       fnd_application_vl fav1
 WHERE frgu.request_unit_id = fcp.concurrent_program_id
   AND fav.application_id=fcp.application_id
   AND fav1.application_id=frgu.application_id
   AND frgu.request_group_id = frg.request_group_id
   AND fe.executable_id = fcp.executable_id
   AND fcp.concurrent_program_name = :conc_prg_name;

Query to list concurrent program details with its parameter, values set and default value/type

Query to list concurrent program details with its parameter, values set and default value/type:

  SELECT fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , fav.APPLICATION_SHORT_NAME
      , fav.APPLICATION_NAME
      , fav.application_id
      , fdfcuv.end_user_column_name
      , fdfcuv.form_left_prompt prompt
      , fdfcuv.enabled_flag
      , fdfcuv.required_flag
      , fdfcuv.display_flag
      , fdfcuv.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , fdfcuv.DEFAULT_VALUE     
 FROM   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl fdfcuv
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
      , fnd_application_vl fav
 WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
 AND    fcpl.user_concurrent_program_name = :conc_prg_name
 AND    fcpl.LANGUAGE = 'US'
 AND    fav.application_id=fcp.application_id
 AND    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
 AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
 AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
 AND    flv.lookup_code(+) = fdfcuv.default_type
 AND    flv.LANGUAGE(+) = USERENV ('LANG');

Query to findout locked objects in Oracle and Kill the session in Oracle

Query for Locked objects in Oracle

SELECT    DO.owner
        , DO.object_name
        , DO.object_type
        , vs.SID
        , vs.serial#
        , vs.status
        , vs.osuser
        , vs.machine
        , vs.program
        , vs.module
        , vs.action
    FROM  v$locked_object vlo
        , v$session vs
        , dba_objects DO
   WHERE
        vs.SID = vlo.session_id
        AND vlo.object_id = DO.object_id
ORDER BY module

How to kill the session in oracle.

Execute the above to get the sid and serial#

alter system kill session 'sid,serial#'
e.g.
altery system kill session '351,13545'

Query to find out concurrent program details and its parameters

Query to find out concurrent program details and its parameters

SELECT fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , fcp.concurrent_program_id
     , fav.APPLICATION_SHORT_NAME
     , fav.APPLICATION_NAME
     , fav.application_id
     , fdfcuv.end_user_column_name
     , fdfcuv.form_left_prompt prompt
     , fdfcuv.enabled_flag
     , fdfcuv.required_flag
     , fdfcuv.display_flag,fdfcuv.SRW_PARAM token,ffvs.FLEX_VALUE_SET_NAME VALUE_SET_NAME
FROM   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl fdfcuv
     , fnd_application_vl fav
     ,fnd_flex_value_sets ffvs
WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
AND    fcpl.user_concurrent_program_name = :conc_prg_name
AND ffvs.FLEX_VALUE_SET_ID = fdfcuv.FLEX_VALUE_SET_ID
AND    fav.application_id=fcp.application_id
AND    fcpl.LANGUAGE = 'US'

AND    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;

Friday, December 24, 2010