[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete from Ego_Publication_Batch_GT;
INSERT INTO EGO_PUBLICATION_BATCH_GT
(
Batch_id
,Pk1_Value
,Pk2_value
,Pk3_value
,Pk4_Value
,Pk5_value
,user_entered
,entity_type_id
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
l_batch_id
,pk1_value(i)
,pk2_value(i)
,pk3_value(i)
,pk4_value(i)
,pk5_value(i)
,user_entered(i)
,entity_type_id(i)
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
);
||' ' || sql%rowcount || ' rows are inserted into EGO_PUBLICATION_BATCH_GT.');
delete from EGO_PUBLICATION_BATCH_GT;
INSERT INTO Ego_Pub_Bat_Ent_Objs_B
( Batch_Entity_Object_id
,Batch_id
,PK1_value
,PK2_value
,PK3_value
,PK4_value
,PK5_value
,user_entered
,entity_type_id
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,object_version_number
)
SELECT
Ego_Pub_Bat_Ent_Objs_S1.NEXTVAL
,Batch_Id
,Pk1_Value
,Pk2_Value
,Pk3_Value
,Pk4_Value
,Pk5_Value
,User_Entered
,Entity_Type_Id
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,1
FROM Ego_Publication_Batch_GT
WHERE Batch_Id = p_batch_id;
||' ' || sql%rowcount || ' rows are inserted into EGO_PUB_BAT_ENT_OBJS_B.');
SELECT Batch_Entity_Object_id
,System_Code
BULK COLLECT INTO
t_batch_ent_obj_id
,t_system_code
FROM Ego_Pub_Bat_Ent_Objs_B EO,
EGO_PUB_BAT_SYSTEMS_B SYS
WHERE EO.Batch_Id = p_batch_id
AND EO.User_Entered = C_NO
AND SYS.Batch_Id = EO.Batch_Id;
INSERT INTO Ego_Pub_Bat_Status_B
(
Batch_Id
,System_Code
,Batch_Entity_Object_id
,Status_Code
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,object_version_number
)
VALUES
(
p_batch_id
,t_system_code(i)
,t_batch_ent_obj_id(i)
,C_IN_PROCESS
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,1
);
||' ' || sql%rowcount || ' rows are inserted into EGO_PUB_BAT_STATUS_B.');
Procedure Update_Pub_Status_Thru_AIA(p_batch_id IN NUMBER
,p_mode In Number
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_stmt_num NUMBER;
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA'
,p_message => 'Enter EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA ... '
||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
Select Batch_id
,System_Code
,Pk1_Value
,Pk2_value
,Pk3_value
,Pk4_Value
,Pk5_value
,Status
,Message
BULK COLLECT INTO
t_dbg_batchid
,t_dbg_system_code
,dbg_pk1_value
,dbg_pk2_value
,dbg_pk3_value
,dbg_pk4_value
,dbg_pk5_value
,t_dbg_status
,t_dbg_msg
FROM Ego_Publication_Batch_GT
WHERE Batch_id = p_batch_id;
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA'
,p_message =>' i --' ||
' batch_id(i) --' ||
' pk1_value(i) --' ||
' pk2_value(i) --' ||
' pk3_value(i) --' ||
' pk4_value(i) --' ||
' pk5_value(i) --' ||
' system_code(i) --' ||
' status(i) --' ||
' msg(i)--'
);
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA'
,p_message => i || ' --' ||
t_dbg_batchid(i) || ' --' ||
dbg_pk1_value(i) || ' --' ||
dbg_pk2_value(i) || ' --' ||
dbg_pk3_value(i) || ' --' ||
dbg_pk4_value(i) || ' --' ||
dbg_pk5_value(i) || ' --' ||
t_dbg_system_code(i) || ' --' ||
t_dbg_status(i)|| ' --' ||
t_dbg_msg(i)
);
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA'
,p_message => 'Exit EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA successfully... '
||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status_Thru_AIA'
,p_message => 'Unexpedted Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
END Update_Pub_Status_Thru_AIA;
Select count(*)
into l_ct_S
from ego_publication_batch_gt
WHERE batch_id = p_batch_id
and RETURN_STATUS = C_SUCCESS;
select count(*)
into l_ct_bat
from ego_publication_batch_gt
WHERE batch_id = p_batch_id;
Update ego_publication_batch_gt
set return_status = C_FAILED,
return_error_code = 'EGO_INVALID_MODE',
return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_INVALID_MODE'),
ret_err_msg_lang = USERENV('LANG')
where batch_id = p_batch_id;
Select batch_id
into l_batch_id
from Ego_Pub_Bat_Hdr_B
where batch_id = p_batch_Id;
Update ego_publication_batch_gt
set return_status = C_FAILED,
return_error_code = 'EGO_PUB_BATCHID_NULL',
return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_PUB_BATCHID_NULL'),
ret_err_msg_lang = USERENV('LANG')
where batch_id =p_batch_id;
Update ego_publication_batch_gt
set return_status = C_FAILED,
return_error_code = 'EGO_NO_HEADER',
return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_NO_HEADER'),
ret_err_msg_lang = USERENV('LANG')
where batch_id =p_batch_id;
Update Ego_Publication_Batch_GT
Set Return_status = C_FAILED,
process_flag = 1,
return_error_code = 'EGO_INVALID_STATUS_CODE',
return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_INVALID_STATUS_CODE'),
ret_err_msg_lang = USERENV('LANG')
where Status not in (select lookup_code
from fnd_lookups
where Lookup_Type = 'EGO_PUBLICATION_STATUS')
and batch_id = p_batch_id;
Update Ego_Publication_Batch_GT
Set Return_status = C_FAILED,
process_flag = 2,
return_error_code = 'EGO_INVALID_BAT_SYS',
return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_INVALID_BAT_SYS'),
ret_err_msg_lang = USERENV('LANG')
where (batch_id,
system_Code) not in (select batch_id,
system_code
from Ego_Pub_Bat_status_b
where batch_id = p_batch_id)
and batch_id = p_batch_id
and process_flag is null;
UPDATE ego_publication_batch_gt gt
SET batch_entity_object_id =
(SELECT eo.batch_entity_object_id
FROM ego_pub_bat_ent_objs_b eo
WHERE batch_id = gt.batch_id
AND eo.pk1_value = nvl(gt.pk1_value, -99)
AND nvl(eo.pk2_value, -99) = nvl(gt.pk2_value, -99)
AND nvl(eo.pk3_value, -99) = nvl(gt.pk3_value, -99)
AND nvl(eo.pk4_value, -99) = nvl(gt.pk4_value, -99)
AND nvl(eo.pk5_value, -99) = nvl(gt.pk5_value, -99)
and gt.batch_id = p_batch_id);
UPDATE ego_publication_batch_gt
SET return_status = C_FAILED,
process_flag = 3,
return_error_code = 'EGO_INVALID_BAT_SYS_ENT',
return_error_message = FND_MESSAGE.get_string ('EGO', 'EGO_INVALID_BAT_SYS_ENT'),
ret_err_msg_lang = USERENV('LANG')
WHERE ( nvl(batch_entity_object_id,-99),
nvl(batch_id,-99),
nvl(system_code,-99)) NOT IN
(SELECT
batch_entity_object_id,
batch_id,
system_code
FROM ego_pub_bat_status_b
WHERE batch_id = p_batch_id)
AND batch_id = p_batch_id
and process_flag is null;
SELECT batch_entity_object_id,
batch_id,
system_code,
status,
message
BULK COLLECT INTO
t_batch_ent_obj_id,
t_batch_id,
t_system_code,
t_status,
t_message
from ego_publication_batch_gt
where batch_id = p_batch_id
and return_status is null
and process_flag is null;
UPDATE ego_pub_bat_status_b
SET status_code = t_status(i),
message = t_message(i)
WHERE batch_id = t_batch_id(i);
UPDATE ego_pub_bat_status_b
SET status_code = t_status(i),
message = t_message(i)
WHERE batch_id = t_batch_id(i)
AND system_code = t_system_code(i);
UPDATE ego_pub_bat_status_b
SET status_code = t_status(i),
message = t_message(i)
WHERE batch_entity_object_id = t_batch_ent_obj_id(i)
AND batch_id = t_batch_id(i)
AND system_code = t_system_code(i);
update ego_publication_batch_gt
set return_status = C_SUCCESS
where batch_id = p_batch_id
and process_flag is null;
PROCEDURE Update_Pub_Status ( p_batch_id IN NUMBER
,p_mode In Number
,p_bat_status_in IN TBL_OF_BAT_ENT_OBJ_STAT_TYPE
,x_bat_status_out OUT NOCOPY TBL_OF_BAT_ENT_OBJ_RSTS_TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
--The following variables types should match with Ego_Publication_Batch_GT
pk1_value CHAR150_ARR_TBL_TYPE;
delete from Ego_Publication_Batch_GT;
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
,p_message => 'Enter EGO_PUB_FWK_PK.Update_Pub_Status ...'
||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
,p_message =>' i --' ||
' pk1_value(i) --' ||
' pk2_value(i) --' ||
' pk3_value(i) --' ||
' pk4_value(i) --' ||
' pk5_value(i) --' ||
' system_code(i) --' ||
' status(i) --' ||
' message(i)' );
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
,p_message => i || ' --' ||
p_bat_status_in(i).pk1_value || ' --' ||
p_bat_status_in(i).pk2_value || ' --' ||
p_bat_status_in(i).pk3_value || ' --' ||
p_bat_status_in(i).pk4_value || ' --' ||
p_bat_status_in(i).pk5_value || ' --' ||
p_bat_status_in(i).system_code || ' --' ||
p_bat_status_in(i).status || ' --' ||
p_bat_status_in(i).message);
INSERT INTO EGO_PUBLICATION_BATCH_GT
(
Batch_id
,pk1_value
,pk2_value
,pk3_value
,pk4_value
,pk5_value
,system_code
,status
,message
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
p_batch_id
,pk1_value(i)
,pk2_value(i)
,pk3_value(i)
,pk4_value(i)
,pk5_value(i)
,t_system_code(i)
,t_status(i)
,t_message(i)
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
);
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
,p_message => 'lStmtNum=' || to_char(l_stmt_num)
||' ' ||sql%rowcount || ' rows are inserted into EGO_PUBLICATION_BATCH_GT.');
Select Batch_id
,System_Code
,Pk1_Value
,Pk2_value
,Pk3_value
,Pk4_Value
,Pk5_value
,Return_Status
,Return_Error_Message
,RETURN_ERROR_CODE
,RET_ERR_MSG_LANG
BULK COLLECT INTO
t_ret_batchid
,t_ret_system_code
,ret_pk1_value
,ret_pk2_value
,ret_pk3_value
,ret_pk4_value
,ret_pk5_value
,t_ret_status
,t_ret_err_msg
,t_ret_err_code
,t_ret_err_msg_lang
FROM Ego_Publication_Batch_GT
WHERE Batch_id = p_batch_id;
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
,p_message =>' i --' ||
' batch_id(i) --' ||
' pk1_value(i) --' ||
' pk2_value(i) --' ||
' pk3_value(i) --' ||
' pk4_value(i) --' ||
' pk5_value(i) --' ||
' system_code(i) --' ||
' ret_status(i) --' ||
' ret_err_msg(i) --' ||
' ret_err_code(i) --' ||
' ret_err_msg_lang(i)' );
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
,p_message => i || ' --' ||
t_ret_batchid(i) || ' --' ||
ret_pk1_value(i) || ' --' ||
ret_pk2_value(i) || ' --' ||
ret_pk3_value(i) || ' --' ||
ret_pk4_value(i) || ' --' ||
ret_pk5_value(i) || ' --' ||
t_ret_system_code(i) || ' --' ||
t_ret_status(i)|| ' --' ||
t_ret_err_msg(i) || ' --' ||
t_ret_err_code(i) || ' --' ||
t_ret_err_msg_lang(i) );
delete from Ego_Publication_Batch_GT;
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
,p_message => 'Exit EGO_PUB_FWK_PK.Update_Pub_Status successfully... '
||to_char(sysdate, 'dd-mon-yyyy hh:mi:ss'));
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
,p_message => 'Exception EGO_NO_BAT_STS_IN in stmt num: ' || l_stmt_num|| ': '||'sqlerrm=>' ||sqlerrm);
,p_module => 'EGO_PUB_FWK_PK.Update_Pub_Status'
,p_message => 'Others Exception in stmt num: ' || l_stmt_num || ': '||'sqlerrm=>' ||sqlerrm );
END Update_Pub_Status;
Procedure DeleteGTTableData(x_return_status OUT NOCOPY VARCHAR2)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
Delete from Ego_Publication_Batch_GT;
,p_module => 'EGO_PUB_FWK_PK.DeleteGTTableData'
,p_message => 'sqlerrm=>' ||sqlerrm );
END DeleteGTTableData;
select ORIG_SYSTEM into l_system_code
from hz_orig_systems_vl ssm
where ssm.Status = 'A' AND ssm.orig_system_type = 'SPOKE'
AND ORIG_SYSTEM = l_system_code;
select ORIG_SYSTEM into l_system_code
from hz_orig_systems_vl ssm
where ssm.Status = 'A' AND ssm.orig_system_type = 'SPOKE'
AND ORIG_SYSTEM_NAME = l_system_name;
,p_message =>'SELECT_DIFFERENT_SYSTEM');
select inner_name into l_struct_name
from (
SELECT bom_globals.retrieve_message('BOM', 'BOM_PRIMARY') displayname,
'PRIMARY' inner_name
FROM dual
UNION
SELECT bad.display_name displayname,
bad.alternate_designator_code inner_name
FROM bom_alternate_designators_vl bad
WHERE bad.alternate_designator_code IS NOT NULL
AND Nvl(bad.disable_date,SYSDATE + 1) > SYSDATE
) struct
where (struct.displayname = l_struct_name
or struct.inner_name = l_struct_name)
and rownum = 1;
select rkey into l_resp_key from (
select r.responsibility_key rkey
from FND_USER_RESP_GROUPS_DIRECT gd,
fnd_responsibility r,
fnd_user u
where gd.responsibility_id = r.responsibility_id
and gd.responsibility_application_id = r.application_id
and gd.user_id = u.user_id
and (gd.end_date > sysdate or gd.end_date is null)
and u.user_id = l_user_id
and r.responsibility_id = l_resp_id
union all
select r.responsibility_key rkey
from FND_USER_RESP_GROUPS_INDIRECT gi,
fnd_responsibility r,
fnd_user u
where gi.responsibility_id = r.responsibility_id
and gi.responsibility_application_id = r.application_id
and gi.user_id = u.user_id
and (gi.end_date > sysdate or gi.end_date is null)
and u.user_id = l_user_id
and r.responsibility_id = l_resp_id) rdi;
SELECT party_id INTO l_party_id from ego_user_v
where user_id = p_user_id;
select concatenated_segments into l_item_number
from mtl_system_items_kfv
where inventory_item_id = l_item_id and organization_id = l_org_id;
SELECT 'N'
FROM MTL_ITEM_REVISIONS_B
WHERE IMPLEMENTATION_DATE IS null AND REVISION_ID = c_rev_id;
SELECT Max(revision_id) RevisionId
FROM mtl_item_revisions_b
WHERE inventory_item_id = c_item_id
AND organization_id = c_org_id
AND effectivity_date <= c_expl_date
AND implementation_date IS NOT NULL;
select concatenated_segments into l_item_number
from mtl_system_items_kfv
where inventory_item_id = l_item_id
and organization_id = l_org_id;
select mi.inventory_item_id into l_entity_id
from mtl_system_items_b mi, mtl_item_revisions_b mr
where mi.inventory_item_id = mr.inventory_item_id
and mi.organization_id = mr.organization_id
and mi.inventory_item_id = l_pk1_value
and mi.organization_id = l_pk2_value
and mr.REVISION_ID = l_pk3_value
and SYSDATE BETWEEN NVL(mi.START_DATE_ACTIVE, SYSDATE) and NVL(mi.END_DATE_ACTIVE, SYSDATE);
select icc.ITEM_CATALOG_GROUP_ID into l_entity_id
from MTL_ITEM_CATALOG_GROUPS_B icc, EGO_MTL_CATALOG_GRP_VERS_B icc_ver
where icc.ITEM_CATALOG_GROUP_ID = icc_ver.ITEM_CATALOG_GROUP_ID
and icc.ITEM_CATALOG_GROUP_ID = l_pk1_value
and icc_ver.VERSION_SEQ_ID = l_pk2_value
and SYSDATE BETWEEN NVL(icc.START_DATE_ACTIVE, SYSDATE) and NVL(icc.END_DATE_ACTIVE, SYSDATE);
select icc.ITEM_CATALOG_GROUP_ID into l_entity_id
from MTL_ITEM_CATALOG_GROUPS_B icc
where icc.ITEM_CATALOG_GROUP_ID = l_pk1_value
and SYSDATE BETWEEN NVL(icc.START_DATE_ACTIVE, SYSDATE) and NVL(icc.END_DATE_ACTIVE, SYSDATE);
select value_set_id into l_entity_id
from EGO_VALUE_SETS_V
where value_set_id = l_pk1_value;
select ATTR_GROUP_ID into l_entity_id
from EGO_ATTR_GROUPS_V
where ATTR_GROUP_ID = l_pk1_value;
select PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE, PK5_VALUE
BULK COLLECT INTO l_pk1_vals, l_pk2_vals, l_pk3_vals, l_pk4_vals, l_pk5_vals
from EGO_PUB_BAT_ENT_OBJS_B where BATCH_ID = p_batch_id and USER_ENTERED = 'Y';
* check the selected item rev is implemented
*/
if p_check_priv then
begin
select batch_id into l_batch_id
from EGO_PUB_BAT_HDR_B
where batch_id = p_batch_id and created_by = l_user_id;
select distinct batch_id into l_batch_id
from EGO_PUB_BAT_STATUS_B
where batch_id = p_batch_id and STATUS_CODE = C_STATUS_DRAFT;
select date_value into l_expl_date
from EGO_PUB_BAT_PARAMS_B
where type_id = p_batch_id and parameter_name = C_PARAM_EXPLDATE;
SELECT nvl(customer_id , person_party_id) into l_party_id
FROM fnd_user WHERE user_id = l_user_id;
update EGO_PUB_BAT_HDR_B set PUBLISHED_BY = l_party_id, BATCH_CREATION_DATE = sysdate
where batch_id = l_batch_id;
update EGO_PUB_BAT_STATUS_B set STATUS_CODE = C_STATUS_SUBMITTED
where batch_id = l_batch_id and STATUS_CODE <> C_STATUS_SUBMITTED;
select MTL_BUSINESS_EVENTS_S.nextval into l_event_key from dual;
select CASE WHEN (CHAR_VALUE IS NULL) THEN 'N' ELSE 'Y' END
into l_struct_flag
from EGO_PUB_BAT_PARAMS_B
where TYPE_ID = l_batch_id AND PARAMETER_NAME = C_PARAM_STRUCTN;
select CHAR_VALUE into l_icc_parent_flag
from EGO_PUB_BAT_PARAMS_B
where TYPE_ID = l_batch_id AND PARAMETER_NAME = C_PARAM_PUBPARENT;
select CHAR_VALUE into l_icc_child_flag
from EGO_PUB_BAT_PARAMS_B
where TYPE_ID = l_batch_id AND PARAMETER_NAME = C_PARAM_PUBCHILD;
select 1 into l_name_ct from EGO_PUB_BAT_HDR_B
where batch_name = p_batch_name;
SELECT nvl(customer_id , person_party_id) into l_party_id
FROM fnd_user WHERE user_id = l_user_id;
select EGO_PUB_BAT_HDR_S1.nextVal into l_batch_id from dual;
insert into EGO_PUB_BAT_HDR_B (
BATCH_ID,
BATCH_NAME,
BATCH_TYPE,
PUBLISHED_BY,
RESPONSIBILITY_ID,
BATCH_CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
Values (
l_batch_id,
p_batch_name,
p_batch_type,
l_party_id,
l_resp_id,
l_submit_date,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
p_api_version
);
select EGO_PUB_BAT_PARAMS_S1.NEXTVAL into l_type_param_id from dual;
insert into EGO_PUB_BAT_PARAMS_B (
TYPE_PARAM_ID,
TYPE_ID,
TYPE,
PARAMETER_NAME,
DATA_TYPE,
DATE_VALUE,
CHAR_VALUE,
NUMERIC_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
values (
l_type_param_id,
l_batch_id,
l_pub_params(i).param_type,
l_pub_params(i).param_name,
l_pub_params(i).data_type,
l_pub_params(i).date_value,
l_pub_params(i).char_value,
l_pub_params(i).number_value,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
p_api_version
);
select EGO_PUB_BAT_ENT_OBJS_S1.NEXTVAL into l_ent_obj_id from dual;
insert into EGO_PUB_BAT_ENT_OBJS_B (
batch_entity_object_id,
batch_id,
entity_type_id,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
user_entered,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
values (
l_ent_obj_id,
l_batch_id,
p_batch_type,
l_batch_entities(i).pk1_value,
l_batch_entities(i).pk2_value,
l_batch_entities(i).pk3_value,
l_batch_entities(i).pk4_value,
l_batch_entities(i).pk5_value,
l_batch_entities(i).user_entered,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
p_api_version
);
insert into EGO_PUB_BAT_SYSTEMS_B (
batch_id,
system_code,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
VALUES (
l_batch_id,
l_target_systems(i).system_code,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
p_api_version
);
insert into EGO_PUB_BAT_STATUS_B (
batch_id,
system_code,
batch_entity_object_id,
status_code,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER
)
values (
l_batch_id,
l_target_systems(j).system_code,
l_batch_entity_obj_ids(i),
l_batch_status,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
p_api_version
);
select batch_id, batch_type, created_by
into l_batch_id, l_batch_type, l_creator_id
from ego_pub_bat_hdr_b
where batch_name = l_batch_name and batch_id = l_batch_id;
select batch_id, batch_type, created_by
into l_batch_id, l_batch_type, l_creator_id
from ego_pub_bat_hdr_b
where batch_name = l_batch_name ;
select batch_id, batch_type, created_by
into l_batch_id, l_batch_type, l_creator_id
from ego_pub_bat_hdr_b
where batch_id = l_batch_id;
select status_code into l_status
from EGO_PUB_BAT_STATUS_B
where batch_id = l_batch_id and rownum = 1;
select nvl(char_value, 'PUBLISH') into l_pub_type
from ego_pub_bat_params_b
where type_id = l_batch_id AND PARAMETER_NAME = C_PARAM_PUBTYPE;
* check the selected item rev is implemented
*/
x_return_status := FND_API.G_RET_STS_SUCCESS;
select batch_id, batch_name, batch_type
into l_batch_id, l_batch_name, l_batch_type
from ego_pub_bat_hdr_b
where batch_name = p_batch_name and batch_id = p_batch_id;
select batch_id, batch_name, batch_type
into l_batch_id, l_batch_name, l_batch_type
from ego_pub_bat_hdr_b
where batch_name = p_batch_name ;
select batch_id, batch_name, batch_type
into l_batch_id, l_batch_name, l_batch_type
from ego_pub_bat_hdr_b
where batch_id = p_batch_id;
select TYPE, PARAMETER_NAME, DATA_TYPE, DATE_VALUE,
CHAR_VALUE, NUMERIC_VALUE
BULK COLLECT INTO l_param_types, l_param_names, l_param_data_types,
l_param_date_vals, l_param_char_vals, l_param_num_vals
from EGO_PUB_BAT_PARAMS_B where TYPE_ID = l_batch_id ;
select SYSTEM_CODE
BULK COLLECT INTO l_system_codes
from EGO_PUB_BAT_SYSTEMS_B where BATCH_ID = l_batch_id;
select count(1) into l_counter
from ego_pub_bat_hdr_b
where batch_name like l_batch_name || '%';
PROCEDURE delete_batch (p_api_version IN NUMBER,
p_batch_names IN dbms_sql.varchar2_table,
p_batch_ids IN dbms_sql.number_table,
p_purge_days IN NUMBER,
x_con_req_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_module_name varchar2(50) := 'EGO_PUB_FWK_PK.delete_batch';
select batch_id into l_batch_id
from ego_pub_bat_hdr_b
where batch_name = l_batch_name and batch_id = l_batch_id;
select batch_id into l_batch_id
from ego_pub_bat_hdr_b
where batch_name = l_batch_name ;
select batch_id into l_batch_id
from ego_pub_bat_hdr_b
where batch_id = l_batch_id;
end delete_batch;