The concept of Business Events in the context of EBS plays a critcal role in enabling event-driven integration with other systems outside the application.
In addition to that, the Business Events in E-Business Suite in particular allow for an exceptionally effective way of decoupling the standard product functionality, available out of the box, from client customizations that seek to adapt the standard product to meet customer-specific business needs. In other words, Oracle Applications developers should consider using Business Events whenever possible when configuring and customizing the standard products.
dgreybarrow What are Business Events
“A business event is an occurrence in an internet or intranet application or program that might be significant to other objects in a system or to external agents.”
For example, the creation of a purchase order is an example of a business event in a purchasing application
Business events
dgreybarrow Oracle Business Events =>Architecture
The Oracle Workflow Business Event System is an application service that leverages the Oracle Advanced Queuing (AQ) infrastructure to communicate business events between systems.
The Business Event System consists of the Event Manager and workflow process event activities
Is available with both standalone and E-Business Suite Workflow
Provides event driven processing
Allows Application modules and external systems to raise events
Facilitates Oracle Application modules and external system to subscribe to these events
Subscriptions can be synchronous or asynchronous
Business events1
dgreybarrow Do you know,
11i10 E-Business Suite is preconfigured with 915 Business Events
Each Business Event represents a ready to use Integration or extension point
915 Outbound Integration/extension points
915 Inbound Integration/extension points
Integration points centered around the major E-Business Suite flows like p2p, o2c etc
dgreybarrowComponent Architecture
Typically Business events Component can be best understood as:
Business eventscomponents
Transactional Diagram of business Events can be best understood as:
Business events2
Below is Architectural Diagram for Inbound Business Events , typical flow consist of
Event Name
Payload
Event Parameter
Unique Event Key (auto generated)
Business events3
Below is Architectural Diagram for Outbound Business Events , typical flow consist of
Creates deferred subscription to the selected
Deferred subscription transfers the event to the customer queue (WF_BPEL_Q)
Unique consumer is created automatically
Business events4
dgreybarrow Event Manager for Oracle Applications
The Oracle Workflow Event Manager lets you register interesting business events that may occur in your applications, the systems among which events will be communicated, named communication agents within those systems, and subscriptions indicating that an event is significant to a particular system. The Event Manager also performs subscribtion processing when events occur.
dgreybarrow Subscriptions for Business Events
Events that trigger custom code
Events that send information to Workflow
Events that send information to other queues or systems
dgreybarrow where you can Uses of Business Events
System integration messaging hubs
Distributed applications messaging
Message-based system integration
Business-event based workflow processes
Non-invasive customization of packaged applications
dgreybarrow PLSQL vs Java Business Event System
Oracle Workflow provides Business Event System implementation within the database (PLSQL) and in the middle tier (Java).
The implementation is exactly the same in terms of the event subscription processing in both these layers but the only difference is how the Developer wants to leverage Business Event System's capabilities for event processing requirements.
With the availability of Business Event System implementation in PLSQL and Java, different subscription processing scenarios can be achieved.
dgreybarrow How to Proceed if Business events are required to use
Design your Business Event/s
Define your event
dgreybarrow Setting Up the Business Event System [Adopted workflow user documentation]
To set up the Business Event System and enable message propagation, perform the following steps:
If you want to communicate business events between the local system and external systems, create database links to those external systems.
If you want to use custom queues for propagating events, set up your queues.
Check the Business Event System setup parameters.
Schedule listeners for local inbound agents.
Schedule propagation for local outbound agents.
If you are using the version of Oracle Workflow embedded in Oracle Applications, synchronize event and subscription license statuses with product license statuses.
Ensure that the WF_CONTROL queue is periodically cleaned up to remove inactive subscribers.
For Creating a event in R11i or R12 you can refer to Oracle documentation .
Sunday, December 24, 2017
How to enable and disable Self Service Personal by script
Enable Disable Self-Service Personal by script
DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('FND_DISABLE_OA_CUSTOMIZATIONS', 'Y', 'SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;
Disable Disable Self-Service Personal by script
DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('FND_DISABLE_OA_CUSTOMIZATIONS', 'N', 'SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;
How to clear cache from Application (frontend)
By using Functional Administrator responsibility, cache can be cleared.
Switch to Functional Administrator/ Core Services/ Caching Framework / Global Configuration
Clear All Statistics or Clear All Cache
Switch to Functional Administrator/ Core Services/ Caching Framework / Global Configuration
Clear All Statistics or Clear All Cache
Friday, December 22, 2017
How to join GL tables to XLA tables
select
*
from
gl_je_headers gh,
gl_je_lines jl,
xla_ae_lines xl,
xla_ae_headers xh
where
gh.je_header_id=jl.je_header_id
and xl.gl_sl_link_id=jl.gl_sl_link_id
and xl.gl_sl_link_table=jl.gl_sl_link_table
and jl.gl_sl_link_table='XLAJEL'
and xh.ae_header_id=xl.ae_header_id
and xh.ae_header_id=461566960
*
from
gl_je_headers gh,
gl_je_lines jl,
xla_ae_lines xl,
xla_ae_headers xh
where
gh.je_header_id=jl.je_header_id
and xl.gl_sl_link_id=jl.gl_sl_link_id
and xl.gl_sl_link_table=jl.gl_sl_link_table
and jl.gl_sl_link_table='XLAJEL'
and xh.ae_header_id=xl.ae_header_id
and xh.ae_header_id=461566960
Monday, December 18, 2017
Removing OA Framework VO substitutions
As part of OA
Framework extensibility it is possible to substitute a VO definition, which
allows to satisfy business requirements such as adding additional conditions to
it as well as to retrieve additional columns.
Such extensions, however, are not shown on the personalization catalog available under the System Administration responsibility and thus are not easily disabled in case something is wrong with them.
In order to remove the substitution definition, you have to identify the customization path and then use the jdr API to remove it.
In order to identify the customization, use the following call:
Such extensions, however, are not shown on the personalization catalog available under the System Administration responsibility and thus are not easily disabled in case something is wrong with them.
In order to remove the substitution definition, you have to identify the customization path and then use the jdr API to remove it.
In order to identify the customization, use the following call:
begin
jdr_utils.listcustomizations('/oracle/apps/po/notifications/server/PoNotifLinesSummaryVO');
end
It will output the path of the customization, which is the one to remove.
/oracle/apps/po/notifications/server/customizations/site/0/PoNotifLinesSummaryVO
In order to delete the customization definition, use the following call, using the output from the previous command as a parameter.
begin
jdr_utils.deletedocument('/oracle/apps/po/notifications/server/customizations/site/0/PoNotifLinesSummaryVO');
end;
You will get the following message if everything is ok, otherwise you could get an error if document is not found in case you did not properly copy the path or if you execute the command a second time.
Succesfuly deleted document /oracle/apps/po/notifications/server/customizations/site/0/PoNotifLinesSummaryVO
Now, be careful not to pass the base document path to the delete call, otherwise you will remove the actual seeded VO or page definition and you will panic (I did) when the page no longer opens when called on the application. Do not Worry, you will find the xml either on $JAVA_TOP or under the mds folder of the corresponding application and XMLImporter can be used to restore it.
Monday, November 6, 2017
How to update two way POs from Receipt to PO
update PO_LINE_LOCATIONS_ALL pll set pll.match_option='P'
where
pll.inspection_required_flag='N'
and pll.receipt_required_flag='N'
and pll.match_option='R'
where
pll.inspection_required_flag='N'
and pll.receipt_required_flag='N'
and pll.match_option='R'
Monday, October 30, 2017
API to Create Jobs
declare
l_business_group_id number;
l_job_group_id number;
l_job_id number;
l_object_version_number number;
l_job_definition_id number;
l_job_name varchar2(200);
v_error varchar2(500);
v_name varchar2(100);
begin
-- get group id and business group id
begin
select business_group_id,
job_group_id
into l_business_group_id,
l_job_group_id
from per_job_groups
where displayed_name = 'sample'; -- is used for all business group will be changed
exception
when others then
dbms_output.put_line('Unable to get the job group details.Error:'||sqlerrm);
raise;
end;
for i in (
select
name,
date_from,
vacation_days,
hr.job_id_nav
from
xxx_sample hr where hr.job_id=0
)
loop
v_name:=i.name;
HR_JOB_API.CREATE_JOB
(p_validate => FALSE
,p_business_group_id => l_business_group_id
,p_date_from => to_date('01/01/2006','dd/mm/yyyy')
,p_comments => 'test purpose'
,p_date_to => NULL
,p_job_group_id => l_job_group_id
,p_concat_segments => i.name
--output
,p_job_id => l_job_id
,p_object_version_number => l_object_version_number
,p_job_definition_id => l_job_definition_id
,p_name => l_job_name
,p_attribute11 => i.vacation_days-- annual vacation days
);
DBMS_OUTPUT.PUT_LINE('job id: '||l_job_id);
DBMS_OUTPUT.PUT_LINE('object_version_number: '||l_object_version_number);
DBMS_OUTPUT.PUT_LINE('job_definition_id: '||l_job_definition_id);
DBMS_OUTPUT.PUT_LINE('job_name: '||l_job_name);
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('unable to create a job.error:'||SQLERRM);
rollback;
RAISE;
END;
l_business_group_id number;
l_job_group_id number;
l_job_id number;
l_object_version_number number;
l_job_definition_id number;
l_job_name varchar2(200);
v_error varchar2(500);
v_name varchar2(100);
begin
-- get group id and business group id
begin
select business_group_id,
job_group_id
into l_business_group_id,
l_job_group_id
from per_job_groups
where displayed_name = 'sample'; -- is used for all business group will be changed
exception
when others then
dbms_output.put_line('Unable to get the job group details.Error:'||sqlerrm);
raise;
end;
for i in (
select
name,
date_from,
vacation_days,
hr.job_id_nav
from
xxx_sample hr where hr.job_id=0
)
loop
v_name:=i.name;
HR_JOB_API.CREATE_JOB
(p_validate => FALSE
,p_business_group_id => l_business_group_id
,p_date_from => to_date('01/01/2006','dd/mm/yyyy')
,p_comments => 'test purpose'
,p_date_to => NULL
,p_job_group_id => l_job_group_id
,p_concat_segments => i.name
--output
,p_job_id => l_job_id
,p_object_version_number => l_object_version_number
,p_job_definition_id => l_job_definition_id
,p_name => l_job_name
,p_attribute11 => i.vacation_days-- annual vacation days
);
DBMS_OUTPUT.PUT_LINE('job id: '||l_job_id);
DBMS_OUTPUT.PUT_LINE('object_version_number: '||l_object_version_number);
DBMS_OUTPUT.PUT_LINE('job_definition_id: '||l_job_definition_id);
DBMS_OUTPUT.PUT_LINE('job_name: '||l_job_name);
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('unable to create a job.error:'||SQLERRM);
rollback;
RAISE;
END;
Tuesday, October 17, 2017
How to update Vendor Taxpayer ID (NUM_1099) with API
DECLARE
lc_return_status VARCHAR2(2000);
ln_msg_count NUMBER;
ll_msg_data LONG;
Ln_Vendor_Id NUMBER;
Ln_Vendor_site_Id NUMBER;
ln_message_int NUMBER;
Ln_Party_Id NUMBER;
lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
cursor c_vendors is
select s.taxpayer_id, s.vendor_id_ora from temporary_suppliers s where s.taxpayer_id is not null and segment1='0'
and s.acc_end_date is null;
BEGIN
for i in c_vendors
loop
Ln_Vendor_Id := i.vendor_id_ora;
Lrec_Vendor_Rec.JGZZ_FISCAL_CODE := i.taxpayer_id;
ap_vendor_pub_pkg.update_vendor_public( p_api_version => 1,--
x_return_status => lc_return_status, --
x_msg_count => ln_msg_count, --
x_msg_data => ll_msg_data, --
p_vendor_rec => Lrec_Vendor_Rec, --
p_Vendor_Id => Ln_Vendor_Id);
IF (lc_return_status <> 'S') THEN
IF ln_msg_count >= 1 THEN
FOR v_index IN 1..ln_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => ll_msg_data, p_msg_index_out => ln_message_int );
Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);
dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );
END LOOP;
End If;
END IF;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM - '||SQLERRM );
END;
lc_return_status VARCHAR2(2000);
ln_msg_count NUMBER;
ll_msg_data LONG;
Ln_Vendor_Id NUMBER;
Ln_Vendor_site_Id NUMBER;
ln_message_int NUMBER;
Ln_Party_Id NUMBER;
lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
cursor c_vendors is
select s.taxpayer_id, s.vendor_id_ora from temporary_suppliers s where s.taxpayer_id is not null and segment1='0'
and s.acc_end_date is null;
BEGIN
for i in c_vendors
loop
Ln_Vendor_Id := i.vendor_id_ora;
Lrec_Vendor_Rec.JGZZ_FISCAL_CODE := i.taxpayer_id;
ap_vendor_pub_pkg.update_vendor_public( p_api_version => 1,--
x_return_status => lc_return_status, --
x_msg_count => ln_msg_count, --
x_msg_data => ll_msg_data, --
p_vendor_rec => Lrec_Vendor_Rec, --
p_Vendor_Id => Ln_Vendor_Id);
IF (lc_return_status <> 'S') THEN
IF ln_msg_count >= 1 THEN
FOR v_index IN 1..ln_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => ll_msg_data, p_msg_index_out => ln_message_int );
Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);
dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );
END LOOP;
End If;
END IF;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM - '||SQLERRM );
END;
API to update Customer Category
DECLARE
L_ORGANIZATION_REC hz_party_v2pub.organization_rec_type;
x_profile_id NUMBER;
l_vendor_id NUMBER;
l_party_id NUMBER;
l_object_version_number NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_return_status VARCHAR2(10);
begin
for i in ( select s.party_id, s.vendor_id, 'TEST' category_code from ap_suppliers s, hz_parties hp
where s.party_id=hp.party_id
and hp.category_code is null )
loop
SELECT aps.PARTY_ID, hzp.object_version_number
into l_party_id, l_object_version_number
FROM AP_SUPPLIERS aps, HZ_PARTIES hzp
WHERE
vendor_id =i.vendor_id
AND aps.party_id = hzp.party_id;
l_organization_rec.party_rec.party_id := l_party_id;
l_organization_rec.party_rec.category_code:=i.category_code;
HZ_PARTY_V2PUB.update_organization (
p_init_msg_list => fnd_api.g_true,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => x_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
dbms_output.put_line(l_return_status);
dbms_output.put_line(l_party_id ||i.category_code );
FOR I IN 1..l_msg_count
LOOP
l_msg_data := l_msg_data||SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);
dbms_output.put_line(l_msg_data);
END LOOP ;
end loop;
exception
when others then
raise;
end;
L_ORGANIZATION_REC hz_party_v2pub.organization_rec_type;
x_profile_id NUMBER;
l_vendor_id NUMBER;
l_party_id NUMBER;
l_object_version_number NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_return_status VARCHAR2(10);
begin
for i in ( select s.party_id, s.vendor_id, 'TEST' category_code from ap_suppliers s, hz_parties hp
where s.party_id=hp.party_id
and hp.category_code is null )
loop
SELECT aps.PARTY_ID, hzp.object_version_number
into l_party_id, l_object_version_number
FROM AP_SUPPLIERS aps, HZ_PARTIES hzp
WHERE
vendor_id =i.vendor_id
AND aps.party_id = hzp.party_id;
l_organization_rec.party_rec.party_id := l_party_id;
l_organization_rec.party_rec.category_code:=i.category_code;
HZ_PARTY_V2PUB.update_organization (
p_init_msg_list => fnd_api.g_true,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => x_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
dbms_output.put_line(l_return_status);
dbms_output.put_line(l_party_id ||i.category_code );
FOR I IN 1..l_msg_count
LOOP
l_msg_data := l_msg_data||SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);
dbms_output.put_line(l_msg_data);
END LOOP ;
end loop;
exception
when others then
raise;
end;
Wednesday, October 4, 2017
API to create relationship between Vendors
CREATE or replace PROCEDURE P_relationships(p_party_id IN NUMBER,
p_vendor_site_id IN NUMBER,
p_rem_party_id IN number,
p_rem_site_id IN number,
p_rem_vendor_site in number)
IS
l_rem_vend_site_id NUMBER;
l_rem_party_id NUMBER;
v_count number;
BEGIN
select count(*) into v_count from iby_ext_payee_relationships pr where pr.party_id=p_party_id
and pr.supplier_site_id=p_vendor_site_id;
if v_count=0 then
SELECT B.vendor_site_id,
A.party_id
INTO l_rem_vend_site_id, l_rem_party_id
FROM ap_suppliers A,
ap_supplier_sites_all B
WHERE A.vendor_id = B.vendor_id
and a.party_id =p_rem_party_id
and b.party_site_id= p_rem_site_id
and b.vendor_site_id=p_rem_vendor_site;
-- AND A.vendor_name = p_rem_supplier_name
-- AND B.vendor_site_code = p_rem_supplier_site_name;
INSERT INTO iby_ext_payee_relationships
(relationship_id,
party_id,
supplier_site_id,
remit_party_id,
remit_supplier_site_id,
from_date,
primary_flag,
active,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES (iby_ext_payee_relship_seq.NEXTVAL,
p_party_id,
p_vendor_site_id,
l_rem_party_id,
l_rem_vend_site_id,
to_date('28/09/2017','dd/mm/yyyy'),
'Y',
'Y',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
1 );
COMMIT;
dbms_output.put_line('inserted');
end if;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('exception1');
END P_relationships;
--Run following :
declare
l_rem_party_id number;
l_rem_site_id number;
l_rem_vend_site_id number;
begin
select
A.party_id,
b.party_site_id,
B.vendor_site_id
INTO l_rem_party_id, l_rem_site_id, l_rem_vend_site_id
FROM ap_suppliers A,
ap_supplier_sites_all B
WHERE A.vendor_id = B.vendor_id
and a.segment1='vendor_number'
and b.vendor_site_code='site_code';
for i in ( SELECT distinct
A.party_id,
B.vendor_site_id
FROM ap_suppliers A,
ap_supplier_sites_all B
WHERE A.vendor_id = B.vendor_id
and b.inactive_date is null)
loop
P_relationships(i.party_id, i.vendor_site_id, l_rem_party_id, l_rem_site_id,l_rem_vend_site_id);
end loop;
end;
p_vendor_site_id IN NUMBER,
p_rem_party_id IN number,
p_rem_site_id IN number,
p_rem_vendor_site in number)
IS
l_rem_vend_site_id NUMBER;
l_rem_party_id NUMBER;
v_count number;
BEGIN
select count(*) into v_count from iby_ext_payee_relationships pr where pr.party_id=p_party_id
and pr.supplier_site_id=p_vendor_site_id;
if v_count=0 then
SELECT B.vendor_site_id,
A.party_id
INTO l_rem_vend_site_id, l_rem_party_id
FROM ap_suppliers A,
ap_supplier_sites_all B
WHERE A.vendor_id = B.vendor_id
and a.party_id =p_rem_party_id
and b.party_site_id= p_rem_site_id
and b.vendor_site_id=p_rem_vendor_site;
-- AND A.vendor_name = p_rem_supplier_name
-- AND B.vendor_site_code = p_rem_supplier_site_name;
INSERT INTO iby_ext_payee_relationships
(relationship_id,
party_id,
supplier_site_id,
remit_party_id,
remit_supplier_site_id,
from_date,
primary_flag,
active,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES (iby_ext_payee_relship_seq.NEXTVAL,
p_party_id,
p_vendor_site_id,
l_rem_party_id,
l_rem_vend_site_id,
to_date('28/09/2017','dd/mm/yyyy'),
'Y',
'Y',
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
1 );
COMMIT;
dbms_output.put_line('inserted');
end if;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('exception1');
END P_relationships;
--Run following :
declare
l_rem_party_id number;
l_rem_site_id number;
l_rem_vend_site_id number;
begin
select
A.party_id,
b.party_site_id,
B.vendor_site_id
INTO l_rem_party_id, l_rem_site_id, l_rem_vend_site_id
FROM ap_suppliers A,
ap_supplier_sites_all B
WHERE A.vendor_id = B.vendor_id
and a.segment1='vendor_number'
and b.vendor_site_code='site_code';
for i in ( SELECT distinct
A.party_id,
B.vendor_site_id
FROM ap_suppliers A,
ap_supplier_sites_all B
WHERE A.vendor_id = B.vendor_id
and b.inactive_date is null)
loop
P_relationships(i.party_id, i.vendor_site_id, l_rem_party_id, l_rem_site_id,l_rem_vend_site_id);
end loop;
end;
API to delete Assets
declare
l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
l_return_status VARCHAR2(1);
l_mesg_count number := 0;
l_mesg_len number;
l_mesg varchar2(4000);
begin
dbms_output.enable(1000000);
FA_SRVR_MSG.Init_Server_Message;
for i in (select distinct book_type_code, asset_id from fa_books fb where fb.book_type_code='BOOK_NAME'
)
loop
-- asset header info
l_asset_hdr_rec.asset_id := i.asset_id;
l_asset_hdr_rec.book_type_code := i.book_type_code;
FA_DELETION_PUB.do_delete
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_mesg_count,
x_msg_data => l_mesg,
p_calling_fn => null,
px_asset_hdr_rec => l_asset_hdr_rec
);
l_mesg_count := fnd_msg_pub.count_msg;
if l_mesg_count > 0 then
l_mesg := chr(10) || substr(fnd_msg_pub.get
(fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
1, 250);
dbms_output.put_line(l_mesg);
for i in 1..(l_mesg_count - 1) loop
l_mesg :=
substr(fnd_msg_pub.get
(fnd_msg_pub.G_NEXT,
fnd_api.G_FALSE), 1, 250);
dbms_output.put_line(l_mesg);
end loop;
fnd_msg_pub.delete_msg();
end if;
if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
dbms_output.put_line('FAILURE');
else
dbms_output.put_line('SUCCESS');
/* dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
dbms_output.put_line('BOOK: ' || l_asset_hdr_rec.book_type_code);*/
end if;
end loop;
end;
l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
l_return_status VARCHAR2(1);
l_mesg_count number := 0;
l_mesg_len number;
l_mesg varchar2(4000);
begin
dbms_output.enable(1000000);
FA_SRVR_MSG.Init_Server_Message;
for i in (select distinct book_type_code, asset_id from fa_books fb where fb.book_type_code='BOOK_NAME'
)
loop
-- asset header info
l_asset_hdr_rec.asset_id := i.asset_id;
l_asset_hdr_rec.book_type_code := i.book_type_code;
FA_DELETION_PUB.do_delete
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_mesg_count,
x_msg_data => l_mesg,
p_calling_fn => null,
px_asset_hdr_rec => l_asset_hdr_rec
);
l_mesg_count := fnd_msg_pub.count_msg;
if l_mesg_count > 0 then
l_mesg := chr(10) || substr(fnd_msg_pub.get
(fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
1, 250);
dbms_output.put_line(l_mesg);
for i in 1..(l_mesg_count - 1) loop
l_mesg :=
substr(fnd_msg_pub.get
(fnd_msg_pub.G_NEXT,
fnd_api.G_FALSE), 1, 250);
dbms_output.put_line(l_mesg);
end loop;
fnd_msg_pub.delete_msg();
end if;
if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
dbms_output.put_line('FAILURE');
else
dbms_output.put_line('SUCCESS');
/* dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
dbms_output.put_line('BOOK: ' || l_asset_hdr_rec.book_type_code);*/
end if;
end loop;
end;
Thursday, August 17, 2017
API to update Supplier/Vendor Sites
declare
p_api_version NUMBER;
p_init_msg_list VARCHAR2(2000);
p_commit VARCHAR2(2000);
p_validation_level NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
lr_vendor_site_rec apps.ap_vendor_pub_pkg .r_vendor_site_rec_type;
lr_existing_vendor_site_rec ap_supplier_sites_all%ROWTYPE;
p_vendor_site_id NUMBER;
p_calling_prog VARCHAR2(2000);
cursor c_supplier_sites is
SELECT assa.org_id,
assa.vendor_site_id,
assa.vendor_site_code,
s.azf_site,
assa.vendor_id
FROM ap_supplier_sites_all assa,
xxbak_site_mapping_tmp s
where assa.vendor_id=1253
and assa.vendor_site_code=s.ora_site
-- in (select s.vendor_site_id_ora from XXAZF_INT_SUP s )
;
BEGIN
-- Initialize apps session
fnd_global.apps_initialize
(
user_id => 2594 --l_user_id
, resp_id => 52587 --l_resp_id
, resp_appl_id => 200 --l_resp_app_id
);
mo_global.init('SQLAP');
-- MO_GLOBAL.init('po');
mo_global.set_policy_context('s',5259);
-- Assign Basic Values
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_true;
p_validation_level := fnd_api.g_valid_level_full;
-- p_vendor_site_id := 2040; -- to be end dated
p_calling_prog := 'XXCUSTOM';
for c in c_supplier_sites
loop
-- Assign Vendor Site Details
lr_vendor_site_rec.vendor_site_id := c.vendor_site_id;
lr_vendor_site_rec.last_update_date := SYSDATE;
--lr_vendor_site_rec.last_updated_by := -1;
lr_vendor_site_rec.vendor_id := c.vendor_id;
lr_vendor_site_rec.org_id := c.org_id;
lr_vendor_site_rec.VENDOR_SITE_CODE :=c.azf_site;
--lr_vendor_site_rec.purchasing_site_flag :='Y';
--lr_vendor_site_rec.pay_site_flag :='Y';
p_vendor_site_id := c.vendor_site_id;
AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => lr_vendor_site_rec,
p_vendor_site_id => p_vendor_site_id,
p_calling_prog => p_calling_prog);
end loop;
DBMS_OUTPUT.put_line('X_RETURN_STATUS = ' || x_return_status);
--DBMS_OUTPUT.put_line('X_MSG_COUNT = ' || x_msg_count);
-- DBMS_OUTPUT.put_line('X_MSG_DATA = ' || x_msg_data);
END;
p_api_version NUMBER;
p_init_msg_list VARCHAR2(2000);
p_commit VARCHAR2(2000);
p_validation_level NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
lr_vendor_site_rec apps.ap_vendor_pub_pkg .r_vendor_site_rec_type;
lr_existing_vendor_site_rec ap_supplier_sites_all%ROWTYPE;
p_vendor_site_id NUMBER;
p_calling_prog VARCHAR2(2000);
cursor c_supplier_sites is
SELECT assa.org_id,
assa.vendor_site_id,
assa.vendor_site_code,
s.azf_site,
assa.vendor_id
FROM ap_supplier_sites_all assa,
xxbak_site_mapping_tmp s
where assa.vendor_id=1253
and assa.vendor_site_code=s.ora_site
-- in (select s.vendor_site_id_ora from XXAZF_INT_SUP s )
;
BEGIN
-- Initialize apps session
fnd_global.apps_initialize
(
user_id => 2594 --l_user_id
, resp_id => 52587 --l_resp_id
, resp_appl_id => 200 --l_resp_app_id
);
mo_global.init('SQLAP');
-- MO_GLOBAL.init('po');
mo_global.set_policy_context('s',5259);
-- Assign Basic Values
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_true;
p_validation_level := fnd_api.g_valid_level_full;
-- p_vendor_site_id := 2040; -- to be end dated
p_calling_prog := 'XXCUSTOM';
for c in c_supplier_sites
loop
-- Assign Vendor Site Details
lr_vendor_site_rec.vendor_site_id := c.vendor_site_id;
lr_vendor_site_rec.last_update_date := SYSDATE;
--lr_vendor_site_rec.last_updated_by := -1;
lr_vendor_site_rec.vendor_id := c.vendor_id;
lr_vendor_site_rec.org_id := c.org_id;
lr_vendor_site_rec.VENDOR_SITE_CODE :=c.azf_site;
--lr_vendor_site_rec.purchasing_site_flag :='Y';
--lr_vendor_site_rec.pay_site_flag :='Y';
p_vendor_site_id := c.vendor_site_id;
AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => lr_vendor_site_rec,
p_vendor_site_id => p_vendor_site_id,
p_calling_prog => p_calling_prog);
end loop;
DBMS_OUTPUT.put_line('X_RETURN_STATUS = ' || x_return_status);
--DBMS_OUTPUT.put_line('X_MSG_COUNT = ' || x_msg_count);
-- DBMS_OUTPUT.put_line('X_MSG_DATA = ' || x_msg_data);
END;
Monday, July 17, 2017
Please correct the receivable account assignment while running Autoinvoice Master Program
Cause:
Receivable Account and Tax Account are missing in Transaction Types.
Solution.
Receivable Manager / Setup/ Transactions/ Transaction Types
Fill Receivable Account and Tax Account fields.
Receivable Account and Tax Account are missing in Transaction Types.
Solution.
Receivable Manager / Setup/ Transactions/ Transaction Types
Fill Receivable Account and Tax Account fields.
Monday, April 24, 2017
API to Create Memo Line
declare
v_row_id varchar2(250);
v_memo_line_id number;
V_org_id number:=93;
V_SET_OF_BOOKS_ID NUMBER:=2022;
V_LE_TYPE VARCHAR2(200):='LINE';
V_START_DATE DATE:= sysdate;
V_GL_ID_REV NUMBER :=4095;
V_TAX_CODE VARCHAR2(200):='VAT18_EXCLUDE';
V_UOM_CODE VARCHAR2(200):='EA';
V_UNIT_STD_PRICE NUMBER:=1;
V_VOICG_RULE_ID NUMBER;
V_ACCOUNTG_RULE_ID NUMBER;
V_END_DATE DATE;
V_ATTRIBUTE_CATEGORY VARCHAR2(200);
V_ATTRIBUTE1 VARCHAR2(200);
V_ATTRIBUTE2 VARCHAR2(200);
V_ATTRIBUTE3 VARCHAR2(200);
V_ATTRIBUTE4 VARCHAR2(200);
V_ATTRIBUTE5 VARCHAR2(200);
V_ATTRIBUTE6 VARCHAR2(200);
V_ATTRIBUTE7 VARCHAR2(200);
V_ATTRIBUTE8 VARCHAR2(200);
V_ATTRIBUTE9 VARCHAR2(200);
V_ATTRIBUTE10 VARCHAR2(200);
V_ATTRIBUTE11 VARCHAR2(200);
V_ATTRIBUTE12 VARCHAR2(200);
V_ATTRIBUTE13 VARCHAR2(200);
V_ATTRIBUTE14 VARCHAR2(200);
V_ATTRIBUTE15 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(200);
V_GLOBAL_ATTRIBUTE1 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE2 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE3 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE4 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE5 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE6 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE7 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE8 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE9 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE10 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE11 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE12 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE13 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE14 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE15 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE16 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE17 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE18 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE19 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE20 VARCHAR2(200);
V_NAME VARCHAR2(200):='tetetete';
V_DESCRIPTION VARCHAR2(200):='tetete';
V_CREATION_DATE DATE:=sysdate;
V_CREATED_BY NUMBER;
V_LAST_UPDATE_DATE DATE;
V_LAST_UPDATED_BY NUMBER;
V_LAST_UPDATE_LOG NUMBER;
V_TAX_PRODUCT_CATEGORY VARCHAR2(200);
cursor c_memo_lines is
select ml.name,
ml.description,
ml.line_type,
ml.start_date,
ml.gl_id_rev,
ml.revenue_account,
ml.tax_code,
ml.uom_code,
ml.unit_std_price,
ml.memo_line_id,
ml.org_id,
ml.set_of_books_id
from temp_memo_line ml
;
begin
for c in c_memo_lines
loop
select ar_memo_lines_s.nextval into v_memo_line_id from dual;
AR_MEMO_LINES_PKG.LOAD_ROW(
X_org_id =>c.org_id,
X_MEMO_LINE_ID =>v_memo_line_id,
X_SET_OF_BOOKS_ID =>c.set_of_books_id,
X_LINE_TYPE =>c.line_type,
X_START_DATE =>c.start_date,
X_GL_ID_REV =>c.gl_id_rev,
X_TAX_CODE =>c.tax_code,
X_UOM_CODE =>c.uom_code,
X_UNIT_STD_PRICE => c.unit_std_price,
X_INVOICING_RULE_ID =>V_VOICG_RULE_ID,
X_ACCOUNTING_RULE_ID =>V_ACCOUNTG_RULE_ID,
X_END_DATE =>V_END_DATE,
X_ATTRIBUTE_CATEGORY =>V_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>V_ATTRIBUTE1,
X_ATTRIBUTE2 =>V_ATTRIBUTE2,
X_ATTRIBUTE3 =>V_ATTRIBUTE3,
X_ATTRIBUTE4 =>V_ATTRIBUTE4,
X_ATTRIBUTE5 =>V_ATTRIBUTE5,
X_ATTRIBUTE6 =>V_ATTRIBUTE6,
X_ATTRIBUTE7 =>V_ATTRIBUTE7,
X_ATTRIBUTE8 =>V_ATTRIBUTE8,
X_ATTRIBUTE9 =>V_ATTRIBUTE9,
X_ATTRIBUTE10 =>V_ATTRIBUTE10,
X_ATTRIBUTE11 =>V_ATTRIBUTE11,
X_ATTRIBUTE12 =>V_ATTRIBUTE12,
X_ATTRIBUTE13 =>V_ATTRIBUTE13,
X_ATTRIBUTE14 =>V_ATTRIBUTE14,
X_ATTRIBUTE15 =>V_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE_CATEGORY =>V_GLOBAL_ATTRIBUTE_CATEGORY,
X_GLOBAL_ATTRIBUTE1 =>V_GLOBAL_ATTRIBUTE1,
X_GLOBAL_ATTRIBUTE2 =>V_GLOBAL_ATTRIBUTE2,
X_GLOBAL_ATTRIBUTE3 =>V_GLOBAL_ATTRIBUTE3,
X_GLOBAL_ATTRIBUTE4 =>V_GLOBAL_ATTRIBUTE4,
X_GLOBAL_ATTRIBUTE5 =>V_GLOBAL_ATTRIBUTE5,
X_GLOBAL_ATTRIBUTE6 =>V_GLOBAL_ATTRIBUTE6,
X_GLOBAL_ATTRIBUTE7 =>V_GLOBAL_ATTRIBUTE7,
X_GLOBAL_ATTRIBUTE8 =>V_GLOBAL_ATTRIBUTE8,
X_GLOBAL_ATTRIBUTE9 =>V_GLOBAL_ATTRIBUTE9,
X_GLOBAL_ATTRIBUTE10 =>V_GLOBAL_ATTRIBUTE10,
X_GLOBAL_ATTRIBUTE11 =>V_GLOBAL_ATTRIBUTE11,
X_GLOBAL_ATTRIBUTE12 =>V_GLOBAL_ATTRIBUTE12,
X_GLOBAL_ATTRIBUTE13 =>V_GLOBAL_ATTRIBUTE13,
X_GLOBAL_ATTRIBUTE14 =>V_GLOBAL_ATTRIBUTE14,
X_GLOBAL_ATTRIBUTE15 =>V_GLOBAL_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE16 =>V_GLOBAL_ATTRIBUTE16,
X_GLOBAL_ATTRIBUTE17 =>V_GLOBAL_ATTRIBUTE17,
X_GLOBAL_ATTRIBUTE18 =>V_GLOBAL_ATTRIBUTE18,
X_GLOBAL_ATTRIBUTE19 =>V_GLOBAL_ATTRIBUTE19,
X_GLOBAL_ATTRIBUTE20 =>V_GLOBAL_ATTRIBUTE20,
X_NAME =>c.name,
X_DESCRIPTION =>c.description,
X_OWNER => 'SEED',
/*X_CREATION_DATE =>V_CREATION_DATE,
X_CREATED_BY =>V_CREATED_BY,
X_LAST_UPDATE_DATE =>V_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY =>V_LAST_UPDATED_BY,
X_LAST_UPDATE_LOG =>V_LAST_UPDATE_LOG,*/
X_TAX_PRODUCT_CATEGORY =>V_TAX_PRODUCT_CATEGORY
);
commit;
end loop;
end;
v_row_id varchar2(250);
v_memo_line_id number;
V_org_id number:=93;
V_SET_OF_BOOKS_ID NUMBER:=2022;
V_LE_TYPE VARCHAR2(200):='LINE';
V_START_DATE DATE:= sysdate;
V_GL_ID_REV NUMBER :=4095;
V_TAX_CODE VARCHAR2(200):='VAT18_EXCLUDE';
V_UOM_CODE VARCHAR2(200):='EA';
V_UNIT_STD_PRICE NUMBER:=1;
V_VOICG_RULE_ID NUMBER;
V_ACCOUNTG_RULE_ID NUMBER;
V_END_DATE DATE;
V_ATTRIBUTE_CATEGORY VARCHAR2(200);
V_ATTRIBUTE1 VARCHAR2(200);
V_ATTRIBUTE2 VARCHAR2(200);
V_ATTRIBUTE3 VARCHAR2(200);
V_ATTRIBUTE4 VARCHAR2(200);
V_ATTRIBUTE5 VARCHAR2(200);
V_ATTRIBUTE6 VARCHAR2(200);
V_ATTRIBUTE7 VARCHAR2(200);
V_ATTRIBUTE8 VARCHAR2(200);
V_ATTRIBUTE9 VARCHAR2(200);
V_ATTRIBUTE10 VARCHAR2(200);
V_ATTRIBUTE11 VARCHAR2(200);
V_ATTRIBUTE12 VARCHAR2(200);
V_ATTRIBUTE13 VARCHAR2(200);
V_ATTRIBUTE14 VARCHAR2(200);
V_ATTRIBUTE15 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(200);
V_GLOBAL_ATTRIBUTE1 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE2 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE3 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE4 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE5 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE6 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE7 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE8 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE9 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE10 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE11 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE12 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE13 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE14 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE15 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE16 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE17 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE18 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE19 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE20 VARCHAR2(200);
V_NAME VARCHAR2(200):='tetetete';
V_DESCRIPTION VARCHAR2(200):='tetete';
V_CREATION_DATE DATE:=sysdate;
V_CREATED_BY NUMBER;
V_LAST_UPDATE_DATE DATE;
V_LAST_UPDATED_BY NUMBER;
V_LAST_UPDATE_LOG NUMBER;
V_TAX_PRODUCT_CATEGORY VARCHAR2(200);
cursor c_memo_lines is
select ml.name,
ml.description,
ml.line_type,
ml.start_date,
ml.gl_id_rev,
ml.revenue_account,
ml.tax_code,
ml.uom_code,
ml.unit_std_price,
ml.memo_line_id,
ml.org_id,
ml.set_of_books_id
from temp_memo_line ml
;
begin
for c in c_memo_lines
loop
select ar_memo_lines_s.nextval into v_memo_line_id from dual;
AR_MEMO_LINES_PKG.LOAD_ROW(
X_org_id =>c.org_id,
X_MEMO_LINE_ID =>v_memo_line_id,
X_SET_OF_BOOKS_ID =>c.set_of_books_id,
X_LINE_TYPE =>c.line_type,
X_START_DATE =>c.start_date,
X_GL_ID_REV =>c.gl_id_rev,
X_TAX_CODE =>c.tax_code,
X_UOM_CODE =>c.uom_code,
X_UNIT_STD_PRICE => c.unit_std_price,
X_INVOICING_RULE_ID =>V_VOICG_RULE_ID,
X_ACCOUNTING_RULE_ID =>V_ACCOUNTG_RULE_ID,
X_END_DATE =>V_END_DATE,
X_ATTRIBUTE_CATEGORY =>V_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>V_ATTRIBUTE1,
X_ATTRIBUTE2 =>V_ATTRIBUTE2,
X_ATTRIBUTE3 =>V_ATTRIBUTE3,
X_ATTRIBUTE4 =>V_ATTRIBUTE4,
X_ATTRIBUTE5 =>V_ATTRIBUTE5,
X_ATTRIBUTE6 =>V_ATTRIBUTE6,
X_ATTRIBUTE7 =>V_ATTRIBUTE7,
X_ATTRIBUTE8 =>V_ATTRIBUTE8,
X_ATTRIBUTE9 =>V_ATTRIBUTE9,
X_ATTRIBUTE10 =>V_ATTRIBUTE10,
X_ATTRIBUTE11 =>V_ATTRIBUTE11,
X_ATTRIBUTE12 =>V_ATTRIBUTE12,
X_ATTRIBUTE13 =>V_ATTRIBUTE13,
X_ATTRIBUTE14 =>V_ATTRIBUTE14,
X_ATTRIBUTE15 =>V_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE_CATEGORY =>V_GLOBAL_ATTRIBUTE_CATEGORY,
X_GLOBAL_ATTRIBUTE1 =>V_GLOBAL_ATTRIBUTE1,
X_GLOBAL_ATTRIBUTE2 =>V_GLOBAL_ATTRIBUTE2,
X_GLOBAL_ATTRIBUTE3 =>V_GLOBAL_ATTRIBUTE3,
X_GLOBAL_ATTRIBUTE4 =>V_GLOBAL_ATTRIBUTE4,
X_GLOBAL_ATTRIBUTE5 =>V_GLOBAL_ATTRIBUTE5,
X_GLOBAL_ATTRIBUTE6 =>V_GLOBAL_ATTRIBUTE6,
X_GLOBAL_ATTRIBUTE7 =>V_GLOBAL_ATTRIBUTE7,
X_GLOBAL_ATTRIBUTE8 =>V_GLOBAL_ATTRIBUTE8,
X_GLOBAL_ATTRIBUTE9 =>V_GLOBAL_ATTRIBUTE9,
X_GLOBAL_ATTRIBUTE10 =>V_GLOBAL_ATTRIBUTE10,
X_GLOBAL_ATTRIBUTE11 =>V_GLOBAL_ATTRIBUTE11,
X_GLOBAL_ATTRIBUTE12 =>V_GLOBAL_ATTRIBUTE12,
X_GLOBAL_ATTRIBUTE13 =>V_GLOBAL_ATTRIBUTE13,
X_GLOBAL_ATTRIBUTE14 =>V_GLOBAL_ATTRIBUTE14,
X_GLOBAL_ATTRIBUTE15 =>V_GLOBAL_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE16 =>V_GLOBAL_ATTRIBUTE16,
X_GLOBAL_ATTRIBUTE17 =>V_GLOBAL_ATTRIBUTE17,
X_GLOBAL_ATTRIBUTE18 =>V_GLOBAL_ATTRIBUTE18,
X_GLOBAL_ATTRIBUTE19 =>V_GLOBAL_ATTRIBUTE19,
X_GLOBAL_ATTRIBUTE20 =>V_GLOBAL_ATTRIBUTE20,
X_NAME =>c.name,
X_DESCRIPTION =>c.description,
X_OWNER => 'SEED',
/*X_CREATION_DATE =>V_CREATION_DATE,
X_CREATED_BY =>V_CREATED_BY,
X_LAST_UPDATE_DATE =>V_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY =>V_LAST_UPDATED_BY,
X_LAST_UPDATE_LOG =>V_LAST_UPDATE_LOG,*/
X_TAX_PRODUCT_CATEGORY =>V_TAX_PRODUCT_CATEGORY
);
commit;
end loop;
end;
Tuesday, April 18, 2017
API to assign bank account number to suppliers
declare
p_api_version number;
p_init_msg_list varchar2(200);
p_commit varchar2(200);
x_return_status varchar2(200);
x_msg_count number;
x_msg_data varchar2(200);
p_payee apps.iby_disbursement_setup_pub.payeecontext_rec_type;
p_assignment_attribs apps.iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
lr_ext_bank_acct_dtl iby_ext_bank_accounts%rowtype;
x_assign_id number;
x_response apps.iby_fndcpt_common_pub.result_rec_type;
lv_vendor_site_code varchar2(100);
lv_vendor_id number;
lv_bank_acct_name varchar2(100);
lv_supp_site_id varchar2(100);
lv_supp_party_site_id varchar2(100);
lv_acct_owner_party_id varchar2(100);
lv_org_id varchar2(100);
l_msg varchar2(200);
cursor c_suppliers is
select
s.vendor_id,
s.vendor_site_code,
s.street,
s.address_line2,
s.address_line3,
s.city,
s.country,
s.bank_name,
s.branch_name,
s.bank_account_country,
s.bank_account_num,
s.currency_code,
s.iban,
s.bank_account_type,
s.payment_factor_flag,
s.foreign_payment_use_flag,
s.bank_account_name,
s.bank_account_num_electronic,
s.liability_code_id,
s.prepay_code_combination_id
from tem_suppliers s;
begin
-- initialize apps session
fnd_global.apps_initialize(user_id=>6837,
resp_id=>50558,
resp_appl_id=>200);
mo_global.init('S', null);
--fnd_client_info.set_org_context(101);
-- input values
for c in c_suppliers loop
lv_vendor_site_code := c.vendor_site_code;
lv_vendor_id := c.vendor_id;
lv_bank_acct_name := c.bank_account_name;
-- assign api parameters
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_true;
-- get ext bank account details
begin
select *
into lr_ext_bank_acct_dtl
from iby_ext_bank_accounts
where bank_account_name = lv_bank_acct_name;
exception
when others then
dbms_output.put_line('Unable to derive the external bank details:' ||
sqlerrm);
end;
-- get supplier details
begin
select assa.vendor_site_id,
assa.party_site_id,
aps.party_id,
assa.org_id
into lv_supp_site_id,
lv_supp_party_site_id,
lv_acct_owner_party_id,
lv_org_id
from ap_suppliers aps, ap_supplier_sites_all assa
where aps.vendor_id = assa.vendor_id
and aps.vendor_id = c.vendor_id
and assa.vendor_site_code = lv_vendor_site_code;
exception
when others then
dbms_output.put_line('Error- Get supp_site_id and supp_party_site_id' ||
sqlcode || sqlerrm);
end;
-- assign payee values
p_payee.supplier_site_id := lv_supp_site_id;
p_payee.party_id := lv_acct_owner_party_id;
p_payee.party_site_id := lv_supp_party_site_id;
p_payee.payment_function := 'PAYABLES_DISB';
p_payee.org_id := lv_org_id;
p_payee.org_type := 'OPERATING_UNIT';
-- assignment values
p_assignment_attribs.instrument.instrument_type := 'BANKACCOUNT';
p_assignment_attribs.instrument.instrument_id := lr_ext_bank_acct_dtl.ext_bank_account_id;
-- external bank account id
p_assignment_attribs.priority := 1;
p_assignment_attribs.start_date := sysdate;
iby_disbursement_setup_pub.set_payee_instr_assignment
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payee => p_payee,
p_assignment_attribs => p_assignment_attribs,
x_assign_id => x_assign_id,
x_response => x_response
);
dbms_output.put_line('X_RETURN_STATUS = ' || x_return_status);
dbms_output.put_line('X_MSG_COUNT = ' || x_msg_count);
dbms_output.put_line('X_MSG_DATA = ' || x_msg_data);
dbms_output.put_line('X_ASSIGN_ID = ' || x_assign_id);
dbms_output.put_line('X_RESPONSE.Result_Code = ' ||
x_response.result_code);
dbms_output.put_line('X_RESPONSE.Result_Category = ' ||
x_response.result_category);
dbms_output.put_line('X_RESPONSE.Result_Message = ' ||
x_response.result_message);
commit;
if (x_return_status <> fnd_api.g_ret_sts_success) then
for i in 1 .. fnd_msg_pub.count_msg loop
l_msg := fnd_msg_pub.get(p_msg_index => i,
p_encoded => fnd_api.g_false);
dbms_output.put_line('The API call failed with error ' || l_msg);
end loop;
else
dbms_output.put_line('The API call ended with SUCESSS status');
end if;
end loop;
end;
p_api_version number;
p_init_msg_list varchar2(200);
p_commit varchar2(200);
x_return_status varchar2(200);
x_msg_count number;
x_msg_data varchar2(200);
p_payee apps.iby_disbursement_setup_pub.payeecontext_rec_type;
p_assignment_attribs apps.iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
lr_ext_bank_acct_dtl iby_ext_bank_accounts%rowtype;
x_assign_id number;
x_response apps.iby_fndcpt_common_pub.result_rec_type;
lv_vendor_site_code varchar2(100);
lv_vendor_id number;
lv_bank_acct_name varchar2(100);
lv_supp_site_id varchar2(100);
lv_supp_party_site_id varchar2(100);
lv_acct_owner_party_id varchar2(100);
lv_org_id varchar2(100);
l_msg varchar2(200);
cursor c_suppliers is
select
s.vendor_id,
s.vendor_site_code,
s.street,
s.address_line2,
s.address_line3,
s.city,
s.country,
s.bank_name,
s.branch_name,
s.bank_account_country,
s.bank_account_num,
s.currency_code,
s.iban,
s.bank_account_type,
s.payment_factor_flag,
s.foreign_payment_use_flag,
s.bank_account_name,
s.bank_account_num_electronic,
s.liability_code_id,
s.prepay_code_combination_id
from tem_suppliers s;
begin
-- initialize apps session
fnd_global.apps_initialize(user_id=>6837,
resp_id=>50558,
resp_appl_id=>200);
mo_global.init('S', null);
--fnd_client_info.set_org_context(101);
-- input values
for c in c_suppliers loop
lv_vendor_site_code := c.vendor_site_code;
lv_vendor_id := c.vendor_id;
lv_bank_acct_name := c.bank_account_name;
-- assign api parameters
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_true;
-- get ext bank account details
begin
select *
into lr_ext_bank_acct_dtl
from iby_ext_bank_accounts
where bank_account_name = lv_bank_acct_name;
exception
when others then
dbms_output.put_line('Unable to derive the external bank details:' ||
sqlerrm);
end;
-- get supplier details
begin
select assa.vendor_site_id,
assa.party_site_id,
aps.party_id,
assa.org_id
into lv_supp_site_id,
lv_supp_party_site_id,
lv_acct_owner_party_id,
lv_org_id
from ap_suppliers aps, ap_supplier_sites_all assa
where aps.vendor_id = assa.vendor_id
and aps.vendor_id = c.vendor_id
and assa.vendor_site_code = lv_vendor_site_code;
exception
when others then
dbms_output.put_line('Error- Get supp_site_id and supp_party_site_id' ||
sqlcode || sqlerrm);
end;
-- assign payee values
p_payee.supplier_site_id := lv_supp_site_id;
p_payee.party_id := lv_acct_owner_party_id;
p_payee.party_site_id := lv_supp_party_site_id;
p_payee.payment_function := 'PAYABLES_DISB';
p_payee.org_id := lv_org_id;
p_payee.org_type := 'OPERATING_UNIT';
-- assignment values
p_assignment_attribs.instrument.instrument_type := 'BANKACCOUNT';
p_assignment_attribs.instrument.instrument_id := lr_ext_bank_acct_dtl.ext_bank_account_id;
-- external bank account id
p_assignment_attribs.priority := 1;
p_assignment_attribs.start_date := sysdate;
iby_disbursement_setup_pub.set_payee_instr_assignment
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payee => p_payee,
p_assignment_attribs => p_assignment_attribs,
x_assign_id => x_assign_id,
x_response => x_response
);
dbms_output.put_line('X_RETURN_STATUS = ' || x_return_status);
dbms_output.put_line('X_MSG_COUNT = ' || x_msg_count);
dbms_output.put_line('X_MSG_DATA = ' || x_msg_data);
dbms_output.put_line('X_ASSIGN_ID = ' || x_assign_id);
dbms_output.put_line('X_RESPONSE.Result_Code = ' ||
x_response.result_code);
dbms_output.put_line('X_RESPONSE.Result_Category = ' ||
x_response.result_category);
dbms_output.put_line('X_RESPONSE.Result_Message = ' ||
x_response.result_message);
commit;
if (x_return_status <> fnd_api.g_ret_sts_success) then
for i in 1 .. fnd_msg_pub.count_msg loop
l_msg := fnd_msg_pub.get(p_msg_index => i,
p_encoded => fnd_api.g_false);
dbms_output.put_line('The API call failed with error ' || l_msg);
end loop;
else
dbms_output.put_line('The API call ended with SUCESSS status');
end if;
end loop;
end;
Monday, April 17, 2017
API to Create Item Categories
declare
l_category_rec inv_item_category_pub .category_rec_type;
l_return_status varchar2(80);
l_error_code number;
l_msg_count number;
l_msg_data varchar2(80);
l_out_category_id number;
begin
l_category_rec.segment1 := 'London';
select f.id_flex_num
into l_category_rec.structure_id
from fnd_id_flex_structures f
where f.id_flex_structure_code = 'CITY';
l_category_rec.description := 'London is located in Uk';
inv_item_category_pub.create_category
(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_true,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_rec => l_category_rec,
x_category_id => l_out_category_id
);
if l_return_status = fnd_api.g_ret_sts_success then
commit;
dbms_output.put_line ('Creation of Item Category is Successful : '||l_out_category_id);
else
dbms_output.put_line ('Creation of Item Category Failed with the error :'||l_error_code);
rollback;
end if;
end ;
l_category_rec inv_item_category_pub .category_rec_type;
l_return_status varchar2(80);
l_error_code number;
l_msg_count number;
l_msg_data varchar2(80);
l_out_category_id number;
begin
l_category_rec.segment1 := 'London';
select f.id_flex_num
into l_category_rec.structure_id
from fnd_id_flex_structures f
where f.id_flex_structure_code = 'CITY';
l_category_rec.description := 'London is located in Uk';
inv_item_category_pub.create_category
(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_true,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_rec => l_category_rec,
x_category_id => l_out_category_id
);
if l_return_status = fnd_api.g_ret_sts_success then
commit;
dbms_output.put_line ('Creation of Item Category is Successful : '||l_out_category_id);
else
dbms_output.put_line ('Creation of Item Category Failed with the error :'||l_error_code);
rollback;
end if;
end ;
API to create AR customer
declare
l_num_user_id NUMBER;
l_num_appl_id NUMBER;
l_num_resp_id NUMBER;
cust_account_rec_type hz_cust_account_v2pub.cust_account_rec_type;
l_num_obj_ver_num NUMBER;
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (500);
l_num_profile_id NUMBER;
cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
l_organization_rec hz_party_v2pub.organization_rec_type;
l_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
l_num_cust_id NUMBER;
l_chr_acct_num VARCHAR2 (500);
l_num_party_id NUMBER;
l_chr_party_number VARCHAR2 (500);
l_cust_acct_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
l_chr_sit_return_status VARCHAR2 (500);
l_num_sit_msg_count NUMBER;
l_chr_sit_msg_data VARCHAR2 (500);
l_chr_situ_return_status VARCHAR2 (500);
l_num_situ_msg_count NUMBER;
l_chr_situ_msg_data VARCHAR2 (500);
l_num_site_use_id NUMBER;
CURSOR update_base_tables_cur
IS
select distinct ps.party_site_name, a.party_number, ps.party_site_number, a.party_id,
'CUST-' ||a.party_id||'-'||ass.org_id as customer_ref,
'ADD-'||ps.party_site_id||'-'||ass.org_id as address_ref,
ps.party_site_id,
ass.address_line1,
ass.city, ass.state, ass.zip, ass.country,
'N' primary_site_use_flag,
a.orig_system_reference as party_ref,
a.party_name as customer_name, ass.org_id
from apps.ap_suppliers ap,
apps.ap_supplier_sites_all ass,
apps.hz_parties a,
apps.hz_party_sites ps
where ap.vendor_id = ass.vendor_id
and a.party_id = ap.party_id
and ps.party_id = ap.party_id
and ass.party_site_id = ps.party_site_id
and a.party_name = ap.vendor_name
and not exists (select * from hz_cust_accounts ac
where ac.party_id = a.party_id);
begin
fnd_global.apps_initialize( 6837, 50546, 222); -- input from 1st sql
dbms_output.put_line('***************************');
for update_base_tables_rec in update_base_tables_cur
loop
null;
cust_account_rec_type.cust_account_id := fnd_api.g_miss_num;
cust_account_rec_type.account_name := update_base_tables_rec.customer_name;
cust_account_rec_type.customer_type := 'R';
--l_organization_rec.party_rec_type.category_code := 'ROAMING';
l_organization_rec.party_rec.party_id := update_base_tables_rec.party_id;
l_organization_rec.party_rec.party_number := update_base_tables_rec.party_number;
l_organization_rec.organization_name := update_base_tables_rec.customer_name;
cust_account_rec_type.orig_system_reference := update_base_tables_rec.customer_ref;
l_customer_profile_rec.party_id := update_base_tables_rec.party_id;
l_customer_profile_rec.profile_class_id := 0 ; -- use default profile with id=0
l_customer_profile_rec.created_by_module := 'HZ_CPUI';
cust_account_rec_type.created_by_module := 'HZ_CPUI';
hz_cust_account_v2pub.create_cust_account
(p_init_msg_list => fnd_api.g_false,
p_cust_account_rec => cust_account_rec_type,
p_organization_rec => l_organization_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile_amt => fnd_api.g_true,
x_cust_account_id => l_num_cust_id,
x_account_number => l_chr_acct_num,
x_party_id => l_num_party_id,
x_party_number => l_chr_party_number,
x_profile_id => l_num_profile_id,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data
);
dbms_output.put_line('x_return_status: '||l_chr_return_status);
dbms_output.put_line('x_cust_account_id: '||l_num_cust_id);
dbms_output.put_line('x_account_number: '||l_chr_acct_num);
dbms_output.put_line('x_party_id: '||l_num_party_id);
dbms_output.put_line(l_chr_return_status ||' ' ||l_chr_msg_data );
if l_chr_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
-- dbms_output.put_line (j);
l_chr_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
-- dbms_output.put_line ('Message(' || j || '):= ' || l_chr_msg_data);
end loop;
end if;
begin
select cust_account_id
into l_cust_acct_site_rec.cust_account_id
from hz_cust_accounts
where orig_system_reference = update_base_tables_rec.customer_ref;
dbms_output.put_line(2 );
exception
when others
then
l_cust_acct_site_rec.cust_account_id := fnd_api.g_miss_num;
end;
l_cust_acct_site_rec.party_site_id := update_base_tables_rec.party_site_id;
l_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
l_cust_acct_site_rec.orig_system_reference := update_base_tables_rec.address_ref;
l_cust_acct_site_rec.org_id := update_base_tables_rec.org_id;
mo_global.init ('ONT');
mo_global.set_policy_context (p_access_mode => 'S',
p_org_id => update_base_tables_rec.org_id
);
dbms_output.put_line(3 );
hz_cust_account_site_v2pub.create_cust_acct_site
(p_init_msg_list => 'T',
p_cust_acct_site_rec => l_cust_acct_site_rec,
x_cust_acct_site_id => l_num_obj_ver_num,
x_return_status => l_chr_sit_return_status,
x_msg_count => l_num_sit_msg_count,
x_msg_data => l_chr_sit_msg_data
);
dbms_output.put_line('x_cust_acct_site_id: '||l_num_obj_ver_num);
dbms_output.put_line('x_return_status: '||l_chr_sit_return_status);
dbms_output.put_line(4||' '|| l_chr_sit_return_status );
if l_chr_sit_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
l_chr_sit_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
end loop;
end if;
begin
select cust_acct_site_id
into l_cust_acct_site_use_rec.cust_acct_site_id
from hz_cust_acct_sites_all
where orig_system_reference = update_base_tables_rec.address_ref;
exception
when others
then
l_cust_acct_site_use_rec.cust_acct_site_id := fnd_api.g_miss_num;
end;
l_cust_acct_site_use_rec.org_id := update_base_tables_rec.org_id;
l_cust_acct_site_use_rec.site_use_code := 'BILL_TO';
l_cust_acct_site_use_rec.status := 'A';
l_cust_acct_site_use_rec.location := update_base_tables_rec.party_site_name;
l_cust_acct_site_use_rec.primary_flag := 'Y';
l_cust_acct_site_use_rec.orig_system_reference :=
update_base_tables_rec.address_ref;
l_cust_acct_site_use_rec.created_by_module := 'HZ_CPUI';
mo_global.set_policy_context (p_access_mode => 'S',
p_org_id => update_base_tables_rec.org_id
);
hz_cust_account_site_v2pub.create_cust_site_use
(p_init_msg_list => 'T',
p_cust_site_use_rec => l_cust_acct_site_use_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile => fnd_api.g_true,
p_create_profile_amt => fnd_api.g_true,
x_site_use_id => l_num_site_use_id,
x_return_status => l_chr_situ_return_status,
x_msg_count => l_num_situ_msg_count,
x_msg_data => l_chr_situ_msg_data
);
dbms_output.put_line('x_site_use_id: '||l_num_site_use_id);
dbms_output.put_line('x_return_status: '||l_chr_situ_return_status);
if l_chr_situ_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
l_chr_situ_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
end loop;
end if;
end loop;
end;
l_num_user_id NUMBER;
l_num_appl_id NUMBER;
l_num_resp_id NUMBER;
cust_account_rec_type hz_cust_account_v2pub.cust_account_rec_type;
l_num_obj_ver_num NUMBER;
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (500);
l_num_profile_id NUMBER;
cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
l_organization_rec hz_party_v2pub.organization_rec_type;
l_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
l_num_cust_id NUMBER;
l_chr_acct_num VARCHAR2 (500);
l_num_party_id NUMBER;
l_chr_party_number VARCHAR2 (500);
l_cust_acct_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
l_chr_sit_return_status VARCHAR2 (500);
l_num_sit_msg_count NUMBER;
l_chr_sit_msg_data VARCHAR2 (500);
l_chr_situ_return_status VARCHAR2 (500);
l_num_situ_msg_count NUMBER;
l_chr_situ_msg_data VARCHAR2 (500);
l_num_site_use_id NUMBER;
CURSOR update_base_tables_cur
IS
select distinct ps.party_site_name, a.party_number, ps.party_site_number, a.party_id,
'CUST-' ||a.party_id||'-'||ass.org_id as customer_ref,
'ADD-'||ps.party_site_id||'-'||ass.org_id as address_ref,
ps.party_site_id,
ass.address_line1,
ass.city, ass.state, ass.zip, ass.country,
'N' primary_site_use_flag,
a.orig_system_reference as party_ref,
a.party_name as customer_name, ass.org_id
from apps.ap_suppliers ap,
apps.ap_supplier_sites_all ass,
apps.hz_parties a,
apps.hz_party_sites ps
where ap.vendor_id = ass.vendor_id
and a.party_id = ap.party_id
and ps.party_id = ap.party_id
and ass.party_site_id = ps.party_site_id
and a.party_name = ap.vendor_name
and not exists (select * from hz_cust_accounts ac
where ac.party_id = a.party_id);
begin
fnd_global.apps_initialize( 6837, 50546, 222); -- input from 1st sql
dbms_output.put_line('***************************');
for update_base_tables_rec in update_base_tables_cur
loop
null;
cust_account_rec_type.cust_account_id := fnd_api.g_miss_num;
cust_account_rec_type.account_name := update_base_tables_rec.customer_name;
cust_account_rec_type.customer_type := 'R';
--l_organization_rec.party_rec_type.category_code := 'ROAMING';
l_organization_rec.party_rec.party_id := update_base_tables_rec.party_id;
l_organization_rec.party_rec.party_number := update_base_tables_rec.party_number;
l_organization_rec.organization_name := update_base_tables_rec.customer_name;
cust_account_rec_type.orig_system_reference := update_base_tables_rec.customer_ref;
l_customer_profile_rec.party_id := update_base_tables_rec.party_id;
l_customer_profile_rec.profile_class_id := 0 ; -- use default profile with id=0
l_customer_profile_rec.created_by_module := 'HZ_CPUI';
cust_account_rec_type.created_by_module := 'HZ_CPUI';
hz_cust_account_v2pub.create_cust_account
(p_init_msg_list => fnd_api.g_false,
p_cust_account_rec => cust_account_rec_type,
p_organization_rec => l_organization_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile_amt => fnd_api.g_true,
x_cust_account_id => l_num_cust_id,
x_account_number => l_chr_acct_num,
x_party_id => l_num_party_id,
x_party_number => l_chr_party_number,
x_profile_id => l_num_profile_id,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data
);
dbms_output.put_line('x_return_status: '||l_chr_return_status);
dbms_output.put_line('x_cust_account_id: '||l_num_cust_id);
dbms_output.put_line('x_account_number: '||l_chr_acct_num);
dbms_output.put_line('x_party_id: '||l_num_party_id);
dbms_output.put_line(l_chr_return_status ||' ' ||l_chr_msg_data );
if l_chr_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
-- dbms_output.put_line (j);
l_chr_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
-- dbms_output.put_line ('Message(' || j || '):= ' || l_chr_msg_data);
end loop;
end if;
begin
select cust_account_id
into l_cust_acct_site_rec.cust_account_id
from hz_cust_accounts
where orig_system_reference = update_base_tables_rec.customer_ref;
dbms_output.put_line(2 );
exception
when others
then
l_cust_acct_site_rec.cust_account_id := fnd_api.g_miss_num;
end;
l_cust_acct_site_rec.party_site_id := update_base_tables_rec.party_site_id;
l_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
l_cust_acct_site_rec.orig_system_reference := update_base_tables_rec.address_ref;
l_cust_acct_site_rec.org_id := update_base_tables_rec.org_id;
mo_global.init ('ONT');
mo_global.set_policy_context (p_access_mode => 'S',
p_org_id => update_base_tables_rec.org_id
);
dbms_output.put_line(3 );
hz_cust_account_site_v2pub.create_cust_acct_site
(p_init_msg_list => 'T',
p_cust_acct_site_rec => l_cust_acct_site_rec,
x_cust_acct_site_id => l_num_obj_ver_num,
x_return_status => l_chr_sit_return_status,
x_msg_count => l_num_sit_msg_count,
x_msg_data => l_chr_sit_msg_data
);
dbms_output.put_line('x_cust_acct_site_id: '||l_num_obj_ver_num);
dbms_output.put_line('x_return_status: '||l_chr_sit_return_status);
dbms_output.put_line(4||' '|| l_chr_sit_return_status );
if l_chr_sit_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
l_chr_sit_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
end loop;
end if;
begin
select cust_acct_site_id
into l_cust_acct_site_use_rec.cust_acct_site_id
from hz_cust_acct_sites_all
where orig_system_reference = update_base_tables_rec.address_ref;
exception
when others
then
l_cust_acct_site_use_rec.cust_acct_site_id := fnd_api.g_miss_num;
end;
l_cust_acct_site_use_rec.org_id := update_base_tables_rec.org_id;
l_cust_acct_site_use_rec.site_use_code := 'BILL_TO';
l_cust_acct_site_use_rec.status := 'A';
l_cust_acct_site_use_rec.location := update_base_tables_rec.party_site_name;
l_cust_acct_site_use_rec.primary_flag := 'Y';
l_cust_acct_site_use_rec.orig_system_reference :=
update_base_tables_rec.address_ref;
l_cust_acct_site_use_rec.created_by_module := 'HZ_CPUI';
mo_global.set_policy_context (p_access_mode => 'S',
p_org_id => update_base_tables_rec.org_id
);
hz_cust_account_site_v2pub.create_cust_site_use
(p_init_msg_list => 'T',
p_cust_site_use_rec => l_cust_acct_site_use_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile => fnd_api.g_true,
p_create_profile_amt => fnd_api.g_true,
x_site_use_id => l_num_site_use_id,
x_return_status => l_chr_situ_return_status,
x_msg_count => l_num_situ_msg_count,
x_msg_data => l_chr_situ_msg_data
);
dbms_output.put_line('x_site_use_id: '||l_num_site_use_id);
dbms_output.put_line('x_return_status: '||l_chr_situ_return_status);
if l_chr_situ_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
l_chr_situ_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
end loop;
end if;
end loop;
end;
How to find user id,responsibility id, application id for FND_GLOBAL.APPS_INITIALIZE
How to find USER_ID:
select user_id from fnd_user where user_name='test';
How to find RESPONSIBILITY_ID:
select responsibility_id from fnd_responsibility_vl where responsibility_name like 'AP Accountant';
How to get APPLICATION_ID:
select * from fnd_responsibility_vl where responsibility_id=223;
Sunday, April 16, 2017
How to find a word with different endings in the string
SELECT
REGEXP_SUBSTR('I have earned 100 dollars today ','earn((ing)|(ed))')
FROM dual;
result : earned
SELECT
REGEXP_SUBSTR('I have been earning 100 dollars today ','earn((ing)|(ed))')
FROM dual;
result: earning
API to create JOBS
declare
l_business_group_id number;
l_job_group_id number;
l_job_id number;
l_object_version_number number;
l_job_definition_id number;
l_job_name varchar2(200);
l_group_name; varchar2(200) :='test';
begin
-- get group id and business group id
begin
select business_group_id,
job_group_id
into l_business_group_id,
l_job_group_id
from per_job_groups
where displayed_name = l_group_name;
exception
when others then
dbms_output.put_line('Unable to get the job group details.Error:'||sqlerrm);
raise;
end;
--- loop values from temporary table
for i in (
select
name,
date_from,
vacation_days
from temp_jobs
)
loop
HR_JOB_API .CREATE_JOB
(p_validate => FALSE
,p_business_group_id => l_business_group_id
,p_date_from =>i.date_from
,p_comments => 'test for nar'
,p_date_to => NULL
,p_job_group_id => l_job_group_id
,p_concat_segments => i.name
--output
,p_job_id => l_job_id
,p_object_version_number => l_object_version_number
,p_job_definition_id => l_job_definition_id
,p_name => l_job_name
,p_attribute11 => i.vacation_days-- annual vacation days
);
commit;
end loop;
DBMS_OUTPUT.PUT_LINE('job id: '||l_job_id);
DBMS_OUTPUT.PUT_LINE('object_version_number: '||l_object_version_number);
DBMS_OUTPUT.PUT_LINE('job_definition_id: '||l_job_definition_id);
DBMS_OUTPUT.PUT_LINE('job_name: '||l_job_name);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('unable to create a job.error:'||SQLERRM);
rollback;
RAISE;
END;
Tuesday, January 17, 2017
Why organization that I want is not in the list when I create PR?
When PR is created for the certain item, the item should be assigned for this organization first.
Go to Inventory> Items>Master Items
Search for the Item> Go to Organization Assessment folder:
As it is seen above, the item is assigned to other organization but not to the one we need.
So click on the box for it to be assigned and pop up in Organization list while creating PR.
Monday, January 16, 2017
Why the journals are not posted automatically to GL even ‘Transfer to General Ledger’ and ‘Post in General Ledger’is set to Yes?
This is because the period is closed for running period. You should open the period and run Create Accounting again with ‘Transfer to General Ledger’ and ‘Post in General Ledger’ set to Yes.
The journals will be posted automatically.