APPS.ISC_MAINT_LAB_BLG_ETL_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 23

    select 1 / decode( conversion_rate
                     , 0 , 1 -- prevent "divide by zero" error
                     , conversion_rate )
    from mtl_uom_conversions
    where uom_class = g_bom_time_class
    and uom_code = g_bom_hour_code
    and inventory_item_id = 0;

Line: 194

Insert /*+ append parallel (ISC_MAINT_LAB_BLG_F) */
into ISC_MAINT_LAB_BLG_F
(
organization_id
,user_defined_status_id  /* added user_defined work order status */
,work_order_name
,work_order_id
,resource_id
,department_id
,operation_seq_number
,op_start_date
,op_end_date
,hours_required
,hours_charged
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_id
,program_login_id
,program_application_id
,request_id
)
select  /*+ parallel(BR) parallel(FACT) parallel(WO) parallel(WOR)
            parallel(M1) parallel(M2) use_hash(WOR) use_hash(WO)
            pq_distribute(WO,hash,hash) pq_distribute(WOR,hash,hash) */
 fact.organization_id  			organization_id
,fact.user_defined_Status_id            user_defined_status_id
,fact.work_order_name	   		work_order_name
,fact.work_order_id	   		work_order_id
,WOR.RESOURCE_ID	   		resource_id
,WO.DEPARTMENT_ID	   		department_id
,WO.OPERATION_SEQ_NUM  	    		operation_seq_number
,WO.first_unit_start_date		op_start_date
,WO.first_unit_completion_date		op_end_date
,WOR.usage_rate_or_amount*m1.conversion_rate*g_time_base_to_hours
					hours_required
,WOR.applied_resource_units*m1.conversion_rate*g_time_base_to_hours
					hours_charged
,sysdate                        	creation_date
,g_user_id                      	created_by
,sysdate                        	last_update_date
,g_user_id                      	last_updated_by
,g_login_id                     	last_update_login
,g_program_id				program_id
,g_program_login_id			program_login_id
,g_program_application_id		program_application_id
,g_request_id				request_id


from
 WIP_OPERATIONS				wo
,WIP_OPERATION_RESOURCES		wor
,BOM_RESOURCES				br
,ISC_MAINT_WORK_ORDERS_F    		fact -- get only the work orders that satisfy the G_start_date criteria
,mtl_uom_conversions			m1

where
fact.status_type  in (17,6,3,1) and --to get the work orders in the status draft,released,unreleased and on-hold
fact.organization_id = wo.organization_id and    -- to get the same organizational work orders.
fact.organization_id = wor.organization_id and 	 -- to get the same organizational work orders.
fact.organization_id = br.organization_id and 	 -- to get the same organizational work orders.
-- departmental level join not required as it is not specified in the join.
-- courtesy etrm (wip_operation_resources)
nvl(wo.operation_completed,'N') = 'N' and -- to get the non completed operations
fact.work_order_id = wo.wip_entity_id  	 and 	-- to get the same work orders.
fact.work_order_id = wor.wip_entity_id   and 	-- to get the same work orders.
wo.operation_seq_num = wor.operation_seq_num and   -- to get the resource
wor.resource_id      = br.resource_id and	   -- to get the resource
br.resource_type = 2 and   -- only labor
WOR.usage_rate_or_amount > WOR.applied_resource_units and -- backlog indicator.
m1.inventory_item_id = 0 and
m1.uom_code = br.unit_of_measure and
m1.uom_class = g_bom_time_class;

Line: 283

bis_collection_utilities.log(l_rowcount||' rows inserted into the Base Summary Table',1);