Innovative Zero: July 2009

Friday, July 31, 2009

Creating OM Delivery through API

/* Formatted on 2009/07/31 17:32 (Formatter Plus v4.8.6) */
DECLARE
v_api_version_number NUMBER;
v_init_msg_list VARCHAR2 (1000);
v_return_status VARCHAR2 (1000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (1000);
p_action_code VARCHAR2 (1000);
lx_msg_data VARCHAR2 (2000);
lx_msg_index_out NUMBER;
--v_delivery WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
v_delivery wsh_deliveries_pub.delivery_pub_rec_type;
v_delivery_name VARCHAR2 (100);
x_delivery_id NUMBER;
v_name VARCHAR2 (1000);
l_wsh_rec wsh_new_deliveries%ROWTYPE;
v_rowid VARCHAR2 (10);
v_delivery_details wsh_delivery_details_pub.id_tab_type;
v_validation_level NUMBER;
v_delivery_id NUMBER;
BEGIN
DBMS_OUTPUT.put_line ('Replay Start');
v_return_status := wsh_util_core.g_ret_sts_success;
v_api_version_number := 1.0;
--v_delivery.name := 278712044;
SELECT *
INTO l_wsh_rec
FROM wsh_new_deliveries
WHERE delivery_id = 278710043;
--v_delivery.DELIVERY_ID := 278713053 ;
v_delivery.NAME := '278742043';
--v_delivery.PLANNED_FLAG := l_wsh_rec.PLANNED_FLAG ;
--v_delivery.STATUS_CODE := l_wsh_rec.STATUS_CODE ;
--v_delivery.DELIVERY_TYPE := l_wsh_rec.DELIVERY_TYPE ;
--v_delivery.LOADING_SEQUENCE := l_wsh_rec.LOADING_SEQUENCE ;
--v_delivery.LOADING_ORDER_FLAG := l_wsh_rec.LOADING_ORDER_FLAG ;
--v_delivery.INITIAL_PICKUP_DATE := l_wsh_rec.INITIAL_PICKUP_DATE ;
v_delivery.initial_pickup_location_id := 121;
--l_wsh_rec.INITIAL_PICKUP_LOCATION_ID ;
v_delivery.organization_id := 46; --l_wsh_rec.ORGANIZATION_ID ;
v_delivery.ultimate_dropoff_location_id := 84483;
--l_wsh_rec.ULTIMATE_DROPOFF_LOCATION_ID ;
--v_delivery.ULTIMATE_DROPOFF_DATE := l_wsh_rec.ULTIMATE_DROPOFF_DATE ;
--v_delivery.CUSTOMER_ID := l_wsh_rec.CUSTOMER_ID ;
--v_delivery.INTMED_SHIP_TO_LOCATION_ID := l_wsh_rec.INTMED_SHIP_TO_LOCATION_ID ;
--v_delivery.POOLED_SHIP_TO_LOCATION_ID := l_wsh_rec.POOLED_SHIP_TO_LOCATION_ID ;
--v_delivery.CARRIER_ID := l_wsh_rec.CARRIER_ID ;
--v_delivery.SHIP_METHOD_CODE := l_wsh_rec.SHIP_METHOD_CODE ;
--v_delivery.FREIGHT_TERMS_CODE := l_wsh_rec.FREIGHT_TERMS_CODE ;
--v_delivery.FOB_CODE := l_wsh_rec.FOB_CODE ;
--v_delivery.FOB_LOCATION_ID := l_wsh_rec.FOB_LOCATION_ID ;
--v_delivery.WAYBILL := l_wsh_rec.WAYBILL ;
--v_delivery.DOCK_CODE := l_wsh_rec.DOCK_CODE ;
--v_delivery.ACCEPTANCE_FLAG := l_wsh_rec.ACCEPTANCE_FLAG ;
--v_delivery.ACCEPTED_BY := l_wsh_rec.ACCEPTED_BY ;
--v_delivery.ACCEPTED_DATE := l_wsh_rec.ACCEPTED_DATE ;
--v_delivery.ACKNOWLEDGED_BY := l_wsh_rec.ACKNOWLEDGED_BY ;
--v_delivery.CONFIRMED_BY := l_wsh_rec.CONFIRMED_BY ;
--v_delivery.CONFIRM_DATE := l_wsh_rec.CONFIRM_DATE ;
--v_delivery.ASN_DATE_SENT := l_wsh_rec.ASN_DATE_SENT ;
--v_delivery.ASN_STATUS_CODE := l_wsh_rec.ASN_STATUS_CODE ;
--v_delivery.ASN_SEQ_NUMBER := l_wsh_rec.ASN_SEQ_NUMBER ;
--v_delivery.GROSS_WEIGHT := l_wsh_rec.GROSS_WEIGHT ;
--v_delivery.NET_WEIGHT := l_wsh_rec.NET_WEIGHT ;
v_delivery.weight_uom_code := 'EA';
--l_wsh_rec.WEIGHT_UOM_CODE ;
--v_delivery.VOLUME := l_wsh_rec.VOLUME ;
v_delivery.volume_uom_code := 'EA';
--l_wsh_rec.VOLUME_UOM_CODE ;
--v_delivery.ADDITIONAL_SHIPMENT_INFO := l_wsh_rec.ADDITIONAL_SHIPMENT_INFO ;
--v_delivery.CURRENCY_CODE := l_wsh_rec.CURRENCY_CODE ;
--v_delivery.ATTRIBUTE_CATEGORY := l_wsh_rec.ATTRIBUTE_CATEGORY ;
--v_delivery.ATTRIBUTE1 := l_wsh_rec.ATTRIBUTE1 ;
--v_delivery.ATTRIBUTE2 := l_wsh_rec.ATTRIBUTE2 ;
--v_delivery.ATTRIBUTE3 := l_wsh_rec.ATTRIBUTE3 ;
--v_delivery.ATTRIBUTE4 := l_wsh_rec.ATTRIBUTE4 ;
--v_delivery.ATTRIBUTE5 := l_wsh_rec.ATTRIBUTE5 ;
--v_delivery.ATTRIBUTE6 := l_wsh_rec.ATTRIBUTE6 ;
--v_delivery.ATTRIBUTE7 := l_wsh_rec.ATTRIBUTE7 ;
--v_delivery.ATTRIBUTE8 := l_wsh_rec.ATTRIBUTE8 ;
--v_delivery.ATTRIBUTE9 := l_wsh_rec.ATTRIBUTE9 ;
v_delivery.attribute10 := '278742043';
--v_delivery.ATTRIBUTE11 := l_wsh_rec.ATTRIBUTE11 ;
--v_delivery.ATTRIBUTE12 := l_wsh_rec.ATTRIBUTE12 ;
--v_delivery.ATTRIBUTE13 := l_wsh_rec.ATTRIBUTE13 ;
--v_delivery.ATTRIBUTE14 := l_wsh_rec.ATTRIBUTE14 ;
--v_delivery.ATTRIBUTE15 := l_wsh_rec.ATTRIBUTE15 ;
--v_delivery.TP_ATTRIBUTE_CATEGORY := l_wsh_rec.TP_ATTRIBUTE_CATEGORY ;
--v_delivery.TP_ATTRIBUTE1 := l_wsh_rec.TP_ATTRIBUTE1 ;
--v_delivery.TP_ATTRIBUTE2 := l_wsh_rec.TP_ATTRIBUTE2 ;
--v_delivery.TP_ATTRIBUTE3 := l_wsh_rec.TP_ATTRIBUTE3 ;
--v_delivery.TP_ATTRIBUTE4 := l_wsh_rec.TP_ATTRIBUTE4 ;
--v_delivery.TP_ATTRIBUTE5 := l_wsh_rec.TP_ATTRIBUTE5 ;
--v_delivery.TP_ATTRIBUTE6 := l_wsh_rec.TP_ATTRIBUTE6 ;
--v_delivery.TP_ATTRIBUTE7 := l_wsh_rec.TP_ATTRIBUTE7 ;
--v_delivery.TP_ATTRIBUTE8 := l_wsh_rec.TP_ATTRIBUTE8 ;
--v_delivery.TP_ATTRIBUTE9 := l_wsh_rec.TP_ATTRIBUTE9 ;
--v_delivery.TP_ATTRIBUTE10 := l_wsh_rec.TP_ATTRIBUTE10 ;
--v_delivery.TP_ATTRIBUTE11 := l_wsh_rec.TP_ATTRIBUTE11 ;
--v_delivery.TP_ATTRIBUTE12 := l_wsh_rec.TP_ATTRIBUTE12 ;
--v_delivery.TP_ATTRIBUTE13 := l_wsh_rec.TP_ATTRIBUTE13 ;
--v_delivery.TP_ATTRIBUTE14 := l_wsh_rec.TP_ATTRIBUTE14 ;
--v_delivery.TP_ATTRIBUTE15 := l_wsh_rec.TP_ATTRIBUTE15 ;
--v_delivery.GLOBAL_ATTRIBUTE_CATEGORY := l_wsh_rec.GLOBAL_ATTRIBUTE_CATEGORY ;
--v_delivery.GLOBAL_ATTRIBUTE1 := l_wsh_rec.GLOBAL_ATTRIBUTE1 ;
--v_delivery.GLOBAL_ATTRIBUTE2 := l_wsh_rec.GLOBAL_ATTRIBUTE2 ;
--v_delivery.GLOBAL_ATTRIBUTE3 := l_wsh_rec.GLOBAL_ATTRIBUTE3 ;
--v_delivery.GLOBAL_ATTRIBUTE4 := l_wsh_rec.GLOBAL_ATTRIBUTE4 ;
--v_delivery.GLOBAL_ATTRIBUTE5 := l_wsh_rec.GLOBAL_ATTRIBUTE5 ;
--v_delivery.GLOBAL_ATTRIBUTE6 := l_wsh_rec.GLOBAL_ATTRIBUTE6 ;
--v_delivery.GLOBAL_ATTRIBUTE7 := l_wsh_rec.GLOBAL_ATTRIBUTE7 ;
--v_delivery.GLOBAL_ATTRIBUTE8 := l_wsh_rec.GLOBAL_ATTRIBUTE8 ;
--v_delivery.GLOBAL_ATTRIBUTE9 := l_wsh_rec.GLOBAL_ATTRIBUTE9 ;
--v_delivery.GLOBAL_ATTRIBUTE10 := l_wsh_rec.GLOBAL_ATTRIBUTE10 ;
--v_delivery.GLOBAL_ATTRIBUTE11 := l_wsh_rec.GLOBAL_ATTRIBUTE11 ;
--v_delivery.GLOBAL_ATTRIBUTE12 := l_wsh_rec.GLOBAL_ATTRIBUTE12 ;
--v_delivery.GLOBAL_ATTRIBUTE13 := l_wsh_rec.GLOBAL_ATTRIBUTE13 ;
--v_delivery.GLOBAL_ATTRIBUTE14 := l_wsh_rec.GLOBAL_ATTRIBUTE14 ;
--v_delivery.GLOBAL_ATTRIBUTE15 := l_wsh_rec.GLOBAL_ATTRIBUTE15 ;
--v_delivery.GLOBAL_ATTRIBUTE16 := l_wsh_rec.GLOBAL_ATTRIBUTE16 ;
--v_delivery.GLOBAL_ATTRIBUTE17 := l_wsh_rec.GLOBAL_ATTRIBUTE17 ;
--v_delivery.GLOBAL_ATTRIBUTE18 := l_wsh_rec.GLOBAL_ATTRIBUTE18 ;
--v_delivery.GLOBAL_ATTRIBUTE19 := l_wsh_rec.GLOBAL_ATTRIBUTE19 ;
--v_delivery.GLOBAL_ATTRIBUTE20 := l_wsh_rec.GLOBAL_ATTRIBUTE20 ;
--v_delivery.CREATION_DATE := l_wsh_rec.CREATION_DATE ;
--v_delivery.CREATED_BY := l_wsh_rec.CREATED_BY ;
--v_delivery.LAST_UPDATE_DATE := l_wsh_rec.LAST_UPDATE_DATE ;
--v_delivery.LAST_UPDATED_BY := l_wsh_rec.LAST_UPDATED_BY ;
--v_delivery.LAST_UPDATE_LOGIN := l_wsh_rec.LAST_UPDATE_LOGIN ;
--v_delivery.PROGRAM_APPLICATION_ID := l_wsh_rec.PROGRAM_APPLICATION_ID ;
--v_delivery.PROGRAM_ID := l_wsh_rec.PROGRAM_ID ;
--v_delivery.PROGRAM_UPDATE_DATE := l_wsh_rec.PROGRAM_UPDATE_DATE ;
--v_delivery.REQUEST_ID := l_wsh_rec.REQUEST_ID ;
--v_delivery.BATCH_ID := l_wsh_rec.BATCH_ID ;
--v_delivery.HASH_VALUE := l_wsh_rec.HASH_VALUE ;
--v_delivery.SOURCE_HEADER_ID := l_wsh_rec.SOURCE_HEADER_ID ;
--v_delivery.NUMBER_OF_LPN := l_wsh_rec.NUMBER_OF_LPN ;
--v_delivery.COD_AMOUNT := l_wsh_rec.COD_AMOUNT ;
--v_delivery.COD_CURRENCY_CODE := l_wsh_rec.COD_CURRENCY_CODE ;
--v_delivery.COD_REMIT_TO := l_wsh_rec.COD_REMIT_TO ;
--v_delivery.COD_CHARGE_PAID_BY := l_wsh_rec.COD_CHARGE_PAID_BY ;
--v_delivery.PROBLEM_CONTACT_REFERENCE := l_wsh_rec.PROBLEM_CONTACT_REFERENCE ;
--v_delivery.PORT_OF_LOADING := l_wsh_rec.PORT_OF_LOADING ;
--v_delivery.PORT_OF_DISCHARGE := l_wsh_rec.PORT_OF_DISCHARGE ;
--v_delivery.FTZ_NUMBER := l_wsh_rec.FTZ_NUMBER ;
--v_delivery.ROUTED_EXPORT_TXN := l_wsh_rec.ROUTED_EXPORT_TXN ;
--v_delivery.ENTRY_NUMBER := l_wsh_rec.ENTRY_NUMBER ;
--v_delivery.ROUTING_INSTRUCTIONS := l_wsh_rec.ROUTING_INSTRUCTIONS ;
--v_delivery.IN_BOND_CODE := l_wsh_rec.IN_BOND_CODE ;
--v_delivery.SHIPPING_MARKS := l_wsh_rec.SHIPPING_MARKS ;
--v_delivery.SERVICE_LEVEL := l_wsh_rec.SERVICE_LEVEL ;
--v_delivery.MODE_OF_TRANSPORT := l_wsh_rec.MODE_OF_TRANSPORT ;
--v_delivery.ASSIGNED_TO_FTE_TRIPS := l_wsh_rec.ASSIGNED_TO_FTE_TRIPS ;
--v_delivery.AUTO_SC_EXCLUDE_FLAG := l_wsh_rec.AUTO_SC_EXCLUDE_FLAG ;
--v_delivery.AUTO_AP_EXCLUDE_FLAG := l_wsh_rec.AUTO_AP_EXCLUDE_FLAG ;
--v_delivery.AP_BATCH_ID := l_wsh_rec.AP_BATCH_ID ;
--v_delivery.ROWID := l_wsh_rec.ROWID ;
--v_delivery.LOADING_ORDER_DESC := l_wsh_rec.LOADING_ORDER_DESC ;
--v_delivery.ORGANIZATION_CODE := l_wsh_rec.ORGANIZATION_CODE ;
--v_delivery.ULTIMATE_DROPOFF_LOCATION_CODE := l_wsh_rec.ULTIMATE_DROPOFF_LOCATION_CODE ;
--v_delivery.INITIAL_PICKUP_LOCATION_CODE := l_wsh_rec.INITIAL_PICKUP_LOCATION_CODE ;
--v_delivery.CUSTOMER_NUMBER := l_wsh_rec.CUSTOMER_NUMBER ;
--v_delivery.INTMED_SHIP_TO_LOCATION_CODE := l_wsh_rec.INTMED_SHIP_TO_LOCATION_CODE ;
--v_delivery.POOLED_SHIP_TO_LOCATION_CODE := l_wsh_rec.POOLED_SHIP_TO_LOCATION_CODE ;
--v_delivery.CARRIER_CODE := l_wsh_rec.CARRIER_CODE ;
--v_delivery.SHIP_METHOD_NAME := l_wsh_rec.SHIP_METHOD_NAME ;
--v_delivery.MS_NAME := l_wsh_rec.MS_NAME ;
--v_delivery.FOB_NAME := l_wsh_rec.FOB_NAME ;
--v_delivery.FOB_LOCATION_CODE := l_wsh_rec.FOB_LOCATION_CODE ;
--v_delivery.WEIGHT_UOM_DESC := l_wsh_rec.WEIGHT_UOM_DESC ;
--v_delivery.VOLUME_UOM_DESC := l_wsh_rec.VOLUME_UOM_DESC ;
--v_delivery.CURRENCY_NAME := l_wsh_rec.CURRENCY_NAME ;
--v_delivery.SHIPMENT_DIRECTION := l_wsh_rec.SHIPMENT_DIRECTION ;
--v_delivery.VENDOR_ID := l_wsh_rec.VENDOR_ID ;
--v_delivery.PARTY_ID := l_wsh_rec.PARTY_ID ;
--v_delivery.ROUTING_RESPONSE_ID := l_wsh_rec.ROUTING_RESPONSE_ID ;
--v_delivery.RCV_SHIPMENT_HEADER_ID := l_wsh_rec.RCV_SHIPMENT_HEADER_ID ;
--v_delivery.ASN_SHIPMENT_HEADER_ID := l_wsh_rec.ASN_SHIPMENT_HEADER_ID ;
--v_delivery.SHIPPING_CONTROL := l_wsh_rec.SHIPPING_CONTROL ;
--v_delivery.TP_DELIVERY_NUMBER := l_wsh_rec.TP_DELIVERY_NUMBER ;
--v_delivery.EARLIEST_PICKUP_DATE := l_wsh_rec.EARLIEST_PICKUP_DATE ;
--v_delivery.LATEST_PICKUP_DATE := l_wsh_rec.LATEST_PICKUP_DATE ;
--v_delivery.EARLIEST_DROPOFF_DATE := l_wsh_rec.EARLIEST_DROPOFF_DATE ;
--v_delivery.LATEST_DROPOFF_DATE := l_wsh_rec.LATEST_DROPOFF_DATE ;
--v_delivery.IGNORE_FOR_PLANNING := l_wsh_rec.IGNORE_FOR_PLANNING ;
--v_delivery.TP_PLAN_NAME := l_wsh_rec.TP_PLAN_NAME ;
--v_delivery.wv_frozen_flag := l_wsh_rec.wv_frozen_flag ;
--v_delivery.hash_string := l_wsh_rec.hash_string ;
--v_delivery.delivered_date := l_wsh_rec.delivered_date ;
--v_delivery.packing_slip := l_wsh_rec.packing_slip ;
--v_delivery.REASON_OF_TRANSPORT := l_wsh_rec.REASON_OF_TRANSPORT ;
--v_delivery.DESCRIPTION := l_wsh_rec.DESCRIPTION ;
--v_delivery.PRORATE_WT_FLAG := l_wsh_rec.PRORATE_WT_FLAG ;
--v_delivery.TMS_INTERFACE_FLAG := l_wsh_rec.TMS_INTERFACE_FLAG ;
--v_delivery.TMS_VERSION_NUMBER := l_wsh_rec.TMS_VERSION_NUMBER ;
--v_delivery.attribute1 := 'Y';
v_delivery_name := NULL;
DBMS_OUTPUT.put_line ('Replay Start:' v_return_status);
--commit;
/* WSH_NEW_DELIVERIES_PVT.Create_Delivery(p_delivery_info => v_delivery,
x_rowid => v_rowid,
x_delivery_id => x_delivery_id ,
x_name => v_name ,
x_return_status => v_return_status
) ;*/
/*WSH_NEW_DELIVERIES_PVT.Delete_Delivery
( p_delivery_id => 278710043 ,
x_return_status =>v_return_status,
p_validate_flag => 'Y'
) ;*/
DBMS_OUTPUT.put_line ('Replay Start:' v_return_status);
wsh_deliveries_pub.create_update_delivery
(p_api_version_number => v_api_version_number,
p_init_msg_list => v_init_msg_list,
x_return_status => v_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => 'CREATE',
p_delivery_info => v_delivery,
p_delivery_name => v_delivery_name,
x_delivery_id => x_delivery_id,
x_name => v_name
);
DBMS_OUTPUT.put_line ( 'DO CREATED PROBLEM:'
v_return_status
':'
x_delivery_id
);
COMMIT;
v_delivery_details (1) := 10915608; --Delivery Detail id from WDD table
v_delivery_id := 278742043;
wsh_delivery_details_pub.detail_to_delivery
(p_api_version => v_api_version_number,
p_init_msg_list => v_init_msg_list,
p_commit => fnd_api.g_true,
p_validation_level => v_validation_level,
x_return_status => v_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tabofdeldets => v_delivery_details,
p_action => 'ASSIGN',
p_delivery_id => v_delivery_id
);
IF v_return_status = wsh_util_core.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('DO DETAILS CREATED SUCCESSFULLY');
END IF;
IF (v_return_status <> wsh_util_core.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ('DO CREATED PROBLEM');
IF (fnd_msg_pub.count_msg > 1)
THEN
FOR j IN 1 .. fnd_msg_pub.count_msg
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => lx_msg_index_out
);
DBMS_OUTPUT.put_line ('DO CREATED PROBLEM' x_msg_data);
/* INSERT INTO ncr_nz_oe_error_msg
VALUES (ncr_nz_oe_error_msg_s.NEXTVAL, SYSDATE,
'ORDER Line CREATION bERROR1', h_oe.header_id,
'ORA-10000', x_msg_data);*/
END LOOP;
END IF;
ELSE
COMMIT;
END IF;
IF v_return_status = wsh_util_core.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('DO CREATED SUCCESSFULLY');
END IF;
DBMS_OUTPUT.put_line ('Replay End');
END;

How to know DBC file location, Java Version, OA Version

Go to Sysadmin ----> Profile
Personalize Self Service Defn to YES
Go to About This Page : Java System Properties Tab
You will find System Property: DBCFILE
Go to Technology Components : You will file all OA and Java Version

Friday, July 10, 2009

How to get Attribute Default value of DFF from backend.

SELECT default_value FROM FND_DESCR_FLEX_COL_USAGE_VL

WHERE descriptive_flex_context_code = 'Global Data Elements'

AND descriptive_flexfield_name ='PO_VENDORS'

AND application_column_name = 'ATTRIBUTE2'

Oracle Refresh Problem in R12

Some time we add Responsibility to particular user and it does not reflect. We need to do below process to reflect. This may rectify any reflect problem.

Run Below Procedure

BEGIN

wf_event.listen(p_agent_name => 'WF_DEFERRED', p_correlation => 'oracle.apps.fnd.wf.ds.%');

END

and Run below concurrent program.

Workflow_Directory_Service_User/Role_Validation

Query to determine reworkable Item

select * from mtl_item_categories a, mtl_category_sets b, mtl_categories c, mtl_system_items_b d

where a.organization_id = 101

and a.category_set_id = b.category_set_id

and b.category_set_name ='PERP-INV'

and a.category_id = c.category_id

and c.segment1= 'RW-Y'

and a.organization_id = d.organization_id

and a.inventory_item_id = d.inventory_item_id

order by d.segment1

Oracle Debrief Charges Process

Charges process: After the CSR debriefs a task, once the task status is changed to 'Completed'/Suspended'/Cancelled/Rejected, a concurrent program "Debrief Posting Program" is called. This program creates the charges lines as per the task debrief (The charges are associated with the SR).

The program calls Package CSF_DEBRIEF_UPDATE_PKG which calls CSF_DEBRIEF_CHARGES which calls CS_Service_Billing_Engine_PVT which calls CS_Charge_Details_PUB which calls CS_Charge_Details_PVT which calls CS_ESTIMATE_DETAILS_PKG.

The package CS_ESTIMATE_DETAILS_PKG inserts a record into table CS_ESTIMATE_DETAILS for each debrief line. Prior to calling CS_ESTIMATE_DETAILS_PKG, the package CS_Charge_Details_PVT invokes a pre hook and after insertion it calls post hook API.

The hook needs to be registered. I did this by running this query

insert into JTF_USER_HOOKS values (9999999, 'CS_CHARGE_DETAILS_PVT', 'Create_Charge_Details', 'CS', 'A', 'Y', 'C',

1159, sysdate, 1159, sysdate, 0, null, null, null, null, null)

Field description:

The first 3 parameters need to be these 3 values specified for debrief. A signifies post hook (B is for prehook), Y is set to enable the hook (N is to disable), 7th parameter should be 'C' (Customer Hook) rest all fields are WHO fields/attributes (attributes are blank).

The hook package that is called by CS_Charge_Details_PVT is CS_CHARGE_DETAILS_CUHK. It contains methods Create_Charge_Details_Pre, Create_Charge_Details_Post, Update_Charge_Details_Pre, Update_Charge_Details_Post. Oracle has provided the package specification for this hook. We would need to create the package body.

Oracle R12 Field Operation Track (CRM) Information

How to Activate Custom table for PPC in oracle
------------------------------------------------

Enable Profile ASG_CUSTOM_FLAG

Run Installation Manager for R12

Installation Manager

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

java -Xms32m -Xmx1536m -DJTFDBCFILE=/u000/oracle/DEV/inst/apps/DEV_rpc5009/appl/fnd/12.0.0/secure/DEV.dbc -DAFLOG_ENABLED=TRUE -DAFLOG_LEVEL=STATEMENT oracle.apps.asg.setup.InstallationMgr ibm5001 1525 DEV APPS apps MOBILEADMIN mobiledev JTM_TOP jtm/JTM.ini html/download/jtm.zip

Steps to enable downloading of data into custom tables- olite

The example uses the custom table CSM_CUSTOM_4_B. For this,

The PUBLICATION is : SERVICEP_CUSTOM

The PUBLICATION ITEM is : CSM_CUSTOM_4

The ACC TABLE is : CSM.CSM_CUSTOM_4_ACC

The BASE TABLE: CSM.CSM_CUSTOM_4_B

The INQ TABLE is : CSM_CUSTOM_4_INQ (used in the upward sync)

The PUBLICATION ITEM VIEW is : APPS.CSM_CUSTOM_4_V

1. First determine the custom base table/view that needs to be downloaded on to the PPC. For this example the table/view is MTL_SYSTEM_ITEMS_B1_KFV. Please note that the table/view owner must be APPS- determined using query "select * from dba_objects where object_name = 'MTL_SYSTEM_ITEMS_B1_KFV'"

2. Determine the primary key fields of this table. I have assumed them to be INVENTORY_ITEM_ID, ORGANIZATION_ID.

3. Determine the additional fields (other than primary) that need to be downloaded on to PPC. I have assumed it to be SHIPPABLE_ITEM_FLAG.

4. Identify a Custom table from CSM_CUSTOM_1, CSM_CUSTOM_2, CSM_CUSTOM_3, CSM_CUSTOM_4, CSM_CUSTOM_5, CSM_CUSTOM_6 that meets your criteria. Some of these have a single field as primary key and some have 2 fields set as primary key. I have assumed CSM_CUSTOM_4 since it contains 2 primary key fields.

5. Create the publication Item view. The publication item view is downloaded onto the olite (on PPC) custom table. This view is created/modified using the API ASG_CUSTOM_PUB.customize_pub_item. (The API was throwing error. I have created a new Package asg_custom_pvt_hc in order to make this work). The input parameters to this API are:

NCR_FO_ASG_CUSTOM_PUB

ASG_CUSTOM_PUB.customize_pub_item(

p_api_version_number =>1.0 ,

p_init_msg_list =>FND_API.G_TRUE, (to get output error message)

p_pub_item_name =>'CSM_CUSTOM_4', (specify the custom table name in which this would be downloaded on to PPC)

p_base_table_name =>'MTL_SYSTEM_ITEMS_B1_KFV', (specify the base table/view name)

p_primary_key_columns =>'INVENTORY_ITEM_ID, ORGANIZATION_ID', (specify the primary key fields of the base table/view, These would be part of publication item)

p_data_columns =>'SHIPPABLE_ITEM_FLAG', (specify the additional fields of the base table/view. These would be part of publication item)

p_additional_filter =>null, (any query criteria could be specified)

x_msg_count =>x_msg_count,

x_return_status =>x_return_status,

x_error_message =>x_error_message

Once this API is executed, the Publication Item view query changes from

CREATE OR REPLACE FORCE VIEW csm_custom_4_v (

access_id, attribute1, attribute2, attribute3, attribute4,

attribute5, attribute6, attribute7, attribute8, attribute9,

attribute10, attribute11, attribute12, attribute13, attribute14,

attribute15, attribute16, attribute17, attribute18, attribute19,

attribute20, attribute21, attribute22, attribute23, attribute24,

attribute25, attribute26, attribute27, attribute28, attribute29,

attribute30 )

SELECT ACC.ACCESS_ID, b.attribute1, b.attribute2, b.attribute3, b.attribute4, b.attribute5, b.attribute6, b.attribute7, b.attribute8, b.attribute9, b.attribute10, b.attribute11, b.attribute12, b.attribute13, b.attribute14, b.attribute15, b.attribute16, b.attribute17, b.attribute18, b.attribute19, b.attribute20, b.attribute21, b.attribute22, b.attribute23, b.attribute24, b.attribute25, b.attribute26, b.attribute27, b.attribute28, b.attribute29, b.attribute30

FROM csm.csm_custom_4_acc acc, csm.csm_custom_4_b b

WHERE ACC.USER_ID = ASG_BASE.GET_USER_ID

AND ACC.ATTRIBUTE1 = B.ATTRIBUTE1

AND ACC.ATTRIBUTE2 = B.ATTRIBUTE2

CREATE OR REPLACE FORCE VIEW APPS.CSM_CUSTOM_4_V

(ACCESS_ID, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,

ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,

ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,

ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19,

ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24,

ATTRIBUTE25, ATTRIBUTE26, ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29,

ATTRIBUTE30)

select acc.access_id , to_char(MTL_SYSTEM_ITEMS_B1_KFV.INVENTORY_ITEM_ID) attribute1, to_char(MTL_SYSTEM_ITEMS_B1_KFV.ORGANIZATION_ID) attribute2 , MTL_SYSTEM_ITEMS_B1_KFV.SHIPPABLE_ITEM_FLAG attribute3, NULL ATTRIBUTE4, NULL ATTRIBUTE5, NULL ATTRIBUTE6, NULL ATTRIBUTE7, NULL ATTRIBUTE8, NULL ATTRIBUTE9, NULL ATTRIBUTE10, NULL ATTRIBUTE11, NULL ATTRIBUTE12, NULL ATTRIBUTE13, NULL ATTRIBUTE14, NULL ATTRIBUTE15, NULL ATTRIBUTE16, NULL ATTRIBUTE17, NULL ATTRIBUTE18, NULL ATTRIBUTE19, NULL ATTRIBUTE20, NULL ATTRIBUTE21, NULL ATTRIBUTE22, NULL ATTRIBUTE23, NULL ATTRIBUTE24, NULL ATTRIBUTE25, NULL ATTRIBUTE26, NULL ATTRIBUTE27, NULL ATTRIBUTE28, NULL ATTRIBUTE29, NULL ATTRIBUTE30

from MTL_SYSTEM_ITEMS_B1_KFV, CSM_CUSTOM_4_ACC acc

where acc.user_id = asg_base.get_user_id

and to_char(MTL_SYSTEM_ITEMS_B1_KFV.INVENTORY_ITEM_ID) = acc.ATTRIBUTE1

and to_char(MTL_SYSTEM_ITEMS_B1_KFV.ORGANIZATION_ID) = acc.ATTRIBUTE2

6. In order to download the data onto the PPC, first the data needs to be entered into the Access Tables and then mark dirty API needs to be called.

To insert into access tables,

6.1 Determine the record's primary key fields that you want to download to PPC, in sample I used 88, 1222 (select INVENTORY_ITEM_ID, ORGANIZATION_ID, SHIPPABLE_ITEM_FLAG from MTL_SYSTEM_ITEMS_B1_KFV)

6.2 Determine the user_id to which this data should be downloaded using SELECT * FROM asg_user where user_id = 1222- (for US114H71)1226- US114H82

6.3 Determine the apps user that trigerred the change on APPS side- select * from fnd_user where user_id = 1159 (for HC185011)

6.4 Determine the last login - select * from FND_LOGINS where user_id = 1159 (login for HC185011)

6.5 insert record into access table-

insert into CSM_CUSTOM_4_ACC (access_id, ATTRIBUTE1, ATTRIBUTE2, user_id, counter, created_by, creation_date, last_updated_by, last_update_date, last_update_login) values (1, 2001, 88, 1222, 1, 1159, sysdate, 1159, sysdate, 1191116)

Access Id is the primary key field for this table.

6.6 Invoke API corresponding rto the data inserted into access table.

ASG_CUSTOM_PUB.mark_dirty (

p_api_version_number =>1.0,

p_init_msg_list =>FND_API.G_TRUE, (to get output error message)

p_pub_item =>'CSM_CUSTOM_4',

p_accessid =>1,

p_userid =>1222,

p_dml => 'I', (use I- Insert, D delete and U for update)

p_timestamp => SYSDATE,

x_return_status =>x_return_status

);

You would need to commit the data after this API runs. This inserts data into asg_system_dirty_queue to be sent to the PPC user.

7. Sync on the PPC to get the record on olite.

R12 OA Framework Extra Info

LOG message
Whenever you place any System.out.println() in your controller or any where in OA framework. You can get this output in below mentioned file.

The log message should get written in,
$INST_TOP/logs/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.out

Enabling Low Level login

you can set the below profiles using System Admin responsibility.

FND: Debug Log Enabled

FND: Debug Log Level

FND: Debug Log Module

FND: Debug Log Filename for Middle-Tier

Restart Apache
$ADMIN_SCRIPTS_HOME/adapcctl.sh stopall

DBC file location in R12:
$INST_TOP/appl/fnd/12.0.0/secure

JDR_UTILS

Use this API to list of all OA Framework the documents in a path/module. This script is very handy, as it provides a list of all the desired documents/extensions/personalizations in one go.

DECLARE
BEGIN
jdr_utils.listdocuments('/oracle/apps/per', TRUE);
END;

The script above will produce a list of all the MDS Documents in HRMS plus all the BC4J extensions done to HRMS module will be listed here too. Surely you can run this for whatever module/application you desire.

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

List of all the Personalizations/Extensions/Contents for a specific object. For example, as below, I wish to know the various levels at which a specific object has been personalized or extended.

DECLARE
BEGIN
jdr_utils.listcustomizations
(p_document => '/oracle/apps/per/irc/candidateSelfService/webui/RegCreateAccountPG');
END;

Delete Document / Delete A Personalization/ Delete Extension or Substitution

The title says it all. Once you have found the exact name of the document with its full path [using jdr_utils.listdocuments], you can then delete the same by using command below. For example to delete the Application Module substitution, use command

DECLARE
BEGIN
jdr_utils.deletedocument(p_document => '/oracle/apps/per/irc/candidateSelfService/server/customizations/site/0/VisitorAM');
END;

Import Region or Page into database

Go to ..\jdevbin\oaext\bin directory and run import

Example:

import
C:\Jdev1\jdevhome\jdev\myprojects\ncr\fo\oracle\apps\csf\portal\task\webui\CsfExtendedInstallRN.xml -rootdir C:\Jdev1\jdevhome\jdev\myprojects -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ibm5001.daytonoh.ncr.com)(PORT=1525))(CONNECT_DATA=(SID=DEV)))"

import Substitution into Database

Example:

jpximport C:\WS_FTP\Debrief.jpx -userId 1 -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ibm5001.daytonoh.ncr.com)(PORT=1525))(CONNECT_DATA=(SID=DEV)))"

Run Oracle Stored Procedure Parallel

DBMS_JOB is a nice way to execute jobs in parallel because you control the degree of concurrency. You could set up to 36 job queue processes (in 8i) or 1,000 (in 9i) and that would be the degree of concurrency.

The performance benefits could be substantial. For example, I did a performance test on two 1-million-row tables with four indexes to rebuild. The following snippet is the first block of code I used, which rebuilds indexes with NOLOGGING PARALLEL options serially using the RebuildIndex procedure of my Maintenance package:

declare    vStart number;
begin   
vStart := dbms_utility.get_time;

NCR_NZ_REPLAY_PUB_PKG_1.replay_all(23859,23985);
'NCR_NZ_REPLAY_PUB_PKG_1.replay_all(23994,24120);


dbms_output.put_line('Elapsed time: ' ||       round((dbms_utility.get_time - vStart)/100, 2) || ' hsecs' );
end;
/

The second block of code rebuilds indexes in parallel using DBMS_JOB:

declare

vJob number;
x_return_status VARCHAR2(100);

begin

dbms_job.submit(job => vJob, what => 'NCR_NZ_REPLAY_PUB_PKG_1.replay_all(23859,23985);');
dbms_output.put_line('First Job ' || to_char(vJob));

dbms_job.submit(job => vJob, what => 'NCR_NZ_REPLAY_PUB_PKG_1.replay_all(23994,24120);');
dbms_output.put_line('Second Job ' || to_char(vJob));

commit;
end;
/

Disable Oracle APPS Responsiblity from backend on R12

Please make sure that you are working on Oracle R12 because this may be different in Oracle 11i

Find out responsibility for the user you want to disable.

End date the below table for that user and responsibility

WF_USER_ROLE_ASSIGNMENTS.end_date

You can analyse below view to know more about it.

FND_USER_RESP_GROUPS (View)

Thursday, July 9, 2009

Order Management Tables Starting from Entered Status to Closed Status

Entered
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.

Booked
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release

Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock

Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines

wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned

Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock

Ship Confirmed

wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped

oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N

Autoinvoice

wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program for
ra_customer_trx_lines_all specific batch transaction tables get populated

Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines

Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms

AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.
Customer Information
hz_parties Get Customer information include name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers
Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes
End User Details
csi_t_party_details To capture End user Details

Sales Credit Sales Credit Information(How much credit can get)
oe_sales_credits

Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text

Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all

Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions
Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all

Hold Relaese
oe_hold_releases_all Hold released Sales Order.

Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.
Cancel Orders
oe_order_lines_all Cancel Order Details.

Send Popup Message to all Logged in Oracle APPS users

Requirement : - To send Popup Message to all Logged in Oracle APPS user

First I use below query to find the user_name of Logged in user.

select distinct d.user_name
from apps.fnd_logins a,
v$session b,
v$process c,
apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)

You all can user above query and make JDBC connection to get user_name.

To Send Message to user I have used net send command :

Uses of net send Command
------------------------------

net send username/IP Address/machine name "Test Message"

Now I have tried to access Machine name from Active Directory Service and It may possible the
Single person is having one or more machine registered on his/her name. Then below jave programming section will return all machine name for single user.

Hashtable env = new Hashtable();
String adminName = "Your Domain Name\\Your user ID";
String adminPassword = "Your Login Password";
String ldapURL = "ldap://your domain URL.com:389";
String searchFilter=null;
String searchBase = null;
env.put(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.ldap.LdapCtxFactory");
env.put(Context.SECURITY_AUTHENTICATION,"simple");
env.put(Context.SECURITY_PRINCIPAL,adminName);
env.put(Context.SECURITY_CREDENTIALS,adminPassword);
env.put(Context.PROVIDER_URL,ldapURL);
LdapContext ctx = new InitialLdapContext(env,null);
SearchControls searchCtls = new SearchControls();
String returnedAtts[] = {"sn", "givenName", "mail"};
searchCtls.setReturningAttributes(returnedAtts);
searchCtls.setSearchScope(SearchControls.SUBTREE_SCOPE);
searchFilter = "(&(objectClass=computer)(CN=*"+Place search user id+"*))";
searchBase = "DC=your domain,DC=,DC=com";
NamingEnumeration answer = ctx.search(searchBase, searchFilter, searchCtls);
while (answer.hasMoreElements()) {
SearchResult sr = (SearchResult)answer.next();
// You can place your own logic to find out machine name to index out
String Machine = sr.getName().substring(sr.getName().indexOf("=")+1,sr.getName().indexOf(","));
}

Now we have got machine name from above process in Machine variable.

Please below java logic to send message.

try
{
int i=0;

String[] command = new String[3];

command[0] = "cmd";
command[1] = "/C";
command[2] = "net send "+ Machine+"Your Message";

i++;
Process p = Runtime.getRuntime().exec(command);
BufferedReader stdInput = new BufferedReader(new
InputStreamReader(p.getInputStream()));

BufferedReader stdError = new BufferedReader(new
InputStreamReader(p.getErrorStream()));

String s = null;
System.out.println("Here is the standard output of the command:\n");
while ((s = stdInput.readLine()) != null) {
System.out.println(s);
}

// read any errors from the attempted command

System.out.println("Here is the standard error of the command (if any):\n");
while ((s = stdError.readLine()) != null) {
System.out.println(s);
}

}

catch (Exception e)
{
System.out.println(e);
}

}

}
select.close();
con.close();
}
catch( Exception e ) {
e.printStackTrace();
}

Thats All. Enjoy :)

Innovative Zero