[Home] [Help]
205: THEN
206: l_entity_name := 'WSH_NEW_DELIVERIES';
207: ELSIF p_document_type = 'BOL'
208: THEN
209: l_entity_name := 'WSH_DELIVERY_LEGS';
210: ELSIF p_document_type = 'MBOL'
211: THEN
212: l_entity_name := 'WSH_TRIPS';
213: END IF;
252: CURSOR delivery_leg_csr IS
253: SELECT
254: delivery_leg_id
255: FROM
256: wsh_delivery_legs
257: WHERE delivery_id = p_delivery_id
258: AND pick_up_stop_id = p_pick_up_stop_id
259: AND drop_off_stop_id = p_drop_off_stop_id;
260: --
899: -- p_init_msg_list should API reset message stack (default: false)
900: -- p_commit should API do a commit (default: false)
901: -- p_validation_level extent of validation done in the API (not used)
902: -- p_entity_name Entity for which the document is being created
903: -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
904: -- p_entity_id Entity id that the document belongs to
905: -- example: delivery_id, delivery_leg_id, etc
906: -- p_application_id Application which is creating the document (
907: -- should be same as the one that owns the
1014: delivery_id
1015: , pick_up_stop_id
1016: , drop_off_stop_id
1017: FROM
1018: wsh_delivery_legs
1019: WHERE delivery_leg_id = p_entity_id;
1020:
1021:
1022: CURSOR document_csr ( c_entity_name IN VARCHAR2
1088: CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
1089: SELECT
1090: delivery_id
1091: FROM
1092: wsh_delivery_legs
1093: WHERE delivery_leg_id = c_delivery_leg_id;
1094:
1095: --
1096: --Bug 4284167 (FP Bug 4149501)
1096: --Bug 4284167 (FP Bug 4149501)
1097: --
1098: CURSOR get_lock_on_leg(p_delivery_leg_id IN NUMBER) IS
1099: SELECT p_delivery_leg_id
1100: FROM wsh_delivery_legs
1101: WHERE delivery_leg_id = p_delivery_leg_id
1102: FOR UPDATE NOWAIT;
1103:
1104: document_rec document_csr%rowtype;
1198: -- to be used to build the child delivery table later --
1199: -------------------------------------------------------------
1200:
1201: l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
1202: IF l_entity_name = 'WSH_DELIVERY_LEGS'
1203: THEN
1204: OPEN delivery_id_csr (p_entity_id);
1205: FETCH delivery_id_csr INTO delivery_id_rec;
1206: l_delivery_id := delivery_id_rec.delivery_id;
1213: ----------------------------------------------
1214: -- if the document is for delivery leg --
1215: -- get its pick up and drop off trip stops --
1216: ----------------------------------------------
1217: IF l_entity_name = 'WSH_DELIVERY_LEGS'
1218: THEN
1219: OPEN trip_stop_csr;
1220: FETCH trip_stop_csr INTO trip_stop_rec;
1221: CLOSE trip_stop_csr;
1265: -- For each delivery, if the docuement is required for --
1266: -- delivery leg entity then identify the delivery leg id --
1267: -----------------------------------------------------------
1268:
1269: IF l_entity_name = 'WSH_DELIVERY_LEGS'
1270: THEN
1271:
1272: l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
1273: , trip_stop_rec.pick_up_stop_id
1285: IF document_csr%ISOPEN
1286: THEN
1287: CLOSE document_csr;
1288: END IF;
1289: IF l_entity_name = 'WSH_DELIVERY_LEGS'
1290: THEN
1291: OPEN document_csr ( l_entity_name
1292: , l_delivery_leg_id
1293: ) ;
1388: -- The decode statements that were in the values clause of the coming insert stmt
1389: -- have been modified to make use of local variables for performance reasons.
1390: --
1391:
1392: IF l_entity_name = 'WSH_DELIVERY_LEGS' THEN
1393: l_status := 'PLANNED';
1394: l_entity_id := l_delivery_leg_id;
1395: ELSIF l_entity_name ='WSH_NEW_DELIVERIES' THEN
1396: l_status := 'OPEN';
1409: -- then for child deliveries --
1410: -- entity_name is WSH_NEW_DELIVERIES --
1411: -- entity_id is child delivery_id --
1412: -- if the entity is delivery leg ( bill of lading ) --
1413: -- then entity_name is WSH_DELIVERY_LEGS --
1414: -- and entity_id is delivery_leg_id --
1415: -- and if there is consolidation --
1416: -- then for child deliveries --
1417: -- entity_name is WSH_DELIVERY_LEGS --
1413: -- then entity_name is WSH_DELIVERY_LEGS --
1414: -- and entity_id is delivery_leg_id --
1415: -- and if there is consolidation --
1416: -- then for child deliveries --
1417: -- entity_name is WSH_DELIVERY_LEGS --
1418: -- entity_id is the delivery_leg_id --
1419: -- that corresponds to the --
1420: -- child delivery_id, --
1421: -- master pick_up_stop_id, --
1502: --
1503: --Bug 4284167 (FP Bug 4149501)ISSUED DATE OF A BOL IS NOT GETTING SYSTEM GENERATED.
1504: --
1505:
1506: IF l_entity_name = 'WSH_DELIVERY_LEGS' THEN
1507:
1508: OPEN get_lock_on_leg(l_delivery_leg_id);
1509:
1510: FETCH get_lock_on_leg INTO l_delivery_leg_id;
1508: OPEN get_lock_on_leg(l_delivery_leg_id);
1509:
1510: FETCH get_lock_on_leg INTO l_delivery_leg_id;
1511: IF (get_lock_on_leg%FOUND) THEN
1512: UPDATE wsh_delivery_legs
1513: SET doc_date_issued = SYSDATE
1514: WHERE current of get_lock_on_leg;
1515: END IF;
1516: CLOSE get_lock_on_leg;
1684: END IF;
1685: ROLLBACK to WSH_Document_PVT;
1686:
1687: SELECT wnd.name INTO l_delivery_name
1688: FROM wsh_new_deliveries wnd, wsh_delivery_legs wdl
1689: WHERE wnd.delivery_id = wdl.delivery_id
1690: AND wdl.delivery_leg_id = l_delivery_leg_id;
1691:
1692: FND_MESSAGE.SET_NAME('WSH',' WSH_DLVY_DEL_LEG_LOCK');
1776: -- p_init_msg_list should API reset message stack (default: false)
1777: -- p_commit should API do a commit (default: false)
1778: -- p_validation_level extent of validation done in the API (not used)
1779: -- p_entity_name Entity for which the document is being updated
1780: -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
1781: -- p_entity_id Entity id that the document belongs to
1782: -- example: delivery_id, delivery_leg_id, etc
1783: -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
1784: -- p_pod_flag pod_flag for the document
1850: L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Document';
1851: L_API_VERSION CONSTANT NUMBER := 1.0;
1852: l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
1853: l_delivery_id wsh_new_deliveries.delivery_id%type;
1854: l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
1855: l_table_count NUMBER;
1856: l_entity_name wsh_document_instances.entity_name%type;
1857:
1858: CURSOR old_values_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
1873: delivery_id
1874: , pick_up_stop_id
1875: , drop_off_stop_id
1876: FROM
1877: wsh_delivery_legs
1878: WHERE delivery_leg_id = p_entity_id;
1879:
1880: CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
1881: SELECT
1880: CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
1881: SELECT
1882: delivery_id
1883: FROM
1884: wsh_delivery_legs
1885: WHERE delivery_leg_id = c_delivery_leg_id;
1886:
1887: old_values_rec old_values_csr%rowtype;
1888: trip_stop_rec trip_stop_csr%rowtype;
1947: -- to be used to build the child delivery table later --
1948: -------------------------------------------------------------
1949:
1950: l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
1951: IF l_entity_name = 'WSH_DELIVERY_LEGS'
1952: THEN
1953: OPEN delivery_id_csr (p_entity_id);
1954: FETCH delivery_id_csr INTO delivery_id_rec;
1955: l_delivery_id := delivery_id_rec.delivery_id;
1963: -- if the document is for delivery leg --
1964: -- get its pick up and drop off trip stops --
1965: ----------------------------------------------
1966:
1967: IF l_entity_name = 'WSH_DELIVERY_LEGS'
1968: THEN
1969: OPEN trip_stop_csr;
1970: FETCH trip_stop_csr INTO trip_stop_rec;
1971: CLOSE trip_stop_csr;
1999: -- For each delivery, if the docuement relates to --
2000: -- delivery leg entity then identify the delivery leg id --
2001: -----------------------------------------------------------
2002:
2003: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2004: THEN
2005: l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2006: , trip_stop_rec.pick_up_stop_id
2007: , trip_stop_rec.drop_off_stop_id
2011: IF old_values_csr%ISOPEN
2012: THEN
2013: CLOSE old_values_csr;
2014: END IF;
2015: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2016: THEN
2017: OPEN old_values_csr (l_entity_name, l_delivery_leg_id);
2018: ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2019: THEN
2129: -- p_init_msg_list should API reset message stack (default: false)
2130: -- p_commit should API do a commit (default: false)
2131: -- p_validation_level extent of validation done in the API (not used)
2132: -- p_entity_name Entity for which the document is being cancelled
2133: -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
2134: -- p_entity_id Entity id that the document belongs to
2135: -- example: delivery_id, delivery_leg_id, etc
2136: -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
2137: -- p_consolidate_option calling program's choice to cancel document(s)
2170: l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
2171: l_table_count NUMBER;
2172: l_entity_name wsh_document_instances.entity_name%type;
2173: l_delivery_id wsh_new_deliveries.delivery_id%type;
2174: l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
2175:
2176:
2177: -------------------------------------------------------------------------
2178: -- cursor to fetch the current document (in PLAN/OPEN status) of --
2198: delivery_id
2199: , pick_up_stop_id
2200: , drop_off_stop_id
2201: FROM
2202: wsh_delivery_legs
2203: WHERE delivery_leg_id = p_entity_id;
2204:
2205: CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2206: SELECT
2205: CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2206: SELECT
2207: delivery_id
2208: FROM
2209: wsh_delivery_legs
2210: WHERE delivery_leg_id = c_delivery_leg_id;
2211:
2212: trip_stop_rec trip_stop_csr%rowtype;
2213: delivery_id_rec delivery_id_csr%rowtype;
2272: l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
2273:
2274: IF l_entity_name <> 'WSH_TRIPS' THEN
2275:
2276: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2277: THEN
2278: OPEN delivery_id_csr (p_entity_id);
2279: FETCH delivery_id_csr INTO delivery_id_rec;
2280: l_delivery_id := delivery_id_rec.delivery_id;
2288: -- if the document is for delivery leg --
2289: -- get its pick up and drop off trip stops --
2290: ----------------------------------------------
2291:
2292: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2293: THEN
2294: OPEN trip_stop_csr;
2295: FETCH trip_stop_csr INTO trip_stop_rec;
2296: CLOSE trip_stop_csr;
2324: -- For each delivery, if the docuement relates to --
2325: -- delivery leg entity then identify the delivery leg id --
2326: -----------------------------------------------------------
2327:
2328: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2329: THEN
2330: l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2331: , trip_stop_rec.pick_up_stop_id
2332: , trip_stop_rec.drop_off_stop_id
2336: IF status_csr%ISOPEN
2337: THEN
2338: CLOSE status_csr;
2339: END IF;
2340: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2341: THEN
2342: OPEN status_csr (l_entity_name, l_delivery_leg_id);
2343: ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2344: THEN
2456: -- p_init_msg_list should API reset message stack (default: false)
2457: -- p_commit should API do a commit (default: false)
2458: -- p_validation_level extent of validation done in the API (not used)
2459: -- p_entity_name Entity for which the document is being opened
2460: -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
2461: -- p_entity_id Entity id that the document belongs to
2462: -- example: delivery_id, delivery_leg_id, etc
2463: -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
2464: -- p_consolidate_option calling program's choice to open document(s)
2497: l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
2498: l_table_count NUMBER;
2499: l_entity_name wsh_document_instances.entity_name%type;
2500: l_delivery_id wsh_new_deliveries.delivery_id%type;
2501: l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
2502:
2503: CURSOR status_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
2504: SELECT
2505: entity_id
2517: delivery_id
2518: , pick_up_stop_id
2519: , drop_off_stop_id
2520: FROM
2521: wsh_delivery_legs
2522: WHERE delivery_leg_id = p_entity_id;
2523:
2524: CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2525: SELECT
2524: CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2525: SELECT
2526: delivery_id
2527: FROM
2528: wsh_delivery_legs
2529: WHERE delivery_leg_id = c_delivery_leg_id;
2530:
2531: trip_stop_rec trip_stop_csr%rowtype;
2532: delivery_id_rec delivery_id_csr%rowtype;
2588: -- to be used to build the child delivery table later --
2589: -------------------------------------------------------------
2590:
2591: l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
2592: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2593: THEN
2594: OPEN delivery_id_csr (p_entity_id);
2595: FETCH delivery_id_csr INTO delivery_id_rec;
2596: l_delivery_id := delivery_id_rec.delivery_id;
2604: -- if the document is for delivery leg --
2605: -- get its pick up and drop off trip stops --
2606: ----------------------------------------------
2607:
2608: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2609: THEN
2610: OPEN trip_stop_csr;
2611: FETCH trip_stop_csr INTO trip_stop_rec;
2612: CLOSE trip_stop_csr;
2640: -- For each delivery, if the docuement relates to --
2641: -- delivery leg entity then identify the delivery leg id --
2642: -----------------------------------------------------------
2643:
2644: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2645: THEN
2646: l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2647: , trip_stop_rec.pick_up_stop_id
2648: , trip_stop_rec.drop_off_stop_id
2652: IF status_csr%ISOPEN
2653: THEN
2654: CLOSE status_csr;
2655: END IF;
2656: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2657: THEN
2658: OPEN status_csr (l_entity_name, l_delivery_leg_id);
2659: ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2660: THEN
2759: -- p_init_msg_list should API reset message stack (default: false)
2760: -- p_commit should API do a commit (default: false)
2761: -- p_validation_level extent of validation done in the API (not used)
2762: -- p_entity_name Entity for which the document is being completed
2763: -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
2764: -- p_entity_id Entity id that the document belongs to
2765: -- example: delivery_id, delivery_leg_id, etc
2766: -- p_document_type document type codes (PACK_TYPE, BOL, ASN etc.)
2767: -- p_consolidate_option calling program's choice to complete document(s)
2801: l_delivery_id_tab delivery_id_tabtype := delivery_id_tabtype();
2802: l_table_count NUMBER;
2803: l_entity_name wsh_document_instances.entity_name%type;
2804: l_delivery_id wsh_new_deliveries.delivery_id%type;
2805: l_delivery_leg_id wsh_delivery_legs.delivery_leg_id%type;
2806:
2807: CURSOR status_csr (c_entity_name IN VARCHAR2, c_entity_id IN NUMBER) IS
2808: SELECT
2809: entity_id
2821: delivery_id
2822: , pick_up_stop_id
2823: , drop_off_stop_id
2824: FROM
2825: wsh_delivery_legs
2826: WHERE delivery_leg_id = p_entity_id;
2827:
2828: CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2829: SELECT
2828: CURSOR delivery_id_csr (c_delivery_leg_id IN NUMBER) IS
2829: SELECT
2830: delivery_id
2831: FROM
2832: wsh_delivery_legs
2833: WHERE delivery_leg_id = c_delivery_leg_id;
2834:
2835: trip_stop_rec trip_stop_csr%rowtype;
2836: delivery_id_rec delivery_id_csr%rowtype;
2892: -- to be used to build the child delivery table later --
2893: -------------------------------------------------------------
2894:
2895: l_entity_name := Init_Entity_Name (p_document_type, p_entity_name);
2896: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2897: THEN
2898: OPEN delivery_id_csr (p_entity_id);
2899: FETCH delivery_id_csr INTO delivery_id_rec;
2900: l_delivery_id := delivery_id_rec.delivery_id;
2908: -- if the document is for delivery leg --
2909: -- get its pick up and drop off trip stops --
2910: ----------------------------------------------
2911:
2912: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2913: THEN
2914: OPEN trip_stop_csr;
2915: FETCH trip_stop_csr INTO trip_stop_rec;
2916: CLOSE trip_stop_csr;
2944: -- For each delivery, if the docuement relates to --
2945: -- delivery leg entity then identify the delivery leg id --
2946: -----------------------------------------------------------
2947:
2948: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2949: THEN
2950: l_delivery_leg_id := Get_Delivery_Leg_Id ( l_delivery_id_tab(ctr)
2951: , trip_stop_rec.pick_up_stop_id
2952: , trip_stop_rec.drop_off_stop_id
2956: IF status_csr%ISOPEN
2957: THEN
2958: CLOSE status_csr;
2959: END IF;
2960: IF l_entity_name = 'WSH_DELIVERY_LEGS'
2961: THEN
2962: OPEN status_csr (l_entity_name, l_delivery_leg_id);
2963: ELSIF l_entity_name = 'WSH_NEW_DELIVERIES'
2964: THEN
3546: -- p_init_msg_list should API reset message stack (default: false)
3547: -- p_commit should API do a commit (default: false)
3548: -- p_validation_level extent of validation done in the API (not used)
3549: -- p_entity_name Entity for which the document is being cancelled
3550: -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
3551: -- p_entity_id Entity id that the document belongs to
3552: -- example: delivery_id, delivery_leg_id, etc
3553: --
3554: -- OUT
3714: -- p_init_msg_list should API reset message stack (default: false)
3715: -- p_commit should API do a commit (default: false)
3716: -- p_validation_level extent of validation done in the API (not used)
3717: -- p_entity_name Entity for which the document is being cancelled
3718: -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
3719: -- p_entity_id Entity id that the document belongs to
3720: -- example: delivery_id, delivery_leg_id, etc
3721:
3722: -- OUT
3959: -- p_sequence_number sequence number of the document
3960: -- p_status status of the document
3961: -- p_final_print_date final print date
3962: -- p_entity_name Entity for which the document is being updated
3963: -- examples: WSH_NEW_DELIVERIES, WSH_DELIVERY_LEGS
3964: -- p_entity_id Entity id that the document belongs to
3965: -- example: delivery_id, delivery_leg_id, etc
3966: -- p_doc_sequence_category_id document sequence category id
3967: -- p_pod_flag pod_flag for the document