Write message in Log or Out file

Write message in Log or Out file:-
--------------------------------------

When ever, I have to create new package, I normally code a procedure to write any information in the log or Out files of the program.

The following is the Procedure which I use it in any package. This is standard in some companies coding. This save a lot of time and this makes easy to other people who goes through your program.

PROCEDURE write(p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : Procedure writes to the log file or output file
based on type.O=Output File, L=Log File
*************************************************************************/

BEGIN
IF p_type = 'L'
THEN


fnd_file.put_line (fnd_file.log, p_message);

ELSIF p_type = 'O'
THEN

fnd_file.put_line (fnd_file.output, p_message);


END IF;
END write;

The above write procedure can be used in other Procedure/Function in the package to write any information in the Log or Out files.

PROCEDURE main(errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_par1 IN NUMBER
)
IS
v_errbuf VARCHAR2(1000) := NULL;
v_retcode NUMBER := 0;
v_file_name VARCHAR2(100);
BEGIN

v_retcode := 0;

v_file_name := fnd_profile.value('XYZ');


IF v_file_name IS NULL
THEN
write('O','Profile XYZ is not defined or the value is not set');
retcode := 2;
RETURN;
END IF;
END;

Note:- In the above Procedure, I am using the write Procedure and returning 2 for the retcode (0 - Complete, 1- Warning and 2 will be for Error).

Note:- This is one time process and you will realise, how much helpful it will be when ever you have to right something in log or out file.

More then information, this post is more of suggestion. Hope this post will help you make your code easy.

Register the table in Application from backend

Register the table in Application from backend:-
-------------------------------------------------------

Note:- Create this Procedure in APPS schema. This Procedure will register the table and it's column in Applications.

Note:- The procedure has two IN paramters. First would would have the Application_Short_Name where you want to register the table and Second Paramter would have the table name you want to register.

Note:- Even through, if table is not created and we are trying to register some table name with this procedure, then it will register the table name but with no columns in it.

Note:- After registering the table, if you want to confirm it from Oracle Application Front end then, you can check in Application Developer Responsibility.

Navigation:-

Application Developer-->Application-->Database-->Table

Why you need to Register any table in Application?
A) It is important to know, why we need to register any table in the Oracle Application.
If you want to use any table in value set or Oracle Alerts, then you need to
register that table in the Application.

Note:- All we need to give the table name. Rest the values which are needed to register the table or Column in applications would be taken from database automatically. This saves lot of developer time.

I hope you find this post useful in your development work. For any issues or suggestion related to this post, please leave your comment for this post. I will try to reply as soon as possible.

CREATE OR REPLACE PROCEDURE proc_ad_ad
(ap_sname VARCHAR2, ptable_name VARCHAR2)
IS
CURSOR c1
IS
SELECT ROWNUM, column_name, data_type, data_length
FROM all_tab_columns
WHERE table_name = upper(ptable_name);

va c1%ROWTYPE;
BEGIN
ad_dd.register_table (ap_sname, ptable_name, 'T');

OPEN c1;

LOOP
FETCH c1
INTO va;

EXIT WHEN c1%NOTFOUND;
ad_dd.register_column (ap_sname,
ptable_name,
va.column_name,
va.ROWNUM,
va.data_type,
va.data_length,
'Y',
'N'
);
DBMS_OUTPUT.put_line ( va.ROWNUM
' '
va.column_name
' '
va.data_type
' '
va.data_length
);
END LOOP;

CLOSE c1;

COMMIT;
END proc_ad_ad;

Example:-

create table phani (col1 varchar2(10), col2 number, col3 date);

begin
proc_ad_ad('AU', 'Phani');
end;

In the above Example, we have created the table and trying to register the same table in Application using the Procedure.

Submit the Concurrent Program from Backend

Submit the Concurrent Program from Backend:-
-------------------------------------------------------

The following is the Sample Code to Submit the Concurrent Program from the backend.

Note:- This is the Concurrent Program, not the Request Set. To Submit the Request Set from the backend, We have different API.

I have already document, for submitting the request set from the backend in the Following URL.

http://alloracletech.blogspot.com/2008/07/registering-from-backend.html

DECLARE
l_success NUMBER;
BEGIN
BEGIN

fnd_global.apps_initialize( user_id => 2572694, resp_id => 50407, resp_appl_id => 20003);

-- If you are directly running from the database using the TOAD, SQL-NAVIGATOR or --SQL*PLUS etc. Then you need to Initialize the Apps. In this case use the above API to --Initialize the APPS. If you are using same code in some procedure and running directly
--from application then you don't need to initalize.
--Then you can comment the above API.

l_success :=
fnd_request.submit_request
('XXAPP', -- Application Short name of the Concurrent Program.
'XXPRO_RPT', -- Program Short Name.
'Program For testing the backend Report', -- Description of the Program.
SYSDATE, -- Submitted date. Always give the SYSDATE.
FALSE, -- Always give the FLASE.
'1234' -- Passing the Value to the First Parameter of the report.
);
COMMIT;

-- Note:- In the above request Run, I have created the Report, which has one parameter.

IF l_success = 0
THEN
-- fnd_file.put_line (fnd_file.LOG, 'Request submission For this store FAILED' );
DBMS_OUTPUT.PUT_LINE( 'Request submission For this store FAILED' );
ELSE
-- fnd_file.put_line (fnd_file.LOG, 'Request submission for this store SUCCESSFUL');
DBMS_OUTPUT.PUT_LINE( 'Request submission For this store SUCCESSFUL' );
END IF;

--Note:- If you are running directly from database, use DBMS API to display. If you are
-- Running directly from Application, then Use the fnd_file API to write the message
-- in the log file.

END;

I hope the above information is helping you. For any Suggestion or issues with the above, please leave your comments. I will try to reply back as soon as possible.

Flexfields avilable for your Oracle Application Version

Flexfields avilable for your Oracle Application Version:-
-----------------------------------------------------------------

The following Query gives us, all the flexfields avilable for your Oracle Application version.

select faa.APPLICATION_NAME "Application Name", fif.id_flex_name "Flexfield Name", faa.description "Application Description"
from FND_ID_FLEXS fif, FND_APPLICATION_ALL_VIEW faa
WHERE fif.APPLICATION_ID = faa.APPLICATION_ID
order by faa.APPLICATION_NAME;

FlexFields

FlexFields:-
-------------

Flexfields stores main Information of the Organizations. We have many flexfields in different modules.

Note:- The following Flexfields are avilable in the 11.5.8 Oracle Application Version.
-----

You may find more flexfield in the new Oracle apps versions.

To check all the Flexfield avilable for your Oracle Apps version, use the Flexfield Query

Application Name	Flex-field Name	Application Description
CRL Financials Assets	Super Group	CRL Financials - Assets
CRL Financials Assets	Group Asset	CRL Financials - Assets
Oracle Advanced Service Online	AHL Route	Oracle Advanced Service Online
Oracle Advanced Service Online	AHL Operation	Oracle Advanced Service Online
Oracle Assets	Location Flexfield	Oracle Assets
Oracle Assets	Category Flexfield	Oracle Assets
Oracle Assets	Asset Key Flexfield	Oracle Assets
Oracle General Ledger	Accounting Flexfield	Oracle General Ledger
Oracle Human Resources	Soft Coded KeyFlexfield	Oracle Human Resources
Oracle Human Resources	Job Flexfield	Oracle Human Resources
Oracle Human Resources	Grade Flexfield	Oracle Human Resources
Oracle Human Resources	CAGR Flexfield	Oracle Human Resources
Oracle Human Resources	Competence Flexfield	Oracle Human Resources
Oracle Human Resources	Item Contexts Keyflex	Oracle Human Resources
Oracle Human Resources	Personal Analysis Flexfield	Oracle Human Resources
Oracle Human Resources	Position Flexfield	Oracle Human Resources
Oracle Inventory	System Items	Oracle Inventory
Oracle Inventory	Sales Orders	Oracle Inventory
Oracle Inventory	Item Categories	Oracle Inventory
Oracle Inventory	Account Aliases	Oracle Inventory
Oracle Inventory	Item Catalogs	Oracle Inventory
Oracle Inventory	ORACLE_SERVICE_ITEM_FLEXFIELD	Oracle Inventory
Oracle Inventory	Stock Locators	Oracle Inventory
Oracle Payroll	People Group Flexfield	Oracle Payroll
Oracle Payroll	Bank Details KeyFlexField	Oracle Payroll
Oracle Payroll	Cost Allocation Flexfield	Oracle Payroll
Oracle Public Sector Budgeting	PSB Position Flexfield	Oracle Public Sector Budgeting
Oracle Receivables	Territory Flexfield	Oracle Receivables
Oracle Receivables	Sales Tax Location Flexfield	Oracle Receivables
Oracle Receivables	ARTA-Receipt Prof Ident	Oracle Receivables
Oracle Training Administration	Training Resources	Oracle Training Administration

FORALL

FORALL Concept:-
---------------------

FORALL concept help us reducing the Iterations between PL/SQL Block and SQL Engine.

Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then, It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine.

If we have some LOOP and it is looping for 100 times then, this will iterate from 100 times from PL/SQL block to SQL Engine.

Example:-

CREATE TABLE emp_by_dept AS SELECT employee_id, department_id
FROM employees WHERE 1 = 0;
DECLARE
TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
deptnums dept_tab;
TYPE NumList IS TABLE OF NUMBER;
-- The zeros in this list will cause divide-by-zero errors.
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
SELECT department_id BULK COLLECT INTO deptnums FROM departments;
-- SAVE EXCEPTIONS means don't stop if some INSERT fail.
FORALL i IN 1..deptnums.COUNT SAVE EXCEPTIONS
INSERT INTO emp_by_dept
SELECT employee_id, department_id FROM employees
WHERE department_id = deptnums(i);
FOR i IN 1..deptnums.COUNT LOOP
-- Count how many rows were inserted for each department; that is,
-- how many employees are in each department.
dbms_output.put_line('Dept '||deptnums(i)||': inserted '||
SQL%BULK_ROWCOUNT(i)||' records');
END LOOP;
dbms_output.put_line('Total records inserted =' || SQL%ROWCOUNT);
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
WHEN dml_errors THEN -- Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of INSERT statements that failed: ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error #' || i || ' occurred during '||
'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;

BULK COLLECT

BULK COLLECT:-
-------------------

Bulk Collect concept help us reducing the Iterations between SQL Engine and PL/SQL Block.

Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine. And if we have some SELECT statement in the PL/SQL Block, then it will move from SQL Enginer to get the data from SQL Enginer to the PL/SQL Block. For the SELECT statement, we use the BULK COLLECT to record the Iteration from the SQL Enginer to the PL/SQL Block.

If I have to put my self in other word then, I should say, BULK COLLECT will process group of SELECT statements at one short.

Similarly for the DML Operations, we use FORALL Concept, please refer the FORALL Concept for more details.

Note:- This Example is based on the EMP table in the SCOTT schema. Here say, EMP table have 90 Records and if we do it in the Normal way (Without BULK COLLECT concept, then It will iterate SQL enginer to the PL/SQL Block for 90 Times). By using BULK COLLECT, we can completly reduce the Iterations.

In the following Example, I have limited to 10, so for 90 records, this will iterate for 9 times.

Example:-
-----------

declare
type array is table of number index by binary_integer;
l_data array;
cursor c is select empno from emp;
begin
open c;
loop
fetch c bulk collect into l_data limit 10;
if ( c%notfound )
then
dbms_output.put_line
( 'Cursor returned NOT FOUND but array has ' || l_data.count
|| ' left to process' );
else
dbms_output.put_line
( 'We have ' || l_data.count
|| ' to process' );
end if;
exit when c%notfound;
end loop;
close c;
end;

Returning clause with the Bulk collect with the DML opeartions:-
---------------------------------------------------------------------------

DELETE FROM emp WHERE num = 30
RETURNING empno, ename BULK COLLECT INTO p_num, p_ename;

Concurrent program name based on the request ID

Concurrent program name based on the request ID:-
------------------------------------------------------------

With the following query, you can get the concurrent program name based on the Concurrent request ID

Select program from FND_CONC_REQ_SUMMARY_V
where request_id = 'Your request ID';

Operating Unit name based on the Operating Unit ID

Operating Unit name based on the Operating Unit ID:- --------------------------------------------------------------

In the following Query you can get Operating Unit Name based on the Operating Unit ID.

select Business_group_id, Organization_id, name,date_from,date_to, legal_entity_id, set_of_books_id from hr_operating_units where organization_id = 'your Operating Unit ID';

INV Module

In this session you will find all the ready scripts avilable for the INV Module.

Modifier Header ID based on the Modifier Description

Item Catalog, Descriptive Element Value

Inventory code and Inventory Name/description

Department Number, Category Number and Cost of item

On-hand Quatity of one Particular Item in whole Organization

Get all the Assembly completion Lot Numbers

Get the Assembly Details

On-hand inventory information

On-hand Quatity of one Particular Item in whole Organization

INV On-hand Quatity of one Particular Item in whole Organization:-
-----------------------------------------------------------------------------------

The following Query will On-hand Quatity of particular item in the whole Organization

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID';

Example:-

Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;

If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.

select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID'
group by organization_id;

Department Number, Category Number and Cost of item based on the Item ID

Department Number, Category Number and Cost of item based on the Item ID:-
--------------------------------------------------------------------------------------------

The Following Query will give the Department Number, Category Number, Cost of the Item based on the Inventory item ID.

select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 'Your Inventory Item ID'
and organization_id = 'Your Inventory Organization ID' and category_set_id = 1 and rownum = 1)
and cs.organization_id = 'Your Inventory Organization ID'
and cs.cost_type_id = 1
and cs.inventory_item_id = 'Your Inventory Item ID';

Example:-
-----------

select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 122251
and organization_id = 22 and category_set_id = 1 and rownum = 1)
and cs.organization_id = 22
and cs.cost_type_id = 1
and cs.inventory_item_id = 122251;

Get the Inventory code and Inventory Name/description

Get the Inventory code and Inventory Name/description:-
-------------------------------------------------------------------

Note:- Inventory Organization will be represented with the Organization_id column.
We many give some code and name to the Inventory. With the following Query we can know the Code and Name details of all the Inventories in the Organization.

SELECT distinct SUBSTR(loc.location_code, 1, 4) "Inventory Code"
,DECODE(SUBSTR(loc.tax_name, 1, 3)
,'Bay', 'H'
,'Zel', 'Z'
,'KMT', 'Z') || lpad(substr(loc.location_code, 1, 4), 4, '0')|| ' ' ||
SUBSTR(loc.address_line_2, 1, 30) "Inventory Name/Description",
ou.ORGANIZATION_ID "Inventory Organization ID"
FROM hr_locations_all loc,
hr_all_organization_units ou
WHERE loc.location_id = ou.location_id;

Item Catalog, Descriptive Element Value

Catalog and Descriptive element value on the Inventory Item ID of the Item:-
-----------------------------------------------------------------------------------------

Note:- The following Query can get multiple records, since one Item can assoiciate to many Inventory Organizations.

In the following Query, I have commented the organization_id condition and given the rownum = 1 condition.

Catalog is the Flexfield. The values in these also depends upon the Set-up part of the Catalog Flexfield.

MTL_DESCR_ELEMENT_VALUES stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.

SELECT c.segment1 || '-' || a.element_value
FROM mtl_descr_element_values a
,mtl_descriptive_elements b
,mtl_item_catalog_groups c
,mtl_system_items_fvl d
WHERE a.element_name = b.element_name
AND a.inventory_item_id = d.inventory_item_id
-- AND d.organization_id = 'Your Organization ID'
AND b.item_catalog_group_id = d.item_catalog_group_id
AND b.item_catalog_group_id = c.item_catalog_group_id
AND a.element_name = b.element_name
AND a.element_name LIKE '%Vendor%'
AND a.inventory_item_id = 'Your Inventory Item ID'
AND ROWNUM = 1;

Modifier Header ID based on the Modifier Description

INV Modifier Header ID based on the Modifier Description:-
---------------------------------------------------------------------

The following Query would get the Modifier Header ID based on the Description given for that Modifier. In many places we use the Modifier Header ID to get other values related to that Modifier.

SELECT list_header_id "Modifier Header ID"
FROM qp_list_headers_b
WHERE trim(SUBSTR (comments, 1, 240)) = trim('ALLORACLETECH Description') AND ROWNUM = 1;

Get the Modifier Description Based on the Modifier ID

The following Query will get the Modifier Description given for the Modifier based on the Modifier Header ID.

SELECT SUBSTR (comments, 1, 240) "Modifier Description"
FROM qp_list_headers_b
WHERE list_header_id = 'Your modifier header ID' AND ROWNUM = 1;

Oracle Assets Module

Oracle Assets Module

This section has all the changes in Oracle Assets Module in the 12 Release.

Overview

Subledger Accounting Architecture

Invoice Distributions from Oracle Payables

Global Descriptive Flexfield Migration for Greece

Global Descriptive Flexfield Migration for Greece

Oracle Assets : Global Descriptive Flexfield Migration for Greece :-
----------------------------------------------------------------------------

Commitment and Investment Law will be migrated from Global Descriptive Flexfields
specific to Greece localizations into named fields that will appear in the Asset
Workbench and be available to all users. The global_attribute1 and global_attribute2
columns in Assets will be copied into the Commitment and Investment Law fields,
respectively.

Invoice Distributions from Oracle Payables

Oracle Assets : Invoice Distributions from Oracle Payables:-
---------------------------------------------------------------------

Invoice distributions from Oracle Payables that have been interfaced to Assets will be
upgraded to display the Invoice Line Number. This new field will appear in the Asset
Workbench, Financial Inquiry, and Mass Additions Prepare forms.

Subledger Accounting Architecture

Oracle Assets : Subledger Accounting Architecture:-
------------------------------------------------------------

In Release 12, Oracle Assets adds Subledger Accounting (SLA) functionality. By default,
transactions in the current fiscal year in Asset books will have their accounting lines
migrated to the Subledger Accounting model. Accounting for current period
depreciation will be upgraded only if depreciation has already run for the period, and
the period remains open. After the upgrade, customers can run the SLA postupgrade
process to update accounting for the past transaction data as needed.

The upgrade for Subledger Accounting performs a number of tasks:

• Transactions that have occurred in the current open period will be upgraded.
Previously, accounting records were not created until after depreciation had run.
The upgrade will create these lines immediately. For example, if a user added an
asset and went to the Transaction History form, he would not see any Addition
accounting lines if depreciation had not been run. Postupgrade, however, these
records would appear in the Transaction History form for the Addition event.

The following transactions are affected:

• Additions

• Backdated Additions

• Backdated Transfers

• Retirements

• Accounting lines for transaction, depreciation, and deferred depreciation events
will be copied from the core Assets data model into the Subledger Accounting data
model. These accounting lines that are upgraded would be viewable in the new
Subledger Accounting pages and reports. Accounting lines that were not upgraded
would only appear in the old historical forms and reports.

• For transaction events, only the data for the current fiscal year are upgraded by
default. When there are less than six periods in the current fiscal year, additional
data from the previous fiscal year are upgraded to ensure that there are at least six
periods worth of data. The customer, however, can change this default setting in the
preupgrade. For depreciation and deferred depreciation events, only the current
open period is upgraded, and this is not changeable during the preupgrade. The
customer can upgrade any of the transactions, depreciation, and deferred
depreciation events that are not upgraded during the down time with the SLA
postupgrade process.

The Subledger Accounting upgrade is generic, and there are very few assumptions that
have been made:

• All of the accounting lines data that are upgraded are copied as is, and no
calculations or functional checks are done. Therefore, the data after the upgrade will
be the same as it was before the upgrade.

• Transactions that have no accounting impact will not be upgraded. That is, if there
are no accounting lines corresponding to a transaction event, that record will not be
upgraded. Tax book events that have accounting lines that correspond to their
corporate book transaction event, but not a tax book one, however, will still be
upgraded.

• Books or reporting books that are disabled will not be upgraded.

After the Subledger Accounting upgrade, a number of changes have been made to the
business flows for the Journal Entry processing as the following:

• Profile option FA: Use Workflow Account Generation is set to Yes during the
upgrade. Customers should analyze current customizations in the workflow setup.
If the requirement is to use the rules in workflow for generating code combinations
for asset transactions, there are two options:

Re-implement the custom rules in Subledger Accounting. Use the workflow rules as they are, which is the default value upon upgrade.

• Journal Source and Journal Category setups have been removed from the Book
Controls setup form. This setup has moved to Subledger Accounting.

• Depreciation Expense Account and the Bonus Expense Account for all category and
book combinations in the Asset Category setup form have been upgraded from a
single segment account value to entire account combinations.

• The intercompany account setup in Book Controls form has been replaced by
Intercompany/Intracompany setup in Accounting Setups.

Note: In Release 12, the Intercompany setups will be maintained at
the ledger level. The upgrade will update the existing
Intercompany setup at book level to ledger level.

• Users will need to run Create Accounting instead of Create Journal Entries.

• Users will need to run Calculate Deferred Depreciation and then Create Accounting
instead of Create Deferred Depreciation Journal Entries.

The following lists those features that are obsolete in Release 12 or replaced by another
feature:

• The Create Journal Entries and Rollback Journal Entries programs are now obsolete.
Create Journal Entries has been replaced by Create Accounting.

• The Create Deferred Depreciation Journal Entries program is now obsolete. Users
now need to run Calculate Deferred Depreciation followed by Create Accounting.

• The Account Drill Down report has been replaced by a new Subledger Accounting
report called the Account Analysis report.

• Profile option "FA: Include Nonrecoverable Tax in Mass Addition" is obsolete. This
is replaced by Post Accounting Programs under SLA. The setup for all eligible lines
from Payables to Assets for Mass Additions Create program is done in Post
Accounting Programs.

Overview

Oracle Assets : Overview :-
-----------------------------

In Release 12, Oracle Assets introduces a number of new features. Some of these
features require a data upgrade and involve terminology, prompt, and flow changes.
Some of the existing functionality is now obsolete.

Oracle iProcurement Module

Oracle iProcurement

This section have covered all the changes in the Oracle iProcurement module in 12 Release.

Overview

Catalog Agreement Management

Content Security

Content Security

Oracle iProcurement : Content Security :-
-----------------------------------------------

The upgrade process will upgrade existing realms, stores, and catalogs to Release 12
stores and content zones in the following way:

• Migrate local catalogs to become Local content zones. Local catalogs may contain
supplier restrictions. These restrictions are carried forward as supplier restrictions
in Release 12 content zones.

• Migrate punch out, transparent punch out, and informational punch out catalogs to
their corresponding types of Release 12 content zones.

• Migrate catalog and non-catalog stores to become Release 12 stores. Release 12
stores can contain both catalog and non-catalog content. Previous stores may
contain operating unit restrictions. These restrictions will not be carried forward
into Release 12 stores. Instead, these restrictions are applied to every content zone
within each store having operating unit restrictions.

• Migrate category realms at the responsibility level. In Release 12, category
restrictions are applied to each individual content zone.

• Migrate item source realms for Responsibilities.

• Migrate Default Smart Forms such that they have the lowest available sequence
number within Release 12 stores. This way, smart forms that appeared by default
previously will continue to appear by default in Release 12.

• POR: Approved Pricing Only profile is no longer used. The upgrade will apply the
correct settings in each content zone to achieve the same functionality in Release 12.

• Migrate descriptor configuration. Previously, it is possible to provide invalid values
for Descriptor settings such as Search Result Visibility, Item Detail Visibility, and Searchability— Invalid configuration settings were simply ignored by the system.
Release 12 validates Descriptor configurations at the time when they are updated in
the user interface, and will not allow any invalid value to be provided.

• The upgrade will re-default all the properties for the seeded descriptors from
previous releases. For example, visibility, sequence setting and alternate names may
change. After the upgrade, administrators should review the seeded descriptors
properties and make any necessary changes.

The following notes refer to changes in behavior in Release 12 as a result of Content
Security enhancements:

• Shopping Lists are migrated but content security is applied differently on Release
12 Public and Favorite lists. In Release 12, items on Shopping Lists are available to a
user if that user's responsibility and Operating Unit context are such that he would
have access to that same item through a Store. If the user does not have access
rights to an item through the normal Store search and browse function, he will not
have access rights to that item on a Shopping List.

• User-level realm will no longer be supported.

Catalog Agreement Management

Oracle iProcurement : Catalog Agreement Management :-
------------------------------------------------------------------

Bulk-loaded items in iProcurement will be migrated to newly created Global Blanket
Procurement Agreements (GBPA) based on their Supplier, Supplier Site, Currency,
Operating Unit, and Contract Reference. From the iProcurement Catalog
Administration responsibility, there will be a summary of all newly created GBPAs for
bulk-loaded items. There will also be a data-exceptions report that lists all the
exceptions that prevent migration of bulk-loaded content to new GBPAs. For more
details on how to correct these exceptions, refer to the iProcurement Preupgrade steps
in the Oracle Applications Upgrade Guide: Release 11i to Release 12.

The following is a list of changes in Release 12 as a result of the catalog upgrade:

• The upgrade will migrate bulk-loaded items into GBPAs. Release 12 , users will
load content into a GBPA and they will no longer need to provide Supplier,
Supplier Site, Operating Unit, and CPA/GCPA reference in the file. The bulk-load
option will also be available for BPAs and quotes.

• In Release 12, users will not be able to load catalog content for all Operating Units.
Please refer to the Oracle Purchasing Implementation guide for more details on
assigning GBPAs to Operating Units.

• Approved Supplier's Lists (ASLs) will not be shown in iProcurement search results.
In Release 12, the Oracle Purchasing Sourcing Rule will be used to determine the
best Supplier and Supplier Site combination during the requisition creation process.

• The bulk-load file format in Release 12 has been changed. A Converter has been
provided for users to convert old files to Release 12 format.

• In Release 12, users will not need to run the extractor to update the catalog content.
It will be updated in real-time.

Overview

Oracle iProcurement : Overview:-
--------------------------------------

Oracle iProcurement Release 12 gives catalog administrators the ability to make small
and quick updates to catalog content online. The online authoring feature complements
the existing batch upload process, which is optimized for large data upload. This release
also gives more comprehensive and flexible rules for securing catalog content. Below is
a description of the upgrade impact on the catalog management.

Invoices Associated with the Given Purchase Order Number

PO: Invoices Associated with the Given Purchase Order Number:-
------------------------------------------------------------------------------------------------------------------

SELECT aip.invoice_id invoice_id,
b.po_number po_number
FROM ap_invoices_all aip,
(SELECT invoice_id,po_number FROM ( SELECT ai.invoice_id,
AP_INVOICES_PKG.GET_PO_NUMBER( ai.invoice_id) po_number
FROM AP_INVOICES_ALL AI) A
WHERE a.po_number <>'UNMATCHED') b
where b.invoice_id=aip.invoice_id
and b.po_number= ‘Purchase Order Number’;

AP Module

In this session you will find all the ready scripts avilable for the AP Module.

P2P Query based on the Purchase Order Number

Summary Hold information based on the Invoice Number

Detail Hold information of the Invoice

Detail Hold information of the Invoice

AP Detail Hold information of the Invoice:-
--------------------------------------------------

When we Purchase some material/Goods/Items from Vendor/Supplier, after receiving the material. Vendor would send the INVOICE (In other words we receive BILL for the Items you have received). And payment will be done automatically. If there is some Discrepancy in the Items received and in the BILL/INVOICE you received, for them to hold the payment we normally set the HOLD rules. From the following Query you can know the Hold reason at the detail level.

Note:- Normally this kind of Information will be required for the Top level management for the decision making.

select * from
(SELECT 'Holds - Source1' AS SOURCE,
api.invoice_date AS invoice_date,
api.invoice_num AS invoice_num,
pov.vendor_name AS supplier_name,
apd.distribution_line_number AS inv_line_num,
apd.amount AS invoice_line_amount,
DECODE (aph.hold_reason, NULL, 'N', 'Y') AS defect,
poh.segment1 AS po_number,
por.release_num AS po_release_num,
pol.line_num AS po_line_num,
aph.hold_date AS hold_date,
aph.hold_lookup_code AS hold_lookup_code,
aph.hold_reason AS hold_reason,
aph.last_update_date AS release_date,
(TRUNC (NVL (aph.last_update_date, SYSDATE)) - TRUNC (aph.hold_date)) AS days_os,
pod.quantity_ordered AS shipment_quantity_ordered,
pod.quantity_delivered AS shipment_quantity_delivered,
pod.quantity_billed AS shipment_quantity_billed,
api.invoice_received_date AS invoice_received_date,
pob.agent_name AS buyer,
povs.vendor_site_code AS supplier_site,
ppf.full_name AS requestor,
rcvh.receipt_num AS receipt_number,
rcv.quantity AS received_accepted_qty,
rcv.unit_of_measure AS uom,
rcv.creation_date AS receipt_transacted_date,
rcv.transaction_date AS receipt_date
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.po_headers_all poh,
APPS.po_releases_all por,
APPS.po_lines_all pol,
APPS.ap_holds_all aph,
APPS.po_vendors pov,
APPS.po_agents_v pob,
APPS.po_vendor_sites_all povs,
APPS.rcv_transactions rcv,
APPS.rcv_shipment_headers rcvh,
APPS.po_line_locations_all pll,
APPS.hr_locations_all loc,
APPS.per_all_people_f ppf
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.vendor_id = pov.vendor_id(+)
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND poh.po_header_id(+) = pod.po_header_id
AND por.po_release_id(+) = pod.po_release_id
AND pol.po_header_id (+) = pod.po_header_id --
AND pol.po_line_id (+) = pod.po_line_id
AND poh.agent_id = pob.agent_id(+)
AND povs.vendor_site_id(+) = poh.vendor_site_id
AND apd.po_distribution_id = rcv.po_distribution_id(+)
AND rcvh.shipment_header_id(+) = rcv.shipment_header_id
AND rcv.destination_type_code(+) = 'RECEIVING'
AND pll.line_location_id(+) = pod.line_location_id
AND pll.ship_to_location_id = loc.location_id(+)
AND pod.deliver_to_person_id = ppf.person_id (+)
AND NVL (ppf.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE
UNION
SELECT 'Holds NotLinked To PO-Source2' AS SOURCE,
api.invoice_date AS invoice_date, api.invoice_num AS invoice_num,
pov.vendor_name AS supplier_name,
apd.distribution_line_number AS inv_line_num,
apd.amount AS invoice_line_amount,
DECODE (aph.hold_reason, NULL, 'N', 'Y') AS defect,
poh.segment1 AS po_number, por.release_num AS po_release_num,
pol.line_num AS po_line_num, aph.hold_date AS hold_date,
aph.hold_lookup_code AS hold_lookup_code,
aph.hold_reason AS hold_reason, aph.last_update_date AS release_date,
(TRUNC (NVL (aph.last_update_date, SYSDATE)) - TRUNC (aph.hold_date)
) AS days_os,
pod.quantity_ordered AS shipment_quantity_ordered,
pod.quantity_delivered AS shipment_quantity_delivered,
pod.quantity_billed AS shipment_quantity_billed,
api.invoice_received_date AS invoice_received_date,
pob.agent_name AS buyer, povs.vendor_site_code AS supplier_site,
ppf.full_name AS requestor, rcvh.receipt_num AS receipt_number,
rcv.quantity AS received_accepted_qty, rcv.unit_of_measure AS uom,
rcv.creation_date AS receipt_transacted_date,
rcv.transaction_date AS receipt_date
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.po_headers_all poh,
APPS.po_releases_all por,
APPS.po_lines_all pol,
APPS.ap_holds_all aph,
APPS.po_vendors pov,
APPS.po_agents_v pob,
APPS.po_vendor_sites_all povs,
APPS.rcv_transactions rcv,
APPS.rcv_shipment_headers rcvh,
APPS.po_line_locations_all pll,
APPS.hr_locations_all loc,
APPS.per_all_people_f ppf
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.vendor_id = pov.vendor_id(+)
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND aph.line_location_id IS NULL
AND poh.po_header_id(+) = pod.po_header_id
AND por.po_release_id(+) = pod.po_release_id
AND pol.po_header_id(+) = pod.po_header_id
AND pol.po_line_id(+) = pod.po_line_id
AND poh.agent_id = pob.agent_id(+)
AND povs.vendor_site_id(+) = poh.vendor_site_id
AND apd.po_distribution_id = rcv.po_distribution_id(+)
AND rcvh.shipment_header_id(+) = rcv.shipment_header_id
AND rcv.destination_type_code(+) = 'RECEIVING'
AND pll.line_location_id(+) = pod.line_location_id
AND pll.ship_to_location_id = loc.location_id(+)
AND pod.deliver_to_person_id = ppf.person_id(+)
AND NVL (ppf.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE
UNION
SELECT 'NON Holds - Source 3' AS SOURCE, apii.invoice_date AS invoice_date,
apii.invoice_num AS invoice_num, pov.vendor_name AS supplier_name,
apd.distribution_line_number AS inv_line_num,
apd.amount AS invoice_line_amount, 'N' AS defect,
poh.segment1 AS po_number, por.release_num AS po_release_num,
pol.line_num AS po_line_num, NULL AS hold_date,
NULL AS hold_lookup_code, NULL AS hold_reason, NULL AS release_date,
0 AS days_os, pod.quantity_ordered AS shipment_quantity_ordered,
pod.quantity_delivered AS shipment_quantity_delivered,
pod.quantity_billed AS shipment_quantity_billed,
apii.invoice_received_date AS invoice_received_date,
pob.agent_name AS buyer, povs.vendor_site_code AS supplier_site,
ppf.full_name AS requestor, rcvh.receipt_num AS receipt_number,
rcv.quantity AS received_accepted_qty, rcv.unit_of_measure AS uom,
rcv.creation_date AS receipt_transacted_date,
rcv.transaction_date AS receipt_date
FROM APPS.ap_invoices_all apii,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.po_headers_all poh,
APPS.po_releases_all por,
APPS.po_lines_all pol,
APPS.po_vendors pov,
APPS.po_agents_v pob,
APPS.po_vendor_sites_all povs,
APPS.rcv_transactions rcv,
APPS.rcv_shipment_headers rcvh,
APPS.po_line_locations_all pll,
APPS.hr_locations_all loc,
APPS.per_all_people_f ppf
WHERE 1 = 1
AND apii.invoice_id = apd.invoice_id
AND apii.vendor_id = pov.vendor_id(+)
AND apii.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND apd.distribution_line_number NOT IN (
SELECT apd.distribution_line_number
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.ap_holds_all aph
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND api.invoice_id = apii.invoice_id)
AND poh.po_header_id(+) = pod.po_header_id
AND por.po_release_id(+) = pod.po_release_id
AND pol.po_header_id(+) = pod.po_header_id
AND pol.po_line_id(+) = pod.po_line_id
AND poh.agent_id = pob.agent_id(+)
AND povs.vendor_site_id(+) = poh.vendor_site_id
AND apd.po_distribution_id = rcv.po_distribution_id(+)
AND rcvh.shipment_header_id(+) = rcv.shipment_header_id
AND rcv.destination_type_code(+) = 'RECEIVING'
AND pll.line_location_id(+) = pod.line_location_id
AND pll.ship_to_location_id = loc.location_id(+)
AND pod.deliver_to_person_id = ppf.person_id(+)
AND NVL (ppf.effective_start_date, SYSDATE) <= SYSDATE
AND NVL (ppf.effective_end_date, SYSDATE + 1) > SYSDATE)
where invoice_num='Your Invoice number'