[Home] [Help]
304: SUM(WDD.REQUESTED_QUANTITY) - NVL(SUM(WDD.SHIPPED_QUANTITY)
305: ,0)
306: INTO TOTAL_UNSHIPPED_QUANTITY
307: FROM
308: WSH_DELIVERY_DETAILS WDD,
309: OE_ORDER_LINES_ALL L
310: WHERE WDD.SOURCE_LINE_ID = L.LINE_ID
311: AND WDD.SOURCE_CODE = 'OE'
312: AND WDD.CONTAINER_FLAG = 'N'
318: SUM(WDD.REQUESTED_QUANTITY) - NVL(SUM(WDD.SHIPPED_QUANTITY)
319: ,0)
320: INTO TOTAL_UNSHIPPED_QUANTITY
321: FROM
322: WSH_DELIVERY_DETAILS WDD
323: WHERE WDD.SOURCE_LINE_ID = C_SRC_LINE_ID
324: AND WDD.SOURCE_CODE = 'OKE';
325: END IF;
326: RETURN TOTAL_UNSHIPPED_QUANTITY;
536: SELECT
537: count(*)
538: FROM
539: WSH_DELIVERY_ASSIGNMENTS_V WDA,
540: WSH_DELIVERY_DETAILS WDD
541: WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
542: AND WDD.CONTAINER_FLAG = 'Y'
543: AND WDA.PARENT_DELIVERY_DETAIL_ID IS NULL
544: AND WDA.DELIVERY_ID is not null
564:
565: -- RTV Changes
566: CURSOR C_RTV_BILL_TO_SITE IS
567: SELECT VENDOR_SITE_ID
568: FROM WSH_DELIVERY_DETAILS WDD,
569: RCV_TRANSACTIONS_INTERFACE RTI
570: WHERE WDD.SOURCE_CODE = 'RTV'
571: AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
572: AND WDD.SOURCE_LINE_ID = RTI.INTERFACE_TRANSACTION_ID
572: AND WDD.SOURCE_LINE_ID = RTI.INTERFACE_TRANSACTION_ID
573: AND ROWNUM = 1
574: UNION
575: SELECT VENDOR_SITE_ID
576: FROM WSH_DELIVERY_DETAILS WDD,
577: RCV_TRANSACTIONS RT
578: WHERE WDD.SOURCE_CODE = 'RTV'
579: AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
580: AND WDD.DELIVERY_DETAIL_ID = RT.INTERFACE_SOURCE_LINE_ID
613: SELECT SHIP_TO_LOCATION_ID,
614: PO_SHIPMENT_LINE_ID
615: INTO BILL_TO_LOC_ID,
616: PO_SHIP_LINE_ID
617: FROM WSH_DELIVERY_DETAILS
618: WHERE SOURCE_CODE = 'RTV'
619: AND SOURCE_LINE_ID = F_OE_LINE_ID
620: AND ROWNUM = 1;
621: EXCEPTION
662: OLA.SHIP_TO_CONTACT_ID,
663: OLA.INVOICE_TO_CONTACT_ID
664: INTO OE_LINE_ID,SHP_TO_CONTACT_ID,BILL_TO_CONTACT_ID
665: FROM
666: WSH_DELIVERY_DETAILS WDD,
667: WSH_DELIVERY_ASSIGNMENTS_V WDA,
668: OE_ORDER_LINES_ALL OLA
669: WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
670: AND NVL(WDD.CONTAINER_FLAG
685: SELECT
686: WDD.SOURCE_LINE_ID
687: INTO OE_LINE_ID
688: FROM
689: WSH_DELIVERY_DETAILS WDD,
690: WSH_DELIVERY_ASSIGNMENTS_V WDA,
691: OE_ORDER_LINES_ALL OLA
692: WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
693: AND WDD.CONTAINER_FLAG = 'N'
872: NVL(HP.PERSON_PRE_NAME_ADJUNCT
873: ,HP.PERSON_TITLE) TITLE
874: INTO BILL_TO_CUST_NAME,L_PERSON_TITLE
875: FROM
876: WSH_DELIVERY_DETAILS WDD,
877: PO_VENDORS PV,
878: HZ_PARTIES HP
879: WHERE WDD.SOURCE_LINE_ID = F_OE_LINE_ID
880: AND WDD.SOURCE_CODE = 'RTV'
951: SHIP_TO_SITE_USE_ID,
952: CONSIGNEE_FLAG
953: INTO SHP_TO_SITE_USE_ID,CONS_FLAG
954: FROM
955: WSH_DELIVERY_DETAILS
956: WHERE DELIVERY_DETAIL_ID = F_DEL_DETAIL_ID
957: AND SOURCE_CODE = CP_SOURCE_CODE
958: AND ROWNUM < 2;
959:
1026: SELECT
1027: SHIP_TO_SITE_USE_ID
1028: INTO SHP_TO_SITE_USE_ID
1029: FROM
1030: WSH_DELIVERY_DETAILS
1031: WHERE DELIVERY_DETAIL_ID = F_DEL_DETAIL_ID
1032: AND SOURCE_CODE = 'OE'
1033: AND ROWNUM < 2;
1034: IF SQL%NOTFOUND THEN
1038: SELECT
1039: SHIP_TO_SITE_USE_ID
1040: INTO SHP_TO_SITE_USE_ID
1041: FROM
1042: WSH_DELIVERY_DETAILS
1043: WHERE DELIVERY_DETAIL_ID = F_DEL_DETAIL_ID
1044: AND SOURCE_CODE = 'OKE'
1045: AND ROWNUM < 2;
1046: END IF;
1057: DECLARE
1058: -- RTV Changes
1059: CURSOR C_RTV_BILL_TO_SITE IS
1060: SELECT VENDOR_SITE_ID
1061: FROM WSH_DELIVERY_DETAILS WDD,
1062: RCV_TRANSACTIONS_INTERFACE RTI
1063: WHERE WDD.SOURCE_CODE = 'RTV'
1064: AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
1065: AND WDD.SOURCE_LINE_ID = RTI.INTERFACE_TRANSACTION_ID
1065: AND WDD.SOURCE_LINE_ID = RTI.INTERFACE_TRANSACTION_ID
1066: AND ROWNUM = 1
1067: UNION
1068: SELECT VENDOR_SITE_ID
1069: FROM WSH_DELIVERY_DETAILS WDD,
1070: RCV_TRANSACTIONS RT
1071: WHERE WDD.SOURCE_CODE = 'RTV'
1072: AND WDD.SOURCE_LINE_ID = F_OE_LINE_ID
1073: AND WDD.DELIVERY_DETAIL_ID = RT.INTERFACE_SOURCE_LINE_ID
1106: SELECT SHIP_TO_LOCATION_ID,
1107: PO_SHIPMENT_LINE_ID
1108: INTO BILL_TO_LOC_ID,
1109: PO_SHIP_LINE_ID
1110: FROM WSH_DELIVERY_DETAILS
1111: WHERE SOURCE_CODE = 'RTV'
1112: AND SOURCE_LINE_ID = F_OE_LINE_ID
1113: AND ROWNUM = 1;
1114: EXCEPTION
1155: OLA.SHIP_TO_CONTACT_ID,
1156: OLA.INVOICE_TO_CONTACT_ID
1157: INTO OE_LINE_ID,SHP_TO_CONTACT_ID,BILL_TO_CONTACT_ID
1158: FROM
1159: WSH_DELIVERY_DETAILS WDD,
1160: WSH_DELIVERY_ASSIGNMENTS_V WDA,
1161: OE_ORDER_LINES_ALL OLA
1162: WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1163: AND NVL(WDD.CONTAINER_FLAG
1178: SELECT
1179: WDD.SOURCE_LINE_ID
1180: INTO OE_LINE_ID
1181: FROM
1182: WSH_DELIVERY_DETAILS WDD,
1183: WSH_DELIVERY_ASSIGNMENTS_V WDA
1184: WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1185: AND WDD.CONTAINER_FLAG = 'N'
1186: AND WDA.DELIVERY_ID = P_DELIVERY_ID
1193: ELSIF CP_SOURCE_CODE = 'RTV' THEN
1194: SELECT SOURCE_LINE_ID
1195: INTO OE_LINE_ID
1196: FROM
1197: WSH_DELIVERY_DETAILS WDD,
1198: WSH_DELIVERY_ASSIGNMENTS_V WDA
1199: WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1200: AND WDD.CONTAINER_FLAG = 'N'
1201: AND WDA.DELIVERY_ID = P_DELIVERY_ID
1226: SELECT
1227: count(distinct WDD.SOURCE_CODE)
1228: INTO L_COUNT
1229: FROM
1230: WSH_DELIVERY_DETAILS WDD,
1231: WSH_DELIVERY_ASSIGNMENTS_V WDA
1232: WHERE WDA.DELIVERY_ID IS NOT NULL
1233: AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
1234: AND WDA.DELIVERY_ID = P_DELIVERY_ID
1242: WDD.SOURCE_CODE,
1243: WDD.DELIVERY_DETAIL_ID
1244: INTO L_SOURCE_CODE,DEL_DTL_ID
1245: FROM
1246: WSH_DELIVERY_DETAILS WDD,
1247: WSH_DELIVERY_ASSIGNMENTS_V WDA
1248: WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1249: AND WDD.CONTAINER_FLAG = 'N'
1250: AND WDA.DELIVERY_ID = P_DELIVERY_ID