[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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, ' /* B4157063 Resource Batching */
|| ' batching_window, ' /* B4157063 Resource Batching */
|| ' 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*/
|| ') '
|| ' SELECT 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
*/
ins_res_group := ' INSERT INTO msc_st_resource_groups '
|| ' ( group_code, '
|| ' meaning, '
|| ' description, '
|| ' from_date, '
|| ' to_date, '
|| ' enabled_flag, '
|| ' sr_instance_id '
|| ' ) '
|| ' SELECT distinct crd.group_resource , '
|| ' crm.resource_desc,'
|| ' crm.resource_desc,'
|| ' sysdate,'
|| ' NULL,'
|| ' 1,'
|| ' :instance_id '
/*sowsubra - ME changes - Start*/
|| ' 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_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;
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' ;
||' gmp_calendar_pkg.insert_gmp_resource_avail'||p_db_link
||' (:p1, '
||' :p2, '
||' :p3, '
||' :p4, '
||' NULL, '
||' :p5); '
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 ===================*/
stmt_no := 30;
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('Resource Calendar Insertion Is Done' );
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('Resource Calendar Instance Insertion Is Done' );
/* Insert for msc_st_resource_shifts Starts here - 2213101 */
stmt_no := 32;
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 */
|| ' 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('Resource Shift Insertion Is Done' );
upd_res_avl := 'UPDATE msc_st_net_resource_avail '
||' SET to_time = 86400 '
||' WHERE to_time = 86399 '
||' AND shift_num >= 99999 ' ;
upd_res_avl := 'UPDATE msc_st_net_resource_avail '
||' SET shift_num = (shift_num - 99999) '
||' WHERE shift_num >= 99999 ' ;
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
IF return_status THEN
v_cp_enabled := TRUE;
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, '
|| ' 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)';
get_shift_time := ' INSERT INTO msc_st_shift_times '
|| ' ( shift_num, '
|| ' calendar_code, '
|| ' from_time, '
|| ' to_time, '
|| ' deleted_flag, '
|| ' sr_instance_id '
|| ' ) '
|| ' SELECT distinct 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
);
sqlstmt := ' SELECT calendar_code '
|| ' FROM mtl_parameters'||p_db_link
|| ' WHERE organization_id = :orgn_id1 ';
sql_stmt1 := ' SELECT /*+ ALL_ROWS */ '
|| ' 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 ';
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_stmt := NULL ;
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, '
||' cal.description '
||' 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 ';
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;
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;
EXECUTE IMMEDIATE delete_stmt USING cal_shift_rec(i).calendar_no, p_org_id;
log_message('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('FAILED net_rsrc_insert WPS Summary Rows');
END insert_gmp_resource_avail;
sqlstmt := ' SELECT calendar_code '
|| ' FROM mtl_parameters'||p_db_link
|| ' WHERE organization_id = :orgn_id1 ';
sql_stmt1 := ' SELECT /*+ ALL_ROWS */ '
|| ' 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
);
sqlupt := 'UPDATE gmp_resource_avail'
||' SET to_time = 86400 '
||' WHERE to_time = 86399 '
||' AND shift_num >= 99999 ' ;
sqlupt := 'UPDATE gmp_resource_avail'
||' SET shift_num = (shift_num - 99999) '
||' WHERE shift_num >= 99999 ' ;
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
IF nvl(p_from_time,0) = 0 AND nvl(p_to_time,0) = 0 THEN
NULL ;
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 ;