-- Assign missing Doc sequence values
Declare
CURSOR v_receipts IS
SELECT receipt_number ,
receipt_Date ,
cra.cash_receipt_id ,
org_id ,
cra.receipt_method_id,
( SELECT listagg(DISTINCT fdsa.doc_sequence_id,',') WITHIN GROUP (ORDER BY fdsa.set_of_books_id ASC)
FROM fnd_doc_sequence_categories fdsc,
fnd_doc_sequence_assignments fdsa
WHERE fdsc.application_id = fdsa.application_id
AND fdsc.table_name = 'AR_CASH_RECEIPTS_ALL'
AND fdsc.code = fdsa.category_code
AND trunc(SYSDATE) BETWEEN fdsa.start_date AND nvl(fdsa.end_date,SYSDATE+1)
AND (fdsc.NAME,fdsa.set_of_books_id) in ( SELECT rm.NAME,
(SELECT hou.set_of_books_id
FROM ar_receipt_method_accounts_all rma,
hr_operating_units hou
WHERE rma.receipt_method_id = rm.receipt_method_id
AND rma.org_id = hou.organization_id
AND rma.org_id = cra.org_id )
FROM ar_receipt_methods rm
WHERE rm.receipt_method_id = cra.receipt_method_id
AND rm.end_date IS NULL ) )
doc_seq_id ,
cra.status
FROM ar_cash_receipts_all cra
WHERE org_id = 711
AND creation_date >= '01-JAN-2021'
AND doc_sequence_id IS NULL
AND doc_sequence_value IS NULL
AND status NOT IN ('REV') ;
lv_doc_seq_stmt VARCHAR2(200);
lv_doc_seq_val NUMBER;
lv_count NUMBER := 0;
BEGIN
FOR rec IN v_receipts loop
dbms_output.put_line( rec.RECEIPT_NUMBER ||' - '|| rec.RECEIPT_DATE ||' - '||rec.CASH_RECEIPT_ID||' - '||rec.ORG_ID
||' - '||rec.RECEIPT_METHOD_ID||' - '||rec.DOC_SEQ_ID||' - '||rec.STATUS );
IF rec.DOC_SEQ_ID IS NULL OR instr(rec.DOC_SEQ_ID,',') > 0 THEN
CONTINUE;
END IF;
lv_doc_seq_stmt := 'Select fnd_doc_seq_'||rec.DOC_SEQ_ID||'_s.nextval from dual';
EXECUTE IMMEDIATE lv_doc_seq_stmt INTO lv_doc_seq_val;
dbms_output.put_line('Generated sequence value : '|| lv_doc_seq_val );
IF lv_doc_seq_val IS NOT NULL THEN
UPDATE ar_cash_receipts_all cra
SET cra.doc_sequence_id = rec.doc_seq_id
, cra.doc_sequence_value = lv_doc_seq_val
WHERE cash_receipt_id = rec.cash_receipt_id
AND org_id = rec.org_id ;
lv_count := lv_count +1;
End if;
END loop;
dbms_output.put_line(lv_count|| ' records updated successfully.' );
COMMIT;
Exception
WHEN others THEN
dbms_output.put_line( sqlerrm );
ROLLBACK;
End;
-- grant select to custom users
begin
EXECUTE IMMEDIATE 'Grant execute on apps.pa_proj_detail_v to xxuser';
end;
-- Vendor Site Creation Script
DECLARE
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data varchar2(1000);
l_idx number ;
l_message varchar2(2000);
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;
BEGIN
fnd_global.apps_initialize(3262,52371,200);
FOR rec IN (
SELECT DISTINCT pv.vendor_id , pv.vendor_name,
pvsa.address_line1,pvsa.address_line2,pvsa.country_of_origin_code
FROM ap_suppliers pv, ap_supplier_sites_all pvsa
WHERE pv.vendor_id = pvsa.vendor_id
and (pvsa.org_id = 711 )
AND NOT EXISTS
(SELECT 1
FROM ap_supplier_Sites_all pvs
WHERE pvs.vendor_id = pv.vendor_id
and pvs.org_id = 5983 ) ) Loop
l_vendor_site_rec.vendor_id := rec.vendor_id ;
l_vendor_site_rec.vendor_site_code := 'New Site' ;
l_vendor_site_rec.address_line1 := rec.address_line1;
l_vendor_site_rec.address_line2 := rec.address_line2;
l_vendor_site_rec.country := rec.country_of_origin_code;
l_vendor_site_rec.org_id := 5983;
l_vendor_site_rec.purchasing_site_flag:= 'Y';
l_vendor_site_rec.pay_site_flag := 'Y';
l_vendor_site_rec.rfq_only_site_flag := 'N';
pos_vendor_pub_pkg.create_vendor_site ( p_vendor_site_rec => l_vendor_site_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_site_id => l_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id );
IF l_return_status = 'S' Then COMMIT; End if;
dbms_output.put_line('return_status: '||l_return_status);
IF (l_msg_count > 0)
THEN
FOR i IN 1..FND_MSG_PUB.Count_Msg
LOOP
FND_MSG_PUB.Get (p_msg_index => i,
p_encoded => 'F',
p_data => l_message,
p_msg_index_out => l_idx );
dbms_output.put_line('Errors : '||l_message);
end loop;
end if;
End loop;
Exception WHEN others THEN
dbms_output.put_line(sqlerrm);
END;
Declare
CURSOR v_receipts IS
SELECT receipt_number ,
receipt_Date ,
cra.cash_receipt_id ,
org_id ,
cra.receipt_method_id,
( SELECT listagg(DISTINCT fdsa.doc_sequence_id,',') WITHIN GROUP (ORDER BY fdsa.set_of_books_id ASC)
FROM fnd_doc_sequence_categories fdsc,
fnd_doc_sequence_assignments fdsa
WHERE fdsc.application_id = fdsa.application_id
AND fdsc.table_name = 'AR_CASH_RECEIPTS_ALL'
AND fdsc.code = fdsa.category_code
AND trunc(SYSDATE) BETWEEN fdsa.start_date AND nvl(fdsa.end_date,SYSDATE+1)
AND (fdsc.NAME,fdsa.set_of_books_id) in ( SELECT rm.NAME,
(SELECT hou.set_of_books_id
FROM ar_receipt_method_accounts_all rma,
hr_operating_units hou
WHERE rma.receipt_method_id = rm.receipt_method_id
AND rma.org_id = hou.organization_id
AND rma.org_id = cra.org_id )
FROM ar_receipt_methods rm
WHERE rm.receipt_method_id = cra.receipt_method_id
AND rm.end_date IS NULL ) )
doc_seq_id ,
cra.status
FROM ar_cash_receipts_all cra
WHERE org_id = 711
AND creation_date >= '01-JAN-2021'
AND doc_sequence_id IS NULL
AND doc_sequence_value IS NULL
AND status NOT IN ('REV') ;
lv_doc_seq_stmt VARCHAR2(200);
lv_doc_seq_val NUMBER;
lv_count NUMBER := 0;
BEGIN
FOR rec IN v_receipts loop
dbms_output.put_line( rec.RECEIPT_NUMBER ||' - '|| rec.RECEIPT_DATE ||' - '||rec.CASH_RECEIPT_ID||' - '||rec.ORG_ID
||' - '||rec.RECEIPT_METHOD_ID||' - '||rec.DOC_SEQ_ID||' - '||rec.STATUS );
IF rec.DOC_SEQ_ID IS NULL OR instr(rec.DOC_SEQ_ID,',') > 0 THEN
CONTINUE;
END IF;
lv_doc_seq_stmt := 'Select fnd_doc_seq_'||rec.DOC_SEQ_ID||'_s.nextval from dual';
EXECUTE IMMEDIATE lv_doc_seq_stmt INTO lv_doc_seq_val;
dbms_output.put_line('Generated sequence value : '|| lv_doc_seq_val );
IF lv_doc_seq_val IS NOT NULL THEN
UPDATE ar_cash_receipts_all cra
SET cra.doc_sequence_id = rec.doc_seq_id
, cra.doc_sequence_value = lv_doc_seq_val
WHERE cash_receipt_id = rec.cash_receipt_id
AND org_id = rec.org_id ;
lv_count := lv_count +1;
End if;
END loop;
dbms_output.put_line(lv_count|| ' records updated successfully.' );
COMMIT;
Exception
WHEN others THEN
dbms_output.put_line( sqlerrm );
ROLLBACK;
End;
-- grant select to custom users
begin
EXECUTE IMMEDIATE 'Grant execute on apps.pa_proj_detail_v to xxuser';
end;
-- Vendor Site Creation Script
DECLARE
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data varchar2(1000);
l_idx number ;
l_message varchar2(2000);
l_vendor_site_id NUMBER;
l_party_site_id NUMBER;
l_location_id NUMBER;
BEGIN
fnd_global.apps_initialize(3262,52371,200);
FOR rec IN (
SELECT DISTINCT pv.vendor_id , pv.vendor_name,
pvsa.address_line1,pvsa.address_line2,pvsa.country_of_origin_code
FROM ap_suppliers pv, ap_supplier_sites_all pvsa
WHERE pv.vendor_id = pvsa.vendor_id
and (pvsa.org_id = 711 )
AND NOT EXISTS
(SELECT 1
FROM ap_supplier_Sites_all pvs
WHERE pvs.vendor_id = pv.vendor_id
and pvs.org_id = 5983 ) ) Loop
l_vendor_site_rec.vendor_id := rec.vendor_id ;
l_vendor_site_rec.vendor_site_code := 'New Site' ;
l_vendor_site_rec.address_line1 := rec.address_line1;
l_vendor_site_rec.address_line2 := rec.address_line2;
l_vendor_site_rec.country := rec.country_of_origin_code;
l_vendor_site_rec.org_id := 5983;
l_vendor_site_rec.purchasing_site_flag:= 'Y';
l_vendor_site_rec.pay_site_flag := 'Y';
l_vendor_site_rec.rfq_only_site_flag := 'N';
pos_vendor_pub_pkg.create_vendor_site ( p_vendor_site_rec => l_vendor_site_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_site_id => l_vendor_site_id,
x_party_site_id => l_party_site_id,
x_location_id => l_location_id );
IF l_return_status = 'S' Then COMMIT; End if;
dbms_output.put_line('return_status: '||l_return_status);
IF (l_msg_count > 0)
THEN
FOR i IN 1..FND_MSG_PUB.Count_Msg
LOOP
FND_MSG_PUB.Get (p_msg_index => i,
p_encoded => 'F',
p_data => l_message,
p_msg_index_out => l_idx );
dbms_output.put_line('Errors : '||l_message);
end loop;
end if;
End loop;
Exception WHEN others THEN
dbms_output.put_line(sqlerrm);
END;
-- Re enable users in test server
DECLARE
TYPE v_user_array IS VARRAY(4) OF VARCHAR2(30) ;
v_users v_user_array := v_user_array('TEST1','TEST1','TEST1','TEST1');
BEGIN
FOR i IN 1..v_users.count LOOP
fnd_user_pkg.enableuser(username => v_users(i) );
IF fnd_user_pkg.changepassword(v_users(i),'test1234') THEN
COMMIT;
END IF;
END loop;
DBMS_OUTPUT.put_line( 'Users Re-Enabled.');
For rec in ( SELECT fa.application_short_name, fr.responsibility_key,
fsg.security_group_key, frt.description
FROM apps.fnd_responsibility fr,
fnd_application fa,
fnd_security_groups fsg,
fnd_responsibility_tl frt
WHERE frt.responsibility_name in ('Application Developer','ERP Support')
AND frt.LANGUAGE = USERENV ('LANG')
AND frt.responsibility_id = fr.responsibility_id
AND fr.application_id = fa.application_id
AND fr.data_group_id = fsg.security_group_id ) Loop
fnd_user_pkg.addresp (username => v_users(1),
resp_app => rec.application_short_name,
resp_key => rec.responsibility_key,
security_group => rec.security_group_key,
description => rec.description,
start_date => SYSDATE,
end_date => NULL
);
End loop ;
COMMIT;
DBMS_OUTPUT.put_line( 'Responsiblities Assigned Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Unable to attach responsibility to user due to'
|| SQLCODE
|| ' '
|| SUBSTR (SQLERRM, 1, 100)
);
END;
-- Profile option
UPDATE FND_PROFILE_OPTION_VALUES
SET PROFILE_OPTION_VALUE = Decode(PROFILE_OPTION_ID,1058,'Y',3214,'N')
WHERE PROFILE_OPTION_ID in (1058,3214) --diagnostics
AND LEVEL_VALUE = 22219
AND LEVEL_ID = 10004
AND ROWNUM = 1;
COMMIT;
--- profile update script
UPDATE FND_PROFILE_OPTION_VALUES
SET PROFILE_OPTION_VALUE = '24000'
WHERE PROFILE_OPTION_ID = 7499 --upload file size
AND LEVEL_VALUE = 22219
AND LEVEL_ID = 10004
AND ROWNUM = 1;
Commit;
-- Drop and Create WF adhoc Role
Declare
l_role_name VARCHAR2(50) := 'XX_FYI_ROLE';
l_role_display_name VARCHAR2(100) := 'Internal FYI Role';
l_role_list VARCHAR2(500) := NULL;
l_wf_cnt NUMBER := 0;
vc_proc_name VARCHAR2(30) := 'SET_FYI_ROLE';
BEGIN
SELECT
LISTAGG(user_name, ',') WITHIN GROUP(
ORDER BY
user_name
)
INTO l_role_list
FROM
xx_hierarchy_all h,
xx_hierarchy_group_all g
WHERE
g.group_id = h.group_id
AND g.group_name = 'Internal FYI'
AND sysdate BETWEEN nvl(h.start_date_active, sysdate - 1) AND nvl(h.end_date_active, sysdate + 1);
SELECT
COUNT(1)
INTO l_wf_cnt
FROM
wf_roles
WHERE
name = 'XX_FYI_ROLE';
IF l_wf_cnt > 0 THEN
WF_DIRECTORY.SetAdHocRoleExpiration (role_name=> 'XX_SALE_COMM_FYI_ROLE' ,
expiration_date=> sysdate);
WF_PURGE.Directory (sysdate);
commit;
END IF;
wf_directory.createadhocrole(role_name => l_role_name,
role_display_name => l_role_display_name,
language => NULL,
territory => NULL,
role_description => NULL,
notification_preference => 'MAILHTM2',
--role_users => l_role_list,
email_address => NULL,
fax => NULL,
status => 'ACTIVE',
expiration_date => NULL,
parent_orig_system => NULL,
parent_orig_system_id => NULL,
owner_tag => NULL);
wf_directory.adduserstoadhocrole(role_name => l_role_name,
role_users => l_role_list);
commit;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
-- Initiate Workflow Instance
Declare
l_itemtype VARCHAR2(30) := 'XXCUSTPROC';
l_itemkey VARCHAR2(30) := 'DUP1353';
l_user_id NUMBER :=15302;
l_user_name FND_USER.USER_NAME%TYPE := 'TEST1';
l_count NUMBER;
l_case_id NUMBER := 1353;
l_wf_key NUMBER;
BEGIN
wf_engine.createprocess(l_itemtype, l_itemkey, l_itemtype);
wf_engine.setitemuserkey(itemtype => l_itemtype,
itemkey => l_itemkey,
userkey => l_itemkey);
wf_engine.setitemattrNumber(itemtype => l_itemtype,
ITEMKEY => l_itemkey,
ANAME => 'WORKFLOW_STARTED_BY_ID',
AVALUE => l_user_id);
wf_engine.setitemattrText(itemtype => l_itemtype,
ITEMKEY => l_itemkey,
ANAME => 'WORKFLOW_STARTED_BY_USER',
AVALUE => l_user_name);
wf_engine.setitemattrNumber(itemtype => l_itemtype,
ITEMKEY => l_itemkey,
ANAME => 'PENDING_COUNT',
AVALUE => 5 );
wf_engine.setitemattrNumber(itemtype => l_itemtype,
ITEMKEY => l_itemkey,
ANAME => 'HEADER_ID',
AVALUE => l_case_id);
wf_engine.setitemattrText(itemtype => l_itemtype,
ITEMKEY => l_itemkey,
ANAME => 'ITEM_NO',
AVALUE => 'DM-01361' );
wf_engine.setitemowner(itemtype => l_itemtype
,itemkey => l_itemkey
,owner => l_user_name);
wf_engine.startprocess(l_itemtype, l_itemkey);
commit;
EXCEPTION
WHEN others THEN
dbms_output.put_line ('Error in set userkey processs:' || sqlerrm);
END;
-- Delete adhoc role
DECLARE
l_role_name varchar2(80) := 'XX_FYI_ROLE';
BEGIN
for rec in (SELECT user_name, role_name,user_orig_system,user_orig_system_id,role_orig_system,role_orig_system_id
FROM wf_user_roles
WHERE role_name = 'XX_FYI_ROLE' -- l_role_name
) Loop
wf_directory.DeleteUserRole( p_username => rec.user_name ,
p_rolename => rec.role_name,
p_userOrigSystem => rec.user_orig_system,
p_userorigSystemID => rec.user_orig_system_id,
p_roleOrigSystem => rec.role_orig_system,
p_roleOrigSystemID => rec.role_orig_system_id ) ;
END loop;
-- wf_directory.adduserstoadhocrole(role_name => l_role_name, role_users => 'TEST1');
COMMIT;
dbms_output.put_line( 'User w/ role assignment is complete ... ' );
Exception
WHEN others THEN
dbms_output.put_line( sqlerrm );
End;
-- to enforce date format on a dff field
-- Validation Type special
FND PLSQL "declare
l_value varchar2(15) := :!value ;
l_valid NUMBER := 0 ;
BEGIN
SELECT 1
INTO l_valid
FROM dual
Where l_value like '__-___-____'
and to_date(l_value,'DD-MON-YYYY') between '01-JAN-2000' and '31-DEC-2147' ;
EXCEPTION
WHEN OTHERS THEN
fnd_message.set_name( 'FND', 'FND_GENERIC_MESSAGE' ) ;
fnd_message.set_token( 'MESSAGE', 'Date must be in DD-MON-YYYY format only.' );
fnd_message.raise_error ;
END;
"
No comments:
Post a Comment