Pipeline Function

In some developments, situation demands to write sql query on PL/SQL table type.
Oracle provides pipe line commands to achieve this .

I found it handy to use along with Oracle Application Express ( APEX )which needs report query region to have customized data display.

How to query from PL/SQL?
Use below simple steps and its ready for query

1) Create database data type and table type.
Note the type declaration should match pl/sql record/table type so data can be "poured" into database type

create or replace type contract_lines_obj_t as object
(
line NUMBER,
instance_id NUMBER,
end_customer NUMBER,
product VARCHAR2 (240),
qty NUMBER,
description VARCHAR2 (450),
status VARCHAR2 (50),
start_date DATE,
end_date DATE,
price NUMBER,
unit_price NUMBER,
exception_status varchar2(2),
exception_code varchar2(200),
exception_information varchar2(4000)
);
/
create or replace type contract_lines_ntt as table of contract_lines_obj_t;

2) Develop a pl/sql function to get the table type populated

function contract_lines(pid in number)
return contract_lines_ntt pipelined
is
pragma autonomous_transaction;
pout_contract_lines_tab contract_lines_tab_type ;
v_exception_key number;
v_index number;

l_return_status varchar2(100);

begin
-- populate data and get pl/sql table type as out parameter
begin
xxrh_show_contract_lines( pid ,
l_return_status ,
pout_contract_lines_tab );
exception
when others
then
v_exception_key := pout_contract_lines_tab.next(pout_contract_lines_tab.last);
if v_exception_key is null
then
v_exception_key := 1;
end if;

pout_contract_lines_tab(v_exception_key).exception_status := 'Y';
pout_contract_lines_tab(v_exception_key).exception_code := SQLCODE;
pout_contract_lines_tab(v_exception_key).exception_information := SQLERRM || ' Stack: ' || dbms_utility.format_error_backtrace;
end;

if(pout_contract_lines_tab.count > 0)
then
v_index := pout_contract_lines_tab.first;
loop
exit when v_index is null;
commit;
pipe row (contract_lines_obj_t(pout_contract_lines_tab(v_index).line
, pout_contract_lines_tab(v_index).instance_id
, pout_contract_lines_tab(v_index).end_customer
....
, pout_contract_lines_tab(v_index).exception_code
, pout_contract_lines_tab(v_index).exception_information
)
);
v_index := pout_contract_lines_tab.next(v_index);
end loop;
else
commit;
pipe row (contract_lines_obj_t(null
........
, null
));
end if;
return;
exception
when others
then
commit;
pipe row (contract_lines_obj_t(null
, null
.......
, 'Y'
, SQLCODE
, SQLERRM || ' Stack: ' || dbms_utility.format_error_backtrace
));
return;
end contract_lines;

Running of above function causes data to be populated in the table type

3) Now you are ready to query data populated already

select LINE , INSTANCE_ID, END_CUSTOMER, PRODUCT , QTY , DESCRIPTION , STATUS ,START_DATE , END_DATE , PRICE , UNIT_PRICE
from table(xxrh_supx_orcl_pkg_ypatil.contract_lines(3103165));

Note the from clause

More details at : http://www.adp-gmbh.ch/ora/plsql/pipeline.html
http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm

P.S. Oracle should have some function to clear projects pipeline as well :) to have work for all eligible hands!

MLS Function in CCP

Developers can create an MLS function for concurrent programs. The MLS function determines in which of the installed languages a request should run.

For example, an MLS function for a Print Invoices program could require that any request for that program to run only in the preferred languages of the customers who have pending invoices.

This restriction saves system resources by assuring that the request does not run in languages for which no output will be produced.

This restriction also prevents user error by automatically selecting the appropriate languages for a request.

MLS functions are PL/SQL stored procedures, written to a specific API.
When the concurrent manager processes a multilingual request for a concurrent program with an associated MLS function, it calls the MLS function to retrieve a list of languages and submits the appropriate child requests for each language.

The concurrent program application short name, the concurrent program short name, and the concurrent request parameters are all available to the MLS function
to determine the list of languages that the request should be run in.

Beginning with Release 12.1, MLS functions can also support multiple territories and numeric character settings (",." for example).

MLS functions are registered in the Concurrent Program Executable form. A registered MLS function can be assigned
to one or more concurrent programs in the Concurrent Programs form.

Its used in RPT RH JP Delivery Statement for the RDF

Executable : WSH_EXTREPS_MLS_LANG
Short Name : WSH_EXTREPS_MLS_LANG
Application : Shipping Execution
Description : MLS Function for Oracle Shipping Reports
Execution Method : Multi Language Function'
Execution File Name : WSH_EXTREPS_MLS_LANG.GET_LANG

More at http://docs.oracle.com/cd/E18727_01/doc.121/e12894/T202991T202993.htm

Host Script Error: FND-CP-ESP

I used below steps successfully in one of similar situations.
Thanks to sampawar from ITToolBox!

1.Convert File format into unix dos2unix filename.prog

dos2unix xxg_sqldumper_interface.prog

2 make sure that file has execution permissions chmod 777 $XXG_TOP/bin/xxg_sqldumper_interface.prog

=>chmod 777 xxg_sqldumper_interface.prog

3.check symbolic link ln -s $FND_TOP/bin/fndcpesr/ $/bin/extension>.

=>ln -s $FND_TOP/bin/fndcpesr/ xxg_sqldumper_interface

Datatype Char vs Byte

There can be some characters requiring multiple bytes .. e.g. French characters
Oracle default character data type uses byte semantic i.e. VARCHAR2(30) is same as VARCHAR2(30 BYTE)
One can also define the data type as VARCHAR2(30 CHAR) meaning it will include 30 characters even if multiple bytes

So question arises when we use VARCHAR2(30) , how Oracle knows its byte or Char?

Well the reason is in  instance parameters

select * from v$parameter where name like 'nls_length_semantics'

Value BYTE indicates the length is in bytes

Toad describe table command caused the question to be raised why Bytes? :)

Form Personalization

Form Personalization Action Type Special is used for adding Tools--> Menu

Menu label is the prompt which
appears to the users when Tools menu is invoked, block specifies the blocks for which the special
menu should be activated and Icon name is the .ico file name

This can be used for allowing user to validate the data before/after
fixing Open Interface Table errors e.g. Order Import Errors

The Action Type builtin within can be used to perform call to custom package for validation Builtin Type will be FORMS_DDL and begin end block calling the custom pkg.

Item Open Interface

Oracle Base tables related to Item setup

mtl_system_items_b
mtl_item_categories mic
mtl_category_sets_tl mcst
mtl_category_sets_b mcs
mtl_descriptive_elements -- Catalog Element Names
mtl_descr_element_values -- Contains Catalog Element Values for Item
apps.mtl_categories_kfv -- View for cat KFF contains concatenated_segments

Learning : Categories controlled at master org level can not be changed at individual org level.
Error Message : Cannot Create/Delete Item Controlled category set from Organization Items

Tables to be populated as part of interface
mtl_system_items_interface
mtl_item_categories_interface

Notes: We can have one record for each org in main interface table.
Transaction type to be used is CREATE

• Item Catalog API to populate catalog element values for item

p_item_desc_element_table APPS.INV_ITEM_CATALOG_ELEM_PUB.ITEM_DESC_ELEMENT_TABLE;

INV_ITEM_CATALOG_ELEM_PUB.PROCESS_ITEM_DESCR_ELEMENTS( p_api_version => p_api_version, p_init_msg_list => p_init_msg_list, p_commit_flag => p_commit_flag, p_validation_level => p_validation_level, p_inventory_item_id => p_inventory_item_id, p_item_number => p_item_number, p_item_desc_element_table => p_item_desc_element_table, x_generated_descr => x_generated_descr, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data );