[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY PLS_INTEGER,
CREATION_DATE DATE,
CREATED_BY PLS_INTEGER,
LAST_UPDATE_LOGIN PLS_INTEGER,
REQUEST_ID PLS_INTEGER,
PROGRAM_APPLICATION_ID PLS_INTEGER,
PROGRAM_ID PLS_INTEGER,
PROGRAM_UPDATE_DATE DATE,
ATTRIBUTE_CATEGORY VARCHAR2(30),
ATTRIBUTE1 VARCHAR2(150),
ATTRIBUTE2 VARCHAR2(150),
ATTRIBUTE3 VARCHAR2(150),
ATTRIBUTE4 VARCHAR2(150),
ATTRIBUTE5 VARCHAR2(150),
ATTRIBUTE6 VARCHAR2(150),
ATTRIBUTE7 VARCHAR2(150),
ATTRIBUTE8 VARCHAR2(150),
ATTRIBUTE9 VARCHAR2(150),
ATTRIBUTE10 VARCHAR2(150),
ATTRIBUTE11 VARCHAR2(150),
ATTRIBUTE12 VARCHAR2(150),
ATTRIBUTE13 VARCHAR2(150),
ATTRIBUTE14 VARCHAR2(150),
ATTRIBUTE15 VARCHAR2(150),
DELETED_FLAG PLS_INTEGER,
REFRESH_ID PLS_INTEGER,
SR_INSTANCE_CODE VARCHAR2(3),
MESSAGE_ID PLS_INTEGER,
PROCESS_FLAG PLS_INTEGER,
DATA_SOURCE_TYPE VARCHAR2(15),
ST_TRANSACTION_ID PLS_INTEGER,
ERROR_TEXT VARCHAR2(2000),
BATCH_ID PLS_INTEGER,
COMPANY_NAME VARCHAR2(225)
);
insert_stmt varchar2(8000);
/* Insert_Stmt := 'insert into gmp_collection_messages'
||' (msg_no, '
||' message, '
||' proc_name, '
||' appl_name, '
||' creation_date '
||' )'
||' values '
||' (:p1,:p2,:p3,:p4,:p5)';
EXECUTE IMMEDIATE Insert_Stmt USING g_log_no, Pbuff,proc_name,PRG_EXECUTING,sysdate;
/* Insert_Stmt := 'insert into gmp_collection_messages'
||' (msg_no, '
||' message, '
||' proc_name, '
||' appl_name, '
||' creation_date '
||' )'
||' values '
||' (:p1,:p2,:p3,:p4,:p5)';
EXECUTE IMMEDIATE Insert_Stmt USING g_log_no, Pbuff,proc_name,PRG_EXECUTING,sysdate;
REM| 10/13/99 - Added deleted_flag in the insert statement |
REM| 11/23/99 - Changed value of aggregate_resource_flag from 1 to 2 |
REM| 01/12/00 - Added owning_department_id column in the Insert statement |
REM| - Bug# 1140113 |
REM| 4/03/00 - using mtl_organization_id from ic_whse_mst instead of |
REM| - organization_id from mtl_parameters - Bug# 1252322 |
REM| 4/18/00 - Fixed Bug# 1273557 - Department count is Zero |
REM| - Changes made to the insert statement, changed |
REM| - s.organization_id to w.mtl_organization_id |
REM| 12/26/01 - Adding Code changes for Resource Utilization and Resource |
REM| Efficiency - B2163006 |
REM| 12/20/02 - Sridhar Gidugu B2714583, Populated 3 new columns for |
REM| msc_st_department_resources |
REM| 1.Resource_excess_type, |
REM| 2.Resource_shortage_type |
REM| 3.User_time_fence |
REM| 01/09/03 - Sridhar Gidugu Used mrp_planning_exception_sets |
REM| instead of mrp_planning_exception_sets_v |
REM| also added extra join with Organization_id |
REM| 01/22/03 - Sridhar Gidugu Insert statement for Resource Groups |
REM| 05/11/03 - Rajesh Patangya Used to_number(NULL) in palce of NULL |
REM| 05/20/03 - Sridhar Gidugu B2971120 Populating new columns |
REM| Over_utilized_percent and |
REM| under_utilized_percent in dept_rsc table |
REM| 04/21/2004 - Navin Sinha - B3577871 -ST:OSFME2: collections failing |
REM| in planning data pull. |
REM| Added handling of NO_DATA_FOUND Exception.|
REM| And return the return_status as TRUE. |
REM+==========================================================================+
*/
PROCEDURE rsrc_extract(p_instance_id IN PLS_INTEGER,
p_db_link IN VARCHAR2,
return_status OUT NOCOPY BOOLEAN) IS
ins_dept_res varchar2(25000);
ins_dept_res := ' INSERT INTO msc_st_department_resources '
|| ' ( organization_id, '
|| ' sr_instance_id, '
|| ' resource_id, '
|| ' department_id, '
|| ' resource_code, '
|| ' resource_description, '
|| ' department_code, '
|| ' owning_department_id, '
|| ' line_flag, '
|| ' aggregated_resource_flag, '
|| ' capacity_units, '
|| ' available_24_hours_flag, '
|| ' resource_cost, '
|| ' ctp_flag, '
|| ' deleted_flag, '
|| ' resource_excess_type, '
|| ' resource_shortage_type, '
|| ' user_time_fence, '
|| ' over_utilized_percent, ' /* B2971120 */
|| ' under_utilized_percent, ' /* B2971120 */
|| ' efficiency, '
|| ' utilization, '
|| ' planning_exception_set, '
|| ' resource_group_name, '
|| ' bottleneck_flag, '
|| ' chargeable_flag, '
|| ' capacity_tolerance, '
|| ' batchable_flag, '
|| ' batching_window, '
|| ' min_capacity, '
|| ' max_capacity, '
|| ' unit_of_measure, '
|| ' idle_time_tolerance, '
|| ' sds_scheduling_window, '
|| ' batching_penalty, '
|| ' schedule_to_instance, '
|| ' resource_type ' /*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
|| ') '
-- Bug # 12797605 VKINDURI, Added hint /*+ ordered use_nl(r m mrp p hr) */ as part of performance issue.
-- || ' SELECT p.organization_id , '
|| ' SELECT /*+ ordered use_nl(r m mrp p hr) */ p.organization_id , '
|| ' :instance_id, '
|| ' ((r.resource_id * 2) + 1),' /* B1177070 encoded */
|| ' ((p.organization_id * 2) + 1) department_id,' /* B1177070 encoded */
|| ' r.resources , '
|| ' m.resource_desc, ' /*Sowmya-Changed from resources to resource_desc*/
|| ' p.organization_code , '
|| ' ((p.organization_id * 2) + 1) , ' /* B1177070 */
|| ' 2, ' /* Line Flag */
|| ' 2, ' /* Yes = 1 and No = 2 resource Flag */
|| ' r.assigned_qty, '
|| ' 2, ' /* Avail 24 hrs flag */
|| ' r.nominal_cost, '
|| ' 1,' /* for ATP to check Resources (RDP)*/
|| ' 2, '
|| ' mrp.resource_excess_type, ' /* B2714583 */
|| ' mrp.resource_shortage_type, ' /* B2714583 */
|| ' mrp.user_time_fence, ' /* B2714583 */
|| ' mrp.over_utilized_percent, ' /* B2971120 */
|| ' mrp.under_utilized_percent, ' /* B2971120 */
|| ' r.efficiency, ' /* B2163006 */
|| ' r.utilization, ' /* B2163006 */
|| ' r.planning_exception_set, ' /* B2714583 */
|| ' r.group_resource, '
|| ' NULL, '
|| ' decode(r.capacity_constraint,1,1,2), '
|| ' r.capacity_tolerance, '
|| ' r.batchable_flag, ' /* B4157063 Resource Batching */
|| ' r.batch_window, ' /* B4157063 Resource Batching */
|| ' r.min_capacity, '
|| ' r.max_capacity, '
|| ' r.capacity_um, '
|| ' r.idle_time_tolerence, '
|| ' r.sds_window, '
|| ' NULL, '
/* If the Resource is scheduled to Instance, then value is Yes else No */
|| ' decode(r.schedule_ind,2,1,2), '
|| ' 1 '/*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
/*sowsubra - ME changes - Start*/
/* Replace the use of sy_orgn_mst with mtl_parameters and hr_organization_units */
|| ' FROM cr_rsrc_dtl'||p_db_link||' r, '
|| ' cr_rsrc_mst'||p_db_link||' m, '
|| ' mrp_planning_exception_sets'||p_db_link||' mrp, '
|| ' mtl_parameters'||p_db_link||' p, '
|| ' hr_organization_units'||p_db_link||' hr '
|| ' WHERE r.organization_id = p.organization_id '
|| ' AND r.resources = m.resources '
|| ' AND p.organization_id = hr.organization_id '
|| ' AND nvl(hr.date_to,sysdate) >= sysdate '
|| ' AND r.planning_exception_set = mrp.exception_set_name '
|| ' AND p.organization_id = mrp.organization_id '
|| ' AND p.process_enabled_flag = '||''''||'Y'||'''' ;
|| ' AND r.delete_mark = 0 '
|| ' UNION ALL '
|| ' SELECT p.organization_id , '
|| ' :instance_id1, '
|| ' ((r.resource_id * 2) + 1),' /* B1177070 encoded */
|| ' ((p.organization_id * 2) + 1),' /* B1177070 encoded */
|| ' r.resources ,'
|| ' m.resource_desc, ' /*Sowmya-Changed from resources to resource_desc*/
|| ' p.organization_code , '
|| ' ((p.organization_id * 2) + 1) , ' /* B1177070 */
|| ' 2, ' /* Line Flag */
|| ' 2, ' /* Yes = 1 and No = 2 resource Flag */
|| ' r.assigned_qty, '
|| ' 2, ' /* Avail 24 hrs flag */
|| ' r.nominal_cost, '
|| ' 1,' /* for ATP to check Resources (RDP)*/
|| ' 2, '
|| ' to_number(NULL), ' /* B2714583 */
|| ' to_number(NULL), ' /* B2714583 */
|| ' to_number(NULL), ' /* B2714583 */
|| ' to_number(NULL), ' /* B2971120 */
|| ' to_number(NULL), ' /* B2971120 */
|| ' r.efficiency, ' /* B2163006 */
|| ' r.utilization, ' /* B2163006 */
|| ' r.planning_exception_set, ' /* B2714583 */
|| ' r.group_resource, '
|| ' NULL, '
|| ' decode(r.capacity_constraint,1,1,2), '
|| ' r.capacity_tolerance, '
|| ' r.batchable_flag, ' /* B4157063 Resource Batching */
|| ' r.batch_window, ' /* B4157063 Resource Batching */
|| ' r.min_capacity, '
|| ' r.max_capacity, '
|| ' r.capacity_um, '
|| ' r.idle_time_tolerence, '
|| ' r.sds_window, '
|| ' NULL, '
/* If the Resource is scheduled to Instance, then value is Yes else No */
|| ' decode(r.schedule_ind,2,1,2), '
|| ' 1 ' /*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
|| ' FROM cr_rsrc_dtl'||p_db_link||' r, '
/*sowsubra - ME changes - Start*/
/* Replace the use of sy_orgn_mst with mtl_parameters and hr_organization_units */
|| ' mtl_parameters'||p_db_link||' p, '
|| ' cr_rsrc_mst'||p_db_link||' m, '
|| ' hr_organization_units'||p_db_link||' hr '
|| ' where r.organization_id = p.organization_id '
|| ' AND r.resources = m.resources '
|| ' AND r.planning_exception_set IS NULL '
|| ' AND p.organization_id = hr.organization_id '
|| ' AND nvl(hr.date_to,sysdate) >= sysdate '
|| ' AND p.process_enabled_flag = '||''''||'Y'||''''
|| ' AND r.delete_mark = 0 ' ;
/* Insert into MSC_ST_RESOURCE_GROUPS for Bottleneck Resources
Sending only those resources that are used in Planning for APS
*/
-- Commented for BUG 14619874 VKINDURI Start
/*ins_res_group := ' INSERT INTO msc_st_resource_groups '
|| ' ( group_code, '
|| ' meaning, '
|| ' description, '
|| ' from_date, '
|| ' to_date, '
|| ' enabled_flag, '
|| ' sr_instance_id '
|| ' ) '
ins_res_group := ' SELECT distinct crd.group_resource , '
|| ' substr(crm.resource_desc,1,80),'--bug14480509 posco field widen project. michael
|| ' substr(crm.resource_desc,1,80),'--bug14480509 posco field widen project. michael
|| ' sysdate,'
|| ' NULL,'
|| ' 1,'
|| ' :instance_id '
|| ' FROM mtl_parameters'||p_db_link||' p, '
|| ' hr_organization_units'||p_db_link||' hr, '
|| ' cr_rsrc_dtl'||p_db_link||' crd, '
|| ' cr_rsrc_mst'||p_db_link||' crm '
|| ' WHERE p.organization_id = crd.organization_id '
|| ' AND p.organization_id = hr.organization_id '
|| ' AND nvl(hr.date_to,sysdate) >= sysdate '
|| ' AND p.process_enabled_flag = '||''''||'Y'||''''
|| ' AND crd.resources = crm.resources '
|| ' AND crd.group_resource = crm.resources '
|| ' AND crd.delete_mark = 0 ';
||' AND EXISTS ( SELECT 1 FROM mtl_system_items'||p_db_link||' msi'
||' WHERE msi.organization_id = p.organization_id )' ;
ins_res_group := ' SELECT DISTINCT'
|| ' crd.group_resource GROUP_CODE,'
|| ' substr(crm.resource_desc,1,80) MEANING,'--bug14480509 posco field widen project. michael
|| ' substr(crm.resource_desc,1,80) DESCRIPTION,'--bug14480509 posco field widen project. michael
|| ' SYSDATE FROM_DATE,'
|| ' NULL TO_DATE,'
|| ' 1 ENABLED_FLAG,'
|| ' :instance_id SR_INSTANCE_ID,'
|| ' NULL LAST_UPDATE_DATE,'
|| ' NULL LAST_UPDATED_BY,'
|| ' NULL CREATION_DATE,'
|| ' NULL CREATED_BY,'
|| ' NULL LAST_UPDATE_LOGIN,'
|| ' NULL REQUEST_ID,'
|| ' NULL PROGRAM_APPLICATION_ID,'
|| ' NULL PROGRAM_ID,'
|| ' NULL PROGRAM_UPDATE_DATE,'
|| ' NULL ATTRIBUTE_CATEGORY,'
|| ' NULL ATTRIBUTE1,'
|| ' NULL ATTRIBUTE2,'
|| ' NULL ATTRIBUTE3,'
|| ' NULL ATTRIBUTE4,'
|| ' NULL ATTRIBUTE5,'
|| ' NULL ATTRIBUTE6,'
|| ' NULL ATTRIBUTE7,'
|| ' NULL ATTRIBUTE8,'
|| ' NULL ATTRIBUTE9,'
|| ' NULL ATTRIBUTE10,'
|| ' NULL ATTRIBUTE11,'
|| ' NULL ATTRIBUTE12,'
|| ' NULL ATTRIBUTE13,'
|| ' NULL ATTRIBUTE14,'
|| ' NULL ATTRIBUTE15,'
|| ' NULL DELETED_FLAG,'
|| ' NULL REFRESH_ID,'
|| ' NULL SR_INSTANCE_CODE,'
|| ' NULL MESSAGE_ID,'
|| ' NULL PROCESS_FLAG,'
|| ' NULL DATA_SOURCE_TYPE,'
|| ' NULL ST_TRANSACTION_ID,'
|| ' NULL ERROR_TEXT,'
|| ' NULL BATCH_ID,'
|| ' NULL COMPANY_NAME'
|| ' FROM mtl_parameters'||p_db_link||' p, '
|| ' hr_organization_units'||p_db_link||' hr, '
|| ' cr_rsrc_dtl'||p_db_link||' crd, '
|| ' cr_rsrc_mst'||p_db_link||' crm '
|| ' WHERE p.organization_id = crd.organization_id '
|| ' AND p.organization_id = hr.organization_id '
|| ' AND nvl(hr.date_to,sysdate) >= sysdate '
|| ' AND p.process_enabled_flag = '||''''||'Y'||''''
|| ' AND crd.resources = crm.resources '
|| ' AND crd.group_resource = crm.resources '
|| ' AND crd.delete_mark = 0 ';
||' AND EXISTS ( SELECT 1 FROM mtl_system_items'||p_db_link||' msi'
||' WHERE msi.organization_id = p.organization_id )' ;
INSERT INTO msc_st_resource_groups
VALUES rsrc_groups_tab(i);
ins_res_instance := ' INSERT INTO msc_st_dept_res_instances '
||' ( sr_instance_id, '
||' res_instance_id, '
||' resource_id, '
||' department_id, '
||' organization_id, '
||' serial_number, '
||' equipment_item_id, '
||' last_known_setup, '
||' effective_start_date, '
||' effective_end_date, '
||' deleted_flag '
||' ) '
||' SELECT :instance_id, '
||' ((gri.instance_id * 2) + 1), '
||' ((gri.resource_id * 2) + 1) x_resource_id, '
||' ((m.organization_id * 2) + 1) department_id, ' /* encoded */
||' m.organization_id, '
||' NVL(gri.eqp_serial_number, to_char(gri.instance_number)), '
||' gri.equipment_item_id, '
||' gri.last_setup_id, ' -- Conc Prog routine will populate this
||' gri.eff_start_date, '
||' gri.eff_end_date, '
||' 2 '
||' FROM '
||' gmp_resource_instances'||p_db_link||' gri, '
||' cr_rsrc_dtl'||p_db_link||' c, '
||' hr_organization_units'||p_db_link||' hr, '
||' mtl_parameters'||p_db_link||' m '
||' WHERE '
||' gri.resource_id = c.resource_id '
|| ' AND m.organization_id = hr.organization_id '
|| ' AND nvl(hr.date_to,sysdate) >= sysdate '
||' AND c.schedule_ind = 2 '
||' AND c.organization_id = m.organization_id '
||' AND m.process_enabled_flag = '||''''||'Y'||''''
||' AND gri.inactive_ind = 0 '
||' AND c.delete_mark = 0 ';
log_message('Error in department/Res Group Insert: '||p_instance_id);
REM| Update trading Partners and net_rsrc_insert procedure. |
REM| |
REM| 9/7/99 - Changed the Main Procedure, removed UNION ALL for main cursor|
REM| 9/28/99 - Changed the main query ordering by Organization Id and |
REM| - changed logic for populating plsqltbl |
REM| 4/03/00 - using mtl_organization_id from ic_whse_mst instead of |
REM| - organization_id from mtl_parameters - Bug# 1252322 |
REM| 5/03/00 - Add instance code as a prefix to the calendar code |
REM| - Bug # 1288143 |
REM| 7/07/00 - Anchor Date Problem Fixed in the Calendar Code |
REM| - Bug # 1337084. |
REM| 7/12/00 - Removed the Debugging Statement shcl.calendar_id in |
REM| - (121,126) - bug#1353845 |
REM| 10/18/01 - B2041247 - Modified the cursor to consider Calendars |
REM| associated with the OPM Plants |
REM| |
REM| 7th Mar 2003 -- Performance issue fix and B2671540 00:00 shift fix |
REM| 04/21/2004 - Navin Sinha - B3577871 -ST:OSFME2: collections failing |
REM| in planning data pull. |
REM| Added handling of NO_DATA_FOUND Exception.|
REM| And return the return_status as TRUE. |
REM| |
REM| 07-May-2004 - Sowmya - B3599089 - ST: ORG SPECIFIC COMPLETE COLLETION |
REM| FOR OPM ORGS TAKING MORE TIME. |
REM| As the varaibale l_org_specific was not getting |
REM| refreshed,the resource availability |
REM| was getting collected irrespective of whether or|
REM| not the org is enabled. To overcome this, added |
REM| if clause containing the l_cur%NOTFOUND.So when |
REM| the no values are returned the l_org_specific= 0|
REM| |
REM+==========================================================================+
*/
PROCEDURE populate_rsrc_cal(p_run_date IN date,
p_instance_id IN PLS_INTEGER,
p_delimiter IN varchar2,
p_db_link IN varchar2,
p_nra_enabled IN NUMBER,
return_status OUT NOCOPY BOOLEAN) IS
union_cal_ref ref_cursor_typ;
delete_msg varchar2(500);
/* delete_msg := 'DELETE FROM gmp_collection_messages'||p_db_link ;
EXECUTE IMMEDIATE delete_msg ;
select name into DEST_DBNAME from V$database ;
inst_stmt := ' SELECT instance_code '
|| ' FROM msc_apps_instances'
|| ' WHERE instance_id = :instance_id ';
/* Select All the calendars which have calendar_code associated with
resources and if not associated then organization calendar_code */
stmt_no := 15;
|| ' SELECT unique cal.calendar_code, '
|| ' cal.description, '
|| ' mp.organization_code, '
|| ' mp.organization_id organization_id, '
|| ' 0 '
|| ' FROM bom_calendars'||p_db_link||' cal, '
|| ' hr_organization_units'||p_db_link||' hr, '
|| ' mtl_parameters'||p_db_link||' mp, '
|| ' cr_rsrc_dtl'||p_db_link||' crd '
|| ' WHERE mp.organization_id = hr.organization_id '
|| ' AND nvl(hr.date_to,sysdate) >= sysdate '
|| ' AND mp.process_enabled_flag = '||''''||'Y'||''''
|| ' AND crd.organization_id = mp.organization_id '
|| ' AND NVL(crd.calendar_code,mp.calendar_code)=cal.calendar_code'
|| ' AND crd.delete_mark = 0 ' ;
update_trading_partners(cursor_rec.organization_id,
l_calendar_no,
return_status
);
/* REGENERATE THE CALENDAR AND THEN INSERT IT INTO MSC TABLE */
res_passed := 'DONOT_RETRIEVE_CALENDAR' ;
log_message('REGENERATE THE CALENDAR insert_gmp_resource_avail call = ' || p_db_link );
' BEGIN apps.gmp_calendar_pkg.insert_gmp_resource_avail'||p_db_link||'(:p1, :p2, :p3, :p4, :p5, :p6); END ;' ;
log_message('After insert_gmp_resource_avail call = ' || v_errbuf || ' REG '|| v_retcode );
sql_stmt := 'select message from gmp_collection_messages'||p_db_link ;
Upd_Process_Org := 'UPDATE MSC_ST_TRADING_PARTNERS'
||' SET organization_type = 2'
||' WHERE sr_tp_id in (SELECT organization_id '
||' FROM mtl_parameters'||p_db_link
||' WHERE process_enabled_flag = '||''''||'Y'||'''' || ')'
||' AND partner_type = 3' ;
log_message('Trading Partner Update is Done' );
log_message('Error in UPDATE TRADING_PARTNERS '||stmt_no);
/* ======================= Staging table Inserts ===================*/
log_message('INSERT IT INTO msc_st_net_resource_avail ');
ins_res_avl := ' INSERT INTO msc_st_net_resource_avail '
|| ' ( organization_id, '
|| ' sr_instance_id, '
|| ' resource_id, '
|| ' department_id, '
|| ' simulation_set, '
|| ' shift_num, '
|| ' shift_date, '
|| ' from_time, '
|| ' to_time, '
|| ' capacity_units, '
|| ' deleted_flag '
|| ' ) '
|| ' SELECT '
|| ' gra.organization_id, '
|| ' :instance_id, '
|| ' ((gra.resource_id*2)+1), '
|| ' ((gra.organization_id*2)+1) department_id, ' /* encoded */
|| ' :simulation_set, '
|| ' gra.shift_num, '
|| ' gra.shift_date, '
|| ' gra.from_time, '
|| ' gra.to_time, '
|| ' gra.resource_units, '
|| ' 2 '
|| ' FROM '
|| ' gmp_resource_avail'||p_db_link||' gra, '
|| ' mtl_parameters'||p_db_link||' m, '
|| ' cr_rsrc_dtl'||p_db_link||' crd '
|| ' WHERE nvl(gra.resource_instance_id,0) = 0 '
|| ' AND nvl(crd.calendar_code,m.calendar_code) = gra.calendar_code '
|| ' AND gra.organization_id = crd.organization_id '
|| ' AND gra.organization_id = m.organization_id '
|| ' AND gra.resource_id = crd.resource_id '
|| ' AND m.process_enabled_flag = '||''''||'Y'||''''
|| ' AND crd.delete_mark = 0 ';
log_message('msc_st_net_resource_avail Insertion Is Done' );
log_message('INSERT IT INTO msc_st_net_res_inst_avail ');
inst_resavl := ' INSERT INTO msc_st_net_res_inst_avail '
|| ' ( res_instance_id, '
|| ' serial_number, '
|| ' equipment_item_id, '
|| ' Organization_Id, '
|| ' Resource_Id, '
|| ' Shift_Num, '
|| ' Shift_Date, '
|| ' From_Time, '
|| ' To_Time, '
|| ' Department_id, '
|| ' sr_instance_id '
|| ' ) '
|| ' SELECT '
|| ' ((gri.instance_id*2)+1), '
|| ' NVL(gri.eqp_serial_number, to_char(gri.instance_number)), '
|| ' gri.equipment_item_id, '
|| ' gra.organization_id, '
|| ' ((gra.resource_id*2)+1), ' /* B4223622 */
|| ' gra.shift_num, '
|| ' gra.shift_date, '
|| ' gra.from_time, '
|| ' gra.to_time, '
|| ' ((gra.organization_id*2)+1) department_id, '/* encoded */
|| ' :instance_id '
|| ' FROM '
|| ' gmp_resource_instances'||p_db_link||' gri, '
|| ' gmp_resource_avail'||p_db_link||' gra, '
|| ' mtl_parameters'||p_db_link||' m, '
|| ' cr_rsrc_dtl'||p_db_link||' crd '
|| ' WHERE gri.resource_id = gra.resource_id '
|| ' AND gri.instance_id = gra.resource_instance_id '
|| ' AND gri.inactive_ind = 0 '
|| ' AND nvl(crd.calendar_code,m.calendar_code) = gra.calendar_code '
|| ' AND gra.organization_id = crd.organization_id '
|| ' AND gra.organization_id = m.organization_id '
|| ' AND gra.resource_id = crd.resource_id '
|| ' AND m.process_enabled_flag = '||''''||'Y'||''''
|| ' AND crd.delete_mark = 0 ';
log_message('msc_st_net_res_inst_avail Insertion Is Done' );
/* Insert for msc_st_resource_shifts Starts here - 2213101 */
-- bug: 8486550 Vpedarla modified the shift_num column insertion for msc_st_resource_shifts
log_message('INSERT IT INTO msc_st_resource_shifts ');
ins_res_shft := ' INSERT INTO msc_st_resource_shifts '
|| ' ( department_id, '
|| ' shift_num, '
|| ' resource_id, '
|| ' deleted_flag, '
|| ' sr_instance_id, '
|| ' capacity_units '
|| ' ) '
|| ' SELECT unique '
|| ' ((m.organization_id*2)+1) , ' /* encoded */
|| ' decode(sign(gtmp.shift_num - 99999),0,0,1,(gtmp.shift_num - 99999),-1 ,gtmp.shift_num), '
|| ' ((crd.resource_id*2)+1), '
|| ' 2, '
|| ' :instance_id, '
|| ' crd.assigned_qty '
|| ' FROM gmp_calendar_detail_gtmp'||p_db_link||' gtmp, '
|| ' mtl_parameters'||p_db_link||' m, '
|| ' cr_rsrc_dtl'||p_db_link||' crd '
|| ' WHERE NVL(crd.calendar_code,m.calendar_code) = gtmp.calendar_code '
|| ' AND m.organization_id = crd.organization_id '
|| ' AND m.process_enabled_flag = '||''''||'Y'||''''
|| ' AND crd.delete_mark = 0 ';
log_message(' msc_st_resource_shifts Insertion Is Done' );
log_message(' msc_st_net_resource_avail Update to 86400' );
upd_res_avl := 'UPDATE msc_st_net_resource_avail '
||' SET to_time = 86400 '
||' WHERE to_time = 86399 '
||' AND shift_num >= 99999 ' ;
log_message(' msc_st_net_resource_avail Update to 86400 IS Done' );
log_message(' msc_st_net_resource_avail Update to shift_num - 99999' );
upd_res_avl := 'UPDATE msc_st_net_resource_avail '
||' SET shift_num = (shift_num - 99999) '
||' WHERE shift_num >= 99999 ' ;
log_message(' msc_st_net_resource_avail Update to shift_num - 99999 is done' );
REM| update_trading_partners |
REM| |
REM| Type |
REM| public |
REM| |
REM| DESCRIPTION |
REM| |
REM| This procedure updates the following table : |
REM| |
REM| 1. msc_st_trading_partners |
REM| |
REM| Input Parameters |
REM| p_org_id - Organization_id |
REM| p_cal_code - Calendar_code |
REM| |
REM| Output Parameters |
REM| None |
REM| |
REM| |
REM| HISTORY |
REM| Created 5th Aug 1999 by Sridhar Gidugu (OPM Development Oracle US) |
REM| 8/30/99 - Removed the existing Trading Partner Procedure and changed |
REM| to a single Update Procedure. |
REM| 10/1/99 - Changed Updating Trading Partners, |
REM| - Updated Organization_typw with a value 2 and changed |
REM| - partner_type = 3 |
REM| |
REM| |
REM+==========================================================================+
*/
PROCEDURE update_trading_partners(p_org_id IN PLS_INTEGER,
p_cal_code IN varchar2,
return_status OUT NOCOPY BOOLEAN) IS
Begin
proc_name := 'update_trading_partners';
UPDATE MSC_ST_TRADING_PARTNERS
-- No need to update calendar code B5926204 Rajesh Patangya
-- SET calendar_code = p_cal_code,
SET organization_type = 2
WHERE sr_tp_id = p_org_id
AND partner_type = 3;
log_message('Failure:Trading Partners Update Occured ');
end update_trading_partners; /* End of Updating Trading partners */
REM| 10/13/99 - Added deleted_flag in the insert statement |
REM| 10/18/99 - Changed value of Exception set Id from 1 to -1 |
REM| 12/09/99 - Added Code to include all Calendar Days |
REM| 12/17/99 - Fixed Code for Bug# 1117565 |
REM| 02/01/00 - next seq and prior seqs are made same as seq number in |
REM| - msc_calendar_dates insert, bug#1175906 |
REM| - similarly for next date and prior date are same as calendar|
REM| - dates |
REM| 03/01/00 - Added Code to not to include rows which have |
REM| shift_duration as zero seconds - Bug#1221285 |
REM| 03/20/03 - Added Inserts to msc_st_shift_times table - 2213101 |
REM| 03/20/03 - Added Inserts to msc_st_shift_dates table - 2213101 |
REM| |
REM| |
REM+==========================================================================+
*/
PROCEDURE retrieve_calendar_detail( p_calendar_code IN VARCHAR2,
p_cal_desc IN VARCHAR2,
p_run_date IN DATE,
p_db_link IN VARCHAR2,
p_instance_id IN PLS_INTEGER,
p_usage IN VARCHAR2,
return_status OUT NOCOPY BOOLEAN) IS
n_calendar_code varchar2(40);
/* Insert for Net Resource starts here, The following select statement gets
the period that are availble for a given calendar, From time and To Time
are taken in seconds here.
*/
IF return_status
THEN
v_cp_enabled := TRUE;
sql_cal := ' SELECT sd.shift_date calendar_date, '
|| ' sd.shift_num shift_no, '
|| ' st.from_time from_time, '
|| ' DECODE(st.to_time,0,86400,st.to_time) to_time '
|| ' FROM bom_calendars'||p_db_link||' cal, '
|| ' bom_shift_dates'||p_db_link||' sd, '
|| ' bom_shift_times'||p_db_link||' st '
|| ' WHERE cal.calendar_code = :curr_cal_code '
|| ' AND sd.calendar_code = cal.calendar_code '
|| ' AND st.calendar_code = sd.calendar_code '
|| ' AND sd.shift_num = st.shift_num '
|| ' AND sd.seq_num is not null '
|| ' ORDER BY calendar_date,from_time,to_time ';
new_rec.delete;
be inserted in gtmp table. The gtmp table will have the calendar code
thats not associated to the instance. This change has been
done esp. when the user choses to generate and collect the resource
data. */
n_calendar_code := p_cal_desc;
ins_stmt := 'INSERT INTO gmp_calendar_detail_gtmp'||p_db_link
||' ( '
||' calendar_code, '
||' shift_num, '
||' shift_date, '
||' from_time, '
||' to_time, '
||' from_date, '
||' to_date '
||' ) '
||' VALUES '
||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7)';
/* ins_stmt1 := 'INSERT INTO temp_cal'||p_db_link
||' ( '
||' calendar_code, '
||' shift_num, '
||' shift_date, '
||' from_time, '
||' to_time, '
||' from_date, '
||' to_date '
||' ) '
||' VALUES '
||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7)';
log_message('p_usage = APS before INSERT INTO msc_st_shift_times');
get_shift_time := ' INSERT INTO msc_st_shift_times '
|| ' ( shift_num, '
|| ' calendar_code, '
|| ' from_time, '
|| ' to_time, '
|| ' deleted_flag, '
|| ' sr_instance_id '
|| ' ) '
|| ' SELECT distinct decode(sign(gtmp.shift_num - 99999),0,0,1,(gtmp.shift_num - 99999),-1 ,gtmp.shift_num) shift_num , '
|| ' :v_calendar , '
|| ' from_time, '
|| ' to_time, '
|| ' 2 , '
|| ' :instance_id '
|| ' FROM gmp_calendar_detail_gtmp'||p_db_link||' gtmp '
|| ' WHERE calendar_code = :curr_cal_code '
|| ' ORDER BY shift_num,from_time,to_time ';
REM| net_rsrc_insert |
REM| |
REM| Type |
REM| public |
REM| |
REM| DESCRIPTION |
REM| |
REM| |
REM| Input Parameters |
REM| p_org_id - Organization id |
REM| p_orgn_code - Orgn Code |
REM| p_calendar_id - calendar_id |
REM| p_instance_id - Instance Id |
REM| p_usage - Used foir APS or WPS |
REM| p_db_link - Data Base Link |
REM| |
REM| Output Parameters |
REM| return_status |
REM| |
REM| HISTORY |
REM| Created on 4th Jan 2002 By Rajesh Patangya |
REM| 7th Mar 2003 -- Performance issue fix and B2671540 00:00 shift fix |
REM| B3161696 - 26-SEP-2003 TARGETTED RESOURCE AVAILABILITY PLACEHOLDER BUG |
REM| |
REM+==========================================================================+
*/
PROCEDURE net_rsrc_insert(p_org_id IN PLS_INTEGER,
p_orgn_code IN varchar2,
p_simulation_set IN varchar2,
p_db_link IN varchar2,
p_instance_id IN PLS_INTEGER,
p_run_date IN DATE ,
p_calendar_code IN varchar2,
p_usage IN varchar2,
return_status OUT NOCOPY BOOLEAN) IS
/* Local array definition */
TYPE interval_typ_r is RECORD
(
resource_count PLS_INTEGER,
resource_id PLS_INTEGER,
instance_id PLS_INTEGER,
instance_number number,
shift_num number,
from_date date,
to_date date
);
proc_name := 'net_rsrc_insert'; /* B13866449 Rajesh Patangya */
log_message(' in net_rsrc_insert proc ');
sqlstmt := ' SELECT calendar_code '
|| ' FROM mtl_parameters'||p_db_link
|| ' WHERE organization_id = :orgn_id1 ';
sql_stmt1 := ' SELECT /*+ RULE leading(rt) cardinality(rt,1) */ '
|| ' decode(rt.interval_date,rt.lead_idate,rt.assigned_qty,decode(rt.rsum,0,rt.assigned_qty,rt.assigned_qty-rt.rsum)) resource_count '
|| ' ,rt.resource_id '
|| ' ,0 instance_id '
|| ' ,0 instance_number '
|| ' ,rt.shift_num '
|| ' ,rt.interval_date from_date '
|| ' ,rt.lead_idate to_date '
|| ' FROM '
|| ' ( '
|| ' SELECT '
|| ' t.resource_id '
|| ' ,t.shift_num '
|| ' ,t.interval_date '
|| ' ,t.assigned_qty '
|| ' ,nvl(sum(u.resource_units),0) rsum '
|| ' ,max(t.lead_idate) lead_idate '
|| ' FROM '
|| ' ( '
|| ' SELECT unique resource_id,instance_number,from_date, '
|| ' to_date to_date1,resource_units '
|| ' FROM ( '
|| ' SELECT un.resource_id, '
|| ' gri.instance_number, '
|| ' un.from_date, '
|| ' un.to_date, '
|| ' 1 resource_units'
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.schedule_ind = 2 '
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.organization_id = :orgn_id1 '
|| ' AND nvl(crd.calendar_code,:g_default_code1)=:l_cal_code1';
|| ' SELECT un.resource_id, '
|| ' gri.instance_number, '
|| ' un.from_date, '
|| ' un.to_date, '
|| ' 1 resource_units'
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.schedule_ind = 2 '
|| ' AND nvl(un.instance_id,0) = 0 '
|| ' AND crd.organization_id = :orgn_id2 '
|| ' AND nvl(crd.calendar_code,:g_default_code2)=:l_cal_code2';
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances'||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' UNION ALL '
|| ' SELECT un.resource_id, '
|| ' 0 instance_number, '
|| ' un.from_date, '
|| ' un.to_date, '
|| ' un.resource_units '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un'
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.organization_id = :orgn_id3 '
|| ' AND nvl(crd.calendar_code,:g_default_code3)=:l_cal_code3';
|| ' (SELECT 1 '
|| ' FROM gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE gri.resource_id = un.resource_id ) '
|| ' ) '
|| ' ) u, '
|| ' ( '
|| ' SELECT resource_id,shift_num,interval_date, '
|| ' assigned_qty,lead_idate '
|| ' FROM '
|| ' ( '
|| ' SELECT resource_id,shift_num,interval_date, '
|| ' assigned_qty '
|| ' ,lead(resource_id,1) over(order by '
|| ' resource_id,interval_date,shift_num) as lead_rid '
|| ' ,lead(interval_date,1) over(order by '
|| ' resource_id,interval_date,shift_num) as lead_idate '
|| ' ,lead(shift_num,1) over(order by '
|| ' resource_id,interval_date,shift_num) as lead_snum '
|| ' FROM '
|| ' ( '
|| ' SELECT unique cmd.resource_id, '
|| ' 0 , '
|| ' exp.shift_num, '
|| ' 0 , '
|| ' cmd.interval_date, '
|| ' cmd.assigned_qty '
|| ' FROM ( '
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_number instance_number,'
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.from_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.schedule_ind = 2 '
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) <> 0 '
|| ' AND crd.organization_id = :orgn_id4 '
|| ' AND nvl(crd.calendar_code,:g_default_code4)=:l_cal_code4';
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_number instance_number,'
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.to_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.schedule_ind = 2 '
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) <> 0 '
|| ' AND crd.organization_id = :orgn_id5 '
|| ' AND nvl(crd.calendar_code,:g_default_code5)=:l_cal_code5';
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_number instance_number,'
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.from_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.schedule_ind = 2 '
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) = 0 '
|| ' AND crd.organization_id = :orgn_id6 '
|| ' AND nvl(crd.calendar_code,:g_default_code6)=:l_cal_code6';
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances'||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' UNION ALL '
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_number instance_number,'
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.to_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.schedule_ind = 2 '
|| ' AND nvl(un.instance_id,0) = 0 '
|| ' AND crd.organization_id = :orgn_id7 '
|| ' AND nvl(crd.calendar_code,:g_default_code7)=:l_cal_code7 ';
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances'||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' UNION ALL '
|| ' SELECT un.resource_id, '
|| ' 0 instance_number, '
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.from_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.organization_id = :orgn_id8 '
|| ' AND nvl(crd.calendar_code,:g_default_code8)=:l_cal_code8 ';
|| ' (SELECT 1 '
|| ' FROM gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE gri.resource_id = un.resource_id ) '
|| ' UNION ALL '
|| ' SELECT un.resource_id, '
|| ' 0 instance_number, '
|| ' 0 shift_num,'
|| ' 0 resource_count,'
|| ' un.to_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.organization_id = :orgn_id9 '
|| ' AND nvl(crd.calendar_code,:g_default_code9)=:l_cal_code9';
|| ' (SELECT 1 '
|| ' FROM gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE gri.resource_id = un.resource_id ) '
|| ' ) cmd, '
|| ' gmp_calendar_detail_gtmp'||p_db_link||' exp '
|| ' WHERE exp.calendar_code = :CAL91 '
|| ' AND cmd.interval_date BETWEEN '
|| ' exp.from_date AND exp.to_date '
|| ' UNION ALL '
|| ' SELECT crd.resource_id , '
|| ' 0 , '
|| ' exp.shift_num, '
|| ' 0 , '
|| ' exp.from_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_calendar_detail_gtmp'||p_db_link||' exp '
|| ' WHERE crd.delete_mark = 0 '
|| ' AND crd.organization_id = :orgn_id10 '
|| ' AND nvl(crd.calendar_code,:g_default_code10)=:l_cal_code10 ';
|| ' SELECT crd.resource_id , '
|| ' 0 , '
|| ' exp.shift_num, '
|| ' 0 , '
|| ' exp.to_date interval_date, '
|| ' crd.assigned_qty assigned_qty '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_calendar_detail_gtmp'||p_db_link||' exp '
|| ' WHERE crd.delete_mark = 0 '
|| ' AND crd.organization_id = :orgn_id11 '
|| ' AND nvl(crd.calendar_code,:g_default_code11)=:l_cal_code11 ';
log_message('p_usage = WPS from net_rsrc_insert');
INSERT INTO gmp_resource_avail
(
instance_id, organization_id, resource_id,
calendar_code, resource_instance_id, shift_num,
shift_date, from_time, to_time,
resource_units, creation_date, created_by,
last_update_date, last_updated_by, last_update_login
) VALUES
(
p_instance_id,
p_org_id,
resource_id(i),
p_calendar_code,
instance_id(i),
shift_num(i),
trunc(f_date(i)),
((f_date(i) - trunc(f_date(i))) * 86400 ),
((t_date(i) - trunc(t_date(i))) * 86400 ),
resource_count(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
);
/* Moved this select statement above. This procedure will not be called
for APS. So this Select statement will never get executed. To handle this,
moving this select statement in procedure populate_rsrc_cal*/
/* Insert for msc_st_resource_shifts Starts here - 2213101 */
return_status := TRUE;
log_message('NO DATA FOUND exception: Gmp_calendar_pkg.net_rsrc_insert');
log_message('Error in Net Resource Insert: '||stmt_no);
end net_rsrc_insert;
REM| insert_gmp_resource_avail |
REM| |
REM| Type |
REM| public |
REM| |
REM| DESCRIPTION |
REM| |
REM| Input Parameters |
REM| p_orgn_code - Orgn Code |
REM| |
REM| Output Parameters |
REM| errbuf and retcode |
REM| |
REM| HISTORY |
REM| Created on 4th Jan 2002 By Rajesh Patangya |
REM| B3161696 - 26-SEP-2003 TARGETTED RESOURCE AVAILABILITY PLACEHOLDER BUG |
REM| sowsubra - calendar convergence ME changes - |
REM| Replace the calendar id with calendar code,orgn_code with organization_id|
REM| B4724360 - 12-DEC-2005 Modified code to TO ADD TIME OR A SHIFT TO A |
REM| PLANT RESOURCE |
REM+==========================================================================+
*/
PROCEDURE insert_gmp_resource_avail(errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY number ,
p_org_id IN PLS_INTEGER ,
p_from_rsrc IN varchar2 ,
p_to_rsrc IN varchar2 ,
p_calendar_code IN varchar2 ) IS
TYPE cal_shift_typ is RECORD
(
calendar_no varchar2(16),
calendar_desc varchar2(40)
);
delete_stmt VARCHAR2(9000) ;
delete_msg VARCHAR2(500) ;
proc_name := 'insert_gmp_resource_avail'; /* B13866449 Rajesh Patangya */
delete_stmt := NULL ;
/* delete_msg := 'DELETE FROM gmp_collection_messages ' ;
EXECUTE IMMEDIATE delete_msg ;
log_message(' P_FROM_RSRC from insert_gmp_resource_avail: '||P_FROM_RSRC || ' PRG_EXECUTING = ' || PRG_EXECUTING );
select name into SOURCE_DBNAME from V$database ;
log_message('Entered insert_gmp_resource_avail PARAMETERS - ' ||p_org_id||'**'|| p_calendar_code
|| ' V_FROM_RSRC: '||V_FROM_RSRC||'** V_TO_RSRC: '||V_TO_RSRC||'** res_count: '||res_count ||'bom_result -'||bom_result);
sql_1 := ' SELECT count(*) from '
|| ' ( select min(LAST_UPDATE_DATE) lud from gmp_resource_avail '
|| ' where CALENDAR_CODE = :cal_code '
|| ' and ORGANIZATION_ID = :orgn_id ) gmp, '
|| ' (select min(LAST_UPDATE_DATE) lud from bom_shift_dates '
|| ' where CALENDAR_CODE = :cal_code ) bom '
|| ' WHERE bom.lud > gmp.lud ' ;
SELECT MIN(RESOURCES), MAX(RESOURCES) into VRESOURCE_FROM, VRESOURCE_TO
FROM (
SELECT crd.RESOURCES FROM
( SELECT RESOURCES , RESOURCE_id , c.LAST_UPDATE_DATE
FROM cr_rsrc_dtl c , mtl_parameters m
WHERE c.ORGANIZATION_ID = p_org_id
AND c.ORGANIZATION_ID = m.organization_id
AND NVL(c.CALENDAR_CODE,m.CALENDAR_CODE) = p_calendar_code
UNION
SELECT c.RESOURCES , c.RESOURCE_id , b.LAST_UPDATE_DATE
FROM cr_rsrc_dtl c , mtl_parameters m , gmp_rsrc_unavail_man b
WHERE c.ORGANIZATION_ID = p_org_id
AND c.ORGANIZATION_ID = m.organization_id
AND NVL(c.CALENDAR_CODE,m.CALENDAR_CODE) = p_calendar_code
AND c.resource_id = b.resource_id
UNION
-- VKINDURI BUG 13645301 Resource Unavailability
SELECT c.RESOURCES , c.RESOURCE_id , MAX(b.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM cr_rsrc_dtl c , mtl_parameters m , gmp_rsrc_excp_asnmt b
WHERE c.ORGANIZATION_ID = p_org_id
AND c.ORGANIZATION_ID = m.organization_id
AND NVL(c.CALENDAR_CODE,m.CALENDAR_CODE) = p_calendar_code
AND c.resource_id = b.resource_id
GROUP BY c.RESOURCES , c.RESOURCE_ID
) crd ,
( SELECT MIN(LAST_UPDATE_DATE) lud , resource_id FROM gmp_resource_avail
WHERE CALENDAR_CODE = p_calendar_code
AND ORGANIZATION_ID = p_org_id
GROUP BY resource_id
) gmp
WHERE crd.LAST_UPDATE_DATE > gmp.lud AND crd.resource_id = gmp.resource_id
UNION
SELECT c.RESOURCES
FROM cr_rsrc_dtl c , mtl_parameters m
WHERE c.ORGANIZATION_ID = p_org_id
AND c.ORGANIZATION_ID = m.organization_id
AND NVL(c.CALENDAR_CODE,m.CALENDAR_CODE) = p_calendar_code
AND NOT EXISTS ( SELECT 1 FROM gmp_resource_avail gmp
WHERE gmp.CALENDAR_CODE = p_calendar_code
AND gmp.ORGANIZATION_ID = p_org_id
AND c.resource_id = gmp.resource_id )
) ;
sql_get_orgn := ' SELECT m.organization_code '
||' FROM hr_organization_units hr, '
||' mtl_parameters m '
||' WHERE m.organization_id = :orgn_id '
||' AND m.organization_id = hr.organization_id '
||' AND nvl(hr.date_to,sysdate) >= sysdate '
||' AND m.process_enabled_flag = '||''''||'Y'||'''';
cal_shift_rec.DELETE;
sql_get_cal := ' SELECT DISTINCT cal.calendar_code, '
||' cal.description '
||' FROM bom_calendars cal, '
||' hr_organization_units hr, '
||' mtl_parameters m, '
||' cr_rsrc_dtl crd '
||' WHERE m.organization_id = :orgn_id '
||' AND m.organization_id = hr.organization_id '
||' AND nvl(hr.date_to,sysdate) >= sysdate '
||' AND m.process_enabled_flag = '||''''||'Y'||''''
||' AND crd.organization_id = m.organization_id '
||' AND NVL(crd.calendar_code,m.calendar_code)=cal.calendar_code '
||' AND crd.delete_mark = 0 ' ;
sql_get_cal := ' SELECT cal.calendar_code, '
||' substr(cal.description,1,40) '
||' FROM bom_calendars cal '
||' WHERE cal.calendar_code = :cal_code ';
delete_stmt := 'DELETE FROM gmp_resource_avail '||
' WHERE CALENDAR_CODE = :cal_code ' ||
' AND organization_id = :org_id1 ';
log_message('Delete Case A - Both from and To resources are Entered ');
delete_stmt := delete_stmt ||' AND resource_id in (select resource_id '
||' FROM cr_rsrc_dtl '
||' WHERE organization_id = :org_id2 '
||' AND resources BETWEEN :frsrc and :trsrc ) ';
EXECUTE IMMEDIATE delete_stmt USING cal_shift_rec(i).calendar_no, p_org_id,
p_org_id, v_from_rsrc, v_to_rsrc;
log_message('Delete Case B - From Resource is entered and To Resource is blank ');
delete_stmt := delete_stmt ||' AND resource_id in (select resource_id '
||' FROM cr_rsrc_dtl '
||' WHERE organization_id = :org_id2 '
||' AND resources > :frsrc ) ';
EXECUTE IMMEDIATE delete_stmt USING cal_shift_rec(i).calendar_no, p_org_id,
p_org_id, v_from_rsrc;
log_message('Delete Case C - Both from and To resources are BLANK');
EXECUTE IMMEDIATE delete_stmt USING cal_shift_rec(i).calendar_no, p_org_id;
log_message(delete_stmt) ;
log_message('Calling net_rsrc_insert WPS Summary Rows');
log_message ('before Calling net_rsrc_insert WPS Summary Rows');
net_rsrc_insert(p_org_id, /*sowsubra - org_id is passed.*/
p_orgn_code,
null,
null,
0,
sysdate,
cal_shift_rec(i).calendar_no,
V_WPS,
ret_status) ;
log_message ('after Calling net_rsrc_insert WPS Summary Rows');
log_message('FAILED net_rsrc_insert WPS Summary Rows');
log_message('insert_gmp_resource_avail ' || sqlerrm);
END insert_gmp_resource_avail;
sqlstmt := ' SELECT calendar_code '
|| ' FROM mtl_parameters'||p_db_link
|| ' WHERE organization_id = :orgn_id1 ';
sql_stmt1 := ' SELECT /*+ RULE leading(rt) cardinality(rt,1) */ '
|| ' decode(rt.interval_date,rt.lead_idate,rt.assigned_qty,'
|| ' (rt.assigned_qty-nvl(rt.rsum,0))) resource_count '
|| ' ,rt.resource_id '
|| ' ,rt.instance_id '
|| ' ,rt.shift_num '
|| ' ,rt.interval_date '
|| ' ,rt.lead_idate '
|| ' FROM '
|| ' ( '
|| ' SELECT '
|| ' t.resource_id '
|| ' ,t.instance_id '
|| ' ,t.shift_num '
|| ' ,t.interval_date '
|| ' ,t.assigned_qty '
|| ' ,nvl(u.resource_units,0) rsum '
|| ' ,max(t.lead_idate) lead_idate '
|| ' FROM ( '
|| ' SELECT unique resource_id,instance_id,from_date, '
|| ' to_date to_date1,resource_units '
|| ' FROM ( '
|| ' SELECT un.resource_id, '
|| ' gri.instance_id, '
|| ' un.from_date, '
|| ' un.to_date, '
|| ' 1 resource_units'
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.organization_id = :orgn_id1 '
|| ' AND nvl(crd.calendar_code,:g_default_cal_code1)=:l_cal_code1'
|| ' AND crd.schedule_ind = 2 '
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) <> 0 ' ;
|| ' SELECT un.resource_id, '
|| ' gri.instance_id, '
|| ' un.from_date, '
|| ' un.to_date, '
|| ' 1 resource_units'
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.organization_id = :orgn_id2 '
|| ' AND nvl(crd.calendar_code,:g_default_cal_code2)=:l_cal_code2'
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.schedule_ind = 2 '
|| ' AND nvl(un.instance_id,0) = 0 ' ;
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances'||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' ) '
|| ' ) u, '
|| ' ( '
|| ' SELECT resource_id,instance_id, shift_num, '
|| ' interval_date,assigned_qty,lead_idate '
|| ' FROM '
|| ' ( '
|| ' SELECT '
|| ' resource_id,instance_id,shift_num, '
|| ' interval_date,1 assigned_qty, '
|| ' lead(resource_id,1) over(order by '
|| ' resource_id,instance_id,interval_date,shift_num) as lead_rid, '
|| ' lead(instance_id,1) over(order by '
|| ' resource_id,instance_id,interval_date,shift_num) as lead_iid, '
|| ' lead(interval_date,1) over(order by '
|| ' resource_id,instance_id,interval_date,shift_num) as lead_idate, '
|| ' lead(shift_num,1) over(order by '
|| ' resource_id,instance_id,interval_date,shift_num) as lead_snum '
|| ' FROM '
|| ' ( '
|| ' SELECT unique cmd.resource_id, '
|| ' cmd.instance_id, '
|| ' exp.shift_num, '
|| ' 1 , '
|| ' cmd.interval_date '
|| ' FROM ( '
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_id instance_id,'
|| ' 0 shift_num,'
|| ' 1 resource_count,'
|| ' un.from_date interval_date '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.organization_id = :orgn_id3 '
|| ' AND nvl(crd.calendar_code,:g_default_cal_code3)=:l_cal_code3'
|| ' AND crd.schedule_ind = 2 '
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) <> 0 ' ;
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_id instance_id,'
|| ' 0 shift_num,'
|| ' 1 resource_count,'
|| ' un.to_date interval_date '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND un.instance_id = gri.instance_id '
|| ' AND crd.organization_id = :orgn_id4 '
|| ' AND nvl(crd.calendar_code,:g_default_cal_code4)=:l_cal_code4'
|| ' AND crd.schedule_ind = 2 '
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) <> 0 ' ;
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_id instance_id,'
|| ' 0 shift_num,'
|| ' 1 resource_count,'
|| ' un.from_date interval_date '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.organization_id = :orgn_id5 '
|| ' AND nvl(crd.calendar_code,:g_default_cal_code5)=:l_cal_code5'
|| ' AND crd.schedule_ind = 2 '
|| ' AND crd.delete_mark = 0 '
|| ' AND nvl(un.instance_id,0) = 0 ' ;
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances'||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' UNION ALL '
|| ' SELECT un.resource_id resource_id, '
|| ' gri.instance_id instance_id,'
|| ' 0 shift_num,'
|| ' 1 resource_count,'
|| ' un.to_date interval_date '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.resource_id = un.resource_id '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.organization_id = :orgn_id6 '
|| ' AND nvl(crd.calendar_code,:g_default_cal_code6)=:l_cal_code6'
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.schedule_ind = 2 '
|| ' AND nvl(un.instance_id,0) = 0 ' ;
|| ' ( select tgri.instance_number '
|| ' FROM gmp_resource_instances'||p_db_link||' tgri '
|| ' WHERE tgri.resource_id = crd.resource_id '
|| ' AND rownum <= un.resource_units '
|| ' ) '
|| ' ) cmd, '
|| ' gmp_calendar_detail_gtmp'||p_db_link||' exp '
|| ' WHERE exp.calendar_code = :curr_cal1 '
|| ' AND cmd.interval_date BETWEEN '
|| ' exp.from_date AND exp.to_date '
|| ' UNION ALL '
|| ' SELECT crd.resource_id , '
|| ' gri.instance_id, '
|| ' exp.shift_num, '
|| ' 1 , '
|| ' (exp.shift_date + '
|| ' (exp.from_time/86400)) interval_date '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_calendar_detail_gtmp'||p_db_link||' exp, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.organization_id = :orgn_id7 '
|| ' AND nvl(crd.calendar_code,:g_default_cal_code7)=:l_cal_code7'
|| ' AND exp.calendar_code = :curr_cal2 '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.delete_mark = 0 '
|| ' AND crd.schedule_ind = 2 ' ;
|| ' SELECT crd.resource_id , '
|| ' gri.instance_id, '
|| ' exp.shift_num, '
|| ' 1 , '
|| ' (exp.shift_date + '
|| ' (exp.to_time/86400)) interval_date '
|| ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
|| ' gmp_calendar_detail_gtmp'||p_db_link||' exp, '
|| ' gmp_resource_instances'||p_db_link||' gri '
|| ' WHERE crd.organization_id = :orgn_id8 '
|| ' AND nvl(crd.calendar_code,:g_default_cal_code8)=:l_cal_code8'
|| ' AND exp.calendar_code = :curr_cal3 '
|| ' AND crd.resource_id = gri.resource_id '
|| ' AND crd.delete_mark = 0 ' ;
INSERT INTO gmp_resource_avail
(
instance_id, organization_id, resource_id,
calendar_code, resource_instance_id, shift_num,
shift_date, from_time, to_time,
resource_units, creation_date, created_by,
last_update_date, last_updated_by, last_update_login
) VALUES
(
p_instance_id,
p_org_id,
resource_id(i),
p_calendar_code,
instance_id(i),
shift_num(i),
trunc(f_date(i)),
((f_date(i) - trunc(f_date(i))) * 86400 ),
((t_date(i) - trunc(t_date(i))) * 86400 ),
resource_count(i),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
) ;
sql_assembly := ' SELECT /*+ ALL_ROWS */ '
|| ' net.resource_id , '
|| ' net.resource_instance_id, '
|| ' net.shift_date , '
|| ' net.shift_num , '
|| ' net.resource_units , '
|| ' min(net.from_time) from_time, '
|| ' max(net.lead_tt) to_time '
|| ' FROM ( '
|| ' SELECT resource_id , '
|| ' resource_instance_id, '
|| ' shift_date , '
|| ' shift_num , '
|| ' from_time , '
|| ' to_time , '
|| ' resource_units , '
|| ' lead(resource_id,1) over(order by resource_id,resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_rid, '
|| ' lead(resource_instance_id,1) over(order by resource_id, '
|| ' resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_iid, '
|| ' lead(shift_date,1) over(order by resource_id,resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_sdt, '
|| ' lead(shift_num,1) over(order by resource_id,resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_sn, '
|| ' lead(from_time,1) over(order by resource_id,resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_ft, '
|| ' lead(to_time,1) over(order by resource_id,resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_tt, '
|| ' lead(resource_units,1) over(order by resource_id, '
|| ' resource_instance_id, '
|| ' shift_date, shift_num,from_time,to_time,resource_units) as lead_rc '
|| ' FROM gmp_resource_avail'
|| ' WHERE organization_id = :orgn_id1 '
|| ' AND calendar_code = :cal_code ' ;
sql_assembly := sql_assembly ||' AND resource_id in (select resource_id '
||' from cr_rsrc_dtl'||p_db_link
||' WHERE organization_id = :orgn_id2 '
||' AND resources BETWEEN :frsrc and :trsrc )' ;
sql_del := 'DELETE FROM gmp_resource_avail'
|| ' WHERE organization_id = :org_id1 '
|| ' AND calendar_code = :cal_code '
|| ' AND resource_id = :prid '
|| ' AND resource_instance_id = :piid '
|| ' AND shift_date = :psdt '
|| ' AND shift_num = :psn '
|| ' AND from_time >= :pft '
|| ' AND to_time <= :ptt '
|| ' AND resource_units = :prc ' ;
net_rsrc_avail_insert(
p_instance_id,
p_org_id,
interval_record.resource_instance_id,
p_calendar_code,
interval_record.resource_id,
interval_record.resource_units,
interval_record.shift_num,
interval_record.shift_date,
interval_record.from_time,
interval_record.to_time
);
log_message('UPDATE gmp_resource_avail calender= ' || p_calendar_code || ' org_id= ' || p_org_id);
sqlupt := 'UPDATE gmp_resource_avail'
||' SET to_time = 86400 '
||' WHERE to_time = 86399 '
||' AND shift_num >= 99999 '
||' AND calendar_code = :t_calendar_code '
||' AND organization_id = :t_org_id '
||' AND resource_id <> 0' ;
sqlupt := 'UPDATE gmp_resource_avail'
||' SET shift_num = (shift_num - 99999) '
||' WHERE shift_num >= 99999 '
||' AND calendar_code = :t_calendar_code '
||' AND organization_id = :t_org_id '
||' AND resource_id <> 0' ;
/* UPDATE gmp_resource_avail
SET to_time = 86400 , shift_num = (shift_num - 99999)
WHERE shift_num >= 99999
AND calendar_code = p_calendar_code
AND organization_id = p_org_id
AND resource_id <> 0 ; */
log_message('Error in Net Resource Instance Insert: '||stmt_no);
REM| net_rsrc_avail_insert |
REM| |
REM| Type |
REM| public |
REM| |
REM| DESCRIPTION |
REM| The following procedure inserts rows into gmp_resource_avail |
REM| |
REM| Input Parameters |
REM| p_instance_id - Instance Id |
REM| p_orgn_code - Plant Code |
REM| p_resource_instance_id - Resource Instance Id |
REM| p_Calendar_id - Calendar id |
REM| p_resource_id - Resource Id |
REM| p_assigned_qty - Resource units |
REM| p_shift_num - Shift number |
REM| p_calendar_date - Calendar date |
REM| p_from_time - shift starting time |
REM| p_to_time - Shift Ending time |
REM| |
REM| Output Parameters |
REM| None |
REM| |
REM| HISTORY |
REM| Created on 4th Jan 2002 By Rajesh Patangya |
REM| |
REM+==========================================================================+
*/
PROCEDURE net_rsrc_avail_insert(p_instance_id IN PLS_INTEGER,
p_org_id IN PLS_INTEGER,
p_resource_instance_id IN PLS_INTEGER,
p_calendar_code IN VARCHAR2,
p_resource_id IN PLS_INTEGER,
p_assigned_qty IN NUMBER,
p_shift_num IN PLS_INTEGER,
p_calendar_date IN DATE,
p_from_time IN NUMBER,
p_to_time IN NUMBER ) IS
Begin
proc_name := 'net_rsrc_avail_insert';
INSERT INTO gmp_resource_avail (
instance_id, plant_code, resource_id,
calendar_code, resource_instance_id, shift_num,
shift_date, from_time, to_time,
resource_units, creation_date, created_by,
last_update_date, last_updated_by, last_update_login )
VALUES (
p_instance_id,
p_org_id,
p_resource_id,
p_calendar_code,
p_resource_instance_id,
p_shift_num,
p_calendar_date,
p_from_time,
p_to_time,
p_assigned_qty,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID ) ;
log_message('Error in Net Resource Avail Insert ');
END net_rsrc_avail_insert;
SELECT MSC_CL_GMP_UTILITY.is_aps_compatible
INTO l_aps_compatible FROM DUAL ;
SELECT to_char(sysdate,'DD-MON-RRRR HH24:MI:SS')
INTO cur_time FROM sys.dual ;