[Home] [Help]
248: l_search_method varchar2(30) := fnd_profile.value('CSP_PART_SEARCH_METHOD_SCHEDULER');
249:
250: cursor get_line_id is
251: select distinct(crl.requirement_line_id) req_line_id
252: from csp_requirement_lines crl,csp_requirement_headers crh
253: where crh.task_id = p_task_id
254: and crl.requirement_header_id = crh.requirement_header_id;
255:
256: cursor get_asgn_id is
341: l_partial_line := 0;
342: SELECT count(l.requirement_line_id)
343: into l_partial_line
344: FROM csp_requirement_headers h,
345: csp_requirement_lines l,
346: csp_req_line_details d,
347: oe_order_lines_all oola
348: WHERE h.task_id = p_task_id
349: AND h.task_assignment_id = r_asgn.task_assignment_id
375: l_fl_rcvd_lines := 0;
376: SELECT COUNT(l.requirement_line_id)
377: into l_fl_rcvd_lines
378: FROM csp_requirement_headers h,
379: csp_requirement_lines l,
380: csp_req_line_details dio,
381: csp_req_line_details dres,
382: oe_order_lines_all oola,
383: mtl_reservations mr
404: if l_fl_rcvd_lines > 0 then
405: SELECT ooha.order_number
406: into l_order_number
407: FROM csp_requirement_headers h,
408: csp_requirement_lines l,
409: csp_req_line_details dio,
410: oe_order_lines_all oola,
411: oe_order_headers_all ooha
412: WHERE h.task_id = p_task_id
433: l_fl_rcvd_lines := 0;
434: SELECT COUNT(l.requirement_line_id)
435: into l_fl_rcvd_lines
436: FROM csp_requirement_headers h,
437: csp_requirement_lines l,
438: csp_req_line_details dio,
439: csp_req_line_details dres,
440: oe_order_lines_all oola,
441: mtl_reservations mr
465: l_fl_rcvd_multi_source := 0;
466: SELECT COUNT(l.requirement_line_id)
467: into l_fl_rcvd_multi_source
468: FROM csp_requirement_headers h,
469: csp_requirement_lines l,
470: csp_req_line_details dio,
471: csp_req_line_details dres,
472: csp_req_line_details dother,
473: oe_order_lines_all oola,
511: into l_oth_req_line
512: FROM
513: (SELECT l.requirement_line_id
514: FROM csp_requirement_headers h,
515: csp_requirement_lines l,
516: csp_req_line_details d
517: WHERE h.task_id = p_task_id
518: AND h.task_assignment_id = r_asgn.task_assignment_id
519: AND h.requirement_header_id = l.requirement_header_id
521: AND l.requirement_line_id = d.requirement_line_id
522: MINUS
523: SELECT l.requirement_line_id
524: FROM csp_requirement_headers h,
525: csp_requirement_lines l,
526: csp_req_line_details dio,
527: csp_req_line_details dres,
528: oe_order_lines_all oola,
529: mtl_reservations mr
564: into l_non_src_line
565: FROM
566: (SELECT l.requirement_line_id
567: FROM csp_requirement_headers h,
568: csp_requirement_lines l
569: WHERE h.task_id = p_task_id
570: AND h.task_assignment_id = r_asgn.task_assignment_id
571: AND h.requirement_header_id = l.requirement_header_id
572: AND h.address_type IN ('T', 'C', 'P')
575: WHERE d.requirement_line_id = l.requirement_line_id) = 0
576: MINUS
577: SELECT l.requirement_line_id
578: FROM csp_requirement_headers h,
579: csp_requirement_lines l,
580: csp_req_line_details dio,
581: csp_req_line_details dres,
582: oe_order_lines_all oola,
583: mtl_reservations mr
627: l_shpd_lines := 0;
628: SELECT COUNT(l.requirement_line_id)
629: into l_shpd_lines
630: FROM csp_requirement_headers h,
631: csp_requirement_lines l,
632: csp_req_line_details d,
633: oe_order_lines_all oola
634: WHERE h.task_id = p_task_id
635: AND h.task_assignment_id = r_asgn.task_assignment_id
663: if l_tech_spec_pr > 0 then
664: SELECT ooha.order_number
665: INTO l_order_number
666: FROM csp_requirement_headers h,
667: csp_requirement_lines l,
668: csp_req_line_details d,
669: oe_order_lines_all oola,
670: oe_order_headers_all ooha
671: WHERE h.task_assignment_id = r_asgn.task_assignment_id
691: l_ship_multi_src := 0;
692: SELECT COUNT(l.requirement_line_id)
693: into l_ship_multi_src
694: FROM csp_requirement_headers h,
695: csp_requirement_lines l,
696: csp_req_line_details d,
697: csp_req_line_details dother,
698: oe_order_lines_all oola
699: WHERE h.task_id = p_task_id
729: into l_oth_req_line
730: FROM
731: (SELECT l.requirement_line_id
732: FROM csp_requirement_headers h,
733: csp_requirement_lines l,
734: csp_req_line_details d
735: WHERE h.task_id = p_task_id
736: AND h.task_assignment_id = r_asgn.task_assignment_id
737: AND h.requirement_header_id = l.requirement_header_id
738: AND l.requirement_line_id = d.requirement_line_id
739: MINUS
740: SELECT l.requirement_line_id
741: FROM csp_requirement_headers h,
742: csp_requirement_lines l,
743: csp_req_line_details d,
744: oe_order_lines_all oola
745: WHERE h.task_id = p_task_id
746: AND h.task_assignment_id = r_asgn.task_assignment_id
773: into l_non_src_line
774: FROM
775: (SELECT l.requirement_line_id
776: FROM csp_requirement_headers h,
777: csp_requirement_lines l
778: WHERE h.task_id = p_task_id
779: AND h.task_assignment_id = r_asgn.task_assignment_id
780: AND h.requirement_header_id = l.requirement_header_id
781: AND (SELECT COUNT (d.requirement_line_id)
783: WHERE d.requirement_line_id = l.requirement_line_id) = 0
784: MINUS
785: SELECT l.requirement_line_id
786: FROM csp_requirement_headers h,
787: csp_requirement_lines l,
788: csp_req_line_details d,
789: oe_order_lines_all oola
790: WHERE h.task_id = p_task_id
791: AND h.task_assignment_id = r_asgn.task_assignment_id
806: l_old_arrival_date := null;
807: SELECT MAX(oel.request_date)
808: into l_old_arrival_date
809: FROM csp_requirement_headers h,
810: csp_requirement_lines l,
811: csp_req_line_details d,
812: oe_order_lines_all oel
813: WHERE h.task_id = p_task_id
814: AND h.task_assignment_id = r_asgn.task_assignment_id
892: l_old_arrival_date := null;
893: SELECT MAX(oel.request_date)
894: into l_old_arrival_date
895: FROM csp_requirement_headers h,
896: csp_requirement_lines l,
897: csp_req_line_details d,
898: oe_order_lines_all oel
899: WHERE h.task_id = p_task_id
900: AND h.task_assignment_id = r_asgn.task_assignment_id
1268: crh.destination_subinventory,
1269: crh.ship_to_location_id
1270: from csp_available_parts_temp capt,
1271: org_organization_definitions ood,
1272: csp_requirement_lines crl,
1273: csp_requirement_headers crh,
1274: mtl_system_items_b msib
1275: where ood.organization_id = capt.organization_id
1276: and crl.requirement_header_id = crh.requirement_header_id
1295: min_cost NUMBER ;
1296: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1297: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
1298: l_org_ship_methode org_ship_methodes_tbl_type ;
1299: -- l_Requirement_Line_Tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
1300: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
1301: l_requirement_header CSP_Requirement_Headers_PVT.REQUIREMENT_HEADER_Rec_Type;
1302: l_parts_header csp_parts_requirement.Header_rec_type;
1303: l_parts_lines csp_parts_requirement.Line_Tbl_type;
1389: l_fl_rcvd_lines := 0;
1390: SELECT COUNT(l.requirement_line_id)
1391: into l_fl_rcvd_lines
1392: FROM csp_requirement_headers h,
1393: csp_requirement_lines l,
1394: csp_req_line_details dio,
1395: csp_req_line_details dres,
1396: oe_order_lines_all oola,
1397: mtl_reservations mr
1442: l_shpd_lines := 0;
1443: SELECT COUNT(l.requirement_line_id)
1444: into l_shpd_lines
1445: FROM csp_requirement_headers h,
1446: csp_requirement_lines l,
1447: csp_req_line_details d,
1448: oe_order_lines_all oola
1449: WHERE h.task_id = p_task_id
1450: AND h.task_assignment_id = r_asgn.task_assignment_id
2083: l_requirement_header.DESTINATION_ORGANIZATION_ID := l_eligible_resources_list(I).source_org;
2084: l_requirement_header.DESTINATION_SUBINVENTORY := l_eligible_resources_list(I).sub_inventory;
2085:
2086: l_req_line_details_tbl.extend;
2087: select CSP_REQUIREMENT_LINES_S1.nextval INTO l_req_line_details_tbl(l_req_line_details_tbl.count).req_line_detail_id from dual;
2088: l_req_line_details_tbl(l_req_line_details_tbl.count).requirement_line_id := l_eligible_resources_list(I).line_id ;
2089: l_req_line_details_tbl(l_req_line_details_tbl.count).source_type := 'RES' ;
2090: l_req_line_details_tbl(l_req_line_details_tbl.count).source_id := l_reservation_id ;
2091: ELSE
2104: l_parts_lines(rec_count).reservation_id := l_reservation_id;
2105: l_parts_lines(rec_count).requirement_line_id := l_requirements_line_id;
2106: rec_count := rec_count + 1;
2107: l_req_line_details_tbl.extend;
2108: select CSP_REQUIREMENT_LINES_S1.nextval INTO l_req_line_details_tbl(l_req_line_details_tbl.count).req_line_detail_id from dual;
2109: l_req_line_details_tbl(l_req_line_details_tbl.count).requirement_line_id := l_eligible_resources_list(I).line_id ;
2110: l_req_line_details_tbl(l_req_line_details_tbl.count).source_type := 'IO' ;
2111: END IF;
2112: END LOOP;
2151: EXIT;
2152: END IF;
2153: END LOOP;
2154: END LOOP;
2155: --CSP_Requirement_Lines_PVT.Update_requirement_lines(
2156: --P_Api_Version_Number => 1.0,
2157: --P_Init_Msg_List => FND_API.G_FALSE,
2158: --P_Commit => FND_API.G_FALSE,
2159: --p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2225: ,x_msg_count OUT NOCOPY NUMBER) IS
2226:
2227: CURSOR cancel_reserv IS
2228: SELECT crl.RESERVATION_ID,crl.LOCAL_RESERVATION_ID,crl.REQUIREMENT_LINE_ID
2229: FROM CSP_REQUIREMENT_LINES crl, csp_requirement_headers crh
2230: WHERE crh.task_assignment_id = p_task_assignment_id
2231: and crl.REQUIREMENT_HEADER_ID = crh.requirement_header_id
2232: AND crl.local_RESERVATION_ID IS NOT NULL;
2233:
2232: AND crl.local_RESERVATION_ID IS NOT NULL;
2233:
2234: cursor cancel_order IS
2235: select distinct oeh.header_id,crl.requirement_line_id
2236: from oe_order_lines_all oel, oe_order_headers_all oeh, csp_requirement_headers crh, csp_requirement_lines crl
2237: where crh.task_assignment_id = p_task_assignment_id
2238: and crl.REQUIREMENT_HEADER_ID = crh.REQUIREMENT_HEADER_ID
2239: and oel.line_id = crl.order_line_id
2240: and oeh.header_id = oel.header_id
2249: l_msg_count NUMBER;
2250: l_order_line_id NUMBER;
2251: l_previous_order_id NUMBER := 0;
2252: l_requirement_line_id NUMBER;
2253: l_Requirement_Line_Tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
2254: l_Requirement_Line_Tbl_order CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
2255: req_loop NUMBER := 0 ;
2256: reservation_present BOOLEAN := FALSE;
2257: order_present BOOLEAN := FALSE;
2250: l_order_line_id NUMBER;
2251: l_previous_order_id NUMBER := 0;
2252: l_requirement_line_id NUMBER;
2253: l_Requirement_Line_Tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
2254: l_Requirement_Line_Tbl_order CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
2255: req_loop NUMBER := 0 ;
2256: reservation_present BOOLEAN := FALSE;
2257: order_present BOOLEAN := FALSE;
2258: BEGIN
2294: ROLLBACK TO CLEAN_MATERIAL_TRANSACTION;
2295: return;
2296: ELSE
2297: IF reservation_present THEN
2298: CSP_Requirement_Lines_PVT.Update_requirement_lines(
2299: P_Api_Version_Number => 1.0,
2300: P_Init_Msg_List => FND_API.G_FALSE,
2301: P_Commit => FND_API.G_FALSE,
2302: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2350: ROLLBACK TO CLEAN_MATERIAL_TRANSACTION;
2351: return;
2352: ELSE
2353: IF order_present then
2354: CSP_Requirement_Lines_PVT.Update_requirement_lines(
2355: P_Api_Version_Number => 1.0,
2356: P_Init_Msg_List => FND_API.G_FALSE,
2357: P_Commit => FND_API.G_FALSE,
2358: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2433:
2434: CURSOR get_reservations is
2435: select crld.source_id , crld.req_line_detail_id
2436: from csp_req_line_details crld
2437: ,csp_requirement_lines crl
2438: ,csp_requirement_headers crh
2439: where crh.task_assignment_id = p_task_assignment_id
2440: and crl.requirement_header_id = crh.requirement_header_id
2441: and crld.requirement_line_id = crl.requirement_line_id
2443:
2444: CURSOR get_orders is
2445: select oeh.header_id, crld.req_line_detail_id, crh.address_type
2446: from csp_req_line_details crld
2447: ,csp_requirement_lines crl
2448: ,csp_requirement_headers crh
2449: ,oe_order_lines_all oel
2450: ,oe_order_headers_all oeh
2451: where crh.task_assignment_id = p_task_assignment_id
2495: l_fl_rcvd_lines := 0;
2496: SELECT COUNT(l.requirement_line_id)
2497: into l_fl_rcvd_lines
2498: FROM csp_requirement_headers h,
2499: csp_requirement_lines l,
2500: csp_req_line_details dio,
2501: csp_req_line_details dres,
2502: oe_order_lines_all oola,
2503: mtl_reservations mr
2527: l_shpd_lines := 0;
2528: SELECT COUNT(l.requirement_line_id)
2529: into l_shpd_lines
2530: FROM csp_requirement_headers h,
2531: csp_requirement_lines l,
2532: csp_req_line_details d,
2533: oe_order_lines_all oola
2534: WHERE h.task_assignment_id = p_task_assignment_id
2535: AND h.requirement_header_id = l.requirement_header_id
2812:
2813: CURSOR get_orders is
2814: select distinct oeh.header_id, oel.line_id
2815: from csp_req_line_details crld
2816: ,csp_requirement_lines crl
2817: ,csp_requirement_headers crh
2818: ,oe_order_lines_all oel
2819: ,oe_order_headers_all oeh
2820: where crh.task_assignment_id = p_task_assignment_id
2887: /* CURSOR get_parts(c_task_assignment NUMBER) IS
2888: SELECT INVENTORY_ITEM_ID, UOM_CODE,REVISION
2889: ,SHIP_COMPLETE_FLAG,SOURCE_ORGANIZATION_ID
2890: ,ORDERED_QUANTITY,REQUIREMENT_LINE_ID,RESERVATION_ID
2891: FROM CSP_REQUIREMENT_HEADERS HR,CSP_REQUIREMENT_LINES LN
2892: WHERE HR.TASK_ASSIGNMENT_ID = c_task_assignment
2893: AND LN.REQUIREMENT_HEADER_ID = HR.REQUIREMENT_HEADER_ID;
2894:
2895:
2905: CURSOR C2(c_header_id number) is
2906: SELECT INVENTORY_ITEM_ID, UOM_CODE,REVISION
2907: ,SHIP_COMPLETE_FLAG,SOURCE_ORGANIZATION_ID
2908: ,ORDERED_QUANTITY,REQUIREMENT_LINE_ID,RESERVATION_ID
2909: FROM CSP_REQUIREMENT_LINES
2910: WHERE REQUIREMENT_HEADER_ID = c_header_id;
2911:
2912:
2913: CURSOR get_resource(c_task_assignment_id NUMBER) IS
2931:
2932: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CREATE_ORDERS';
2933: l_parts_header csp_parts_requirement.Header_rec_type;
2934: l_parts_lines csp_parts_requirement.Line_Tbl_type;
2935: l_requrements_lines CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
2936: l_return_status VARCHAR2(30);
2937: l_msg_count NUMBER;
2938: l_msg_data VARCHAR2(1000);
2939: l_resource_id NUMBER;
3078: ELSE
3079: l_requrements_lines(I).reservation_id := x_reservation_id;
3080: END IF;
3081: END LOOP;
3082: CSP_Requirement_Lines_PVT.Update_requirement_lines(
3083: P_Api_Version_Number => 1.0,
3084: P_Init_Msg_List => FND_API.G_FALSE,
3085: P_Commit => FND_API.G_FALSE,
3086: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3189: ,x_msg_count OUT NOCOPY NUMBER) IS
3190:
3191: CURSOR parts_list(product_task NUMBER, parts_category NUMBER) IS
3192: SELECT INVENTORY_ITEM_ID,REQUIRED_QUANTITY , UOM_CODE,SHIP_COMPLETE_FLAG,REVISION,REQUIREMENT_LINE_ID
3193: FROM CSP_REQUIREMENT_LINES crl, CSP_REQUIREMENT_HEADERS crh
3194: WHERE crh.TASK_ID = product_task
3195: AND crl.requirement_header_id = crh.requirement_header_id
3196: AND nvl(crl.LIKELIHOOD,0) >= nvl(parts_category,0) ;
3197:
3248: ,x_msg_count OUT NOCOPY NUMBER) IS
3249: /* CURSOR reservation_check(org_id NUMBER, sub_inv_code VARCHAR2,item_id NUMBER)
3250: IS
3251: SELECT NVL((REQUIRED_QUANTITY-ORDERED_QUANTITY),0)
3252: FROM CSP_REQUIREMENT_LINES
3253: WHERE REQUIREMENT_HEADER_ID = (SELECT REQUIREMENT_HEADER_ID
3254: FROM CSP_REQUIREMENT_HEADERS
3255: WHERE OPEN_REQUIREMENT = 'Yes'
3256: AND DESTINATION_ORGANIZATION_ID = org_id)
6996: ,x_msg_count OUT NOCOPY NUMBER) IS
6997: /*CURSOR reservation_check(org_id NUMBER, sub_inv_code VARCHAR2,item_id NUMBER)
6998: IS
6999: SELECT (REQUIRED_QUANTITY-ORDERED_QUANTITY)
7000: FROM CSP_REQUIREMENT_LINES
7001: WHERE REQUIREMENT_HEADER_ID = (SELECT REQUIREMENT_HEADER_ID
7002: FROM CSP_REQUIREMENT_HEADERS
7003: WHERE OPEN_REQUIREMENT = 'Yes'
7004: AND DESTINATION_ORGANIZATION_ID = org_id)
8048:
8049: CURSOR get_reservations is
8050: select crld.source_id , crld.req_line_detail_id
8051: from csp_req_line_details crld
8052: ,csp_requirement_lines crl
8053: ,csp_requirement_headers crh
8054: where crh.task_id = l_task_id
8055: and crl.requirement_header_id = crh.requirement_header_id
8056: and crld.requirement_line_id = crl.requirement_line_id
8058:
8059: CURSOR get_orders is
8060: select oeh.header_id, crld.req_line_detail_id
8061: from csp_req_line_details crld
8062: ,csp_requirement_lines crl
8063: ,csp_requirement_headers crh
8064: ,oe_order_lines_all oel
8065: ,oe_order_headers_all oeh
8066: where crh.task_id = l_task_id
8147: rcl.quantity_received quantity_received,crld.req_line_detail_id req_line_detail_id
8148: from mtl_material_transactions mmt,RCV_SHIPMENT_headers rsh,
8149: rcv_shipment_lines rcl,
8150: oe_order_lines_all oola,csp_req_line_details crld,
8151: csp_requirement_lines crl,
8152: csp_requirement_headers crh
8153: where mmt.transaction_id = p_transaction_id
8154: and mmt.shipment_number = rsh.shipment_num
8155: and rsh.shipment_header_id = rcl.shipment_header_id
8167: crld.requirement_line_id requirement_line_id, mmt.transaction_quantity quantity_shipped,
8168: mmt.transaction_quantity quantity_received,crld.req_line_detail_id req_line_detail_id
8169: from mtl_material_transactions mmt,
8170: oe_order_lines_all oola,csp_req_line_details crld,
8171: csp_requirement_lines crl,csp_requirement_headers crh
8172: where mmt.transaction_id = p_transaction_id
8173: and oola.source_document_id = mmt.transaction_source_id
8174: and crld.source_id = oola.line_id
8175: and (mmt.source_code = 'ORDER ENTRY')
8363: dres.requirement_line_id,
8364: dres.req_line_detail_id,
8365: h.requirement_header_id
8366: FROM csp_requirement_headers h,
8367: csp_requirement_lines l,
8368: csp_req_line_details dio,
8369: csp_req_line_details dres,
8370: oe_order_lines_all oola,
8371: mtl_reservations mr