APPS.CSM_CSP_REQ_LINES_EVENT_PKG SQL Statements

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

Line: 33

   CSM_ACC_PKG.Insert_Acc
     ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
      ,P_ACC_TABLE_NAME         => g_acc_table_name1
      ,P_SEQ_NAME               => g_acc_sequence_name1
      ,P_PK1_NAME               => g_pk1_name1
      ,P_PK1_NUM_VALUE          => p_requirement_line_id
      ,P_USER_ID                => p_user_id
     );

Line: 66

   CSM_ACC_PKG.Delete_Acc
     ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
      ,P_ACC_TABLE_NAME         => g_acc_table_name1
      ,P_PK1_NAME               => g_pk1_name1
      ,P_PK1_NUM_VALUE          => p_requirement_line_id
      ,P_USER_ID                => p_user_id
     );

Line: 106

       CSM_ACC_PKG.Update_Acc
          ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
           ,P_ACC_TABLE_NAME         => g_acc_table_name1
           ,P_ACCESS_ID              => l_access_id
           ,P_USER_ID                => p_user_id
          );

Line: 127

PROCEDURE CONC_ORDER_UPDATE(p_status OUT NOCOPY VARCHAR2,
                            p_message OUT NOCOPY VARCHAR2)
IS

  /*** get the last run date of the concurent program ***/
  CURSOR  c_LastRundate
  IS
    SELECT NVL(LAST_RUN_DATE, to_date('1','J')) LAST_RUN_DATE
    FROM   JTM_CON_REQUEST_DATA
    WHERE  package_name =  'CSM_CSP_REQ_LINES_EVENT_PKG'
    AND    procedure_name = 'CONC_ORDER_UPDATE';

Line: 143

  SELECT acc.user_id
       , acc.access_id
  FROM   csm_req_lines_acc acc
       , CSP_REQ_LINE_DETAILS crld
       , OE_ORDER_LINES_ALL ol
  WHERE  acc.requirement_line_id = crld.requirement_line_id
  AND    crld.source_id          = ol.line_id
  AND    crld.SOURCE_TYPE='IO'
  AND    ol.LAST_UPDATE_DATE    >= b_last_run_date  */

CURSOR c_order_info (b_last_run_date DATE) IS
	 SELECT ol.line_id,acc.user_id,acc.requirement_line_id
	 FROM oe_order_lines_all ol,csm_req_lines_acc ACC, CSP_REQ_LINE_DETAILS crld
     WHERE acc.requirement_line_id= crld.requirement_line_id and crld.source_type='IO' and ol.line_id=crld.source_id
	 AND ol.last_update_date > b_last_run_date
   UNION ALL
	 SELECT ol.line_id,acc.user_id ,acc.requirement_line_id
	 FROM oe_order_lines_all ol,csm_req_lines_acc ACC, CSP_REQ_LINE_DETAILS crld
     WHERE acc.requirement_line_id= crld.requirement_line_id and crld.source_type='IO' and ol.line_id=crld.source_id
	 AND  ol.last_update_date < b_last_run_date
	 and  ol.flow_status_code in ('AWAITING_SHIPPING' ,'PRODUCTION_COMPLETE')
	 AND EXISTS(SELECT 1 FROM wsh_delivery_details WHERE source_line_id=ol.line_id AND source_code = 'OE' AND LAST_UPDATE_DATE > b_last_run_date);

Line: 169

  SELECT acc.user_id
       , res.reservation_id
  FROM   csm_req_lines_acc acc
       , CSP_REQ_LINE_DETAILS crld
       , MTL_RESERVATIONS res
  WHERE  acc.requirement_line_id = crld.requirement_line_id
  AND    crld.source_id          = res.RESERVATION_ID
  AND    crld.SOURCE_TYPE='RES'
  AND    res.LAST_UPDATE_DATE    >= b_last_run_date ;

Line: 188

SELECT HEADER_ID FROM OE_ORDER_LINES_ALL WHERE LINE_ID=b_line_id;

Line: 192

  CSM_UTIL_PKG.LOG('Entering CONC_ORDER_UPDATE','CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE',FND_LOG.LEVEL_PROCEDURE);

Line: 201

  l_tab_oe_line_id.DELETE;

Line: 202

  l_tab_user_id.DELETE;

Line: 203

  l_tab_rqmt_line_id.DELETE;

Line: 209

    CSM_UTIL_PKG.LOG('Updating Order Lines','CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE',FND_LOG.LEVEL_PROCEDURE);

Line: 235

          CSM_WF_PKG.RAISE_START_AUTO_SYNC_EVENT('CSM_REQ_LINES',to_char(l_tab_rqmt_line_id(i)),'UPDATE');

Line: 240

  l_tab_res_id.DELETE;

Line: 241

  l_tab_user_id.DELETE;

Line: 247

    CSM_UTIL_PKG.LOG('Updating Reservation Lines','CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE',FND_LOG.LEVEL_PROCEDURE);

Line: 262

  UPDATE JTM_CON_REQUEST_DATA
  SET LAST_RUN_DATE = l_current_run_date
  WHERE package_name =  'CSM_CSP_REQ_LINES_EVENT_PKG'
  AND   procedure_name = 'CONC_ORDER_UPDATE';

Line: 269

  CSM_UTIL_PKG.LOG('Leaving CONC_ORDER_UPDATE','CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE',FND_LOG.LEVEL_PROCEDURE);

Line: 272

  p_message :=  'CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE Executed successfully';

Line: 277

  p_message := 'Error in CSM_CSP_REQ_LINES_EVENT_PKG.CONC_ORDER_UPDATE: ' || substr(SQLERRM, 1, 2000);

Line: 283

    , 'Caught exception in CONC_ORDER_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 286

  fnd_msg_pub.Add_Exc_Msg('CSM_CSP_REQ_LINES_EVENT_PKG','CONC_ORDER_UPDATE',sqlerrm);

Line: 287

END CONC_ORDER_UPDATE;

Line: 305

  SELECT ACCESS_ID,USER_ID,SOURCE_ID FROM CSM_REQ_LINE_DETAILS_ACC WHERE REQ_LINE_DETAIL_ID=p_rld;

Line: 308

SELECT HEADER_ID FROM OE_ORDER_LINES_ALL WHERE LINE_ID=b_line_id;

Line: 330

     SELECT REQUIREMENT_LINE_ID,SOURCE_TYPE,SOURCE_ID INTO l_req_line_id,l_src,l_src_id FROM CSP_REQ_LINE_DETAILS WHERE req_line_detail_id=l_req_line_detail_id;

Line: 341

         INSERT INTO CSM_REQ_LINE_DETAILS_ACC(ACCESS_ID,REQ_LINE_DETAIL_ID,USER_ID,ITEM_ID,ORG_ID,SOURCE_ID,CREATION_DATE,LAST_UPDATE_DATE,CREATED_BY,LAST_UPDATED_BY)
            select CSM_REQ_LINES_ACC_S.nextval,req_line_detail_id,user_id,b.inventory_item_id,d.destination_organization_id,b.source_id,sysdate,sysdate,1,1
			from   (select b.req_line_detail_id,a.user_id,c.inventory_item_id,b.requirement_line_id,b.source_id
					from csm_req_lines_acc a, csp_req_line_details b ,
						 oe_order_lines_all c ,asg_user au
					where a.requirement_line_id=b.requirement_line_id
					and  b.source_type= 'IO' and b.source_id=c.line_id
					and b.req_line_detail_id=l_req_line_detail_id
					and  au.user_id=a.user_id
					UNION ALL
					select b.req_line_detail_id,a.user_id,c.inventory_item_id,b.requirement_line_id,b.source_id
					from csm_req_lines_acc a, csp_req_line_details b , mtl_reservations c,asg_user au
					where a.requirement_line_id=b.requirement_line_id
					and  b.source_type= 'RES' and b.source_id=c.reservation_id
					and  b.req_line_detail_id=l_req_line_detail_id
					and  au.user_id=a.user_id ) b,
					csp_requirement_headers d ,
					csp_requirement_lines e
			where  e.requirement_line_id=b.requirement_line_id
            and d.requirement_header_id=e.requirement_header_id
			and  not exists(select 1 from CSM_REQ_LINE_DETAILS_ACC acc
							where acc.req_line_detail_id=b.req_line_detail_id
							and acc.user_id=b.user_id);

Line: 370

         SELECT ACCESS_ID,USER_ID,ITEM_ID,ORG_ID BULK COLLECT INTO l_tab_access_id,l_tab_user_id,l_tab_item_id,l_tab_org_id
		 FROM CSM_REQ_LINE_DETAILS_ACC WHERE req_line_detail_id=l_req_line_detail_id;

Line: 396

			   IF p_DML_t='U' AND l_tab_old_src_id(I) <> l_src_id THEN  --on receive, reservation gets deleted and new one gets created with RLD updated to new one
			                                                            --on Addr change , RLD is updated with new IO line/header(Bug 16339885)

                    CSM_UTIL_PKG.LOG('Update Acc src and Mark Delete for old '||l_src||':'||l_tab_old_src_id(I)||' for user_id'||l_tab_user_id(I),
					'CSM_CSP_REQ_LINES_EVENT_PKG.REQ_LINE_DTL_IUD',FND_LOG.LEVEL_PROCEDURE);

Line: 416

					UPDATE CSM_REQ_LINE_DETAILS_ACC SET SOURCE_ID=l_src_id
					WHERE req_line_detail_id=l_req_line_detail_id AND USER_ID=l_tab_user_id(I);

Line: 441

	     DELETE FROM CSM_REQ_LINE_DETAILS_ACC WHERE ACCESS_ID=l_tab_access_id(I);

Line: 445

	  FOR I IN 1..l_tab_item_id.COUNT  --delete will be handled by mtl sys item refresh conc prog
	  LOOP
		   BEGIN
			  select 1 INTO l_src_id from csm_mtl_system_items_acc acc
			  where acc.inventory_item_id=l_tab_item_id(I)
			  and  acc.organization_id=l_tab_org_id(I)
			  and acc.user_id=l_tab_user_id(I);

Line: 459

			select csm_mtl_system_items_acc_s.NEXTVAL INTO l_access_id from dual;

Line: 461

			INSERT INTO csm_mtl_system_items_acc(access_id , user_id, inventory_item_id, organization_id, counter,
				   created_by, creation_date, last_updated_by, last_update_date, last_update_login)
			VALUES (l_access_id, l_tab_user_id(I),l_tab_item_id(I), l_tab_org_id(I), 1,fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);