xml template query

select * from XDO_DS_DEFINITIONS_B where data_source_code = 'MASNSPMVEN'

select * from XDO_DS_DEFINITIONS_TL where data_source_code = 'MASNSPMVEN' --rownum < 3

select * from XDO_TEMPLATES_B where TEMPLATE_CODE like '%MASCGPRECEIPTRPT%'--'Template Short code'

select * from XDO_TEMPLATES_TL where TEMPLATE_CODE = 'MASCGPRECEIPTRPT'--'Template Short code'

select * from XDO_TEMPLATES_VL where TEMPLATE_CODE like 'MASCGPRECEIPTRPT%'

select * from XDO_LOBS where lob_code like 'MASCGPRECEIPTRPT%'-- 'Template Short code'

concurrent program in sysadmin using backend

select * from fnd_concurrent_programs_vl where user_concurrent_program_name like 'Receipt%Register%Report%'

select * from fnd_application where application_id = 20003

select * from fnd_application_vl where basepath like 'XX%'

select * from fnd_application_vl where basepath like 'Re%'

select * from FND_REQUEST_GROUPs where application_id = 275

select * from fnd_responsibility_vl where responsibility_name like 'NSORO_Project IT Supreme User%'

select * from fnd_request_groups where request_group_id =263

select * from fnd_menus_vl where menu_id = 68262





Declare

BEGIN

 -- Arguments
--   program_short_name  - Short name of the program. (e.g. FNDSCRMT)
--   program_application - Application of the program.
--                         (e.g. 'Application Object Library')
--   request_group       - Name of request group.
--   group_application   - Application of the request group.
--

 /*PROCEDURE add_to_group(program_short_name            IN VARCHAR2,
                         program_application         IN VARCHAR2,
                        request_group                 IN VARCHAR2,
                          group_application             IN VARCHAR2) */
                         
  fnd_program.add_to_group ('MAS_NS_REFRESH_PSI_MV' -- Concurrent Program Short Name
                            ,'NSORO Custom Application'                 -- Application Name
                           ,'All Project Billing Programs'                -- Request Group Name
                           ,'Projects'); -- Request Group Application Name

--  fnd_set.add_set_to_group (request_set       => 'NSTMPRJCRT',
--                            set_application   => 'NSORO Custom Application', --REQUEST SET APPLICATION SHORT NAME
--                            request_group     => 'All Project Billing Programs',---REQUEST GROUP NAME
--                            group_application => 'Projects'--REQUEST GROUP APPLICATION SHORT NAME
--                            );
 COMMIT;

  DBMS_OUTPUT.put_line('Concurrent Program registered successfully');

EXCEPTION

  WHEN OTHERS THEN
 
    DBMS_OUTPUT.put_line('Concurrent Program already exists' || SQLERRM);
 
END;

    Final Coding :
==============

Declare

BEGIN

                         
  fnd_program.add_to_group ('MAS_AR_RCPT_RGTR_RPT' -- Concurrent Program Short Name
                            ,'MASAPP'           -- Application Short Name
                           ,'Receivables All'                -- Request Group Name
                           ,'AR'); -- Request Group Application Short Name
 COMMIT;

  DBMS_OUTPUT.put_line('Concurrent Program registered successfully');

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.put_line('Concurrent Program already exists' || SQLERRM);

END;


=======================================

BEGIN
fnd_program.add_to_group ('MASPAWOHIST'
,' Custom Application Name'
,'All Project Billing Programs'
,'Projects');
COMMIT;
DBMS_OUTPUT.put_line ('Done');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Object already exists' || SQLERRM);
END;

Sql Script to extract Vendor, Project, Receipt information for a PO

SELECT pha.po_header_id, pha.segment1 po_number, pov.vendor_name,
       pov.segment1 vendor_num, pla.line_num po_line_number, pla.item_id,
       (SELECT MAX (segment1)
          FROM mtl_system_items_b
         WHERE inventory_item_id = pla.item_id) item,
       DECODE (NVL (pla.item_id, 0),
               0, NULL,
               pla.item_description
              ) item_description,
       DECODE (pla.cancel_flag,
               'Y', 'CANCELLED',
               DECODE (pha.authorization_status,
                       'IN PROCESS', 'PENDING APPROVAL',
                       NVL (pha.authorization_status, 'INCOMPLETE')
                      )
              ) authorization_status,
       DECODE (pla.cancel_flag,
               'Y', 'N/A',
               DECODE (pha.authorization_status,
                       'APPROVED', DECODE (NVL (pla.closed_code, 'b'),
                                           'CLOSED', 'CLOSED',
                                           'FINALLY CLOSED', 'CLOSED',
                                           'OPEN'
                                          ),
                       'N/A'
                      )
              ) po_status,
       pha.creation_date date_issued, pla.creation_date line_creation_date,
       (pda.quantity_ordered - pda.quantity_cancelled) po_quantity,
       (NVL (pla.unit_price, 0) * NVL (pda.quantity_ordered, 0)) po_line_amt,
       (NVL (pla.unit_price, 0) * NVL (pda.quantity_cancelled, 0)
       ) po_cancelled_amount,
       (NVL (pla.unit_price, 0) * NVL (pda.quantity_delivered, 0)
       ) po_line_received_amount,
       rt.transaction_date receipt_date,
       TO_CHAR (rt.transaction_date, 'MON-RR') receipt_month, rsh.receipt_num,
       (DECODE (NVL (rt.transaction_type, 'a'),
                'RETURN TO VENDOR', NVL (rt.quantity * (-1), 0),
                'RETURN TO RECEIVING', NVL (rt.quantity * (-1), 0),
                NVL (rt.quantity, 0)
               )
       ) receipt_quantity,
       (  NVL (rt.po_unit_price, 0)
        * DECODE (NVL (rt.transaction_type, 'a'),
                  'RETURN TO VENDOR', NVL (rt.quantity * (-1), 0),
                  'RETURN TO RECEIVING', NVL (rt.quantity * (-1), 0),
                  NVL (rt.quantity, 0)
                 )
       ) receipt_amount,
       ai.invoice_id, ai.invoice_num, ai.invoice_amount, ai.amount_paid,
       ai.invoice_date, aid.quantity_invoiced, aid.amount inv_line_amount,
       ppa.project_id, ppa.segment1 project_number, ppa.NAME project_name,
       (SELECT full_name
          FROM pa_project_players_v
         WHERE project_id = ppa.project_id
           AND UPPER (ROLE) = 'PROJECT MANAGER'
           AND NVL (end_date_active, SYSDATE) IN (
                  SELECT MAX (NVL (end_date_active, SYSDATE))
                    FROM pa_project_players_v
                   WHERE 1 = 1
                     AND project_id = ppa.project_id
                     AND UPPER (ROLE) = 'PROJECT MANAGER')
           AND ROWNUM < 2) "Project Manager",
       total_billings.invoice_amount total_actual_billings
  FROM po_headers_all pha,
       po_lines_all pla,
       po_distributions_all pda,
       ap_invoices_all ai,
       ap_invoice_distributions_all aid,
       rcv_transactions rt,
       rcv_shipment_headers rsh,
       po_vendors pov,
       pa_projects_all ppa,
       (SELECT   i.project_id, SUM (NVL (ii.inv_amount, 0)) invoice_amount
            FROM pa_draft_invoices_all i, pa_draft_inv_items_bas ii
           WHERE 1 = 1
             AND ii.project_id(+) = i.project_id
             AND ii.draft_invoice_num(+) = i.draft_invoice_num
             AND i.pa_date < ((SELECT end_date
                                 FROM pa_periods_all ppi
                                WHERE ppi.current_pa_period_flag = 'Y') + 1)
        GROUP BY i.project_id) total_billings
 WHERE pha.po_header_id = pla.po_header_id
   AND pda.po_header_id = pla.po_header_id
   AND pda.po_line_id = pla.po_line_id
   AND pda.po_header_id = pha.po_header_id
   AND pha.vendor_id = pov.vendor_id
   AND pda.po_distribution_id = aid.po_distribution_id(+)
   AND aid.invoice_id = ai.invoice_id(+)
   AND pla.po_line_id = rt.po_line_id(+)
   AND rt.shipment_header_id = rsh.shipment_header_id(+)
   AND rt.destination_type_code(+) = 'RECEIVING'
   AND pda.project_id = ppa.project_id(+)
   AND ppa.project_id = total_billings.project_id(+)

OPP service log in oracle apps

The Concurrent Request ends with Phase 'Completed' and Status 'Warning' which indicates that the Output Post Processor (OPP) failed to generate an output file.

In such cases the request log file shows a generic error message indicating the the post-processing action has failed.

...
+------------- 1) PUBLISH -------------+
Beginning post-processing of request 3181343 on node FINAPPS at 25-OCT-2011 11:41:30.
Post-processing of request 3181343 failed at 25-OCT-2011 11:41:31 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
+--------------------------------------+
...

The actual error returned by the XML Publisher Core engine is captured in the OPP log file.
One of the easiest way to obtain the OPP log file is to run the below script from the database by providing request_id.

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
  FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
 WHERE fcpp.processor_id = fcp.concurrent_process_id
   AND fcpp.action_type = 6
   AND fcpp.concurrent_request_id = &request_id;

Output of the script contains logfile location just like below
/u01/app/inst/apps/NZAPPS/logs/appl/conc/log/FNDOPP10981694.txt

Developer/Administrator/DBA has to go to that location and take the OPP logfile

Alternate Method:


Getting OPP Log from the application itself
a. System Administrator > Concurrent > Manager > Administer
b. Search for 'Output Post Processor'
c. Click the 'Processes' button
d. Click the Manager Log button. This will open the 'OPP'
e. Upload the OPP log file.

check OOP file as follows: 

sysadmin -> Concurrent -> Manager -> administer -> Output Post Processor -> processes -> manager log

or better way is from find request window , choose concurrent program -> Diagnostics -> view XML 

Check the xml file in the end for error.

 

PO Receipt Tax Details in R12

SELECT DISTINCT hou.NAME operating_unit, TO_NUMBER (poh.segment1) po_no,
                (rsh.receipt_num) receipt_no, pla.line_num,
                pov1.vendor_name fright_vendor, site.vendor_site_code,
                (SELECT ood.organization_code
                   FROM org_organization_definitions ood
                  WHERE ood.organization_id =
                                        rsh.organization_id
                    AND ood.operating_unit = hou.organization_id)
                                                            organization_code,
                (SELECT ood.organization_name
                   FROM org_organization_definitions ood
                  WHERE ood.organization_id =
                                        rsh.organization_id
                    AND ood.operating_unit = hou.organization_id)
                                                            organization_name,
                rsl.to_subinventory sub_inventory, jir.tax_line_no,
                jir.tax_name, jir.tax_rate, jir.tax_type, jir.precedence_1,
                jir.precedence_2, jir.precedence_3, jir.precedence_4,
                jir.precedence_5, jir.currency, jir.modvat_flag,
                jir.third_party_flag, jir.tax_amount
           FROM jai_rcv_line_taxes jir,
                rcv_shipment_lines rsl,
                po_vendors pov,
                po_vendors pov1,
                po_vendor_sites_all site,
                po_headers_all poh,
                po_lines_all pla,
                rcv_shipment_headers rsh,
                hr_operating_units hou   --,--org_organization_definitions ood
          WHERE jir.shipment_line_id = jir.shipment_line_id
            AND jir.shipment_header_id = rsl.shipment_header_id
            AND pov.vendor_id = jir.vendor_id
            AND site.vendor_site_id(+) = jir.vendor_site_id
            AND poh.po_header_id = rsl.po_header_id
            AND poh.po_header_id = pla.po_header_id
            AND rsl.po_line_id = pla.po_line_id
            AND jir.vendor_id = pov1.vendor_id
            AND rsh.shipment_header_id =
                    jir.shipment_header_id
            AND rsh.RECEIPT_NUM ='3'
                   

personilzation document

http://wenku.baidu.com/view/5aa75bfcc8d376eeaeaa3196.html

ORACLE PAYABLE SETUPS

 ORACLE PAYABLE SETUPS:-

http://www.apps2fusion.com/training_demo/ranu/oracle-ap/oracle_payables.pdf

reprte NLS_NUMERIC_CHARACTERS error

+---------------------------------------------------------------------------+
NSORO Custom Application: Version : UNKNOWN

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

MASNSPMVEN module: Nsoro List of Project Managers By Vendors Report
+---------------------------------------------------------------------------+

Current system time is 23-APR-2012 02:18:15

+---------------------------------------------------------------------------+


+-----------------------------
| Starting concurrent program execution...
+-----------------------------

Arguments
------------
P_MANAGER_NAMR='AARON'
------------

Environment will now switch to UTF-8 code-set.
Parts of this log file may not display correctly
as a result.  This is an expected behavior.     

 XML_REPORTS_XENVIRONMENT is : 
/u01/nsd01/nsd01ora/8.0.6/guicommon6/tk60/admin/Tk2Motif_UTF8.rgb

 XENVIRONMENT is set to  /u01/nsd01/nsd01ora/8.0.6/guicommon6/tk60/admin/Tk2Motif_UTF8.rgb


 Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
American_America.UTF8

'.,'


Report Builder: Release 6.0.8.28.0 - Production on Mon Apr 23 02:18:15 2012

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Enter Username: 
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+


+---------------------------------------------------------------------------+
Executing request completion options...

+------------- 1) PUBLISH -------------+
Beginning post-processing of request 6361621 on node NSD01 at 23-APR-2012 02:18:24.
Post-processing of request 6361621 failed at 23-APR-2012 02:18:24 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
+--------------------------------------+

+------------- 2) PRINT   -------------+
Not printing the output of this request because post-processing failed.
+--------------------------------------+


Finished executing request completion options.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 23-APR-2012 02:18:24

+---------------------------------------------------------------------------+

 =========================

Solution

======================

Applies to:

BI Publisher (formerly XML Publisher) - Version: 9.2.0.8 - Release: 9.2
Information in this document applies to any platform.

Symptoms

Using XML Publisher to generate a custom report.
You created an RDF report, configured it as a concurrent program,
changed output to XML and created a .rtf template in XML Publisher in order to have output in .pdf
When run, no output is generated.
The OPP log contains errors Caused by: java.io.UnsupportedEncodingException: &Encoding

Cause

Incorrect encoding value in the report's prolog

Solution

To resolve the issue:

1.Open the custom report in the Report Builder

2.Go to the property pallette of the Report

3.Remove the xml prologue value

4.Save the changes

5.Retest the issue.

xml forest

Description

The XMLFOREST function returns the values of each expression tagged with its own XML (or HTML) tag.

XMLFOREST can be used in a SELECT query or subquery that references either a table or a view. XMLFOREST can appear in a SELECT list alongside ordinary column values.

The specified expression value is returned enclosed by a start tag and an end tag, as shown in the following format:

<tag>value</tag>

Commonly, expression is the name of a column, or an expression containing one or more column names. XMLFOREST tags each expression as follows:

•  If AS tag is specified, XMLFOREST tags the resulting values with the specified tag. The tag value is case-sensitive.
•  If AS tag is omitted, and expression is a column name, XMLFOREST tags the resulting values with the column name. Column name default tags are always uppercase.
•  If AS tag is omitted, and expression is not a column name (for example, an aggregate function, a literal, or a concatenation of two columns)XMLFOREST tags the resulting values with a blank tag. For example: <>literal string</>

XMLFOREST provides a separate tag for each item in a comma-separated list. XMLELEMENT concatenates all of the items in a comma-separated list within a single tag.

XMLFOREST functions can be nested. Any combination of nested XMLFOREST and XMLELEMENT functions is permitted. XMLFOREST functions can be concatenated using XMLCONCAT.

NULL Values

The XMLFOREST function only returns a tag for actual data values. It does not return a tag when the expression value is NULL. The empty string ('') is considered a data value. If the value to be tagged is the empty string (''), XMLFOREST returns:

<tag></tag>

XMLFOREST differs from XMLELEMENT in the handling of NULL. XMLELEMENT always returns a tag value, even when the field value is NULL.

Punctuation Character Values

If a data value contains a punctuation character that XML/HTML might interpret as a tag or other coding, XMLFOREST and XMLELEMENT convert this character to the corresponding encoded form:

ampersand (&) becomes &amp;

apostrophe (') becomes &apos;

quotation mark (") becomes &quot;

open angle bracket (<) becomes &lt;

close angle bracket (>) becomes &gt;

To represent an apostrophe in a supplied text string, specify two apostrophes, as in the following example: 'can''t'. Doubling apostrophes is not necessary for column data.

Examples

The following query returns the Name column values in Sample.Person as ordinary data and as xml tagged data:

SELECT Name,XMLFOREST(Name) AS ExportName
     FROM Sample.Person

A sample row of the data returned would appear as follows. Here the tag defaults to the name of the column:

Name                    ExportName
Emerson,Molly N.   <NAME>Emerson,Molly N.</NAME>

The following example specifies multiple columns:

SELECT XMLFOREST(Home_City,
                 Home_State AS Home_State,
                 AVG(Age) AS AvAge) AS ExportData
FROM Sample.Person

The Home_City field specifies no tag; the tag is generated from the column name in all capital letters: <HOME_CITY>. The Home_State field's AS clause is optional. It is specified here because specifying the tag name allows you to control the case of the tag: <Home_State>, rather than <HOME_STATE>. The AVG(Age) AS clause is mandatory, because the value is an aggregate, not a column value, and thus has no column name. A sample row of the data returned would appear as follows.

ExportData
<HOME_CITY>Chicago</HOME_CITY><Home_State>IL</Home_State><AvAge>48.0198019801980198</AvAge>

RDF

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $P_APPS_PWD \
-JDBC_CONNECTION $JDBC \
-LOB_TYPE TEMPLATE_SOURCE \
-APPS_SHORT_NAME XXFIN \
-LOB_CODE XXFIN_PSBE6_1_TEMPLATE_PDF \
-LANGUAGE en \
-TERRITORY CA \
-XDO_FILE_TYPE RTF \
-NLS_LANG American_America.WE8ISO8859P1 \
-FILE_CONTENT_TYPE 'application/rtf' \
-FILE_NAME $XXFIN_TOP/admin/import/TEMPLATE_SOURCE_XXFIN_XXFIN_PSBE6_1_TEMPLATE_PDF_en_CA.rtf \
-CUSTOM_MODE FORCE \
-LOG_FILE XXFIN_PSB_EXP_ANN_SUM_UP_TMP.log

XLS-FO

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION nsd02.mastec.com:1522:NSD01 \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME XXNS \
-LOB_CODE XXNS_PO_CUSTOM_XSLFO \
-LANGUAGE en \
-TERRITORY US \
-XDO_FILE_TYPE XSL-FO \
-FILE_CONTENT_TYPE 'application/xls' \
-FILE_NAME XXNS_PO_CUSTOM_XSLFO.xsl \
-NLS_LANG ENGLISH_UNITED STATES.WE8ISO8859P1


-CUSTOM_MODE FORCE \
-LOG_FILE XXNS_PO_CUSTOM_XSLFO.log

     Change The Condition :-
--------------------------------
-LOB_TYPE TEMPLATE \
...and...
-FILE_CONTENT_TYPE 'text/xml' \

SRW Message in oracle report

function BeforeReport return boolean is
begin
  SRW.user_exit('FND SRWINIT');
  return (TRUE);
end;

function AfterReport return boolean is
begin
  srw.user_exit( 'FND SRWEXIT' );
    RETURN (TRUE);
  --return (TRUE);
end;

P_CONC_REQUEST_ID

 RRRR/MM/DD HH24:MI:SS

java rtf script

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION nsd02.mastec.com:1522:NSD01 \
-LOB_TYPE TEMPLATE_SOURCE \
-APPS_SHORT_NAME XXNS \
-LOB_CODE MASNSISSUEPOREP \
-LANGUAGE en \
-TERRITORY US \
-XDO_FILE_TYPE RTF \
-FILE_CONTENT_TYPE 'application/rtf' \
-FILE_NAME MASNSISSUEPOREP_en_US.rtf \
-NLS_LANG ENGLISH_UNITED STATES.WE8ISO8859P1

Query to find the Menus

SELECT
  B.ROWID ROW_ID,
  B.MENU_ID,
  B.MENU_NAME,
  B.TYPE,
  B.LAST_UPDATE_DATE,
  B.LAST_UPDATED_BY ,
  B.LAST_UPDATE_LOGIN ,
  B.CREATION_DATE ,
  B.CREATED_BY ,
  T.USER_MENU_NAME ,
  T.DESCRIPTION,
  T.LANGUAGE,
  C.SUB_MENU_ID
 FROM
  apps.FND_MENUS_TL T,
  apps.FND_MENUS B,
  apps.FND_MENU_ENTRIES C
 -- (SELECT USER_MENU_NAME,MENU_ID FROM FND_MENUS_TL)SUB
WHERE
  B.MENU_ID = T.MENU_ID
  AND T.LANGUAGE = USERENV('LANG')
  --AND T.USER_MENU_NAME = 'Activity Based Management'
  AND B.MENU_ID=C.MENU_ID

Query to find the Descriptive Field information for PO and GL

select flex_vl.title,flex.descriptive_flexfield_name,
 context_vl.descriptive_flex_context_code,context_vl.description,
 col_usage_vl.application_column_name, col_usage_vl.end_user_column_name
from apps.FND_DESCRIPTIVE_FLEXS flex,
apps.FND_DESCRIPTIVE_FLEXS_vl flex_vl
,apps.FND_DESCR_FLEX_CONTEXTS_VL context_vl
,apps.FND_DESCR_FLEX_COL_USAGE_VL col_usage_vl
where flex_vl.application_id in (201,101)
and flex_vl.title = 'Approved Supplier List'
and  flex.descriptive_flexfield_name = flex_vl.descriptive_flexfield_name
--'PO_APPROVED_SUPPLIER_LIST'
and  flex.descriptive_flexfield_name = context_vl.descriptive_flexfield_name
and  flex.descriptive_flexfield_name = col_usage_vl.descriptive_flexfield_name
and  col_usage_vl.descriptive_flex_context_code = context_vl.descriptive_flex_context_code
order by col_usage_vl.descriptive_flex_context_code,col_usage_vl.application_column_name

Query to find the Responsibilites

SELECT B.RESPONSIBILITY_ID,
                 A.RESPONSIBILITY_NAME,
                 A.LANGUAGE,
                 B.RESPONSIBILITY_KEY ,
                 B.APPLICATION_ID
  FROM APPS.FND_RESPONSIBILITY_TL A,
       APPS.FND_RESPONSIBILITY B
  WHERE A.RESPONSIBILITY_ID(+)=B.RESPONSIBILITY_ID
  AND A.LANGUAGE = USERENV('LANG')
  ORDER BY A.RESPONSIBILITY_NAME

Query to Find the REQUEST_GROUP

select * from fnd_responsibility WHERE APPLICATION_ID =101 AND RESPONSIBILITY_ID = 20479 --REQUEST_GROUP_ID

select * from fnd_responsibility_tl WHERE APPLICATION_ID =101 AND RESPONSIBILITY_ID = 20479

select * from fnd_request_groups where REQUEST_GROUP_ID =111
 

Query to find the Flexfields and valuesets

  
     SELECT       A.ID_FLEX_STRUCTURE_CODE,
                   B.ID_FLEX_CODE,
                   E.LANGUAGE,
                   D.FLEX_VALUE,
                   E.DESCRIPTION,
                   E.FLEX_VALUE_MEANING,
                   B.FLEX_VALUE_SET_ID,
                   B.APPLICATION_COLUMN_NAME,
                   B.SEGMENT_NAME,
                   C.FLEX_VALUE_SET_NAME,
                   D.FLEX_VALUE_ID                
        FROM     APPS.fnd_id_flex_structures A,
                 APPS.fnd_id_flex_segments B,
                 APPS.fnd_flex_value_sets C,
                 APPS.fnd_flex_values D,     
                 APPS.fnd_flex_values_tl E      
        WHERE  A.ID_FLEX_NUM=B.ID_FLEX_NUM
         AND   B.FLEX_VALUE_SET_ID=C.FLEX_VALUE_SET_ID
         AND   C.FLEX_VALUE_SET_ID=D.FLEX_VALUE_SET_ID
         AND   D.FLEX_VALUE_ID=E.FLEX_VALUE_ID
         AND   A.APPLICATION_ID IN (101,201)
         AND   E.LANGUAGE='US'
         ORDER BY A.ID_FLEX_STRUCTURE_CODE

Procure to Pay Frequently Asked Questions

Procure to Pay Frequently Asked Questions

• General
• Understanding Terms
• Creating and Modifying a Requisition
• Approving a Requisition
• Notifications
• Purchasing Procedures
• Shipments and Deliveries
• Invoice Processing
• Quick Orders
• Check Requests
• Internal Purchase Orders (IPOs)
• Travel and Business Expense Reports, Travel Advances and Travel Order Forms
  
  

General

1. Who can be a Reviewer of a requisition?
   
   A reviewer is an individual outside the preparer's direct requisition approval hierarchy to whom a requisition has been manually forwarded for review. A reviewer can be within the same organization as the preparer or an outside organization. A reviewer does not have final approval authority for that requisition. Instead, he/she will be recorded in the approval history with his/her review comments only.
   Back to Top
   
2. Can I add Rutgers Exchange items to My Favorites List?
   
   You cannot save RU Exchange items to My Favorites list in the Internet Procurement Application. However, you can save your Rutgers Exchange "favorites" on the website of each Rutgers Exchange Supplier. Also, you may wish to save multi-line item orders in the Exchange Supplier's shopping cart before you exit the website in the event you do not properly return to the Internet Procurement Application.
   Back to Top
   
3. When will I use the Internet Procurement Application?
   
   The Internet Procurement Application will be used to:
   
   
   • Create or approve a purchase requisition and a quick purchase order for an external supplier for up to $5,000
   • Create or approve a purchase requisition for an external supplier
   • Create or approve a check request for a payment to supplier or other type of payee
   • Create or approve a requisition and release order for internal suppliers. A listing of these IPO Suppliers can be found on the RIAS website.
   • Record the receipt of goods for purchase order line items over $5,000.
   Back to Top
   
4. Is there any one screen that contains all the details of a requisition?
   
   The View Requisition Details screen provides all the details of a submitted requisition.
   Back to Top
   
5. How will I know what was on the purchase order that was sent to the Supplier?
   
   Upon departmental approval, preparers will receive original Quick Purchase Orders for issuance to suppliers via an email attachment. This copy must be mailed, faxed or emailed to the supplier, to receive the goods or services.
   Back to Top
   
6. Where do I get a Change Order Request Form?
   
   The Change Order Request form can be found on the RIAS Forms page and downloaded onto your desktop.
   Back to Top
   
7. My department prepares thousands of requisitions. How do I manage so many requisition files so that they can be retrieved quickly?
   
   Select the Requisitions tab. You can use the search function to query a requisition by the preparer's name, requester's name, requisition number, creation date, status of the requisition and/or supplier.
   Back to Top
   
8. How do I add or delete Preparers or Approvers, or initiate other changes in the requisition approval hierarchy?
   
   Departments will need to complete the RIAS Access Request form to initiate changes affecting an individual who is named in the requisition approval hierarchy for their organization.
   Back to Top
   

Understanding Terms

1. What is a Notification?
A system-generated message informing the recipient of the status of a transaction or that an action is required on a transaction. Back to Top
2. What is a Supplier Site?
   
   A supplier site is the specific location of a supplier. The naming convention of supplier site is the name of the town/city the supplier is located, followed by a dash, then a number representing the number of the supplier's locations in that particular town/city. (i.e. Piscataway-01, Piscataway-02). A supplier may have one or more supplier sites.
   Back to Top
   
3. What is a 3-Way match?
   
   A 3-Way match is an automated process of verifying that the information contained in the Purchase Order, Receipt of Goods or Services, and Supplier Invoice matches within accepted tolerance levels.
   Back to Top
   
4. What is a Requisition Number?
   
   A requisition number is a system-generated number assigned to the requisition when a preparer creates a requisition.
   Back to Top
   
5. What is the difference between a Sole Source purchase and a Preferred or Single Source purchase?
   
   A Sole Source purchase occurs when the goods or services required are so unique that competitive sources are not available. A Preferred or Single Source purchase occurs when goods or services are requested from a particular vendor for an appropriate business purpose. For example, to augment work on a project completed by a vendor in a previous budget period.
   Back to Top
   
6. What is a Purchase Order Number?
   
   A system-generated number assigned to the purchase order when approved by the Purchasing Department. In the case of quick purchase orders and requisitions to Rutgers Exchange vendors, the purchase order number is generated by the system when the department approves the requisition.
   Back to Top
   
7. What is a Rutgers Exchange Supplier?
   
   A Rutgers Exchange supplier is a supplier that offers products through electronic catalogs at pre-negotiated prices with the university.
   Back to Top
   
8. What are the benefits of using Rutgers Exchange Suppliers?
   
   You can select items from electronic catalogs to include in your shopping cart at pre-negotiated prices by a click of the mouse. Also, upon departmental approval, purchase requisitions to Rutgers Exchange suppliers will be quickly and automatically converted into purchase orders without buyer involvement.
   Back to Top
   
9. What is the Oracle Exchange?
   
   The Oracle Exchange is an electronic catalog of goods and services offered by a wide range of suppliers. The Oracle Exchange catalog does not reflect pre-negotiated prices with the University.
   Back to Top
   
10. What is the difference between the Rutgers Exchange and Oracle Exchange?
    
    A Rutgers Exchange supplier offers products through an electronic catalog maintained on its own website at pre-negotiated prices with the university. An Oracle Exchange supplier offers products through the Oracle Exchange electronic catalog, but not at pre-negotiated prices with the university.
    Back to Top
    
11. What does the status pre-approved mean?
    
    Pre-Approved is the status of a requisition when an authorized approver has approved the requisition and has forwarded the requisition to another individual to review.
    Back to Top
    
12. What does the Approver Checkout button mean?
    
    When an "approver" or "reviewer" edits a requisition, clicking the Approver Checkout button will start them through the editing requisition process and will take them through all the data completed on the requisition.
    Back to Top
    
13. What is the difference between a rejected and a returned requisition?
    
    Only an approver or a reviewer can reject requisition. Only a buyer can return a requisition. A buyer will return a requisition to the preparer for incomplete or inaccurate information.
    Back to Top
    
14. In the list of values contained on the Requisition Status page, what is meant by "My Group's Requisitions"?
    
    "My Group's Requisitions", displays all requisitions created in your organization.
    Back to Top
    
15. What is an Organization ID number and how do I find what my department's number?
    
    Each organizational unit within the university has been assigned an Organization Identification (ORG ID) number which issued in conjunction with general ledger account numbers to code and identify purchasing and payment transactions.
    Back to Top
    
16. What is the Requisition Approval Hierarchy?
    
    A structure that defines the automatic routing of requisitions from a preparer to an approver.
    Back to Top
    
17. Is the Requisition Number the same as the Purchase Order Number?
    
    No. Requisition numbers will be system-generated during the "Review and Submit" step when creating a requisition. Once the requisition is approved and turned into a purchase order, the system will generate another number for the purchase order. The numbers are cross-reference in the system, so if you know one number, such as the requisition number, you can find its corresponding purchase order number.
    Back to Top
    

Creating and Modifying a Requisition

1. How do I prepare a requisition?
   
   Refer to the Procure to Pay User Guide for complete instructions.
   Back to Top
   
2. How do I prepare a requisition that will be funded by more than one university department? Does the requisition require approval from both approvers?
   
   If the items on a requisition will be split funded among multiple organizations, the preparers must insert the names of the authorized approvers from each organization. The departmental approvers must be inserted to review the requisition before the default approver (Creighton Pfeifer). Do not remove the default approver.
   Back to Top
   
3. Can a Preparer withdraw a requisition that has been submitted for approval?
   
   Yes, as long as the status of the requisition is "In Process". Additionally, approved requisitions can be withdrawn as long as a purchase number has not automatically been generated or one has not been created by a buyer within the Purchasing Department.
   Back to Top
   
4. How do I make a correction on the requisition after I have submitted it for approval?
   
   It depends upon the status of the requisition. If the requisition status is "In Process", the preparer of the requisition can withdraw the requisition, make any required changes and resubmit for approval. Also, the withdraw function can be used to recall and re-route an "In Process" requisition to the backup approver if the primary approver is unexpectedly unavailable.
   
   If the status is "Approved" and it was not a Quick Order or Check Request, you can contact the buyer in Purchasing to stop the order before it is issued to the supplier. The buyer will return the requisition to you to make your changes.
   
   If the status is "Approved" and it was a Quick Order, you must complete a Change Order Request form to cancel the Quick Order.
   
   If the status is "Approved" and it was a Check Request, you must contact the Disbursement Control to cancel the check.
   Back to Top
   
5. How will my Approver know what type of requisition (non-catalog, Rutgers Exchange, Quick Order or Check Request) I am submitting for approval?
   
   The approver can use the View Requisition Details screen to get this information. The View Requisition Details screen also provides information about the charging instruction for a requisition.
   Back to Top
   
6. If my Approver rejects my requisition, is it considered cancelled?
   
   No. The requisition must be cancelled by the preparer to remove the commitment in the general ledger.
   Back to Top
   
7. Who can edit a requisition and what fields can they edit?
   
   A preparer can make any changes to a requisition using the Edit Lines functionality. An approver and/or reviewer can edit any fields on a requisition prior to approving it by clicking the Edit Requisition link.
   Back to Top
   
8. What is the purpose of choosing a Category when creating a requisition?
   
   The Category box is a required field and is used to define goods and services at a summary or detailed level.
   Back to Top
   
9. What happens if I lose power while I am creating a requisition?
   
   If you have not saved your requisition, you may lose the information you entered.
   Back to Top
   
10. How long can the Shopping Carts be saved?
    
    The contents of a shopping cart will remain saved until you complete the creation of a requisition.
    Back to Top
    
11. How do you save attachments in the Internet Procurement application?
    
    When you add an attachment to your requisition, select the save icon in the toolbar and ensure that you saved the attachment with the appropriate extension (.xls for Excel, .doc for Word, .pdf for Adobe Acrobat or .vsd for Visio files) so that the attachment can be opened by the Purchasing Department.
    Back to Top
    
12. Will the information entered in the requisition description field be reflected under requisitions at a glance?
    
    Yes, any text in the requisition description field will be reflected under requisitions at a glance.
    Back to Top
    
13. How do I prepare a requisition to engage an independent contractor to perform services?
    
    A requisition must be prepared in the Internet Procurement Application with a completed Request for Consulting or Independent Personal Services Form (RCIPS) attached ("To Buyer"). The buyer will review the RCIPS to determine if the worker is properly classified as an independent contractor. Upon receipt of the signed agreement, the buyer will issue either a regular purchase order to the individual or classify the work as an employee and return the requisition to the preparer.
    Back to Top
    
14. Will I be able to approve requisitions that I prepare?
    
    No. The system will automatically route the requisition from the preparer to the next individual in the requisition approval hierarchy who is authorized for that dollar amount and organization. If you are a Level 2 approver who prepares a requisition, you must change the default approver to a Peer Approver prior to submitting the requisition.
    Back to Top
    
15. Can I forward my requisition to any person in the list of values?
    
    Not necessarily. If the approver takes the action of Forward or Approve and Forward, and selects an individual who is not in the hierarchy, the approver will receive a notification that the requisition was sent to an invalid person. The approver must resubmit the requisition for approval.
    Back to Top
    
16. What happens if I forget to enter my Fund Source, Natural Account and Organization ID (ORG ID)?
    
    The requisition will automatically be routed to Purchasing. Purchasing will return the requisition to the preparer for correction to the proper charging instructions.
    Back to Top
    
17. Do I have to insert my Fund Source, Natural Account and Organization ID (ORG ID) each time I create a requisition?
    
    No, you can store pre-defined account distributions in Preferences and select them to populate the required fields.
    Back to Top
    
18. Will I be able to charge someone else's account for inter-disciplinary purchases?
    
    Yes, provided you are given their account number and organizational identification number. The preparer must select Add Approver to add the authorized approver for the designated account and organization for inter-disciplinary purchases.
    Back to Top
    
19. How will I know which Buyer received my requisition?
    
    The buyer's name will be reflected on the Requisition Detail Screen.
    Back to Top
    
20. What fields will print on the purchase order?
    
    The following fields will be printed on a purchase order: item description, unit of measure, quantity, unit price and total, supplier name and address, need by date, requestor name, deliver-to location and notes to supplier.
    Back to Top
    
21. Are there shortcuts to reorder frequently requested items?
    
    Some shortcuts you can use are:
    
    • Non-Catalog Request - Prior to adding the item to your Shopping Cart, you can also add it to your Favorites List. This will allow you to select the item again at a later date. To add multiple items to your shopping cart, check the select box by each item you want and click the Add to Cart button.
    • Internal Suppliers - You can add frequently used internal suppliers to your Favorites List prior to adding items to your Shopping Cart for Internal Purchase Orders. This will allow to select them again at a later date.
    • Copy to Cart - (From "My Requisitions" area) is a quick way to create a new shopping cart. Select a requisition, open it, and click "Copy to Cart" or click on the copy icon in the toolbar. Then make changes to any field in the new shopping cart and proceed through the three steps of creating a requisition. Any attachments, notes, reviewers or special item information from the original requisition will not be copied to the new shopping cart. If this information is needed, it must be added. When adding one of these notes/attachments, select "To Approver" or "To Buyer".
    Back to Top
    

Approving a Requisition

1. How many people will need to approve a requisition?
   
   Requisitions require only one approval.
   Back to Top
   
2. Will the system automatically route requisitions to approvers by fund sources (e.g., state accounts to one approver and grant accounts to another approver)?
   
   No. The requisition hierarchy is based upon organizational unit, not fund source. Level 1 approvers can approve all requisitions up to $10,000, regardless of fund source. Level 2 approvers can approve all requisitions up to the departmental budget, regardless of fund source.
   Back to Top
   
3. Will the system automatically check for funds availability?
   
   No, the system will NOT automatically check for funds availability. The approver is responsible for checking the general ledger, to determine if there are sufficient funds to cover the expenditure during the budget or project period.
   Back to Top
   
4. I am a level 2 approver and creating a requisition. Who will approve my requisition?
   
   As a level 2 approver, you need to manually change the default approver by clicking the Change First Approver link to a "peer" approver. If you do not change the default approver, the requisition will be routed to the Purchasing Department. The Purchasing Department will reject the requisition with a note for you to change the approver's name.
   Back to Top
   
5. What happens when an Approver selects the action of Approve and Forward?
   
   If an approver approves and forwards a requisition, the status of the requisition is pre-approved. The reviewer who the requisition is forwarded to can change any information on the requisition. Unless the reviewer indicates the changes he/she has made in the notes section of the approval page, the approver will not know the requisition has been altered.
   
   If a Level 1 approver selects the Approve and Forward function and the reviewer changes the dollar amount to over the Level 1 approval authority, the requisition will be routed to the approver Level 2 to approve changes made.
   Back to Top
   
6. Can an Approver change the specified Requester of the requisition?
   
   Yes, provided the individual is in the list of values.
   Back to Top
   
7. Can an Approver add a Reviewer to a requisition?
   
   If an approver requires additional information from someone in the hierarchy prior to approving it, the approver would use the Forward function to route it to an added reviewer.
   Back to Top
   
8. What happens to a requisition if the Preparer enters charging instructions for an account outside his/her own organizational unit? Does it go directly to his/her Approver or to the Approver for the other organization?
   
   If a preparer is authorized to prepare a requisition with a fund source outside his/her organizational unit, the preparer should add the authorized approver for that organization to the requisition to ensure proper routing and approval.
   Back to Top
   
9. What happens if my Approver does not take an action on a requisition that has been routed to him/her?
   
   If an approver does not take an action on a requisition within a day of receiving the requisition, the approver will receive a system generated reminder notification. If the approver does not take action on the second day, the approver will receive a second reminder notification. If the approver does not take action by the third day, the preparer will receive a notification that the approver has not taken any action on the requisition. The preparer can either withdraw the requisition and either cancel the requisition or manually change the approver to the Back Up Approver and resubmit the requisition.
   
   
10. How can I get approval for my requisition when my Approver is on vacation?
    
    If the approver has an expected absence (e.g., vacation), the approver should re-assign his/her requisitions to automatically route to a Back Up Approver for a specified amount of time. If the approver has an unexpected absence (e.g., out sick) and the preparer is aware of their absence, the preparer can manually forward the requisition to the Back Up Approver.