[Home] [Help]
1: PACKAGE BODY CSP_SCH_INT_PVT AS
2: /* $Header: cspgscib.pls 120.9.12010000.3 2008/11/07 10:37:18 htank ship $ */
3: -- Start of Comments
4: -- Package name : CSP_SCH_INT_PVT
5: -- Purpose :
1: PACKAGE BODY CSP_SCH_INT_PVT AS
2: /* $Header: cspgscib.pls 120.9.12010000.3 2008/11/07 10:37:18 htank ship $ */
3: -- Start of Comments
4: -- Package name : CSP_SCH_INT_PVT
5: -- Purpose :
6: -- History :
7: -- NOTE :
8: -- End of Comments
6: -- History :
7: -- NOTE :
8: -- End of Comments
9:
10: G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_SCH_INT_PVT';
11: G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgscib.pls';
12:
13: g_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
14: g_schedular_call VARCHAR2(1) := 'Y' ;
9:
10: G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_SCH_INT_PVT';
11: G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgscib.pls';
12:
13: g_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
14: g_schedular_call VARCHAR2(1) := 'Y' ;
15: g_shipto_timezone_id NUMBER;
16: g_earliest_delivery_date Date := sysdate + 365 * 10;
17: TYPE CSP_RESOURCE_ORG_REC_TYP IS RECORD(resource_id NUMBER
18: ,resource_type VARCHAR2(30)
19: ,organization_id NUMBER
20: ,sub_inv_code VARCHAR2(10));
21:
22: TYPE CSP_RESOURCE_ORG_tbl_TYP IS TABLE OF CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_REC_TYP ;
23:
24: TYPE CSP_SHIP_PARAMETERS_REC_TYPE IS RECORD( resource_id NUMBER
25: ,resource_type VARCHAR2(30)
26: ,lead_time NUMBER
27: ,transfer_cost NUMBER
28: ,shipping_methodes VARCHAR2(4000)
29: ,arrival_date DATE);
30:
31: TYPE CSP_SHIP_PARAMETERS_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_REC_TYPE ;
32:
33: TYPE CSP_ORGS_SHIP_PARAM_REC_TYPE IS RECORD(resource_id NUMBER
34: ,resource_type VARCHAR2(30)
35: ,from_org_id NUMBER
39: ,transfer_cost NUMBER
40: ,shipping_method VARCHAR2(30)
41: ,delivery_time DATE);
42:
43: TYPE CSP_ORGS_SHIP_PARAM_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_REC_TYPE;
44:
45:
46: TYPE CSP_SHIP_QUANTITY_REC_TYPE IS RECORD(resource_id NUMBER
47: ,resource_type VARCHAR2(30)
49: ,to_org_id NUMBER
50: ,quantity NUMBER
51: ,destination_location_id NUMBER);
52:
53: TYPE CSP_SHIP_QUANTITY_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_REC_TYPE ;
54:
55: TYPE CSP_SHIP_METHOD_COUNT_REC_TYP IS RECORD(from_org_id NUMBER
56: ,to_org_id NUMBER
57: ,shipping_methodes NUMBER
58: ,min_leadtime NUMBER
59: ,max_leadtime NUMBER);
60:
61:
62: TYPE CSP_SHIP_METHOD_COUNT_TBL_TYP IS TABLE OF CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_REC_TYP ;
63:
64:
65: TYPE org_ship_methodes IS RECORD(from_org NUMBER,
66: to_org NUMBER,
65: TYPE org_ship_methodes IS RECORD(from_org NUMBER,
66: to_org NUMBER,
67: shipping_methode varchar2(30));
68:
69: TYPE org_ship_methodes_tbl_type IS TABLE OF csp_sch_int_pvt.org_ship_methodes ;
70: TYPE csp_req_line_details_rec_typ is RECORD(req_line_detail_id NUMBER
71: ,requirement_line_id NUMBER
72: ,source_type varchar2(30)
73: ,source_id NUMBER);
72: ,source_type varchar2(30)
73: ,source_id NUMBER);
74: TYPE csp_req_line_details_tabl_typ is table of csp_req_line_details_rec_typ;
75:
76: PROCEDURE GET_ORGANIZATION_SUBINV(p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
77: ,x_resource_org_subinv OUT NOCOPY CSP_SCH_INT_PVT.csp_resource_org_tbl_typ
78: ,x_return_status OUT NOCOPY VARCHAR2
79: ,x_msg_data OUT NOCOPY VARCHAR2
80: ,x_msg_count OUT NOCOPY NUMBER);
73: ,source_id NUMBER);
74: TYPE csp_req_line_details_tabl_typ is table of csp_req_line_details_rec_typ;
75:
76: PROCEDURE GET_ORGANIZATION_SUBINV(p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
77: ,x_resource_org_subinv OUT NOCOPY CSP_SCH_INT_PVT.csp_resource_org_tbl_typ
78: ,x_return_status OUT NOCOPY VARCHAR2
79: ,x_msg_data OUT NOCOPY VARCHAR2
80: ,x_msg_count OUT NOCOPY NUMBER);
81:
80: ,x_msg_count OUT NOCOPY NUMBER);
81:
82: PROCEDURE GET_PARTS_LIST(p_task_id IN NUMBER
83: ,p_likelihood IN NUMBER
84: ,x_parts_list OUT NOCOPY CSP_SCH_INT_PVT.csp_parts_tbl_typ1
85: ,x_return_status OUT NOCOPY VARCHAR2
86: ,x_msg_data OUT NOCOPY VARCHAR2
87: ,x_msg_count OUT NOCOPY NUMBER);
88:
85: ,x_return_status OUT NOCOPY VARCHAR2
86: ,x_msg_data OUT NOCOPY VARCHAR2
87: ,x_msg_count OUT NOCOPY NUMBER);
88:
89: PROCEDURE CHECK_LOCAl_INVENTORY( p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
90: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
91: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
92: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
93: ,x_return_status OUT NOCOPY VARCHAR2
86: ,x_msg_data OUT NOCOPY VARCHAR2
87: ,x_msg_count OUT NOCOPY NUMBER);
88:
89: PROCEDURE CHECK_LOCAl_INVENTORY( p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
90: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
91: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
92: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
93: ,x_return_status OUT NOCOPY VARCHAR2
94: ,x_msg_data OUT NOCOPY VARCHAR2
87: ,x_msg_count OUT NOCOPY NUMBER);
88:
89: PROCEDURE CHECK_LOCAl_INVENTORY( p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
90: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
91: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
92: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
93: ,x_return_status OUT NOCOPY VARCHAR2
94: ,x_msg_data OUT NOCOPY VARCHAR2
95: ,x_msg_count OUT NOCOPY NUMBER);
88:
89: PROCEDURE CHECK_LOCAl_INVENTORY( p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
90: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
91: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
92: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
93: ,x_return_status OUT NOCOPY VARCHAR2
94: ,x_msg_data OUT NOCOPY VARCHAR2
95: ,x_msg_count OUT NOCOPY NUMBER);
96:
93: ,x_return_status OUT NOCOPY VARCHAR2
94: ,x_msg_data OUT NOCOPY VARCHAR2
95: ,x_msg_count OUT NOCOPY NUMBER);
96:
97: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
98: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
99: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
100: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
101: ,x_return_status OUT NOCOPY VARCHAR2
94: ,x_msg_data OUT NOCOPY VARCHAR2
95: ,x_msg_count OUT NOCOPY NUMBER);
96:
97: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
98: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
99: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
100: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
101: ,x_return_status OUT NOCOPY VARCHAR2
102: ,x_msg_data OUT NOCOPY VARCHAR2
95: ,x_msg_count OUT NOCOPY NUMBER);
96:
97: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
98: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
99: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
100: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
101: ,x_return_status OUT NOCOPY VARCHAR2
102: ,x_msg_data OUT NOCOPY VARCHAR2
103: ,x_msg_count OUT NOCOPY NUMBER);
96:
97: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
98: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
99: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
100: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
101: ,x_return_status OUT NOCOPY VARCHAR2
102: ,x_msg_data OUT NOCOPY VARCHAR2
103: ,x_msg_count OUT NOCOPY NUMBER);
104:
101: ,x_return_status OUT NOCOPY VARCHAR2
102: ,x_msg_data OUT NOCOPY VARCHAR2
103: ,x_msg_count OUT NOCOPY NUMBER);
104:
105: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
106: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
107: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
108: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
109: ,x_return_status OUT NOCOPY VARCHAR2
102: ,x_msg_data OUT NOCOPY VARCHAR2
103: ,x_msg_count OUT NOCOPY NUMBER);
104:
105: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
106: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
107: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
108: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
109: ,x_return_status OUT NOCOPY VARCHAR2
110: ,x_msg_data OUT NOCOPY VARCHAR2
103: ,x_msg_count OUT NOCOPY NUMBER);
104:
105: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
106: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
107: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
108: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
109: ,x_return_status OUT NOCOPY VARCHAR2
110: ,x_msg_data OUT NOCOPY VARCHAR2
111: ,x_msg_count OUT NOCOPY NUMBER);
104:
105: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
106: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
107: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
108: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
109: ,x_return_status OUT NOCOPY VARCHAR2
110: ,x_msg_data OUT NOCOPY VARCHAR2
111: ,x_msg_count OUT NOCOPY NUMBER);
112:
109: ,x_return_status OUT NOCOPY VARCHAR2
110: ,x_msg_data OUT NOCOPY VARCHAR2
111: ,x_msg_count OUT NOCOPY NUMBER);
112:
113: PROCEDURE CONSOLIDATE_QUANTITIES(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
114: ,x_ship_quantity OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
115: ,x_return_status OUT NOCOPY VARCHAR2
116: ,X_MSG_DATA OUT NOCOPY VARCHAR2
117: ,x_msg_count OUT NOCOPY NUMBER);
110: ,x_msg_data OUT NOCOPY VARCHAR2
111: ,x_msg_count OUT NOCOPY NUMBER);
112:
113: PROCEDURE CONSOLIDATE_QUANTITIES(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
114: ,x_ship_quantity OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
115: ,x_return_status OUT NOCOPY VARCHAR2
116: ,X_MSG_DATA OUT NOCOPY VARCHAR2
117: ,x_msg_count OUT NOCOPY NUMBER);
118:
119:
120: PROCEDURE EXTEND_ATP_REC(p_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_REC_TYP
121: ,x_return_status OUT NOCOPY VARCHAR2);
122:
123: /*PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
124: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tab_typ
125: ,x_return_status OUT NOCOPY VARCHAR2);*/
126:
127: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
120: PROCEDURE EXTEND_ATP_REC(p_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_REC_TYP
121: ,x_return_status OUT NOCOPY VARCHAR2);
122:
123: /*PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
124: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tab_typ
125: ,x_return_status OUT NOCOPY VARCHAR2);*/
126:
127: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
128: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
123: /*PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
124: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tab_typ
125: ,x_return_status OUT NOCOPY VARCHAR2);*/
126:
127: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
128: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
129: ,x_return_status OUT NOCOPY VARCHAR2
130: ,x_msg_data OUT NOCOPY VARCHAR2
131: ,x_msg_count OUT NOCOPY NUMBER);
124: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tab_typ
125: ,x_return_status OUT NOCOPY VARCHAR2);*/
126:
127: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
128: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
129: ,x_return_status OUT NOCOPY VARCHAR2
130: ,x_msg_data OUT NOCOPY VARCHAR2
131: ,x_msg_count OUT NOCOPY NUMBER);
132:
129: ,x_return_status OUT NOCOPY VARCHAR2
130: ,x_msg_data OUT NOCOPY VARCHAR2
131: ,x_msg_count OUT NOCOPY NUMBER);
132:
133: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
134: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
135: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
136: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
137: ,x_return_status OUT NOCOPY VARCHAR2
130: ,x_msg_data OUT NOCOPY VARCHAR2
131: ,x_msg_count OUT NOCOPY NUMBER);
132:
133: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
134: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
135: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
136: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
137: ,x_return_status OUT NOCOPY VARCHAR2
138: ,x_msg_data OUT NOCOPY VARCHAR2
131: ,x_msg_count OUT NOCOPY NUMBER);
132:
133: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
134: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
135: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
136: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
137: ,x_return_status OUT NOCOPY VARCHAR2
138: ,x_msg_data OUT NOCOPY VARCHAR2
139: ,x_msg_count OUT NOCOPY NUMBER);
132:
133: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
134: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
135: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
136: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
137: ,x_return_status OUT NOCOPY VARCHAR2
138: ,x_msg_data OUT NOCOPY VARCHAR2
139: ,x_msg_count OUT NOCOPY NUMBER);
140:
137: ,x_return_status OUT NOCOPY VARCHAR2
138: ,x_msg_data OUT NOCOPY VARCHAR2
139: ,x_msg_count OUT NOCOPY NUMBER);
140:
141: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
142: ,x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
143: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
144: ,x_return_status OUT NOCOPY VARCHAR2
145: ,x_msg_data OUT NOCOPY VARCHAR2
138: ,x_msg_data OUT NOCOPY VARCHAR2
139: ,x_msg_count OUT NOCOPY NUMBER);
140:
141: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
142: ,x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
143: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
144: ,x_return_status OUT NOCOPY VARCHAR2
145: ,x_msg_data OUT NOCOPY VARCHAR2
146: ,x_msg_count OUT NOCOPY NUMBER);
139: ,x_msg_count OUT NOCOPY NUMBER);
140:
141: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
142: ,x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
143: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
144: ,x_return_status OUT NOCOPY VARCHAR2
145: ,x_msg_data OUT NOCOPY VARCHAR2
146: ,x_msg_count OUT NOCOPY NUMBER);
147:
144: ,x_return_status OUT NOCOPY VARCHAR2
145: ,x_msg_data OUT NOCOPY VARCHAR2
146: ,x_msg_count OUT NOCOPY NUMBER);
147:
148: PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
149: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
150: ,x_return_status OUT NOCOPY VARCHAR2
151: ,x_msg_data OUT NOCOPY VARCHAR2
152: ,x_msg_count OUT NOCOPY NUMBER);
145: ,x_msg_data OUT NOCOPY VARCHAR2
146: ,x_msg_count OUT NOCOPY NUMBER);
147:
148: PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
149: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
150: ,x_return_status OUT NOCOPY VARCHAR2
151: ,x_msg_data OUT NOCOPY VARCHAR2
152: ,x_msg_count OUT NOCOPY NUMBER);
153:
176: ,x_reservation_id OUT NOCOPY NUMBER
177: ,x_msg_data OUT NOCOPY VARCHAR2
178: ,x_msg_count OUT NOCOPY NUMBER);
179:
180: PROCEDURE strip_into_lines (px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
181: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
182: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
183: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
184: ,x_msg_data OUT NOCOPY varchar2
177: ,x_msg_data OUT NOCOPY VARCHAR2
178: ,x_msg_count OUT NOCOPY NUMBER);
179:
180: PROCEDURE strip_into_lines (px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
181: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
182: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
183: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
184: ,x_msg_data OUT NOCOPY varchar2
185: ,x_return_status OUT NOCOPY varchar2
178: ,x_msg_count OUT NOCOPY NUMBER);
179:
180: PROCEDURE strip_into_lines (px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
181: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
182: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
183: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
184: ,x_msg_data OUT NOCOPY varchar2
185: ,x_return_status OUT NOCOPY varchar2
186: ,x_msg_count OUT NOCOPY NUMBER);
179:
180: PROCEDURE strip_into_lines (px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
181: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
182: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
183: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
184: ,x_msg_data OUT NOCOPY varchar2
185: ,x_return_status OUT NOCOPY varchar2
186: ,x_msg_count OUT NOCOPY NUMBER);
187:
197: ,x_msg_data OUT NOCOPY VARCHAR2);
198:
199: PROCEDURE GET_AVAILABILITY_OPTIONS(p_api_version_number IN NUMBER
200: ,p_task_id IN NUMBER
201: ,p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
202: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
203: ,p_likelihood IN NUMBER
204: ,p_subinv_only IN BOOLEAN
205: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
198:
199: PROCEDURE GET_AVAILABILITY_OPTIONS(p_api_version_number IN NUMBER
200: ,p_task_id IN NUMBER
201: ,p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
202: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
203: ,p_likelihood IN NUMBER
204: ,p_subinv_only IN BOOLEAN
205: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
206: ,x_return_status OUT NOCOPY VARCHAR2
201: ,p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
202: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
203: ,p_likelihood IN NUMBER
204: ,p_subinv_only IN BOOLEAN
205: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
206: ,x_return_status OUT NOCOPY VARCHAR2
207: ,x_msg_data OUT NOCOPY VARCHAR2
208: ,x_msg_count OUT NOCOPY NUMBER) IS
209:
206: ,x_return_status OUT NOCOPY VARCHAR2
207: ,x_msg_data OUT NOCOPY VARCHAR2
208: ,x_msg_count OUT NOCOPY NUMBER) IS
209:
210: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
211: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
212: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
213: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
214: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
207: ,x_msg_data OUT NOCOPY VARCHAR2
208: ,x_msg_count OUT NOCOPY NUMBER) IS
209:
210: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
211: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
212: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
213: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
214: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
215: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
208: ,x_msg_count OUT NOCOPY NUMBER) IS
209:
210: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
211: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
212: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
213: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
214: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
215: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
216: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
209:
210: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
211: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
212: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
213: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
214: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
215: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
216: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
217: l_return_status VARCHAR2(128) := FND_API.G_RET_STS_SUCCESS;
210: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
211: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
212: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
213: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
214: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
215: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
216: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
217: l_return_status VARCHAR2(128) := FND_API.G_RET_STS_SUCCESS;
218: l_del_date VARCHAR2(50);
211: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
212: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
213: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
214: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
215: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
216: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
217: l_return_status VARCHAR2(128) := FND_API.G_RET_STS_SUCCESS;
218: l_del_date VARCHAR2(50);
219: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS';
212: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
213: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
214: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
215: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
216: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
217: l_return_status VARCHAR2(128) := FND_API.G_RET_STS_SUCCESS;
218: l_del_date VARCHAR2(50);
219: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS';
220: l_temp_line_id NUMBER;
215: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
216: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
217: l_return_status VARCHAR2(128) := FND_API.G_RET_STS_SUCCESS;
218: l_del_date VARCHAR2(50);
219: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS';
220: l_temp_line_id NUMBER;
221:
222: cursor get_line_id is
223: select distinct(crl.requirement_line_id) req_line_id
225: where crh.task_id = p_task_id
226: and crl.requirement_header_id = crh.requirement_header_id;
227: BEGIN
228:
229: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
230: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
231: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
232: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
233: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
226: and crl.requirement_header_id = crh.requirement_header_id;
227: BEGIN
228:
229: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
230: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
231: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
232: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
233: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
234: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
227: BEGIN
228:
229: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
230: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
231: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
232: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
233: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
234: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
235: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
228:
229: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
230: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
231: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
232: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
233: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
234: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
235: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
236: x_return_status := FND_API.G_RET_STS_SUCCESS;
229: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
230: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
231: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
232: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
233: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
234: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
235: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
236: x_return_status := FND_API.G_RET_STS_SUCCESS;
237: g_interval := p_interval;
230: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
231: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
232: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
233: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
234: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
235: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
236: x_return_status := FND_API.G_RET_STS_SUCCESS;
237: g_interval := p_interval;
238: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(p_resources, l_resource_org_subinv , x_return_status,x_msg_data,x_msg_count);
231: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
232: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
233: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
234: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
235: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
236: x_return_status := FND_API.G_RET_STS_SUCCESS;
237: g_interval := p_interval;
238: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(p_resources, l_resource_org_subinv , x_return_status,x_msg_data,x_msg_count);
239: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
234: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
235: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
236: x_return_status := FND_API.G_RET_STS_SUCCESS;
237: g_interval := p_interval;
238: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(p_resources, l_resource_org_subinv , x_return_status,x_msg_data,x_msg_count);
239: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
240: RETURN;
241: END IF;
242: CSP_SCH_INT_PVT.GET_PARTS_LIST(p_task_id,p_likelihood,l_parts_list,x_return_status,x_msg_data,x_msg_count);
238: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(p_resources, l_resource_org_subinv , x_return_status,x_msg_data,x_msg_count);
239: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
240: RETURN;
241: END IF;
242: CSP_SCH_INT_PVT.GET_PARTS_LIST(p_task_id,p_likelihood,l_parts_list,x_return_status,x_msg_data,x_msg_count);
243: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
244: RETURN;
245: END IF;
246: IF l_parts_list.count = 0 THEN
254: END LOOP;
255: RETURN;
256: END IF;
257: IF l_parts_list.count > 0 THEN
258: CSP_SCH_INT_PVT.CHECK_LOCAl_INVENTORY(l_resource_org_subinv,l_parts_list,l_unavailable_list,l_final_available_list,x_return_status,x_msg_data,x_msg_count);
259: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
260: RETURN;
261: END IF ;
262: IF l_unavailable_list.count > 0 AND (NOT p_subinv_only) AND
261: END IF ;
262: IF l_unavailable_list.count > 0 AND (NOT p_subinv_only) AND
263: (fnd_profile.value(name => 'CSP_CHECK_ATP')= 'ALWAYS' or
264: fnd_profile.value(name => 'CSP_CHECK_ATP')= 'SCHONLY' ) THEN
265: CSP_SCH_INT_PVT.DO_ATP_CHECK(l_unavailable_list,p_interval,l_available_list,l_final_unavailable_list,x_return_status,x_msg_data,x_msg_count);
266: ELSE
267: l_final_unavailable_list := l_unavailable_list;
268: END IF;
269: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
283: END LOOP;
284: END LOOP;
285: END LOOP;
286: END IF;
287: CSP_SCH_INT_PVT.ELIGIBLE_RESOURCES(p_resources,l_final_available_list,l_final_unavailable_list,l_eligible_resources_list,x_return_status,x_msg_data,x_msg_count);
288: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
289: RETURN;
290: END IF;
291: IF l_eligible_resources_list.count > 0 THEN
288: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
289: RETURN;
290: END IF;
291: IF l_eligible_resources_list.count > 0 THEN
292: CSP_SCH_INT_PVT.GET_TIME_COST(l_eligible_resources_list,x_options,x_return_status,x_msg_data,x_msg_count);
293: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
294: RETURN;
295: /*ELSE
296: IF x_options.count = 0 THEN
337: PROCEDURE CHOOSE_OPTION(p_api_version_number IN NUMBER
338: ,p_task_id IN NUMBER
339: ,p_task_assignment_id IN NUMBER
340: ,p_likelihood IN NUMBER
341: ,p_options IN CSP_SCH_INT_PVT.csp_sch_options_rec_typ
342: ,x_return_status OUT NOCOPY VARCHAR2
343: ,x_msg_data OUT NOCOPY VARCHAR2
344: ,x_msg_count OUT NOCOPY NUMBER) IS
345:
361: FROM CSP_RS_SHIP_TO_ADDRESSES_ALL_V
362: WHERE RESOURCE_ID = c_resource_id
363: AND RESOURCE_TYPE = c_resource_type
364: AND PRIMARY_FLAG = 'Y';
365: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
366: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
367: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
368: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
369: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
362: WHERE RESOURCE_ID = c_resource_id
363: AND RESOURCE_TYPE = c_resource_type
364: AND PRIMARY_FLAG = 'Y';
365: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
366: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
367: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
368: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
369: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
370: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
363: AND RESOURCE_TYPE = c_resource_type
364: AND PRIMARY_FLAG = 'Y';
365: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
366: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
367: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
368: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
369: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
370: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
371: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
364: AND PRIMARY_FLAG = 'Y';
365: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
366: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
367: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
368: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
369: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
370: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
371: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
372: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
365: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
366: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
367: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
368: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
369: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
370: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
371: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
372: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
373: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
366: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
367: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
368: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
369: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
370: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
371: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
372: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
373: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
374: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
367: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
368: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
369: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
370: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
371: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
372: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
373: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
374: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
375: l_return_status VARCHAR2(128);
368: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
369: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
370: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
371: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
372: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
373: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
374: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
375: l_return_status VARCHAR2(128);
376: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
369: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
370: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
371: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
372: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
373: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
374: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
375: l_return_status VARCHAR2(128);
376: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
377: min_cost NUMBER ;
370: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
371: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
372: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
373: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
374: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
375: l_return_status VARCHAR2(128);
376: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
377: min_cost NUMBER ;
378: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
372: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
373: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
374: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
375: l_return_status VARCHAR2(128);
376: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
377: min_cost NUMBER ;
378: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
379: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
380: l_org_ship_methode org_ship_methodes_tbl_type ;
374: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
375: l_return_status VARCHAR2(128);
376: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
377: min_cost NUMBER ;
378: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
379: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
380: l_org_ship_methode org_ship_methodes_tbl_type ;
381: -- l_Requirement_Line_Tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
382: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
375: l_return_status VARCHAR2(128);
376: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
377: min_cost NUMBER ;
378: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
379: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
380: l_org_ship_methode org_ship_methodes_tbl_type ;
381: -- l_Requirement_Line_Tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
382: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
383: l_requirement_header CSP_Requirement_Headers_PVT.REQUIREMENT_HEADER_Rec_Type;
378: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
379: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
380: l_org_ship_methode org_ship_methodes_tbl_type ;
381: -- l_Requirement_Line_Tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
382: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
383: l_requirement_header CSP_Requirement_Headers_PVT.REQUIREMENT_HEADER_Rec_Type;
384: l_parts_header csp_parts_requirement.Header_rec_type;
385: l_parts_lines csp_parts_requirement.Line_Tbl_type;
386: l_revision varchar2(3);
392: l_reservation_id NUMBER;
393: l_requirements_line_id NUMBER;
394: req_loop NUMBER := 1;
395: l_requirement_header_id NUMBER;
396: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CHOOSE_OPTION';
397: l_msg varchar2(2000);
398: rec_count NUMBER;
399: l_destination_sub_inv varchar2(30);
400: l_destination_org_id NUMBER;
400: l_destination_org_id NUMBER;
401: l_ship_to_location_id NUMBER;
402: l_ship_methode_count NUMBER;
403: l_temp_line_id NUMBER;
404: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
405: l_req_line_details_tbl CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ;
406: l_address_type varchar2(3);
407: BEGIN
408: savepoint choose_options;
401: l_ship_to_location_id NUMBER;
402: l_ship_methode_count NUMBER;
403: l_temp_line_id NUMBER;
404: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
405: l_req_line_details_tbl CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ;
406: l_address_type varchar2(3);
407: BEGIN
408: savepoint choose_options;
409: x_return_status := FND_API.G_RET_STS_SUCCESS ;
406: l_address_type varchar2(3);
407: BEGIN
408: savepoint choose_options;
409: x_return_status := FND_API.G_RET_STS_SUCCESS ;
410: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
411: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
412: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
413: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
414: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
407: BEGIN
408: savepoint choose_options;
409: x_return_status := FND_API.G_RET_STS_SUCCESS ;
410: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
411: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
412: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
413: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
414: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
415: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
408: savepoint choose_options;
409: x_return_status := FND_API.G_RET_STS_SUCCESS ;
410: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
411: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
412: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
413: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
414: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
415: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
416: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
409: x_return_status := FND_API.G_RET_STS_SUCCESS ;
410: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
411: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
412: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
413: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
414: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
415: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
416: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
417: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
410: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
411: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
412: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
413: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
414: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
415: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
416: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
417: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
418: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
411: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
412: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
413: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
414: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
415: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
416: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
417: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
418: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
419: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
412: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
413: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
414: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
415: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
416: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
417: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
418: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
419: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
420: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
413: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
414: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
415: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
416: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
417: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
418: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
419: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
420: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
421: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
414: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
415: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
416: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
417: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
418: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
419: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
420: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
421: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
422: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
415: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
416: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
417: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
418: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
419: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
420: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
421: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
422: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
423: l_org_ship_methode := org_ship_methodes_tbl_type();
416: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
417: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
418: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
419: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
420: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
421: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
422: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
423: l_org_ship_methode := org_ship_methodes_tbl_type();
424: l_req_line_details_tbl := CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ();
417: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
418: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
419: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
420: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
421: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
422: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
423: l_org_ship_methode := org_ship_methodes_tbl_type();
424: l_req_line_details_tbl := CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ();
425:
418: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
419: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
420: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
421: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
422: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
423: l_org_ship_methode := org_ship_methodes_tbl_type();
424: l_req_line_details_tbl := CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ();
425:
426:
420: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
421: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
422: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
423: l_org_ship_methode := org_ship_methodes_tbl_type();
424: l_req_line_details_tbl := CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ();
425:
426:
427: l_resources.extend;
428: l_resources(1).resource_id := p_options.resource_id ;
428: l_resources(1).resource_id := p_options.resource_id ;
429: l_resources(1).resource_type := p_options.resource_type ;
430: -- l_interval.earliest_time :=
431: l_interval.latest_time := p_options.start_time;
432: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(l_resources, l_resource_org_subinv , x_return_status,x_msg_data,x_msg_count);
433: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
434: RETURN;
435: END IF;
436: CSP_SCH_INT_PVT.GET_PARTS_LIST(p_task_id,p_likelihood,l_parts_list,x_return_status,x_msg_data,x_msg_count);
432: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(l_resources, l_resource_org_subinv , x_return_status,x_msg_data,x_msg_count);
433: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
434: RETURN;
435: END IF;
436: CSP_SCH_INT_PVT.GET_PARTS_LIST(p_task_id,p_likelihood,l_parts_list,x_return_status,x_msg_data,x_msg_count);
437: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
438: RETURN;
439: END IF;
440: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
437: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
438: RETURN;
439: END IF;
440: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
441: CSP_SCH_INT_PVT.CHECK_LOCAL_INVENTORY(l_resource_org_subinv,l_parts_list,l_unavailable_list,l_final_available_list,x_return_status,x_msg_data,x_msg_count);
442: ELSE
443: return;
444: END IF;
445: IF l_unavailable_list.count >= 1 THEN
442: ELSE
443: return;
444: END IF;
445: IF l_unavailable_list.count >= 1 THEN
446: CSP_SCH_INT_PVT.DO_ATP_CHECK(l_unavailable_list,l_interval,l_available_list,l_final_unavailable_list,x_return_status,x_msg_data,x_msg_count);
447: END IF;
448: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
449: RETURN;
450: END IF;
459: l_final_available_list(l_final_available_list.count) := l_available_list(I);
460: l_temp_line_id := l_available_list(I).line_id;
461: END IF;
462: END LOOP;
463: CSP_SCH_INT_PVT.ELIGIBLE_RESOURCES(l_resources,l_final_available_list,l_final_unavailable_list,l_eligible_resources_list,x_return_status,x_msg_data,x_msg_count);
464: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
465: RETURN;
466: END IF;
467: IF l_eligible_resources_list.count > 0 THEN
464: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
465: RETURN;
466: END IF;
467: IF l_eligible_resources_list.count > 0 THEN
468: --CSP_SCH_INT_PVT.GET_TIME_COST(l_eligible_resources_list,l_options,x_return_status);
469: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_eligible_resources_list,l_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
470: ELSE
471: x_return_status := FND_API.G_RET_STS_ERROR;
472: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_OPTION_NOT_VALIED');
465: RETURN;
466: END IF;
467: IF l_eligible_resources_list.count > 0 THEN
468: --CSP_SCH_INT_PVT.GET_TIME_COST(l_eligible_resources_list,l_options,x_return_status);
469: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_eligible_resources_list,l_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
470: ELSE
471: x_return_status := FND_API.G_RET_STS_ERROR;
472: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_OPTION_NOT_VALIED');
473: FND_MSG_PUB.ADD;
610: l_reservation_parts.quantity_needed := l_eligible_resources_list(I).quantity;
611: l_reservation_parts.sub_inventory_code := l_eligible_resources_list(I).sub_inventory;
612: l_reservation_parts.line_id := l_eligible_resources_list(I).line_id;
613: l_reservation_parts.revision := l_eligible_resources_list(I).revision;
614: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
615: IF l_reservation_id <= 0 THEN
616: ROLLBACK TO choose_options;
617: x_return_status := FND_API.G_RET_STS_ERROR;
618: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
770: and oel.line_id = crl.order_line_id
771: and oeh.header_id = oel.header_id
772: order by oeh.header_id;
773:
774: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CLEAN_MATERIAL_TRANSACTION';
775: l_reserv_id NUMBER;
776: l_local_reserv_id NUMBER;
777: l_order_id NUMBER;
778: l_return_status VARCHAR2(3);
793: LOOP
794: FETCH cancel_reserv INTO l_reserv_id,l_local_reserv_id,l_requirement_line_id;
795: EXIT WHEN cancel_reserv%NOTFOUND;
796: IF l_reserv_id IS NOT NULL THEN
797: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_reserv_id,x_return_status,x_msg_data,x_msg_count);
798: END IF;
799: IF l_local_reserv_id IS NOT NULL THEN
800: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_local_reserv_id,x_return_status,x_msg_data,x_msg_count);
801: END IF;
796: IF l_reserv_id IS NOT NULL THEN
797: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_reserv_id,x_return_status,x_msg_data,x_msg_count);
798: END IF;
799: IF l_local_reserv_id IS NOT NULL THEN
800: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_local_reserv_id,x_return_status,x_msg_data,x_msg_count);
801: END IF;
802: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
803: ROLLBACK TO CLEAN_MATERIAL_TRANSACTION;
804: x_return_status := FND_API.G_RET_STS_ERROR;
847: LOOP
848: FETCH cancel_order INTO l_order_id,l_requirement_line_id ;
849: EXIT WHEN cancel_order % NOTFOUND;
850: IF l_order_id <> l_previous_order_id THEN
851: CSP_SCH_INT_PVT.CANCEL_ORDER(l_order_id,x_return_status,x_msg_data,x_msg_count);
852: l_previous_order_id := l_order_id;
853: ELSE
854: x_return_status := FND_API.G_RET_STS_SUCCESS;
855: END IF;
977: OPEN get_reservations ;
978: LOOP
979: FETCH get_reservations INTO l_reserv_id,l_req_details_line_id;
980: EXIT WHEN get_reservations%NOTFOUND;
981: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_reserv_id,x_return_status,x_msg_data,x_msg_count);
982: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
983: x_return_status := FND_API.G_RET_STS_SUCCESS;
984: exit;
985: ELSE
999: OPEN get_order_status(l_order_id);
1000: FETCH get_order_status INTO l_status;
1001: CLOSE get_order_status;
1002: IF l_status <> 'CANCELLED' THEN
1003: CSP_SCH_INT_PVT.CANCEL_ORDER(l_order_id,x_return_status,x_msg_data,x_msg_count);
1004: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1005: x_return_status := FND_API.G_RET_STS_SUCCESS;
1006: EXIT;
1007: ELSE
1051: PROCEDURE CANCEL_RESERVATION(p_reserv_id IN NUMBER
1052: ,x_return_status OUT NOCOPY VARCHAR2
1053: ,x_msg_data OUT NOCOPY VARCHAR2
1054: ,x_msg_count OUT NOCOPY NUMBER) IS
1055: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CANCEL_RESERVATION';
1056: l_api_version_number NUMBER := 1.0;
1057: l_return_status VARCHAR2(3);
1058: l_msg_count NUMBER;
1059: l_init_msg_lst VARCHAR2(1) := fnd_api.g_true;
1090: ,x_msg_count OUT NOCOPY NUMBER) IS
1091: l_parts_header csp_parts_requirement.Header_rec_type;
1092: l_parts_lines csp_parts_requirement.Line_Tbl_type;
1093: l_api_version NUMBER := 1.0;
1094: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CANCEL_ORDER';
1095: BEGIN
1096: x_return_status := FND_API.G_RET_STS_SUCCESS;
1097: l_parts_header.order_header_id := p_order_id;
1098: l_parts_header.operation := 'CANCEL';
1137: and oel.line_id = crld.source_id
1138: and oeh.header_id = oel.header_id
1139: order by oeh.header_id;
1140:
1141: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CREATE_ORDERS';
1142: l_parts_header csp_parts_requirement.Header_rec_type;
1143: l_parts_lines csp_parts_requirement.Line_Tbl_type;
1144: l_order_header_id NUMBER;
1145: l_order_line_id NUMBER;
1241: WHERE RESOURCE_ID = c_resource_id
1242: AND RESOURCE_TYPE = c_resource_type
1243: AND PRIMARY_FLAG = 'Y';
1244:
1245: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CREATE_ORDERS';
1246: l_parts_header csp_parts_requirement.Header_rec_type;
1247: l_parts_lines csp_parts_requirement.Line_Tbl_type;
1248: l_requrements_lines CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
1249: l_return_status VARCHAR2(30);
1375: ELSE
1376: FOR I IN 1..l_parts_lines.count LOOP
1377: l_requrements_lines(I).REQUIREMENT_LINE_ID := l_parts_lines(I).requirement_line_id;
1378: l_requrements_lines(I).ORDER_LINE_ID := l_parts_lines(I).order_line_id;
1379: CSP_SCH_INT_PVT.TRANSFER_RESERVATION(p_reservation_id => l_parts_lines(I).reservation_id
1380: ,p_order_header_id => l_parts_header.order_header_id
1381: ,p_order_line_id => l_parts_lines(I).order_line_id
1382: ,x_return_status => x_return_status
1383: ,x_reservation_id => x_reservation_id
1418: , p_data => x_msg_data);
1419: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1420: END CREATE_ORDERS;*/
1421:
1422: PROCEDURE GET_ORGANIZATION_SUBINV( p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
1423: ,x_resource_org_subinv OUT NOCOPY CSP_SCH_INT_PVT.csp_resource_org_tbl_typ
1424: ,x_return_status OUT NOCOPY VARCHAR2
1425: ,x_msg_data OUT NOCOPY VARCHAR2
1426: ,x_msg_count OUT NOCOPY NUMBER) IS
1419: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1420: END CREATE_ORDERS;*/
1421:
1422: PROCEDURE GET_ORGANIZATION_SUBINV( p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
1423: ,x_resource_org_subinv OUT NOCOPY CSP_SCH_INT_PVT.csp_resource_org_tbl_typ
1424: ,x_return_status OUT NOCOPY VARCHAR2
1425: ,x_msg_data OUT NOCOPY VARCHAR2
1426: ,x_msg_count OUT NOCOPY NUMBER) IS
1427:
1437: FROM JTF_OBJECTS_VL
1438: WHERE OBJECT_CODE = l_resource_type;
1439: l_organization NUMBER;
1440: l_sub_inventory VARCHAR2(10);
1441: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV';
1442: l_msg_count number;
1443: l_msg_data number;
1444: l_resource_name varchar2(1000);
1445: l_resource_type_name varchar2(1000);
1444: l_resource_name varchar2(1000);
1445: l_resource_type_name varchar2(1000);
1446: BEGIN
1447: x_return_status := FND_API.G_RET_STS_SUCCESS;
1448: x_resource_org_subinv := CSP_SCH_INT_PVT.csp_resource_org_tbl_typ();
1449: FOR I IN 1..p_resources.count LOOP
1450: OPEN csp_resource_org(p_resources(I).resource_id, p_resources(I).resource_type);
1451: LOOP
1452: FETCH csp_resource_org INTO l_organization,l_sub_inventory ;
1493: END GET_ORGANIZATION_SUBINV;
1494:
1495: PROCEDURE GET_PARTS_LIST(p_task_id IN NUMBER
1496: ,p_likelihood IN NUMBER
1497: ,x_parts_list OUT NOCOPY CSP_SCH_INT_PVT.csp_parts_tbl_typ1
1498: ,x_return_status OUT NOCOPY VARCHAR2
1499: ,x_msg_data OUT NOCOPY VARCHAR2
1500: ,x_msg_count OUT NOCOPY NUMBER) IS
1501:
1513: l_ship_complet VARCHAR2(30);
1514: l_msg_data VARCHAR2(2000);
1515: l_msg_count NUMBER;
1516: l_revision varchar2(3);
1517: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.GET_PARTS_LIST' ;
1518: l_header_id NUMBER;
1519: loop_count NUMBER := 0;
1520: l_line_id NUMBER;
1521:
1521:
1522: BEGIN
1523: -- savepoint GET_PARTS_LIST;
1524: x_return_status := FND_API.G_RET_STS_SUCCESS ;
1525: --x_parts_list := CSP_SCH_INT_PVT.csp_parts_tbl_typ1();
1526: OPEN parts_list(p_task_id, p_likelihood);
1527: LOOP
1528: FETCH parts_list INTO part_number, part_quantity , part_uom , l_ship_complet,l_revision , l_line_id;
1529: EXIT WHEN parts_list % NOTFOUND;
1548: , p_data => x_msg_data);
1549: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1550: return;
1551: END GET_PARTS_LIST;
1552: PROCEDURE CHECK_LOCAl_INVENTORY(p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
1553: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
1554: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1555: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
1556: ,x_return_status OUT NOCOPY VARCHAR2
1549: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1550: return;
1551: END GET_PARTS_LIST;
1552: PROCEDURE CHECK_LOCAl_INVENTORY(p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
1553: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
1554: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1555: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
1556: ,x_return_status OUT NOCOPY VARCHAR2
1557: ,x_msg_data OUT NOCOPY VARCHAR2
1550: return;
1551: END GET_PARTS_LIST;
1552: PROCEDURE CHECK_LOCAl_INVENTORY(p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
1553: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
1554: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1555: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
1556: ,x_return_status OUT NOCOPY VARCHAR2
1557: ,x_msg_data OUT NOCOPY VARCHAR2
1558: ,x_msg_count OUT NOCOPY NUMBER) IS
1551: END GET_PARTS_LIST;
1552: PROCEDURE CHECK_LOCAl_INVENTORY(p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
1553: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
1554: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1555: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
1556: ,x_return_status OUT NOCOPY VARCHAR2
1557: ,x_msg_data OUT NOCOPY VARCHAR2
1558: ,x_msg_count OUT NOCOPY NUMBER) IS
1559: /* CURSOR reservation_check(org_id NUMBER, sub_inv_code VARCHAR2,item_id NUMBER)
1586: l_qs NUMBER;
1587: l_att NUMBER;
1588: l_temp_reserv_quantity NUMBER;
1589: l_substitute_item NUMBER;
1590: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CHECK_LOCAl_INVENTORY';
1591: --- l_cumulative_att NUMBER;
1592: l_supersede_items CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
1593: l_required_quantity NUMBER;
1594: l_alternate_parts CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
1593: l_required_quantity NUMBER;
1594: l_alternate_parts CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
1595: l_append boolean := true;
1596: l_revision_controlled BOOLEAN := false;
1597: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
1598: l_reservation_id NUMBER;
1599: l_res_ids CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
1600: BEGIN
1601: savepoint csp_check_local_inv;
1599: l_res_ids CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
1600: BEGIN
1601: savepoint csp_check_local_inv;
1602: x_return_status := FND_API.G_RET_STS_SUCCESS;
1603: x_unavailable_list:= CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1604: x_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
1605: if l_res_ids.count > 0 THEN
1606: l_res_ids.delete;
1607: END IF;
1600: BEGIN
1601: savepoint csp_check_local_inv;
1602: x_return_status := FND_API.G_RET_STS_SUCCESS;
1603: x_unavailable_list:= CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1604: x_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
1605: if l_res_ids.count > 0 THEN
1606: l_res_ids.delete;
1607: END IF;
1608: FOR I IN 1..p_resource_org_subinv.count LOOP
1703: l_reservation_parts.quantity_needed := x_available_list(x_available_list.count).quantity ;
1704: l_reservation_parts.sub_inventory_code := x_available_list(x_available_list.count).sub_inventory;
1705: l_reservation_parts.line_id := x_available_list(x_available_list.count).line_id ;
1706: l_reservation_parts.revision := x_available_list(x_available_list.count).revision;
1707: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
1708:
1709: IF l_reservation_id <= 0 THEN
1710: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
1711: FND_MSG_PUB.ADD;
1738: l_reservation_parts.quantity_needed := x_available_list(x_available_list.count).quantity ;
1739: l_reservation_parts.sub_inventory_code := x_available_list(x_available_list.count).sub_inventory;
1740: l_reservation_parts.line_id := x_available_list(x_available_list.count).line_id ;
1741: l_reservation_parts.revision := x_available_list(x_available_list.count).revision;
1742: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
1743:
1744: IF l_reservation_id <= 0 THEN
1745: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
1746: FND_MSG_PUB.ADD;
1811: l_reservation_parts.quantity_needed := x_available_list(x_available_list.count).quantity ;
1812: l_reservation_parts.sub_inventory_code := x_available_list(x_available_list.count).sub_inventory;
1813: l_reservation_parts.line_id := x_available_list(x_available_list.count).line_id ;
1814: l_reservation_parts.revision := x_available_list(x_available_list.count).revision;
1815: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
1816:
1817: IF l_reservation_id <= 0 THEN
1818: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
1819: FND_MSG_PUB.ADD;
1848: l_reservation_parts.quantity_needed := x_available_list(x_available_list.count).quantity ;
1849: l_reservation_parts.sub_inventory_code := x_available_list(x_available_list.count).sub_inventory;
1850: l_reservation_parts.line_id := x_available_list(x_available_list.count).line_id ;
1851: l_reservation_parts.revision := x_available_list(x_available_list.count).revision;
1852: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
1853:
1854: IF l_reservation_id <= 0 THEN
1855: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
1856: FND_MSG_PUB.ADD;
1898: END IF;
1899: END LOOP;
1900: END LOOP;
1901: for i in 1..l_res_ids.count loop
1902: CSP_SCH_INT_PVT.DELETE_RESERVATION(l_res_ids(i),x_return_status,x_msg_data);
1903: end loop;
1904: EXCEPTION
1905: WHEN OTHERS THEN
1906: FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1912: , p_data => x_msg_data);
1913: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1914: return;
1915: END CHECK_LOCAl_INVENTORY;
1916: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1917: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
1918: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
1919: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1920: ,x_return_status OUT NOCOPY VARCHAR2
1913: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1914: return;
1915: END CHECK_LOCAl_INVENTORY;
1916: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1917: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
1918: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
1919: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1920: ,x_return_status OUT NOCOPY VARCHAR2
1921: ,x_msg_data OUT NOCOPY VARCHAR2
1914: return;
1915: END CHECK_LOCAl_INVENTORY;
1916: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1917: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
1918: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
1919: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1920: ,x_return_status OUT NOCOPY VARCHAR2
1921: ,x_msg_data OUT NOCOPY VARCHAR2
1922: ,x_msg_count OUT NOCOPY NUMBER) IS
1915: END CHECK_LOCAl_INVENTORY;
1916: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1917: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
1918: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
1919: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
1920: ,x_return_status OUT NOCOPY VARCHAR2
1921: ,x_msg_data OUT NOCOPY VARCHAR2
1922: ,x_msg_count OUT NOCOPY NUMBER) IS
1923: CURSOR instance IS
1969: -- x_msg_count NUMBER;
1970: l_msg varchar2(2000);
1971: l_ship_complete_profile VARCHAR2(3);
1972: l_instance_id NUMBER;
1973: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.DO_ATP_CHECK' ;
1974: l_message varchar2(2000);
1975: l_item_segments varchar2(1000);
1976: l_error BOOLEAN := FALSE;
1977: dbug_variable1 NUMBER;
1980: l_assignment_set_id NUMBER;
1981: l_unavailable varchar2(1);
1982: BEGIN
1983: x_return_status := FND_API.G_RET_STS_SUCCESS;
1984: x_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1985: OPEN instance;
1986: FETCH instance INTO l_instance_id;
1987: CLOSE instance;
1988: l_mrp_database_link := fnd_profile.value(name => 'MRP_ATP_DATABASE_LINK');
2205: END DO_ATP_CHECK;
2206:
2207:
2208:
2209: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
2210: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2211: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
2212: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2213: ,x_return_status OUT NOCOPY VARCHAR2
2206:
2207:
2208:
2209: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
2210: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2211: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
2212: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2213: ,x_return_status OUT NOCOPY VARCHAR2
2214: ,x_msg_data OUT NOCOPY VARCHAR2
2207:
2208:
2209: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
2210: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2211: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
2212: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2213: ,x_return_status OUT NOCOPY VARCHAR2
2214: ,x_msg_data OUT NOCOPY VARCHAR2
2215: ,x_msg_count OUT NOCOPY NUMBER) IS
2208:
2209: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
2210: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2211: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
2212: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2213: ,x_return_status OUT NOCOPY VARCHAR2
2214: ,x_msg_data OUT NOCOPY VARCHAR2
2215: ,x_msg_count OUT NOCOPY NUMBER) IS
2216:
2214: ,x_msg_data OUT NOCOPY VARCHAR2
2215: ,x_msg_count OUT NOCOPY NUMBER) IS
2216:
2217: resource_eligible BOOLEAN := TRUE;
2218: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.ELIGIBLE_RESOURCES';
2219: BEGIN
2220: x_return_status := FND_API.G_RET_STS_SUCCESS;
2221: x_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2222: FOR I IN 1..p_resource_list.count LOOP
2217: resource_eligible BOOLEAN := TRUE;
2218: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.ELIGIBLE_RESOURCES';
2219: BEGIN
2220: x_return_status := FND_API.G_RET_STS_SUCCESS;
2221: x_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2222: FOR I IN 1..p_resource_list.count LOOP
2223: FOR J IN 1..p_final_unavailable_list.count LOOP
2224: IF p_final_unavailable_list(J).resource_id = p_resource_list(I).resource_id THEN
2225: resource_eligible := FALSE;
2259: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2260: return;
2261: END ELIGIBLE_RESOURCES;
2262:
2263: PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2264: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
2265: ,x_return_status OUT NOCOPY VARCHAR2
2266: ,x_msg_data OUT NOCOPY VARCHAR2
2267: ,x_msg_count OUT NOCOPY NUMBER) IS
2260: return;
2261: END ELIGIBLE_RESOURCES;
2262:
2263: PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2264: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
2265: ,x_return_status OUT NOCOPY VARCHAR2
2266: ,x_msg_data OUT NOCOPY VARCHAR2
2267: ,x_msg_count OUT NOCOPY NUMBER) IS
2268: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
2264: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
2265: ,x_return_status OUT NOCOPY VARCHAR2
2266: ,x_msg_data OUT NOCOPY VARCHAR2
2267: ,x_msg_count OUT NOCOPY NUMBER) IS
2268: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
2269: l_temp_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2270: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2271: loop_count NUMBER;
2272: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2265: ,x_return_status OUT NOCOPY VARCHAR2
2266: ,x_msg_data OUT NOCOPY VARCHAR2
2267: ,x_msg_count OUT NOCOPY NUMBER) IS
2268: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
2269: l_temp_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2270: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2271: loop_count NUMBER;
2272: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2273: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
2266: ,x_msg_data OUT NOCOPY VARCHAR2
2267: ,x_msg_count OUT NOCOPY NUMBER) IS
2268: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
2269: l_temp_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2270: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2271: loop_count NUMBER;
2272: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2273: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
2274: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2268: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
2269: l_temp_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2270: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2271: loop_count NUMBER;
2272: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2273: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
2274: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2275: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_TIME_COST' ;
2276: BEGIN
2269: l_temp_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2270: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2271: loop_count NUMBER;
2272: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2273: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
2274: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2275: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_TIME_COST' ;
2276: BEGIN
2277: x_return_status := FND_API.G_RET_STS_SUCCESS;
2270: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2271: loop_count NUMBER;
2272: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2273: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
2274: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2275: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_TIME_COST' ;
2276: BEGIN
2277: x_return_status := FND_API.G_RET_STS_SUCCESS;
2278: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2271: loop_count NUMBER;
2272: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
2273: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
2274: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2275: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_TIME_COST' ;
2276: BEGIN
2277: x_return_status := FND_API.G_RET_STS_SUCCESS;
2278: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2279: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2274: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2275: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_TIME_COST' ;
2276: BEGIN
2277: x_return_status := FND_API.G_RET_STS_SUCCESS;
2278: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2279: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2280: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2281: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2282: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
2275: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_TIME_COST' ;
2276: BEGIN
2277: x_return_status := FND_API.G_RET_STS_SUCCESS;
2278: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2279: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2280: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2281: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2282: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
2283: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
2276: BEGIN
2277: x_return_status := FND_API.G_RET_STS_SUCCESS;
2278: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2279: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2280: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2281: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2282: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
2283: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
2284: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2277: x_return_status := FND_API.G_RET_STS_SUCCESS;
2278: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2279: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2280: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2281: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2282: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
2283: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
2284: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2285: loop_count :=1 ;
2278: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2279: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2280: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2281: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2282: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
2283: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
2284: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2285: loop_count :=1 ;
2286: WHILE loop_count <= p_eligible_resources_list.count LOOP
2279: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2280: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2281: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2282: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
2283: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
2284: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2285: loop_count :=1 ;
2286: WHILE loop_count <= p_eligible_resources_list.count LOOP
2287: IF loop_count > 1 THEN
2280: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
2281: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
2282: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
2283: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
2284: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2285: loop_count :=1 ;
2286: WHILE loop_count <= p_eligible_resources_list.count LOOP
2287: IF loop_count > 1 THEN
2288: IF p_eligible_resources_list(loop_count).resource_id =
2299: l_temp_resources_list(l_temp_resources_list.count).quantity := p_eligible_resources_list(loop_count).quantity;
2300: l_temp_resources_list(l_temp_resources_list.count).sub_inventory := p_eligible_resources_list(loop_count).sub_inventory;
2301: l_temp_resources_list(l_temp_resources_list.count).available_date := p_eligible_resources_list(loop_count).available_date;
2302: IF loop_count = p_eligible_resources_list.count THEN
2303: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_resources_list,l_temp_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
2304: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2305: RETURN;
2306: END IF;
2307: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
2303: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_resources_list,l_temp_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
2304: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2305: RETURN;
2306: END IF;
2307: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
2308: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2309: RETURN;
2310: END IF;
2311: l_temp_options.trim(l_temp_options.count);
2311: l_temp_options.trim(l_temp_options.count);
2312: l_temp_resources_list.trim(l_temp_resources_list.count);
2313: END IF;
2314: ELSE
2315: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_resources_list,l_temp_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
2316: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2317: RETURN;
2318: END IF;
2319: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
2315: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_resources_list,l_temp_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
2316: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2317: RETURN;
2318: END IF;
2319: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
2320: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2321: RETURN;
2322: END IF;
2323: l_temp_options.trim(l_temp_options.count);
2331: l_temp_resources_list(l_temp_resources_list.count).sub_inventory := p_eligible_resources_list(loop_count).sub_inventory ;
2332: l_temp_resources_list(l_temp_resources_list.count).available_date := p_eligible_resources_list(loop_count).available_date;
2333: END IF;
2334: IF loop_count = p_eligible_resources_list.count THEN
2335: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_resources_list,l_temp_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
2336: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2337: RETURN;
2338: END IF;
2339: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
2335: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_resources_list,l_temp_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
2336: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2337: RETURN;
2338: END IF;
2339: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
2340: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2341: RETURN;
2342: END IF;
2343: l_temp_options.trim(l_temp_options.count);
2352: l_temp_resources_list(l_temp_resources_list.count).quantity := p_eligible_resources_list(loop_count).quantity;
2353: l_temp_resources_list(l_temp_resources_list.count).sub_inventory := p_eligible_resources_list(loop_count).sub_inventory ;
2354: l_temp_resources_list(l_temp_resources_list.count).available_date := p_eligible_resources_list(loop_count).available_date;
2355: IF p_eligible_resources_list.count =1 THEN
2356: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_resources_list,l_temp_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
2357: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2358: RETURN;
2359: END IF;
2360: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
2356: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_resources_list,l_temp_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
2357: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2358: RETURN;
2359: END IF;
2360: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
2361: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2362: RETURN;
2363: END IF;
2364: l_temp_options.trim(l_temp_options.count);
2378: , p_data => x_msg_data);
2379: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2380: return;
2381: END GET_TIME_COST;
2382: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
2383: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
2384: ,x_return_status OUT NOCOPY VARCHAR2
2385: ,x_msg_data OUT NOCOPY VARCHAR2
2386: ,x_msg_count OUT NOCOPY NUMBER) IS
2379: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2380: return;
2381: END GET_TIME_COST;
2382: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
2383: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
2384: ,x_return_status OUT NOCOPY VARCHAR2
2385: ,x_msg_data OUT NOCOPY VARCHAR2
2386: ,x_msg_count OUT NOCOPY NUMBER) IS
2387: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.BUILD_FINAL_LIST';
2383: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
2384: ,x_return_status OUT NOCOPY VARCHAR2
2385: ,x_msg_data OUT NOCOPY VARCHAR2
2386: ,x_msg_count OUT NOCOPY NUMBER) IS
2387: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.BUILD_FINAL_LIST';
2388: l_need_by_date date;
2389: BEGIN
2390: IF g_interval.latest_time IS NOT NULL
2391: THEN
2419: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2420: return;
2421: END BUILD_FINAL_LIST;
2422:
2423: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2424: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
2425: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
2426: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
2427: ,x_return_status OUT NOCOPY VARCHAR2
2420: return;
2421: END BUILD_FINAL_LIST;
2422:
2423: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2424: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
2425: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
2426: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
2427: ,x_return_status OUT NOCOPY VARCHAR2
2428: ,x_msg_data OUT NOCOPY VARCHAR2
2421: END BUILD_FINAL_LIST;
2422:
2423: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2424: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
2425: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
2426: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
2427: ,x_return_status OUT NOCOPY VARCHAR2
2428: ,x_msg_data OUT NOCOPY VARCHAR2
2429: ,x_msg_count OUT NOCOPY NUMBER) IS
2422:
2423: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2424: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
2425: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
2426: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
2427: ,x_return_status OUT NOCOPY VARCHAR2
2428: ,x_msg_data OUT NOCOPY VARCHAR2
2429: ,x_msg_count OUT NOCOPY NUMBER) IS
2430:
2435: and ism.from_location_id = hao.location_id
2436: and ism.to_location_id = c_to_location_id;
2437:
2438:
2439: l_resource_shipping_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2440: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2441: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
2442: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2443: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2436: and ism.to_location_id = c_to_location_id;
2437:
2438:
2439: l_resource_shipping_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2440: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2441: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
2442: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2443: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2444: L_API_NAME VARCHAR2(60) := 'CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS';
2437:
2438:
2439: l_resource_shipping_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2440: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2441: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
2442: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2443: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2444: L_API_NAME VARCHAR2(60) := 'CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS';
2445: loop_start NUMBER :=0 ;
2438:
2439: l_resource_shipping_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2440: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2441: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
2442: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2443: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2444: L_API_NAME VARCHAR2(60) := 'CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS';
2445: loop_start NUMBER :=0 ;
2446: loop_end NUMBER :=0 ;
2439: l_resource_shipping_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2440: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2441: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
2442: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2443: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2444: L_API_NAME VARCHAR2(60) := 'CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS';
2445: loop_start NUMBER :=0 ;
2446: loop_end NUMBER :=0 ;
2447: loop_min NUMBER :=100000 ;
2440: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
2441: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
2442: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2443: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
2444: L_API_NAME VARCHAR2(60) := 'CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS';
2445: loop_start NUMBER :=0 ;
2446: loop_end NUMBER :=0 ;
2447: loop_min NUMBER :=100000 ;
2448: loop_max NUMBER :=0 ;
2466: l_to_org_id NUMBER;
2467: l_loc_ship_method_count NUMBER := 0;
2468: BEGIN
2469: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2470: l_resource_shipping_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2471: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2472: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
2473: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
2474: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2467: l_loc_ship_method_count NUMBER := 0;
2468: BEGIN
2469: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2470: l_resource_shipping_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2471: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2472: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
2473: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
2474: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2475: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2468: BEGIN
2469: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2470: l_resource_shipping_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2471: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2472: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
2473: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
2474: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2475: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2476: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2469: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2470: l_resource_shipping_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2471: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2472: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
2473: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
2474: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2475: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2476: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2477: FOR I IN 1..p_eligible_resources.count LOOP
2470: l_resource_shipping_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2471: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2472: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
2473: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
2474: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2475: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2476: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2477: FOR I IN 1..p_eligible_resources.count LOOP
2478: l_to_org_id := p_eligible_resources(I).organization_id;
2471: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
2472: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
2473: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
2474: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2475: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2476: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2477: FOR I IN 1..p_eligible_resources.count LOOP
2478: l_to_org_id := p_eligible_resources(I).organization_id;
2479: OPEN location_shipping_method_count(p_eligible_resources(I).source_org, p_eligible_resources(I).destination_location_id);
2472: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
2473: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
2474: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2475: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
2476: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2477: FOR I IN 1..p_eligible_resources.count LOOP
2478: l_to_org_id := p_eligible_resources(I).organization_id;
2479: OPEN location_shipping_method_count(p_eligible_resources(I).source_org, p_eligible_resources(I).destination_location_id);
2480: FETCH location_shipping_method_count INTO l_loc_ship_method_count;
2488: END IF;
2489: END LOOP;
2490:
2491: IF do_optimize THEN
2492: CSP_SCH_INT_PVT.CONSOLIDATE_QUANTITIES(p_eligible_resources, l_ship_quantity,x_return_status,x_msg_data,x_msg_count);
2493: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2494: return;
2495: END IF;
2496: CSP_SCH_INT_PVT.GET_SHIPPING_PARAMETERS(l_ship_quantity,l_resource_shipping_parameters,x_ship_count,x_return_status,x_msg_data,x_msg_count);
2492: CSP_SCH_INT_PVT.CONSOLIDATE_QUANTITIES(p_eligible_resources, l_ship_quantity,x_return_status,x_msg_data,x_msg_count);
2493: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2494: return;
2495: END IF;
2496: CSP_SCH_INT_PVT.GET_SHIPPING_PARAMETERS(l_ship_quantity,l_resource_shipping_parameters,x_ship_count,x_return_status,x_msg_data,x_msg_count);
2497: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2498: return;
2499: END IF;
2500: x_ship_parameters := l_resource_shipping_parameters;
2803: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2804: return;
2805: END OPTIMIZE_OPTIONS;
2806:
2807: PROCEDURE CONSOLIDATE_QUANTITIES(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2808: ,x_ship_quantity OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
2809: ,x_return_status OUT NOCOPY VARCHAR2
2810: ,x_msg_data OUT NOCOPY VARCHAR2
2811: ,x_msg_count OUT NOCOPY NUMBER) IS
2804: return;
2805: END OPTIMIZE_OPTIONS;
2806:
2807: PROCEDURE CONSOLIDATE_QUANTITIES(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
2808: ,x_ship_quantity OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
2809: ,x_return_status OUT NOCOPY VARCHAR2
2810: ,x_msg_data OUT NOCOPY VARCHAR2
2811: ,x_msg_count OUT NOCOPY NUMBER) IS
2812: I NUMBER;
2811: ,x_msg_count OUT NOCOPY NUMBER) IS
2812: I NUMBER;
2813: J NUMBER;
2814: available BOOLEAN;
2815: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CONSOLIDATE_QUANTITIES';
2816: BEGIN
2817: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2818: x_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE();
2819: x_ship_quantity.trim(x_ship_quantity.count);
2814: available BOOLEAN;
2815: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CONSOLIDATE_QUANTITIES';
2816: BEGIN
2817: x_return_status := FND_API.G_RET_STS_SUCCESS ;
2818: x_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE();
2819: x_ship_quantity.trim(x_ship_quantity.count);
2820: FOR I IN 1..p_eligible_resources_list.count LOOP
2821: IF p_eligible_resources_list(I).sub_inventory IS NULL THEN
2822: IF x_ship_quantity.count >= 1 THEN
2865: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2866: return;
2867: END CONSOLIDATE_QUANTITIES;
2868:
2869: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE,
2870: x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
2871: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
2872: ,x_return_status OUT NOCOPY VARCHAR2
2873: ,x_msg_data OUT NOCOPY VARCHAR2
2866: return;
2867: END CONSOLIDATE_QUANTITIES;
2868:
2869: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE,
2870: x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
2871: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
2872: ,x_return_status OUT NOCOPY VARCHAR2
2873: ,x_msg_data OUT NOCOPY VARCHAR2
2874: ,x_msg_count OUT NOCOPY NUMBER) IS
2867: END CONSOLIDATE_QUANTITIES;
2868:
2869: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE,
2870: x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
2871: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
2872: ,x_return_status OUT NOCOPY VARCHAR2
2873: ,x_msg_data OUT NOCOPY VARCHAR2
2874: ,x_msg_count OUT NOCOPY NUMBER) IS
2875: CURSOR csp_ship_methodes(from_org varchar2,to_org varchar2) IS
2972: l_msg_data VARCHAR2(2000);
2973: l_return_status VARCHAR2(3);
2974: l_server_delivery_date DATE;
2975: l_server_cutoff_time DATE;
2976: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_SHIPPING_PARAMETERS';
2977: l_from_org_code VARCHAR2(10);
2978: l_to_org_code VARCHAR2(10);
2979: l_sysdate DATE;
2980: l_server_sys_date Date;
2994: l_need_by_date := g_interval.latest_time;
2995: ELSE
2996: l_need_by_date := g_interval.earliest_time;
2997: END IF;
2998: x_resource_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2999: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
3000: x_ship_count.trim(x_ship_count.count) ;
3001: FOR I IN 1..p_ship_quantity.count LOOP
3002: IF p_ship_quantity(I).resource_type = 'DUMMY' and
2995: ELSE
2996: l_need_by_date := g_interval.earliest_time;
2997: END IF;
2998: x_resource_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
2999: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
3000: x_ship_count.trim(x_ship_count.count) ;
3001: FOR I IN 1..p_ship_quantity.count LOOP
3002: IF p_ship_quantity(I).resource_type = 'DUMMY' and
3003: p_ship_quantity(I).resource_id = '999999999999999' or g_shipto_timezone_id is not null THEN
3274: l_timezone_id NUMBER;
3275: l_saftey_zone NUMBER;
3276: l_delivery_date DATE;
3277: l_default_unit_for_hour varchar2(3);
3278: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.GET_DELIVERY_DATE' ;
3279: BEGIN
3280: x_return_status := FND_API.G_RET_STS_SUCCESS ;
3281: l_default_unit_for_hour := FND_PROFILE.VALUE(NAME => 'CSF_UOM_HOURS');
3282: OPEN C1(p_relation_ship_id);
3388: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3389: return;
3390: END EXTEND_ATP_REC;
3391:
3392: FUNCTION CREATE_RESERVATION(p_reservation_parts IN CSP_SCH_INT_PVT.RESERVATION_REC_TYP
3393: ,x_return_status OUT NOCOPY VARCHAR2
3394: ,x_msg_data OUT NOCOPY VARCHAR2)
3395: RETURN NUMBER
3396: IS
3625: l_msg_count NUMBER;
3626: l_msg_data varchar2(1000);
3627: l_min_leadtime NUMBER := 100000000;
3628: l_shipping_methode varchar2(30);
3629: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.GET_SHIPPING_METHODE' ;
3630: BEGIN
3631: x_return_status := FND_API.G_RET_STS_SUCCESS ;
3632: l_server_time_zone_id := FND_PROFILE.VALUE(NAME => 'SERVER_TIMEZONE_ID');
3633: OPEN C3(p_from_org_id,p_to_org_id);
3633: OPEN C3(p_from_org_id,p_to_org_id);
3634: LOOP
3635: FETCH C3 INTO l_cost,l_relation_ship_id ,l_cutoff_time,l_timezone_id,l_shipping_methode ;
3636: EXIT WHEN C3%NOTFOUND;
3637: CSP_SCH_INT_PVT.GET_DELIVERY_DATE(l_relation_ship_id, x_delivery_date,x_shipping_option,x_return_status,x_msg_data,x_msg_count);
3638: IF p_timezone_id <> l_server_time_zone_id THEN
3639: HZ_TIMEZONE_PUB.Get_Time( p_api_version => 1.0,
3640: p_init_msg_list => FND_API.G_FALSE,
3641: p_source_tz_id => p_timezone_id,
3686: , p_data => x_msg_data);
3687: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3688: return;
3689: END GET_SHIPPING_METHODE;
3690: PROCEDURE CHECK_PARTS_AVAILABILITY(p_resource IN CSP_SCH_INT_PVT.csp_sch_resources_rec_typ
3691: ,p_organization_id IN NUMBER
3692: ,p_subinv_code IN VARCHAR2
3693: ,p_need_by_date IN DATE
3694: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
3690: PROCEDURE CHECK_PARTS_AVAILABILITY(p_resource IN CSP_SCH_INT_PVT.csp_sch_resources_rec_typ
3691: ,p_organization_id IN NUMBER
3692: ,p_subinv_code IN VARCHAR2
3693: ,p_need_by_date IN DATE
3694: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
3695: ,p_timezone_id IN NUMBER
3696: ,x_availability OUT NOCOPY CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1
3697: ,x_return_status OUT NOCOPY VARCHAR2
3698: ,X_MSG_DATA OUT NOCOPY VARCHAR2
3692: ,p_subinv_code IN VARCHAR2
3693: ,p_need_by_date IN DATE
3694: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
3695: ,p_timezone_id IN NUMBER
3696: ,x_availability OUT NOCOPY CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1
3697: ,x_return_status OUT NOCOPY VARCHAR2
3698: ,X_MSG_DATA OUT NOCOPY VARCHAR2
3699: ,x_msg_count OUT NOCOPY NUMBER
3700: ,p_called_from IN varchar2
3772: AND mri.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;
3773:
3774:
3775:
3776: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
3777: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
3778: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
3779: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3780: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3773:
3774:
3775:
3776: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
3777: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
3778: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
3779: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3780: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3781: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3774:
3775:
3776: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
3777: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
3778: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
3779: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3780: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3781: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3782: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
3775:
3776: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
3777: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
3778: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
3779: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3780: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3781: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3782: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
3783: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3776: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
3777: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
3778: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
3779: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3780: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3781: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3782: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
3783: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3784: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3777: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
3778: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
3779: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3780: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3781: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3782: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
3783: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3784: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3785: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
3778: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
3779: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3780: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3781: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3782: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
3783: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3784: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3785: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
3786: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
3779: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3780: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3781: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3782: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
3783: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3784: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3785: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
3786: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
3787: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
3780: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3781: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3782: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
3783: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3784: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3785: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
3786: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
3787: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
3788: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
3781: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3782: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
3783: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3784: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3785: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
3786: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
3787: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
3788: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
3789: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3782: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
3783: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3784: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3785: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
3786: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
3787: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
3788: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
3789: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3790: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3783: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3784: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3785: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
3786: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
3787: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
3788: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
3789: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3790: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3791: l_org_ship_methode org_ship_methodes_tbl_type ;
3784: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
3785: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
3786: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
3787: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
3788: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
3789: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3790: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3791: l_org_ship_methode org_ship_methodes_tbl_type ;
3792: l_supersede_items CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
3785: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
3786: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
3787: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
3788: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
3789: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3790: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3791: l_org_ship_methode org_ship_methodes_tbl_type ;
3792: l_supersede_items CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
3793: min_cost NUMBER ;
3786: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
3787: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
3788: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
3789: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3790: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
3791: l_org_ship_methode org_ship_methodes_tbl_type ;
3792: l_supersede_items CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
3793: min_cost NUMBER ;
3794: rec_pointer NUMBER;
3802: l_need_by_date DATE;
3803: previous_position NUMBER;
3804: str_length NUMBER;
3805: current_position NUMBER;
3806: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CHECK_PARTS_AVAILABILITY';
3807: g_arrival_date DATE;
3808: l_ship_methode_count NUMBER;
3809: l_assignment_set_id NUMBER;
3810: l_vendor_id NUMBER;
3814: l_temp_quantity NUMBER;
3815: l_substitute_item NUMBER;
3816: l_alternate_items_list alternate_items_table_type;
3817: l_alternate_parts CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
3818: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3819: l_atp_success BOOLEAN;
3820: check_subinventory BOOLEAN;
3821:
3822: l_att NUMBER;
3820: check_subinventory BOOLEAN;
3821:
3822: l_att NUMBER;
3823: l_onhand NUMBER;
3824: x_final_availability CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1;
3825: l_temp_subinv_code varchar2(30);
3826: try_buy_from BOOLEAN;
3827: l_temp_line_id NUMBER;
3828: quantity_fullfilled BOOLEAN := FALSE ;
3829: l_required_quantity NUMBER;
3830: l_no_of_options NUMBER;
3831: l_dest_org_id NUMBER;
3832: l_atleast_one_rec_per_line boolean;
3833: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3834: min_arrival_date DATE;
3835: l_no_of_days number;--heh
3836: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
3837: l_reservation_id number;
3832: l_atleast_one_rec_per_line boolean;
3833: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
3834: min_arrival_date DATE;
3835: l_no_of_days number;--heh
3836: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
3837: l_reservation_id number;
3838: l_res_ids CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
3839:
3840: l_primary_uom_code varchar2(10);
3844: fnd_msg_pub.initialize;
3845: IF p_timezone_id is not null THEN
3846: g_shipto_timezone_id := p_timezone_id;
3847: END IF;
3848: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
3849: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
3850: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3851: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
3852: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3845: IF p_timezone_id is not null THEN
3846: g_shipto_timezone_id := p_timezone_id;
3847: END IF;
3848: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
3849: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
3850: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3851: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
3852: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3853: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3846: g_shipto_timezone_id := p_timezone_id;
3847: END IF;
3848: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
3849: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
3850: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3851: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
3852: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3853: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3854: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
3847: END IF;
3848: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
3849: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
3850: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3851: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
3852: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3853: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3854: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
3855: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
3848: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
3849: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
3850: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3851: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
3852: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3853: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3854: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
3855: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
3856: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
3849: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
3850: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3851: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
3852: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3853: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3854: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
3855: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
3856: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
3857: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
3850: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3851: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
3852: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3853: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3854: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
3855: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
3856: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
3857: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
3858: l_org_ship_methode := org_ship_methodes_tbl_type() ;
3851: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
3852: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3853: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3854: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
3855: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
3856: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
3857: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
3858: l_org_ship_methode := org_ship_methodes_tbl_type() ;
3859: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
3852: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3853: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3854: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
3855: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
3856: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
3857: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
3858: l_org_ship_methode := org_ship_methodes_tbl_type() ;
3859: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
3860: l_subinv_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();*/
3853: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3854: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
3855: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
3856: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
3857: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
3858: l_org_ship_methode := org_ship_methodes_tbl_type() ;
3859: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
3860: l_subinv_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();*/
3861: -- x_availability := CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1();
3855: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
3856: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
3857: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
3858: l_org_ship_methode := org_ship_methodes_tbl_type() ;
3859: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
3860: l_subinv_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();*/
3861: -- x_availability := CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1();
3862: l_interval.latest_time := p_need_by_date;
3863: x_return_status := FND_API.G_RET_STS_SUCCESS;
3856: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
3857: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
3858: l_org_ship_methode := org_ship_methodes_tbl_type() ;
3859: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
3860: l_subinv_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();*/
3861: -- x_availability := CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1();
3862: l_interval.latest_time := p_need_by_date;
3863: x_return_status := FND_API.G_RET_STS_SUCCESS;
3864: l_alternate_items_list := alternate_items_table_type();
3857: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
3858: l_org_ship_methode := org_ship_methodes_tbl_type() ;
3859: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
3860: l_subinv_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();*/
3861: -- x_availability := CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1();
3862: l_interval.latest_time := p_need_by_date;
3863: x_return_status := FND_API.G_RET_STS_SUCCESS;
3864: l_alternate_items_list := alternate_items_table_type();
3865: l_final_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3861: -- x_availability := CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1();
3862: l_interval.latest_time := p_need_by_date;
3863: x_return_status := FND_API.G_RET_STS_SUCCESS;
3864: l_alternate_items_list := alternate_items_table_type();
3865: l_final_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3866: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3867: l_timezone_id := p_timezone_id ;
3868: IF p_organization_id IS NOT NULL THEN
3869: l_temp_org_id := p_organization_id;
3862: l_interval.latest_time := p_need_by_date;
3863: x_return_status := FND_API.G_RET_STS_SUCCESS;
3864: l_alternate_items_list := alternate_items_table_type();
3865: l_final_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3866: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
3867: l_timezone_id := p_timezone_id ;
3868: IF p_organization_id IS NOT NULL THEN
3869: l_temp_org_id := p_organization_id;
3870: l_timezone_id := p_timezone_id ;
3886: CLOSE C1;
3887: l_resources.extend;
3888: l_resources(1).resource_id := p_resource.resource_id;
3889: l_resources(1).resource_type := p_resource.resource_type;
3890: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(l_resources, l_resource_org_subinv , l_return_status,x_msg_data,x_msg_count);
3891: l_temp_org_id := l_resource_org_subinv(1).organization_id;
3892: l_temp_subinv_code := l_resource_org_subinv(1).sub_inv_code;
3893: ELSE
3894: l_resources.extend;
3978: l_reservation_parts.quantity_needed := least(l_final_available_list(l_final_available_list.count).available_quantity,l_unavailable_list(l_unavailable_list.count).quantity);
3979: l_reservation_parts.sub_inventory_code := l_final_available_list(l_final_available_list.count).sub_inventory;
3980: l_reservation_parts.line_id := l_final_available_list(l_final_available_list.count).line_id ;
3981: l_reservation_parts.revision := l_final_available_list(l_final_available_list.count).revision;
3982: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
3983:
3984: IF l_reservation_id <= 0 THEN
3985: x_return_status := FND_API.G_RET_STS_ERROR;
3986: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
4109: l_reservation_parts.quantity_needed := least(l_final_available_list(l_final_available_list.count).available_quantity,l_unavailable_list(l_unavailable_list.count).quantity) ;
4110: l_reservation_parts.sub_inventory_code := l_final_available_list(l_final_available_list.count).sub_inventory;
4111: l_reservation_parts.line_id := l_final_available_list(l_final_available_list.count).line_id ;
4112: l_reservation_parts.revision := l_final_available_list(l_final_available_list.count).revision;
4113: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
4114:
4115: IF l_reservation_id <= 0 THEN
4116: x_return_status := FND_API.G_RET_STS_ERROR;
4117: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
4133: l_alternate_items_list := alternate_items_table_type();
4134: END IF;
4135: END LOOP;
4136: for i in 1..l_res_ids.count loop
4137: CSP_SCH_INT_PVT.DELETE_RESERVATION(l_res_ids(i),x_return_status,x_msg_data);
4138: end loop;
4139: IF fnd_profile.value(name => 'CSP_CHECK_ATP')= 'ALWAYS' or
4140: fnd_profile.value(name => 'CSP_CHECK_ATP')= 'PRONLY' THEN
4141: IF l_unavailable_list.count >=1 THEN
4138: end loop;
4139: IF fnd_profile.value(name => 'CSP_CHECK_ATP')= 'ALWAYS' or
4140: fnd_profile.value(name => 'CSP_CHECK_ATP')= 'PRONLY' THEN
4141: IF l_unavailable_list.count >=1 THEN
4142: CSP_SCH_INT_PVT.DO_ATP_CHECK(l_unavailable_list,l_interval,l_available_list,l_final_unavailable_list,l_return_status,x_msg_data,x_msg_count);
4143: END IF;
4144:
4145: IF l_available_list.count > 0 THEN
4146: FOR K IN 1..p_parts_list.count LOOP
4146: FOR K IN 1..p_parts_list.count LOOP
4147: l_atleast_one_rec_per_line := FALSE;
4148: FOR J IN 1..l_available_list.count LOOP
4149: IF l_available_list(J).line_id = p_parts_list(K).line_id THEN
4150: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4151: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
4152: l_temp_avail_list.extend;
4153: l_temp_avail_list(1) := l_available_list(J);
4154: l_temp_avail_list(1).destination_location_id := p_location_id;
4147: l_atleast_one_rec_per_line := FALSE;
4148: FOR J IN 1..l_available_list.count LOOP
4149: IF l_available_list(J).line_id = p_parts_list(K).line_id THEN
4150: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4151: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
4152: l_temp_avail_list.extend;
4153: l_temp_avail_list(1) := l_available_list(J);
4154: l_temp_avail_list(1).destination_location_id := p_location_id;
4155: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_avail_list,l_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
4151: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
4152: l_temp_avail_list.extend;
4153: l_temp_avail_list(1) := l_available_list(J);
4154: l_temp_avail_list(1).destination_location_id := p_location_id;
4155: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_avail_list,l_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
4156:
4157:
4158: min_cost := null; l_temp_avail_list.trim;
4159: min_arrival_date := null;
4472: l_return_status VARCHAR2(128);
4473: l_qr NUMBER;
4474: l_qs NUMBER;
4475: l_temp_reserv_quantity NUMBER;
4476: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CHECK_LOCAl_INVENTORY';
4477: l_is_revision_control boolean := null;
4478: BEGIN
4479: IF p_revision IS NOT NULL THEN
4480: l_is_revision_control := TRUE;
4543: ,p_called_by => 1);
4544:
4545:
4546: END TASKS_POST_INSERT;
4547: PROCEDURE strip_into_lines(px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
4548: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
4549: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
4550: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4551: ,x_msg_data OUT NOCOPY varchar2
4544:
4545:
4546: END TASKS_POST_INSERT;
4547: PROCEDURE strip_into_lines(px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
4548: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
4549: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
4550: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4551: ,x_msg_data OUT NOCOPY varchar2
4552: ,x_return_status OUT NOCOPY varchar2
4545:
4546: END TASKS_POST_INSERT;
4547: PROCEDURE strip_into_lines(px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
4548: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
4549: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
4550: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4551: ,x_msg_data OUT NOCOPY varchar2
4552: ,x_return_status OUT NOCOPY varchar2
4553: ,x_msg_count OUT NOCOPY NUMBER) IS
4546: END TASKS_POST_INSERT;
4547: PROCEDURE strip_into_lines(px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
4548: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
4549: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
4550: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4551: ,x_msg_data OUT NOCOPY varchar2
4552: ,x_return_status OUT NOCOPY varchar2
4553: ,x_msg_count OUT NOCOPY NUMBER) IS
4554:
4553: ,x_msg_count OUT NOCOPY NUMBER) IS
4554:
4555:
4556: l_org_ship_methode org_ship_methodes_tbl_type ;
4557: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4558: min_cost number;
4559: previous_position NUMBER;
4560: str_length NUMBER;
4561: l_ship_methode_count NUMBER;
4562: g_arrival_date DATE;
4563: current_position NUMBER;
4564:
4565: BEGIN
4566: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4567: l_org_ship_methode := org_ship_methodes_tbl_type() ;
4568: FOR I IN 1..px_options.count LOOP
4569: IF I =1 THEN
4570: min_cost := px_options(I).transfer_cost;
4654: END LOOP;
4655: END strip_into_lines;
4656: PROCEDURE ws_Check_engineers_subinv(p_resource_type IN varchar2
4657: ,p_resource_id IN NUMBER
4658: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
4659: ,p_include_alternate IN varchar2
4660: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
4661: ,x_return_status OUT NOCOPY varchar2
4662: ,x_msg_data OUT NOCOPY varchar2
4656: PROCEDURE ws_Check_engineers_subinv(p_resource_type IN varchar2
4657: ,p_resource_id IN NUMBER
4658: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
4659: ,p_include_alternate IN varchar2
4660: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
4661: ,x_return_status OUT NOCOPY varchar2
4662: ,x_msg_data OUT NOCOPY varchar2
4663: ,x_msg_count OUT NOCOPY NUMBER) IS
4664: l_att NUMBER:=0;
4664: l_att NUMBER:=0;
4665: l_onhand NUMBER := 0;
4666: l_org_id NUMBER;
4667: l_subinv_code varchar2(30);
4668: l_items csp_sch_int_pvt.alternate_items_table_type;
4669:
4670: CURSOR get_all_subinventories(l_resource_id number, l_resource_type varchar2) IS
4671: SELECT cil.ORGANIZATION_ID, cil.SUBINVENTORY_CODE
4672: FROM CSP_INV_LOC_ASSIGNMENTS cil,csp_sec_inventories csi
4711: AND organization_id = org_id;
4712: l_primary_uom varchar2(500);
4713:
4714: BEGIN
4715: l_items := csp_sch_int_pvt.alternate_items_table_type();
4716: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
4717: OPEN get_default_subinventory(p_resource_id,p_resource_type);
4718: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
4719: CLOSE get_default_subinventory;
4712: l_primary_uom varchar2(500);
4713:
4714: BEGIN
4715: l_items := csp_sch_int_pvt.alternate_items_table_type();
4716: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
4717: OPEN get_default_subinventory(p_resource_id,p_resource_type);
4718: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
4719: CLOSE get_default_subinventory;
4720: l_items.extend;
4809: END LOOP;
4810: END LOOP;
4811: CLOSE get_all_subinventories;
4812: END ws_Check_engineers_subinv;
4813: PROCEDURE ws_Check_other_eng_subinv(p_resource_list IN csp_sch_int_pvt.csp_ws_resource_table_type
4814: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
4815: ,p_include_alternate IN varchar2
4816: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
4817: ,x_return_status OUT NOCOPY varchar2
4810: END LOOP;
4811: CLOSE get_all_subinventories;
4812: END ws_Check_engineers_subinv;
4813: PROCEDURE ws_Check_other_eng_subinv(p_resource_list IN csp_sch_int_pvt.csp_ws_resource_table_type
4814: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
4815: ,p_include_alternate IN varchar2
4816: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
4817: ,x_return_status OUT NOCOPY varchar2
4818: ,x_msg_data OUT NOCOPY varchar2
4812: END ws_Check_engineers_subinv;
4813: PROCEDURE ws_Check_other_eng_subinv(p_resource_list IN csp_sch_int_pvt.csp_ws_resource_table_type
4814: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
4815: ,p_include_alternate IN varchar2
4816: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
4817: ,x_return_status OUT NOCOPY varchar2
4818: ,x_msg_data OUT NOCOPY varchar2
4819: ,x_msg_count OUT NOCOPY NUMBER) IS
4820:
4821: l_att NUMBER:=0;
4822: l_onhand NUMBER := 0;
4823: l_org_id NUMBER;
4824: l_subinv_code varchar2(30);
4825: l_items csp_sch_int_pvt.alternate_items_table_type;
4826:
4827: CURSOR get_default_subinventory(l_resource_id number, l_resource_type varchar2) IS
4828: SELECT ORGANIZATION_ID, SUBINVENTORY_CODE
4829: FROM CSP_INV_LOC_ASSIGNMENTS
4858: AND organization_id = org_id;
4859: l_primary_uom varchar2(500);
4860:
4861: BEGIN
4862: l_items := csp_sch_int_pvt.alternate_items_table_type();
4863: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
4864: --null;
4865: /* OPEN get_default_subinventory(p_resource_list(1).resource_id,p_resource_list(1).resource_type);
4866: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
4859: l_primary_uom varchar2(500);
4860:
4861: BEGIN
4862: l_items := csp_sch_int_pvt.alternate_items_table_type();
4863: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
4864: --null;
4865: /* OPEN get_default_subinventory(p_resource_list(1).resource_id,p_resource_list(1).resource_type);
4866: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
4867: CLOSE get_default_subinventory;
4984: END ws_Check_other_eng_subinv;
4985:
4986: PROCEDURE ws_Check_organizations(p_resource_type IN varchar2
4987: ,p_resource_id IN NUMBER
4988: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
4989: ,p_include_alternate IN varchar2
4990: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
4991: ,x_return_status OUT NOCOPY varchar2
4992: ,x_msg_data OUT NOCOPY varchar2
4986: PROCEDURE ws_Check_organizations(p_resource_type IN varchar2
4987: ,p_resource_id IN NUMBER
4988: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
4989: ,p_include_alternate IN varchar2
4990: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
4991: ,x_return_status OUT NOCOPY varchar2
4992: ,x_msg_data OUT NOCOPY varchar2
4993: ,x_msg_count OUT NOCOPY NUMBER) IS
4994:
4995: l_att NUMBER:=0;
4996: l_onhand NUMBER := 0;
4997: l_org_id NUMBER;
4998: l_subinv_code varchar2(30);
4999: l_items csp_sch_int_pvt.alternate_items_table_type;
5000: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5001: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
5002: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5003: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
4996: l_onhand NUMBER := 0;
4997: l_org_id NUMBER;
4998: l_subinv_code varchar2(30);
4999: l_items csp_sch_int_pvt.alternate_items_table_type;
5000: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5001: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
5002: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5003: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5004: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
4997: l_org_id NUMBER;
4998: l_subinv_code varchar2(30);
4999: l_items csp_sch_int_pvt.alternate_items_table_type;
5000: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5001: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
5002: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5003: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5004: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5005: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
4998: l_subinv_code varchar2(30);
4999: l_items csp_sch_int_pvt.alternate_items_table_type;
5000: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5001: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
5002: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5003: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5004: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5005: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5006: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
4999: l_items csp_sch_int_pvt.alternate_items_table_type;
5000: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5001: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
5002: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5003: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5004: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5005: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5006: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
5007: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
5000: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5001: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
5002: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5003: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5004: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5005: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5006: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
5007: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
5008: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5001: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
5002: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5003: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5004: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5005: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5006: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
5007: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
5008: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5009: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5002: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5003: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5004: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5005: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5006: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
5007: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
5008: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5009: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5010: l_return_status varchar2(10);
5003: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
5004: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5005: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5006: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
5007: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
5008: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5009: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5010: l_return_status varchar2(10);
5011: p_need_by_date date := null ;
5004: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
5005: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5006: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
5007: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
5008: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5009: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5010: l_return_status varchar2(10);
5011: p_need_by_date date := null ;
5012: l_atleast_one_rec BOOLEAN;
5005: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5006: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
5007: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
5008: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5009: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
5010: l_return_status varchar2(10);
5011: p_need_by_date date := null ;
5012: l_atleast_one_rec BOOLEAN;
5013: min_cost NUMBER;
5051: where lookup_code = c_ship_method;
5052:
5053: BEGIN
5054:
5055: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
5056: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
5057: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
5058:
5059: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
5052:
5053: BEGIN
5054:
5055: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
5056: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
5057: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
5058:
5059: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
5060: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
5053: BEGIN
5054:
5055: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
5056: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
5057: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
5058:
5059: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
5060: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
5061: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5055: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
5056: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
5057: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
5058:
5059: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
5060: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
5061: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5062: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5063: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
5056: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
5057: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
5058:
5059: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
5060: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
5061: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5062: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5063: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
5064: l_items := csp_sch_int_pvt.alternate_items_table_type();
5057: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
5058:
5059: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
5060: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
5061: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5062: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5063: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
5064: l_items := csp_sch_int_pvt.alternate_items_table_type();
5065: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
5058:
5059: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
5060: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
5061: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5062: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5063: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
5064: l_items := csp_sch_int_pvt.alternate_items_table_type();
5065: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
5066: OPEN get_default_subinventory(p_resource_id,p_resource_type);
5059: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
5060: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
5061: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5062: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5063: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
5064: l_items := csp_sch_int_pvt.alternate_items_table_type();
5065: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
5066: OPEN get_default_subinventory(p_resource_id,p_resource_type);
5067: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
5060: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
5061: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5062: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5063: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
5064: l_items := csp_sch_int_pvt.alternate_items_table_type();
5065: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
5066: OPEN get_default_subinventory(p_resource_id,p_resource_type);
5067: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
5068: CLOSE get_default_subinventory;
5061: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5062: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5063: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
5064: l_items := csp_sch_int_pvt.alternate_items_table_type();
5065: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
5066: OPEN get_default_subinventory(p_resource_id,p_resource_type);
5067: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
5068: CLOSE get_default_subinventory;
5069: l_items.extend;
5094: l_unavailable_list(l_unavailable_list.count).item_type := l_items(J).relation_type ;
5095: l_unavailable_list(l_unavailable_list.count).line_id := 1;
5096: END LOOP;
5097: IF l_unavailable_list.count >=1 THEN
5098: CSP_SCH_INT_PVT.DO_ATP_CHECK(l_unavailable_list,l_interval,l_available_list,l_final_unavailable_list,l_return_status,x_msg_data,x_msg_count);
5099: END IF;
5100: IF l_available_list.count > 0 THEN
5101: l_atleast_one_rec := FALSE;
5102: FOR J IN 1..l_available_list.count LOOP
5101: l_atleast_one_rec := FALSE;
5102: FOR J IN 1..l_available_list.count LOOP
5103: l_temp_avail_list.extend;
5104: l_temp_avail_list(1) := l_available_list(J);
5105: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
5106: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5107: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_avail_list,l_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
5108: l_temp_avail_list.trim;
5109: min_cost := null;
5102: FOR J IN 1..l_available_list.count LOOP
5103: l_temp_avail_list.extend;
5104: l_temp_avail_list(1) := l_available_list(J);
5105: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
5106: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5107: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_avail_list,l_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
5108: l_temp_avail_list.trim;
5109: min_cost := null;
5110: min_arrival_date := null;
5103: l_temp_avail_list.extend;
5104: l_temp_avail_list(1) := l_available_list(J);
5105: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
5106: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
5107: CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS(l_temp_avail_list,l_options,l_ship_count,l_res_ship_parameters,x_return_status,x_msg_data,x_msg_count);
5108: l_temp_avail_list.trim;
5109: min_cost := null;
5110: min_arrival_date := null;
5111: rec_pointer := null;
5198: END IF;
5199: END LOOP;
5200:
5201: END ws_Check_organizations;
5202: PROCEDURE get_alternates(p_parts_rec IN csp_sch_int_pvt.CSP_PARTS_REC_TYPE
5203: ,p_org_id IN NUMBER
5204: ,px_alternate_items IN OUT NOCOPY csp_sch_int_pvt.alternate_items_table_type
5205: ,x_return_status OUT NOCOPY varchar2
5206: ,x_msg_data OUT NOCOPY varchar2
5200:
5201: END ws_Check_organizations;
5202: PROCEDURE get_alternates(p_parts_rec IN csp_sch_int_pvt.CSP_PARTS_REC_TYPE
5203: ,p_org_id IN NUMBER
5204: ,px_alternate_items IN OUT NOCOPY csp_sch_int_pvt.alternate_items_table_type
5205: ,x_return_status OUT NOCOPY varchar2
5206: ,x_msg_data OUT NOCOPY varchar2
5207: ,x_msg_count OUT NOCOPY NUMBER) IS
5208:
5559: OPEN get_reservations ;
5560: LOOP
5561: FETCH get_reservations INTO l_reserv_id,l_req_details_line_id;
5562: EXIT WHEN get_reservations%NOTFOUND;
5563: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_reserv_id,l_return_status,x_msg_data,x_msg_count);
5564: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5565: x_return_status := FND_API.G_RET_STS_SUCCESS;
5566: ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
5567: delete from csp_req_line_details where req_line_detail_id = l_req_details_line_id;
5575: OPEN get_order_status(l_order_id);
5576: FETCH get_order_status INTO l_status;
5577: CLOSE get_order_status;
5578: IF l_status <> 'CANCELLED' THEN
5579: CSP_SCH_INT_PVT.CANCEL_ORDER(l_order_id,l_return_status,x_msg_data,x_msg_count);
5580: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5581: x_return_status := FND_API.G_RET_STS_SUCCESS;
5582: ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
5583: open get_line_details(l_order_id);
5636: and crld.requirement_line_id = crl.requirement_line_id
5637: and crh.requirement_header_id = crl.requirement_header_id
5638: and crh.task_id is not null;
5639:
5640: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
5641: l_reservation_id NUMBER;
5642: l_req_line_detali_id NUMBER;
5643:
5644: BEGIN
5651: l_reservation_parts.quantity_needed := grd.transaction_quantity ;
5652: l_reservation_parts.sub_inventory_code := grd.subinv_code ;
5653: l_reservation_parts.line_id := p_transaction_id;
5654: l_reservation_parts.revision := grd.revision;
5655: l_reservation_id := csp_sch_int_pvt.CREATE_RESERVATION(l_reservation_parts
5656: ,x_return_status
5657: ,x_msg_data );
5658: l_req_line_detali_id := NULL;
5659: IF l_reservation_id IS NOT NULL AND l_reservation_id >0 THEN
5789: WHEN OTHERS THEN
5790: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5791:
5792: END DELETE_RESERVATION;
5793: END CSP_SCH_INT_PVT;