ERPCIRCLES: December 2014

Tuesday, 30 December 2014

PO Receipt Interface

INSERT INTO rcv_headers_interface(header_interface_id
,GROUP_ID
,processing_status_code
,receipt_source_code
,transaction_type
,last_update_date
,last_updated_by
,last_update_login
,vendor_id
,expected_receipt_date
,validation_flag)
VALUES
(rcv_headers_interface_s.NEXTVAL
,p_group_id
,'PENDING'
,'VENDOR'
,'NEW'
,SYSDATE
,fnd_global.user_id
,0
,p_vendor_id
,SYSDATE
,'Y');

INSERT INTO rcv_transactions_interface (interface_transaction_id
,GROUP_ID
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,transaction_type
,transaction_date
,processing_status_code
,processing_mode_code
,transaction_status_code
,po_header_id
,po_line_id
,item_id
,quantity
,unit_of_measure
,po_line_location_id
,auto_transact_code
,receipt_source_code
,to_organization_code
,source_document_code
,header_interface_id
,validation_flag --, subinventory
)
VALUES
(l_parent_txn
,rcv_interface_groups_s.CURRVAL
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,0
,'RECEIVE'
,SYSDATE
,'PENDING'
,'BATCH'
,'PENDING'
,cr_po_rec.po_header_id
,cr_po_rec.po_line_id
,cr_po_rec.item_id
,cr_po_rec.quantity
,cr_po_rec.unit_meas_lookup_code
,cr_po_rec.line_location_id
,'RECEIVE'
,'VENDOR'
,cr_po_rec.organization_code
,'PO'
,rcv_headers_interface_s.CURRVAL
,'Y' --, p_subinventory_name
);

INSERT INTO rcv_transactions_interface (interface_transaction_id
,GROUP_ID
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,transaction_type
,transaction_date
,processing_status_code
,processing_mode_code
,transaction_status_code
,po_header_id
,po_line_id
,item_id
,quantity
,unit_of_measure
,po_line_location_id
,auto_transact_code
,receipt_source_code
,to_organization_code
,source_document_code
,header_interface_id
,validation_flag
,subinventory
,parent_interface_txn_id)
VALUES
(rcv_transactions_interface_s.NEXTVAL
,rcv_interface_groups_s.CURRVAL
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,0
,'DELIVER'
,SYSDATE
,'PENDING'
,'BATCH'
,'PENDING'
,cr_po_rec.po_header_id
,cr_po_rec.po_line_id
,cr_po_rec.item_id
,cr_po_rec.quantity
,cr_po_rec.unit_meas_lookup_code
,cr_po_rec.line_location_id
,'INVENTORY'
,'VENDOR'
,cr_po_rec.organization_code
,'PO'
,rcv_headers_interface_s.CURRVAL
,'Y'
,'PICKLINE'
,l_parent_txn);

INTERFACE TABLES
================
rcv_headers_interface
rcv_transactions_interface
po_interface_errors

Submitting the Receipt Interface
================================
l_receipt_request_id := fnd_request.submit_request (application => 'PO'
,program => 'RVCTP'
,description => NULL
,start_time => NULL
,sub_request => FALSE
,argument1 => 'BATCH'
,argument2 => l_group_id
,argument3 => NULL);

COMMIT;

Friday, 26 December 2014

Basics of XML Publisher

XML Publisher is used to Generate the Reports. The following are the steps need to follow while developing Report

1.) Templates
2.) Data Definitions

Create a Template and save it as RTF. Some of the basics things which are useful while developing a RTF are as follows.

Form Field
==========
138 Characters is the Maximum after that we can write in the Help Tab.

Templates
=========
Calling a Templage
------------------------
<?call-template:Header?>

Template Definition
-------------------------
<?template:countdown?>
Temp_Param
Recursive_Template
<?end template?>

Simple if
========
<?xdofx:if COMM ='' then 'NULL' else ','?>

Simple Loop
===========
<?for-each:GROUP_NAE?> <?end for-each?>

Setting Variables
=================
<?xdoxslt:set_variable($_XDOCTX, 'net_amt', 0)?>
<?xdoxslt:set_variable($_XDOCTX, 'dis_amt', 0)?>

Getting Variables
=================
<?xdoxslt:set_variable($_XDOCTX, 'net_amt', 0)?>
<?xdoxslt:set_variable($_XDOCTX, 'dis_amt', 0)?>

Formatting Dates and Numbers
=============================
<?xdofx:to_char(PaymentDate,'DD-MON-YYYY')?>
<?format-number(PaymentAmount/Value, '#,##0.00;(-#,##0.00)')?>

Choose LayOut
=============
<?choose:?>
<?when:Payer/Address/Country='US'?>
<?Payer/Address/City?>
<?xdofx:if Payer/Address/State !='' then ',' end if?>
<?Payer/Address/S
<?end otherwise?>
<?end choose?>

Eg:2
<?choose:?><?when:Payer/Address/Country!='US'?>
<?Payer/Address/City?>
<?xdofx:if Payer/Address/State !='' then ',' end if?>
<?end otherwise?>
<?end choose?>

Number of Lines Per Pages
==========================
<?xdoxslt:set_variable($_XDOCTX, ’Counter’, 0)?> --outside the for-each
<?xdoxslt:set_variable($_XDOCTX, ’Counter’, xdoxslt: get_variable($_XDOCTX, ’Counter’) + 2)?>
<?if: xdoxslt:get_variable($_XDOCTX, ’Counter’) mod 10=0?> <?split-by-page-break:?>
<?end if?>

Adding Page Totals
==================

<?add-page-total:pt3;'COUNTED_VALUE'?>
<?add-page-total:pt2;'COUNTED_QTY'?>

Printing Page Total
====================

<?show-page-total:pt2?>
<?if:P_PRINT_ACTUAL_COUNTS='Y'?><?show-page-total:pt3?><?end if?>

Printing Numbers to Words
=========================
<?show-page-total:pt;'to_check_number:0;CASE_INIT_CAP'?>
<?show-page-total:pt2;'to_check_number:0;CASE_INIT_CAP'?>
<?show-page-total:pt3;'to_check_number:RUB;CASE_INIT_CAP;DECIMAL_STYLE_FRACTION2'?>

XXRL_UTILITIES_PKG.money_to_char(:AMT, :CURRENCY,'RU')

Printing Report Totals
======================
<?format-number:sum(ACCOUNTED_QTY);LC_FORMAT_STR?>
<?sum(ACCOUNTED_QTY)?>

Printing Serial Numbers
=======================
<?xdoxslt:set_variable($_XDOCTX, 'RTotVar', xdoxslt:get_variable($_XDOCTX, 'RTotVar') + 1)?>
<?xdoxslt:get_variable($_XDOCTX, 'RTotVar')?>

Printing Only the Decimal Part
==============================
<?xdofx:rpad(substr(ACTUAL_AMT_IN_NUM,instr(ACTUAL_AMT_IN_NUM, '.',1)+1),2,0)?>

Printing the Outer Group Value
========================
../../column_name

SUPPORTING Links
================
http://apps2fusion.com/apps/apps/155-xml-publisher-developing-reports-printed-on-pre-printed-stationary --nO. of Lines per page
http://docs.oracle.com/cd/E21764_01/bi.1111/e13881/T527073T558233.htm
https://community.oracle.com/thread/2379391?tstart=0
https://docs.oracle.com/cd/E10091_01/doc/bip.1013/b40017/T421739T481157.htm
https://docs.oracle.com/cd/E28280_01/bi.1111/e22254/extend_func.htm

Printing Serial Number Based on Condition
=================================
Initialize Variables
<?xdoxslt:set_variable($_XDOCTX, 'slno',0)?>
<?xdoxslt:set_variable($_XDOCTX, 'slf',0)?>

If Condition
<?if: string(../../INVOICE_NUM) != string((xdoxslt:get_variable($_XDOCTX,'slf')))?>
<?xdoxslt:set_variable($_XDOCTX, 'slf',string(../../INVOICE_NUM))?>
<?xdoxslt:set_variable($_XDOCTX, 'slno', xdoxslt:get_variable($_XDOCTX, 'slno') + 1)?>
<?end if?>

Print the Sl. No
<?xdoxslt:get_variable($_XDOCTX, 'slno')?>

Date Field Issue Excel output :
======================

When the date field prints in the Excel file

01-JAN-15 as 1-JAN-15 (This is Excel limitation)
12-FEB-15 as 12-FEB-15

But expected output is

01-JAN-15 (zero has to be there )
12-FEB-15

Solution:

Place the cursor in front of rtf tag and use shift + ctrl+ Space Bar (Which is called non-breaking space)

Thursday, 25 December 2014

API for Ship Confirm in Oracle Apps

DECLARE
p_api_version NUMBER;
p_init_msg_list VARCHAR2(30);
p_commit VARCHAR2(30);
p_action_code VARCHAR2(15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2(30);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2(30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2(30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2(30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2(10);
p_sc_close_trip_flag VARCHAR2(10);
p_sc_create_bol_flag VARCHAR2(10);
p_sc_stage_del_flag VARCHAR2(10);
p_sc_trip_ship_method VARCHAR2(30);
p_sc_actual_dep_date VARCHAR2(30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2(60);
p_wv_override_flag VARCHAR2(10);
p_sc_defer_interface_flag VARCHAR2(1);
x_trip_id VARCHAR2(30);
x_trip_name VARCHAR2(30);
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
vApiErrorException EXCEPTION;
BEGIN
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;

FND_GLOBAL.APPS_INITIALIZE( user_id => 1318 , resp_id => 21623 , resp_appl_id => 660);
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := 41012; -- The delivery that needs to be confirmed
p_sc_action_flag := 'S'; -- Ship entered quantity.
p_sc_close_trip_flag := 'Y'; -- Close the trip after ship confirm
p_sc_trip_ship_method := 'DHL'; -- The ship method code
p_sc_defer_interface_flag := 'N';

WSH_DELIVERIES_PUB.Delivery_Action( p_api_version_number => 1.0
, p_init_msg_list => p_init_msg_list
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_action_code => p_action_code
, p_delivery_id => p_delivery_id
, p_delivery_name => p_delivery_name
, p_asg_trip_id => p_asg_trip_id
, p_asg_trip_name => p_asg_trip_name
, p_asg_pickup_stop_id => p_asg_pickup_stop_id
, p_asg_pickup_loc_id => p_asg_pickup_loc_id
, p_asg_pickup_loc_code => p_asg_pickup_loc_code
, p_asg_pickup_arr_date => p_asg_pickup_arr_date
, p_asg_pickup_dep_date => p_asg_pickup_dep_date
, p_asg_dropoff_stop_id => p_asg_dropoff_stop_id
, p_asg_dropoff_loc_id => p_asg_dropoff_loc_id
, p_asg_dropoff_loc_code => p_asg_dropoff_loc_code
, p_asg_dropoff_arr_date => p_asg_dropoff_arr_date
, p_asg_dropoff_dep_date => p_asg_dropoff_dep_date
, p_sc_action_flag => p_sc_action_flag
, p_sc_close_trip_flag => p_sc_close_trip_flag
, p_sc_create_bol_flag => p_sc_create_bol_flag
, p_sc_stage_del_flag => p_sc_stage_del_flag
, p_sc_trip_ship_method => p_sc_trip_ship_method
, p_sc_actual_dep_date => p_sc_actual_dep_date
, p_sc_report_set_id => p_sc_report_set_id
, p_sc_report_set_name => p_sc_report_set_name
, p_wv_override_flag => p_wv_override_flag
, p_sc_defer_interface_flag => p_sc_defer_interface_flag
, x_trip_id => x_trip_id
, x_trip_name => x_trip_name);

IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
RAISE vApiErrorException;
ELSE
dbms_output.put_line('The confirm action on the delivery '||p_delivery_id||' is successful');
END IF;
EXCEPTION
WHEN vApiErrorException THEN
WSH_UTIL_CORE.get_messages('Y', x_msg_summary, x_msg_details,x_msg_count);
IF x_msg_count > 1 THEN
x_msg_data := x_msg_summary || x_msg_details;
DBMS_OUTPUT.PUT_LINE('Message Data : '||x_msg_data);
ELSE
x_msg_data := x_msg_summary;
DBMS_OUTPUT.PUT_LINE('Message Data : '||x_msg_data);
END IF;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected Error: '||SQLERRM);
END;

Wednesday, 24 December 2014

AOL Scenarios

Handling NULL and additional Columns in AOL List of Values

Defaulting the Parameter value to the Next Parameter

To get the Previous Value in Where Condition $FLEX$.VALUESETNAME

Note: The: $FLEX$ should be in CAPS. The value will be taken as ID of the Value Set. If the Id Column not exists then it will consider the Value Column.

Important Links

http://alloracleapps.com/oracle_apps/oracleappsappsfunctionalfunctionalmoduleshrandfi/ --MOAC SETUP

http://www.cs.colostate.edu/helpdocs/ftp.html --FTP_COMMANDS

http://orafinappssetups.blogspot.com/2013/01/autolock-box-in-r12.html --//Oracle Setups

https://docs.oracle.com/cd/E18727_01/doc.121/e13450/T291651T295121.htm -- Inventory REports

http://vipulsharmaoracleapps.blogspot.com/2013/08/physical-inventory-count.html --Physical Inventory Setups

http://www.oracleappshub.com/aol/aol-valueset-a-beginner-guide/ --AOL Concepts

https://docs.oracle.com/cd/E10091_01/doc/bip.1013/b40017/T421739T481157.htm --XML PUBLISHER

https://docs.oracle.com/cd/E28280_01/bi.1111/e22254/extend_func.htm --XML PUBLISHER

Oracle Apps Interview Questions

SQL
===
1.) There is view which is created on tables t1 and t2. If we drop table t1 what happes to the view.?
2.) If we re-create the t1 table again? what will be the status.
3.) what is Materialized view.
4.) What is global temporary table what is the use of it. Have u ever used? what is the scenario.
5.) What is index and what is its use.---How it Improves the peroformance technically
What is the algorithm it will use by defaut.
6.) Have you ever used analitical function. In which scenario have u used.

PLSQL
=====
1.) What is a ref cursor. Have you ever used it. If yes what is the scenario
2.) If a package spec is having two procedures p1 and p2. I have wrintten the code for p1 in body. What will happen if i compile
What will happend to the spec.
3.) Difference between create or replace and droping and crating the package.
4.) What are all the pragmas we have pl/sql
5.) What is the use of use of NOCOPY hint.

XML Reports
==========
1.) What is the process to register an XML in Oracle Apps
2.) What are all the triggers that will be used in XML report. How it will be used.
3.) How do you develop multi org reports.
4.) How can you restrict the no. of lines per page.
5.) Have u ever called the sub-templates.
FORMS
=====
1.) How can we register the form.
2.) How can we create an lov
3.) diffferce between pre-query and post-query.
4.) what are all the triggers fired when move from one-text item to ohter
5.) Differnce between when-validat-item and post-text-item

Form Personalization
===============
1.) What is a form personalization. How can we do it.
2.) What are all the triggers available in Form Personalization
3.) What is difference between custom.pll and form personalization.
4.) What is a Zoom functionality. How can we do using the form Personalization.
5.) If we are using the form personalization and custom.pll which will get over write.
AOL
===
1.) if we have two parameters p1 and p2. I want to enable parameter p2 based on the value p1.
2.) How can we get the default value to next parameter
3.) How can we make the second parameter dependent on first.
4.) I have two parameter p1 and p2. P2 is dependent on p1. If i din't give p1 still i need to select the values from p2. How can we archive this.

Alerts
=====
1.) How many types of alerts are there?
2.) Where can we define the alerts?
3.) Can we send the notifications to users using alerts.
4.) I want to send the error log file to the list of users using alerts. How can we approach this.

Collections
=========
1.) What is a collection?
2.) How many types of collections are there what are they?
3.) What is a Bulk Bind. What are all the Bulk Exceptions available.
4.) What is the difference between Assosiative Arrays and Nested Tables.
5.) What is the other name for Assosiative arrays.

Performance Tuning
=================
1.) one custom report is taking long time to complete . what is the approch you follow.
Order to Cash
=============
1.) When will the order becomes back-order.
2.) what is the use of ITP and when it will fire.
3.) what is use of workflow bg proces. Why we need this. Why can't we directly put the interface records to AR Module.
4.) why do we need the price-list in sales order
Procure-to-Pay
===============
1.) How may types of requisitions? table flow?
2.) what are types of POs?
3.) which tables it will impact.
4.) what is the link between req and po.
Workflow
=========
1.) what are all the tables you used in workflow
2.) which is the table contains all the activities
3.) have u created any custome workflow/customize workflow
4.) what is the use of access-level in work flow.
5.) how can you make the process deferred.--have u heard abt threshold

Wednesday, 17 December 2014

Delete Script for XML Reports

Delete Script for XML Publisher

begin
delete from XDO_TEMPLATES_B where template_code = 'ERPC_TEST' ;
delete from XDO_TEMPLATES_TL where template_code = 'ERPC_TEST' ;
delete from XDO_LOBS where lob_code = 'ERPC_TEST' ;
delete from XDO_DS_DEFINITIONS_TL where data_source_code = 'ERPC_TEST' ;
delete from XDO_DS_DEFINITIONS_b where data_source_code = 'ERPC_TEST' ;
end;

LDT File for XML Publisher

FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME="XXCUST" DATA_SOURCE_CODE="XX_SOURCE_CODE" TMPL_APP_SHORT_NAME="XXCUST" TEMPLATE_CODE="XX_SOURCE_CODE"