[Home] [Help]
550: IS
551: SELECT Det.Delivery_Detail_Id, Det.Rowid,
552: Dlvy.Customer_Id
553: FROM Wsh_Delivery_Details Det,
554: Wsh_Delivery_Assignments DelAsgn,
555: Wsh_New_Deliveries Dlvy
556: WHERE Dlvy.Delivery_Id = DelAsgn.Delivery_Id
557: AND DelAsgn.Delivery_Detail_Id = Det.Delivery_Detail_Id
558: AND Det.Container_Flag = 'Y'
557: AND DelAsgn.Delivery_Detail_Id = Det.Delivery_Detail_Id
558: AND Det.Container_Flag = 'Y'
559: AND Det.Delivery_Detail_Id IN
560: ( SELECT Asgn.Parent_Delivery_Detail_Id
561: FROM Wsh_Delivery_Assignments Asgn
562: WHERE Asgn.Parent_Delivery_Detail_Id IS NOT NULL
563: CONNECT BY PRIOR Asgn.Parent_Delivery_Detail_Id = Asgn.Delivery_Detail_Id
564: START WITH Asgn.delivery_detail_id IN
565: ( SELECT Wdd.Delivery_Detail_Id
563: CONNECT BY PRIOR Asgn.Parent_Delivery_Detail_Id = Asgn.Delivery_Detail_Id
564: START WITH Asgn.delivery_detail_id IN
565: ( SELECT Wdd.Delivery_Detail_Id
566: FROM WSH_DELIVERY_DETAILS Wdd,
567: WSH_DELIVERY_ASSIGNMENTS Wda,
568: WSH_NEW_DELIVERIES Wnd,
569: Wsh_Tmp Tmp
570: WHERE Wdd.Container_Flag = 'N'
571: AND Wdd.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
579: AND Wnd.Status_Code = 'OP'
580: AND Wnd.Delivery_Id = Tmp.Column1
581: AND exists
582: ( SELECT 'x'
583: FROM WSH_DELIVERY_ASSIGNMENTS WDA1,
584: WSH_DELIVERY_DETAILS WDD1
585: WHERE WDD1.DELIVERY_DETAIL_ID = WDA1.DELIVERY_DETAIL_ID
586: AND WDD1.Container_Flag = 'N'
587: AND WDA1.Delivery_Id = WND.Delivery_Id
602: Wdd.Organization_Id, Wdd.Inventory_Item_Id,
603: Wdd.Move_Order_Line_Id, Wdd.Released_Status,
604: Wnd.Ignore_For_Planning -- OTM R12 : unassign delivery detail
605: FROM WSH_DELIVERY_DETAILS Wdd,
606: WSH_DELIVERY_ASSIGNMENTS Wda,
607: WSH_NEW_DELIVERIES Wnd,
608: Wsh_Freight_Costs Wfc,
609: Wsh_Tmp Tmp
610: WHERE Wdd.Container_Flag = 'N'
621: AND Wnd.Status_Code = 'OP'
622: AND Wnd.Delivery_Id = Tmp.Column1
623: AND exists (
624: SELECT 'x'
625: FROM WSH_DELIVERY_ASSIGNMENTS WDA1,
626: WSH_DELIVERY_DETAILS WDD1
627: WHERE WDD1.DELIVERY_DETAIL_ID = WDA1.DELIVERY_DETAIL_ID
628: AND WDD1.Container_Flag = 'N'
629: AND WDA1.Delivery_Id = WND.Delivery_Id
792: RAISE Unassign_Del_Exp;
793: END IF;
794:
795: FORALL unassignDelCnt IN l_deliveryAssgRowidTab.FIRST..l_deliveryAssgRowidTab.LAST
796: UPDATE WSH_DELIVERY_ASSIGNMENTS
797: SET parent_delivery_detail_id = null,
798: delivery_id = null,
799: last_update_date = SYSDATE,
800: last_updated_by = fnd_global.user_id,
803: program_id = fnd_global.conc_program_id,
804: program_update_date = SYSDATE
805: WHERE rowid = l_deliveryAssgRowidTab(unassignDelCnt);
806:
807: setARMessageRowCount( 'WSH_DELIVERY_ASSIGNMENTS', SQL%ROWCOUNT );
808:
809: -- OTM R12 : unassign delivery detail
810: -- container_flag is always 'N' for rows in l_deliveryAssgRowidTab
811: --
970: l_return_status := NULL;
971:
972: Insert_Log_Table (
973: p_id_tab => l_deliveryDetailIdTab,
974: p_table_name => 'WSH_DELIVERY_ASSIGNMENTS',
975: p_req_id => p_req_id,
976: x_return_status => l_return_status );
977:
978: IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1217: CURSOR Get_Delivery_Containers
1218: IS
1219: SELECT Wdd.Delivery_Detail_Id, Wdd.Rowid
1220: FROM Wsh_Delivery_Details Wdd,
1221: Wsh_Delivery_Assignments Wda,
1222: Wsh_Tmp Tmp
1223: WHERE Wdd.Container_Flag = 'Y'
1224: AND Wdd.Delivery_Detail_Id = Wda.Parent_Delivery_Detail_Id
1225: AND Parent_Delivery_Detail_Id IS NOT NULL
1276: AND wnd.status_code = 'OP'
1277: AND NOT EXISTS
1278: (
1279: SELECT 1
1280: FROM wsh_delivery_assignments wda,
1281: wsh_delivery_details wdd
1282: WHERE wda.delivery_id = wnd.delivery_id
1283: AND wda.delivery_detail_id = wdd.delivery_detail_id
1284: AND wdd.container_flag = 'N'
2208: WHERE nvl(Wnd.Customer_Id, p_customer_id) = p_customer_id
2209: AND Wnd.Ultimate_Dropoff_Location_Id = p_location_id
2210: AND Wnd.Status_Code = 'OP'
2211: AND NOT EXISTS
2212: ( SELECT 'x' FROM Wsh_Delivery_Assignments Wda
2213: WHERE Wda.Delivery_Id = Wnd.Delivery_Id )
2214: FOR UPDATE OF Wnd.Delivery_Id NOWAIT;
2215:
2216: -- Cursor to fetch deliveries which contains only containers under it.
2220: IS
2221: SELECT Det.Delivery_Detail_Id, Dlvy.Delivery_Id,
2222: Det.Rowid, Dlvy.Rowid
2223: FROM Wsh_Delivery_Details Det,
2224: Wsh_Delivery_Assignments Asg,
2225: Wsh_New_Deliveries Dlvy
2226: WHERE Det.Delivery_Detail_Id = Asg.Delivery_Detail_Id
2227: AND Asg.Delivery_Id = Dlvy.Delivery_Id
2228: AND Dlvy.Delivery_Id in
2227: AND Asg.Delivery_Id = Dlvy.Delivery_Id
2228: AND Dlvy.Delivery_Id in
2229: ( SELECT Wnd.Delivery_Id
2230: FROM Wsh_Delivery_Details Wdd,
2231: Wsh_Delivery_Assignments Wda,
2232: Wsh_New_Deliveries Wnd
2233: WHERE Wdd.Container_Flag = 'Y'
2234: AND Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_id
2235: AND Wda.Delivery_Id = Wnd.Delivery_Id
2237: AND Wnd.Ultimate_Dropoff_Location_Id = p_location_id
2238: AND Wnd.Status_Code = 'OP'
2239: AND NOT EXISTS
2240: ( SELECT 'X'
2241: FROM Wsh_Delivery_Assignments Asgn
2242: WHERE Asgn.Parent_Delivery_Detail_Id = Wdd.Delivery_Detail_Id
2243: AND Asgn.Delivery_Id = Wnd.Delivery_Id )
2244: AND NOT EXISTS
2245: ( SELECT 'X'
2243: AND Asgn.Delivery_Id = Wnd.Delivery_Id )
2244: AND NOT EXISTS
2245: ( SELECT 'X'
2246: FROM Wsh_Delivery_Details dd,
2247: Wsh_Delivery_Assignments da
2248: WHERE dd.Container_Flag = 'N'
2249: AND dd.Delivery_Detail_Id = da.Delivery_Detail_Id
2250: AND da.Delivery_Id = Wnd.Delivery_Id ) )
2251: FOR UPDATE OF Det.Delivery_Detail_Id, Dlvy.Delivery_Id NOWAIT;
2500: WDD.Volume, Wdd.Weight_Uom_code, Wdd.Volume_Uom_Code,
2501: Wdd.Inventory_Item_Id, Wdd.Organization_Id,
2502: WDD.Rowid, WDA.Rowid
2503: FROM Wsh_Delivery_Details WDD,
2504: Wsh_Delivery_Assignments WDA,
2505: Wsh_New_Deliveries WND,
2506: Wsh_Delivery_Legs WDL,
2507: Wsh_Trip_Stops WTS
2508: WHERE WTS.Stop_id (+) = WDL.Drop_Off_Stop_Id
2680:
2681: -- Unassign delivery details from containers if it is packed and not
2682: -- assigned to a delivery.
2683: FORALL unassignCnt IN l_deliveryAssgRowidTab.FIRST..l_deliveryAssgRowidTab.LAST
2684: UPDATE WSH_DELIVERY_ASSIGNMENTS
2685: SET parent_delivery_detail_id = null,
2686: last_update_date = SYSDATE,
2687: last_updated_by = arp_standard.profile.user_id,
2688: last_update_login = arp_standard.profile.last_update_login,
2693: AND Parent_Delivery_Detail_Id IS NOT NULL
2694: AND Delivery_Id IS NULL;
2695:
2696: --
2697: setARMessageRowCount( 'WSH_DELIVERY_ASSIGNMENTS', SQL%ROWCOUNT );
2698:
2699: l_return_status := NULL;
2700: l_dummyIdTab.delete;
2701:
2916: FROM Wsh_Delivery_Details Det
2917: WHERE Det.Container_Flag = 'Y'
2918: AND Det.Delivery_Detail_Id IN
2919: ( SELECT Wda.Parent_Delivery_Detail_Id
2920: FROM Wsh_Delivery_Assignments Wda
2921: WHERE Wda.Parent_Delivery_Detail_Id IS NOT NULL
2922: CONNECT BY PRIOR Wda.Parent_Delivery_Detail_Id = Wda.Delivery_Detail_Id
2923: START WITH wda.delivery_detail_id IN
2924: ( SELECT WDD.Delivery_Detail_Id
2922: CONNECT BY PRIOR Wda.Parent_Delivery_Detail_Id = Wda.Delivery_Detail_Id
2923: START WITH wda.delivery_detail_id IN
2924: ( SELECT WDD.Delivery_Detail_Id
2925: FROM Wsh_Delivery_Details WDD,
2926: Wsh_Delivery_Assignments WDA,
2927: Wsh_New_Deliveries WND
2928: WHERE Wnd.Status_Code in ( 'CO', 'CL', 'IT' )
2929: -- Added Parent_Delivery_Detail_Id for Perf. improvement,
2930: -- as per perf. team suggestion.
2947: p_location_id NUMBER )
2948: IS
2949: SELECT Wdd.Delivery_Detail_Id, Wdd.Rowid
2950: FROM Wsh_Delivery_Details WDD,
2951: Wsh_Delivery_Assignments WDA,
2952: Wsh_New_Deliveries WND
2953: WHERE Wnd.Status_Code in ( 'CO', 'CL', 'IT' )
2954: AND Wnd.Delivery_Id = Wda.Delivery_Id
2955: AND Wda.Delivery_Detail_Id = Wdd.Delivery_Detail_Id
3746: SELECT 1 FROM DUAL
3747: WHERE EXISTS
3748: ( SELECT 'x'
3749: FROM wsh_delivery_details wdd,
3750: wsh_delivery_assignments wda,
3751: ra_customer_merges rcm,
3752: mtl_parameters mtl
3753: WHERE mtl.wms_enabled_flag = 'Y'
3754: AND mtl.organization_id = wdd.organization_id