How to Cancel Inprocess PO

This is workaround to cancel inprocess PO using below given script.
========================================================================
create or replace procedure apps.xx_cancel_inprocess_po
(p_po_num varchar2,
p_org_id number)
is
l_header_id number;
--
BEGIN
--
SELECT po_header_id into l_header_id
FROM po_headers_all WHERE segment1 =p_po_num;
--
UPDATE po_headers_all c
SET c.authorization_status ='approved',
c.approved_flag ='Y',
c.approved_date =SYSDATE,
c.cancel_flag ='Y',
c.closed_code ='closed',
c.closed_date =SYSDATE
WHERE segment1 =p_po_num
and org_id =p_org_id;
COMMIT;
--
UPDATE po_headers_archive_all c
SET c.authorization_status ='approved',
c.approved_flag ='Y',
c.approved_date =SYSDATE,
c.cancel_flag ='Y',
c.closed_code ='closed',
c.closed_date =SYSDATE
WHERE segment1 =p_po_num
and org_id =p_org_id;
--
UPDATE po_lines_all c
SET
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
UPDATE po_lines_archive_all c
SET
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
UPDATE po_line_locations_all c
SET
c.approved_date =SYSDATE,
c.approved_flag ='Y',
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_by=-1,
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
UPDATE po_line_locations_archive_all c
SET
c.approved_date =SYSDATE,
c.approved_flag ='Y',
c.cancelled_by =-1,
c.cancel_flag ='Y',
c.cancel_reason ='OLD PO',
c.cancel_date =SYSDATE,
c.closed_code ='closed',
c.closed_date =SYSDATE,
c.closed_flag='n',
c.closed_by=-1,
c.closed_reason ='UPDATE closure status rolled up'
WHERE po_header_id =l_header_id;
--
COMMIT;
--
END ;
/
=========================================================================

iExpense Setup in Oracle

iExpenses Setups In R12

Context Definition :
Navigation :Internet Expenses Setup and Administration à Internet Expenses Setup à Context

• Navigate to the web page by following the navigation given above.
• Select the operating units for which iExpenses setups need to be done.
• Click ‘Add to Selections’ for the policy and location setups to be applicable to the selected operating units.

Accounting :

Navigation: Internet Expenses Setup and Administration à Internet Expenses Setup à Accounting

• Navigate to the web page by following the navigation given above.
• Select enabled for Accounting segment (entity, Cost center, Account etc) you want to update default value. 

Exchange Rate Defaults :

Navigation : Internet Expenses Setup and Administration à Internet Expenses Setup à Policy à  Exchange Rates

For foreign currency transactions, the exchange rate defaults need to be set in order to covert the amounts into functional currency.   

Setup : Navigate to the web page by following the navigation given above.

Click ‘Define Defaults’.

Enable the defaults, default exchange rate, exchange rate types etc.

Click ‘Apply’.

Future Date Tolerance :

Navigation : Internet Expenses Setup and Administration à Internet Expenses Setup à General à  Options à Future Expenses

To set tolerance for expense claims where in employee enters a future date.

Navigate to the web page by following the navigation given above.

IExpense DFF:

Navigation : System Administrator à Application à Flexfield à Descriptive à Segments

Descriptive Flexfields can be used to capture additional information on various screens for iexpense.

• Select Application Name as 'Payables'
• Select Title as 'Expense Report'
• Click on segment Button
• Add value for DFF attribute.

Expense Template:

Navigation : Payables Manager  à Oracle Payables à Setup à Invoice à Expense Report Templates

The templates setup here will appear in iExpenses when employees want to raise expense claims. The ‘Expense Item’ fields will appear as a drop down once the templates are selected and the policies are enforced on each of these.  

• Follow the navigation to the Expense Report Template window as mentioned above.
• Enter the operating unit for which the template is being setup.
• Enter the template name, description and Enable these for iExpenses.
• Click ‘Save‘.

Define Audit Rule:

Navigation : Internet Expenses Setup and Administration à Internet Expenses Setup à Audit

 Navigate to the web page by following the navigation given above.

• Select value for drop down value " Assign audit queue auditor for an expense report" as " After Manager approval".
• Press Apply
• Now assign the rule to all operating units
• Press Update

Define Notification Rule :

Navigation :Internet Expenses Setup and Administration à Internet Expenses Setup à Audit à Notifications 

• Navigate to the web page by following the navigation given above.
• Enter Rule Name
• Select Value for "Notify Individuals" as " when receipt are received" 
• Select value for overdue Receipt notification 
• Press apply
• Now assign the rule to all operating units/ Particular Operating Unit. 
• Press Update

Define Holds :Define Holds and assign to each operating unit.   

Navigation : Internet Expenses Setup and Administration à Internet Expenses Setup à Audit à Holds

• Navigate to the web page by following the navigation given above.
• Select value for individual Payment Hold, choose one radio button from Until receipt received or when receipt are overdue.
• Press apply
• Now assign the rule to all operating units
• Press Update

Define MOAC

• Define Responsibilities 
• Define Business Group.
• Define Ledger.
• Define Operating Unit.
• Define Security Profile.
• Run Security List Maintenance Program.
• Assign Security Profile to Responsibilities.
• Run Replicate Seed Data Program. 

--
Define Job :
Navigation: HRMS --> Work structures --> Job --> Description.

• Click on New button.
• Enter the Job Name and Code.
• Save.

--
Define Position :

• Click on New button
• Enter mandatory field like Position number and name ,type, Organization,job etc
• Save.

--
Define Employee :
Navigation: HRMS --> People --> Enter and Maintain.

• Click on New.
• Enter Mandatory fields like first name, last name etc
• Save and click on Assignments.
• Enter Mandatory fields like job name position name.
• Save.
• Click left lov button and select the purchase order information.
•  Enter the primary ledger name and default expenses account.
• Save.

--
Define Financial Options:
Navigation: Payables --> Setup --> Options --> Financial.
--
Define Expenses Template:
Navigation: Payables --> Setup --> Invoice --> Expenses Report Templates.
--
Define Payable Options:  
Navigation: Payables --> Setup --> Options --> Payable Options.
--
Assign Cost Center Flexfiedl qualifier 
Navigation: Payables --> Setup --> Flexfield --> Key --> Segments.
--
Define Signing Limits.
Navigation: Payables --> Employees --> Signing Limits.
--
Assign Profile Option to IExpenses Responsibility.
Navigation: System Administrator --> Profile --> Systems.

WebAdi in Oracle apps

WEBAdi :

Web Applications Desktop Integrator is a tool that leverages the standard desktop applications to perform some of the Oracle E-Business Suite task. The Web ADI brings Oracle E-Business suite to the desktop where familiar desktop tool like Excel can be used to create spreadsheet, enter and modify data in the spreadsheet and finally upload the data into the Oracle Applications. Web ADI can be very useful for the users who are experienced with Excel and will like to use Excel to enter the data related to Oracle Applications. These users can use the special features of excel i.e. copy, paste, drag cells to increment and various arithmetic functions to optimize their business task and increase their productivity.

Web Application Desktop Integrator or Web ADI is a tool that brings Oracle E-Business Suite functionality to a spreadsheet, where familiar data entry and modeling techniques can be used to complete Oracle E-Business Suite tasks.

It is one of oracle's tool which connects Oracle Apps with Excel. Helps in uploading of Journal data etc. through excel. You can also create custom WEB ADI and write a custom procedure to do Data Migration through excel into temporary tables through WEB ADI.

You can create your own layouts and even create your own integrator through assigning WEBAdi responsibility.

Why to use WebAdi :

• Increase Productivity
• Minimal Training
• Useful in Large transactions
• Improved user acceptance

Setting in Excel for WebAdi :

For Excel Macro Setting please follow below given steps.

1. Go to Arrow above in excel file and click on “More commands” , Please refer below given screenshot.

2.  It will open Excel Option window

3. Kindly click on Trust Center in Excel Option and then click on Trust Center Setting Button

4.  In This Select Macros Setting Option :

Select "Disable all Macros with notification" .

And in Developer Macro setting select "Trust access to the VBA project Object Model"



WEB Browser Settings for WebAdi :

Go to Internet Explorer Web browser

Tool -> Internet Options

2. Click on Security Tab and then click on Custom Level

3. In this Enabled option for "Allow status bar updates via script"



3. Click on OK , It will show you warning message Click 'Yes' to change setting.

By using above two setup now we can use WebAdi functionality in Oracle apps.

Navigation :

GL SuperUser Responsibility -> Journals -> Launch Journal Wizards

Enter You Layout Name for which you want to upload data.

• Budgets - Multiple
• Budgets - Single
• Foreign Actual - Multiple
• Foreign Actual - Single
• Functional Actual - Multiple
• Functional Actual - Single
• The options are therefore:
• Functional or Foreign currency
  • Actual or Budget Balances
  • Multiple or Single spreadsheet

Click on Create Documents

Enter data in opened Excel sheet  Like given Below

e.g.

Balance Type  : Actual Or Budget

Database :  XXVIS12

Data Access Set : XX_PL

Chart of Account : XX_COA

Ledger : XX_Ledger_name

Category : Billed Unearned

Source : XX_Financial

Currency : USD

Account Date: 15-07-2015

Journal : Unearned Revenue

Journal : Standard

In Line details enter code combinations, debit amount  Credit amount and Line description and Journal Type as Standard.

Once you upload this by it will show you :-) smiley infront of successfull uploaded lines. Please see below given screenshot.

After this posting, you can view the transaction in General Ledger by running program for Trial Balance by passing segment value for account or company.

REF Cursor in Plsql

REF CURSOR : A REF Cursor or Cursor Variable is just reference to static cursor or handle to static cursor. It allows user to pass this refrence to same cursor in entire program which needs access to the cursor.

Type of REF Cursor :

Strong Ref Cursor
Weak Ref Cursor

Strong Ref Cursor : In this type of cursor while defining ref cursor you also define what cursor can return.
If Ref cursor return any thing other than Return type it will raise exception 'ROWTYPE MISMATCH'
e.g.  TYPE xxemp_ref_cur is REF CURSOR
         RETURNING emp%type;

Weak Ref Cursor : Ref cursor called as weak Ref cursor if it does not define what data type it will return.
e.g.  TYPE xxemp_ref_cur is REF CURSOR;

AP to GL Drilldown

Lets see how we can Drilldown from Account Payables Invoice details to GL Journal Detail.

xla_entity_id_mappings : This is most important table for SLA as it includes all the join condition between xla tables and base table. It will gives you column name mapping between xla table with base table. You just need to pass entity code and you get all details.
e.g. Entity code for Account Payable Invoice is 'AP_INVOICES'. Please refer below given query.

select * from xla_entity_id_mappings

where entity_code like 'AP_INVOICES';

1. When we create invoices it will hit below given table in account Payables.

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

e.g. :

Invoice Header:

select * from ap_invoices_all

where invoice_num='IN0097158'; -- Copy the value for Invoice_id for future rerfrence 16211228

Invoice Line : 

select * from ap_invoice_lines_all

where invoice_id=16211228;

Invoice Distributions :

select * from ap_invoice_distributions_all

where invoice_id=16211228;

SLA Tables :

select * from XLA.xla_transaction_entities

where source_id_int_1=16211228

and transaction_number='IN0097158'; -- From this table copy value for Entity id 580427218

--  

SELECT * FROM XLA_AE_HEADERS

WHERE ENTITY_ID=580427218;  --- From this table copy value Event id 582978337 and ae_header_id value 126276903

-- 

select * from xla_events

WHERE EVENT_ID=582978337;

-- 

select * from xla_ae_lines

WHERE AE_HEADER_ID=126276903;

--

SELECT *

from gl_import_references gir

where gir.gl_sl_link_id in

(select gl_sl_link_id from xla_ae_lines

WHERE AE_HEADER_ID=126276903); -- from this table you will get detail for je_header_id, je_line_num, je_batch_id

--

select * from gl_je_batches

where je_batch_id= 5709025;

-- 

select * from gl_je_headers

where je_header_id=6780748;

--

select * from gl_je_lines

where je_header_id=6780748

and je_line_num in (9619,6734);

--

  

select

gjh.name Journal_name,

gjb.name Batch_name,

gjl.description Line_desc,

gjc.je_category_name,

gjs.je_source_name,

xl.entered_dr,

xl.entered_cr,

xl.accounted_dr,

xl.accounted_cr

from gl_import_references gir,

gl_je_headers gjh,

gl_je_batches gjb,

gl_je_lines gjl,

xla_ae_lines XL,

gl_je_categories gjc,

gl_je_sources gjs

where gir.gl_sl_link_id in

(select gl_sl_link_id from xla_ae_lines

WHERE AE_HEADER_ID=126276903)

and gir.je_batch_id=gir.je_batch_id

and gjh.je_header_id=gir.je_header_id

and gjl.je_line_num=gir.je_line_num

and gjl.je_header_id=gjh.je_header_id

and gjh.je_batch_id=gjb.je_batch_id

and gjh.actual_flag='A'

AND gjh.status='P'

and xl.gl_sl_link_id=gir.gl_sl_link_id

and gjh.je_category=gjc.je_category_name

and gjs.je_source_name=gjh.je_source    ;

P2P : Procure to Pay Cycle in Oracle Apps

P2P CYCLE: 

• INVENTORY=>
• REQUIESTITION=>
• RFQ=>
• QUOTATION=>
• QUOTATION ANALYSIS=>
• PURCHASE ORDER =>
• INVOICE =>
• VALIDATE INVOICE =>
• PAYMENT =>
• TRANSFER TO GL =>
• UNPOSTED JOURNAL=>
• POST THE JOURNAL IN GL

DETAIL TECHNICAL DESCRIPTION ABOUT P2P CYCLE :

1. Creation of Requisition :

When organization's any item stock ended they raise requisition to get quotation against that item from diffrent vendor.

Requisition raise in Purchase order Responsibility.

Navigation : Purchase Order Superuser -> Requisition -> Requisitions

Backend Table Detail :

a) PO_REQUISITION_HEADERS_ALL (Requisition Header Information) :

Select *

from PO_REQUISITION_HEADERS_ALL

where segment1 = '1234' -- segment1 will be rerequisition number

b) PO_REQUISITION_LINES_ALL (Requisition Lines Information ):

select *

from PO_REQUISITION_LINES_ALL

where requisition_header_id =

(Select requisition_header_id

from PO_REQUISITION_HEADERS_ALL

where segment1 = '1234')

Kindly press Approve button which send requisition for approval. Once it get approved from Manager its status will get change to 'APPROVED' from 'INCOMPLETE'

2. Creation of Purchase Order (PO) against Requisition :

PO we can either create manually or using auto create option in Purchase order responsibility.

Navigation : Purchase order Responsibility -> Purchase Orders -> Purchase Orders

-> Click on Auto Create option on Purchase order screen

-> Search requisition '1234' detail

-> Click Automatic

-> it will create Purchase Order.

-> Kindly enter shipment details with ship-to details

-> Save Record and Submit For approval. Its status become APPROVED once approver approve PO.

Backend Table Details :

a) PO_HEADERS_ALL (Po Header Information) :

Select *

From PO_HEADERS_ALL

where segment1='5700' -- segment1 is Po Number

b) PO_LINES_ALL (PO Line Level information) :

Select *

From PO_LINES_ALL

where Po_header_id =

( Select po_header_id

From PO_HEADERS_ALL

where segment1='5700')

c) PO_LINE_LOCATIONS_ALL (PO shipment schedule information ):

Select *

From PO_LINE_LOCATIONS_ALL

Where Po_header_id =

( Select Po_header_id

From PO_HEADERS_ALL

where segment1='5700')

d) PO_DISTRIBUTIONS_ALL (Distribution Information) :

Select *

From PO_DISTRIBUTIONS_ALL

Where Po_header_id =

( Select Po_header_id

From PO_HEADERS_ALL

where segment1='5700')

3. Receiving Against Purchase Order
Purchasing Responsbility change the receiving Organization before we create receipt to which org you supposed to ship the goods.
You can do this by entering PO number against which we want to recive.

Navigation : Purchasing Responsibility -> Receiving -> Receipt
-> Enter PO Number and click on Find
-> Receive the good against PO Number

Backend Table Details :
a) RCV_SHIPMENT_HEADERS (Receipts Header Information )
Select * from RCV_SHIPMENT_HEADERS
where po_header_id =

( Select Po_header_id

From PO_HEADERS_ALL

where segment1='5700');

b) RCV_SHIPMENT_LINES (Receipts Lines Information)
Select * from RCV_SHIPMENT_LINES
where shipment_header_id =
(Select shipment_header_id
from RCV_SHIPMENT_HEADERS
where po_header_id =

( Select Po_header_id

From PO_HEADERS_ALL

where segment1='5700')

);

c) RCV_TRANSACTIONS (Receipt Transaction Information)
Select * from RCV_TRANSACTIONS
where po_header_id =

( Select Po_header_id

From PO_HEADERS_ALL

where segment1='5700');

4. Creation of Invoice in Account Payables

Now you need to switch Responsibility from Purchasing to Payables (AP Module)

Navigation :  Payables Responsibility -> Invoices -> Invoice
It will takes you to invoice workbench form.
Enter details on header like Invoice type (Standard, DM, CM etc), Supplier Name, Supplier Site etc.
-> Match invoice against PO Number or Receipt Number.
-> Initially its status will be Not Validated.
-> Go to Action  -> Check Validate and press OK
-> See for Hold, If holds present then Release Hold.
-> Click on check box  Force Approval. Press OK.
-> Click on 'Create Accounting' Check box and press OK.
Now Invoice is ready for Payments.

Backend Table Details :
a) AP_INVOICES_ALL (Invoice Header information)
Select * From AP_INVOICES_ALL
where po_header_id =

( Select Po_header_id

From PO_HEADERS_ALL

where segment1='5700');

b) AP_INVOICE_LINES_ALL (Invoice Lines Information)
 Select * from AP_INVOICE_LINES_ALL
where invoice_id =
(Select invoice_id
 From AP_INVOICES_ALL
where po_header_id =

( Select Po_header_id

From PO_HEADERS_ALL

where segment1='5700'));

c) AP_INVOICE_DISTRIBUTIONS_ALL (Invoice Distributions information)
 Select * from AP_INVOICE_DISTRIBUTIONS_ALL
where invoice_id =
(Select invoice_id
 From AP_INVOICES_ALL
where po_header_id =

( Select Po_header_id

From PO_HEADERS_ALL

where segment1='5700'));

5. Payments done against Invoice in Payables

Navigation : Payables Responsibility -> Payments -> Payments

Backend Table Details :
a) AP_PAYMENT_SCHEDULES_ALL (Payment Details)
 Select *
from AP_PAYMENT_SCHEDULES_ALL
WHERE invoice_id =
(select invoice_id
from AP_INVOICES_ALL
WHERE po_header_id =

( Select Po_header_id

From PO_HEADERS_ALL

where segment1='5700'));

b) AP_CHECKS_ALL (Checks Details)
Select * from AP_CHECKS_ALL
where Check_num='Chq-1234';

6. Payables Transfer To GL
Kindly Run Payables Transfer To GL Program from Payables Responsibilty.
It will transfer Payables Data into Gl modules.

Backend Tables Details :
a) GL_JE_HEADERS (Journal Headers Information)
 Select * from GL_JE_HEADERS

b) GL_JE_LINES (Jornal Lines Information)
   Select * from GL_JE_LINES

c) GL_JE_BATCHES (Journal Batch Information )
   Select * from GL_JE_BATCHES;