APPS.CSM_MULTI_MOBQRY_PKG SQL Statements

Line: 650

  select   mr.reservation_id document_number,
         rld.source_type,
         mr.organization_id source_organization_id,
         mr.subinventory_code source_subinventory,
         rh.destination_organization_id ,
         rh.destination_subinventory,
         mr.reservation_id header_id,
         mr.reservation_id line_id,
         mr.inventory_item_id,
		 mr.revision,
         mr.reservation_uom_code uom_code,
         mr.serial_number,
         mr.reservation_quantity order_quantity,
         decode(mr.serial_number, NULL, mr.reservation_quantity, 1) as ship_quantity,
         rld.req_line_detail_id,
         NULL as shipment_number,
         NULL as shipment_line_id
from csp_req_line_details rld,
     csp_requirement_lines rl,
     csp_requirement_headers rh,
	 mtl_reservations mr
where rld.source_type='RES'
and rld.requirement_line_id=rl.requirement_line_id
and rl.requirement_header_id=rh.requirement_header_id
and rld.source_id=mr.reservation_id
and (rh.destination_organization_id<>mr.organization_id OR NVL(mr.subinventory_code,'-999') <> NVL(rh.destination_subinventory,'-999'))
and req_line_detail_id=b_rld_id
and rownum < b_qty+1
union all
select   oeh.order_number,
         rld.source_type,
         oel.ship_from_org_id source_organization_id,
         oel.subinventory source_subinventory,
         rh.destination_organization_id ,
         rh.destination_subinventory,
         oel.header_id,
         oel.line_id,
         oel.inventory_item_id,
		 NULL as REVISION,
         oel.shipping_quantity_uom uom_code,
         rss.serial_num serial_number,
         oel.ordered_quantity order_quantity,
         decode(rss.serial_num, NULL,oel.shipped_quantity,1) ship_quantity,
         rld.req_line_detail_id,
         rch.shipment_num as shipment_number,
         rcl.shipment_line_id
from csp_req_line_details rld,
     csp_requirement_lines rl,
     csp_requirement_headers rh ,
      oe_order_lines_all oel,
      oe_order_headers_all oeh,
      rcv_shipment_lines rcl,
	  rcv_shipment_headers rch	,
    rcv_serials_supply rss
where rld.source_type='IO'
and rld.source_id=oel.line_id
and oel.header_id=oeh.header_id
and rld.requirement_line_id=rl.requirement_line_id
and rl.requirement_header_id=rh.requirement_header_id
and rcl.source_document_code='REQ'
AND rcl.requisition_line_id=oel.source_document_line_id
and rcl.shipment_header_id=rch.shipment_header_id
and rcl.quantity_received< rcl.quantity_shipped
and rcl.shipment_line_id = rss.shipment_line_id (+)
and req_line_detail_id=b_rld_id
and rownum < b_qty+1;

Line: 781

	SELECT user_id,responsibility_id, app_id
	INTO  l_user_id,l_resp_id,l_app_id
	FROM  asg_user WHERE user_name=p_user_name;

Line: 853

				  FOR rec IN (SELECT USER_ID FROM CSP_REQ_LINE_DETAILS rld ,CSM_REQ_LINE_DETAILS_ACC acc
							  WHERE rld.SOURCE_TYPE='IO' AND rld.SOURCE_ID=r_part_line.line_id AND acc.REQ_LINE_DETAIL_ID=rld.REQ_LINE_DETAIL_ID)
				  LOOP   --to update received qty
					 l_dummy := asg_download.mark_dirty(
						p_pub_item         => 'CSM_ORDER_LINES'
					  , p_accessid         => r_part_line.line_id
					  , p_userid           => rec.user_id
					  , p_dml              => 'U'
					  , p_timestamp        => sysdate
					  );

Line: 927

    SELECT TASK_ID INTO l_task_id
    FROM JTF_TASK_ASSIGNMENTS
    WHERE TASK_ASSIGNMENT_ID=(SELECT TASK_ASSIGNMENT_ID FROM CSP_REQUIREMENT_HEADERS WHERE REQUIREMENT_HEADER_ID=P_REQ_HDR_ID);

Line: 939

l_rqmt_header_Rec.last_update_date           := sysdate;

Line: 941

l_rqmt_header_Rec.last_updated_by            := asg_base.get_user_id(p_user_name);

Line: 942

l_rqmt_header_Rec.last_update_login          := nvl(fnd_global.login_id, -1);

Line: 949

      CSP_REQUIREMENT_HEADERS_PVT.Update_requirement_headers(
 P_Api_Version_Number         => l_api_Version_number,P_Init_Msg_List =>  FND_API.G_TRUE,P_Commit => FND_API.G_FALSE,
 p_validation_level           => null,
 P_REQUIREMENT_HEADER_Rec     => l_rqmt_header_rec,
 X_Return_Status              => l_Return_status,
 X_Msg_Count   => l_msg_count,
 X_Msg_Data    => l_msg_data
      );

Line: 1005

    SELECT TASK_ID INTO l_task_id
    FROM JTF_TASK_ASSIGNMENTS
    WHERE TASK_ASSIGNMENT_ID=(SELECT TASK_ASSIGNMENT_ID FROM CSP_REQUIREMENT_HEADERS WHERE REQUIREMENT_HEADER_ID=P_REQ_HDR_ID);

Line: 1022

	SELECT user_id,responsibility_id, app_id ,org_id
	INTO  l_user_id,l_resp_id,l_app_id,l_org_id
	FROM  asg_user WHERE user_name=p_user_name;

Line: 1033

				,p_update_req_header =>'Y'
				,x_return_status =>l_return_status
				,x_msg_count =>l_msg_count
				,x_msg_data	 =>l_msg_data );

Line: 1082

	SELECT user_id,responsibility_id, app_id ,org_id
	INTO  l_user_id,l_resp_id,l_app_id,l_org_id
	FROM  asg_user WHERE user_name=p_user_name;

Line: 1135

	SELECT user_id,responsibility_id, app_id
	INTO  l_user_id,l_resp_id,l_app_id
	FROM  asg_user WHERE user_name=p_user_name;

Line: 1149

	SELECT a.cust_account_id ,b.party_id,b.location_id,a.org_id
	INTO l_account_id,l_party_id,x_loc_id,l_org_id
	FROM hz_cust_acct_sites_all a, hz_party_sites b,jtf_tasks_b t,cs_incidents_all_b i
	WHERE b.PARTY_SITE_ID=a.PARTY_SITE_ID
	AND t.task_id=p_task_id
	and t.sourcE_object_type_code='SR'
	and i.customer_id=b.party_id
	and t.sourcE_object_id=i.incident_id
	AND a.ORG_ID=i.org_id
	AND b.PARTY_SITE_ID=p_party_site_id;

Line: 1219

	SELECT user_id,responsibility_id, app_id ,org_id,resource_id
	INTO  l_user_id,l_resp_id,l_app_id,l_org_id,l_rs_id
	FROM  asg_user WHERE user_name=p_user_name;

Line: 1359

	select b.party_site_id,c.location_id ,a.address_id,a.org_id
	into l_hz_psite_id,l_hz_loc_id,l_cust_acct_site_id,l_org_id
	from po_location_associations_all a, HZ_CUST_ACCT_SITES_ALL b ,hz_party_sites c
	where a.location_id=x_loc_id
	and b.cust_account_id=x_cust_id
	and a.address_id=b.cust_acct_site_id
	and b.party_site_id=c.party_site_id;

Line: 1395

SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B
WHERE UPPER(SEGMENT1) = UPPER(p_item)
AND   ORGANIZATION_ID   = p_ORG_ID;

Line: 1401

SELECT p.master_organization_id,tl.NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL tl,mtl_parameters p
WHERE LANGUAGE = asg_base.get_language(p_user_name)
AND tl.ORGANIZATION_ID = p.master_organization_id
AND p.ORGANIZATION_ID =p_org_id;*/

Line: 1408

SELECT tl.organization_id,tl.NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL tl
WHERE LANGUAGE = asg_base.get_language(p_user_name)
AND tl.ORGANIZATION_ID = p_org_id;

Line: 1415

SELECT organization_code from mtl_parameters
WHERE ORGANIZATION_ID =p_org_id;

Line: 1419

SELECT  acc.organization_id, acc.subinventory_code,acc.REQUIRED_ITEM_ID,acc.REQUIRED_ITEM_REV,acc.REQUIRED_QUANTITY,
      acc.supplied_item_id,acc.supplied_item_rev,acc.supplied_quantity,acc.SUPPLIED_ITEM_TYPE,acc.source_type_code,
	  acc.SHIPPING_DATE ,acc.SHIPPING_METHOD,acc.SHIPPING_COST,acc.ARRIVAL_DATE,acc.DISTANCE,acc.OPEN_OR_CLOSED,
	  msik.concatenated_segments as item_name, msik.serial_number_control_code as serial_number_control_code,
	  tl.description as description, msik.primary_uom_code as uom_code,msik.comms_nl_trackable_flag as comms_nl_trackable_flag,
	  msik.material_billable_flag, msik.RESTRICT_LOCATORS_CODE, msik.LOCATION_CONTROL_CODE, msik.restrict_subinventories_code, msik.RECOVERED_PART_DISP_CODE
	  ,flv.meaning as ship_method_meaning
FROM csp_available_parts_temp acc, mtl_system_items_kfv msik, mtl_system_items_tl tl ,fnd_lookup_values flv
WHERE msik.inventory_item_id = acc.supplied_item_id AND msik.organization_id = acc.organization_id
AND acc.required_item_id=b_req_item_id
AND msik.mtl_transactions_enabled_flag = 'Y' AND tl.inventory_item_id = msik.inventory_item_id
AND tl.organization_id = msik.organization_id AND tl.LANGUAGE = asg_base.get_language(p_user_name)
AND flv.lookup_type(+) = 'SHIP_METHOD' AND flv.lookup_code(+) = acc.SHIPPING_METHOD AND flv.language(+)=asg_base.get_language(p_user_name)
ORDER BY acc.SHIPPING_COST,acc.organization_id,nvl(acc.distance,0);

Line: 1770

	FOR rec IN (SELECT msi.inventory_item_id , msi.organization_id , msi.segment1 AS inventory_name, msi_tl.description , msi.enabled_flag , msi.start_date_active ,
				msi.end_date_active , msi.primary_uom_code , msi.service_item_flag , msi.serviceable_product_flag , msi.material_billable_flag , msi.returnable_flag ,
				msi.mtl_transactions_enabled_flag , msi.serv_req_enabled_code, msi.serial_number_control_code , msi.lot_control_code , msi.inventory_asset_flag ,
				msi.purchasing_enabled_flag , msi.internal_order_enabled_flag , msi.internal_order_flag , msi.purchasing_item_flag , msi.restrict_subinventories_code,
				msi.comms_nl_trackable_flag , msi.stock_enabled_flag, msi.attribute1 , msi.attribute2 , msi.attribute3 , msi.attribute4 , msi.attribute5 , msi.attribute6 ,
				msi.attribute7 , msi.attribute8 , msi.attribute9 , msi.attribute10 , msi.attribute11 , msi.attribute12, msi.attribute13 , msi.attribute14 , msi.attribute15 ,
				msi.attribute_category, msi.contract_item_type_code, msi.recovered_part_disp_code, msi.revision_qty_control_code, msi.RESTRICT_LOCATORS_CODE,
				msi.LOCATION_CONTROL_CODE, msi.LIST_PRICE_PER_UNIT, UPPER(msi.segment1) AS inventory_name_upper
				FROM mtl_system_items_b msi, mtl_system_items_tl msi_tl
				WHERE msi_tl.inventory_item_id = msi.inventory_item_id AND msi_tl.organization_id = msi.organization_id AND msi_tl.language = asg_base.get_language(p_user_name)
				AND msi.segment1 like l_item_name AND msi.organization_id=P_ORG_ID and rownum<=11 order by msi.segment1)
				                                  --at client limiting to 10 items found , showing 11 for them to know that more exists here
   LOOP
    IF l_count=0 THEN
     L_XML_RESULT:= L_XML_RESULT||'inventory_item_idorganization_idinventory_name';

Line: 1866

 SELECT object_version_number
 FROM JTF_TASK_ASSIGNMENTS
 WHERE TASK_ASSIGNMENT_ID=to_number(P_TA_ID);

Line: 1871

 SELECT name  FROM JTF_TASK_STATUSES_TL WHERE TASK_STATUS_ID=to_number(P_TA_ST_ID)
 AND LANGUAGE=asg_base.get_language(p_user_name);

Line: 1889

IS select * from jtf_task_assignments where task_assignment_id=to_number(p_ta_id);

Line: 1892

  SELECT dh.debrief_header_id,         tst.rejected_flag,
         tst.on_hold_flag,         tst.cancelled_flag,
         tst.closed_flag,         tst.completed_flag
  FROM jtf_task_assignments tas,        csf_debrief_headers dh,
       jtf_task_statuses_b tst
  WHERE tas.task_assignment_id = p_task_assignment_id
  AND tas.task_assignment_id = dh.task_assignment_id
  AND tas.assignment_status_id = tst.task_status_id;

Line: 1916

	SELECT user_id,responsibility_id, app_id
	INTO  l_user_id,l_resp_id,l_app_id
	FROM  asg_user
	WHERE user_name=p_user_name	;

Line: 1927

	csf_task_assignments_pub.update_assignment_status
        ( p_api_version                => 1.0
        , p_init_msg_list              => FND_API.G_TRUE
        , p_commit                     => FND_API.G_TRUE
        -- Bug 101406041 , p_validation_level           => FND_API.G_VALID_LEVEL_NONE
        -- Validate task status transitions.
        , p_validation_level           => FND_API.G_VALID_LEVEL_FULL
        , x_return_status              => l_return_status
        , x_msg_count                  => l_msg_count
        , x_msg_data                   => l_msg_data
        , p_task_assignment_id         => l_ta_id
        , p_assignment_status_id       => to_number(P_TA_ST_ID)
        , p_object_version_number      => l_ta_ovn
        , p_update_task                => 'T'
        , x_task_object_version_number => l_task_ovn
        , x_task_status_id             => l_tsk_st_id
        );

Line: 1954

		  -- csf_debrief_update_pkg.form_Call for processing charges

		  OPEN c_chk_task_status ( l_ta_id );

Line: 1975

				csf_debrief_update_pkg.form_Call (1.0, l_debrief_header_id );