APPS.WSH_WF SQL Statements

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

Line: 272

SELECT
wdd.delivery_detail_id
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
mtl_system_items msi
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND   wda.delivery_id = wnd.delivery_id
AND   wnd.status_code in ('IT','CL')
AND   wnd.initial_pickup_date > p_last_notif_date
AND   nvl(wdd.shipped_quantity,0) > 0
AND   wdd.inventory_item_id = msi.inventory_item_id
AND   wdd.organization_id = msi.organization_id
AND   wdd.source_header_id = p_source_header_id
AND   wdd.source_code = p_source_code
AND   nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO')   -- J Inbound Logistics jckwok
AND   decode(p_contact_type,
	   'SHIP_TO',wdd.ship_to_contact_id,
	   'SOLD_TO',wdd.sold_to_contact_id,
	   wdd.customer_id) = p_contact_id;

Line: 300

SELECT
wdd.delivery_detail_id
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
mtl_system_items msi
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND   wdd.date_scheduled < sysdate
--AND   wdd.date_scheduled > p_last_notif_date
AND   nvl(wdd.picked_quantity, wdd.requested_quantity) > 0
AND   wdd.released_status NOT IN ('C', 'D')
AND   wda.delivery_id = wnd.delivery_id (+)
AND   nvl(wnd.status_code,'XX') not in ('IT','CL')
AND   wdd.inventory_item_id = msi.inventory_item_id
AND   wdd.organization_id = msi.organization_id
AND   wdd.source_header_id = p_source_header_id
AND   wdd.source_code = p_source_code
AND   nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO')   -- J Inbound Logistics jckwok
AND   decode(p_contact_type,
	   'SHIP_TO',wdd.ship_to_contact_id,
	   'SOLD_TO',wdd.sold_to_contact_id,
	   wdd.customer_id) = p_contact_id;

Line: 497

SELECT wdd.delivery_detail_id
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND   wda.delivery_id  = wnd.delivery_id (+)
AND   nvl(wnd.status_code,'XX') not in ('IT','CL')
AND   wdd.source_header_id = p_source_header_id
AND   wdd.source_code = p_source_code
AND   nvl(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO')   -- J Inbound Logistics jckwok
AND   decode(p_contact_type,
           'SHIP_TO',wdd.ship_to_contact_id,
           'SOLD_TO',wdd.sold_to_contact_id,
           wdd.customer_id) = p_contact_id;

Line: 628

SELECT item_key
FROM   wf_item_activity_statuses
WHERE  notification_id = to_number(c_document_id);

Line: 783

SELECT item_key
FROM   wf_item_activity_statuses
WHERE  notification_id = to_number(c_document_id);

Line: 940

PROCEDURE Update_Workflow(
           p_delivery_id in number) is

CURSOR c_get_item_key(p_delivery_id in number) IS
SELECT
DISTINCT wdd.source_header_id,
wdd.source_code,
decode(NVL(wdd.ship_to_contact_id,-99),
		-99,decode(nvl(wdd.sold_to_contact_id,-99),
			  -99, 'CUSTOMER',
				  'SOLD_TO'),
          'SHIP_TO') contact_type,
nvl(wdd.ship_to_contact_id,nvl(wdd.sold_to_contact_id,wdd.customer_id)) contact_id
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND   wda.delivery_id = p_delivery_id
AND   wda.delivery_id IS NOT NULL
AND   wdd.source_code = 'OE';

Line: 967

l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_WORKFLOW';

Line: 991

         WSH_DEBUG_SV.logmsg(l_module_name,  'IN PROCEDURE WSH_WF.UPDATE_WORKFLOW FOR DELIVERY '||P_DELIVERY_ID  );

Line: 1082

	    WSH_DEBUG_SV.logmsg(l_module_name,  'ERROR IN WSH_WF.UPDATE_WORKFLOW'  );

Line: 1093

END Update_Workflow;

Line: 1399

SELECT
party_id
FROM
hz_cust_accounts
WHERE
cust_account_id/*customer_id*/ = c_contact_id
AND    c_contact_type = 'CUSTOMER'
UNION
SELECT
rel.subject_id /*contact_party_id*/
FROM
hz_cust_account_roles Acct_role,
hz_relationships Rel,
hz_org_contacts Org_cont,
hz_cust_accounts Role_acct
WHERE
acct_role.cust_account_role_id/*contact id*/ = c_contact_id
AND acct_role.party_id = rel.party_id
AND acct_role.Role_type = 'CONTACT'
AND org_cont.party_relationship_id = rel.relationship_id
AND rel.party_id is not null
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES'
And acct_role.cust_account_id = role_acct.cust_account_id
AND role_acct.party_id = rel.Object_id
AND c_contact_type in ('SHIP_TO','SOLD_TO'); --TCA view Removal Ends