Thursday, May 31, 2012

Setup & Instructions to Run - PYUPIP HR Trace in the Applications

Metalink Note: 301343.1




---------------------------------------------------------------------------



STEP ONE - Setting up PYUPIP and HR Trace to run within the applications

---------------------------------------------------------------------------

1a. Check to see if HR TRACE is already setup as an option off of the Toolbar.

Navigate from within your HR responsibility to the screen which is throwing your error.

Once in the screen ... go to ...

Tools> HR Trace

If the 'HR Trace' function is NOT there, continue with steps 1b - 1c

else, you are done with 'STEP ONE'



1b.Create a function with the name of HR_DEBUG_TOOLS

As System Administrator responsibility navigate to

Application > Function

from the 'Description' Tab :

Function = HR_DEBUG_TOOLS

User Function Name = HR_DEBUG_TOOLS

Description = PYUPIP function

from the 'Properties' Tab :

Type = SUBFUNCTION

Maintenance Mode Support = None

Context Dependence = Responsibility



Add nothing to the other Tabs: ie Form, Web HTML, Web Host, Region

save your changes



1c.Add the 'HR_DEBUG_TOOLS' function to the navigation menu.

First find the defined Menu associated with the responsibility that the user

is using by navigating through the following steps:

As System Administrator responsibility navigate to

Security > Responsibility > Define > query the users responsibility

In the middle left of the Responsibilities form, make a note of the MENU being

called by this responsibility.



Once you know the Menu associated with the responsibility, then,

as System Administrator responsibility navigate to

Application > Menu



Query the menu that is tied to the responsibility.

Go to the last Seq (sequence) and add a new Seq (sequence) number.

Make sure you make the new Seq number much larger than the last Seq number

as HR Trace ( function HR_DEBUG_TOOLS ) should always be the last Seq number

for the menu.



Seq = your last Seq value + 200 (this should be a large enough gap)

Prompt = HR Trace

Function = HR_DEBUG_TOOLS

Description = PYUPIP



save your changes



---------------------------------------------------------------------------

STEP TWO - Running HR Trace / Trace File from within the applications

---------------------------------------------------------------------------

2a.Change responsibilities and navigate to screen where error occurs ...

and before making 'any' changes to the data ... go to ...



Tools > HR Trace > and select the following options:



HR Trace = Trace File

PL/SQL Trace = None

Bind Variable Trace = yes ( check the 'Bind Variable Trace' box )

click OK



2b.

Perform the action on the screen that throws the error, answer OK to the error,

and then stop



2c.

Go to ...

Tools > HR Trace >

click on 'Write Info' button and make note of the trace file name



2d.

The trace file should have been written to the user_dump_dest location

defined in your database.

To find your user_dump_dest location run the following script in sqlplus.



set pagesize 1000

col name for a30

col value for a42

select name, value

from v$parameter

where UPPER(name) = UPPER('user_dump_dest')

order by name;



Go to user_dump_dest location, pull the trace file

and upload the PYUPIP trace file to the tar in metalink.















The step prior to the error thrown in this trace should be the package being run

when the error occured.

The output you read will be in the format of the 'proper name' of the package.

To find the 'sql' name for the package run the following script.



Example:

If the 'proper name' of the package read from the PYUPIP trace is

' per_accrual_calc_functions.Get_Carry_Over '

then you need to know the 'sql' name for the package 'per_accrual_calc_functions'.

To find the 'sql' name, run the following script from sqlplus.



When prompted, enter the 'proper name' of the package, in this case 'per_accrual_calc_functions'.



set pagesize 1000

select name, text

from all_source

where UPPER(name) like UPPER('%&NAME%')

and owner = 'APPS'

and text like '%$Header%'

order by name;



( example of script use )

SQL> set pagesize 1000

SQL> select name, text

2 from all_source

3 where UPPER(name) like UPPER('%&NAME%')

4 and owner = 'APPS'

5 and text like '%$Header%'

6 order by name;

Enter value for name: per_accrual_calc_functions

old 3: where UPPER(name) like UPPER('%&NAME%')

new 3: where UPPER(name) like UPPER('%per_accrual_calc_functions%')

NAME

------------------------------

TEXT

----------------------------------------------------------------------

PER_ACCRUAL_CALC_FUNCTIONS

/* $Header: peaclcal.pkh 115.10 2003/01/28 16:14:05 ptitoren ship $ */

PER_ACCRUAL_CALC_FUNCTIONS



/* $Header: peaclcal.pkb 115.35 2004/02/22 23:42:08 kjagadee ship $ */



Hence, the package body throwing the error would have been peaclcal.pkb

and we would want to run a PYUPIP trace against package peaclcal.pkb

to see what part of the code was throwing the error.

--------------------------------------------------------------------------

STEP THREE - Running PYUPIP against a package

---------------------------------------------------------------------------

NOTE:

The following 'cannot be done' from the same machine and requires 2 separate desktops/laptops,

with one machine executing the PYUPIP from $PAY_TOP

and the other machine performing the application steps.



3a. From UNIX - cd to where the package that is being executed is stored.



Such as the PTO Carry Over Process - $PAY_TOP/patch/110/sql>pyusptoa.pkb

Make a back up of the package using the 'cp' Unix command



example:

cp pyusptoa.pkb pyusptoa.old



3b. After the first BEGIN statement add:

hr_utility.trace_on (null, 'enter a name')



Example

hr_utility.trace_on (null, 'ORACLE')



Compile and save the package

To compile the package, execute the package from sqlplus and commit such as



SQL> @pyusptoa.pkb

SQL> commit;



3c. From UNIX - $PAY_TOP/bin (this is where the PYUPIP utility is stored) run PYUPIP



To run PYUPIP from Unix execute the following string



PYUPIP >



Example

PYUPIP apps/apps ORACLE > PTOPYUPIP















3d. Execute whatever process that runs your package either from the application

or from sqlplus.



Watch the unix screen and you will see the package being executed.



NOTE:

Do not stop the PYUPIP utility from unix until the build completes.



3e. Be sure to turn PYUPIP off after you complete your trace OR

it will bring your database to its knees.



To turn off PYUPIP from sqlplus run the following



SQL> execute hr_utility.trace_off



3f. 'ctrl C' from the Unix prompt to kill your PYUPIP session



3g. Delete the modified PYUPIP package, rename the saved package back to the original name,

and recompile the old backed up package back to the database,

which will recompile the package with the PYUPIP trace statement now gone!!



a. delete the modified package with PYUPIP trace 'turned on' using the 'rm' command:

rm pyusptoa.pkb



b. rename your backed up package back to the original name using the 'cp' command:

cp pyusptoa.old pyusptoa.pkb



c. recompile the original package, with the PYUPIP trace statement gone, through sqlplus,

and commit:



SQL> @pyusptoa.pkb

SQL> commit;



Your PYUPIP trace output file ( from step 3c )should now tell you what section of the package code is throwing the error, and this is what needs to be looked at.



---------------------------------------------------------------------------

STEP FOUR - Running HR Trace / DBMS_PIPE from within the applications

---------------------------------------------------------------------------



4a. Enter the form you are working with.

Perform the steps necessary to cause the error, but do not do the last step

that actually causes the error.



From the Toolbar select Tools > HR Trace







Select the DBMS_PIPE button under HR Trace.

In the Pipe field, is displayed a value like PIDxx.

Make a note of the PIDxx number.



Select OK to exit the Trace form. PYUPIP is now running on this form.



4b. Open a UNIX session and from your Home Directory execute the PYUPIP command.



$PAY_TOP/bin/PYUPIP apps/ PIDXXX > output.file



Where PIDxx is the PID number recorded and output.file is whatever name you want

for the log file to be named



example:

PYUPIP apps/apps PID87 > PID87_01.txt



4c. Go back to the application and execute the error.



4d. Turn off HR Trace.

From the Tools Menu, select the HR Trace option.

Click the None button under HR Trace.

Click OK to turn off PYUPIP.



4e. Review log file to see where the error is occurring.





Friday, May 11, 2012

Oracle FNDLOAD

Oracle FNDLOAD

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ... ]

: The APPS schema and password in the form username/password[@connect_string].
■< 0 Y > : Concurrent program flags
■mode : UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to the database. DOWNLOAD causes the loader tofetch rows and write them to the datafile.
: The configuration file to use (usually with a suffix of .lct, but not enforced or supplied by the loader)
: The data file to write (usually with a suffix of .ldt, but not enforced or supplied by the loader). If the data file already exists, it will be overwritten.
: The entity(ies) to upload or download. When uploading, you should always upload all entities, so specify a “-” to upload all entities.
■< [param] > : Zero or more additional parameters are used to provide bind values in the access SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form NAME=VALUE. NAME should not conflict with an attribute name for the entities being loaded.

Here are the FNDLOAD Scripts to Migrate 15 different AOL Entities

1.Profile Options
2.Forms
3.Functions
4.Menus
5.Responsibilities
6.Request Groups
7.Request Sets
8.Lookups
9.Valuesets
10.Descriptive Flexfields
11.Key Flexfields
12.Concurrent Programs
13.Form Personalization
14.Fnd Users
15.Alerts

1.Profile Options:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
PROFILE PROFILE_NAME=”XXPRNAME” APPLICATION_SHORT_NAME=”PN”

Target:
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt PROFILE PROFILE_NAME=” XXPRNAME” APPLICATION_SHORT_NAME=”PN”

2.Forms:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFRMNAME.ldt FORM APPLICATION_SHORT_NAME=”PN” FORM_NAME=”XXFRMNAME”

Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXFRMNAME.ldt

3.Functions:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFUNNAME.ldt FUNCTION FUNC_APP_SHORT_NAME=”PN” FUNCTION_NAME=”XXFUNNAME”

Target:
FNDLOAD apps/apps O Y UPLOAD @FND:patch/115/import/afsload.lct XXFUNNAME.ldt

4.Menus:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXMNNAME.ldt MENU MENU_NAME=”XXMNNAME”

Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXMNNAME.ldt

5.Responsibilities:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY=”XXRESNAME”

Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt

2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY=”XXRESNAME” APPLICATION_SHORT_NAME=”PN”

6.Request Groups:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”XXRQGNAME” APPLICATION_SHORT_NAME=”PN”

Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt
2. FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”XXRQGNAME” APPLICATION_SHORT_NAME=”PN”

7.Request Sets:
Source:
1. FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt REQ_SET REQUEST_SET_NAME=”XXRQSNAME”

2. FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt REQ_SET_LINKS REQUEST_SET_NAME=”XXRQSNAME”

Target:
1.FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt

8.Lookups:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”PN” LOOKUP_TYPE=”XXLKPNAME”

Target:
1.FNDLOAD apps/apps 0 Y UPLOAD aflvmlu.lct XXLKPNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE LOOKUP_TYPE=”XXLKPNAME” APPLICATION_SHORT_NAME=”PN”

9.Value sets:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME=”XXVALSNAME”

Target:
1.FNDLOAD apps/apps 0 Y UPLOAD afffload.lct XXVALSNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME=”XXVALSNAME” APPLICATION_SHORT_NAME=”PN”

10.Descriptive Flex-fields:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXDFFNAME.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME=”PN”

DESCRIPTIVE_FLEXFIELD_NAME=”PN_LEASE_DETAILS” P_CONTEXT_CODE=”Global Data Elements”
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXDFFNAME.ldt

11.Key Flex-fields:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXKFFNAME.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’
APPLICATION_SHORT_NAME=”FND” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”

Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXKFFNAME.ldt

12.Concurrent Programs:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM APPLICATION_SHORT_NAME=”PN” CONCURRENT_PROGRAM_NAME=”XXCPNAME”

Target:
1.FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afcpprog.lct XXCPNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM CONCURRENT_PROGRAM_NAME=”XXCPNAME” APPLICATION_SHORT_NAME=”PN”

13.Form Personalization:
Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt FND_FORM_CUSTOM_RULES function_name=”XXFPNAME”

Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt
14.FND Users:

Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ./XXUSERNAME.ldt FND_USER USER_NAME=’XXUSERNAME’

Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ./ XXUSERNAME.ldt

15.Alerts:
Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XXALERTNAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PER Alert_name=”XXALERTNAME”

Target:
FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XXALERTNAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PER Alert_name=”XXALERTNAME”



Monday, August 22, 2011

INV - Sample Script to Get Onhand Quantity Using INV_Quantity_Tree_PUB API


Excellent Repository for Oracle APIs
http://irep.oracle.com/index.html


This example is for a Lot controlled Item. Depending on your requirement, it can be Lot/Serial controlled, or no control. Also, in this example, I am using Subinventory.

DECLARE
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
x_qoh NUMBER;
x_rqoh NUMBER;
x_qr NUMBER;
x_qs NUMBER;
x_att NUMBER;
x_atr NUMBER;
x_sqoh NUMBER;
x_srqoh NUMBER;
x_sqr NUMBER;
x_sqs NUMBER;
x_satt NUMBER;
x_sqtr NUMBER;
BEGIN
inv_globals.set_org_id (104);

inv_quantity_tree_pub.clear_quantity_cache;

inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => 107,
p_inventory_item_id => 28459,
p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => TRUE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => '123456',
p_subinventory_code => 'PACK_FOAM',
p_locator_id => NULL,
x_qoh => x_qoh,
x_rqoh => x_rqoh,
x_qr => x_qr,
x_qs => x_qs,
x_att => x_att,
x_atr => x_atr);

DBMS_OUTPUT.put_line ('x_return_status = ' ||x_return_status);
DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
DBMS_OUTPUT.put_line ('x_qr = ' || x_qr);
DBMS_OUTPUT.put_line ('x_qoh = ' || x_qoh);
DBMS_OUTPUT.put_line ('x_rqoh = ' || x_rqoh);
DBMS_OUTPUT.put_line ('x_qs = ' || x_qs);
DBMS_OUTPUT.put_line ('x_att = ' || x_att);
DBMS_OUTPUT.put_line ('x_atr = ' || x_atr);

END;

Some explanations:
X_QOH NUMBER - quantity on hand
X_RQOH NUMBER - reservable quantity on hand
X_QR NUMBER - quantity reserved
X_QS NUMBER - quantity suggested
X_ATT NUMBER - quantity available to transact
X_ATR NUMBER - quantity available to reserve

Friday, March 12, 2010

Order to Invoice Flow

1. Order Entry
This is first stage, When the order is entered in the system, it basically creates a record in order headers and Order Lines table.

oe_order_headers_all (Here the flow_status_code as entered)
oe_order_lines_all (flow_status_code as entered) ( order number is generated)

2.Order Booking
This is next stage , when Order is booked then the Flow status changed from Entered to Booked. At this stage ,these below table get affected.

oe_order_headers_all (flow_status_code as booked ,booked_flag updated)
oe_order_lines_all (flow_status_code as awaiting shipping,booked_flag updated)
wsh_new_deliveries (status_code OP open)
wsh_delivery_details (released_status ‘R’ ready to release)
Same time, Demand interface program runs in background And insert into inventory tables mtl_demand

3. Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved.Once this program get successfully get completed , the mtl_reservations table get updated.

4. Pick Release
Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory. Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:

oe_order_lines_all (flow_status_code ‘PICKED’ )
wsh_delivery_details (released_status ‘S’ ‘submitted for release’ )
mtl_txn_request_headers
mtl_txn_request_lines
(move order tables.Here request is generated to move item from saleble to staging sub inventory)
Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id

5.Pick Confirm
Items are transferred from saleble to staging Subinventory.

mtl_material_transactions
mtl_transaction_accounts
wsh_delivery_details (released_status ‘Y’‘Released’ )
wsh_delivery_assignments

6.Ship Confirm
Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries

oe_order_lines_all (flow_status_code ‘shipped’)
wsh_delivery_details (released_status ‘C’ ‘Shipped’)
mtl_transaction_interface
mtl_material_transactions(linked through Transaction source header id)
mtl_transaction_accounts
Data deleted from mtl_demand,mtl_reservations
Item deducted from mtl_onhand_quantities

7.Enter Invoice
This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables.

ra_interface_lines_all (interface table into which the data is transferred from order management)Then Autoinvoice program imports data from this table which get affected into this stage are recievables base table.

ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)
ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to header_id (or order number) and line_id of the orders)

8.Complete Line
In this stage order line leval table get updated with Flow status and open flag.

oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)

9.Close Order
This is last step of Order Processing . In this stage only oe_order_lines_all table get updated. These are the table get affected in this step.

oe_order_lines_all (flow_status_code ‘closed’,open_flag “N”)

These are the typically data flow of a order to cash model for a
standard order.

Sunday, December 20, 2009

Oracle Order Status List

Order Header Statuses

Following is a list of all possible statuses assigned to an order header.

• Active
• Awaiting Invoice Interface - Incomplete Data
• Awaiting Invoice Interface - On Hold
• Awaiting Start Date
• Booked
• Cancelled
• Closed
• Customer Accepted
• Draft
• Draft - Customer Rejected
• Draft - Internal Rejected
• Draft Submitted
• Entered
• Expired
• Internal Approved
• Internal Rejected
• Invoice Interface - Complete
• Lost
• Offer Expired
• Pending Customer Acceptance
• Pending Internal Approval
• Submitted
• Terminated
• User Working


Order Line Statuses

Following is a list of all possible statuses assigned to an order line.

• Awaiting Export Screening
• Awaiting Fulfillment
• Awaiting Invoice Interface - Incomplete Data
• Awaiting Invoice Interface - On Hold
• Awaiting Invoice Interface - Partially Interfaced, RFR Item
• Awaiting Invoice Interface - Pending Complete Delivery
• Awaiting Invoice Interface - RFR Item
• Awaiting Invoice Interface - Unexpected error
• Awaiting Payment Assurance - On Hold
• Awaiting Payment Assurance - Receipts Not Assured
• Awaiting Receipt
• Awaiting Reprice - Invalid setup
• Awaiting Reprice - On reprice line hold
• Awaiting Reprice - Pricing error
• Awaiting Reprice - Unexpected error
• Awaiting Return
• Awaiting Return Disposition
• Awaiting Shipping
• Awaiting Supply
• BOM and Routing Created
• Booked
• Cancelled
• Closed
• Completed Export Screening
• Config Item Created
• Customer Accepted
• Data Error Export Screening
• Draft
• Draft - Customer Rejected
• Draft - Internal Rejected
• Draft Submitted
• Entered
• Fulfilled
• Interfaced to Receivables
• Internal Approved
• Internal Rejected
• Inventory Interfaced
• Invoice Interface - Not Applicable
• Lost
• Offer Expired
• PO-Created
• PO-Partial
• PO-Received
• PO-ReqCreated
• PO-ReqRequested
• Partially Interfaced to Receivables
• Payment Assurance - Complete
• Payment Assurance - Incorrect Data
• Pending Customer Acceptance
• Pending Internal Approval
• Picked
• Picked Partial
• Preprovision
• Preprovision Failed
• Preprovision Requested
• Preprovision Succeeded
• Production Complete
• Production Eligible
• Production Open
• Production Partial
• Provisioning Failed to update Transaction Details
• Provisioning Rejected
• Provisioning Requested
• Provisioning Successful
• Provisioning in Error
• Released to Warehouse
• Reprice - Complete
• Reprice - Not Applicable
• Returned
• Scheduled
• Shipped
• Supply Eligible
• Supply Open
• Supply Partial
• Third Party Billing Failed
• Third Party Billing Requested
• Third Party Billing Succeeded

Wednesday, October 14, 2009

Creating AP Checks in Oracle R12

Check Printing has changed considerably in Oracle R12. Initially it looked to me to be very complex, but after spending considerable time understanding it, I have started to like many of the new features. Some of the new additions are:


  1. There is a standard Oracle Program to generate the XML. It creates most of the fields necessary to create checks.
  2. Check payment data has been moved from 'Payables' responsibility to 'Payments' responsibility. The schema name for Payments is IBY.
  3. You have to create only the RTF file since the XML file is generated by Oracle. You may change or add a new RTF - all you have to do is to upload the new RTF file once the check processes have been setup.
  4. The number of lines per page can be setup in the Payment Processing Profile. The XML file will be generated based on this number. It does seem to me that this option puts some restrictions on the flexibility provided by oracle, but so far I was able to work with it.

Pre-requisites:

  1. Download BI Publisher Template Builder for Word ver 10.1.3.4.1 from download.oracle.com
  2. Basic requirements to start creating checks is to have an RTF file (Template) and an XML file (data definition).
  3. There are 2 ways that you can have an RTF file:
    o Create your own, or
    o (Recommended) Download standard oracle Template
    Payables Manager > Setup > Payment > Payment Administrator > Formats > XML Publisher Format Templates
    Query and download the RTF file
    ‘Standard Check Format’ IBYDC_STD1.rtf or
    Standard Check Format (Stub After Payment) IBYDC_STD1A.rtf
  4. Getting an XML file is tricky. I had to spend some trying to get this extract. This is kind of a Catch-22 situation. You have to actually create the entire Payment setup first and run a payment process to get the extract. Here is what Oracle recommends:

    --> Go to Metalink and Check Oracle Note 457539.1. This note says that the Package IBY_FD_EXTRACT_EXT_PUB controls the XML extract used in check printing. The supplied version can be found in $IBY_TOP/patch/115/sql/ibyfdxeb.pls

    --> Better way is to follow the instructions in Doc ID: 465389.1 R12 Create Or Modify A Payment Format Using XML Builder. This note explains the same steps that I have defined below
  5. MICR Fonts. This is another important requirement which you will run into later on. So I have decided to address it right away because you may end up spending a lot more time if you start thinking about it later. One of the easiest way is to buy the MICR font from a third party like IDAutomation (http://www.idautomation.com/) and use MICRW1B.ttf. Typical cost is around $750.00 per user license – you may need to purchase only one license. You may also download their Demo version which is good for testing.

    The good news is that Oracle also provides a MICR font which is available if you have downloaded the BI Template Builder. Some banks reject this font, but so far I did not have any problem with the bank that we used.

    Download and install BI Publisher ver 10.1.3.4.1
    Font File Location: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\fonts\ MICR____.TTF

    In order to use this file in the RTF template, copy the font file MICR____.TTF to C:\WINDOWS\Fonts

At this stage, you may or may not have designed the Check template. But you can still use IBYDC_STD1.rtf downloaded earlier.

Following steps are needed to create a check in AP from scratch. These steps may also be used to create the initial payment to get the XML file.

Step 1: Create XML Template

Payables Manager > Setup > Payment > Payment Administrator > Formats > XML Publisher Format Templates
Click 'Create Template'
Enter valid name, Code, Application (= Payments)
Type = RTF
Data Definition = Oracle Payments Funds Disbursement Payment Instruction Extract 1.0
Default File Language = English
Default File Territory = UNITED STATES
Upload your RTF File

Step 2: Create Payment Format

Payables Manager > Setup > Payment > Payment Administrator > Formats > Formats
Select Type = Disbursement Payment Instruction
Click on Create
Enter Code, NameType = Same as above
Data Extract = Oracle Payments Funds Disbursement Payment Instruction Extract 1.0
XML Publisher Extract = XML Template name created in Step 1

Step 3: Create Payment Document

Payables Manager > Setup > Payment > Bank Accounts

Query up your Bank Name. It is assumed that your Bank has already been setup in AP.
Select the Bank Account and click on Manage Payment Documents.
Click on Create
Enter:
Name: XXXX Check Payment Document
Paper Stock Type: Blank Stock or Prenumbered Stock
If you have remittance stub, check the box for Attached Remittance Stub
Important: If you check the above box, a new field called ‘Number of Lines per Remittance Stub’ will show up. This is number that you will have to find out by trial and error once you start testing your template. Initially, set it to a lower number, say 15.
Format: Enter Format defined in step 2
First Available Document Number:
Last Available Document Number
Enter any other information that you may need.
Click Apply

Step 4: Create Payment Profile

Payables Manager > Setup > Payment > Payment Administrator > Payment Process Profiles

Click on Create
Enter:
Code
Name
Payment Instruction Format: As defined in Step 2
Processing Type: Printed
Payment Completion Point: When the Payment Instruction is Formatted
Default Payment Document: Document created in Step 3
Payment File: Send to Printer
Check the box ‘Automatically Print After Formatting’ (Note: You mau choose not to check this box initially – it will help in testing)
Default Printer: Valid oracle printer name
Click Apply

Now you are all set to create checks. All you have to do is create an Invoice, or select any existing validated invoice for a vendor.

There is one more thing that you must check before creating the payment.

Verify Concurrent Program

Go to Application Developer or Sys Admin responsibility and bring up the concurrent program Format payment Instructions

Concurrent > Program > Define > F11 > Format Payment Instructions > Ctrl-F11
(Short Name: IBY_FD_PAYMENT_FORMAT)

Set Output Format to XML: Use this option to first generate your XML (Data Definition) file

Set Output to PDF : This will create your check in PDF format once your check template is ready. Also, Set Style to PDF Publisher in this case.


How to Submit a Request to create Payments

Submission process for AP Checks/Payments has also changed in R12. Most of the options are now available in HTML screens. The good news is that you can create templates (Not XML templates - these are different) so that you do not have to enter the same information every time you submit a request.

Submit Request:

Payables Manager > Payment Manager > Submit Single Payment Process Request

Following parameters are minimum required - you can enter others as necessary:

Following parameters are minimum required - you can enter others as necessary:
Payment Process Request name (Mandatory): Provide any useful Name

Use Template: Leave Blank

Payee : Vendor NamePayment Method: Check (Or any other name that has been setup is payment method)

Click on Payment Attributes Tab:
Payment Date: Defaulted to today's date

Disbursement Bank Account: Enter your Bank Account Name associated with this payment
Payment Document : Enter payment document name created in Step 3 above
Payment Process Profile : This value will get filled automatically with the profile defined above in step 4
Payment Exchange Rate Type: Enter valid excahnge rate type (Ex: Corporate, Spot)


These parameters are sufficient to create a payment. Click on Submit in order to process. However, I do recommend following steps also in the begining.

Click on Processing Tabs:

Check following boxes

  • Stop Process for Review After Scheduled Payment Selection. This step will stop after building the payments so that you can review the payments selected, and cancel any of them, if necessary
  • Stop Process for Review After Creation of Proposed Payments
    Click on Submit. Click on Home. Click on Monitor Requests.

How to Create Template for Request Submission

Payables Manager > Payment Manager > Create Template

Note: All of the fields in this process are the same as in 'Submit Single Request'. Enter any fields that you repeat for request submission. In the above example, usually leave the Payee field blank if you want to run for separate vendors every time. Once the template has been created and saved, you may enter the template name in the field 'Use Template' in the example above.

There are several other options which I haven't explored yet. I will add more once I get to try them out.