be happy and make others to be happy: May 2013

Monday, 13 May 2013

Some Intresting Sql Queries

Query to retrieve 1,4,7,10.....rows from a table

SELECT a.* FROM emp a WHERE (ROWID, 1) IN (SELECT ROWID, MOD (ROWNUM, 3) FROM emp);

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

Query to print Rupees in words
SELECT sal "Salary ", (' Rs. ' (TO_CHAR (TO_DATE (sal, 'j'), 'Jsp')) ' only.' ) "Sal in Words" FROM emp

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

Query to print the calender for the year

SELECT LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Sun", "Mon",
"Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
TO_CHAR (dt + 1, 'iw') week,
MAX (DECODE (TO_CHAR (dt, 'd'),
'1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sun",
MAX (DECODE (TO_CHAR (dt, 'd'),
'2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Mon",
MAX (DECODE (TO_CHAR (dt, 'd'),
'3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Tue",
MAX (DECODE (TO_CHAR (dt, 'd'),
'4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Wed",
MAX (DECODE (TO_CHAR (dt, 'd'),
'5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Thu",
MAX (DECODE (TO_CHAR (dt, 'd'),
'6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Fri",
MAX (DECODE (TO_CHAR (dt, 'd'),
'7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
)
) "Sat"
FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12) - TRUNC (SYSDATE, 'y')) GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week)

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

Use of Order by clause for records with datatype as character

We generally cannot order the records for a column with character data type. Please use the following trick to use the order clause:

select employee_number from employees
order by lpad(employee_number,100);

Using the lpad the zeros would be appended and then sql will treat them as numbers and the employee number would be sorted.

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

Printing Fibonacci series in PL/SQL

DECLARE
RESULT NUMBER := 1;
previous NUMBER := -1;
l_sum NUMBER;
n NUMBER;
l_in NUMBER := 10;
BEGIN
FOR n IN 1 .. l_in
LOOP
l_sum := RESULT + previous;
previous := RESULT;
RESULT := l_sum;
DBMS_OUTPUT.put_line (l_sum);
END LOOP;
END;

Other ways to print the Fib series is as follows:

CREATE OR REPLACE FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
IF (n = 1) OR (n = 2) THEN -- terminating condition
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2); -- recursive call
END IF;
END fib;

/

-- Test Fibonacci Series:
SELECT fib(1), fib(2), fib(3), fib(4), fib(5) FROM dual;

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

Demonstrate simple encoding and decoding of secret messages

SELECT TRANSLATE(
'HELLO WORLD', -- Message to encode
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'1234567890!@#$%^&*()-=_+;,.') ENCODED_MESSAGE
FROM DUAL
/

SELECT TRANSLATE(
'85@@%._%*@4', -- Message to decode
'1234567890!@#$%^&*()-=_+;,.',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ ') DECODED_MESSAGE
FROM DUAL
/

Computing the Factorial of a number (n!)

CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER IS
BEGIN
IF n = 1 THEN -- terminating condition
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- recursive call
END IF;
END fac;
/

-- Test n!
SELECT fac(1), fac(2), fac(3), fac(4), fac(5) FROM dual;

sql to print string vertically
SELECT SUBSTR ('&&String', ROWNUM, 1)
FROM all_tables
WHERE ROWNUM <= LENGTH (TRIM ('&STRING'));

Procedure to reverse a string
SQL> create or replace procedure rev(x in varchar2) as
2 c char(1);
3 i number;
4 begin

5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(c);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /

SQL> set serverout on
SQL> exec rev('Java')
avaJ

Display the PL/SQL Dependency Tree
SELECT lvl, u.object_id, u.object_type, LPAD (' ', lvl) || object_name obj
FROM (SELECT LEVEL lvl, object_id
FROM SYS.public_dependency s
START WITH s.object_id =
(SELECT object_id
FROM user_objects
WHERE object_name = UPPER ('&OBJECT_NAME')
AND object_type = UPPER ('&OBJECT_TYPE'))
CONNECT BY s.object_id = PRIOR referenced_object_id
GROUP BY LEVEL, object_id) tree,
user_objects u
WHERE tree.object_id = u.object_id
ORDER BY lvl

When prompted, enter the OBJECT_NAME of the object whose dependencies you want to identify.

The OBJECT_NAME can be a PACKAGE, PACKAGE BODY, or PROCEDURE. The OBJECT_NAME is the name of the object at the root of the tree.

SQL script to lists all the profile settings (all levels)
SELECT pot.user_profile_option_name "Profile"
, DECODE( a.profile_option_value
, '1', '1 (may be "Yes")'
, '2', '2 (may be "No")'
, a.profile_option_value) "Value"
, DECODE( a.level_id
, 10001, 'Site'
, 10002, 'Appl'
, 10003, 'Resp'
, 10004, 'User'
, '????') "Levl"
, DECODE( a.level_id
, 10002, e.application_name
, 10003, c.responsibility_name
, 10004, d.user_name
, '-') "Location"
FROM applsys.fnd_application_tl e
, applsys.fnd_user d , applsys.fnd_responsibility_tl c
, applsys.fnd_profile_option_values a , applsys.fnd_profile_options b
, applsys.fnd_profile_options_tl pot
WHERE UPPER( pot.user_profile_option_name) LIKE UPPER( '%&&v_profile%')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id = a.application_id (+)
AND b.profile_option_id = a.profile_option_id (+)
AND a.level_value = c.responsibility_id (+)
AND a.level_value = d.user_id (+) AND a.level_value = e.application_id
(+)
AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')
OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')
OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'))
ORDER BY "Profile", "Levl", "Location", "Value"

Important questions to be remembered by a Oracle Consultant

What is Set of Books ? What are the four conditions when you change your SOBs?
Chart of Accounts, Currency & Calendar
It is similar to the bank passbook, used to record all the financial transactions. There could be one primary set of books and many reporting set of books. Defining COA, currency and calendar are pre-requisite to define the SOB.

What is an Invoice? How many types of invoices ar...
There are 9 types of Invoices in AP: Standard, Credit Memo, Debit Memo, Prepayment, Withholding Tax, Mixed Invoice, Expense Report, PO default and Quick Match.
There are 6 types of Invoice in AR : Invoice, Credit Memo, Debit Memo, Chargeback, Deposit and Guarantee.

What is the difference between data conversion and data migration?
Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to ur our the target module using the standard import program.

Set ups need to approve invoice in AP
For approving an invoice in AP we have to raise one invoice for raising an invoice we have some mandatory fields like supplier, supplier number, Payment terms, distribution sets, date, payment method, bank, payment document etc. After defining all the above we can raise a invoice, There's no need of defining the approval group it s not a mandatory,

What is a profile option? What are the types?The profile options are available to every product in Oracle Applications. For each profile option, we give a brief overview of how Oracle Application Object Library uses the profile's setting.
1) User Level
2) Responsibility Level
3) Application Level
4) Site Level.

What are _ALL tables in Oracle Apps?
_ALL tables in oracle applications give the info about multiple organizations info about these tables.

What is descriptive flex field and what is the useDescriptive Flexfileds r used to add additional informations, and these values r stored to the column attributes. Go to sysadmin application-flex fields-descriptive-segments
What is a FlexField? What are Descriptive and Key...
A flexfield is made up of sub-fields or segments.. A flexfield appers on ur form as a pop-up window that contains a prompt for each segment. Each segment has a name and a set of valid values..
Two types of Flex field..
Key Flexfield: Key flexfield are flexible enough to let any organization use the code scheme they want wothout programming.Key flexfield can be used to represent codes that is made up of meaningful segment to identify GL a/c Part no. and other business entities..Oracle app store these codes in key flexfields..
Descriptive Flexfield: They provide customizable "expansion space" on ur forms. You can use desc flexfields to tract additional information important and unique to ur business that would not otherwise be captured by the form.

What Credit memo / Debit MemoBoth Credit and Debit Memo are used for adjusting the suppliers balance and both is a negative amount. Debit Memo is created by you and send to the supplier and credit memo is recieved from the supplier and record it.

What is a request Sets? how to create a request Se..
Request set is a group of requests.It is made to perform the request in a certain sequence.Request se can be created from System administrator responsibility.

What are the types of Concurrent Managers?Ca...
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.
We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

What is Multiple Organization technical architecture?
Multi Organization :Using a single installation of any oracle application product , to support any number of an organization even it has different Set Of Books Bussiness Level(It Secures Human TransactionInformation) Set Of Books(It secures all Transaction Information In Oracle Genral Ledger) Leagel Entry(All leagal Information in the Organization) Operating Unit(It Uses Order management, Cash managment,AR,AP...it may be Sales Office , Division,Department) Inventry Organization(Inventry Details)
before multi org existed, we could have only one bussiness group,set of books, legal entity, operating unit in one installation of oracle E-bussiness suite, and now with the multi organisation structure in place, we can have multiple bussiness groups, set of books, legal entity and operating unit and the best part is once these are set up intercompany accounting is automatically taken care of, say for example i have Two inventory organisation, and these use a common set of books(to start with) , now if we have sales order on one inventory organisation A1 and if that item is not available in A1, and we have inventory for the item in Inventory org A2, we need to take the order in A1 as internal order and run the order import concurrent program and for the item in inventory A1(org assignment) we need to set up the source(purchase tab) as inventory organisation A2,and also specify shipping network between A1 and A2, and once we have run the order import program and Oracle applications now imports the order to organisation A2, with the ship to address as A1 location. and after you perform pick release and pick confirm process, and run auto invoice in A2, it automatically sends invoice to inventory organisation A1 and now we can receive the item in Inventory organisation A1 from organisation A2.
and all transactions are taken care of, courtesy multi organisation structure.

What are different period types ?Year Quarter Month Week

What are the different types of files used in SQLDifferent types of files are Data File,Control File,Discard File,Bad file

What is Oracle Financials?
Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can meet accounting management demands with:
o Oracle Assets: Ensures that an organization's property and equipment investment is accurate and that the correct asset tax accounting strategies are chosen.
o Oracle General Ledger: Offers a complete solution to journal entry, budgeting, allocations, consolidation, and financial reporting needs.
o Oracle Inventory: Helps an organization make better inventory decisions by minimizing stock and maximizing cash flow.
o Oracle Order Entry: Provides organizations with a sophisticated order entry system for managing customer commitments.
o Oracle Payables: Lets an organization process more invoices with fewer staff members and tighter controls. Helps save money through maximum discounts, bank float, and prevention of duplicate payment.
o Oracle Personnel: Improves the management of employee- related issues by retaining and making available every form of personnel data.
o Oracle Purchasing: Improves buying power, helps negotiate bigger discounts, eliminates paper flow, increases financial controls, and increases productivity.
o Oracle Receivables:. Improves cash flow by letting an organization process more payments faster, without off-line research. Helps correctly account for cash, reduce outstanding receivables, and improve collection effectiveness.
o Oracle Revenue Accounting: Gives an organization timely and accurate revenue and flexible commissions reporting.
o Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.

What is the difference between Fields and FlexFields?
A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field.
A flexfield is an Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization. There are two types of flexfields: key flexfields and descriptive flexfields.

Difference between Conversions and Interfaces.
Conversion is to bring the data from other (non-OraApps) system to Oracle-Application system. This is one time activity (bring account details, transactions, orders, receipts and so on). So conversion itself is a project.
Interfaces are Concurrent PRograms/Program sets (pl/sql, pro*C, unix scripts, executables) . Interfaces are basically of two types (Oracle supplied (vanila programs eg:Autoinvoice in AR) and created by developer).
Hope this clarifies the concepts.
Conversion means one time activityinterface means periodic activityexample:- to transfer the data old version to new version it is called conversion to transfer the data from staging table to interface table it is called interface , it is process on every day or every hour ........

Which module is not a multiorg?
General Ledger and CRM Foundation Modules

What are the types of Concurrent Managers?Can we delete a Concurrent Manager?
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.
We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

Trading community Architecture(TCA)
ICA (Internet Computing Architecture)

What are AP setup steps ?setup---->suppliers....>invoices..>payments.....>reports......>periods.....>transfer to GL

What does US mean in appl_top/au/11.5.0/reports/US?
US is the language directory specifying that the source files to be placed under this dir is for English/American Language
This is the standard of apps directory structure that for very language you implement oracle apps there should be a language specific folder

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

What are the Back ground processes in Oracle and what are they.
There are basically 9 Processes.They do the house keeping activities for the Oracle and are common in any system.The various background processes in oracle are:
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is commited.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup.This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor peforms process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the
most recent checkpoint
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.

How many types of Sql Statements are there in Oracle
There are basically 6 types of sql statments.They are
a) Data Defination Language(DDL) :: The DDL statments define and maintain objects and drop objects.
b) Data Manipulation Language(DML) :: The DML statments manipulate database data.
c) Transaction Control Statements :: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g, Alter Statements,Set Role
e) System Control Statements :: Change Properties of Oracle Instance .e.g, Alter System
f) Embedded Sql :: Incorporate DDL,DML and T.C.S in Programming Language.e.g, Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly commited or rolled back.

Key Words Used in Oracle
The Key words that are used in Oracle are ::
a) Commiting :: A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transactino into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explcit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache :: Databese Buffer of SGA stores the most recently used blocks of datatbase data.The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechansim in Operating System that executes series of steps.

What are Procedure,functions and Packages
* Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
* Procedures do not Return values while Functions return one One Value
*Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

What are Database Triggers and Stored Procedures
Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
e.g:: operations insert,update ,delete 3
before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures :: Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules :: The Third Intigrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

What are the Various Master and Detail Relation ships.
The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is exisiting
b) Isolated :: The Master can be deleted when the child is exisiting
c) Cascading :: The child gets deleted when the Master is deleted.

What are the Various Block Coordination Properties
The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deffered with No Auto Query
The operator must navigate to the detail block and explicitly execute a query

What are the Different Optimisation Techniques
The Various Optimisation techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint ::
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No

Restrict '&' in Sql*Plus
sql> set define off
This will restrict the use of '&' in the sql*plus for that particular session

Workflow Errors

Error:
ORA-04061: existing state of has been invalidated ORA-04061: existing state of package "APPS.HR_TRANSACTION_WEB" has been invalidated ORA-04065: not executed, altered or dropped package "APPS.HR_TRANSACTION_WEB" ORA-06508: PL/SQL: could not find program unit being called.
Resolution:
The cause for this is the SGA (memory that is allocated by an Oracle Instance and is shared among Oracle processes). Any object in SGA referencing an object that is compiled become invalid during this compilation.

So in this case the process accessing the compiled object needs to be restarted. For example, in case of Oracle Workflows, this error can be rectified by restarting the Workflow Mailer ( or a Retry also helps). One more way is to bounce the database itself which ofcourse will be a last resort.

Workaround
The invalid object needs to be recompiled to become valid again. Oracle 10g includes a mechanism to recompile some invalid objects when they are accessed for the first time. However, this mechanism does not always work and errors of this sort can occur. If this error is encountered, the invalid packages and any invalid type bodies can be maually recompiled.

Be sure to start a new Oracle session after recompiling before verifying the recompiling solved the problem.

To find invalid objects owned by the user SDE, connect to Oracle with SQL*PLus and execute the following statement:

SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY')
AND STATUS = 'INVALID';

To recompile a type body, use the following command:

ALTER TYPE object_name COMPILE BODY;

To recompile a package body, use the following command:

ALTER PACKAGE object_name COMPILE BODY;

Once the above process is completed and still the issue is not resolved then we need to bounce the instance and restart the workflow process.

Error:
3146: Commit happened in activity/function 'HR_HRSSA_APPROVED_PRC:HR_COMMIT_TRANSACTION_FCT/HR_TRANSACTION_WEB.COMMIT_TRANSACTION'
Resolution:
Once we have updated the package we need to bounce the instance and restart the workflow process.

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

Workflow Commands
Command to download workflow: WFLOAD apps/apps 0 Y DOWNLOAD workflow_name Internal_Name
Command to upload workflow: WFLOAD apps/apps 0 Y UPLOAD workflow_name

FND_LOAD commands.

FND in Oracle:
1. FND is short for “Foundation” which was the name Oracle initially wanted to use for the Application Object Library- the application with common components shared between all the other applications in the Oracle E-Business Suite.

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:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
2. FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt PROFILE PROFILE_NAME=" XXPRNAME" APPLICATION_SHORT_NAME="PN"

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

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

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

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"

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"

Request Sets:

Source:
Step1:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt REQ_SET REQUEST_SET_NAME="XXRQSNAME"
Step2:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt REQ_SET_LINKS REQUEST_SET_NAME="XXRQSNAME"

Target:

Step1:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt
Step2:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt

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"

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"

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

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

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"

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

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

Printer Styles:

Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct XXPRSTYLE.ldt STYLE PRINTER_STYLE_NAME="XXPRSTYLE"

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

Some useful Meta-link Notes related to FNDLOAD:

1. For NLS Language using FNDLOAD:
Note: 434724.1
2. Troubleshooting Incorrect translation with FNDLOAD
Note: 299080.1

Note:
1. Test FNDLOAD commands multiple times in multiple instances before running into PROD instances.
2. UPLOAD_PARTIAL is used to modify existed programs.
3. Execution sequence is important ex: To create a responsibility  Create Form Create Function  Create Menu  Create Responsibility

Standard Line Status Flows in OM

Oracle Order Management captures the order line status in the Sales Order Pad on the Line Items Main tab in the status field and in the Order Organizer on the Summary and Line tabs. Oracle Shipping Execution displays the delivery line status in the Shipping Transactions Form on the Lines/LPN Main tab in the Line Status field. For a standard flow the statuses are:

Begin by placing the order in Order Management (OM):

Entered (OM): Order is saved but not booked.

Booked (OM): Order is booked.

Scheduled (OM): A user can customize the Workflow to show the Scheduled status which indicates that the order line has been successfully scheduled. When the ship line logic starts, the order line status changes to Awaiting Shipping.

Awaiting Shipping (OM): Order is booked but lines are not yet picked.

Open (OM): This status of a delivery on the Additional Line Information form indicates that none of the delivery lines associated with that delivery have been ship confirmed.

Ready to Release (SE): Order line is booked and passed to Shipping Execution. It is now a delivery line that is eligible for Pick Release.

Submitted for Release (SE): In Release 11i.4, Submitted for Release status changes name to Released to warehouse.

Released to Warehouse (SE): Pick Release has started but not completed. Either no allocations were created or allocations have not been Pick Confirmed.

Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used.

Released (SE): Pick Release has run successfully. The action has translated the delivery lines into move order lines in Inventory. The move order lines have been allocated and pick confirmed. Once a delivery has been created, the delivery and its associated delivery lines are eligible for Ship Confirm. This status is no longer valid starting with Release 11i.4. Released was replaced by the following three additional statuses:

Backordered (SE): The delivery line is pick released but no allocations were created or partial allocations occurred. As an example, if a delivery line has a quantity of 100, and at pick release only 25 are available for allocation, the original delivery line splits to create a new line (quantity of 75) for the unallocated portion with a status of

Backordered. The quantity on the original delivery line changes to 25 to reflect the allocated portion with a status of Staged/Pick Confirmed.
Staged/Pick Confirmed (SE): The delivery line is successfully pick released. It occurs after pick confirm to indicate subinventory transfer from source location to staging location is complete. Lines staged until they are ship confirmed.

Both Backordered and Staged/Pick Confirmed status provide the ability to perform opportunistic cross-docking for warehouse organizations with Oracle Warehouse Management System (WMS) installed.

Shipped (SE): This line status indicates that the delivery associated with the delivery line(s) is ship confirmed.

In Transit (SE): This delivery status indicates that the delivery associated with the line is ship confirmed and the pick up stop is closed.

Confirmed (SE): This delivery status indicates that the delivery line is either shipped or backordered and the trip stops are open.

Navigate back to Order Management and query the order which results in OM pulling updated Pick Release information from Shipping Execution (in R11i.4, these statuses will occur after Staged/Pick Confirmed and Backordered respectively):

Picked (OM): Pick release has completed normally (both allocation and pick confirm). The delivery associated with the delivery line(s) may have also been Ship Confirmed but the Delivery may not be set in transit and the Trip may not be closed.

Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred. The delivery line splits during Ship Confirm and the information passes to Order Management through the Process Order API. The order line then splits to reflect the changes that occurred during the Shipping process. As an example, a customer orders quantity 50. There are 20 on hand in inventory. The delivery line splits into two delivery lines and therefore represents two order lines in Order Management. The order line with quantity 20 has the status of Picked or Shipped depending on whether
or not the delivery line is Ship Confirmed, the Delivery set in transit and the Trip closed. The second order line with a quantity of 30 has a status of Awaiting Shipping.

Shipping Execution pushes status information to Order Management once Ship
Confirm is completed:

Shipped (OM): The delivery associated with the line is Ship Confirmed. The Delivery status is set to in transit. This status appears in the Additional Line Information at the Pick Status field.

Interfaced (SE): If delivery was sourced from Oracle OM: The delivery line is shipped and the OM Interface and Inventory Interface concurrent processes have completed.

If delivery was sourced from an Oracle Application other than OM: The delivery
line is shipped and the Inventory Interface concurrent process has completed.

Awaiting Fulfillment (OM): Not all shippable lines in a fulfillment set or a configuration are fulfilled. This is a synchronization step within the Workflow process.

Fulfilled (OM): All lines in a fulfillment set are fulfilled.
Note: Fulfillment Sets are defined as a group of order lines that get fulfilled together. Items that are not shippable can be in fulfillment sets with shippable items, and then will not be fulfilled (and therefore invoiced) until the shippable items are fulfilled. A line can belong to either a ship set or an arrival set, but can belong to multiple fulfillment sets.

Interfaced to Receivables (OM): Invoice Interface has been launched. Order Management writes information to Receivables tables.
Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.

Closed (OM): Closed indicates that the line is closed. It does not necessarily indicate that the line is interfaced to Accounts Receivable (AR) since you must “close line” activity in a no-bill flow.

Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line.

The following scenario will emulate a Standard customer order from the first customer call to the invoice. The line status will assist the customer service agent on the Shipper's side to answer the questions of the Customer.

Entered Status (OM)
A customer calls and begins placing an order with the customer service representative. The customer is unclear whether or not the order is complete and indicates that he/she will call back to finish placing the order. The customer service representative saves the order to capture the current information but will not book the order because the customer has indicated that the order is not complete. Both the Order Header and the Order Lines associated with the customer call will have the status of Entered once the order is saved. The line on the order exists in the system and can be queried when the customer calls back to complete the order.

Booked Status (OM)
The customer service representative receives a second call the customer and adds two additional lines to the order. The customer indicates that the order is complete so the user Books the order.

Ready to Release Status (SE)
Once the order has been booked, the information passes to Shipping Execution. Order lines appear as delivery lines. Initially, it is a one to one ratio of order line to delivery line. The customer service agent calls the warehouse to ensure that the order that was just booked has appeared in Shipping Execution. The warehouse clerk queries the delivery lines by the order number provided by the customer service representative and indicates that the Line Status is Ready to Release indicating the delivery lines are eligible for Pick Release. The customer service representative has been assured that the booked order lines are visible in the Shipping Transactions form and are ready for the next step, Pick Release.

Staged/Pick Confirmed and Released to Warehouse Statuses (SE)
The warehouse clerk launches Pick Release. Upon querying the delivery lines by order number, the warehouse clerk will see that the Pick Release status is: Staged/Pick Confirmed for those delivery lines that have received allocation and Pick Confirmed successfully and Released to Warehouse for delivery lines that require a manual Pick Confirm or have not been allocated.

Picked and Awaiting Shipping Statuses (OM)
The customer who placed the order calls up and wants to know the status, the customer service representative queries up the order in the Order Organizer and finds that the status of the lines are Picked and Awaiting Shipping. The customer service representative is equipped to report that two of the order lines are processing smoothly as they have been picked from their source location and transferred to the staging location within the warehouse. The customer service representative can also inform the customer that the third order line has been released to the warehouse.

Closed and Picked Status (OM)
The warehouse clerk has just Ship Confirmed the delivery associated with the delivery lines corresponding to the customer’s order. The warehouse clerk used the check boxes on the Ship Confirm form to automatically set the delivery in transit and close the trip. Order Management will be updated through the Process Order API and the order lines that previously had the status of Picked will now show a status of Closed. The customer calls back to check the status of the order, the customer service representative can tell the customer the date(s) that two of the order lines physically shipped from the warehouse. The customer service representative can also inform the customer that the third order line has been Picked, indicating that the next function is to ship the product.

Standard Line Status Flows in OM

Oracle AIM Document Templates

1. Business Process Architecture (BP)
BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure

2. Business Requirements Definition (RD)
RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements

3. Business Requirements Mapping
BR.010 Analyze High-Level Gaps
BR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model
BR.070 Create Reporting Fit Analysis
BR.080 Test Business Solutions
BR.090 Confirm Integrated Business Solutions
BR.100 Define Applications Setup
BR.110 Define security Profiles

4. Application and Technical Architecture (TA)
TA.010 Define Architecture Requirements and Strategy
TA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy
TA.070 Revise Conceptual Architecture
TA.080 Define Application Security Architecture
TA.090 Define Application and Database Server Architecture
TA.100 Define and Propose Architecture Subsystems
TA.110 Define System Capacity Plan
TA.120 Define Platform and Network Architecture
TA.130 Define Application Deployment Plan
TA.140 Assess Performance Risks
TA.150 Define System Management Procedures

5. Module Design and Build (MD)
MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines

6. Data Conversion (CV)
CV.010 Define data conversion requirements and strategy
CV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs
CV.070 Prepare conversion test plans
CV.080 Develop conversion programs
CV.090 Perform conversion unit tests
CV.100 Perform conversion business objects
CV.110 Perform conversion validation tests
CV.120 Install conversion programs
CV.130 Convert and verify data

7. Documentation (DO)
DO.010 Define documentation requirements and strategy
DO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual
DO.070 Publish user guide
DO.080 Publish technical reference manual
DO.090 Publish system management guide

8. Business System Testing (TE)
TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test

9. PERFORMACE TESTING(PT)
PT.010 - Define Performance Testing Strategy
PT.020 - Identify Performance Test Scenarios
PT.030 - Identify Performance Test Transaction
PT.040 - Create Performance Test Scripts
PT.050 - Design Performance Test Transaction Programs
PT.060 - Design Performance Test Data
PT.070 - Design Test Database Load Programs
PT.080 - Create Performance Test TransactionPrograms
PT.090 - Create Test Database Load Programs
PT.100 - Construct Performance Test Database
PT.110 - Prepare Performance Test Environment
PT.120 - Execute Performance Test

10. Adoption and Learning (AP)
AP.010 - Define Executive Project Strategy
AP.020 - Conduct Initial Project Team Orientation
AP.030 - Develop Project Team Learning Plan
AP.040 - Prepare Project Team Learning Environment
AP.050 - Conduct Project Team Learning Events
AP.060 - Develop Business Unit Managers’Readiness Plan
AP.070 - Develop Project Readiness Roadmap
AP.080 - Develop and Execute CommunicationCampaign
AP.090 - Develop Managers’ Readiness Plan
AP.100 - Identify Business Process Impact onOrganization
AP.110 - Align Human Performance SupportSystems
AP.120 - Align Information Technology Groups
AP.130 - Conduct User Learning Needs Analysis
AP.140 - Develop User Learning Plan
AP.150 - Develop User Learningware
AP.160 - Prepare User Learning Environment
AP.170 - Conduct User Learning Events
AP.180 - Conduct Effectiveness Assessment

11. Production Migration (PM)
PM.010 - Define Transition Strategy
PM.020 - Design Production Support Infrastructure
PM.030 - Develop Transition and Contingency Plan
PM.040 - Prepare Production Environment
PM.050 - Set Up Applications
PM.060 - Implement Production Support Infrastructure
PM.070 - Verify Production Readiness
PM.080 - Begin Production
PM.090 - Measure System Performance
PM.100 - Maintain System
PM.110 - Refine Production System
PM.120 - Decommission Former Systems
PM.130 - Propose Future Business Direction
PM.140 - Propose Future Technical Direction

Types of table extensions in Oracle

_ALL : Table holds all the information about different operating units. Multi-Org environment. You can also set the client_info to specific operating unit to see the data specific to that operating unit only.

_TL are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.

_B these are the BASE tables.
They are very important and the data is stored in the table with all validations.
It is supposed that these table will always contain the perfect format data.
If anything happens to the BASE table data, then it is a data corruption issue.

_F these are date tracked tables, which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap. Many think they are Secured data. Guess someone from Oracle confirms.

_V tables are the views created on base tables

_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV(’LANG’).

_S are sequences, used for finding new values for the primary key of a table.

_A are Audit Shadow Tables

_AVN and _ACN are Audit Shadow Views (when data was changed, and with what values

P2P CYCLE Query

Query that covers P2P life cycle

P2P Cycle

There are certain things that should be wkown before going indetail about the P2P cycle.

What is PO?
PO is a commercial document issued by a buyer to a seller, indicating the type, quantities and agreed prices for products or services that the seller will provide to the buyer.

Why we need this PO?
This document represents the buyer’s intent to purchase specific quantities of product at specified prices. In the event of non-payment, the seller can use the PO as a legal document in a court of law.

What are the types of Purchase Orders?

1. Standard Purchase Orders:You generally create standard purchase orders for one-time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require. Each standard purchase order line can have multiple shipments, and you can distribute the quantity of each shipment across multiple accounts.

2.Contract Purchase Orders:A contract purchase agreement is an agreement between you and a supplier for unspecified goods or services. This agreement may include terms and conditions, committed amount, and effective and expiration dates.

3. Blanket Purchase Orders:With blanket purchase agreements and releases, you can negotiate a stable, long-term contract while maintaining flexible delivery schedules and order quantity commitments. You create blanket purchase agreements when you know the details of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules.

4. Planned Purchase Orders:You create a planned purchase order when you want to establish a long-term agreement with a single supplier to buy goods or services. Planned purchase orders include tentative delivery schedules and accounting distributions. You schedule releases against the planned purchase order to actually order the goods or services.

(P to P) cycle: There are different steps involved in P to P cycle they are `

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.

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.

Note: The query to retrieve the data which covers the complete p2p cycle is as follows:

SELECT
A.ORG_ID “ORG ID”,
E.VENDOR_NAME “VENDOR NAME”,
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) “VENDOR TYPE”,
F.VENDOR_SITE_CODE “VENDOR SITE”,
F.ADDRESS_LINE1 “ADDRESS”,
F.CITY “CITY”,
F.COUNTRY “COUNTRY”,
TO_CHAR(TRUNC(D.CREATION_DATE)) “PO DATE”,
D.SEGMENT1 “PO NUMBER”,
D.TYPE_LOOKUP_CODE “PO TYPE”,
C.QUANTITY_ORDERED “QTY ORDERED”,
C.QUANTITY_CANCELLED “QTY CANCALLED”,
G.ITEM_DESCRIPTION “ITEM DESCRIPTION”,
G.UNIT_PRICE “UNIT PRICE”,
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) “PO Line Amount”,
(SELECT
DECODE(PH.APPROVED_FLAG, ‘Y’, ‘Approved’)
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) “PO STATUS”,
A.INVOICE_TYPE_LOOKUP_CODE “INVOICE TYPE”,
A.INVOICE_AMOUNT “INVOICE AMOUNT”,
TO_CHAR(TRUNC(A.INVOICE_DATE)) “INVOICE DATE”,
A.INVOICE_NUM “INVOICE NUMBER”,
(SELECT
DECODE(X.MATCH_STATUS_FLAG, ‘A’, ‘Approved’)
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)”Invoice Approved?”,
A.AMOUNT_PAID,
H.AMOUNT,
I.CHECK_NUMBER “CHEQUE NUMBER”,
TO_CHAR(TRUNC(I.CHECK_DATE)) “PAYMENT DATE”
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = ‘Y’
AND D.TYPE_LOOKUP_CODE != ‘BLANKET’;

A comparative analysis between SQL*LOADER and UTL_FILE utility

In implementing new systems we come across problems of importing "alien" data. This may be coming from a legacy system or an on-going system. This data is transported via extract files from the legacy system to the Oracle system. The gateway to Oracle for this data is SQL*Loader and data is loaded into tables via a control script into tables.

Typically, the older systems do not have very normalized data, nor have they been operating with fully implemented database constraints. The lack of constraints over the years in legacy system can lead to bad data that has crept in. Therefore, while bringing external data into oracle system we need a refined set of checks and balances to ensure that we get good data. This requires a lot of programmatic control in the process of data-loading.

The approach applied in case of SQL* Loader is as follows :
1. Load the data into temporary tables via SQL*Loader via control file and make the data native to ORACLE.
2. Write a PL/SQL program to do the processing.
3. Load the data into live tables.
This approach has a lot of dependencies as well as a strong lack of integration of steps and programmatic control. To overcome this, we have analyzed another facility in that has been release Oracle 7.3.x onwards. It is called the UTL_FILE package. With some creative use of this package we can achieve whatever SQL*LOADER offers and in addition to that do some high level validation and complex data loading. In the following discussion a study of two tools is done.

A BRIEF OVERVIEW OF SQL*Loader:
SQL*Loader is a server utility for loading data from external data files into Oracle database. The basic advantage of using SQL*Loader is for simple loads and fast loading of data. It can load data into myriad data formats, perform elementary filtering, load data into multiple tables, and create one logical record from one or more physical records.

It creates a detailed log file, a bad file that contains rejected records and a discard file to hold the records that are selectively not loaded. The tool is executed from a command line and a username and password and the control file name and location are required to run it.

A BRIEF OVERVIEW OF UTL_FILE:
PL/SQL does not have text file input output capabilities but acquires it via UTL_FILE package. It provides rudimentary utility for reading ( as well as writing) files from within a PL/SQL program. The lines in the file are read sequentially and hence it effects the performance of the program.

The UTL_FILE package can be wrapped around with a PL/SQL program and since this package is integrated with PL/SQL it provides us the tremendous ability for flexing our "programming muscles." Some procedures and functions can be added to this wrapper program that serve as a handy "tool" for doing normal file reading operations. With this approach we can achieve whatever SQL*Loader can do and much more. The security mechanism for UTL_FILE is achieved by defining a parameter in INIT.ora file called utl_file_dir parameter. The directories that UTL_FILE can read from and write to need to have permissions of Oracle instance owner and the user running the package.

CONCLUSIONS:
The comparative analysis of SQL*Loader and UTL_FILE reveals that these tools are suitable to our environment subject to the conditions of our needs.

If the data load is complex (as is the case in relational databases) UTL_FILE seems to be the tool of choice. This tool does require programmatic effort in terms of writing a wrapper package but the subsequent effort in this direction is greatly reduced once the initial tool kit is built for your environment.

The UTL_FILE tightly integrates the file input with the programmatic control and the data manipulation inside a single PL/SQL unit. There are disadvantages of speed in loading in case of UTL_FILE but these are totally offset by the programmatic control it offers and the integration it brings in.

Script to get the invoice and supplier details

SELECT ai.invoice_id, ai.invoice_num, ai.invoice_type_lookup_code,
ai.invoice_date, (SELECT segment1
FROM po_headers_all
WHERE po_header_id = ai.po_header_id) po_number,
ai.vendor_id, pv.segment1 vendor_number, pv.vendor_name,
ai.vendor_site_id, pvs.vendor_site_code,
( NVL (ai.invoice_amount, 0)
- NVL (ai.amount_paid, 0)
+ NVL (ai.discount_amount_taken, 0)
) remaining_amount,
ai.invoice_currency_code, ai.exchange_rate, ai.exchange_rate_type,
ai.exchange_date, ai.terms_id, (SELECT NAME
FROM ap_terms
WHERE term_id = ai.terms_id) terms,
ai.description, ai.awt_group_id,
(SELECT awt.NAME
FROM ap_awt_groups awt
WHERE awt.GROUP_ID = ai.awt_group_id) awt_group, ai.last_update_date,
ai.last_updated_by, ai.last_update_login, ai.creation_date,
ai.created_by, ai.attribute_category, ai.attribute1, ai.attribute2,
ai.attribute3, ai.attribute4, ai.attribute5, ai.attribute6,
ai.attribute7, ai.attribute8, ai.attribute9, ai.attribute10,
ai.attribute11, ai.attribute12, ai.attribute13, ai.attribute14,
ai.attribute15, ai.global_attribute_category, ai.global_attribute1,
ai.global_attribute2, ai.global_attribute3, ai.global_attribute4,
ai.global_attribute5, ai.global_attribute6, ai.global_attribute7,
ai.global_attribute8, ai.global_attribute9, ai.global_attribute10,
ai.global_attribute11, ai.global_attribute12, ai.global_attribute13,
ai.global_attribute14, ai.global_attribute15, ai.global_attribute16,
ai.global_attribute17, ai.global_attribute18, ai.global_attribute19,
ai.global_attribute20, ai.payment_cross_rate_type,
ai.payment_cross_rate_date, ai.payment_cross_rate,
ai.payment_currency_code, ai.doc_category_code, ai.voucher_num,
ai.payment_method_lookup_code, ai.pay_group_lookup_code,
ai.goods_received_date, ai.invoice_received_date, ai.gl_date,
ai.accts_pay_code_combination_id, ai.ussgl_transaction_code,
ai.exclusive_payment_flag, ai.org_id, ai.amount_applicable_to_discount,
pvs.email_address, ai.terms_date, ai.requester_id
FROM ap_invoices_all ai, po_vendors pv, po_vendor_sites_all pvs
WHERE 1 = 1
AND ai.vendor_id = pv.vendor_id
AND pvs.vendor_id = pv.vendor_id
AND ai.vendor_site_id = pvs.vendor_site_id

OM CYCLE AND P2P CYCLE

Flow of Order Management

Begin by placing the order in Order Management (OM):
• Entered (OM): Order is saved but not booked.
• Booked (OM): Order is booked.
• Scheduled (OM): You can customize the workflow to show the Scheduled statuswhich indicates that the order line has been successfully scheduled by adding acustomized activity after the Schedule activity. This activity will make a process Shipping
14-31order API call to update the status to Scheduled. When the ship line logic starts, theorder line status changes to Awaiting Shipping.
• Awaiting Shipping (OM): Order is booked and scheduled but lines have not beenpicked. This status is also displayed after the line has been ship confirmed but beforethe Order Management interface has been run.
• Picked (OM): Order is booked and lines are picked.
• Open (OM): This status of a delivery on the Additional Line Information formindicates that none of the delivery lines associated with that delivery have beenship confirmed.Navigating to Shipping Execution, the delivery line status flow is:
• Ready to Release (SE): Order line is booked and passed to Shipping Execution. Itis now a delivery line that is eligible for Pick Release.
• Not Ready to Release (SE): A delivery line might be in this status when it is interfacedmanually into Shipping Execution, is not scheduled, and has no reservations. Whenlines are imported automatically from Order Management this status is not used.
• Backordered (SE): The delivery line is pick released but no allocations were createdor partial allocations occurred. As an example, if a delivery line has a quantity of100, and at pick release only 25 are available for allocation, the original delivery linesplits to create a new line (quantity of 75) for the unallocated portion with a status ofBackordered. The quantity on the original delivery line changes to 25 to reflect theallocated portion with a status of Staged/Pick Confirmed.
• Staged/Pick Confirmed (SE): The delivery line is successfully pick released. It occursafter pick confirm to indicate subinventory transfer from source location to staginglocation is complete. Lines remain staged until they are ship confirmed.
• Released to Warehouse (SE): Pick Release has started but not completed. Either noallocations were created or allocations have not been Pick Confirmed.Note: Both Backordered and Staged/Pick Confirmed statusesprovide the ability to perform opportunistic cross-docking forwarehouse organizations with Oracle Warehouse Management(WMS) installed.
• Shipped (SE): This line status indicates that the delivery associated with the deliverylines is ship confirmed.
• Interfaced (SE): If delivery was sourced from Oracle OM: The delivery line isshipped and the OM Interface and Inventory Interface concurrent processeshave completed. If delivery was sourced from an Oracle Application other thanOM: The delivery line is shipped and the Inventory Interface concurrent processhas completed.
• Canceled (SE): This status indicates that the delivery line was cancelled.Navigate back to Order Management and query the order which results in OM pullingupdated Pick Release information from Shipping Execution:
• Picked (OM): Pick release has completed normally (both allocation and pickconfirm). The delivery associated with the delivery line(s) may have also been ShipConfirmed but the Delivery may not be set in transit and the Trip may not be closed.14-32 Oracle Order Management Implementation Manual
• Picked Partial (OM): This status occurs when a delivery line is not allocated the fullquantity during Pick Release and Ship Confirm has not occurred.The delivery line splits during Ship Confirm and the information passes toOrder Management through the Process Order API. The order line splits toreflect the changes that occurred during the Shipping process. As an example, acustomer orders quantity 50. There are 20 on hand in inventory. The delivery linesplits into two delivery lines and therefore represents two order lines in OrderManagement. The order line with quantity 20 has the status of Picked or Shippeddepending on whether or not the delivery line is Ship Confirmed, the Delivery set intransit, and the Trip closed. The second order line with a quantity of 30 has statusof Awaiting Shipping.Shipping Execution passes the status information to Order Management when ShipConfirm is complete:
• Shipped (OM): The delivery associated with the line is Ship Confirmed. TheDelivery status is set to In transit. This status appears at the line level as well as inthe Additional Line Information at the Pick Status field.
• Awaiting Shipping (OM): Awaiting information from shipping. This status willremain until the Order Management interface is run.
• Awaiting Fulfillment (OM): Not all shippable lines in a fulfillment set or aconfiguration are fulfilled. The current line is waiting for other lines in the fulfillmentset or the configuration to be fulfilled. This is a synchronization step within theworkflow process.
• Fulfilled (OM): All lines in a fulfillment set are fulfilled.Fulfillment Sets are defined as a group of order lines that are fulfilled together. Itemsthat are not shippable can be in fulfillment sets with shippable items, and then willnot be fulfilled (and therefore invoiced) until the shippable items are fulfilled. A linecan belong to either a ship set or an arrival set, but can belong to multiple fulfillmentsets.
• Interfaced to Receivables (OM): Order Management has written information tothe Receivables Interface tables. You should run Auto Invoice (from Receivables)to generate the Invoice.
• Partially Interfaced to Receivables (OM): This status is used in a PTO flow andindicates that the particular PTO item is required for revenue.
• Closed (OM): Closed indicates that the line is closed. It does not necessarily indicatethat the line is interfaced to Accounts Receivable (AR) since you must close lineactivity in a no-bill flow.
• Canceled (OM): Indicates that the line is completely canceled. No further processing will occur for this line.

===

ORDER NO:66424

====ORDER ENTRY IN HEADERS===

SELECT OOHA.ORDER_NUMBER,OOHA.HEADER_ID,OOHA.FLOW_STATUS_CODE
FROM OE_ORDER_HEADERS_ALL OOHA
WHERE OOHA.ORDER_NUMBER='66424'

O/P: OOHA.FLOW_STATUS_CODE=ENTERED

====ORDER ENTERY IN LINES==

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOHA.ORDER_NUMBER='66424'

O/P: OOHA.FLOW_STATUS_CODE=ENTERED,OOLA.FLOW_STATUS_CODE=ENTERED

===========BOOKING THE ORDER====

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND OOHA.ORDER_NUMBER='66424'

O/P:OOHA.FLOW_STATUS_CODE=BOOKED,OOLA.FLOW_STATUS_CODE=AWAITING_SHIPPING,WDD.RELEASED_STATUS=R

===========PICK RELEASE THE ORDER==

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_NUMBER='66424'

O/P:OOHA.FLOW_STATUS_CODE=BOOKED,OOLA.FLOW_STATUS_CODE=AWAITING_SHIPPING,WDD.RELEASED_STATUS=R

===============SHIP CONFIRM THE ORDER==

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED,WDA.DELIVERY_ID,WDA.DELIVERY_DETAIL_ID,WND.DELIVERY_ID,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_NUMBER='66424'

O/P:OOHA.FLOW_STATUS_CODE=BOOKED,OOLA.FLOW_STATUS_CODE=AWAITING_SHIPPING,WDD.RELEASED_STATUS=Y,WND.STATUS_CODE:OPEN

IN THE BACK END:Pick Slip Report,Shipping Exceptions Report,Auto Pack Report (Auto-pack Report)
WILL RUN IN THE BACKGROUND

======SHIP CONFIRM THE ORDER==

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED,WDA.DELIVERY_ID,WDA.DELIVERY_DETAIL_ID,WND.DELIVERY_ID,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_NUMBER='66423'

O/P:OOHA.FLOW_STATUS_CODE=BOOKED,OOLA.FLOW_STATUS_CODE=SHIPPED,WDD.RELEASED_STATUS=C,WND.STATUS_CODE:CL

BACKGROUND PROCESS:Bill of Lading,Packing Slip Report,Commercial Invoice,
Vehicle Load Sheet Details,Interface Trip Stop WILL RUN IN BACKGROUND

====RUN WORK FLOW BACKGROUND PROCESS TO IMPORT THE DATA INTO INVOICE INTERFACE TABLES ==

HERE IN BACKGROUND :Workflow Background Process,ADS, (Autoinvoice Master Program),
Autoinvoice Import Program,Prepayment Matching Program (Prepayments Matching Program) WILL RUN

HERE COPY THE INVOICE NO:10037642,AMOUNT:8,714.55 FROM ORDER ORGANIZER

==ENTERING THE RECEIPT IN MANUAL METHOD==

GO TO RECEIVALES VISION OPERATIONS->RECEIPTS->RECEIPTS->ENTER THE RECEIPT MANUALLY->APPLY TO ORDER ->SAVE

===CREATE ACCOUNTING==

LEDGER->VISION OPERATIONS(USA),END DATE:SYSDATE,ETC->SUBMIT

HERE IN BACKGROUND:Create Accounting,Accounting Program,Journal Import,Subledger Accounting Balances Update
WILL RUN IN BACKGROUND

COPY THE JOURNAL IMPORT REQUEST ID:5861766

===TRANSFER TO GENRAL LDGGER==

ledger:%5861766%

IN BACKGROUND Posting: Single Ledger WILL RUN

======COMPLETE O2C CYCLE==

====TECHNICAL FLOW OF O2C==

ENTER THE SALES ORDER

OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA

OOHA.HEADER_ID=OOLA.HEADER_ID

BOOK THE SALES ORDER

OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD

OOHA.HEADER_ID=OOLA.HEADER_ID
OOLA.LINE_ID=WDD.SOURCE_LINE_ID

PICK RELEASE THE SALES ORDER

OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND

OOHA.HEADER_ID=OOLA.HEADER_ID
OOLA.LINE_ID=WDD.SOURCE_LINE_ID
WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
WDA.DELIVERY_ID=WND.DELIVERY_ID

=TABLES EFFECTED B/W PICK RELEASE AND SHIP CONFIRM==

WSH_DELIVERY_LEGS WDL,
WSH_TRIPS WT,
WSH_TRIP_STOPS WTS

==SHIP CONFIRM THE ORDER==

OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND

OOHA.HEADER_ID=OOLA.HEADER_ID
OOLA.LINE_ID=WDD.SOURCE_LINE_ID
WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
WDA.DELIVERY_ID=WND.DELIVERY_ID

==AUTO INVOICE==

RA_INTERFACE_LINES_ALL RILA,
RA_INTERFACE_DISTRIBUTIONS_ALL RIDA

==TRANSACTIONS==

RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCTLA

RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID

==CASH RECEIPTS==

AR_PAYMENT_SCHEDULES_ALL APSA,
AR_CASH_RECEIPTS_ALL ACRA

==SUB LEDGER ACCOUNTING==

XLA_AE_HEADERS XAH (XAH.AE_HEADER_ID=XAL.AE_HEADER_ID,XAH.APPLICATION_ID=XAL.APPLICATION_ID)
XLA_AE_LINES XAL, (XAL.APPLICATION_ID=XDL.APPLICATION_ID,XAL.AE_LINE_NUM=XDL.AE_LINE_NUM)
XLA_EVENTS XE, (XAH.APPLICATION_ID=XE.APPLICATION_ID,XAH.EVENT_ID=XE.EVENT_ID)
XLA_TRANSACTION_ENTITIES XTE, (XAH.APPLICATION_ID=XTE.APPLICATION_ID,XAH.ENTITY_ID=XTE.ENTITY_ID)
XLA_DISTRIBUTION_LINKS XDL (XDL.APPLICATION_ID=XAH.APPLICATION_ID,XDL.AE_HEADER_ID=XAH.AE_HEADER_ID)

==GENERAL LEDGER ==

GL_JE_BATCHES GJB, GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
GJ_JE_HEADERS GJH, GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
GL_JE_LINES GJL, GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID,
(GJL.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID,GJL.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE)
(GJL.JE_HEADER_ID=GIR.JE_HEADER_ID,GJL.JE_LINE_NUM=GIR.JE_LINE_NUM)
GL_BALANCES GB, GB.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID,
GL_CODE_COMBINATIONS GCC,(GCC.CODE_COMBINATION_ID=PRDA.
CODE_COMBINATION_ID,GCC.CODE_COMBINATION_ID=PDA.CODE_COMBINATION_ID)
GL_IMPORT_REFERENCES GIR
(GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID,GIR.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE)

==MOVE ORDER TABLES==

MTL_TXN_REQUEST_HEADERS MTRH
MTL_TXN_REQUEST_LINES MTRL
MTL_MATERIAL_TRANSACTIONS MMT
MTL_DEMAND MD
MTL_SYSTEM_ITEMS_B MSIB
MTL_ONHAND_QUANTITIES MOQ

MTRH.HEADER_ID=MTRL.HEADER_ID
MSIB.ORGANIZATION_ID=MMT.ORGANIZATION_ID
MSIB.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID
MSIB.ORGANIZATION_ID=MOQ.ORGANIZATION_ID
MSIB.INVENTORY_ITEM_ID=MOQ.INVENTORY_ITEM_ID

======================P2P CYCLE=====================

FIRST REQUISITION

ENTER THE ITEM,QUANTITY,NEED BY DATE->SAVE IT
THEN COPY THE REQUISITION NO:14318

SELECT PRHA.SEGMENT1 "REQ_NO",PRHA.AUTHORIZATION_STATUS
FROM
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA
--PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
--AND   PRLA.REQUISITION_LINE_ID=PRD.REQUISITION_LINE_ID
AND PRHA.SEGMENT1='14318'

AT HEADERS LEVEL: O/P IS REQ_NO:14318,PRHA.AUTHORIZATION_STATUS=INCOMPLETE

===APPROVING THE REQUISITION==

SELECT PRHA.SEGMENT1 "REQ_NO",PRHA.AUTHORIZATION_STATUS,PRD.DISTRIBUTION_ID,PRD.DISTRIBUTION_NUM
FROM
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND   PRLA.REQUISITION_LINE_ID=PRD.REQUISITION_LINE_ID
AND PRHA.SEGMENT1='14318'

O/P:O/P IS REQ_NO:14318,PRHA.AUTHORIZATION_STATUS=APPROVED,PRD.DISTRIBUTION_ID=207077,PRD.DISTRIBUTION_NUM=1

===CREATE PUCHASE ORDER==

GO TO PURCHASE ORDERS->PURCHASE ORDERS->

GIVE THE SUPPLIER:DELL,ITEM,QUANTITY,NEED BY DATE,

GO TO SHIPMENTS->MORE->THER U CAN SEE THE MATCH APPROVE LEVEL(2-WAY(PO,INVOICE),3-WAY(PO,RECEIPT,INVOICE),
4-WAY(PO,RECEIPT,INVOICE,INSPECTION) WITH IN THE TOLLERENCE TIME)
GO TO DISTRIBUTIONS -> IF U WANT TO SHIP THE ITEM TO MORE THAN ONE SHIP TO ADDRESS

SAVE IT
THEN A PURCHASE ORDER NO WILL GENERATE :6040

NEXT APPROVE IT

SELECT PHA.SEGMENT1 "PO_NO",PHA.AUTHORIZATION_STATUS,PLA.LINE_NUM,PHA.PO_HEADER_ID,PLLA.PO_LINE_ID,PLLA.LINE_LOCATION_ID
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA
WHERE PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND PHA.SEGMENT1='6040'

O/P:PO_NO:6040,AUTHORIZATION_STATUS:APPROVED,LINEE_NUM:1,PO_HEADER_ID:110486,PO_LINE_ID:173415,LINE_LOCATION_ID:264561

==RECEIVING->RECEIPTS->SELECT ORGANIZATION==

ENTER THE PURCHASE ORDER NO:6040 ->FIND

CHECK THE LINE,ENTER THE SUB INVENTORY->SAVE->THE CLICK ON HEADER AND SAVE THE RECEIPT NO:8460

SELECT DISTINCT RSH.RECEIPT_NUM,RSL.AMOUNT,RSH.SHIPMENT_HEADER_ID
FROM
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_REQUISITION_LINES_ALL PRLA,
PO_HEADERS_ALL PHA,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
RCV_TRANSACTIONS RT,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND PHA.PO_HEADER_ID=RT.PO_HEADER_ID
AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND PHA.PO_HEADER_ID=PDA.PO_HEADER_ID
AND PDA.PO_DISTRIBUTION_ID=RSL.PO_DISTRIBUTION_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND RSH.RECEIPT_NUM='8460'
--AND PHA.SEGMENT1='6040'

===PAYABLES VISION OPERATIONS==

GO TO INVOICES->INQUIRY->GIVE THE PO NUMBER->FIND->COPY THE INVOICE NO:ERS-8460-163753

THEN GO TO THE INVOICES->ENTRY ->VIEW->QUERY->ENTER THE INVOICE NO:ERS-8460-163753->VIEW->QUERY

THEN YOU MUST CHECK THE ACTIONS->VALIDATE->OK(STATUS WILL BE VALIDATED)
THEN ACTIONS CREATE ACCOUNTING(FINAL)->OK(HERE ACCOUNTING WILL BE CREATED FOR THE PERTICULAR TRANSACTION)
THEN ACTIONS PAY IN FILL->OK->IT WILL GO TO THE NEW WINDOW->THERE GIVE THE PAYMENT DATE->10-APR-2012,BANK ACCOUNT:AP/AR NETTING,
PAYMENT METHOD:AUTOMATICALLY IT WILL POP OUT,PAYMENT PROCESS PROFILE->ISABEL FOREIGN->SAVE IT

===THEN REQUEST FOR THE TRANSFER TO GENERAL LEDGER->
PARAMETERSLEDGER:Vision Operations (USA)
END DATE:10-APR-2012,
POST IN GENERAL LEDGER:NO
OK->SUBMIT->REQUEST->FIND->

IN BACKGROUND:Format Payment Instructions with Text Output,Transfer Journal Entries to GL,Journal Import,
Open Account Balances Data Manager,TB Worker 1 (Open Account Balances Data Manager Worker Process),
WILL RUN IN BACKGROUND

COPY THE JOURNAL IMPORT:5861787

===GO TO GENERAL LEDGER VISION OPERATIONS==

JOURNALS->ENTER->LEDGER:%5861787%->FIND->POST THE JOURNAL LEDGER->REVIEW THE JOURNAL LEDGER->
BOTH DEBIT AND CREDIT MUST MATCH

IN BACKGROUND:Posting: Single Ledger WILL RUN

INVOICE TABLES=
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA

==ACCOUNTING ENTRIES TABLES==

AP_ACCOUNTING_EVENTS_ALL ACEA,
AP_AE_HEADERS_ALL AAHA,
AP_AE_LINES_ALL AALA

==PAYMENT TABLES==

AP_INVOICE_PAYMENTS_ALL AIPA,
AP_PAYMENT_SCHEDULES_ALL APSA,
AP_CHECKS_ALL ACA,
AP_CHECK_FORMATS ACF,
AP_BANK_ACCOUNTS_ALL ABAA,
AP_BANK_BRANCHES ABB,
AP_TERMS APT

===COMPLETE P2P CYCLE==

=====PROCURE TO PAY QUERY==

WITH OUT RECEIPTS

SELECT REQH.SEGMENT1 "REQ_NUM",REQH.AUTHORIZATION_STATUS "REQ_STATUS",
POH.PO_HEADER_ID,POH.SEGMENT1 "PO_NUM",POH.AUTHORIZATION_STATUS "PO_STATUS",I.INVOICE_NUM,I.INVOICE_ID,
I.INVOICE_AMOUNT,I.AMOUNT_PAID,I.VENDOR_ID,I.VENDOR_CONTACT_ID,I.VENDOR_SITE_ID,P.CHECK_ID,C.CHECK_NUMBER,H.GL_TRANSFER_FLAG,
H.PERIOD_NAME
FROM
AP_INVOICES_ALL I,
AP_INVOICE_DISTRIBUTIONS_ALL INVD,
PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_DISTRIBUTIONS_ALL POD,
AP_SUPPLIERS V,
PO_REQUISITION_HEADERS_ALL REQH,
PO_REQUISITION_LINES_ALL REQL,
PO_REQ_DISTRIBUTIONS_ALL REQD,
AP_INVOICE_PAYMENTS_ALL P,
AP_CHECKS_ALL C,
AP_AE_HEADERS_ALL H,
AP_AE_LINES_ALL L
WHERE 1=1
AND I.VENDOR_ID=V.VENDOR_ID
AND C.CHECK_ID=P.CHECK_ID
AND P.INVOICE_ID=I.INVOICE_ID
AND REQH.REQUISITION_HEADER_ID=REQL.REQUISITION_HEADER_ID
AND REQD.REQUISITION_LINE_ID=REQL.REQUISITION_LINE_ID
AND POD.REQ_DISTRIBUTION_ID=REQD.DISTRIBUTION_ID
AND POD.PO_HEADER_ID=POH.PO_HEADER_ID
AND POD.PO_DISTRIBUTION_ID=INVD.PO_DISTRIBUTION_ID
AND INVD.INVOICE_ID=I.INVOICE_ID
AND I.SOURCE='AP_INVOICES'
AND REQH.SEGMENT1=''
AND POH.SEGMENT1=''
AND I.INVOICE_NUM=''
AND C.CHECK_NUMBER=''
AND I.VENDOR_ID=''

======P2P QUERY WITH RECEIPTS==

SELECT
FROM
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_DISTRIBUTIONS_ALL PDA,
PO_VENDORS PV,
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
AP_AE_HEADERS_ALL AAHA,
AP_AE_LINES_ALL AALA
WHERE 1=1
AND AIA.VENDOR_ID=PV.VENDOR_ID
AND ACA.CHECK_ID=AIPA.CHECK_ID
AND AIPA.INVOICE_ID=AIA.INVOICE_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND PDA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PHA.PO_HEADER_ID=RT.PO_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID(+)
AND RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RT.TRANSACTION_TYPE='RECEIVE'
AND RT.SOURCE_DOCUMENT_CODE='PO'
AND PLA.PO_LINE_ID=RT.PO_LINE_ID
AND PDA.PO_DISTRIBUTION_ID=RT.PO_DISTRIBUTION_ID
AND PDA.PO_DISTRIBUTION_ID=AIDA.PO_DISTRIBUTION_ID
AND AIDA.INVOICE_ID=AIA.INVOICE_ID
AND AAHA.AE_HEADER_ID=AALA.AE_HEADER_ID
AND AALA.SOURCE_TABLE='AP_INVOICES'
AND AALA.SOURCE_ID=AIA.INVOICE_ID
AND PHA.SEGMENT1=''
AND PRHA.SEGMENT1=''
AND AIA.INVOICE_NUM=''
AND ACA.CHECK_NUMBER=
AND RSH.RECEIPT_NUM=
AND PV.VENDOR_ID=

==PURCHASE ORDER TABLES WITH LINKS==
PO_REQUISITION_HEADERS_ALL   PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
PO_VENDOR_CONTACTS PVC,
PER_ALL_PEOPLE_F PAPF
WHERE PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND   PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND   PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND   PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND   PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND   PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND   PHA.PO_HEADER_ID=PDA.PO_HEADER_ID
AND   PHA.VENDOR_ID=PV.VENDOR_ID
AND   PVS.VENDOR_SITE_ID=PHA.VENDOR_CONTACT_ID
AND   PVC.VENDOR_CONTACT_ID=PHA.VENDOR_CONTACT_ID
AND   PAPF.PERSON_ID=PHA.AGENT_ID

IMPORTANT COLUMNS IN THE TABLES

PO_REQUISITION_HEADERS_ALL PRHA
PRHA.REQUISITION_HEADER_ID
PRHA.PREPARER_ID
PRHA.SEGMENT1 "REQQ_NO"

PO_REQUISITION_LINES_ALL PRLA
PRLA.REQUISITION_LINE_ID
PRLA.REQUISITION_HEADER_ID
PRLA.LINE_NUM
PRLA.UNIT_PRICE
PRLA.QUANTITY
PRLA.QUANTITY_CANCELLED
PRLA.QUANTITY_DELIVERED
PRLA.QUANTITY_RECEIVED

PO_REQ_DISTRIBUTIONS_ALL PRDA
PRDA.DISTRIBUTION_ID
PRDA.REQUISITION_LINE_ID
PRDA.SET_OF_BOOKS_ID
PRDA.REQ_LINE_QUANTITY
PRDA.REQ_LINE_AMOUNT

PO_HEADERS_ALL PHA
PHA.PO_HEADER_ID
PHA.AGENT_ID
PHA.SEGMENT1
PHA.VENDOR_ID
PHA.VENDOR_SITE_ID
PHA.SHIP_TO_LOCATION_ID
PHA.BILL_TO_LOCATION_ID

PO_LINES_ALL PLA
PLA.PO_LINE_ID
PLA.PO_HEADER_ID
PLA.ITEM_DESCRIPTION
PLA.UNIT_PRICE
PLA.QUANTITY

PO_DISTRIBUTIONS_ALL PDA
PDA.PO_DISTRIBUTION_ID
PDA.PO_HEADER_ID
PDA.PO_LINE_ID
PDA.QUANTITY_ORDERED
PDA.REQ_DISTRIBUTION_ID

PO_VENDORS PV
PV.VENDOR_ID
PV.VENDOR_NAME
PV.SEGMENT1
PV.SET_OF_BOOKS_ID

PO_VENDOR_SITES_ALL PVS
PVS.VENDOR_SITE_ID
PVS.VENDOR_ID
PVS.VENDOR_SITE_CODE
PVS.ADDRESS_LINE1
PVS.CITY
PVS.PHONE

PO_VENDOR_CONTACTS PVC
PVC.VENDOR_SITE_ID
PVC.VENDOR_CONTACT_ID
PVC.PHONE

PER_ALL_PEOPLE_F PAPF
PAPF.PERSON_ID
PAPF.BUSINESS_GROUP_ID
PAPF.FULL_NAME
PAPF.EMAIL_ADDRESS

===========================END OF P2P==========================================

ORDER NO:66424

====ORDER ENTRY IN HEADERS===

SELECT OOHA.ORDER_NUMBER,OOHA.HEADER_ID,OOHA.FLOW_STATUS_CODE
FROM OE_ORDER_HEADERS_ALL OOHA
WHERE OOHA.ORDER_NUMBER='66407'

O/P: OOHA.FLOW_STATUS_CODE=ENTERED

====ORDER ENTERY IN LINES==

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOHA.ORDER_NUMBER='66407'

O/P: OOHA.FLOW_STATUS_CODE=ENTERED,OOLA.FLOW_STATUS_CODE=ENTERED

===========BOOKING THE ORDER====

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND OOHA.ORDER_NUMBER='66407'

O/P:OOHA.FLOW_STATUS_CODE=BOOKED,OOLA.FLOW_STATUS_CODE=AWAITING_SHIPPING,WDD.RELEASED_STATUS=R

===========PICK RELEASE THE ORDER==

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_NUMBER='66407'

5834765

O/P:OOHA.FLOW_STATUS_CODE=BOOKED,OOLA.FLOW_STATUS_CODE=AWAITING_SHIPPING,WDD.RELEASED_STATUS=R

===============SHIP CONFIRM THE ORDER==

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED,WDA.DELIVERY_ID,WDA.DELIVERY_DETAIL_ID,WND.DELIVERY_ID,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_NUMBER='66407'

O/P:OOHA.FLOW_STATUS_CODE=BOOKED,OOLA.FLOW_STATUS_CODE=AWAITING_SHIPPING,WDD.RELEASED_STATUS=Y,WND.STATUS_CODE:OPEN

IN THE BACK END:Pick Slip Report,Shipping Exceptions Report,Auto Pack Report (Auto-pack Report)
WILL RUN IN THE BACKGROUND

======SHIP CONFIRM THE ORDER==

SELECT OOHA.ORDER_NUMBER,OOHA.FLOW_STATUS_CODE,OOLA.HEADER_ID,OOLA.LINE_ID,OOHA.HEADER_ID,OOLA.FLOW_STATUS_CODE,
WDD.RELEASED_STATUS,WDD.DATE_REQUESTED,WDA.DELIVERY_ID,WDA.DELIVERY_DETAIL_ID,WND.DELIVERY_ID,WND.STATUS_CODE
FROM
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OOLA.LINE_ID=WDD.SOURCE_LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID=WND.DELIVERY_ID
AND OOHA.ORDER_NUMBER='66407'

O/P:OOHA.FLOW_STATUS_CODE=BOOKED,OOLA.FLOW_STATUS_CODE=SHIPPED,WDD.RELEASED_STATUS=C,WND.STATUS_CODE:CL

BACKGROUND PROCESS:Bill of Lading,Packing Slip Report,Commercial Invoice,
Vehicle Load Sheet Details,Interface Trip Stop WILL RUN IN BACKGROUND

====RUN WORK FLOW BACKGROUND PROCESS TO IMPORT THE DATA INTO INVOICE INTERFACE TABLES ==

HERE IN BACKGROUND :Workflow Background Process,ADS, (Autoinvoice Master Program),
Autoinvoice Import Program,Prepayment Matching Program (Prepayments Matching Program) WILL RUN

HERE COPY THE INVOICE NO:10037642,AMOUNT:8,714.55 FROM ORDER ORGANIZER

==ENTERING THE RECEIPT IN MANUAL METHOD==

GO TO RECEIVALES VISION OPERATIONS->RECEIPTS->RECEIPTS->ENTER THE RECEIPT MANUALLY->APPLY TO ORDER ->SAVE

===CREATE ACCOUNTING==

LEDGER->VISION OPERATIONS(USA),END DATE:SYSDATE,ETC->SUBMIT

HERE IN BACKGROUND:Create Accounting,Accounting Program,Journal Import,Subledger Accounting Balances Update
WILL RUN IN BACKGROUND

COPY THE JOURNAL IMPORT REQUEST ID:5861766

===TRANSFER TO GENRAL LDGGER==

ledger:%5861766%

IN BACKGROUND Posting: Single Ledger WILL RUN

======COMPLETE O2C CYCLE==

====TECHNICAL FLOW OF O2C==

ENTER THE SALES ORDER

OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA

OOHA.HEADER_ID=OOLA.HEADER_ID

BOOK THE SALES ORDER

OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD

OOHA.HEADER_ID=OOLA.HEADER_ID
OOLA.LINE_ID=WDD.SOURCE_LINE_ID

PICK RELEASE THE SALES ORDER

OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND

OOHA.HEADER_ID=OOLA.HEADER_ID
OOLA.LINE_ID=WDD.SOURCE_LINE_ID
WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
WDA.DELIVERY_ID=WND.DELIVERY_ID

=TABLES EFFECTED B/W PICK RELEASE AND SHIP CONFIRM==

WSH_DELIVERY_LEGS WDL,
WSH_TRIPS WT,
WSH_TRIP_STOPS WTS

==SHIP CONFIRM THE ORDER==

OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_NEW_DELIVERIES WND

OOHA.HEADER_ID=OOLA.HEADER_ID
OOLA.LINE_ID=WDD.SOURCE_LINE_ID
WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
WDA.DELIVERY_ID=WND.DELIVERY_ID

==AUTO INVOICE==

RA_INTERFACE_LINES_ALL RILA,
RA_INTERFACE_DISTRIBUTIONS_ALL RIDA

==TRANSACTIONS==

RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCTLA

RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID

==CASH RECEIPTS==

AR_PAYMENT_SCHEDULES_ALL APSA,
AR_CASH_RECEIPTS_ALL ACRA

==SUB LEDGER ACCOUNTING==

XLA_AE_HEADERS XAH (XAH.AE_HEADER_ID=XAL.AE_HEADER_ID,XAH.APPLICATION_ID=XAL.APPLICATION_ID)
XLA_AE_LINES XAL, (XAL.APPLICATION_ID=XDL.APPLICATION_ID,XAL.AE_LINE_NUM=XDL.AE_LINE_NUM)
XLA_EVENTS XE, (XAH.APPLICATION_ID=XE.APPLICATION_ID,XAH.EVENT_ID=XE.EVENT_ID)
XLA_TRANSACTION_ENTITIES XTE, (XAH.APPLICATION_ID=XTE.APPLICATION_ID,XAH.ENTITY_ID=XTE.ENTITY_ID)
XLA_DISTRIBUTION_LINKS XDL (XDL.APPLICATION_ID=XAH.APPLICATION_ID,XDL.AE_HEADER_ID=XAH.AE_HEADER_ID)

==GENERAL LEDGER ==

GL_JE_BATCHES GJB, GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
GJ_JE_HEADERS GJH, GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
GL_JE_LINES GJL, GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID,
(GJL.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID,GJL.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE)
(GJL.JE_HEADER_ID=GIR.JE_HEADER_ID,GJL.JE_LINE_NUM=GIR.JE_LINE_NUM)
GL_BALANCES GB, GB.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID,
GL_CODE_COMBINATIONS GCC,(GCC.CODE_COMBINATION_ID=PRDA.
CODE_COMBINATION_ID,GCC.CODE_COMBINATION_ID=PDA.CODE_COMBINATION_ID)
GL_IMPORT_REFERENCES GIR
(GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID,GIR.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE)

==MOVE ORDER TABLES==

MTL_TXN_REQUEST_HEADERS MTRH
MTL_TXN_REQUEST_LINES MTRL
MTL_MATERIAL_TRANSACTIONS MMT
MTL_DEMAND MD
MTL_SYSTEM_ITEMS_B MSIB
MTL_ONHAND_QUANTITIES MOQ

MTRH.HEADER_ID=MTRL.HEADER_ID
MSIB.ORGANIZATION_ID=MMT.ORGANIZATION_ID
MSIB.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID
MSIB.ORGANIZATION_ID=MOQ.ORGANIZATION_ID
MSIB.INVENTORY_ITEM_ID=MOQ.INVENTORY_ITEM_ID

======================P2P CYCLE=====================

FIRST REQUISITION

ENTER THE ITEM,QUANTITY,NEED BY DATE->SAVE IT
THEN COPY THE REQUISITION NO:14318

SELECT PRHA.SEGMENT1 "REQ_NO",PRHA.AUTHORIZATION_STATUS
FROM
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA
--PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
--AND   PRLA.REQUISITION_LINE_ID=PRD.REQUISITION_LINE_ID
AND PRHA.SEGMENT1='6037'

AT HEADERS LEVEL: O/P IS REQ_NO:14318,PRHA.AUTHORIZATION_STATUS=INCOMPLETE

===APPROVING THE REQUISITION==

SELECT PRHA.SEGMENT1 "REQ_NO",PRHA.AUTHORIZATION_STATUS,PRD.DISTRIBUTION_ID,PRD.DISTRIBUTION_NUM
FROM
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRD
WHERE PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND   PRLA.REQUISITION_LINE_ID=PRD.REQUISITION_LINE_ID
AND PRHA.SEGMENT1='14318'

O/P:O/P IS REQ_NO:14318,PRHA.AUTHORIZATION_STATUS=APPROVED,PRD.DISTRIBUTION_ID=207077,PRD.DISTRIBUTION_NUM=1

===CREATE PUCHASE ORDER==

GO TO PURCHASE ORDERS->PURCHASE ORDERS->

GIVE THE SUPPLIER:DELL,ITEM,QUANTITY,NEED BY DATE,

GO TO SHIPMENTS->MORE->THER U CAN SEE THE MATCH APPROVE LEVEL(2-WAY(PO,INVOICE),3-WAY(PO,RECEIPT,INVOICE),
4-WAY(PO,RECEIPT,INVOICE,INSPECTION) WITH IN THE TOLLERENCE TIME)
GO TO DISTRIBUTIONS -> IF U WANT TO SHIP THE ITEM TO MORE THAN ONE SHIP TO ADDRESS

SAVE IT
THEN A PURCHASE ORDER NO WILL GENERATE :6040

NEXT APPROVE IT

SELECT PHA.SEGMENT1 "PO_NO",PHA.AUTHORIZATION_STATUS,PLA.LINE_NUM,PHA.PO_HEADER_ID,PLLA.PO_LINE_ID,PLLA.LINE_LOCATION_ID
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA
WHERE PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND PHA.SEGMENT1='6037'

8460,ERS-8460-163693,5834774
Transfer Journal Entries to GL,Journal Import,Open Account Balances Data Manager,TB Worker 1 (Open Account Balances Data Manager Worker Process)

O/P:PO_NO:6040,AUTHORIZATION_STATUS:APPROVED,LINEE_NUM:1,PO_HEADER_ID:110486,PO_LINE_ID:173415,LINE_LOCATION_ID:264561

==RECEIVING->RECEIPTS->SELECT ORGANIZATION==

ENTER THE PURCHASE ORDER NO:6040 ->FIND

CHECK THE LINE,ENTER THE SUB INVENTORY->SAVE->THE CLICK ON HEADER AND SAVE THE RECEIPT NO:8460

SELECT DISTINCT RSH.RECEIPT_NUM,RSL.AMOUNT,RSH.SHIPMENT_HEADER_ID
FROM
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_REQUISITION_LINES_ALL PRLA,
PO_HEADERS_ALL PHA,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
RCV_TRANSACTIONS RT,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND PHA.PO_HEADER_ID=RT.PO_HEADER_ID
AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND PHA.PO_HEADER_ID=PDA.PO_HEADER_ID
AND PDA.PO_DISTRIBUTION_ID=RSL.PO_DISTRIBUTION_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND RSH.RECEIPT_NUM='8460'
--AND PHA.SEGMENT1='6040'

===PAYABLES VISION OPERATIONS==

GO TO INVOICES->INQUIRY->GIVE THE PO NUMBER->FIND->COPY THE INVOICE NO:ERS-8460-163753

THEN GO TO THE INVOICES->ENTRY ->VIEW->QUERY->ENTER THE INVOICE NO:ERS-8460-163753->VIEW->QUERY

THEN YOU MUST CHECK THE ACTIONS->VALIDATE->OK(STATUS WILL BE VALIDATED)
THEN ACTIONS CREATE ACCOUNTING(FINAL)->OK(HERE ACCOUNTING WILL BE CREATED FOR THE PERTICULAR TRANSACTION)
THEN ACTIONS PAY IN FILL->OK->IT WILL GO TO THE NEW WINDOW->THERE GIVE THE PAYMENT DATE->10-APR-2012,BANK ACCOUNT:AP/AR NETTING,
PAYMENT METHOD:AUTOMATICALLY IT WILL POP OUT,PAYMENT PROCESS PROFILE->ISABEL FOREIGN->SAVE IT

===THEN REQUEST FOR THE TRANSFER TO GENERAL LEDGER->
PARAMETERSLEDGER:Vision Operations (USA)
END DATE:10-APR-2012,
POST IN GENERAL LEDGER:NO
OK->SUBMIT->REQUEST->FIND->

IN BACKGROUND:Format Payment Instructions with Text Output,Transfer Journal Entries to GL,Journal Import,
Open Account Balances Data Manager,TB Worker 1 (Open Account Balances Data Manager Worker Process),
WILL RUN IN BACKGROUND

COPY THE JOURNAL IMPORT:5861787

===GO TO GENERAL LEDGER VISION OPERATIONS==

JOURNALS->ENTER->LEDGER:%5861787%->FIND->POST THE JOURNAL LEDGER->REVIEW THE JOURNAL LEDGER->
BOTH DEBIT AND CREDIT MUST MATCH

IN BACKGROUND:Posting: Single Ledger WILL RUN

INVOICE TABLES=
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA

==ACCOUNTING ENTRIES TABLES==

AP_ACCOUNTING_EVENTS_ALL ACEA,
AP_AE_HEADERS_ALL AAHA,
AP_AE_LINES_ALL AALA

==PAYMENT TABLES==

AP_INVOICE_PAYMENTS_ALL AIPA,
AP_PAYMENT_SCHEDULES_ALL APSA,
AP_CHECKS_ALL ACA,
AP_CHECK_FORMATS ACF,
AP_BANK_ACCOUNTS_ALL ABAA,
AP_BANK_BRANCHES ABB,
AP_TERMS APT

===COMPLETE P2P CYCLE==

=====PROCURE TO PAY QUERY==

WITH OUT RECEIPTS

SELECT REQH.SEGMENT1 "REQ_NUM",REQH.AUTHORIZATION_STATUS "REQ_STATUS",
POH.PO_HEADER_ID,POH.SEGMENT1 "PO_NUM",POH.AUTHORIZATION_STATUS "PO_STATUS",I.INVOICE_NUM,I.INVOICE_ID,
I.INVOICE_AMOUNT,I.AMOUNT_PAID,I.VENDOR_ID,I.VENDOR_CONTACT_ID,I.VENDOR_SITE_ID,P.CHECK_ID,C.CHECK_NUMBER,H.GL_TRANSFER_FLAG,
H.PERIOD_NAME
FROM
AP_INVOICES_ALL I,
AP_INVOICE_DISTRIBUTIONS_ALL INVD,
PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_DISTRIBUTIONS_ALL POD,
AP_SUPPLIERS V,
PO_REQUISITION_HEADERS_ALL REQH,
PO_REQUISITION_LINES_ALL REQL,
PO_REQ_DISTRIBUTIONS_ALL REQD,
AP_INVOICE_PAYMENTS_ALL P,
AP_CHECKS_ALL C,
AP_AE_HEADERS_ALL H,
AP_AE_LINES_ALL L
WHERE 1=1
AND I.VENDOR_ID=V.VENDOR_ID
AND C.CHECK_ID=P.CHECK_ID
AND P.INVOICE_ID=I.INVOICE_ID
AND REQH.REQUISITION_HEADER_ID=REQL.REQUISITION_HEADER_ID
AND REQD.REQUISITION_LINE_ID=REQL.REQUISITION_LINE_ID
AND POD.REQ_DISTRIBUTION_ID=REQD.DISTRIBUTION_ID
AND POD.PO_HEADER_ID=POH.PO_HEADER_ID
AND POD.PO_DISTRIBUTION_ID=INVD.PO_DISTRIBUTION_ID
AND INVD.INVOICE_ID=I.INVOICE_ID
AND I.SOURCE='AP_INVOICES'
AND REQH.SEGMENT1=''
AND POH.SEGMENT1=''
AND I.INVOICE_NUM=''
AND C.CHECK_NUMBER=''
AND I.VENDOR_ID=''

======P2P QUERY WITH RECEIPTS==

SELECT
FROM
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_DISTRIBUTIONS_ALL PDA,
PO_VENDORS PV,
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
AP_AE_HEADERS_ALL AAHA,
AP_AE_LINES_ALL AALA
WHERE 1=1
AND AIA.VENDOR_ID=PV.VENDOR_ID
AND ACA.CHECK_ID=AIPA.CHECK_ID
AND AIPA.INVOICE_ID=AIA.INVOICE_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND PDA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PHA.PO_HEADER_ID=RT.PO_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID(+)
AND RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RT.TRANSACTION_TYPE='RECEIVE'
AND RT.SOURCE_DOCUMENT_CODE='PO'
AND PLA.PO_LINE_ID=RT.PO_LINE_ID
AND PDA.PO_DISTRIBUTION_ID=RT.PO_DISTRIBUTION_ID
AND PDA.PO_DISTRIBUTION_ID=AIDA.PO_DISTRIBUTION_ID
AND AIDA.INVOICE_ID=AIA.INVOICE_ID
AND AAHA.AE_HEADER_ID=AALA.AE_HEADER_ID
AND AALA.SOURCE_TABLE='AP_INVOICES'
AND AALA.SOURCE_ID=AIA.INVOICE_ID
AND PHA.SEGMENT1=''
AND PRHA.SEGMENT1=''
AND AIA.INVOICE_NUM=''
AND ACA.CHECK_NUMBER=
AND RSH.RECEIPT_NUM=
AND PV.VENDOR_ID=

==PURCHASE ORDER TABLES WITH LINKS==
PO_REQUISITION_HEADERS_ALL   PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
PO_VENDOR_CONTACTS PVC,
PER_ALL_PEOPLE_F PAPF
WHERE PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND   PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND   PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND   PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND   PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND   PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND   PHA.PO_HEADER_ID=PDA.PO_HEADER_ID
AND   PHA.VENDOR_ID=PV.VENDOR_ID
AND   PVS.VENDOR_SITE_ID=PHA.VENDOR_CONTACT_ID
AND   PVC.VENDOR_CONTACT_ID=PHA.VENDOR_CONTACT_ID
AND   PAPF.PERSON_ID=PHA.AGENT_ID

IMPORTANT COLUMNS IN THE TABLES

PO_REQUISITION_HEADERS_ALL PRHA
PRHA.REQUISITION_HEADER_ID
PRHA.PREPARER_ID
PRHA.SEGMENT1 "REQQ_NO"

PO_REQUISITION_LINES_ALL PRLA
PRLA.REQUISITION_LINE_ID
PRLA.REQUISITION_HEADER_ID
PRLA.LINE_NUM
PRLA.UNIT_PRICE
PRLA.QUANTITY
PRLA.QUANTITY_CANCELLED
PRLA.QUANTITY_DELIVERED
PRLA.QUANTITY_RECEIVED

PO_REQ_DISTRIBUTIONS_ALL PRDA
PRDA.DISTRIBUTION_ID
PRDA.REQUISITION_LINE_ID
PRDA.SET_OF_BOOKS_ID
PRDA.REQ_LINE_QUANTITY
PRDA.REQ_LINE_AMOUNT

PO_HEADERS_ALL PHA
PHA.PO_HEADER_ID
PHA.AGENT_ID
PHA.SEGMENT1
PHA.VENDOR_ID
PHA.VENDOR_SITE_ID
PHA.SHIP_TO_LOCATION_ID
PHA.BILL_TO_LOCATION_ID

PO_LINES_ALL PLA
PLA.PO_LINE_ID
PLA.PO_HEADER_ID
PLA.ITEM_DESCRIPTION
PLA.UNIT_PRICE
PLA.QUANTITY

PO_DISTRIBUTIONS_ALL PDA
PDA.PO_DISTRIBUTION_ID
PDA.PO_HEADER_ID
PDA.PO_LINE_ID
PDA.QUANTITY_ORDERED
PDA.REQ_DISTRIBUTION_ID

PO_VENDORS PV
PV.VENDOR_ID
PV.VENDOR_NAME
PV.SEGMENT1
PV.SET_OF_BOOKS_ID

PO_VENDOR_SITES_ALL PVS
PVS.VENDOR_SITE_ID
PVS.VENDOR_ID
PVS.VENDOR_SITE_CODE
PVS.ADDRESS_LINE1
PVS.CITY
PVS.PHONE

PO_VENDOR_CONTACTS PVC
PVC.VENDOR_SITE_ID
PVC.VENDOR_CONTACT_ID
PVC.PHONE

PER_ALL_PEOPLE_F PAPF
PAPF.PERSON_ID
PAPF.BUSINESS_GROUP_ID
PAPF.FULL_NAME
PAPF.EMAIL_ADDRESS

===========================END OF P2P==========================================

10037522,13,847.34

Infolinks

Monday, 13 May 2013

Query that covers P2P life cycle

P2P Cycle

Flow of Order Management