[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT meaning table_name,
attribute1 severity,
attribute2 severity_id
FROM fnd_lookup_values
WHERE lookup_type = 'MSC_TEMP_PARTITIONS' AND
enabled_flag = 'Y' AND
view_application_id = 700 AND
language = userenv('lang');
update the msc_apps_instances collections_start_time*/
PROCEDURE SET_COLLECTIONS_START_TIME(pINSTANCE_ID in number,
p_resource_start_time out NOCOPY date)
IS
lv_sql_stmt varchar2(32767);
SELECT DECODE(M2A_DBLINK,
NULL,MSC_UTIL.NULL_DBLINK,
'@'||M2A_DBLINK),
DECODE( A2M_DBLINK,
NULL,MSC_UTIL.NULL_DBLINK,
A2M_DBLINK),
INSTANCE_CODE
INTO lv_dblink,
lv_dest_a2m,
lv_instance_code
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID=pINSTANCE_ID;
lv_sql_stmt:= 'SELECT nvl(mar.LRD,sysdate)- '||lv_res_avail_before_sysdate
||' FROM '||lv_table_name||lv_dblink||' mar'
||' WHERE INSTANCE_ID = '||pINSTANCE_ID
||' AND INSTANCE_CODE = '''||lv_instance_code||''''
||' AND nvl(A2M_DBLINK,'''||MSC_UTIL.NULL_DBLINK||''') = '''||lv_dest_a2m||'''' ;
SELECT nvl(COLLECTIONS_START_TIME,sysdate)
INTO lv_collection_start_time
FROM msc_coll_parameters
where instance_id = pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES
SET COLLECTIONS_START_TIME= lv_collection_start_time
where instance_id = pINSTANCE_ID;
lv_sql_stmt := 'UPDATE MSC_INSTANCE_ORGS '
||' SET last_succ_asl_ref_time = :lv_collection_start_time'
||' WHERE sr_instance_id = ' || pINSTANCE_ID
|| ' AND ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str ;
lv_sql_stmt := 'UPDATE MSC_INSTANCE_ORGS '
||' SET LAST_SUCC_IRO_REF_TIME = :lv_collection_start_time'
||' WHERE sr_instance_id = ' || pINSTANCE_ID
|| ' AND ORGANIZATION_ID ' || MSC_UTIL.v_depot_org_str ;
lv_sql_stmt := 'UPDATE MSC_INSTANCE_ORGS '
||' SET LAST_SUCC_RES_REF_TIME = :lv_collection_start_time'
||' WHERE sr_instance_id = ' || pINSTANCE_ID
|| ' AND ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str ;
lv_sql_stmt := 'UPDATE MSC_INSTANCE_ORGS '
||' SET LAST_SUCC_ITEM_REF_TIME = :lv_collection_start_time'
||' WHERE sr_instance_id = ' || pINSTANCE_ID
|| ' AND ORGANIZATION_ID ' || MSC_UTIL.v_in_org_str ;
SELECT PULL_WRKR_START_TIME into lv_COLL_PULL_START_TIME
from msc_coll_parameters
where INSTANCE_ID = pINSTANCE_ID;
UPDATE msc_apps_instances
SET PULL_WRKR_START_TIME = lv_COLL_PULL_START_TIME,
SNAP_REF_START_TIME = lv_collection_start_time
where instance_id = pINSTANCE_ID;
SELECT MSC_ERRORS_S.NEXTVAL
INTO SEQ
FROM DUAL;
SELECT MSC_ERRORS_S.NEXTVAL
INTO SEQ
FROM DUAL;
SELECT tab.partitioned
FROM ALL_TABLES tab,
FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id= 724
AND tab.owner= a.oracle_username
AND tab.table_name= p_table_name;
SELECT LRTYPE,
SO_LRTYPE,
APPS_VER,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
UPPER(INSTANCE_CODE), /* Bug 2129155 */
INSTANCE_TYPE, -- OPM
LR_SOURCING_FLAG -- Sourcing Flag
INTO lv_last_refresh_type,
lv_so_last_refresh_type,
v_apps_ver,
START_TIME,
v_current_date,
v_current_user,
v_current_date,
v_current_user,
v_instance_code,
v_instance_type, -- OPM
v_sourcing_flag -- Sourcing Flag
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pINSTANCE_ID;
SELECT mai.ST_STATUS
INTO lv_staging_table_status
FROM MSC_APPS_INSTANCES mai
WHERE mai.INSTANCE_ID= pINSTANCE_ID
FOR UPDATE;
UPDATE MSC_APPS_INSTANCES
SET ST_STATUS= MSC_UTIL.G_ST_COLLECTING,
LCID= MSC_COLLECTION_S.NEXTVAL,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE INSTANCE_ID= pINSTANCE_ID;
SELECT MSC_COLLECTION_S.CURRVAL
INTO v_last_collection_id
FROM DUAL;
SELECT mai.ST_STATUS
INTO lv_staging_table_status
FROM MSC_APPS_INSTANCES mai
WHERE mai.INSTANCE_ID= pINSTANCE_ID
FOR UPDATE;
UPDATE MSC_APPS_INSTANCES
SET ST_STATUS= MSC_UTIL.G_ST_PURGING,
SO_TBL_STATUS= MSC_UTIL.SYS_YES,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE INSTANCE_ID= pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES
SET ST_STATUS= MSC_UTIL.G_ST_EMPTY,
SO_TBL_STATUS= MSC_UTIL.SYS_YES,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE INSTANCE_ID= pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES
SET ST_STATUS= MSC_UTIL.G_ST_READY,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE INSTANCE_ID= pINSTANCE_ID;
UPDATE MSC_APPS_INSTANCES mai
SET LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
SUPPLIES_LOAD_FLAG = null,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
PROCEDURE DELETE_MSC_TABLE( p_table_name IN VARCHAR2,
p_instance_id IN NUMBER,
p_plan_id IN NUMBER:= NULL,
p_sub_str IN VARCHAR2:= NULL) IS
-- lv_cnt NUMBER;
FND_MESSAGE.SET_TOKEN('PROCEDURE', 'DELETE_MSC_TABLE:'||p_table_name);
/* lv_sql_stmt:= 'SELECT COUNT(*)'
||' FROM '||p_table_name
||' WHERE SR_INSTANCE_ID= :p_instance_id '
|| p_sub_str;
lv_sql_stmt:= 'DELETE '||p_table_name
||' WHERE SR_INSTANCE_ID= :p_instance_id '
|| p_sub_str;
lv_sql_stmt:= 'DELETE '||p_table_name
||' WHERE SR_INSTANCE_ID= :p_instance_id '
|| p_sub_str
||' AND ROWNUM < :lv_pbs';
/* lv_sql_stmt:= 'SELECT COUNT(*)'
||' FROM '||p_table_name
||' WHERE SR_INSTANCE_ID= :p_instance_id'
||' AND PLAN_ID= -1 '
|| p_sub_str;
lv_sql_stmt:= 'DELETE '||p_table_name
||' WHERE SR_INSTANCE_ID= :lv_instance_id'
||' AND PLAN_ID= -1 '
|| p_sub_str;
lv_sql_stmt:= 'DELETE '||p_table_name
||' WHERE SR_INSTANCE_ID= :p_instance_id '
||' AND PLAN_ID= -1 '
|| p_sub_str
||' AND ROWNUM < :lv_pbs';
END DELETE_MSC_TABLE;
/* to delete
PROCEDURE LOAD_NET_RESOURCE_AVAIL;
/* to delete
PROCEDURE LOAD_ITEM;
/* to delete
PROCEDURE LOAD_RES_INST_REQ;
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = G_JOB_NOT_DONE
where instance_id = v_instance_id;
DELETE_MSC_TABLE( 'MSC_RESOURCE_REQUIREMENTS', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_DEMANDS', v_instance_id, -1 );
DELETE_MSC_TABLE( 'MSC_RESOURCE_REQUIREMENTS', v_instance_id, -1,v_sub_str);
DELETE_MSC_TABLE( 'MSC_DEMANDS', v_instance_id, -1 ,v_sub_str);
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = G_JOB_DONE
where instance_id = v_instance_id;
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = G_JOB_NOT_DONE
where instance_id = v_instance_id;
update msc_apps_instances
set SUPPLIES_LOAD_FLAG = G_JOB_DONE
where instance_id = v_instance_id;
SELECT APPS_VER,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
INTO v_apps_ver,
v_current_date,
v_current_user,
v_current_date,
v_current_user
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= pINSTANCE_ID;
select lcid
INTO v_last_collection_id
from msc_apps_instances
where instance_id = pINSTANCE_ID;
select (SYSDATE- START_TIME) into lv_process_time from dual;
pPURGE_SH IN NUMBER)--to delete the Sourcing History, default is no
IS
lc_i PLS_INTEGER;
lv_delete_flag NUMBER;
SELECT 1
FROM ALL_SOURCE
WHERE NAME = 'MSC_POST_PRO'
AND TYPE = 'PACKAGE BODY'
AND OWNER = lv_apps_schema
AND ROWNUM<2;
Select decode(nra,2,2,1) into lv_pRECALC_NRA_NEW from msc_coll_parameters
where instance_id = pINSTANCE_ID;
select oracle_username
into lv_apps_schema
from fnd_oracle_userid where
read_only_flag = lv_read_only_flag;
select po, sales_order
into lv_po_flag, lv_sales_order_flag
from msc_coll_parameters
where instance_id=pINSTANCE_ID;
SELECT MSC_UTIL.SYS_YES
INTO lv_collection_plan_exists
FROM MSC_PLANS
WHERE PLAN_ID= -1;
INSERT INTO MSC_PLANS
( PLAN_ID,
COMPILE_DESIGNATOR,
SR_INSTANCE_ID,
CURR_APPEND_PLANNED_ORDERS,
CURR_CUTOFF_DATE,
CURR_DEMAND_TIME_FENCE_FLAG,
CURR_OPERATION_SCHEDULE_TYPE,
CURR_OVERWRITE_OPTION,
CURR_PLANNING_TIME_FENCE_FLAG,
CURR_PLAN_TYPE,
CURR_START_DATE,
DAILY_CUTOFF_BUCKET,
DAILY_ITEM_AGGREGATION_LEVEL,
DAILY_MATERIAL_CONSTRAINTS,
DAILY_RESOURCE_CONSTRAINTS,
DAILY_RES_AGGREGATION_LEVEL,
ORGANIZATION_ID,
WEEKLY_CUTOFF_BUCKET,
WEEKLY_ITEM_AGGREGATION_LEVEL,
WEEKLY_MATERIAL_CONSTRAINTS,
WEEKLY_RESOURCE_CONSTRAINTS,
WEEKLY_RES_AGGREGATION_LEVEL,
OPTIMIZE_FLAG,
SCHEDULE_FLAG,
CURR_ENFORCE_DEM_DUE_DATES,
CURR_PLANNED_RESOURCES,
DAILY_RTG_AGGREGATION_LEVEL,
WEEKLY_RTG_AGGREGATION_LEVEL,
PERIOD_CUTOFF_BUCKET,
PERIOD_MATERIAL_CONSTRAINTS,
PERIOD_RESOURCE_CONSTRAINTS,
PERIOD_ITEM_AGGREGATION_LEVEL,
PERIOD_RES_AGGREGATION_LEVEL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY )
VALUES
( -1,
'Collection',
v_instance_id,
1,
v_current_date,
1,
1,
1,
1,
1,
v_current_date,
1,
1,
1,
1,
1,
-1,
1,
1,
1,
1,
1,
1,
1,
1,
1,
1,
1,
1,
1,
1,
1,
1,
v_current_date,
v_current_user,
v_current_date,
v_current_user);
update msc_coll_parameters
set SOURCING_HISTORY = pRECALC_SH
where instance_id = pINSTANCE_ID;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'update so_tbl status to no');
UPDATE MSC_APPS_INSTANCES mai
SET
so_tbl_status= MSC_UTIL.SYS_NO,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1,v_sub_str);
DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1,v_sub_str);
UPDATE MSC_APPS_INSTANCES mai
SET
so_tbl_status= MSC_UTIL.SYS_YES,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'update so tbl status');
select (SYSDATE- START_TIME) into lv_process_time from dual;
UPDATE MSC_APPS_INSTANCES mai
SET LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
SELECT (SYSDATE- lv_start_time) INTO lv_process_time FROM dual;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'update of msc_supplies top_transaction_id for eam is successful.....');
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error in update of msc_supplies top_transaction_id......');
MSC_CL_ITEM_ODS_LOAD.UPDATE_LEADTIME;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_YES
WHERE instance_id= v_instance_id;
DELETE_MSC_TABLE('MSC_PO_RECEIPTS', v_instance_id,NULL,v_sub_str);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Table MSC_PO_RECEIPTS Deleted');
SELECT DECODE(nvl(FND_PROFILE.VALUE('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
INTO lv_delete_flag
FROM DUAL;
IF (lv_delete_flag = 2) THEN
DELETE MSC_SOURCING_HISTORY
WHERE SR_INSTANCE_ID = v_instance_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Table MSC_SOURCING_HISTORY Deleted');
DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1,MSC_UTIL.v_in_org_str);
DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1,MSC_UTIL.v_in_org_str);
SELECT DECODE(M2A_DBLINK,
NULL,'',
'@'||M2A_DBLINK)
INTO lv_dblink
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID=v_instance_id;
SELECT nvl(fnd_profile.value('INV_CTP'),-10)
INTO lv_inv_ctp
FROM dual;
SELECT decode(nvl(fnd_profile.value('MSC_ENABLE_ATP_SUMMARY'),'N'),'Y',1,2)
INTO lv_MSC_ENABLE_ATP_SUMMARY
FROM dual;
PROCEDURE DELETE_PROCESS(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
pINSTANCE_ID IN NUMBER)
IS
BEGIN
INITIALIZE_LOAD_GLOBALS( pINSTANCE_ID);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'DELETER:FAIL TO SEND MESSAGE!');
END DELETE_PROCESS;
pPURGE_SH IN NUMBER) IS --to delete Sourcing History
lc_i PLS_INTEGER;
lv_delete_flag NUMBER;
SELECT 1
FROM ALL_SOURCE
WHERE NAME = 'MSC_POST_PRO'
AND TYPE = 'PACKAGE BODY'
AND OWNER = lv_apps_schema
AND ROWNUM<2;
select oracle_username
into lv_apps_schema
from fnd_oracle_userid where
read_only_flag = lv_read_only_flag;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'update so_tbl status to no');
UPDATE MSC_APPS_INSTANCES mai
SET
so_tbl_status= MSC_UTIL.SYS_NO,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
UPDATE MSC_APPS_INSTANCES mai
SET
so_tbl_status= MSC_UTIL.SYS_YES,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1);
DELETE_MSC_TABLE( 'MSC_NET_RESOURCE_AVAIL', v_instance_id, -1,v_sub_str);
DELETE_MSC_TABLE( 'MSC_NET_RES_INST_AVAIL', v_instance_id, -1,v_sub_str);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'update so_tbl status to no');
UPDATE MSC_APPS_INSTANCES mai
SET
so_tbl_status= MSC_UTIL.SYS_NO,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
UPDATE MSC_APPS_INSTANCES mai
SET
so_tbl_status= MSC_UTIL.SYS_YES,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'update so tbl status');
UPDATE MSC_APPS_INSTANCES mai
SET
so_tbl_status= MSC_UTIL.SYS_YES,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'update so tbl status');
UPDATE MSC_APPS_INSTANCES mai
SET
so_tbl_status= MSC_UTIL.SYS_YES,
LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'update so tbl status');
select (SYSDATE- START_TIME) into lv_process_time from dual;
UPDATE MSC_APPS_INSTANCES mai
SET LAST_UPDATE_DATE= v_current_date,
LAST_UPDATED_BY= v_current_user,
REQUEST_ID= FND_GLOBAL.CONC_REQUEST_ID
WHERE mai.INSTANCE_ID= v_instance_id;
SELECT (SYSDATE- lv_start_time) INTO lv_process_time FROM dual;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'update of msc_supplies top_transaction_id for eam is successful.....');
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Error in update of msc_supplies top_transaction_id......');
MSC_CL_ITEM_ODS_LOAD.UPDATE_LEADTIME;
UPDATE MSC_APPS_INSTANCES
SET so_tbl_status= MSC_UTIL.SYS_YES
WHERE instance_id= v_instance_id;
DELETE_MSC_TABLE('MSC_PO_RECEIPTS', v_instance_id,NULL,v_sub_str);
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Table MSC_PO_RECEIPTS Deleted');
SELECT DECODE(nvl(FND_PROFILE.VALUE('MSC_PURGE_ST_CONTROL'),'N'),'Y',1,2)
INTO lv_delete_flag
FROM DUAL;
IF (lv_delete_flag = 2) THEN
DELETE MSC_SOURCING_HISTORY
WHERE SR_INSTANCE_ID= v_instance_id;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Table MSC_SOURCING_HISTORY Deleted');
SELECT nvl(fnd_profile.value('INV_CTP'),-10)
INTO lv_inv_ctp
FROM dual;
SELECT decode(nvl(fnd_profile.value('MSC_ENABLE_ATP_SUMMARY'),'N'),'Y',1,2)
INTO lv_MSC_ENABLE_ATP_SUMMARY
FROM dual;
This conc program updates the Msc_trading_partners table with the Modeleed Supplier info */
PROCEDURE ENTER_MODELLED_INFO( ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
pINSTANCE_ID IN NUMBER,
pDEST_PARTNER_ORG_ID IN NUMBER,
pSUPPLIER_ID IN NUMBER,
pSUPPLIER_SITE_ID IN NUMBER,
pACCEPT_DEMANDS_FROM_UNMET_PO IN NUMBER)
IS
BEGIN
IF ( (pSUPPLIER_ID IS NULL) AND (pSUPPLIER_SITE_ID IS NULL) ) THEN
UPDATE msc_trading_partners
SET MODELED_SUPPLIER_ID = null,
MODELED_SUPPLIER_SITE_ID = null,
ORG_SUPPLIER_MAPPED = 'N',
ACCEPT_DEMANDS_FROM_UNMET_PO = null
WHERE sr_instance_id = pINSTANCE_ID
AND PARTNER_ID = pDEST_PARTNER_ORG_ID
AND PARTNER_TYPE = 3;
UPDATE msc_trading_partners
SET MODELED_SUPPLIER_ID = pSUPPLIER_ID,
MODELED_SUPPLIER_SITE_ID = pSUPPLIER_SITE_ID,
ORG_SUPPLIER_MAPPED = 'Y',
ACCEPT_DEMANDS_FROM_UNMET_PO = pACCEPT_DEMANDS_FROM_UNMET_PO
WHERE sr_instance_id = pINSTANCE_ID
AND PARTNER_ID = pDEST_PARTNER_ORG_ID
AND PARTNER_TYPE = 3;
MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Updated the Partner Id : '||pDEST_PARTNER_ORG_ID );
SELECT instance_id
INTO lv_instance_id
FROM msc_apps_instances
WHERE upper(instance_code) = p_instance_code;