What is the difference between a Role and a Responsibility?

Responsibilities can now be considered a special type of role that represents the set of navigation menus contained within an application. Therefore, responsibilities loosely represent an application itself, whereas roles can be used to determine what parts of that application (and data therein) a user has access. This represents a shift in the definition of a responsibility in Oracle Applications. Previously, a responsibility has been used not only to define the application navigation menus, but also to confer privileges and permissions within that application. Using this definition of responsibility, it was often necessary to create several similar responsibilities in order to effectively carve out data and functional security access for a group of users. This has increased the overall cost of ownership as the number of responsibilities has grown.
Oracle Applications follows the Role Based Access Control (RBAC) Reference Model (ANSI INCITS 359-2004) definition of a role as "a job function within the context of an organization with some associated semantics regarding the authority and responsibility conferred on the user assigned to the role." Roles can now be defined to determine what applications (responsibilities) as well as what data and functions within those applications a user has access to.
By leveraging the RBAC model, users will no longer need to be directly assigned the lower level permissions and responsibilities, as these can be implicitly inherited based upon the roles assigned to the user. Roles can now be defined to consolidate responsibilities and other roles through role inheritance, as well as lower level permissions (functions) and data security policies. This is accomplished through a one-time setup, where all the permissions are assigned to the role. In order to make a mass update in a production system a client only needs to change the permissions or role inheritance hierarchies defined for a role, then all of the users assigned to that role will instantly inherit the new permissions.

Creating USER MANAGEMENT ROLE in ORACLE through API

Role Creation API

UMX_ACCESS_ROLES_PVT.insert_role(p_role_name in varchar2,
p_orig_system in varchar2,
p_orig_system_id in number,
p_start_date in date,
p_expiration_date in date,
p_display_name in varchar2,
p_owner_tag in varchar2,
p_description in varchar2);

Example;

begin
UMX_ACCESS_ROLES_PVT.insert_role('UMXRAJEEVCODE2',
'UMX',
0,
to_date('18/11/2009','DD/MM/YYYY'),
null,
'rajeev role name2',
'NCRX',
'rajeev desc2');
commit;
end;

Assigement of Roles to Users

Begin
wf_local_synch.PropagateUserRole(
p_user_name => '&USER_NAME',
p_role_name => '&ROLE_KEY');
commit;
end;

We can create or Assign Role from SYSADMIN user though USER MANAGEMENT RESPONSIBILITY.

DBMS SCHEDULAR

Creating Schedule:-
begin

dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_1_MINUTES', start_date => trunc(sysdate)+18/24, repeat_interval => 'freq=MINUTELY;interval=1', comments => 'Runtime: Every day all 1 minutes');

dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_1_SECONDLY', start_date => sysdate, repeat_interval => 'freq=SECONDLY;interval=1', comments => 'Runtime: Every day all 1 SECOND');
dbms_scheduler.create_schedule( schedule_name => 'INTERVAL_EVERY_0_SECONDLY', start_date => sysdate, repeat_interval => 'freq=SECONDLY;interval=0', comments => 'Runtime: Every day all 0 SECOND');
commit;
end;

Creating Program :-

begin -- Call a procedure of a database package

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB1', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(0,1); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders for 0,1' );

-- Call a procedure of a database package

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB2', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(2,3); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders 2,3' );

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB3', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(4,5); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders 4,5' );

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB4', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(6,7); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders for 6,7' );

dbms_scheduler.create_program (program_name=> 'REPLAY_STANDARD_ORDER_JOB5', program_type=> 'PLSQL_BLOCK', program_action=> 'begin OE_REPLAY_PUB_PKG.replay_all(8,9); end;', enabled=>true, comments=>'Procedure to Replay Standard Orders for 8,9' ); commit;
end;


Creating Jobs :--

begin -- Connect both dbms_scheduler parts by creating the final job

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_0_AND_1', program_name=> 'REPLAY_STANDARD_ORDER_JOB1', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_2_AND_3', program_name=> 'REPLAY_STANDARD_ORDER_JOB2', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_4_AND_5', program_name=> 'REPLAY_STANDARD_ORDER_JOB3', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_6_AND_7', program_name=> 'REPLAY_STANDARD_ORDER_JOB4', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 0 minutes');

dbms_scheduler.create_job (job_name => 'REPLAY_JOB_FOR_8_AND_9', program_name=> 'REPLAY_STANDARD_ORDER_JOB5', schedule_name=>'INTERVAL_EVERY_0_SECONDLY', enabled=>true, auto_drop=>false, comments=>'Job to collect data about session values every 5 minutes');
end;

Run Jobs:-

begin dbms_scheduler.run_job('REPLAY_JOB_FOR_0_AND_1',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_2_AND_3',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_4_AND_5',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_6_AND_7',TRUE); end;begindbms_scheduler.run_job('REPLAY_JOB_FOR_8_AND_9',TRUE);

end;

drop job:-
begin--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_0_AND_1', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_2_AND_3', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_4_AND_5', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_6_AND_7', force=>TRUE);--DBMS_SCHEDULER.STOP_JOB(job_name=>'REPLAY_JOB_FOR_8_AND_9', force=>TRUE);
dbms_scheduler.drop_job('REPLAY_JOB_FOR_0_AND_1');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_2_AND_3');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_4_AND_5');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_6_AND_7');
dbms_scheduler.drop_job('REPLAY_JOB_FOR_8_AND_9');

end;


Extra Information

DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'INTERVAL_EVERY_MINUTE', attribute => 'repeat_interval', value => 'freq=MINUTELY;interval=2' );
select * from user_scheduler_job_log where job_name='REPLAY_JOB_FOR_0_AND_1';
Select * from DBA_SCHEDULER_JOB_RUN_DETAILS where (job_name = 'REPLAY_JOB_FOR_0_AND_1' OR job_name = 'REPLAY_JOB_FOR_2_AND_3') and run_duration <> '+00 00:00:00.000000' order by actual_start_date desc
Select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name = 'REPLAY_JOB_FOR_0_AND_1' order by actual_start_date desc

Agile Project Management

Agile Open Source Project Management Tool

http://collaborate.d2labs.org/projects/saralscrum/

3 Reasons Why I Wouldn't Do Agile Project Management

http://www.agile-software-development.com/2009/03/3-reasons-why-i-wouldnt-do-agile.html

10 Key Principales of Agile Development

http://www.agile-software-development.com/search/label/10%20Key%20Principles

How to put Log lines in OA Framework program

OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getOADBTransaction();

if(oadbtransactionimpl.isLoggingEnabled(2)) { oadbtransactionimpl.writeDiagnostics(this, "insertRow().BEGIN", 2); }

Through this if we have enabled Diagnostic on then we can see our logs while runtime.

We can put anywhere in our OA Framework program.

There are Diagnostic option Show Log on Screen

Log Level
Unexpected (6)Error (5)Exception (4)Event (3)Procedure (2)Statement (1)

Easy way to install Apache, MySql, PHP etc

Many people know from their own experience that it's not easy to install an Apache web server and it gets harder if you want to add MySQL, PHP and Perl.
XAMPP is an easy to install Apache distribution containing MySQL, PHP and Perl. XAMPP is really very easy to install and to use - just download, extract and start.
http://www.apachefriends.org/en/xampp.html

Create Service Request and Task through API

declare
lx_msg_count NUMBER;
lx_msg_data VARCHAR2(2000);
lx_request_id NUMBER;
lx_request_number VARCHAR2(50);
lx_interaction_id NUMBER;
lx_workflow_process_id NUMBER;
lx_msg_index_out NUMBER;
lx_return_status VARCHAR2(1);
l_service_request_rec CS_ServiceRequest_PUB.service_request_rec_type;
l_service_request_rec_out CS_ServiceRequest_PUB.sr_create_out_rec_type ;
l_notes_table CS_SERVICEREQUEST_PUB.notes_table;
l_contacts_tab CS_SERVICEREQUEST_PUB.contacts_table;
j number;
lx_individual_owner NUMBER;
lx_group_owner NUMBER;
lx_individual_type VARCHAR2(100) ;

begin

FOR J IN 1 .. 10000
LOOP
-- Populate the SR Record type
--l_service_request_rec.incident_country:= 'US';
--l_service_request_rec.incident_postal_code:= '45202';
l_service_request_rec.incident_location_type :='HZ_PARTY_SITE';
l_service_request_rec.INCIDENT_LOCATION_ID := 276016; --262651;
l_service_request_rec.incident_occurred_date := SYSDATE;
l_service_request_rec.INSTALL_SITE_ID := 276016; --262651;
l_service_request_rec.request_date := SYSDATE;
l_service_request_rec.type_id := 10006; ---
l_service_request_rec.status_id := 1; ---
l_service_request_rec.severity_id := 2; ---
l_service_request_rec.urgency_id := 2;---
--l_service_request_rec.owner_id := 100000101;
l_service_request_rec.summary := 'SR API TEST'; ---
l_service_request_rec.caller_type := 'ORGANIZATION';
l_service_request_rec.customer_number := '276421CH'; --'100692CH'; ---
--l_service_request_rec.verify_cp_flag := 'N';
l_service_request_rec.inventory_item_id := 628814; --607498;
l_service_request_rec.inventory_org_id := 45;
l_service_request_rec.category_id := 3346;
l_service_request_rec.category_set_id := 1;
IF ( l_service_request_rec.owner_id IS NULL
OR l_service_request_rec.owner_id = Fnd_Api.g_miss_num
)
THEN
l_service_request_rec.territory_id := NULL;
ELSE
l_service_request_rec.territory_id := 83;
END IF;

l_notes_table(1).note := 'Test Note';
l_notes_table(1).note_detail:= 'Note details entered here'; -- NULL
l_notes_table(1).note_type := 'ACT_HIS';

dbms_output.put_line('Before calling Public SR API');

cs_servicerequest_PUB.Create_ServiceRequest (
p_api_version => 4.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
x_return_status => lx_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_resp_appl_id => 690,
p_resp_id => 21787,
p_user_id => 10780,
p_login_id => 00,
p_org_id => 150,
p_request_id => null,
p_request_number => null,
p_service_request_rec => l_service_request_rec,
p_notes => l_notes_table,
p_contacts => l_contacts_tab,
P_auto_assign => 'Y',
p_auto_generate_tasks => 'Y',
x_sr_create_out_rec => l_service_request_rec_out,
p_default_contract_sla_ind =>'N',
p_default_coverage_template_id => 0);

if l_service_request_rec_out.auto_task_gen_attempted THEN
dbms_output.put_line('auto task gen status : TRUE');
end if;

dbms_output.put_line('Return Status : ' lx_return_status);
dbms_output.put_line('Inserted request id: ' l_service_request_rec_out.request_id );
dbms_output.put_line('Inserted request num : ' l_service_request_rec_out.request_number );
dbms_output.put_line('Interaction ID : ' l_service_request_rec_out.interaction_id );
dbms_output.put_line('auto task gen status 'l_service_request_rec_out.auto_task_gen_status);
--dbms_output.put_line('auto task gen attempted : 'l_service_request_rec_out.auto_task_gen_attempted);

IF (lx_return_status <> FND_API.G_RET_STS_SUCCESS) then
IF (FND_MSG_PUB.Count_Msg > 1) THEN
--Display all the error messages
FOR j in 1..FND_MSG_PUB.Count_Msg LOOP
FND_MSG_PUB.Get( p_msg_index => j,
p_encoded => 'F', p_data => lx_msg_data,
p_msg_index_out => lx_msg_index_out);

DBMS_OUTPUT.PUT_LINE(lx_msg_data);
END LOOP;
ELSE
--Only one error
FND_MSG_PUB.Get( p_msg_index => 1,
p_encoded => 'F',
p_data => lx_msg_data,
p_msg_index_out => lx_msg_index_out);DBMS_OUTPUT.PUT_LINE(lx_msg_data);
DBMS_OUTPUT.PUT_LINE(lx_msg_index_out);
END IF;
END IF;
END LOOP;
exception
when others then dbms_output.put_line('in others main ' sqlerrm);
end;

Prints the whole menu tree for a given responsibility

SET SERVEROUTPUT ON size 1000000
SET LINESIZE 20000
SPOOL c:/submenu_menu.txt

declare
-- this method prints the whole menu tree for a given responsibility
-- if you want to run for a different responsibility change the
-- l_resp name

l_resp_name varchar2(100) := 'NCR Call-1 Agent';
l_menu_id number;
l_main_menu varchar2(100);

procedure dfs(p_menu_id in varchar2, p_level in number) is

cursor get_submenus is
select e.entry_sequence, m.menu_name, m.user_menu_name, e.sub_menu_id, e.function_id, f.function_name, e.prompt, f.web_html_call, f.user_function_name, e.grant_flag
from fnd_menus_vl m, fnd_menu_entries_vl e, fnd_form_functions_vl f
where e.sub_menu_id = m.menu_id(+)
and e.function_id = f.function_id(+)
and e.menu_id = p_menu_id
order by 1;

l_spaces varchar2(30) := '';
granted varchar2(30);

begin
-- spacing
for i in 1..p_level loop
l_spaces := l_spaces || '..';
end loop;

--loop through menus
for c in get_submenus loop
granted := '';
if c.grant_flag = 'Y' then granted := ' [granted]'; end if;
if c.sub_menu_id is null then
dbms_output.put_line(l_spaces || 'FUNCTION ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.function_name || ' (' || c.user_function_name || ')');
-- dbms_output.put_line(l_spaces || '...........src=' || c.web_html_call);
else
dbms_output.put_line(l_spaces || 'MENU (' || p_level || ') ' || nvl(c.prompt, '[hidden]') || granted || ' ' || c.menu_name || ' (' || c.user_menu_name || ')');
end if;
dfs(c.sub_menu_id, p_level+1);
end loop;
end;
begin
select menu_id into l_menu_id
from fnd_responsibility_vl where responsibility_name = l_resp_name;
select menu_name into l_main_menu from fnd_menus where menu_id = l_menu_id;
dbms_output.put_line('MAIN MENU ' || l_main_menu);
dfs(l_menu_id, 1);
end;
spool off;

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

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

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 CSM_TOP CSW.ini html/download/CSW.zip

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 CSM_TOP CSML.ini html/download/CSML.zip

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 CSM_TOP CSW_2000_ARM.ini html/download/CSW.zip

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 CSM_TOP CSW_2003_XSCALE.ini html/download/CSW.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 )

AS

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

To

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)

AS

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)))"