[Home] [Help]
64: -- to call them only after the delivery status is set to 'OP'
65:
66: IF p_action_code IS NULL THEN
67:
68: update wsh_new_deliveries
69: set status_code ='OP'
70: where delivery_id = p_delivery_id
71: and status_code IN ('SR','SC');
72:
101: raise wsh_invalid_doc_type;
102: ELSIF (p_document_type = 'SR' ) THEN
103:
104: select planned_flag into l_planned_flag
105: from wsh_new_deliveries
106: where delivery_id = p_delivery_id;
107:
108: IF l_planned_flag = 'N' THEN
109:
133: END IF;
134: END IF;
135: END IF;
136: END IF;
137: update wsh_new_deliveries
138: set status_code ='SR'
139: where delivery_id = p_delivery_id
140: and status_code = 'OP';
141: ELSIF ( p_document_type = 'SA' ) THEN
138: set status_code ='SR'
139: where delivery_id = p_delivery_id
140: and status_code = 'OP';
141: ELSIF ( p_document_type = 'SA' ) THEN
142: update wsh_new_deliveries
143: set status_code ='SA'
144: where delivery_id = p_delivery_id
145: and status_code IN ('SR','SC');
146: ELSE
146: ELSE
147: raise wsh_invalid_doc_type;
148: END IF;
149: ELSIF (p_action_code = 'D' ) THEN
150: update wsh_new_deliveries
151: set status_code ='SC'
152: where delivery_id = p_delivery_id
153: and status_code = 'SR';
154: ELSE
212: IS
213:
214: cursor del_status_cur is
215: select status_code, planned_flag
216: from wsh_new_deliveries
217: where delivery_id =p_delivery_id;
218:
219: cursor det_tpw_cur is
220: select 'X'
232: --k proj bmso
233:
234: CURSOR c_get_event_key (v_delivery_id number) is
235: select wth.event_key, wth.item_type
236: from wsh_new_deliveries wnd,
237: wsh_transactions_history wth
238: where delivery_id =v_delivery_id
239: and wnd.name = wth.entity_number
240: and wth.document_direction = 'O'
401: IS
402:
403: cursor del_cur ( p_source_line_id IN NUMBER ) is
404: select count(*)
405: from wsh_new_deliveries wnd,
406: wsh_delivery_assignments_v wda,
407: wsh_delivery_details wdd
408: where wnd.status_code in ('SR','SC')
409: --and wnd.planned_flag = 'Y'
560: select wnd.organization_id,
561: wnd.status_code,
562: wnd.planned_flag,
563: wnd.delivery_id
564: from wsh_new_deliveries wnd,
565: wsh_delivery_legs wdl,
566: wsh_trip_stops wts1,
567: wsh_trip_stops wts2,
568: wsh_trips wt
578: select wnd.organization_id,
579: wnd.status_code,
580: wnd.planned_flag,
581: wnd.delivery_id
582: from wsh_new_deliveries wnd,
583: wsh_delivery_legs wdl
584: where wnd.delivery_id = wdl.delivery_id
585: and wdl.pick_up_stop_id = p_stop_id
586: and rownum = 1;
590: select wnd.organization_id,
591: wnd.status_code,
592: wnd.planned_flag,
593: wnd.delivery_id
594: from wsh_new_deliveries wnd,
595: wsh_delivery_assignments_v wda
596: where wnd.delivery_id = wda.delivery_id
597: and wda.delivery_detail_id = p_del_det_id;
598:
599: cursor del_cur( p_delivery_id IN NUMBER ) is
600: select organization_id,
601: status_code,
602: planned_flag
603: from wsh_new_deliveries
604: where delivery_id=p_delivery_id;
605:
606:
607: -- Cursor to get the org_id for delivery_detail_id
639:
640: cursor trip_to_det_cur( p_trip_id IN NUMBER ) is
641: select distinct 'X'
642: from wsh_delivery_details wdd,
643: wsh_new_deliveries wnd,
644: wsh_delivery_assignments_v wda,
645: wsh_delivery_legs wdl,
646: wsh_trip_stops wts1,
647: wsh_trip_stops wts2,
673: from wsh_transactions_history
674: where transaction_id = (
675: select max(transaction_id)
676: from wsh_transactions_history wth,
677: wsh_new_deliveries wnd
678: where wth.entity_number = wnd.name
679: and wth.trading_partner_id = p_tp_id
680: and wnd.delivery_id = p_delivery_id
681: )