CREATE TABLE APPS.XXAYTB_MEDICAL_MASTER_DATA
(
SR_NO VARCHAR2(240 BYTE),
EMPLOYEE_NUMBER VARCHAR2(240 BYTE),
CONSULTATION_DATE DATE,
TYPE VARCHAR2(240 BYTE),
RESULT VARCHAR2(240 BYTE),
EXAMINER_NAME VARCHAR2(240 BYTE),
NEXT_CONSULTATION_DATE VARCHAR2(240 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
TIME_IN VARCHAR2(50 BYTE),
TIME_OUT VARCHAR2(50 BYTE),
SICK_LEAVE_FROM DATE,
SICK_LEAVE_TO DATE,
CASE_A VARCHAR2(240 BYTE),
REFER_TO VARCHAR2(240 BYTE),
MEDICINE_COST VARCHAR2(100 BYTE),
THIRD_PARTY_COST VARCHAR2(100 BYTE),
REMARKS VARCHAR2(240 BYTE),
API_ERROR VARCHAR2(240 BYTE),
PROCESS_FLAG VARCHAR2(30 BYTE),
ERROR_MESSAGE VARCHAR2(4000 BYTE),
PROCESS_DATE DATE,
REQUEST_ID NUMBER,
CREATED_BY NUMBER,
PERSON_ID NUMBER,
OBJECT_VERSION_NUMBER NUMBER
)
--*****************************************************
CREATE OR REPLACE PROCEDURE APPS.XXAYTB_MEDICAL_PROC(
errbuff OUT VARCHAR2,
retcode OUT NUMBER
)
IS
--Created by Senthil Kalvarayapillai wipro 10-Mar-2013
/* Out variables */
p_validate boolean default false;
p_medical_assessment_id number;
p_object_version_number number;
/* In variables*/
p_person_id number;
p_reference varchar2(240);
p_type varchar2(240);
p_status varchar2(240);
p_status_reason varchar2(240) default null;
p_organization_id NUMBER DEFAULT NULL;
p_next_consultation_date DATE DEFAULT NULL;
l_medical_assessment_id number;
l_object_version_number number;
/* Error Handling Variables */
v_error_message varchar2(240);
v_err_count number := 0;
v_cnt NUMBER := 0;
v_err_code VARCHAR2 (50);
v_err_msg VARCHAR2 (2000);
ln_business_group_id number;
ln_user_id number;
ln_org_id number;
ln_resp_id number;
ln_error NUMBER := 0;
lv_user_msg_err VARCHAR2(10000) := NULL;
v_effective_start_date date;
v_effective_date date;
v_bg_id NUMBER;
v_status VARCHAR2 (100);
l_error_message VARCHAR2 (2000);
l_error_code VARCHAR2 (2000);
CURSOR C1 IS
SELECT distinct ppf.person_id ppf_person_id,
decode(to_char(xmmd.sick_leave_from,'YYYY')||'/'||to_char(xmmd.sick_leave_from,'MM')||'/'||to_char(xmmd.sick_leave_from,'DD'),'//','',to_char(xmmd.sick_leave_from,'YYYY')||'/'||to_char(xmmd.sick_leave_from,'MM')||'/'||to_char(xmmd.sick_leave_from,'DD'))sick_leave_from_aa,
decode(to_char(xmmd.sick_leave_to,'YYYY')||'/'||to_char(xmmd.sick_leave_to,'MM')||'/'||to_char(xmmd.sick_leave_to,'DD'),'//','',to_char(xmmd.sick_leave_to,'YYYY')||'/'||to_char(xmmd.sick_leave_to,'MM')||'/'||to_char(xmmd.sick_leave_to,'DD'))sick_leave_to_aa,
xmmd.*
FROM
APPS.XXAYTB_MEDICAL_MASTER_DATA xmmd
,per_all_people_f ppf
where substr(xmmd.EMPLOYEE_NUMBER,3,15)=ppf.EMPLOYEE_NUMBER
--and xmmd.SR_NO='823'
--AND XMMD.EMPLOYEE_NUMBER='BC23481'
AND XMMD.CONSULTATION_DATE IS NOT NULL
and xmmd.CREATED_BY=666
--AND XMMD.PROCESS_FLAG IS NULL --29693
--AND XMMD.PROCESS_FLAG ='E' --29693
;
BEGIN
ln_user_id := fnd_profile.VALUE ('USER_ID');-- Get the user_id
ln_org_id := fnd_profile.VALUE ('ORG_ID'); -- Get the Org_id
ln_resp_id := fnd_profile.VALUE ('RESP_ID');-- Get the Resp_id
FND_FILE.put_line(FND_FILE.output,'user id '||ln_user_id);
FND_FILE.put_line(FND_FILE.output,'org id '||ln_org_id);
FND_FILE.put_line(FND_FILE.output,'resp id '||ln_resp_id);
--Initializing the variables
fnd_global.apps_initialize (user_id => ln_user_id,
resp_id => 50639,
resp_appl_id => 800
);
--mo_global.init ('PER');
mo_global.set_policy_context ('S', ln_org_id);
For REC IN C1
Loop
begin
per_medical_assessment_api.create_medical_assessment
( p_validate => FALSE
,p_effective_date => TRUNC(SYSDATE)
,p_person_id => rec.ppf_person_id
,p_consultation_date => rec.consultation_date
,p_consultation_type => 'RMC' --nvl(rec.type,'AM')
,p_consultation_result => '' --rec.result
,p_examiner_name => rec.examiner_name
,p_organization_id => p_organization_id -- med_ass_det_rec.service_provider_id
,p_next_consultation_date => p_next_consultation_date
,p_description => rec.description
,p_attribute1 =>rec.time_in
,p_attribute2 =>rec.time_out
,p_attribute3 =>rec.sick_leave_from_aa
,p_attribute4 =>rec.sick_leave_to_aa
,p_attribute5 =>rec.refer_to
,p_attribute6 =>rec.medicine_cost
,p_attribute7 =>rec.third_party_cost
,p_attribute8 =>rec.remarks
,p_attribute9 =>rec.CASE_A
,p_medical_assessment_id => l_medical_assessment_id
,p_object_version_number => l_object_version_number
);
dbms_output.put_line('Medical loaded for the employee '||rec.employee_number);
FND_FILE.put_line(FND_FILE.output,'Medical loaded for the employee '||rec.employee_number);
UPDATE APPS.XXAYTB_MEDICAL_MASTER_DATA
SET PROCESS_FLAG = 'S',
error_message = 'SUCCESS--Medical ID IS '||p_medical_assessment_id,
REQUEST_ID=p_medical_assessment_id
WHERE EMPLOYEE_NUMBER = REC.EMPLOYEE_NUMBER
and SR_NO=rec.SR_NO;
DBMS_OUTPUT.put_line ( 'Medical Created: '
|| REC.EMPLOYEE_NUMBER
|| ' '
|| '-'
|| ' '
|| p_medical_assessment_id
);
p_medical_assessment_id := NULL;
p_object_version_number := NULL;
EXCEPTION
WHEN OTHERS
THEN
v_err_msg := SUBSTR (SQLERRM, 1, 100);
v_err_code := SQLCODE;
DBMS_OUTPUT.put_line ( 'Medical NOT Created:Error in API.'
|| '-'
|| REC.EMPLOYEE_NUMBER
);
FND_FILE.put_line(FND_FILE.output,'Medical NOT loaded for the employee: '||rec.employee_number);
UPDATE XXAYTB_MEDICAL_MASTER_DATA
SET process_flag = 'E',
error_message = v_err_msg
WHERE EMPLOYEE_NUMBER = REC.EMPLOYEE_NUMBER
and SR_NO=rec.SR_NO
;
p_medical_assessment_id := NULL;
p_object_version_number := NULL;
END;
END LOOP;
end;
/
(
SR_NO VARCHAR2(240 BYTE),
EMPLOYEE_NUMBER VARCHAR2(240 BYTE),
CONSULTATION_DATE DATE,
TYPE VARCHAR2(240 BYTE),
RESULT VARCHAR2(240 BYTE),
EXAMINER_NAME VARCHAR2(240 BYTE),
NEXT_CONSULTATION_DATE VARCHAR2(240 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
TIME_IN VARCHAR2(50 BYTE),
TIME_OUT VARCHAR2(50 BYTE),
SICK_LEAVE_FROM DATE,
SICK_LEAVE_TO DATE,
CASE_A VARCHAR2(240 BYTE),
REFER_TO VARCHAR2(240 BYTE),
MEDICINE_COST VARCHAR2(100 BYTE),
THIRD_PARTY_COST VARCHAR2(100 BYTE),
REMARKS VARCHAR2(240 BYTE),
API_ERROR VARCHAR2(240 BYTE),
PROCESS_FLAG VARCHAR2(30 BYTE),
ERROR_MESSAGE VARCHAR2(4000 BYTE),
PROCESS_DATE DATE,
REQUEST_ID NUMBER,
CREATED_BY NUMBER,
PERSON_ID NUMBER,
OBJECT_VERSION_NUMBER NUMBER
)
--*****************************************************
CREATE OR REPLACE PROCEDURE APPS.XXAYTB_MEDICAL_PROC(
errbuff OUT VARCHAR2,
retcode OUT NUMBER
)
IS
--Created by Senthil Kalvarayapillai wipro 10-Mar-2013
/* Out variables */
p_validate boolean default false;
p_medical_assessment_id number;
p_object_version_number number;
/* In variables*/
p_person_id number;
p_reference varchar2(240);
p_type varchar2(240);
p_status varchar2(240);
p_status_reason varchar2(240) default null;
p_organization_id NUMBER DEFAULT NULL;
p_next_consultation_date DATE DEFAULT NULL;
l_medical_assessment_id number;
l_object_version_number number;
/* Error Handling Variables */
v_error_message varchar2(240);
v_err_count number := 0;
v_cnt NUMBER := 0;
v_err_code VARCHAR2 (50);
v_err_msg VARCHAR2 (2000);
ln_business_group_id number;
ln_user_id number;
ln_org_id number;
ln_resp_id number;
ln_error NUMBER := 0;
lv_user_msg_err VARCHAR2(10000) := NULL;
v_effective_start_date date;
v_effective_date date;
v_bg_id NUMBER;
v_status VARCHAR2 (100);
l_error_message VARCHAR2 (2000);
l_error_code VARCHAR2 (2000);
CURSOR C1 IS
SELECT distinct ppf.person_id ppf_person_id,
decode(to_char(xmmd.sick_leave_from,'YYYY')||'/'||to_char(xmmd.sick_leave_from,'MM')||'/'||to_char(xmmd.sick_leave_from,'DD'),'//','',to_char(xmmd.sick_leave_from,'YYYY')||'/'||to_char(xmmd.sick_leave_from,'MM')||'/'||to_char(xmmd.sick_leave_from,'DD'))sick_leave_from_aa,
decode(to_char(xmmd.sick_leave_to,'YYYY')||'/'||to_char(xmmd.sick_leave_to,'MM')||'/'||to_char(xmmd.sick_leave_to,'DD'),'//','',to_char(xmmd.sick_leave_to,'YYYY')||'/'||to_char(xmmd.sick_leave_to,'MM')||'/'||to_char(xmmd.sick_leave_to,'DD'))sick_leave_to_aa,
xmmd.*
FROM
APPS.XXAYTB_MEDICAL_MASTER_DATA xmmd
,per_all_people_f ppf
where substr(xmmd.EMPLOYEE_NUMBER,3,15)=ppf.EMPLOYEE_NUMBER
--and xmmd.SR_NO='823'
--AND XMMD.EMPLOYEE_NUMBER='BC23481'
AND XMMD.CONSULTATION_DATE IS NOT NULL
and xmmd.CREATED_BY=666
--AND XMMD.PROCESS_FLAG IS NULL --29693
--AND XMMD.PROCESS_FLAG ='E' --29693
;
BEGIN
ln_user_id := fnd_profile.VALUE ('USER_ID');-- Get the user_id
ln_org_id := fnd_profile.VALUE ('ORG_ID'); -- Get the Org_id
ln_resp_id := fnd_profile.VALUE ('RESP_ID');-- Get the Resp_id
FND_FILE.put_line(FND_FILE.output,'user id '||ln_user_id);
FND_FILE.put_line(FND_FILE.output,'org id '||ln_org_id);
FND_FILE.put_line(FND_FILE.output,'resp id '||ln_resp_id);
--Initializing the variables
fnd_global.apps_initialize (user_id => ln_user_id,
resp_id => 50639,
resp_appl_id => 800
);
--mo_global.init ('PER');
mo_global.set_policy_context ('S', ln_org_id);
For REC IN C1
Loop
begin
per_medical_assessment_api.create_medical_assessment
( p_validate => FALSE
,p_effective_date => TRUNC(SYSDATE)
,p_person_id => rec.ppf_person_id
,p_consultation_date => rec.consultation_date
,p_consultation_type => 'RMC' --nvl(rec.type,'AM')
,p_consultation_result => '' --rec.result
,p_examiner_name => rec.examiner_name
,p_organization_id => p_organization_id -- med_ass_det_rec.service_provider_id
,p_next_consultation_date => p_next_consultation_date
,p_description => rec.description
,p_attribute1 =>rec.time_in
,p_attribute2 =>rec.time_out
,p_attribute3 =>rec.sick_leave_from_aa
,p_attribute4 =>rec.sick_leave_to_aa
,p_attribute5 =>rec.refer_to
,p_attribute6 =>rec.medicine_cost
,p_attribute7 =>rec.third_party_cost
,p_attribute8 =>rec.remarks
,p_attribute9 =>rec.CASE_A
,p_medical_assessment_id => l_medical_assessment_id
,p_object_version_number => l_object_version_number
);
dbms_output.put_line('Medical loaded for the employee '||rec.employee_number);
FND_FILE.put_line(FND_FILE.output,'Medical loaded for the employee '||rec.employee_number);
UPDATE APPS.XXAYTB_MEDICAL_MASTER_DATA
SET PROCESS_FLAG = 'S',
error_message = 'SUCCESS--Medical ID IS '||p_medical_assessment_id,
REQUEST_ID=p_medical_assessment_id
WHERE EMPLOYEE_NUMBER = REC.EMPLOYEE_NUMBER
and SR_NO=rec.SR_NO;
DBMS_OUTPUT.put_line ( 'Medical Created: '
|| REC.EMPLOYEE_NUMBER
|| ' '
|| '-'
|| ' '
|| p_medical_assessment_id
);
p_medical_assessment_id := NULL;
p_object_version_number := NULL;
EXCEPTION
WHEN OTHERS
THEN
v_err_msg := SUBSTR (SQLERRM, 1, 100);
v_err_code := SQLCODE;
DBMS_OUTPUT.put_line ( 'Medical NOT Created:Error in API.'
|| '-'
|| REC.EMPLOYEE_NUMBER
);
FND_FILE.put_line(FND_FILE.output,'Medical NOT loaded for the employee: '||rec.employee_number);
UPDATE XXAYTB_MEDICAL_MASTER_DATA
SET process_flag = 'E',
error_message = v_err_msg
WHERE EMPLOYEE_NUMBER = REC.EMPLOYEE_NUMBER
and SR_NO=rec.SR_NO
;
p_medical_assessment_id := NULL;
p_object_version_number := NULL;
END;
END LOOP;
end;
/
1 comment:
Casino Review - DRMCD
Play Real 김제 출장안마 Money Online Slots at DRMCD. We have found that 안산 출장안마 we are not only one of the best casino sites for slots 충청남도 출장샵 but 포천 출장샵 also the best 영천 출장안마 table games.
Post a Comment