Friday 28 October 2016

Query to find out the Payment term and Price list for the customer

select 
 --hza.cust_account_id,
 hza.account_number,hza.account_name,
 --hps.PARTY_SITE_ID,
 hps.party_site_number,
 hzas.attribute1 DUNS,
 hzas.org_id
,hzs.location, hzs.site_use_code ,hzs.bill_to_site_use_id,hzs.status,
hzs.FOB_POINT,hzs.FREIGHT_TERM,hzs.PAYMENT_TERM_ID,rt.name "Payment Term",hzs.PRICE_LIST_ID,qp.name "Price List",
(hzs.ATTRIBUTE2||''||hzs.ATTRIBUTE3) "Shipping Instructions",(hzs.ATTRIBUTE4||''||hzs.ATTRIBUTE5) "Packing Instructions", hzs.SITE_USE_ID,hzs.CUST_ACCT_SITE_ID
from
     hz_cust_accounts_all hza,
     hz_cust_acct_sites_all hzas,
     hz_cust_site_uses_all hzs,
     hz_party_sites hps,
     apps.ra_terms rt,
     apps.qp_list_headers_all qp
where 1=1
and hza.CUST_ACCOUNT_ID = hzas.CUST_ACCOUNT_ID
AND hzas.org_id =103
and hzas.PARTY_SITE_ID = hps.PARTY_SITE_ID
AND hzas.cust_acct_site_id = hzs.cust_acct_site_id
--AND SITE_USE_CODE ='SHIP_TO'
AND hzas.status ='A'
AND hzas.attribute1 in ('785565271')
AND  hzs.payment_term_id = rt.term_id
AND  hzs.price_list_id = qp.list_header_id
AND  qp.active_flag = 'Y'
order by hzas.attribute1 ,hzs.site_use_code

Wednesday 19 October 2016

Script to load data for Menu Exclusions while creation responsiblities

create or replace PROCEDURE CLC_RESP_MENU_EXCLU_PRG
(
errbuf                      OUT VARCHAR2
,retcode                   OUT NUMBER
)
as

l_application_id    fnd_responsibility_tl.application_id%type;
l_responsibility_id    fnd_responsibility_tl.responsibility_id%type;
l_action_id        FND_RESP_FUNCTIONS.ACTION_ID%type;
l_rule_type        FND_RESP_FUNCTIONS.rule_type%type;
l_rowid            varchar2(2000);
l_cnt            number;
l_conc_request_id    fnd_concurrent_requests.REQUEST_ID%type;
l_err_msg        varchar2(2000);   

CURSOR c_menu
IS
SELECT distinct RESPONSIBILITY_NAME,
EXCLU_TYPE,
EXCLU_NAME,
EXCLU_DESC
FROM CLC_RESP_MENU_EXCLU_LOAD
where PROCESS_FLAG='P';


Begin

FND_FILE.PUT_LINE(FND_FILE.LOG,'  Starting of CLC_RESP_MENU_EXCLU_PRG...');
--FND_FILE.PUT_LINE(FND_FILE.LOG,'  Initializing Apps');
--FND_GLOBAL.Apps_Initialize(FND_GLOBAL.USER_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID);

select fnd_global.conc_request_id into l_conc_request_id from dual;

for c_rec in c_menu loop
FND_FILE.PUT_LINE(FND_FILE.LOG,'Loop Start');

l_application_id    := null;   
l_responsibility_id    := null;   
l_action_id        := null;   
l_rule_type        := null;   
l_rowid            := null;   
l_cnt            := 0;
l_err_msg        := null;

select application_id,responsibility_id 
into l_application_id,l_responsibility_id
from fnd_RESPONSIBILITY_tl
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and LANGUAGE =USERENV('LANG');

FND_FILE.PUT_LINE(FND_FILE.LOG,'application_id:' || l_application_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'responsibility_id:' || l_responsibility_id);

if c_rec.EXCLU_TYPE ='Function' then
l_rule_type :='F';
begin
SELECT FUNCTION_ID
into l_action_id
FROM FND_FORM_FUNCTIONS_VL  
WHERE USER_FUNCTION_NAME=c_rec.EXCLU_NAME;
exception
when too_many_rows then
l_err_msg :='too_many_rows in FND_FORM_FUNCTIONS_VL';
l_action_id :=null;
when others then
l_err_msg :='No_Data_Found in FND_FORM_FUNCTIONS_VL';
l_action_id :=null;
end;


elsif c_rec.EXCLU_TYPE ='Menu' then
l_rule_type :='M';
begin
select MENU_ID
into l_action_id
from FND_MENUS_VL
where USER_MENU_NAME=c_rec.EXCLU_NAME;
exception
when too_many_rows then
l_err_msg :='too_many_rows exist in FND_MENUS_VL';
l_action_id :=null;
when others then
l_err_msg :='No_Data_Found in FND_MENUS_VL';
l_action_id :=null;
end;

end if;

FND_FILE.PUT_LINE(FND_FILE.LOG,'action_id:' || l_action_id);
begin
select count(*)
into l_cnt
from FND_RESP_FUNCTIONS
where RESPONSIBILITY_ID=l_responsibility_id
and APPLICATION_ID =l_application_id
and RULE_TYPE =l_rule_type
and ACTION_ID =l_action_id;

exception when others then
l_cnt :=0;
end;

FND_FILE.PUT_LINE(FND_FILE.LOG,'l_cnt:' || l_cnt);


if l_application_id is not null and l_responsibility_id is not null and l_rule_type is not null and  l_action_id is not null and l_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'calling FND_RESP_FUNCTIONS_PKG.INSERT_ROW' );
APPS.FND_RESP_FUNCTIONS_PKG.INSERT_ROW
(l_rowid            --X_ROWID in out nocopy VARCHAR2,
 ,l_application_id    --X_APPLICATION_ID in NUMBER,
 ,l_responsibility_id    --X_RESPONSIBILITY_ID in NUMBER,
 ,l_action_id        --X_ACTION_ID in NUMBER,
 ,l_rule_type        --X_RULE_TYPE in VARCHAR2,
 ,FND_GLOBAL.USER_ID    --X_CREATED_BY in NUMBER,
 ,sysdate        --X_CREATION_DATE in DATE,
 ,FND_GLOBAL.USER_ID    --X_LAST_UPDATED_BY in NUMBER,
 ,sysdate        --X_LAST_UPDATE_DATE in DATE,
 ,FND_GLOBAL.USER_ID    --X_LAST_UPDATE_LOGIN in NUMBER)
);
end if;


if l_rowid is null and l_cnt >= 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update1' );
update CLC_RESP_MENU_EXCLU_LOAD
set PROCESS_FLAG='E',
ERROR_MESSAGE ='Data_Already_Exists',
REQUEST_ID=l_conc_request_id
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and EXCLU_NAME =c_rec.EXCLU_NAME;

elsif l_rowid is null and l_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update2' );
update CLC_RESP_MENU_EXCLU_LOAD
set PROCESS_FLAG='E',
ERROR_MESSAGE =l_err_msg,
REQUEST_ID=l_conc_request_id
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and EXCLU_NAME =c_rec.EXCLU_NAME;

elsif l_rowid is not null and l_cnt < 1 then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update3' );
update CLC_RESP_MENU_EXCLU_LOAD
set PROCESS_FLAG='S',
REQUEST_ID=l_conc_request_id
where RESPONSIBILITY_NAME = c_rec.RESPONSIBILITY_NAME
and EXCLU_NAME =c_rec.EXCLU_NAME;
end if;
commit;
FND_FILE.PUT_LINE(FND_FILE.LOG,'End loop' );

end loop;
FND_FILE.PUT_LINE(FND_FILE.LOG,'End Program' );

EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,' OTHER exception ');
end CLC_RESP_MENU_EXCLU_PRG;
/