Hi Tom,

I have written a procedure and called another procedure in it which is updating one column in the table.
when I am running the standalone procedure it is working fine.
But when the same procedure is scheduled through dbms_job.submit the called procedure is not being executed.

Please find the below procedure.

create or replace PROCEDURE PRC_CRM_CON_SUBPRODATA(V_TABLE_NAME VARCHAR2,V_INSTANCE_NAME VARCHAR2,V_CIRCLE_ID VARCHAR2)IS
start_time TIMESTAMP;
V_START_TIME TIMESTAMP;
end_time TIMESTAMP;
V_END_TIME TIMESTAMP;
merge_count NUMBER;
v_update NUMBER;
v_insert NUMBER;
error_msg VARCHAR2(100);
v_date TIMESTAMP;
sql_count number;
v_status_flag varchar2(1);
S varchar2(2000);
S1 varchar2(6000);
l_end_time TIMESTAMP;
t1 VARCHAR2(100);
t2 VARCHAR2(100);
t3 VARCHAR2(100);

V_tbl_name VARCHAR2(10);
V_INST_NAME VARCHAR2(2);
V_cirlce_NAME VARCHAR2(4);

BEGIN
t1:='CRM_HZ_PARTIES_'||v_table_name ;
dbms_output.put_line (t1);
t2:='CRM_HZ_CONTACT_POINTS_'||v_table_name;
dbms_output.put_line (t2);
t3:='CRM_HZ_PERSON_PROFILES_'||v_table_name;
dbms_output.put_line (t3);



SELECT systimestamp INTO start_time FROM dual;
-- v_status_flag:='Y';
dbms_output.put_line('job_start_time:'||start_time);
SELECT MAX(START_TIME) INTO V_START_TIME
FROM CRM_CON_SUBS_PROFILE_TRACE_LOG
WHERE PROGRAM_NAME ='PRC_CRM_CON_SUBPRODATA_'||V_INSTANCE_NAME||'_'||V_CIRCLE_ID and completion_status ='Y';
dbms_output.put_line('start_time:'||V_START_TIME);

select MAX(END_TIME)into V_end_time FROM CRM_CON_SUBS_PROFILE_TRACE_LOG;
--dbms_output.put_line('l_time:'||l_end_time);

S:='SELECT count(party.party_id) '--- into sql_count '
||'FROM '
|| t1 ||' party,'|| t2 ||' mobile,' || t3 || ' HZPROF'

||' WHERE MOBILE.OWNER_TABLE_NAME = ''HZ_PARTIES'' '
||'AND MOBILE.CONTACT_POINT_TYPE = ''PHONE'' '
||'AND MOBILE.PHONE_LINE_TYPE = ''MOBILE'' '
||'AND MOBILE.STATUS = ''A'' '
||'AND MOBILE.OWNER_TABLE_ID = PARTY.PARTY_ID '
||'AND MOBILE.ATTRIBUTE20 = ''CELL'' '
||' AND PARTY.ATTRIBUTE10 = '
||''''||v_Circle_id||''''||
' AND hzprof.party_id = mobile.owner_table_id '
||'AND SUBSTR (party.party_number, 1, 1) = ''S'' '
||'AND hzprof.person_profile_id ='
||' (SELECT MAX (profx.person_profile_id)'
||' FROM '
||t3 || ' profx'
||' WHERE profx.party_id = party.party_id )'
||' AND (mobile.Last_refresh_time BETWEEN '||''''|| V_START_TIME|| ''''|| ' AND ' ||''''|| start_time|| ''''
||' OR party.LAST_REFRESH_TIME BETWEEN ' ||''''|| V_START_TIME || ''''|| ' AND ' ||''''|| start_time || ''''
||' OR HZPROF.LAST_REFRESH_TIME BETWEEN ' ||''''|| V_START_TIME || ''''|| ' AND ' ||''''|| start_time || ''''|| ')';


DBMS_OUTPUT.PUT_LINE(S);
EXECUTE IMMEDIATE S INTO SQL_COUNT;
DBMS_OUTPUT.PUT_LINE('sql_count:'||sql_count);

SELECT completion_status INTO v_status_flag FROM
cache_interval_table WHERE sp_name ='PRC_CRM_CON_SUBPRODATA_'||V_INSTANCE_NAME||'_'||V_CIRCLE_ID ;



DBMS_OUTPUT.PUT_LINE(v_status_flag);

if v_status_flag = 'Y' and sql_count >0 then
S1:=' MERGE INTO CRM_CON_SUBS_PROFILE b'
||' USING ( SELECT DISTINCT party.attribute10 circle_id,
mobile.phone_number msisdn,
party.attribute4 customer_id,
party.attribute3 co_id,
party.party_id,
party.person_last_name_phonetic account_no,
'''' Tariff_plan,
party.attribute18 activation_dt,
'''' puk1,
party.party_name,
party.person_first_name person_first_name,
--party.person_middle_name person_middle_name,
--party.person_last_name person_last_name,
--TO_CHAR (hzprof.date_of_birth, ''DD-MON-YYYY'') birthdate,
LOWER (NVL (
(SELECT email_address'
||' FROM '
||t2 ||' cpx'
||' WHERE cpx.contact_point_type = ''EMAIL'''
||' AND cpx.owner_table_id = party.party_id '
||'AND MOBILE.STATUS = ''A'' '
||' AND ROWNUM < 2 ),'
|| ' party.email_address ) )email_address,'

||'(party.address1 ||'',''||party.address2||'',''||party.address3||'',''||party.address4) Residential_address,'
|| 'UPPER (party.city) city,'
||'party.postal_code, '
||'party.attribute5 alternate_contact_no1,'
||'(SELECT phone_number'
|| ' FROM CRM_HZ_CONTACT_POINTS_DISHA cx '
|| ' WHERE contact_point_type = ''PHONE'''
|| ' AND PHONE_LINE_TYPE = ''ALTERNATE'''
|| ' AND status =''A'''
|| ' AND OWNER_TABLE_ID = PARTY.PARTY_ID'
|| ' AND ROWNUM < 2'
|| ') ALTERNATE_CONTACT_NO2,'
|| ''''' BILL_LANGUAGE,'
|| ''''' BILL_MODE,'
|| ''''' BILL_TYPE_ITEMIZED_YN,'
||'PARTY.ATTRIBUTE6 CUSTOMER_SEGMENT,'
||'HZPROF.PERSON_PRE_NAME_ADJUNCT SALUTATION,'
||'('
||'SELECT DISTINCT ba_id'
||' FROM CRM_VEL_PAY_CHANNEL_DISHA '
||'WHERE fa_id = party.person_last_name_phonetic '
||'AND rownum < 2'
||') BA_ID ,'
||'CASE'
||' WHEN party.attribute6 NOT LIKE ''P%'''
||'THEN ''POSTPAID'''
||'END VFNV_FLAG,'
||' party.attribute10||mobile.phone_number||party.party_id id ,'
||' '''' OPERATION_STATUS ,'
|| 'systimestamp UPDATED_TIME ,'
|| 'systimestamp LAST_REFRESH_TIME'

||' FROM CRM_HZ_PARTIES_DISHA party,'
||' CRM_HZ_CONTACT_POINTS_DISHA mobile,'
||' CRM_HZ_PERSON_PROFILES_DISHA HZPROF'
||' WHERE MOBILE.OWNER_TABLE_NAME = ''HZ_PARTIES'''
||'AND MOBILE.CONTACT_POINT_TYPE = ''PHONE'''
||' AND MOBILE.PHONE_LINE_TYPE = ''MOBILE'''
||' AND MOBILE.STATUS = ''A'''
||' AND MOBILE.OWNER_TABLE_ID = PARTY.PARTY_ID'
||' AND MOBILE.ATTRIBUTE20 = ''CELL'''
||' AND PARTY.ATTRIBUTE10 = '
||''''||v_Circle_id||''''||
' AND hzprof.party_id = mobile.owner_table_id '
||' AND SUBSTR (party.party_number, 1, 1) = ''S'''
||' AND hzprof.person_profile_id = '
||' (SELECT MAX (profx.person_profile_id)'
||' FROM '
||t3 || ' profx'
||' WHERE profx.party_id = party.party_id )'
||' AND (mobile.Last_refresh_time BETWEEN '||''''|| V_START_TIME|| ''''|| ' AND ' ||''''|| start_time|| ''''
||' OR party.LAST_REFRESH_TIME BETWEEN ' ||''''|| V_START_TIME || ''''|| ' AND ' ||''''|| start_time || ''''
||' OR HZPROF.LAST_REFRESH_TIME BETWEEN ' ||''''|| V_START_TIME || ''''|| ' AND ' ||''''|| start_time || ''''|| '))e'


||' ON (b.id=e.id)'
|| ' WHEN matched THEN UPDATE SET '
|| 'b.circle_id =e.circle_id, '
||'b.msisdn=e.msisdn,'
|| ' b.CUSTOMER_ID =e.CUSTOMER_ID,'
|| ' B.CO_ID =E.CO_ID,'
|| ' b.PARTY_ID =e.PARTY_ID, '
|| ' b.ACCOUNT_NO =e.ACCOUNT_NO, '
|| ' b.TARIFF_PLAN =e.TARIFF_PLAN, '
|| ' b.ACTIVATION_DT =e.ACTIVATION_DT, '
|| ' b.PUK1 =e.PUK1, '
|| ' b.PARTY_NAME =e.PARTY_NAME, '
||' b.PERSON_FIRST_NAME =e.PERSON_FIRST_NAME, '
||' b.EMAIL_ADDRESS =e.EMAIL_ADDRESS,'
||' b.RESIDENTIAL_ADDRESS =e.RESIDENTIAL_ADDRESS,'
||'b.CITY =e.CITY, '
||' b.POSTAL_CODE =e.POSTAL_CODE,'
||' b.ALTERNATE_CONTACT_NO1=e.ALTERNATE_CONTACT_NO1,'
||' b.ALTERNATE_CONTACT_NO2=e.ALTERNATE_CONTACT_NO2,'
||' b.BILL_LANGUAGE =e.BILL_LANGUAGE,'
||' b.BILL_MODE =e.BILL_MODE,'
||' b.BILL_TYPE_ITEMIZED_YN=e.BILL_TYPE_ITEMIZED_YN,'
||' b.CUSTOMER_SEGMENT =e.CUSTOMER_SEGMENT,'
||' b.SALUTATION =e.SALUTATION,'
||' b.BA_ID =e.BA_ID,'
||' b.VFNV_FLAG =e.VFNV_FLAG,'
||' b.OPERATION_STATUS =''UPDATED'','
||' b.UPDATED_TIME =e.updated_time,'
||' b.REFRESH_TIMESTAMP=E.LAST_REFRESH_TIME '
||' WHEN NOT matched THEN '
||' INSERT ('
||' b.msisdn,'
||' b.circle_id,'
||' b.CUSTOMER_ID,'
||' B.CO_ID,'
||' b.PARTY_ID,'
||' b.ACCOUNT_NO,'
||' b.TARIFF_PLAN,'
||' b.ACTIVATION_DT,'
||' b.PUK1,'
||' b.PARTY_NAME,'
||' b.PERSON_FIRST_NAME,'
||' b.EMAIL_ADDRESS,'
||' b.RESIDENTIAL_ADDRESS,'
||' b.CITY,'
||' b.POSTAL_CODE,'
||' b.ALTERNATE_CONTACT_NO1,'
||' b.ALTERNATE_CONTACT_NO2,'
||' b.BILL_LANGUAGE,'
||' b.BILL_MODE,'
||' b.BILL_TYPE_ITEMIZED_YN,'
||' b.CUSTOMER_SEGMENT,'
||' b.SALUTATION,'
||' b.BA_ID,'
||' b.VFNV_FLAG,'
||' b.id ,'
||' b.OPERATION_STATUS,'
||' b.UPDATED_TIME,'
||' b.REFRESH_TIMESTAMP ) '
||' VALUES'
||' ( '
||' e.msisdn,'
||' e.circle_id,'
||' e.CUSTOMER_ID,'
||' e.CO_ID,'
||' e.PARTY_ID,'
||' e.ACCOUNT_NO,'
||' e.TARIFF_PLAN,'
||' e.ACTIVATION_DT,'
||' e.PUK1,'
||' e.PARTY_NAME,'
||' e.PERSON_FIRST_NAME,'
||' e.EMAIL_ADDRESS,'
||' e.RESIDENTIAL_ADDRESS,'
||' e.CITY,'
||' e.POSTAL_CODE,'
||' e.ALTERNATE_CONTACT_NO1,'
||' e.ALTERNATE_CONTACT_NO2,'
||' e.BILL_LANGUAGE,'
||' e.BILL_MODE,'
||' e.BILL_TYPE_ITEMIZED_YN,'
||' e.CUSTOMER_SEGMENT,'
||' e.SALUTATION,'
||' e.BA_ID,'
||' e.VFNV_FLAG,'
||' e.id,'
||' ''INSERTED'','
||' e.updated_time,'
||' e.LAST_REFRESH_TIME )'
||'LOG ERRORS INTO CRM_CON_SUBS_PROFILE_ERR (''UNSUCCESSFULL FOR PRC_CRM_CON_SUBPRODATA_'||V_INSTANCE_NAME||'_'||v_circle_id||''' )'

||'REJECT LIMIT unlimited';
DBMS_OUTPUT.PUT_LINE(s1);
EXECUTE IMMEDIATE s1;

merge_count :=sql%rowcount;
DBMS_OUTPUT.PUT_LINE('merge_count:'||merge_count);


COMMIT;

SELECT COUNT(*)
INTO v_insert
FROM CRM_CON_SUBS_PROFILE
WHERE UPDATED_TIME > start_time
AND OPERATION_STATUS='INSERTED'
and CIRCLE_id =V_CIRCLE_id;
SELECT COUNT(*)
INTO v_UPDATE
FROM CRM_CON_SUBS_PROFILE
WHERE UPDATED_TIME > start_time
AND OPERATION_STATUS='UPDATED'
and CIRCLE_id =V_CIRCLE_id;

SELECT systimestamp INTO END_TIME FROM dual;


INSERT
INTO CRM_CON_SUBS_PROFILE_TRACE_LOG
(
PROGRAM_NAME,
START_TIME,
END_TIME,
STATUS,
ERROR_MSG,
TRACE_ID,
LOG_DATE,
merge_count,
INSERT_COUNT,
update_count,
Completion_status
)
VALUES
(
'PRC_CRM_CON_SUBPRODATA_'||V_INSTANCE_NAME||'_'||V_CIRCLE_ID,
start_time,
end_time,
DECODE(merge_count,0,'Not Completed Successfully for PRC_CRM_CON_SUBPRODATA_'||V_INSTANCE_NAME||'_'||V_CIRCLE_ID ,'Completed Successfully for PRC_CRM_CON_SUBPRODATA_'||V_INSTANCE_NAME||'_'||V_CIRCLE_ID),
error_msg,
1,
systimestamp,
merge_count,
v_insert,
v_UPDATE,
decode(merge_count,0,'N','Y')
);
DBMS_OUTPUT.PUT_LINE(END_TIME);


commit;

V_tbl_name :=V_TABLE_NAME;
V_INST_NAME :=V_INSTANCE_NAME;
V_cirlce_NAME:=V_CIRCLE_ID;
dbms_output.put_line(V_tbl_name);
dbms_output.put_line(V_INST_NAME);
dbms_output.put_line(V_cirlce_NAME);

PRC_TARIFF_PLAN_UPDATE1(V_tbl_name ,V_INST_NAME ,V_cirlce_NAME );

else

SELECT systimestamp INTO END_TIME FROM dual;

INSERT
INTO CRM_CON_SUBS_PROFILE_TRACE_LOG
(
PROGRAM_NAME,
START_TIME,
END_TIME,
STATUS,
ERROR_MSG,
TRACE_ID,
LOG_DATE,
merge_count,
INSERT_COUNT,
update_count,
Completion_status
)
VALUES
(
'PRC_CRM_CON_SUBPRODATA_'||V_INSTANCE_NAME||'_'||V_CIRCLE_ID,
start_time,
END_TIME,
decode(sql_count ,0,'Not Completed Successfully for PRC_CRM_CON_SUBPRODATA_'||V_INSTANCE_NAME||'_'||V_CIRCLE_ID || ' as query returns no rows','Status_flag is N'),
error_msg,
1,
systimestamp,
merge_count,
v_insert,
v_UPDATE,
'Y'
-- decode(SQL_COUNT,0,'N','Y')
);
end if;

end;


create or replace PROCEDURE PRC_TARIFF_PLAN_UPDATE1(
V_tbl_name VARCHAR2,
V_INST_name VARCHAR2,
V_CIRCLE_name VARCHAR2)
IS
l_start_time TIMESTAMP;
V_START_TIME TIMESTAMP;
l_end_time TIMESTAMP;
V_END_TIME TIMESTAMP;
merge_count NUMBER;
v_update NUMBER;
v_insert NUMBER;
error_msg VARCHAR2(100);
v_date TIMESTAMP;
v_status_flag VARCHAR2(1);
v_tariff_count NUMBER;
v_merge NUMBER;
s VARCHAR2(2000);
t1 VARCHAR2(100);
t2 VARCHAR2(100);
t3 VARCHAR2(100);
t4 VARCHAR2(100);
BEGIN
--crm_csm_offer_disha csmoff,
-- crm_mtl_system_items_b_disha mtl,
-- crm_csi_item_instances_disha csi,
-- CRM_CSI_INSTANCE_STATUS_DISHA cis
t1:='crm_csm_offer_'||V_tbl_name ;
dbms_output.put_line (t1);
t2:='crm_mtl_system_items_b_'||V_tbl_name;
dbms_output.put_line (t2);
t3:='crm_csi_item_instances_'||V_tbl_name;
dbms_output.put_line (t3);
t4:='CRM_csi_INSTANCE_STATUS_'||V_tbl_name ;
dbms_output.put_line (t4);
SELECT systimestamp INTO l_start_time FROM dual;
dbms_output.put_line('PRC_CRM_CON_SUBPRODATA_'||V_INST_name||'_'||V_CIRCLE_name);
SELECT MAX(START_TIME),
MAX(END_TIME)--,completion_status
INTO V_START_TIME ,
V_END_TIME--,v_status_flag
FROM CRM_CON_SUBS_PROFILE_TRACE_LOG
WHERE PROGRAM_NAME ='PRC_CRM_CON_SUBPRODATA_'
||V_INST_name
||'_'
||V_CIRCLE_name
AND completion_status ='Y';
SELECT completion_status
INTO v_status_flag
FROM CRM_CON_SUBS_PROFILE_TRACE_LOG
WHERE START_TIME =V_START_TIME
AND END_TIME =V_END_TIME;
IF V_status_flag ='Y' THEN
s :='MERGE INTO crm_con_subs_profile b ' ||'USING (select tariff_plan,id ,party_id from (
SELECT
party_id,
csp.id,
mtl.description AS tariff_plan,
ROW_NUMBER() over(PARTITION BY csp.id order by csp.id) as tariff_plan1
FROM '|| t1 ||' csmoff,' ||t2 ||' mtl,'||t3 ||' csi,' ||t4 || ' cis,'||' CRM_CON_SUBS_PROFILE csp ' ||'WHERE csi.owner_party_id = csp.party_id ' ||'AND mtl.inventory_item_id = csi.inventory_item_id ' ||'AND csi.instance_status_id = cis.instance_status_id ' ||'AND cis.NAME = ''ACTIVE'' ' ||'AND TO_CHAR (csmoff.be) = mtl.segment4 ' ||'AND csmoff.soc_cd = mtl.segment1 ' ||'AND mtl.item_type = ''PP'' ' ||' AND CIRCLE_ID ='||V_CIRCLE_name|| ' AND csi.active_start_date = (SELECT MAX(active_start_date) FROM ' ||t3|| ' a WHERE csi.owner_party_id=a.owner_party_id ' || 'AND a.INVENTORY_ITEM_ID =csi.INVENTORY_ITEM_ID ' ||' AND a.INSTANCE_STATUS_ID=csi.INSTANCE_STATUS_ID ' ||
' AND A.ACTIVE_END_DATE IS NULL ) and csp.refresh_timestamp between '||''''|| V_START_TIME|| ''''|| ' AND ' ||''''|| v_end_time|| '''' ||') where tariff_plan1 =1 ) e ' ||' ON (b.id=e.id)' || 'WHEN matched THEN UPDATE SET b.tariff_plan =e.tariff_plan ' ||'LOG ERRORS INTO CRM_CON_SUBS_PROFILE_ERR (''UNSUCCESSFULL FOR PRC_TARIFF_PLAN_UPDATE_'||V_INST_name||V_CIRCLE_name||''' )' ||'REJECT LIMIT unlimited';
dbms_output.put_line(s);
EXECUTE IMMEDIATE s;
v_tariff_count:=sql%rowcount;
dbms_output.put_line('tariff_count:'||v_tariff_count);
SELECT systimestamp INTO l_end_time FROM dual;
INSERT
INTO CRM_CON_SUBS_PROFILE_TRACE_LOG
(
PROGRAM_NAME,
START_TIME,
END_TIME,
STATUS,
ERROR_MSG,
TRACE_ID,
LOG_DATE,
TARIFF_COUNT,
Completion_status
)
VALUES
(
'PRC_TARIFF_PLAN_UPDATE_'
||V_INST_name
||'_'
||V_CIRCLE_name,
l_start_time,
l_end_time,
DECODE(v_TARIFF_COUNT,0,'Not Completed Successfully for PRC_TARIFF_PLAN_UPDATE'
||V_INST_name
||'_'
||V_CIRCLE_name ,'Completed Successfully for PRC_TARIFF_PLAN_UPDATE_'
||V_INST_name
||'_'
||V_CIRCLE_name),
error_msg,
1,
sysdate,
v_TARIFF_COUNT,
DECODE(v_TARIFF_COUNT,0,'N','Y')
);
SELECT merge_count ,
insert_count
INTO v_merge,
v_insert
FROM CRM_CON_SUBS_PROFILE_TRACE_LOG
WHERE v_start_time < l_start_time
AND PROGRAM_NAME ='PRC_CRM_CON_SUBPRODATA_'
||V_INST_name
||'_'
||V_CIRCLE_name
AND start_time =v_start_time;
dbms_output.put_line('v_update' ||v_update);
dbms_output.put_line('v_insert' ||v_insert);
dbms_output.put_line('v_tariff_count' ||v_tariff_count);
dbms_output.put_line('l_end_time'||l_end_time);
IF (v_update >0 OR v_insert> 0) AND v_tariff_count >0 THEN
UPDATE cache_interval_table
SET LAST_EXEC_TIME =l_end_time,
completion_status='Y'
WHERE sp_name ='PRC_CRM_CON_SUBPRODATA_'
||V_INST_name
||'_'
||V_CIRCLE_name
AND CIRCLE_ID =V_CIRCLE_name;
ELSE
UPDATE cache_interval_table
SET completion_status='N'
WHERE sp_name ='PRC_CRM_CON_SUBPRODATA_'
||V_INST_name
||'_'
||V_CIRCLE_name
AND CIRCLE_ID =V_CIRCLE_name;
END IF;
COMMIT;
ELSE
INSERT
INTO CRM_CON_SUBS_PROFILE_TRACE_LOG
(
PROGRAM_NAME,
START_TIME,
END_TIME,
STATUS,
ERROR_MSG,
TRACE_ID,
LOG_DATE,
TARIFF_COUNT,
Completion_status
)
VALUES
(
'PRC_TARIFF_PLAN_UPDATE_'
||V_INST_name
||'_'
||V_CIRCLE_name,
v_start_time,
v_end_time,
'Not Completed Successfully for PRC_TARIFF_PLAN_UPDATE_'
||V_INST_name
||'_'
||V_CIRCLE_name,
error_msg,
1,
sysdate,
v_TARIFF_COUNT,
'N'
);
COMMIT;
END IF;
END PRC_Tariff_Plan_Update1;


script for job

DECLARE
jobno numeric;
BEGIN dbms_job.submit(jobno, 'PRC_CRM_CON_SUBPRODATA(''DISHA'',''DI'',''0022'');', sysdate, 'trunc(sysdate,''mi'')+5/1440');
commit;
END;

Please help with this.I am stuck with this as i am unable to find the solution.

Thanks in Advance.
Priti Singh