661: ol.fulfilled_quantity, ol.invoiced_quantity,
662: oh.order_number, oh.ship_from_org_id,
663: wnd.delivery_id, wnd.status_code dlvy_status_code, wts.status_code stop_status_code, wdl.delivery_leg_id
664: from wsh_delivery_details wdd,
665: oe_order_lines_all ol,
666: oe_order_headers_all oh,
667: wsh_delivery_assignments_v wda,
668: wsh_new_deliveries wnd,
669: wsh_delivery_legs wdl,
697: ol.fulfilled_quantity, ol.invoiced_quantity,
698: oh.order_number, oh.ship_from_org_id,
699: wnd.delivery_id, wnd.status_code dlvy_status_code, wts.status_code stop_status_code, wdl.delivery_leg_id
700: from wsh_delivery_details wdd,
701: oe_order_lines_all ol,
702: oe_order_headers_all oh,
703: wsh_delivery_assignments_v wda,
704: wsh_new_deliveries wnd,
705: wsh_delivery_legs wdl,
732: ol.fulfilled_quantity, ol.invoiced_quantity,
733: oh.order_number, oh.ship_from_org_id,
734: wnd.delivery_id, wnd.status_code dlvy_status_code, wts.status_code stop_status_code, wdl.delivery_leg_id
735: from wsh_delivery_details wdd,
736: oe_order_lines_all ol,
737: oe_order_headers_all oh,
738: wsh_delivery_assignments_v wda,
739: wsh_new_deliveries wnd,
740: wsh_delivery_legs wdl,
768: ol.fulfilled_quantity, ol.invoiced_quantity,
769: oh.order_number, oh.ship_from_org_id,
770: wnd.delivery_id, wnd.status_code dlvy_status_code, wts.status_code stop_status_code, wdl.delivery_leg_id
771: from wsh_delivery_details wdd,
772: oe_order_lines_all ol,
773: oe_order_headers_all oh,
774: wsh_delivery_assignments_v wda,
775: wsh_new_deliveries wnd,
776: wsh_delivery_legs wdl,
787:
788: CURSOR c2_hdr IS
789: select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
790: from wsh_delivery_details wdd,
791: oe_order_lines_all ol
792: where wdd.source_code = 'OE'
793: and wdd.source_line_id = ol.line_id
794: and nvl(wdd.ship_tolerance_above,0) = 0
795: and nvl(wdd.ship_tolerance_below,0) = 0
806:
807: CURSOR c2_line IS
808: select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
809: from wsh_delivery_details wdd,
810: oe_order_lines_all ol
811: where wdd.source_code = 'OE'
812: and wdd.source_line_id = ol.line_id
813: and nvl(wdd.ship_tolerance_above,0) = 0
814: and nvl(wdd.ship_tolerance_below,0) = 0
825:
826: CURSOR c2_dlvy IS
827: select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
828: from wsh_delivery_details wdd,
829: oe_order_lines_all ol,
830: wsh_delivery_assignments_v wda
831: where wdd.source_code = 'OE'
832: and wdd.source_line_id = ol.line_id
833: and nvl(wdd.ship_tolerance_above,0) = 0
847:
848: CURSOR c2_batch IS
849: select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
850: from wsh_delivery_details wdd,
851: oe_order_lines_all ol
852: where wdd.source_code = 'OE'
853: and wdd.source_line_id = ol.line_id
854: and nvl(wdd.ship_tolerance_above,0) = 0
855: and nvl(wdd.ship_tolerance_below,0) = 0
867:
868: CURSOR C12_hdr IS
869: select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
870: from wsh_delivery_details wdd,
871: oe_order_lines_all ol
872: where wdd.source_code = 'OE'
873: and wdd.source_header_id = p_source_header_id
874: and wdd.source_line_id = ol.line_id
875: and nvl(wdd.oe_interfaced_flag,'N') = 'N'
874: and wdd.source_line_id = ol.line_id
875: and nvl(wdd.oe_interfaced_flag,'N') = 'N'
876: and exists (
877: select 'x'
878: from oe_order_lines_all ol1
879: where ol1.ship_set_id = wdd.ship_set_id
880: and ol1.header_id = wdd.source_header_id
881: and ol1.shipped_quantity is NOT NULL
882: and ol1.flow_status_code <> 'AWAITING_SHIPPING')
886:
887: CURSOR C12_line IS
888: select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
889: from wsh_delivery_details wdd,
890: oe_order_lines_all ol
891: where wdd.source_code = 'OE'
892: and wdd.source_line_id = p_source_line_id
893: and wdd.source_line_id = ol.line_id
894: and nvl(wdd.oe_interfaced_flag,'N') = 'N'
893: and wdd.source_line_id = ol.line_id
894: and nvl(wdd.oe_interfaced_flag,'N') = 'N'
895: and exists (
896: select 'x'
897: from oe_order_lines_all ol1
898: where ol1.ship_set_id = wdd.ship_set_id
899: and ol1.header_id = wdd.source_header_id
900: and ol1.shipped_quantity is NOT NULL
901: and ol1.flow_status_code <> 'AWAITING_SHIPPING')
906: CURSOR C12_dlvy IS
907: select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
908: from wsh_delivery_details wdd,
909: wsh_delivery_assignments_v wda,
910: oe_order_lines_all ol
911: where wdd.source_code = 'OE'
912: and wdd.source_line_id = ol.line_id
913: and wdd.delivery_detail_id = wda.delivery_detail_id
914: and wda.delivery_id is not null
915: and wda.delivery_id = p_delivery_id
916: and nvl(wdd.oe_interfaced_flag,'N') = 'N'
917: and exists (
918: select 'x'
919: from oe_order_lines_all ol1
920: where ol1.ship_set_id = wdd.ship_set_id
921: and ol1.header_id = wdd.source_header_id
922: and ol1.shipped_quantity is NOT NULL
923: and ol1.flow_status_code <> 'AWAITING_SHIPPING')
927:
928: CURSOR C12_batch IS
929: select wdd.source_header_number, wdd.source_line_number, wdd.delivery_detail_id
930: from wsh_delivery_details wdd,
931: oe_order_lines_all ol
932: where wdd.source_code = 'OE'
933: and wdd.batch_id = p_batch_id
934: and wdd.source_line_id = ol.line_id
935: and nvl(wdd.oe_interfaced_flag,'N') = 'N'
934: and wdd.source_line_id = ol.line_id
935: and nvl(wdd.oe_interfaced_flag,'N') = 'N'
936: and exists (
937: select 'x'
938: from oe_order_lines_all ol1
939: where ol1.ship_set_id = wdd.ship_set_id
940: and ol1.header_id = wdd.source_header_id
941: and ol1.shipped_quantity is NOT NULL
942: and ol1.flow_status_code <> 'AWAITING_SHIPPING')
950: CURSOR C6_hdr IS
951: select oh.order_number,
952: RTRIM(ol.line_number || '.' || ol.shipment_number || '.' || ol.option_number || '.' || ol.component_number || '.' || ol.service_number, '.') line_number
953: from oe_order_headers_all oh,
954: oe_order_lines_all ol
955: where oh.header_id = ol.header_id
956: and oh.header_id = p_source_header_id
957: and ol.shipping_interfaced_flag = 'Y'
958: and nvl(ol.shippable_flag, 'N') = 'Y'
965: CURSOR C6_line IS
966: select oh.order_number,
967: RTRIM(ol.line_number || '.' || ol.shipment_number || '.' || ol.option_number || '.' || ol.component_number || '.' || ol.service_number, '.') line_number
968: from oe_order_headers_all oh,
969: oe_order_lines_all ol
970: where oh.header_id = ol.header_id
971: and ol.line_id = p_source_line_id
972: and ol.shipping_interfaced_flag = 'Y'
973: and nvl(ol.shippable_flag, 'N') = 'Y'
983: select oeh.order_number,
984: oel.line_id,
985: RTRIM(oel.line_number || '.' || oel.shipment_number || '.' || oel.option_number || '.' || oel.component_number || '.' || oel.service_number, '.') line_number
986: from oe_order_headers_all oeh,
987: oe_order_lines_all oel
988: where oeh.header_id = p_source_header_id
989: and oeh.header_id = oel.header_id
990: and oel.open_flag = 'N'
991: and nvl(oel.shippable_flag, 'N') = 'Y'
1007: select oeh.order_number,
1008: oel.line_id,
1009: RTRIM(oel.line_number || '.' || oel.shipment_number || '.' || oel.option_number || '.' || oel.component_number || '.' || oel.service_number, '.') line_number
1010: from oe_order_headers_all oeh,
1011: oe_order_lines_all oel
1012: where oel.line_id = p_source_line_id
1013: and oeh.header_id = oel.header_id
1014: and oel.open_flag = 'N'
1015: and nvl(oel.shippable_flag, 'N') = 'Y'