[Home] [Help]
1: package body csp_sch_int_pvt as
2: /* $Header: cspgscib.pls 120.68.12020000.14 2013/04/04 10:44:06 cmgandhi ship $
3: * */
4: -- Start of Comments
5: -- Package name : CSP_SCH_INT_PVT
1: package body csp_sch_int_pvt as
2: /* $Header: cspgscib.pls 120.68.12020000.14 2013/04/04 10:44:06 cmgandhi ship $
3: * */
4: -- Start of Comments
5: -- Package name : CSP_SCH_INT_PVT
6: -- Purpose :
7: -- History :
8: -- NOTE :
9: -- End of Comments
7: -- History :
8: -- NOTE :
9: -- End of Comments
10:
11: G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_SCH_INT_PVT';
12: G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgscib.pls';
13:
14: g_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
15: g_schedular_call VARCHAR2(1) := 'Y' ;
10:
11: G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_SCH_INT_PVT';
12: G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgscib.pls';
13:
14: g_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
15: g_schedular_call VARCHAR2(1) := 'Y' ;
16: g_shipto_timezone_id NUMBER;
17: g_earliest_delivery_date Date := sysdate + 365 * 10;
18:
20: ,resource_type VARCHAR2(30)
21: ,organization_id NUMBER
22: ,sub_inv_code VARCHAR2(10));
23:
24: TYPE CSP_RESOURCE_ORG_tbl_TYP IS TABLE OF CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_REC_TYP ;
25:
26: TYPE CSP_SHIP_PARAMETERS_REC_TYPE IS RECORD( resource_id NUMBER
27: ,resource_type VARCHAR2(30)
28: ,lead_time NUMBER
29: ,transfer_cost NUMBER
30: ,shipping_methodes VARCHAR2(4000)
31: ,arrival_date DATE);
32:
33: TYPE CSP_SHIP_PARAMETERS_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_REC_TYPE ;
34:
35: TYPE CSP_ORGS_SHIP_PARAM_REC_TYPE IS RECORD(resource_id NUMBER
36: ,resource_type VARCHAR2(30)
37: ,from_org_id NUMBER
41: ,transfer_cost NUMBER
42: ,shipping_method VARCHAR2(30)
43: ,delivery_time DATE);
44:
45: TYPE CSP_ORGS_SHIP_PARAM_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_REC_TYPE;
46:
47:
48: TYPE CSP_SHIP_QUANTITY_REC_TYPE IS RECORD(resource_id NUMBER
49: ,resource_type VARCHAR2(30)
51: ,to_org_id NUMBER
52: ,quantity NUMBER
53: ,destination_location_id NUMBER);
54:
55: TYPE CSP_SHIP_QUANTITY_TBL_TYPE IS TABLE OF CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_REC_TYPE ;
56:
57: TYPE CSP_SHIP_METHOD_COUNT_REC_TYP IS RECORD(from_org_id NUMBER
58: ,to_org_id NUMBER
59: ,shipping_methodes NUMBER
60: ,min_leadtime NUMBER
61: ,max_leadtime NUMBER);
62:
63:
64: TYPE CSP_SHIP_METHOD_COUNT_TBL_TYP IS TABLE OF CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_REC_TYP ;
65:
66:
67: TYPE org_ship_methodes IS RECORD(from_org NUMBER,
68: to_org NUMBER,
67: TYPE org_ship_methodes IS RECORD(from_org NUMBER,
68: to_org NUMBER,
69: shipping_methode varchar2(30));
70:
71: TYPE org_ship_methodes_tbl_type IS TABLE OF csp_sch_int_pvt.org_ship_methodes ;
72: TYPE csp_req_line_details_rec_typ is RECORD(req_line_detail_id NUMBER
73: ,requirement_line_id NUMBER
74: ,source_type varchar2(30)
75: ,source_id NUMBER);
79: begin
80: --dbms_output.put_line(p_procedure||' - '||p_message);
81: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
82: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
83: 'csp.plsql.csp_sch_int_pvt.'||p_procedure,
84: p_message);
85: end if;
86: end;
87:
84: p_message);
85: end if;
86: end;
87:
88: PROCEDURE GET_ORGANIZATION_SUBINV(p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
89: ,x_resource_org_subinv OUT NOCOPY CSP_SCH_INT_PVT.csp_resource_org_tbl_typ
90: ,x_return_status OUT NOCOPY VARCHAR2
91: ,x_msg_data OUT NOCOPY VARCHAR2
92: ,x_msg_count OUT NOCOPY NUMBER);
85: end if;
86: end;
87:
88: PROCEDURE GET_ORGANIZATION_SUBINV(p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
89: ,x_resource_org_subinv OUT NOCOPY CSP_SCH_INT_PVT.csp_resource_org_tbl_typ
90: ,x_return_status OUT NOCOPY VARCHAR2
91: ,x_msg_data OUT NOCOPY VARCHAR2
92: ,x_msg_count OUT NOCOPY NUMBER);
93:
92: ,x_msg_count OUT NOCOPY NUMBER);
93:
94: PROCEDURE GET_PARTS_LIST(p_task_id IN NUMBER
95: ,p_likelihood IN NUMBER
96: ,x_parts_list OUT NOCOPY CSP_SCH_INT_PVT.csp_parts_tbl_typ1
97: ,x_return_status OUT NOCOPY VARCHAR2
98: ,x_msg_data OUT NOCOPY VARCHAR2
99: ,x_msg_count OUT NOCOPY NUMBER);
100:
97: ,x_return_status OUT NOCOPY VARCHAR2
98: ,x_msg_data OUT NOCOPY VARCHAR2
99: ,x_msg_count OUT NOCOPY NUMBER);
100:
101: PROCEDURE CHECK_LOCAl_INVENTORY( p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
102: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
103: ,p_trunk IN BOOLEAN
104: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
105: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
98: ,x_msg_data OUT NOCOPY VARCHAR2
99: ,x_msg_count OUT NOCOPY NUMBER);
100:
101: PROCEDURE CHECK_LOCAl_INVENTORY( p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
102: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
103: ,p_trunk IN BOOLEAN
104: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
105: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
106: ,x_return_status OUT NOCOPY VARCHAR2
100:
101: PROCEDURE CHECK_LOCAl_INVENTORY( p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
102: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
103: ,p_trunk IN BOOLEAN
104: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
105: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
106: ,x_return_status OUT NOCOPY VARCHAR2
107: ,x_msg_data OUT NOCOPY VARCHAR2
108: ,x_msg_count OUT NOCOPY NUMBER);
101: PROCEDURE CHECK_LOCAl_INVENTORY( p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
102: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
103: ,p_trunk IN BOOLEAN
104: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
105: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
106: ,x_return_status OUT NOCOPY VARCHAR2
107: ,x_msg_data OUT NOCOPY VARCHAR2
108: ,x_msg_count OUT NOCOPY NUMBER);
109:
106: ,x_return_status OUT NOCOPY VARCHAR2
107: ,x_msg_data OUT NOCOPY VARCHAR2
108: ,x_msg_count OUT NOCOPY NUMBER);
109:
110: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
111: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
112: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
113: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
114: ,x_return_status OUT NOCOPY VARCHAR2
107: ,x_msg_data OUT NOCOPY VARCHAR2
108: ,x_msg_count OUT NOCOPY NUMBER);
109:
110: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
111: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
112: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
113: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
114: ,x_return_status OUT NOCOPY VARCHAR2
115: ,x_msg_data OUT NOCOPY VARCHAR2
108: ,x_msg_count OUT NOCOPY NUMBER);
109:
110: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
111: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
112: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
113: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
114: ,x_return_status OUT NOCOPY VARCHAR2
115: ,x_msg_data OUT NOCOPY VARCHAR2
116: ,x_msg_count OUT NOCOPY NUMBER);
109:
110: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
111: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
112: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
113: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
114: ,x_return_status OUT NOCOPY VARCHAR2
115: ,x_msg_data OUT NOCOPY VARCHAR2
116: ,x_msg_count OUT NOCOPY NUMBER);
117:
114: ,x_return_status OUT NOCOPY VARCHAR2
115: ,x_msg_data OUT NOCOPY VARCHAR2
116: ,x_msg_count OUT NOCOPY NUMBER);
117:
118: PROCEDURE SPARES_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
119: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
120: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
121: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
122: ,x_return_status OUT NOCOPY VARCHAR2
115: ,x_msg_data OUT NOCOPY VARCHAR2
116: ,x_msg_count OUT NOCOPY NUMBER);
117:
118: PROCEDURE SPARES_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
119: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
120: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
121: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
122: ,x_return_status OUT NOCOPY VARCHAR2
123: ,x_msg_data OUT NOCOPY VARCHAR2
116: ,x_msg_count OUT NOCOPY NUMBER);
117:
118: PROCEDURE SPARES_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
119: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
120: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
121: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
122: ,x_return_status OUT NOCOPY VARCHAR2
123: ,x_msg_data OUT NOCOPY VARCHAR2
124: ,x_msg_count OUT NOCOPY NUMBER);
117:
118: PROCEDURE SPARES_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
119: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
120: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
121: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
122: ,x_return_status OUT NOCOPY VARCHAR2
123: ,x_msg_data OUT NOCOPY VARCHAR2
124: ,x_msg_count OUT NOCOPY NUMBER);
125:
122: ,x_return_status OUT NOCOPY VARCHAR2
123: ,x_msg_data OUT NOCOPY VARCHAR2
124: ,x_msg_count OUT NOCOPY NUMBER);
125:
126: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
127: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
128: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
129: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
130: ,x_return_status OUT NOCOPY VARCHAR2
123: ,x_msg_data OUT NOCOPY VARCHAR2
124: ,x_msg_count OUT NOCOPY NUMBER);
125:
126: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
127: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
128: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
129: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
130: ,x_return_status OUT NOCOPY VARCHAR2
131: ,x_msg_data OUT NOCOPY VARCHAR2
124: ,x_msg_count OUT NOCOPY NUMBER);
125:
126: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
127: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
128: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
129: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
130: ,x_return_status OUT NOCOPY VARCHAR2
131: ,x_msg_data OUT NOCOPY VARCHAR2
132: ,x_msg_count OUT NOCOPY NUMBER);
125:
126: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
127: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
128: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
129: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
130: ,x_return_status OUT NOCOPY VARCHAR2
131: ,x_msg_data OUT NOCOPY VARCHAR2
132: ,x_msg_count OUT NOCOPY NUMBER);
133:
130: ,x_return_status OUT NOCOPY VARCHAR2
131: ,x_msg_data OUT NOCOPY VARCHAR2
132: ,x_msg_count OUT NOCOPY NUMBER);
133:
134: PROCEDURE CONSOLIDATE_QUANTITIES(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
135: ,x_ship_quantity OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
136: ,x_return_status OUT NOCOPY VARCHAR2
137: ,X_MSG_DATA OUT NOCOPY VARCHAR2
138: ,x_msg_count OUT NOCOPY NUMBER);
131: ,x_msg_data OUT NOCOPY VARCHAR2
132: ,x_msg_count OUT NOCOPY NUMBER);
133:
134: PROCEDURE CONSOLIDATE_QUANTITIES(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
135: ,x_ship_quantity OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
136: ,x_return_status OUT NOCOPY VARCHAR2
137: ,X_MSG_DATA OUT NOCOPY VARCHAR2
138: ,x_msg_count OUT NOCOPY NUMBER);
139:
140:
141: PROCEDURE EXTEND_ATP_REC(p_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_REC_TYP
142: ,x_return_status OUT NOCOPY VARCHAR2);
143:
144: /*PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
145: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tab_typ
146: ,x_return_status OUT NOCOPY VARCHAR2);*/
147:
148: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
141: PROCEDURE EXTEND_ATP_REC(p_atp_rec IN OUT NOCOPY MRP_ATP_PUB.ATP_REC_TYP
142: ,x_return_status OUT NOCOPY VARCHAR2);
143:
144: /*PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
145: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tab_typ
146: ,x_return_status OUT NOCOPY VARCHAR2);*/
147:
148: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
149: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
144: /*PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
145: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tab_typ
146: ,x_return_status OUT NOCOPY VARCHAR2);*/
147:
148: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
149: ,px_options IN 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_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tab_typ
146: ,x_return_status OUT NOCOPY VARCHAR2);*/
147:
148: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
149: ,px_options IN 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:
150: ,x_return_status OUT NOCOPY VARCHAR2
151: ,x_msg_data OUT NOCOPY VARCHAR2
152: ,x_msg_count OUT NOCOPY NUMBER);
153:
154: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
155: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
156: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
157: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
158: ,x_return_status OUT NOCOPY VARCHAR2
151: ,x_msg_data OUT NOCOPY VARCHAR2
152: ,x_msg_count OUT NOCOPY NUMBER);
153:
154: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
155: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
156: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
157: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
158: ,x_return_status OUT NOCOPY VARCHAR2
159: ,x_msg_data OUT NOCOPY VARCHAR2
152: ,x_msg_count OUT NOCOPY NUMBER);
153:
154: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
155: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
156: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
157: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
158: ,x_return_status OUT NOCOPY VARCHAR2
159: ,x_msg_data OUT NOCOPY VARCHAR2
160: ,x_msg_count OUT NOCOPY NUMBER);
153:
154: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
155: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
156: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
157: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
158: ,x_return_status OUT NOCOPY VARCHAR2
159: ,x_msg_data OUT NOCOPY VARCHAR2
160: ,x_msg_count OUT NOCOPY NUMBER);
161:
158: ,x_return_status OUT NOCOPY VARCHAR2
159: ,x_msg_data OUT NOCOPY VARCHAR2
160: ,x_msg_count OUT NOCOPY NUMBER);
161:
162: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
163: ,x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
164: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
165: ,x_return_status OUT NOCOPY VARCHAR2
166: ,x_msg_data OUT NOCOPY VARCHAR2
159: ,x_msg_data OUT NOCOPY VARCHAR2
160: ,x_msg_count OUT NOCOPY NUMBER);
161:
162: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
163: ,x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
164: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
165: ,x_return_status OUT NOCOPY VARCHAR2
166: ,x_msg_data OUT NOCOPY VARCHAR2
167: ,x_msg_count OUT NOCOPY NUMBER);
160: ,x_msg_count OUT NOCOPY NUMBER);
161:
162: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
163: ,x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
164: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
165: ,x_return_status OUT NOCOPY VARCHAR2
166: ,x_msg_data OUT NOCOPY VARCHAR2
167: ,x_msg_count OUT NOCOPY NUMBER);
168:
165: ,x_return_status OUT NOCOPY VARCHAR2
166: ,x_msg_data OUT NOCOPY VARCHAR2
167: ,x_msg_count OUT NOCOPY NUMBER);
168:
169: PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
170: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
171: ,x_return_status OUT NOCOPY VARCHAR2
172: ,x_msg_data OUT NOCOPY VARCHAR2
173: ,x_msg_count OUT NOCOPY NUMBER);
166: ,x_msg_data OUT NOCOPY VARCHAR2
167: ,x_msg_count OUT NOCOPY NUMBER);
168:
169: PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
170: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
171: ,x_return_status OUT NOCOPY VARCHAR2
172: ,x_msg_data OUT NOCOPY VARCHAR2
173: ,x_msg_count OUT NOCOPY NUMBER);
174:
197: ,x_reservation_id OUT NOCOPY NUMBER
198: ,x_msg_data OUT NOCOPY VARCHAR2
199: ,x_msg_count OUT NOCOPY NUMBER);
200:
201: PROCEDURE strip_into_lines (px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
202: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
203: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
204: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
205: ,x_msg_data OUT NOCOPY varchar2
198: ,x_msg_data OUT NOCOPY VARCHAR2
199: ,x_msg_count OUT NOCOPY NUMBER);
200:
201: PROCEDURE strip_into_lines (px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
202: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
203: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
204: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
205: ,x_msg_data OUT NOCOPY varchar2
206: ,x_return_status OUT NOCOPY varchar2
199: ,x_msg_count OUT NOCOPY NUMBER);
200:
201: PROCEDURE strip_into_lines (px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
202: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
203: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
204: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
205: ,x_msg_data OUT NOCOPY varchar2
206: ,x_return_status OUT NOCOPY varchar2
207: ,x_msg_count OUT NOCOPY NUMBER);
200:
201: PROCEDURE strip_into_lines (px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
202: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
203: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
204: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
205: ,x_msg_data OUT NOCOPY varchar2
206: ,x_return_status OUT NOCOPY varchar2
207: ,x_msg_count OUT NOCOPY NUMBER);
208:
218: ,x_msg_data OUT NOCOPY VARCHAR2);
219:
220: PROCEDURE GET_AVAILABILITY_OPTIONS(p_api_version_number IN NUMBER
221: ,p_task_id IN NUMBER
222: ,p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
223: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
224: ,p_likelihood IN NUMBER
225: ,p_subinv_only IN BOOLEAN
226: ,p_mandatory IN BOOLEAN
219:
220: PROCEDURE GET_AVAILABILITY_OPTIONS(p_api_version_number IN NUMBER
221: ,p_task_id IN NUMBER
222: ,p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
223: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
224: ,p_likelihood IN NUMBER
225: ,p_subinv_only IN BOOLEAN
226: ,p_mandatory IN BOOLEAN
227: ,p_trunk IN BOOLEAN
225: ,p_subinv_only IN BOOLEAN
226: ,p_mandatory IN BOOLEAN
227: ,p_trunk IN BOOLEAN
228: ,p_warehouse IN BOOLEAN
229: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
230: ,x_return_status OUT NOCOPY VARCHAR2
231: ,x_msg_data OUT NOCOPY VARCHAR2
232: ,x_msg_count OUT NOCOPY NUMBER) IS
233:
230: ,x_return_status OUT NOCOPY VARCHAR2
231: ,x_msg_data OUT NOCOPY VARCHAR2
232: ,x_msg_count OUT NOCOPY NUMBER) IS
233:
234: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
235: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
236: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
237: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
238: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
231: ,x_msg_data OUT NOCOPY VARCHAR2
232: ,x_msg_count OUT NOCOPY NUMBER) IS
233:
234: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
235: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
236: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
237: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
238: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
239: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
232: ,x_msg_count OUT NOCOPY NUMBER) IS
233:
234: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
235: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
236: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
237: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
238: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
239: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
240: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
233:
234: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
235: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
236: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
237: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
238: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
239: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
240: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
241: l_return_status VARCHAR2(128) := FND_API.G_RET_STS_SUCCESS;
234: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
235: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
236: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
237: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
238: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
239: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
240: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
241: l_return_status VARCHAR2(128) := FND_API.G_RET_STS_SUCCESS;
242: l_del_date VARCHAR2(50);
235: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
236: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
237: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
238: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
239: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
240: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
241: l_return_status VARCHAR2(128) := FND_API.G_RET_STS_SUCCESS;
242: l_del_date VARCHAR2(50);
243: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS';
236: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
237: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
238: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
239: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
240: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
241: l_return_status VARCHAR2(128) := FND_API.G_RET_STS_SUCCESS;
242: l_del_date VARCHAR2(50);
243: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS';
244: l_temp_line_id NUMBER;
239: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
240: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
241: l_return_status VARCHAR2(128) := FND_API.G_RET_STS_SUCCESS;
242: l_del_date VARCHAR2(50);
243: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS';
244: l_temp_line_id NUMBER;
245: l_total_parts NUMBER := 0;
246: l_subinv_only BOOLEAN := p_subinv_only;
247: --hehxxx
283: BEGIN
284:
285: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
286: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
287: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
288: 'Begin');
289: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
290: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
291: 'p_task_id=' || p_task_id);
286: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
287: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
288: 'Begin');
289: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
290: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
291: 'p_task_id=' || p_task_id);
292: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
293: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
294: 'p_resources.count=' || p_resources.count);
289: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
290: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
291: 'p_task_id=' || p_task_id);
292: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
293: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
294: 'p_resources.count=' || p_resources.count);
295: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
296: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
297: 'p_interval.latest_time=' || to_char(p_interval.latest_time, 'DD-MON-RRRR HH24:MI:SS'));
292: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
293: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
294: 'p_resources.count=' || p_resources.count);
295: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
296: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
297: 'p_interval.latest_time=' || to_char(p_interval.latest_time, 'DD-MON-RRRR HH24:MI:SS'));
298: end if;
299:
300: savepoint GET_AVAILABILITY_OPTIONS_SP;
304: elsif not p_warehouse then
305: l_subinv_only := TRUE;
306: end if;
307:
308: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
309: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
310: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
311: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
312: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
305: l_subinv_only := TRUE;
306: end if;
307:
308: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
309: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
310: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
311: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
312: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
313: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
306: end if;
307:
308: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
309: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
310: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
311: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
312: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
313: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
314: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
307:
308: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
309: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
310: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
311: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
312: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
313: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
314: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
315: x_return_status := FND_API.G_RET_STS_SUCCESS;
308: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
309: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
310: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
311: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
312: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
313: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
314: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
315: x_return_status := FND_API.G_RET_STS_SUCCESS;
316: g_interval := p_interval;
309: -- l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1();
310: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
311: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
312: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
313: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
314: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
315: x_return_status := FND_API.G_RET_STS_SUCCESS;
316: g_interval := p_interval;
317:
310: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
311: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
312: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
313: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
314: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
315: x_return_status := FND_API.G_RET_STS_SUCCESS;
316: g_interval := p_interval;
317:
318: -- bug # 12618325
318: -- bug # 12618325
319: -- need to clear old requirement
320: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
321: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
322: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
323: 'trying to clear old requirement if it has any...');
324: end if;
325:
326: for r_asgn in get_asgn_id loop
354: and csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) = 'PARTIALLY RECEIVED';
355:
356: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
357: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
358: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
359: 'l_partial_line = ' || l_partial_line);
360: end if;
361:
362: if l_partial_line > 0 then
396: AND dres.source_id = mr.reservation_id;
397:
398: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
399: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
400: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
401: 'l_fl_rcvd_lines (R or S) = ' || l_fl_rcvd_lines);
402: end if;
403:
404: if l_fl_rcvd_lines > 0 then
454: AND dres.source_id = mr.reservation_id;
455:
456: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
457: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
458: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
459: 'l_fl_rcvd_lines (T or C) = ' || l_fl_rcvd_lines);
460: end if;
461:
462: if l_fl_rcvd_lines > 0 then
490: AND dother.source_id <> dres.source_id;
491:
492: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
493: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
494: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
495: 'l_fl_rcvd_multi_source = ' || l_fl_rcvd_multi_source);
496: end if;
497:
498: if l_fl_rcvd_multi_source > 0 then
543: );
544:
545: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
546: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
547: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
548: 'l_oth_req_line = ' || l_oth_req_line);
549: end if;
550:
551: if l_oth_req_line > 0 then
597: );
598:
599: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
600: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
601: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
602: 'l_non_src_line = ' || l_non_src_line);
603: end if;
604:
605: if l_non_src_line = 0 then
605: if l_non_src_line = 0 then
606: -- if we are here that means all the lines source have been
607: -- already recived
608: -- return to scheduler saying part is available
609: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
610: for res_count in 1..p_resources.count loop
611: x_options.extend;
612: x_options(x_options.count).resource_id := p_resources(res_count).resource_id;
613: x_options(x_options.count).resource_type := p_resources(res_count).resource_type;
640: AND csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) in ('SHIPPED', 'EXPECTED');
641:
642: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
643: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
644: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
645: 'l_shpd_lines = ' || l_shpd_lines);
646: end if;
647:
648: if l_shpd_lines > 0 then
655: AND h.address_type IN ('R', 'S');
656:
657: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
658: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
659: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
660: 'l_tech_spec_pr = ' || l_tech_spec_pr);
661: end if;
662:
663: if l_tech_spec_pr > 0 then
708: AND dother.source_id <> d.source_id;
709:
710: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
711: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
712: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
713: 'l_ship_multi_src = ' || l_ship_multi_src);
714: end if;
715:
716: if l_ship_multi_src > 0 then
752: );
753:
754: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
755: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
756: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
757: 'l_oth_req_line = ' || l_oth_req_line);
758: end if;
759:
760: if l_oth_req_line > 0 then
797: );
798:
799: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
800: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
801: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
802: 'l_non_src_line = ' || l_non_src_line);
803: end if;
804:
805: if l_non_src_line = 0 then
819: AND d.source_id = oel.line_id;
820:
821: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
822: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
823: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
824: 'l_old_arrival_date = ' || to_char(nvl(l_old_arrival_date, sysdate), 'dd-MON-YYYY HH24:MI'));
825: end if;
826:
827: l_dest_ou := -999;
831: WHERE ch.task_id = p_task_id
832: AND ch.task_assignment_id = r_asgn.task_assignment_id
833: AND rownum = 1;
834:
835: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
836: for res_count in 1..p_resources.count loop
837: l_rs_ou := -999;
838: open get_rs_ou(p_resources(res_count).resource_type, p_resources(res_count).resource_id);
839: fetch get_rs_ou into l_rs_ou;
858:
859:
860: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
861: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
862: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
863: 'before making call to CLEAN_REQUIREMENT...');
864: end if;
865:
866: CLEAN_REQUIREMENT(
872: );
873:
874: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
875: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
876: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
877: 'after making call to CLEAN_REQUIREMENT... x_return_status = ' || x_return_status);
878: end if;
879:
880: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
884: if x_return_status = 'C' then
885:
886: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
887: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
888: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
889: 'handle a case where cancellation of an order is failed due to OM processing constraints');
890: end if;
891:
892: l_old_arrival_date := null;
905: AND d.source_id = oel.line_id;
906:
907: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
908: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
909: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
910: 'l_old_arrival_date = ' || to_char(nvl(l_old_arrival_date, sysdate), 'dd-MON-YYYY HH24:MI'));
911: end if;
912:
913: l_dest_ou := -999;
917: WHERE ch.task_id = p_task_id
918: AND ch.task_assignment_id = r_asgn.task_assignment_id
919: AND rownum = 1;
920:
921: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
922: for res_count in 1..p_resources.count loop
923: l_rs_ou := -999;
924: open get_rs_ou(p_resources(res_count).resource_type, p_resources(res_count).resource_id);
925: fetch get_rs_ou into l_rs_ou;
960:
961:
962: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
963: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
964: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
965: 'rollback to GET_AVAILABILITY_OPTIONS_SP');
966: end if;
967:
968: rollback to GET_AVAILABILITY_OPTIONS_SP;
969:
970: return;
971: end if;
972:
973: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(p_resources, l_resource_org_subinv , x_return_status,x_msg_data,x_msg_count);
974:
975: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
976: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
977: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
973: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(p_resources, l_resource_org_subinv , x_return_status,x_msg_data,x_msg_count);
974:
975: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
976: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
977: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
978: 'After CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV... x_return_status=' || x_return_status);
979: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
980: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
981: 'l_resource_org_subinv.count=' || l_resource_org_subinv.count);
974:
975: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
976: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
977: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
978: 'After CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV... x_return_status=' || x_return_status);
979: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
980: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
981: 'l_resource_org_subinv.count=' || l_resource_org_subinv.count);
982: end if;
976: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
977: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
978: 'After CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV... x_return_status=' || x_return_status);
979: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
980: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
981: 'l_resource_org_subinv.count=' || l_resource_org_subinv.count);
982: end if;
983:
984: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
984: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
985: RETURN;
986: END IF;
987:
988: CSP_SCH_INT_PVT.GET_PARTS_LIST(p_task_id,p_likelihood,l_parts_list,x_return_status,x_msg_data,x_msg_count);
989:
990: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
991: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
992: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
988: CSP_SCH_INT_PVT.GET_PARTS_LIST(p_task_id,p_likelihood,l_parts_list,x_return_status,x_msg_data,x_msg_count);
989:
990: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
991: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
992: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
993: 'After CSP_SCH_INT_PVT.GET_PARTS_LIST... x_return_status=' || x_return_status);
994: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
995: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
996: 'l_parts_list.count=' || l_parts_list.count);
989:
990: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
991: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
992: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
993: 'After CSP_SCH_INT_PVT.GET_PARTS_LIST... x_return_status=' || x_return_status);
994: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
995: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
996: 'l_parts_list.count=' || l_parts_list.count);
997: end if;
991: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
992: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
993: 'After CSP_SCH_INT_PVT.GET_PARTS_LIST... x_return_status=' || x_return_status);
994: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
995: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
996: 'l_parts_list.count=' || l_parts_list.count);
997: end if;
998:
999: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1017: RETURN;
1018: END IF;
1019:
1020: IF l_parts_list.count > 0 THEN
1021: CSP_SCH_INT_PVT.CHECK_LOCAl_INVENTORY(l_resource_org_subinv,l_parts_list,p_trunk,l_unavailable_list,l_final_available_list,x_return_status,x_msg_data,x_msg_count);
1022:
1023: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1024: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1025: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1021: CSP_SCH_INT_PVT.CHECK_LOCAl_INVENTORY(l_resource_org_subinv,l_parts_list,p_trunk,l_unavailable_list,l_final_available_list,x_return_status,x_msg_data,x_msg_count);
1022:
1023: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1024: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1025: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1026: 'After CSP_SCH_INT_PVT.CHECK_LOCAl_INVENTORY... x_return_status=' || x_return_status);
1027: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1028: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1029: 'l_unavailable_list.count=' || l_unavailable_list.count);
1022:
1023: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1024: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1025: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1026: 'After CSP_SCH_INT_PVT.CHECK_LOCAl_INVENTORY... x_return_status=' || x_return_status);
1027: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1028: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1029: 'l_unavailable_list.count=' || l_unavailable_list.count);
1030: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1024: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1025: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1026: 'After CSP_SCH_INT_PVT.CHECK_LOCAl_INVENTORY... x_return_status=' || x_return_status);
1027: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1028: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1029: 'l_unavailable_list.count=' || l_unavailable_list.count);
1030: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1031: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1032: 'l_final_available_list.count=' || l_final_available_list.count);
1027: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1028: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1029: 'l_unavailable_list.count=' || l_unavailable_list.count);
1030: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1031: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1032: 'l_final_available_list.count=' || l_final_available_list.count);
1033: end if;
1034:
1035: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1073: and l_unavailable_list.count >= 1 then
1074:
1075: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1076: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1077: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1078: 'Before calling CSP_SCH_INT_PVT.SPARES_CHECK...');
1079: end if;
1080:
1081: CSP_SCH_INT_PVT.SPARES_CHECK(l_unavailable_list,p_interval,l_available_list,l_final_unavailable_list,x_return_status,x_msg_data,x_msg_count);
1074:
1075: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1076: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1077: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1078: 'Before calling CSP_SCH_INT_PVT.SPARES_CHECK...');
1079: end if;
1080:
1081: CSP_SCH_INT_PVT.SPARES_CHECK(l_unavailable_list,p_interval,l_available_list,l_final_unavailable_list,x_return_status,x_msg_data,x_msg_count);
1082:
1077: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1078: 'Before calling CSP_SCH_INT_PVT.SPARES_CHECK...');
1079: end if;
1080:
1081: CSP_SCH_INT_PVT.SPARES_CHECK(l_unavailable_list,p_interval,l_available_list,l_final_unavailable_list,x_return_status,x_msg_data,x_msg_count);
1082:
1083: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1084: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1085: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1081: CSP_SCH_INT_PVT.SPARES_CHECK(l_unavailable_list,p_interval,l_available_list,l_final_unavailable_list,x_return_status,x_msg_data,x_msg_count);
1082:
1083: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1084: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1085: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1086: 'After CSP_SCH_INT_PVT.SPARES_CHECK... x_return_status=' || x_return_status);
1087: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1088: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1089: 'l_unavailable_list.count=' || l_unavailable_list.count);
1082:
1083: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1084: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1085: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1086: 'After CSP_SCH_INT_PVT.SPARES_CHECK... x_return_status=' || x_return_status);
1087: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1088: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1089: 'l_unavailable_list.count=' || l_unavailable_list.count);
1090: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1084: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1085: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1086: 'After CSP_SCH_INT_PVT.SPARES_CHECK... x_return_status=' || x_return_status);
1087: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1088: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1089: 'l_unavailable_list.count=' || l_unavailable_list.count);
1090: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1091: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1092: 'l_available_list.count=' || l_available_list.count);
1087: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1088: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1089: 'l_unavailable_list.count=' || l_unavailable_list.count);
1090: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1091: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1092: 'l_available_list.count=' || l_available_list.count);
1093: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1094: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1095: 'l_final_unavailable_list.count=' || l_final_unavailable_list.count);
1090: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1091: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1092: 'l_available_list.count=' || l_available_list.count);
1093: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1094: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1095: 'l_final_unavailable_list.count=' || l_final_unavailable_list.count);
1096: end if;
1097:
1098: elsif (l_unavailable_list.count > 0 AND (NOT l_subinv_only) AND
1098: elsif (l_unavailable_list.count > 0 AND (NOT l_subinv_only) AND
1099: (fnd_profile.value(name => 'CSP_CHECK_ATP')= 'ALWAYS' or
1100: fnd_profile.value(name => 'CSP_CHECK_ATP')= 'SCHONLY' )) or
1101: (l_unavailable_list.count > 0 and nvl(p_warehouse,FALSE)) THEN
1102: 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);
1103: ELSE
1104: l_final_unavailable_list := l_unavailable_list;
1105: END IF;
1106:
1119: l_final_available_list(l_final_available_list.count) := l_available_list(J);
1120:
1121: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1122: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1123: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1124: 'Got available data... l_final_available_list.count=' || l_final_available_list.count);
1125: end if;
1126:
1127: EXIT;
1130: END LOOP;
1131: END LOOP;
1132: END IF;
1133:
1134: 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);
1135:
1136: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1137: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1138: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1134: 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);
1135:
1136: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1137: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1138: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1139: 'After CSP_SCH_INT_PVT.ELIGIBLE_RESOURCES... x_return_status=' || x_return_status);
1140: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1141: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1142: 'l_eligible_resources_list.count=' || l_eligible_resources_list.count);
1135:
1136: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1137: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1138: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1139: 'After CSP_SCH_INT_PVT.ELIGIBLE_RESOURCES... x_return_status=' || x_return_status);
1140: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1141: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1142: 'l_eligible_resources_list.count=' || l_eligible_resources_list.count);
1143: end if;
1137: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1138: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1139: 'After CSP_SCH_INT_PVT.ELIGIBLE_RESOURCES... x_return_status=' || x_return_status);
1140: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1141: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1142: 'l_eligible_resources_list.count=' || l_eligible_resources_list.count);
1143: end if;
1144:
1145: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1147: END IF;
1148:
1149: IF l_eligible_resources_list.count > 0 THEN
1150:
1151: CSP_SCH_INT_PVT.GET_TIME_COST(l_eligible_resources_list,x_options,x_return_status,x_msg_data,x_msg_count);
1152:
1153: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1154: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1155: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1151: CSP_SCH_INT_PVT.GET_TIME_COST(l_eligible_resources_list,x_options,x_return_status,x_msg_data,x_msg_count);
1152:
1153: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1154: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1155: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1156: 'After CSP_SCH_INT_PVT.GET_TIME_COST... x_return_status=' || x_return_status);
1157: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1158: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1159: 'l_eligible_resources_list.count=' || l_eligible_resources_list.count);
1152:
1153: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1154: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1155: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1156: 'After CSP_SCH_INT_PVT.GET_TIME_COST... x_return_status=' || x_return_status);
1157: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1158: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1159: 'l_eligible_resources_list.count=' || l_eligible_resources_list.count);
1160: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1154: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1155: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1156: 'After CSP_SCH_INT_PVT.GET_TIME_COST... x_return_status=' || x_return_status);
1157: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1158: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1159: 'l_eligible_resources_list.count=' || l_eligible_resources_list.count);
1160: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1161: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1162: 'x_options.count=' || x_options.count);
1157: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1158: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1159: 'l_eligible_resources_list.count=' || l_eligible_resources_list.count);
1160: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1161: 'csp.plsql.CSP_SCH_INT_PVT.GET_AVAILABILITY_OPTIONS',
1162: 'x_options.count=' || x_options.count);
1163: end if;
1164:
1165: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1214: ,p_likelihood IN NUMBER
1215: ,p_mandatory IN BOOLEAN
1216: ,p_trunk IN BOOLEAN
1217: ,p_warehouse IN BOOLEAN
1218: ,p_options IN CSP_SCH_INT_PVT.csp_sch_options_rec_typ
1219: ,x_return_status OUT NOCOPY VARCHAR2
1220: ,x_msg_data OUT NOCOPY VARCHAR2
1221: ,x_msg_count OUT NOCOPY NUMBER) IS
1222:
1279: and msib.inventory_item_id = capt.supplied_item_id
1280: and crh.task_id = p_task_id
1281: order by ood.operating_unit;
1282:
1283: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
1284: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
1285: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
1286: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
1287: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1280: and crh.task_id = p_task_id
1281: order by ood.operating_unit;
1282:
1283: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
1284: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
1285: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
1286: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
1287: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1288: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1281: order by ood.operating_unit;
1282:
1283: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
1284: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
1285: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
1286: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
1287: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1288: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1289: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1282:
1283: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
1284: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
1285: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
1286: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
1287: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1288: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1289: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1290: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1283: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
1284: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
1285: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
1286: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
1287: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1288: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1289: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1290: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1291: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
1284: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
1285: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
1286: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
1287: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1288: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1289: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1290: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1291: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
1292: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
1285: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
1286: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
1287: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1288: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1289: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1290: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1291: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
1292: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
1293: l_return_status VARCHAR2(128);
1286: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ;
1287: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1288: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1289: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1290: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1291: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
1292: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
1293: l_return_status VARCHAR2(128);
1294: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1287: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1288: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1289: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1290: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1291: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
1292: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
1293: l_return_status VARCHAR2(128);
1294: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1295: min_cost NUMBER ;
1288: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1289: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1290: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1291: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
1292: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
1293: l_return_status VARCHAR2(128);
1294: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1295: min_cost NUMBER ;
1296: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1290: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
1291: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
1292: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
1293: l_return_status VARCHAR2(128);
1294: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1295: min_cost NUMBER ;
1296: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1297: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
1298: l_org_ship_methode org_ship_methodes_tbl_type ;
1292: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
1293: l_return_status VARCHAR2(128);
1294: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1295: min_cost NUMBER ;
1296: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1297: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
1298: l_org_ship_methode org_ship_methodes_tbl_type ;
1299: -- l_Requirement_Line_Tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
1300: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
1293: l_return_status VARCHAR2(128);
1294: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1295: min_cost NUMBER ;
1296: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1297: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
1298: l_org_ship_methode org_ship_methodes_tbl_type ;
1299: -- l_Requirement_Line_Tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
1300: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
1301: l_requirement_header CSP_Requirement_Headers_PVT.REQUIREMENT_HEADER_Rec_Type;
1296: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
1297: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
1298: l_org_ship_methode org_ship_methodes_tbl_type ;
1299: -- l_Requirement_Line_Tbl CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
1300: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
1301: l_requirement_header CSP_Requirement_Headers_PVT.REQUIREMENT_HEADER_Rec_Type;
1302: l_parts_header csp_parts_requirement.Header_rec_type;
1303: l_parts_lines csp_parts_requirement.Line_Tbl_type;
1304: l_revision varchar2(3);
1310: l_reservation_id NUMBER;
1311: l_requirements_line_id NUMBER;
1312: req_loop NUMBER := 1;
1313: l_requirement_header_id NUMBER;
1314: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CHOOSE_OPTION';
1315: l_msg varchar2(2000);
1316: rec_count NUMBER := 1;
1317: l_destination_sub_inv varchar2(30) := null;
1318: l_destination_org_id NUMBER := null;
1318: l_destination_org_id NUMBER := null;
1319: l_ship_to_location_id NUMBER;
1320: l_ship_methode_count NUMBER;
1321: l_temp_line_id NUMBER;
1322: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1323: l_req_line_details_tbl CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ;
1324: l_address_type varchar2(3);
1325: --hehxxx
1326: l_search_method varchar2(30) := fnd_profile.value('CSP_PART_SEARCH_METHOD_SCHEDULER');
1319: l_ship_to_location_id NUMBER;
1320: l_ship_methode_count NUMBER;
1321: l_temp_line_id NUMBER;
1322: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
1323: l_req_line_details_tbl CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ;
1324: l_address_type varchar2(3);
1325: --hehxxx
1326: l_search_method varchar2(30) := fnd_profile.value('CSP_PART_SEARCH_METHOD_SCHEDULER');
1327: x_options CSP_SCH_INT_PVT.csp_sch_options_tbl_typ;
1323: l_req_line_details_tbl CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ;
1324: l_address_type varchar2(3);
1325: --hehxxx
1326: l_search_method varchar2(30) := fnd_profile.value('CSP_PART_SEARCH_METHOD_SCHEDULER');
1327: x_options CSP_SCH_INT_PVT.csp_sch_options_tbl_typ;
1328: l_req_line_details_id number := null;
1329: l_old_operating_unit number := null;
1330: l_book_order varchar2(30) := fnd_profile.value('CSP_INIT_IO_STATUS');
1331: l_need_by_date date := null;
1569:
1570: end loop;
1571:
1572: x_return_status := FND_API.G_RET_STS_SUCCESS ;
1573: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1574: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
1575: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
1576: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1577: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
1570: end loop;
1571:
1572: x_return_status := FND_API.G_RET_STS_SUCCESS ;
1573: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1574: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
1575: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
1576: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1577: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
1578: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
1571:
1572: x_return_status := FND_API.G_RET_STS_SUCCESS ;
1573: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1574: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
1575: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
1576: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1577: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
1578: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
1579: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1572: x_return_status := FND_API.G_RET_STS_SUCCESS ;
1573: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1574: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
1575: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
1576: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1577: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
1578: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
1579: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1580: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1573: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1574: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
1575: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
1576: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1577: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
1578: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
1579: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1580: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1581: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
1574: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
1575: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
1576: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1577: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
1578: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
1579: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1580: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1581: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
1582: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
1575: --l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
1576: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1577: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
1578: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
1579: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1580: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1581: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
1582: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
1583: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1576: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1577: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
1578: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
1579: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1580: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1581: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
1582: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
1583: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1584: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1577: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
1578: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
1579: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1580: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1581: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
1582: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
1583: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1584: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1585: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
1578: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
1579: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1580: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1581: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
1582: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
1583: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1584: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1585: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
1586: l_org_ship_methode := org_ship_methodes_tbl_type();
1579: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
1580: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1581: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
1582: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
1583: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1584: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1585: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
1586: l_org_ship_methode := org_ship_methodes_tbl_type();
1587: l_req_line_details_tbl := CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ();
1580: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
1581: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
1582: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
1583: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1584: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1585: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
1586: l_org_ship_methode := org_ship_methodes_tbl_type();
1587: l_req_line_details_tbl := CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ();
1588:
1581: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
1582: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
1583: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1584: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1585: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
1586: l_org_ship_methode := org_ship_methodes_tbl_type();
1587: l_req_line_details_tbl := CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ();
1588:
1589: l_resources.extend;
1583: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1584: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
1585: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
1586: l_org_ship_methode := org_ship_methodes_tbl_type();
1587: l_req_line_details_tbl := CSP_SCH_INT_PVT.csp_req_line_details_tabl_typ();
1588:
1589: l_resources.extend;
1590: l_resources(1).resource_id := p_options.resource_id ;
1591: l_resources(1).resource_type := p_options.resource_type ;
1605: end if;
1606: open c_scheduled_start;
1607: fetch c_scheduled_start into l_need_by_date;
1608: close c_scheduled_start;
1609: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
1610: SPARES_CHECK2(
1611: p_resources => l_resources,
1612: p_task_id => p_task_id,
1613: p_need_by_date => p_options.start_time+1/144,
1674: l_reservation_parts.quantity_needed := cap.supplied_quantity;
1675: l_reservation_parts.sub_inventory_code := cap.subinventory_code;
1676: l_reservation_parts.line_id := cap.requirement_line_id;
1677: l_reservation_parts.revision := cap.supplied_item_rev;
1678: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
1679: if x_return_status = FND_API.G_RET_STS_SUCCESS then
1680: l_req_line_details_id := null;
1681: csp_req_line_details_pkg.insert_row(
1682: l_req_line_details_id
1862: log('choose_option','Return');
1863: return;
1864: end if;
1865: log('choose_option','get_organization_subinv');
1866: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(l_resources, l_resource_org_subinv , x_return_status,x_msg_data,x_msg_count);
1867: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1868: RETURN;
1869: END IF;
1870: CSP_SCH_INT_PVT.GET_PARTS_LIST(p_task_id,p_likelihood,l_parts_list,x_return_status,x_msg_data,x_msg_count);
1866: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(l_resources, l_resource_org_subinv , x_return_status,x_msg_data,x_msg_count);
1867: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1868: RETURN;
1869: END IF;
1870: CSP_SCH_INT_PVT.GET_PARTS_LIST(p_task_id,p_likelihood,l_parts_list,x_return_status,x_msg_data,x_msg_count);
1871: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1872: RETURN;
1873: END IF;
1874: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1871: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1872: RETURN;
1873: END IF;
1874: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1875: CSP_SCH_INT_PVT.CHECK_LOCAL_INVENTORY(l_resource_org_subinv,l_parts_list,p_trunk,l_unavailable_list,l_final_available_list,x_return_status,x_msg_data,x_msg_count);
1876: ELSE
1877: return;
1878: END IF;
1879: --hehxxxx
1878: END IF;
1879: --hehxxxx
1880: if nvl(l_search_method,'SPARES') <> 'ATP' and nvl(p_warehouse,FALSE)
1881: and l_unavailable_list.count >= 1 then
1882: CSP_SCH_INT_PVT.SPARES_CHECK(l_unavailable_list,l_interval,l_available_list,l_final_unavailable_list,x_return_status,x_msg_data,x_msg_count);
1883: elsif l_unavailable_list.count >= 1 and p_warehouse THEN
1884: 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);
1885: END IF;
1886: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1880: if nvl(l_search_method,'SPARES') <> 'ATP' and nvl(p_warehouse,FALSE)
1881: and l_unavailable_list.count >= 1 then
1882: CSP_SCH_INT_PVT.SPARES_CHECK(l_unavailable_list,l_interval,l_available_list,l_final_unavailable_list,x_return_status,x_msg_data,x_msg_count);
1883: elsif l_unavailable_list.count >= 1 and p_warehouse THEN
1884: 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);
1885: END IF;
1886: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1887: RETURN;
1888: END IF;
1898: l_temp_line_id := l_available_list(I).line_id;
1899: END IF;
1900: END LOOP;
1901: if p_mandatory then
1902: 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);
1903: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1904: RETURN;
1905: END IF;
1906: else
1919: l_eligible_resources_list(l_eligible_resources_list.count).line_id := l_final_available_list(K).line_id;
1920: END LOOP;
1921: end if;
1922: IF l_eligible_resources_list.count > 0 THEN
1923: --CSP_SCH_INT_PVT.GET_TIME_COST(l_eligible_resources_list,l_options,x_return_status);
1924: 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);
1925: ELSE
1926: x_return_status := FND_API.G_RET_STS_ERROR;
1927: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_OPTION_NOT_VALIED');
1920: END LOOP;
1921: end if;
1922: IF l_eligible_resources_list.count > 0 THEN
1923: --CSP_SCH_INT_PVT.GET_TIME_COST(l_eligible_resources_list,l_options,x_return_status);
1924: 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);
1925: ELSE
1926: x_return_status := FND_API.G_RET_STS_ERROR;
1927: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_OPTION_NOT_VALIED');
1928: FND_MSG_PUB.ADD;
2065: l_reservation_parts.quantity_needed := l_eligible_resources_list(I).quantity;
2066: l_reservation_parts.sub_inventory_code := l_eligible_resources_list(I).sub_inventory;
2067: l_reservation_parts.line_id := l_eligible_resources_list(I).line_id;
2068: l_reservation_parts.revision := l_eligible_resources_list(I).revision;
2069: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
2070: IF l_reservation_id <= 0 THEN
2071: ROLLBACK TO choose_options;
2072: x_return_status := FND_API.G_RET_STS_ERROR;
2073: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
2239: and oel.line_id = crl.order_line_id
2240: and oeh.header_id = oel.header_id
2241: order by oeh.header_id;
2242:
2243: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CLEAN_MATERIAL_TRANSACTION';
2244: l_reserv_id NUMBER;
2245: l_local_reserv_id NUMBER;
2246: l_order_id NUMBER;
2247: l_return_status VARCHAR2(3);
2262: LOOP
2263: FETCH cancel_reserv INTO l_reserv_id,l_local_reserv_id,l_requirement_line_id;
2264: EXIT WHEN cancel_reserv%NOTFOUND;
2265: IF l_reserv_id IS NOT NULL THEN
2266: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_reserv_id,x_return_status,x_msg_data,x_msg_count);
2267: END IF;
2268: IF l_local_reserv_id IS NOT NULL THEN
2269: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_local_reserv_id,x_return_status,x_msg_data,x_msg_count);
2270: END IF;
2265: IF l_reserv_id IS NOT NULL THEN
2266: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_reserv_id,x_return_status,x_msg_data,x_msg_count);
2267: END IF;
2268: IF l_local_reserv_id IS NOT NULL THEN
2269: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_local_reserv_id,x_return_status,x_msg_data,x_msg_count);
2270: END IF;
2271: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2272: ROLLBACK TO CLEAN_MATERIAL_TRANSACTION;
2273: x_return_status := FND_API.G_RET_STS_ERROR;
2316: LOOP
2317: FETCH cancel_order INTO l_order_id,l_requirement_line_id ;
2318: EXIT WHEN cancel_order % NOTFOUND;
2319: IF l_order_id <> l_previous_order_id THEN
2320: CSP_SCH_INT_PVT.CANCEL_ORDER(l_order_id,x_return_status,x_msg_data,x_msg_count);
2321: l_previous_order_id := l_order_id;
2322: ELSE
2323: x_return_status := FND_API.G_RET_STS_SUCCESS;
2324: END IF;
2392: BEGIN
2393:
2394: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2395: fnd_log.string(fnd_log.level_statement,
2396: 'csp.plsql.csp_sch_int_pvt.clean_material_transaction',
2397: 'in Clean_Material_Transaction procedure.. calling CLEAN_REQUIREMENT....');
2398: fnd_log.string(fnd_log.level_statement,
2399: 'csp.plsql.csp_sch_int_pvt.clean_material_transaction',
2400: 'p_task_assignment_id = ' || p_task_assignment_id);
2395: fnd_log.string(fnd_log.level_statement,
2396: 'csp.plsql.csp_sch_int_pvt.clean_material_transaction',
2397: 'in Clean_Material_Transaction procedure.. calling CLEAN_REQUIREMENT....');
2398: fnd_log.string(fnd_log.level_statement,
2399: 'csp.plsql.csp_sch_int_pvt.clean_material_transaction',
2400: 'p_task_assignment_id = ' || p_task_assignment_id);
2401: end if;
2402: x_return_status := FND_API.G_RET_STS_SUCCESS;
2403:
2412: */
2413:
2414: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2415: fnd_log.string(fnd_log.level_statement,
2416: 'csp.plsql.csp_sch_int_pvt.clean_material_transaction',
2417: 'leaving... x_return_status = ' || x_return_status);
2418: end if;
2419:
2420: END CLEAN_MATERIAL_TRANSACTION;
2479: l_line_to_cancel number;
2480: l_fl_rcvd_lines number;
2481: l_shpd_lines number;
2482: BEGIN
2483: l_module_name:= 'csp.plsql.csp_sch_int_pvt.clean_requirement';
2484:
2485: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2486: fnd_log.string(fnd_log.level_statement, l_module_name,
2487: 'begin...');
2563: fnd_log.string(fnd_log.level_statement, l_module_name,
2564: 'before cancelling reservation');
2565: end if;
2566:
2567: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_reserv_id,x_return_status,x_msg_data,x_msg_count);
2568:
2569: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2570: fnd_log.string(fnd_log.level_statement, l_module_name,
2571: 'after cancelling reservation... x_return_status = ' || x_return_status);
2633: fnd_log.string(fnd_log.level_statement, l_module_name,
2634: 'before CANCEL_ORDER...');
2635: end if;
2636:
2637: CSP_SCH_INT_PVT.CANCEL_ORDER(l_order_id,x_return_status,x_msg_data,x_msg_count);
2638:
2639: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2640: fnd_log.string(fnd_log.level_statement, l_module_name,
2641: 'after CANCEL_ORDER... x_return_status = ' || x_return_status);
2736: PROCEDURE CANCEL_RESERVATION(p_reserv_id IN NUMBER
2737: ,x_return_status OUT NOCOPY VARCHAR2
2738: ,x_msg_data OUT NOCOPY VARCHAR2
2739: ,x_msg_count OUT NOCOPY NUMBER) IS
2740: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CANCEL_RESERVATION';
2741: l_api_version_number NUMBER := 1.0;
2742: l_return_status VARCHAR2(3);
2743: l_msg_count NUMBER;
2744: l_init_msg_lst VARCHAR2(1) := fnd_api.g_true;
2775: ,x_msg_count OUT NOCOPY NUMBER) IS
2776: l_parts_header csp_parts_requirement.Header_rec_type;
2777: l_parts_lines csp_parts_requirement.Line_Tbl_type;
2778: l_api_version NUMBER := 1.0;
2779: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CANCEL_ORDER';
2780: BEGIN
2781: x_return_status := FND_API.G_RET_STS_SUCCESS;
2782: l_parts_header.order_header_id := p_order_id;
2783: --l_parts_header.operation := 'CANCEL';
2824: and oel.line_id = crld.source_id
2825: and oeh.header_id = oel.header_id
2826: order by oeh.header_id;
2827:
2828: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CREATE_ORDERS';
2829: l_parts_header csp_parts_requirement.Header_rec_type;
2830: l_parts_lines csp_parts_requirement.Line_Tbl_type;
2831: l_order_header_id NUMBER;
2832: l_order_line_id NUMBER;
2928: WHERE RESOURCE_ID = c_resource_id
2929: AND RESOURCE_TYPE = c_resource_type
2930: AND PRIMARY_FLAG = 'Y';
2931:
2932: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CREATE_ORDERS';
2933: l_parts_header csp_parts_requirement.Header_rec_type;
2934: l_parts_lines csp_parts_requirement.Line_Tbl_type;
2935: l_requrements_lines CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
2936: l_return_status VARCHAR2(30);
3062: ELSE
3063: FOR I IN 1..l_parts_lines.count LOOP
3064: l_requrements_lines(I).REQUIREMENT_LINE_ID := l_parts_lines(I).requirement_line_id;
3065: l_requrements_lines(I).ORDER_LINE_ID := l_parts_lines(I).order_line_id;
3066: CSP_SCH_INT_PVT.TRANSFER_RESERVATION(p_reservation_id => l_parts_lines(I).reservation_id
3067: ,p_order_header_id => l_parts_header.order_header_id
3068: ,p_order_line_id => l_parts_lines(I).order_line_id
3069: ,x_return_status => x_return_status
3070: ,x_reservation_id => x_reservation_id
3105: , p_data => x_msg_data);
3106: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3107: END CREATE_ORDERS;*/
3108:
3109: PROCEDURE GET_ORGANIZATION_SUBINV( p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
3110: ,x_resource_org_subinv OUT NOCOPY CSP_SCH_INT_PVT.csp_resource_org_tbl_typ
3111: ,x_return_status OUT NOCOPY VARCHAR2
3112: ,x_msg_data OUT NOCOPY VARCHAR2
3113: ,x_msg_count OUT NOCOPY NUMBER) IS
3106: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3107: END CREATE_ORDERS;*/
3108:
3109: PROCEDURE GET_ORGANIZATION_SUBINV( p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ
3110: ,x_resource_org_subinv OUT NOCOPY CSP_SCH_INT_PVT.csp_resource_org_tbl_typ
3111: ,x_return_status OUT NOCOPY VARCHAR2
3112: ,x_msg_data OUT NOCOPY VARCHAR2
3113: ,x_msg_count OUT NOCOPY NUMBER) IS
3114:
3124: FROM JTF_OBJECTS_VL
3125: WHERE OBJECT_CODE = l_resource_type;
3126: l_organization NUMBER;
3127: l_sub_inventory VARCHAR2(10);
3128: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV';
3129: l_msg_count number;
3130: l_msg_data number;
3131: l_resource_name varchar2(1000);
3132: l_resource_type_name varchar2(1000);
3131: l_resource_name varchar2(1000);
3132: l_resource_type_name varchar2(1000);
3133: BEGIN
3134: x_return_status := FND_API.G_RET_STS_SUCCESS;
3135: x_resource_org_subinv := CSP_SCH_INT_PVT.csp_resource_org_tbl_typ();
3136: FOR I IN 1..p_resources.count
3137: LOOP
3138: OPEN csp_resource_org(p_resources(I).resource_id, p_resources(I).resource_type);
3139: LOOP
3182: END GET_ORGANIZATION_SUBINV;
3183:
3184: PROCEDURE GET_PARTS_LIST(p_task_id IN NUMBER
3185: ,p_likelihood IN NUMBER
3186: ,x_parts_list OUT NOCOPY CSP_SCH_INT_PVT.csp_parts_tbl_typ1
3187: ,x_return_status OUT NOCOPY VARCHAR2
3188: ,x_msg_data OUT NOCOPY VARCHAR2
3189: ,x_msg_count OUT NOCOPY NUMBER) IS
3190:
3202: l_ship_complet VARCHAR2(30);
3203: l_msg_data VARCHAR2(2000);
3204: l_msg_count NUMBER;
3205: l_revision varchar2(3);
3206: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.GET_PARTS_LIST' ;
3207: l_header_id NUMBER;
3208: loop_count NUMBER := 0;
3209: l_line_id NUMBER;
3210:
3210:
3211: BEGIN
3212: -- savepoint GET_PARTS_LIST;
3213: x_return_status := FND_API.G_RET_STS_SUCCESS ;
3214: --x_parts_list := CSP_SCH_INT_PVT.csp_parts_tbl_typ1();
3215: OPEN parts_list(p_task_id, p_likelihood);
3216: LOOP
3217: FETCH parts_list INTO part_number, part_quantity , part_uom , l_ship_complet,l_revision , l_line_id;
3218: EXIT WHEN parts_list % NOTFOUND;
3237: , p_data => x_msg_data);
3238: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3239: return;
3240: END GET_PARTS_LIST;
3241: PROCEDURE CHECK_LOCAl_INVENTORY(p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
3242: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
3243: ,p_trunk IN BOOLEAN
3244: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3245: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
3238: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3239: return;
3240: END GET_PARTS_LIST;
3241: PROCEDURE CHECK_LOCAl_INVENTORY(p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
3242: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
3243: ,p_trunk IN BOOLEAN
3244: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3245: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
3246: ,x_return_status OUT NOCOPY VARCHAR2
3240: END GET_PARTS_LIST;
3241: PROCEDURE CHECK_LOCAl_INVENTORY(p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
3242: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
3243: ,p_trunk IN BOOLEAN
3244: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3245: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
3246: ,x_return_status OUT NOCOPY VARCHAR2
3247: ,x_msg_data OUT NOCOPY VARCHAR2
3248: ,x_msg_count OUT NOCOPY NUMBER) IS
3241: PROCEDURE CHECK_LOCAl_INVENTORY(p_resource_org_subinv IN CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP
3242: ,p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1
3243: ,p_trunk IN BOOLEAN
3244: ,x_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3245: ,x_available_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
3246: ,x_return_status OUT NOCOPY VARCHAR2
3247: ,x_msg_data OUT NOCOPY VARCHAR2
3248: ,x_msg_count OUT NOCOPY NUMBER) IS
3249: /* CURSOR reservation_check(org_id NUMBER, sub_inv_code VARCHAR2,item_id NUMBER)
3276: l_qs NUMBER;
3277: l_att NUMBER;
3278: l_temp_reserv_quantity NUMBER;
3279: l_substitute_item NUMBER;
3280: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CHECK_LOCAl_INVENTORY';
3281: --- l_cumulative_att NUMBER;
3282: l_supersede_items CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
3283: l_required_quantity NUMBER;
3284: l_alternate_parts CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
3283: l_required_quantity NUMBER;
3284: l_alternate_parts CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
3285: l_append boolean := true;
3286: l_revision_controlled BOOLEAN := false;
3287: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
3288: l_reservation_id NUMBER;
3289: l_res_ids CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
3290: BEGIN
3291: savepoint csp_check_local_inv;
3289: l_res_ids CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
3290: BEGIN
3291: savepoint csp_check_local_inv;
3292: x_return_status := FND_API.G_RET_STS_SUCCESS;
3293: x_unavailable_list:= CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3294: x_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
3295: if l_res_ids.count > 0 THEN
3296: l_res_ids.delete;
3297: END IF;
3290: BEGIN
3291: savepoint csp_check_local_inv;
3292: x_return_status := FND_API.G_RET_STS_SUCCESS;
3293: x_unavailable_list:= CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3294: x_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
3295: if l_res_ids.count > 0 THEN
3296: l_res_ids.delete;
3297: END IF;
3298: FOR I IN 1..p_resource_org_subinv.count LOOP
3394: l_reservation_parts.quantity_needed := x_available_list(x_available_list.count).quantity ;
3395: l_reservation_parts.sub_inventory_code := x_available_list(x_available_list.count).sub_inventory;
3396: l_reservation_parts.line_id := x_available_list(x_available_list.count).line_id ;
3397: l_reservation_parts.revision := x_available_list(x_available_list.count).revision;
3398: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
3399:
3400: IF l_reservation_id <= 0 THEN
3401: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
3402: FND_MSG_PUB.ADD;
3429: l_reservation_parts.quantity_needed := x_available_list(x_available_list.count).quantity ;
3430: l_reservation_parts.sub_inventory_code := x_available_list(x_available_list.count).sub_inventory;
3431: l_reservation_parts.line_id := x_available_list(x_available_list.count).line_id ;
3432: l_reservation_parts.revision := x_available_list(x_available_list.count).revision;
3433: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
3434:
3435: IF l_reservation_id <= 0 THEN
3436: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
3437: FND_MSG_PUB.ADD;
3502: l_reservation_parts.quantity_needed := x_available_list(x_available_list.count).quantity ;
3503: l_reservation_parts.sub_inventory_code := x_available_list(x_available_list.count).sub_inventory;
3504: l_reservation_parts.line_id := x_available_list(x_available_list.count).line_id ;
3505: l_reservation_parts.revision := x_available_list(x_available_list.count).revision;
3506: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
3507:
3508: IF l_reservation_id <= 0 THEN
3509: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
3510: FND_MSG_PUB.ADD;
3539: l_reservation_parts.quantity_needed := x_available_list(x_available_list.count).quantity ;
3540: l_reservation_parts.sub_inventory_code := x_available_list(x_available_list.count).sub_inventory;
3541: l_reservation_parts.line_id := x_available_list(x_available_list.count).line_id ;
3542: l_reservation_parts.revision := x_available_list(x_available_list.count).revision;
3543: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
3544:
3545: IF l_reservation_id <= 0 THEN
3546: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
3547: FND_MSG_PUB.ADD;
3589: END IF;
3590: END LOOP;
3591: END LOOP;
3592: for i in 1..l_res_ids.count loop
3593: CSP_SCH_INT_PVT.DELETE_RESERVATION(l_res_ids(i),x_return_status,x_msg_data);
3594: end loop;
3595: EXCEPTION
3596: WHEN OTHERS THEN
3597: FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
3604: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3605: return;
3606: END CHECK_LOCAl_INVENTORY;
3607: --hehxxx
3608: PROCEDURE SPARES_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3609: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
3610: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
3611: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3612: ,x_return_status OUT NOCOPY VARCHAR2
3605: return;
3606: END CHECK_LOCAl_INVENTORY;
3607: --hehxxx
3608: PROCEDURE SPARES_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3609: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
3610: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
3611: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3612: ,x_return_status OUT NOCOPY VARCHAR2
3613: ,x_msg_data OUT NOCOPY VARCHAR2
3606: END CHECK_LOCAl_INVENTORY;
3607: --hehxxx
3608: PROCEDURE SPARES_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3609: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
3610: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
3611: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3612: ,x_return_status OUT NOCOPY VARCHAR2
3613: ,x_msg_data OUT NOCOPY VARCHAR2
3614: ,x_msg_count OUT NOCOPY NUMBER) is
3607: --hehxxx
3608: PROCEDURE SPARES_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3609: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
3610: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
3611: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
3612: ,x_return_status OUT NOCOPY VARCHAR2
3613: ,x_msg_data OUT NOCOPY VARCHAR2
3614: ,x_msg_count OUT NOCOPY NUMBER) is
3615:
3654: l_alternates := TRUE;
3655: else
3656: l_alternates := FALSE;
3657: end if;
3658: x_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
3659: for i in 1..p_unavailable_list.count loop
3660: open c_ship_to(p_unavailable_list(i).resource_type,p_unavailable_list(i).resource_id);
3661: fetch c_ship_to into l_hr_location_id,l_hz_location_id;
3662: close c_ship_to;
3679: p_required_parts(1).quantity := p_unavailable_list(i).quantity;
3680:
3681: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3682: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3683: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3684: 'Before calling csp_part_search_pvt.search...');
3685: end if;
3686:
3687: csp_part_search_pvt.search(
3701: close c_available;
3702:
3703: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3704: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3705: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3706: 'After calling csp_part_search_pvt.search...');
3707: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3708: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3709: 'l_organization_id=' || l_organization_id);
3704: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3705: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3706: 'After calling csp_part_search_pvt.search...');
3707: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3708: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3709: 'l_organization_id=' || l_organization_id);
3710: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3711: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3712: 'l_subinventory_code=' || l_subinventory_code);
3707: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3708: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3709: 'l_organization_id=' || l_organization_id);
3710: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3711: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3712: 'l_subinventory_code=' || l_subinventory_code);
3713: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3714: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3715: 'l_shipping_date=' || to_char(l_shipping_date, 'DD-MON-RRRR HH24:MI:SS'));
3710: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3711: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3712: 'l_subinventory_code=' || l_subinventory_code);
3713: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3714: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3715: 'l_shipping_date=' || to_char(l_shipping_date, 'DD-MON-RRRR HH24:MI:SS'));
3716: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3717: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3718: 'l_supplied_quantity=' || l_supplied_quantity);
3713: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3714: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3715: 'l_shipping_date=' || to_char(l_shipping_date, 'DD-MON-RRRR HH24:MI:SS'));
3716: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3717: 'csp.plsql.CSP_SCH_INT_PVT.SPARES_CHECK',
3718: 'l_supplied_quantity=' || l_supplied_quantity);
3719: end if;
3720:
3721: if l_organization_id is not null then
3750: end loop;
3751: end;
3752:
3753: PROCEDURE SPARES_CHECK2(
3754: p_resources IN CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ,
3755: p_task_id in number,
3756: p_need_by_date in date,
3757: p_trunk IN BOOLEAN,
3758: p_warehouse IN BOOLEAN,
3756: p_need_by_date in date,
3757: p_trunk IN BOOLEAN,
3758: p_warehouse IN BOOLEAN,
3759: p_mandatory IN BOOLEAN,
3760: x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ,
3761: x_return_status OUT NOCOPY VARCHAR2,
3762: x_msg_data OUT NOCOPY VARCHAR2,
3763: x_msg_count OUT NOCOPY NUMBER) is
3764:
3858: l_dist_temp_str varchar2(10);
3859: l_ship_method_meaning varchar2(1000);
3860:
3861: begin
3862: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
3863:
3864: open c_requirement_header;
3865: fetch c_requirement_header into l_requirement_header_id,
3866: l_ship_to_location_id,
4152: , p_data => x_msg_data);
4153: end if;
4154: end;
4155:
4156: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4157: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
4158: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4159: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4160: ,x_return_status OUT NOCOPY VARCHAR2
4153: end if;
4154: end;
4155:
4156: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4157: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
4158: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4159: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4160: ,x_return_status OUT NOCOPY VARCHAR2
4161: ,x_msg_data OUT NOCOPY VARCHAR2
4154: end;
4155:
4156: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4157: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
4158: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4159: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4160: ,x_return_status OUT NOCOPY VARCHAR2
4161: ,x_msg_data OUT NOCOPY VARCHAR2
4162: ,x_msg_count OUT NOCOPY NUMBER) IS
4155:
4156: PROCEDURE DO_ATP_CHECK(p_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4157: ,p_interval IN CSP_SCH_INT_PVT.csp_sch_interval_rec_typ
4158: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4159: ,x_final_unavailable_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4160: ,x_return_status OUT NOCOPY VARCHAR2
4161: ,x_msg_data OUT NOCOPY VARCHAR2
4162: ,x_msg_count OUT NOCOPY NUMBER) IS
4163: CURSOR instance IS
4209: -- x_msg_count NUMBER;
4210: l_msg varchar2(2000);
4211: l_ship_complete_profile VARCHAR2(3);
4212: l_instance_id NUMBER;
4213: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.DO_ATP_CHECK' ;
4214: l_message varchar2(2000);
4215: l_item_segments varchar2(1000);
4216: l_error BOOLEAN := FALSE;
4217: dbug_variable1 NUMBER;
4221: l_unavailable varchar2(1);
4222: BEGIN
4223: log('do_atp_check', 'Begin');
4224: x_return_status := FND_API.G_RET_STS_SUCCESS;
4225: x_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
4226: OPEN instance;
4227: FETCH instance INTO l_instance_id;
4228: CLOSE instance;
4229: log('do_atp_check', 'l_instance_id: '||l_instance_id);
4469: END DO_ATP_CHECK;
4470:
4471:
4472:
4473: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
4474: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4475: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4476: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4477: ,x_return_status OUT NOCOPY VARCHAR2
4470:
4471:
4472:
4473: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
4474: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4475: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4476: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4477: ,x_return_status OUT NOCOPY VARCHAR2
4478: ,x_msg_data OUT NOCOPY VARCHAR2
4471:
4472:
4473: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
4474: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4475: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4476: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4477: ,x_return_status OUT NOCOPY VARCHAR2
4478: ,x_msg_data OUT NOCOPY VARCHAR2
4479: ,x_msg_count OUT NOCOPY NUMBER) IS
4472:
4473: PROCEDURE ELIGIBLE_RESOURCES(p_resource_list IN CSP_SCH_INT_PVT.CSP_SCH_RESOURCE_tbl_TYP
4474: ,p_available_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4475: ,p_final_unavailable_list IN CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE
4476: ,x_eligible_resources_list OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4477: ,x_return_status OUT NOCOPY VARCHAR2
4478: ,x_msg_data OUT NOCOPY VARCHAR2
4479: ,x_msg_count OUT NOCOPY NUMBER) IS
4480:
4478: ,x_msg_data OUT NOCOPY VARCHAR2
4479: ,x_msg_count OUT NOCOPY NUMBER) IS
4480:
4481: resource_eligible BOOLEAN := TRUE;
4482: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.ELIGIBLE_RESOURCES';
4483: BEGIN
4484: x_return_status := FND_API.G_RET_STS_SUCCESS;
4485: x_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4486:
4481: resource_eligible BOOLEAN := TRUE;
4482: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.ELIGIBLE_RESOURCES';
4483: BEGIN
4484: x_return_status := FND_API.G_RET_STS_SUCCESS;
4485: x_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4486:
4487: FOR I IN 1..p_resource_list.count LOOP
4488:
4489: FOR J IN 1..p_final_unavailable_list.count LOOP
4526: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4527: return;
4528: END ELIGIBLE_RESOURCES;
4529:
4530: PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4531: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
4532: ,x_return_status OUT NOCOPY VARCHAR2
4533: ,x_msg_data OUT NOCOPY VARCHAR2
4534: ,x_msg_count OUT NOCOPY NUMBER) IS
4527: return;
4528: END ELIGIBLE_RESOURCES;
4529:
4530: PROCEDURE GET_TIME_COST(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4531: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
4532: ,x_return_status OUT NOCOPY VARCHAR2
4533: ,x_msg_data OUT NOCOPY VARCHAR2
4534: ,x_msg_count OUT NOCOPY NUMBER) IS
4535: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
4531: ,x_options OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
4532: ,x_return_status OUT NOCOPY VARCHAR2
4533: ,x_msg_data OUT NOCOPY VARCHAR2
4534: ,x_msg_count OUT NOCOPY NUMBER) IS
4535: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
4536: l_temp_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4537: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4538: loop_count NUMBER;
4539: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4532: ,x_return_status OUT NOCOPY VARCHAR2
4533: ,x_msg_data OUT NOCOPY VARCHAR2
4534: ,x_msg_count OUT NOCOPY NUMBER) IS
4535: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
4536: l_temp_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4537: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4538: loop_count NUMBER;
4539: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4540: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
4533: ,x_msg_data OUT NOCOPY VARCHAR2
4534: ,x_msg_count OUT NOCOPY NUMBER) IS
4535: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
4536: l_temp_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4537: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4538: loop_count NUMBER;
4539: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4540: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
4541: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4535: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
4536: l_temp_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4537: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4538: loop_count NUMBER;
4539: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4540: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
4541: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4542: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_TIME_COST' ;
4543: BEGIN
4536: l_temp_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4537: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4538: loop_count NUMBER;
4539: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4540: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
4541: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4542: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_TIME_COST' ;
4543: BEGIN
4544:
4537: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4538: loop_count NUMBER;
4539: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4540: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
4541: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4542: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_TIME_COST' ;
4543: BEGIN
4544:
4545: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4538: loop_count NUMBER;
4539: l_resources CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE;
4540: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
4541: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4542: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_TIME_COST' ;
4543: BEGIN
4544:
4545: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4546: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4543: BEGIN
4544:
4545: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4546: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4547: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4548: 'begin...');
4549: end if;
4550: x_return_status := FND_API.G_RET_STS_SUCCESS;
4551: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4547: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4548: 'begin...');
4549: end if;
4550: x_return_status := FND_API.G_RET_STS_SUCCESS;
4551: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4552: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4553: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4554: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4555: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
4548: 'begin...');
4549: end if;
4550: x_return_status := FND_API.G_RET_STS_SUCCESS;
4551: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4552: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4553: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4554: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4555: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
4556: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
4549: end if;
4550: x_return_status := FND_API.G_RET_STS_SUCCESS;
4551: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4552: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4553: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4554: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4555: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
4556: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
4557: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4550: x_return_status := FND_API.G_RET_STS_SUCCESS;
4551: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4552: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4553: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4554: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4555: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
4556: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
4557: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4558: loop_count :=1 ;
4551: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4552: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4553: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4554: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4555: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
4556: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
4557: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4558: loop_count :=1 ;
4559:
4552: l_temp_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4553: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4554: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4555: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
4556: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
4557: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4558: loop_count :=1 ;
4559:
4560: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4553: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
4554: l_resources := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
4555: x_options := CSP_SCH_INT_PVT.csp_sch_options_tbl_typ();
4556: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
4557: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4558: loop_count :=1 ;
4559:
4560: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4561: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4558: loop_count :=1 ;
4559:
4560: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4561: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4562: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4563: 'Starting while loop with p_eligible_resources_list.count=' || p_eligible_resources_list.count);
4564: end if;
4565:
4566: WHILE loop_count <= p_eligible_resources_list.count LOOP
4579: l_temp_resources_list(l_temp_resources_list.count).quantity := p_eligible_resources_list(loop_count).quantity;
4580: l_temp_resources_list(l_temp_resources_list.count).sub_inventory := p_eligible_resources_list(loop_count).sub_inventory;
4581: l_temp_resources_list(l_temp_resources_list.count).available_date := p_eligible_resources_list(loop_count).available_date;
4582: IF loop_count = p_eligible_resources_list.count THEN
4583: 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);
4584: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4585: RETURN;
4586: END IF;
4587: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
4583: 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);
4584: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4585: RETURN;
4586: END IF;
4587: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
4588: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4589: RETURN;
4590: END IF;
4591: l_temp_options.trim(l_temp_options.count);
4591: l_temp_options.trim(l_temp_options.count);
4592: l_temp_resources_list.trim(l_temp_resources_list.count);
4593: END IF;
4594: ELSE
4595: 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);
4596: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4597: RETURN;
4598: END IF;
4599: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
4595: 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);
4596: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4597: RETURN;
4598: END IF;
4599: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
4600: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4601: RETURN;
4602: END IF;
4603: l_temp_options.trim(l_temp_options.count);
4611: l_temp_resources_list(l_temp_resources_list.count).sub_inventory := p_eligible_resources_list(loop_count).sub_inventory ;
4612: l_temp_resources_list(l_temp_resources_list.count).available_date := p_eligible_resources_list(loop_count).available_date;
4613: END IF;
4614: IF loop_count = p_eligible_resources_list.count THEN
4615: 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);
4616: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4617: RETURN;
4618: END IF;
4619: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
4615: 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);
4616: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4617: RETURN;
4618: END IF;
4619: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
4620: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4621: RETURN;
4622: END IF;
4623: l_temp_options.trim(l_temp_options.count);
4626: ELSE
4627:
4628: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4629: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4630: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4631: 'In else part of loop_count > 1');
4632: end if;
4633:
4634: l_temp_resources_list.extend;
4643: IF p_eligible_resources_list.count =1 THEN
4644:
4645: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4646: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4647: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4648: 'Before calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS...');
4649: end if;
4650:
4651: 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);
4644:
4645: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4646: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4647: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4648: 'Before calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS...');
4649: end if;
4650:
4651: 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);
4652:
4647: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4648: 'Before calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS...');
4649: end if;
4650:
4651: 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);
4652:
4653: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4654: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4655: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4651: 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);
4652:
4653: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4654: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4655: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4656: 'After calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS... x_return_status=' || x_return_status);
4657: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4658: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4659: 'After calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS... l_temp_options.count=' || l_temp_options.count);
4652:
4653: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4654: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4655: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4656: 'After calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS... x_return_status=' || x_return_status);
4657: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4658: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4659: 'After calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS... l_temp_options.count=' || l_temp_options.count);
4660: end if;
4654: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4655: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4656: 'After calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS... x_return_status=' || x_return_status);
4657: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4658: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4659: 'After calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS... l_temp_options.count=' || l_temp_options.count);
4660: end if;
4661:
4662: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4655: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4656: 'After calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS... x_return_status=' || x_return_status);
4657: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4658: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4659: 'After calling CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS... l_temp_options.count=' || l_temp_options.count);
4660: end if;
4661:
4662: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4663: RETURN;
4664: END IF;
4665:
4666: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4667: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4668: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4669: 'Before calling BUILD_FINAL_LIST...');
4670: end if;
4671:
4672: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
4668: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4669: 'Before calling BUILD_FINAL_LIST...');
4670: end if;
4671:
4672: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
4673:
4674: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4675: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4676: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4672: CSP_SCH_INT_PVT.BUILD_FINAL_LIST(l_temp_options,x_options,x_return_status,x_msg_data,x_msg_count);
4673:
4674: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4675: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4676: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4677: 'After calling BUILD_FINAL_LIST... x_return_status=' || x_return_status);
4678: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4679: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4680: 'After calling BUILD_FINAL_LIST... x_options.count=' || x_options.count);
4675: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4676: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4677: 'After calling BUILD_FINAL_LIST... x_return_status=' || x_return_status);
4678: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4679: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4680: 'After calling BUILD_FINAL_LIST... x_options.count=' || x_options.count);
4681: end if;
4682:
4683: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4693: END LOOP;
4694:
4695: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4696: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4697: 'csp.plsql.CSP_SCH_INT_PVT.GET_TIME_COST',
4698: 'Coming out of the loop...');
4699: end if;
4700:
4701: EXCEPTION
4709: , p_data => x_msg_data);
4710: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4711: return;
4712: END GET_TIME_COST;
4713: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
4714: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
4715: ,x_return_status OUT NOCOPY VARCHAR2
4716: ,x_msg_data OUT NOCOPY VARCHAR2
4717: ,x_msg_count OUT NOCOPY NUMBER) IS
4710: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4711: return;
4712: END GET_TIME_COST;
4713: PROCEDURE BUILD_FINAL_LIST(p_temp_options IN CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
4714: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
4715: ,x_return_status OUT NOCOPY VARCHAR2
4716: ,x_msg_data OUT NOCOPY VARCHAR2
4717: ,x_msg_count OUT NOCOPY NUMBER) IS
4718: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.BUILD_FINAL_LIST';
4714: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.csp_sch_options_tbl_typ
4715: ,x_return_status OUT NOCOPY VARCHAR2
4716: ,x_msg_data OUT NOCOPY VARCHAR2
4717: ,x_msg_count OUT NOCOPY NUMBER) IS
4718: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.BUILD_FINAL_LIST';
4719: l_need_by_date date;
4720: BEGIN
4721: IF g_interval.latest_time IS NOT NULL
4722: THEN
4751: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4752: return;
4753: END BUILD_FINAL_LIST;
4754:
4755: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4756: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
4757: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
4758: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
4759: ,x_return_status OUT NOCOPY VARCHAR2
4752: return;
4753: END BUILD_FINAL_LIST;
4754:
4755: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4756: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
4757: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
4758: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
4759: ,x_return_status OUT NOCOPY VARCHAR2
4760: ,x_msg_data OUT NOCOPY VARCHAR2
4753: END BUILD_FINAL_LIST;
4754:
4755: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4756: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
4757: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
4758: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
4759: ,x_return_status OUT NOCOPY VARCHAR2
4760: ,x_msg_data OUT NOCOPY VARCHAR2
4761: ,x_msg_count OUT NOCOPY NUMBER) IS
4754:
4755: PROCEDURE OPTIMIZE_OPTIONS(p_eligible_resources IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
4756: ,px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
4757: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
4758: ,x_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
4759: ,x_return_status OUT NOCOPY VARCHAR2
4760: ,x_msg_data OUT NOCOPY VARCHAR2
4761: ,x_msg_count OUT NOCOPY NUMBER) IS
4762:
4767: and ism.from_location_id = hao.location_id
4768: and ism.to_location_id = c_to_location_id;
4769:
4770:
4771: l_resource_shipping_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4772: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4773: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
4774: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4775: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4768: and ism.to_location_id = c_to_location_id;
4769:
4770:
4771: l_resource_shipping_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4772: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4773: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
4774: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4775: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4776: L_API_NAME VARCHAR2(60) := 'CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS';
4769:
4770:
4771: l_resource_shipping_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4772: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4773: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
4774: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4775: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4776: L_API_NAME VARCHAR2(60) := 'CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS';
4777: loop_start NUMBER :=0 ;
4770:
4771: l_resource_shipping_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4772: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4773: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
4774: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4775: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4776: L_API_NAME VARCHAR2(60) := 'CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS';
4777: loop_start NUMBER :=0 ;
4778: loop_end NUMBER :=0 ;
4771: l_resource_shipping_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4772: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4773: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
4774: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4775: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4776: L_API_NAME VARCHAR2(60) := 'CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS';
4777: loop_start NUMBER :=0 ;
4778: loop_end NUMBER :=0 ;
4779: loop_min NUMBER :=100000 ;
4772: l_temp_rec CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE ;
4773: l_ship_quantity CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE ;
4774: l_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4775: l_temp_final_resource CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
4776: L_API_NAME VARCHAR2(60) := 'CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS';
4777: loop_start NUMBER :=0 ;
4778: loop_end NUMBER :=0 ;
4779: loop_min NUMBER :=100000 ;
4780: loop_max NUMBER :=0 ;
4798: l_to_org_id NUMBER;
4799: l_loc_ship_method_count NUMBER := 0;
4800: BEGIN
4801: x_return_status := FND_API.G_RET_STS_SUCCESS ;
4802: l_resource_shipping_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4803: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4804: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
4805: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
4806: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4799: l_loc_ship_method_count NUMBER := 0;
4800: BEGIN
4801: x_return_status := FND_API.G_RET_STS_SUCCESS ;
4802: l_resource_shipping_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4803: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4804: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
4805: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
4806: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4807: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4800: BEGIN
4801: x_return_status := FND_API.G_RET_STS_SUCCESS ;
4802: l_resource_shipping_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4803: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4804: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
4805: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
4806: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4807: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4808: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4801: x_return_status := FND_API.G_RET_STS_SUCCESS ;
4802: l_resource_shipping_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4803: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4804: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
4805: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
4806: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4807: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4808: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4809:
4802: l_resource_shipping_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4803: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4804: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
4805: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
4806: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4807: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4808: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4809:
4810: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4803: l_temp_rec := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE() ;
4804: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
4805: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
4806: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4807: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4808: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4809:
4810: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4811: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4804: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP() ;
4805: l_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE() ;
4806: l_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4807: l_temp_final_resource := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
4808: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4809:
4810: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4811: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4812: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4808: x_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
4809:
4810: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4811: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4812: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4813: 'Begin...');
4814: end if;
4815:
4816: FOR I IN 1..p_eligible_resources.count LOOP
4820: CLOSE location_shipping_method_count;
4821:
4822: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4823: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4824: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4825: 'inside first for loop l_loc_ship_method_count for row I =' || l_loc_ship_method_count || '(' || I || ')');
4826: end if;
4827:
4828: IF p_eligible_resources(I).sub_inventory IS NULL AND
4838: IF do_optimize THEN
4839:
4840: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4841: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4842: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4843: 'doing do_optimize...');
4844: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4845: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4846: 'before calling CONSOLIDATE_QUANTITIES...');
4841: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4842: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4843: 'doing do_optimize...');
4844: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4845: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4846: 'before calling CONSOLIDATE_QUANTITIES...');
4847: end if;
4848:
4849: CSP_SCH_INT_PVT.CONSOLIDATE_QUANTITIES(p_eligible_resources, l_ship_quantity,x_return_status,x_msg_data,x_msg_count);
4845: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4846: 'before calling CONSOLIDATE_QUANTITIES...');
4847: end if;
4848:
4849: CSP_SCH_INT_PVT.CONSOLIDATE_QUANTITIES(p_eligible_resources, l_ship_quantity,x_return_status,x_msg_data,x_msg_count);
4850:
4851: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4852: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4853: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4849: CSP_SCH_INT_PVT.CONSOLIDATE_QUANTITIES(p_eligible_resources, l_ship_quantity,x_return_status,x_msg_data,x_msg_count);
4850:
4851: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4852: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4853: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4854: 'After calling CONSOLIDATE_QUANTITIES...x_return_status=' || x_return_status);
4855: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4856: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4857: 'After calling CONSOLIDATE_QUANTITIES...l_ship_quantity.count=' || l_ship_quantity.count);
4852: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4853: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4854: 'After calling CONSOLIDATE_QUANTITIES...x_return_status=' || x_return_status);
4855: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4856: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4857: 'After calling CONSOLIDATE_QUANTITIES...l_ship_quantity.count=' || l_ship_quantity.count);
4858: end if;
4859:
4860: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4862: END IF;
4863:
4864: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4865: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4866: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4867: 'before calling GET_SHIPPING_PARAMETERS...');
4868: end if;
4869:
4870: 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);
4866: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4867: 'before calling GET_SHIPPING_PARAMETERS...');
4868: end if;
4869:
4870: 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);
4871:
4872: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4873: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4874: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4870: 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);
4871:
4872: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4873: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4874: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4875: 'After calling GET_SHIPPING_PARAMETERS...x_return_status=' || x_return_status);
4876: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4877: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4878: 'After calling GET_SHIPPING_PARAMETERS...l_resource_shipping_parameters.count=' || l_resource_shipping_parameters.count);
4873: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4874: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4875: 'After calling GET_SHIPPING_PARAMETERS...x_return_status=' || x_return_status);
4876: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4877: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4878: 'After calling GET_SHIPPING_PARAMETERS...l_resource_shipping_parameters.count=' || l_resource_shipping_parameters.count);
4879: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4880: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4881: 'After calling GET_SHIPPING_PARAMETERS...x_ship_count.count=' || x_ship_count.count);
4876: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4877: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4878: 'After calling GET_SHIPPING_PARAMETERS...l_resource_shipping_parameters.count=' || l_resource_shipping_parameters.count);
4879: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4880: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4881: 'After calling GET_SHIPPING_PARAMETERS...x_ship_count.count=' || x_ship_count.count);
4882: end if;
4883:
4884: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4899: END LOOP;
4900:
4901: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4902: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4903: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4904: 'greatest_available_date=' || to_char(greatest_available_date, 'DD-MON-RRRR HH24:MI:SS'));
4905: end if;
4906:
4907: FOR I IN 1..x_ship_count.count LOOP
4912: END LOOP;
4913:
4914: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4915: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4916: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4917: 'min_leadtime=' || min_leadtime || ', max_leadtime=' || max_leadtime);
4918: end if;
4919:
4920: resource_count := 1 ;
4929: END LOOP;
4930:
4931: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4932: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4933: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
4934: 'after for loop first_count=' || first_count);
4935: end if;
4936:
4937: IF first_count > 1 THEN
5046: END LOOP;
5047:
5048: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5049: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
5050: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
5051: 'Out of while loop ...');
5052: end if;
5053:
5054: FOR I IN 1..x_ship_count.count LOOP
5066: I :=1;
5067:
5068: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5069: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
5070: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
5071: 'another while loop ... l_temp_rec.count=' || l_temp_rec.count);
5072: end if;
5073:
5074: WHILE I <= l_temp_rec.count LOOP
5075: first_count := 0;
5076:
5077: if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5078: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
5079: 'csp.plsql.CSP_SCH_INT_PVT.OPTIMIZE_OPTIONS',
5080: 'another while loop ... first_count=' || first_count);
5081: end if;
5082:
5083: FOR J IN 1..x_ship_count.count LOOP
5234: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5235: return;
5236: END OPTIMIZE_OPTIONS;
5237:
5238: PROCEDURE CONSOLIDATE_QUANTITIES(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
5239: ,x_ship_quantity OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
5240: ,x_return_status OUT NOCOPY VARCHAR2
5241: ,x_msg_data OUT NOCOPY VARCHAR2
5242: ,x_msg_count OUT NOCOPY NUMBER) IS
5235: return;
5236: END OPTIMIZE_OPTIONS;
5237:
5238: PROCEDURE CONSOLIDATE_QUANTITIES(p_eligible_resources_list IN CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
5239: ,x_ship_quantity OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE
5240: ,x_return_status OUT NOCOPY VARCHAR2
5241: ,x_msg_data OUT NOCOPY VARCHAR2
5242: ,x_msg_count OUT NOCOPY NUMBER) IS
5243: I NUMBER;
5242: ,x_msg_count OUT NOCOPY NUMBER) IS
5243: I NUMBER;
5244: J NUMBER;
5245: available BOOLEAN;
5246: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CONSOLIDATE_QUANTITIES';
5247: BEGIN
5248: x_return_status := FND_API.G_RET_STS_SUCCESS ;
5249: x_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE();
5250: x_ship_quantity.trim(x_ship_quantity.count);
5245: available BOOLEAN;
5246: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CONSOLIDATE_QUANTITIES';
5247: BEGIN
5248: x_return_status := FND_API.G_RET_STS_SUCCESS ;
5249: x_ship_quantity := CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE();
5250: x_ship_quantity.trim(x_ship_quantity.count);
5251: FOR I IN 1..p_eligible_resources_list.count LOOP
5252: IF p_eligible_resources_list(I).sub_inventory IS NULL THEN
5253: IF x_ship_quantity.count >= 1 THEN
5296: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5297: return;
5298: END CONSOLIDATE_QUANTITIES;
5299:
5300: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE,
5301: x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
5302: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
5303: ,x_return_status OUT NOCOPY VARCHAR2
5304: ,x_msg_data OUT NOCOPY VARCHAR2
5297: return;
5298: END CONSOLIDATE_QUANTITIES;
5299:
5300: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE,
5301: x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
5302: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
5303: ,x_return_status OUT NOCOPY VARCHAR2
5304: ,x_msg_data OUT NOCOPY VARCHAR2
5305: ,x_msg_count OUT NOCOPY NUMBER) IS
5298: END CONSOLIDATE_QUANTITIES;
5299:
5300: PROCEDURE GET_SHIPPING_PARAMETERS(p_ship_quantity IN CSP_SCH_INT_PVT.CSP_SHIP_QUANTITY_TBL_TYPE,
5301: x_resource_ship_parameters OUT NOCOPY CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
5302: ,x_ship_count OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
5303: ,x_return_status OUT NOCOPY VARCHAR2
5304: ,x_msg_data OUT NOCOPY VARCHAR2
5305: ,x_msg_count OUT NOCOPY NUMBER) IS
5306: CURSOR csp_ship_methodes(from_org varchar2,to_org varchar2) IS
5366: l_msg_data VARCHAR2(2000);
5367: l_return_status VARCHAR2(3);
5368: l_server_delivery_date DATE;
5369: l_server_cutoff_time DATE;
5370: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.GET_SHIPPING_PARAMETERS';
5371: l_from_org_code VARCHAR2(10);
5372: l_to_org_code VARCHAR2(10);
5373: l_sysdate DATE;
5374: l_server_sys_date Date;
5388: l_need_by_date := g_interval.latest_time;
5389: ELSE
5390: l_need_by_date := g_interval.earliest_time;
5391: END IF;
5392: x_resource_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
5393: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
5394: x_ship_count.trim(x_ship_count.count) ;
5395: FOR I IN 1..p_ship_quantity.count LOOP
5396: IF p_ship_quantity(I).resource_type = 'DUMMY' and
5389: ELSE
5390: l_need_by_date := g_interval.earliest_time;
5391: END IF;
5392: x_resource_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
5393: x_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
5394: x_ship_count.trim(x_ship_count.count) ;
5395: FOR I IN 1..p_ship_quantity.count LOOP
5396: IF p_ship_quantity(I).resource_type = 'DUMMY' and
5397: p_ship_quantity(I).resource_id = '999999999999999' or g_shipto_timezone_id is not null THEN
5668: l_timezone_id NUMBER;
5669: l_saftey_zone NUMBER;
5670: l_delivery_date DATE;
5671: l_default_unit_for_hour varchar2(3);
5672: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.GET_DELIVERY_DATE' ;
5673: BEGIN
5674: x_return_status := FND_API.G_RET_STS_SUCCESS ;
5675: l_default_unit_for_hour := FND_PROFILE.VALUE(NAME => 'CSF_UOM_HOURS');
5676: OPEN C1(p_relation_ship_id);
5782: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5783: return;
5784: END EXTEND_ATP_REC;
5785:
5786: FUNCTION CREATE_RESERVATION(p_reservation_parts IN CSP_SCH_INT_PVT.RESERVATION_REC_TYP
5787: ,x_return_status OUT NOCOPY VARCHAR2
5788: ,x_msg_data OUT NOCOPY VARCHAR2)
5789: RETURN NUMBER
5790: IS
6050: l_msg_count NUMBER;
6051: l_msg_data varchar2(1000);
6052: l_min_leadtime NUMBER := 100000000;
6053: l_shipping_methode varchar2(30);
6054: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.GET_SHIPPING_METHODE' ;
6055: BEGIN
6056: x_return_status := FND_API.G_RET_STS_SUCCESS ;
6057: l_server_time_zone_id := FND_PROFILE.VALUE(NAME => 'SERVER_TIMEZONE_ID');
6058: OPEN C3(p_from_org_id,p_to_org_id);
6058: OPEN C3(p_from_org_id,p_to_org_id);
6059: LOOP
6060: FETCH C3 INTO l_cost,l_relation_ship_id ,l_cutoff_time,l_timezone_id,l_shipping_methode ;
6061: EXIT WHEN C3%NOTFOUND;
6062: 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);
6063: IF p_timezone_id <> l_server_time_zone_id THEN
6064: HZ_TIMEZONE_PUB.Get_Time( p_api_version => 1.0,
6065: p_init_msg_list => FND_API.G_FALSE,
6066: p_source_tz_id => p_timezone_id,
6113: return;
6114: END GET_SHIPPING_METHODE;
6115:
6116: PROCEDURE CHECK_PARTS_AVAILABILITY(
6117: p_resource IN CSP_SCH_INT_PVT.csp_sch_resources_rec_typ ,
6118: p_organization_id IN NUMBER ,
6119: p_subinv_code IN VARCHAR2 ,
6120: p_need_by_date IN DATE ,
6121: p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ,
6117: p_resource IN CSP_SCH_INT_PVT.csp_sch_resources_rec_typ ,
6118: p_organization_id IN NUMBER ,
6119: p_subinv_code IN VARCHAR2 ,
6120: p_need_by_date IN DATE ,
6121: p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ,
6122: p_timezone_id IN NUMBER ,
6123: x_availability OUT NOCOPY CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1 ,
6124: x_return_status OUT NOCOPY VARCHAR2 ,
6125: X_MSG_DATA OUT NOCOPY VARCHAR2 ,
6119: p_subinv_code IN VARCHAR2 ,
6120: p_need_by_date IN DATE ,
6121: p_parts_list IN CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYP1 ,
6122: p_timezone_id IN NUMBER ,
6123: x_availability OUT NOCOPY CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1 ,
6124: x_return_status OUT NOCOPY VARCHAR2 ,
6125: X_MSG_DATA OUT NOCOPY VARCHAR2 ,
6126: x_msg_count OUT NOCOPY NUMBER ,
6127: p_called_from IN VARCHAR2 ,
6211: AND mri.INVENTORY_ITEM_ID = item_id
6212: AND mri.RELATIONSHIP_TYPE_ID = 2
6213: AND mri.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;
6214:
6215: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
6216: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
6217: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
6218: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6219: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6212: AND mri.RELATIONSHIP_TYPE_ID = 2
6213: AND mri.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;
6214:
6215: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
6216: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
6217: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
6218: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6219: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6220: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6213: AND mri.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID;
6214:
6215: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
6216: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
6217: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
6218: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6219: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6220: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6221: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
6214:
6215: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
6216: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
6217: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
6218: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6219: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6220: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6221: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
6222: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6215: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
6216: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
6217: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
6218: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6219: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6220: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6221: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
6222: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6223: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6216: l_resources CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ;
6217: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
6218: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6219: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6220: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6221: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
6222: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6223: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6224: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
6217: l_resource_org_subinv CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP;
6218: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6219: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6220: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6221: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
6222: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6223: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6224: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
6225: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
6218: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6219: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6220: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6221: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
6222: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6223: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6224: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
6225: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
6226: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
6219: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6220: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6221: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
6222: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6223: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6224: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
6225: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
6226: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
6227: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
6220: /* l_subinv_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6221: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
6222: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6223: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6224: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
6225: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
6226: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
6227: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
6228: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6221: l_subinv_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;*/
6222: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6223: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6224: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
6225: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
6226: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
6227: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
6228: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6229: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6222: l_eligible_resources_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6223: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6224: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
6225: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
6226: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
6227: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
6228: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6229: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6230: l_org_ship_methode org_ship_methodes_tbl_type ;
6223: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
6224: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
6225: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
6226: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
6227: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
6228: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6229: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6230: l_org_ship_methode org_ship_methodes_tbl_type ;
6231: l_supersede_items CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
6224: l_parts_list CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE;
6225: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
6226: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
6227: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
6228: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6229: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6230: l_org_ship_methode org_ship_methodes_tbl_type ;
6231: l_supersede_items CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
6232: min_cost NUMBER ;
6225: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
6226: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
6227: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
6228: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6229: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
6230: l_org_ship_methode org_ship_methodes_tbl_type ;
6231: l_supersede_items CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
6232: min_cost NUMBER ;
6233: rec_pointer NUMBER;
6241: l_need_by_date DATE;
6242: previous_position NUMBER;
6243: str_length NUMBER;
6244: current_position NUMBER;
6245: l_api_name VARCHAR2(60) := 'CSP_SCH_INT_PVT.CHECK_PARTS_AVAILABILITY';
6246: g_arrival_date DATE;
6247: l_ship_methode_count NUMBER;
6248: l_assignment_set_id NUMBER;
6249: l_vendor_id NUMBER;
6253: l_temp_quantity NUMBER;
6254: l_substitute_item NUMBER;
6255: l_alternate_items_list alternate_items_table_type;
6256: l_alternate_parts CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
6257: l_final_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6258: l_atp_success BOOLEAN;
6259: check_subinventory BOOLEAN;
6260: l_att NUMBER;
6261: l_onhand NUMBER;
6258: l_atp_success BOOLEAN;
6259: check_subinventory BOOLEAN;
6260: l_att NUMBER;
6261: l_onhand NUMBER;
6262: x_final_availability CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1;
6263: l_temp_subinv_code VARCHAR2(30);
6264: try_buy_from BOOLEAN;
6265: l_temp_line_id NUMBER;
6266: quantity_fullfilled BOOLEAN := FALSE ;
6267: l_required_quantity NUMBER;
6268: l_no_of_options NUMBER;
6269: l_dest_org_id NUMBER;
6270: l_atleast_one_rec_per_line BOOLEAN;
6271: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6272: min_arrival_date DATE;
6273: l_no_of_days NUMBER;--heh
6274: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
6275: l_reservation_id NUMBER;
6270: l_atleast_one_rec_per_line BOOLEAN;
6271: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
6272: min_arrival_date DATE;
6273: l_no_of_days NUMBER;--heh
6274: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
6275: l_reservation_id NUMBER;
6276: l_res_ids CSP_SUPERSESSIONS_PVT.NUMBER_ARR;
6277: l_primary_uom_code VARCHAR2(10);
6278: l_replenisment_org_id NUMBER;
6284: fnd_msg_pub.initialize;
6285: IF p_timezone_id IS NOT NULL THEN
6286: g_shipto_timezone_id := p_timezone_id;
6287: END IF;
6288: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
6289: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
6290: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6291: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
6292: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6285: IF p_timezone_id IS NOT NULL THEN
6286: g_shipto_timezone_id := p_timezone_id;
6287: END IF;
6288: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
6289: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
6290: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6291: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
6292: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6293: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6286: g_shipto_timezone_id := p_timezone_id;
6287: END IF;
6288: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
6289: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
6290: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6291: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
6292: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6293: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6294: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
6287: END IF;
6288: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
6289: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
6290: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6291: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
6292: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6293: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6294: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
6295: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
6288: l_resources := CSP_SCH_INT_PVT.csp_sch_resource_tbl_typ();
6289: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
6290: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6291: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
6292: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6293: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6294: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
6295: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
6296: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
6289: l_resource_org_subinv := CSP_SCH_INT_PVT.CSP_RESOURCE_ORG_tbl_TYP();
6290: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6291: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
6292: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6293: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6294: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
6295: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
6296: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
6297: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
6290: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6291: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
6292: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6293: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6294: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
6295: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
6296: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
6297: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
6298: l_org_ship_methode := org_ship_methodes_tbl_type() ;
6291: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ();
6292: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6293: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6294: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
6295: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
6296: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
6297: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
6298: l_org_ship_methode := org_ship_methodes_tbl_type() ;
6299: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
6292: l_eligible_resources_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6293: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6294: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
6295: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
6296: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
6297: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
6298: l_org_ship_methode := org_ship_methodes_tbl_type() ;
6299: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
6300: l_subinv_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();*/
6293: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE();
6294: l_parts_list := CSP_SCH_INT_PVT.CSP_PARTS_TBL_TYPE();
6295: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
6296: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
6297: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
6298: l_org_ship_methode := org_ship_methodes_tbl_type() ;
6299: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
6300: l_subinv_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();*/
6301: -- x_availability := CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1();
6295: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
6296: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
6297: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
6298: l_org_ship_methode := org_ship_methodes_tbl_type() ;
6299: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
6300: l_subinv_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();*/
6301: -- x_availability := CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1();
6302: l_interval.latest_time := p_need_by_date;
6303: x_return_status := FND_API.G_RET_STS_SUCCESS;
6296: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
6297: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
6298: l_org_ship_methode := org_ship_methodes_tbl_type() ;
6299: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
6300: l_subinv_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();*/
6301: -- x_availability := CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1();
6302: l_interval.latest_time := p_need_by_date;
6303: x_return_status := FND_API.G_RET_STS_SUCCESS;
6304: l_alternate_items_list := alternate_items_table_type();
6297: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
6298: l_org_ship_methode := org_ship_methodes_tbl_type() ;
6299: /* l_subinv_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
6300: l_subinv_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();*/
6301: -- x_availability := CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1();
6302: l_interval.latest_time := p_need_by_date;
6303: x_return_status := FND_API.G_RET_STS_SUCCESS;
6304: l_alternate_items_list := alternate_items_table_type();
6305: l_final_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6301: -- x_availability := CSP_SCH_INT_PVT.AVAILABLE_PARTS_TBL_TYP1();
6302: l_interval.latest_time := p_need_by_date;
6303: x_return_status := FND_API.G_RET_STS_SUCCESS;
6304: l_alternate_items_list := alternate_items_table_type();
6305: l_final_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6306: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6307: l_timezone_id := p_timezone_id ;
6308: log('check_parts_availability', 'p_organization_id: '||p_organization_id);
6309: log('check_parts_availability', 'p_resource.resource_id: '||p_resource.resource_id);
6302: l_interval.latest_time := p_need_by_date;
6303: x_return_status := FND_API.G_RET_STS_SUCCESS;
6304: l_alternate_items_list := alternate_items_table_type();
6305: l_final_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6306: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
6307: l_timezone_id := p_timezone_id ;
6308: log('check_parts_availability', 'p_organization_id: '||p_organization_id);
6309: log('check_parts_availability', 'p_resource.resource_id: '||p_resource.resource_id);
6310: IF p_organization_id IS NOT NULL THEN
6333: CLOSE C1;
6334: l_resources.extend;
6335: l_resources(1).resource_id := p_resource.resource_id;
6336: l_resources(1).resource_type := p_resource.resource_type;
6337: CSP_SCH_INT_PVT.GET_ORGANIZATION_SUBINV(l_resources, l_resource_org_subinv , l_return_status,x_msg_data,x_msg_count);
6338: l_temp_org_id := l_resource_org_subinv(1).organization_id;
6339: l_temp_subinv_code := l_resource_org_subinv(1).sub_inv_code;
6340: ELSE
6341: log('check_parts_availability', '5');
6444: 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);
6445: l_reservation_parts.sub_inventory_code := l_final_available_list(l_final_available_list.count).sub_inventory;
6446: l_reservation_parts.line_id := l_final_available_list(l_final_available_list.count).line_id ;
6447: l_reservation_parts.revision := l_final_available_list(l_final_available_list.count).revision;
6448: l_reservation_id := csp_sch_int_pvt.create_reservation(l_reservation_parts,x_return_status,x_msg_data);
6449: log('check_parts_availability', 'l_reservation_id: '||l_reservation_id);
6450: IF l_reservation_id <= 0 THEN
6451: x_return_status := FND_API.G_RET_STS_ERROR;
6452: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
6599: 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) ;
6600: l_reservation_parts.sub_inventory_code := l_final_available_list(l_final_available_list.count).sub_inventory;
6601: l_reservation_parts.line_id := l_final_available_list(l_final_available_list.count).line_id ;
6602: l_reservation_parts.revision := l_final_available_list(l_final_available_list.count).revision;
6603: l_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(l_reservation_parts,x_return_status,x_msg_data);
6604: IF l_reservation_id <= 0 THEN
6605: x_return_status := FND_API.G_RET_STS_ERROR;
6606: FND_MESSAGE.SET_NAME('CSP', 'CSP_SCH_COULD_NOT_RESERVE');
6607: FND_MSG_PUB.ADD;
6623: log('check_parts_availability', 'begin l_res_ids loop. count: '||l_res_ids.count);
6624: FOR i IN 1..l_res_ids.count
6625: LOOP
6626: log('check_parts_availability', 'l_res_ids('||i||'): '||l_res_ids(i));
6627: CSP_SCH_INT_PVT.DELETE_RESERVATION(l_res_ids(i),x_return_status,x_msg_data);
6628: END LOOP;
6629: log('check_parts_availability', 'profile CSP_CHECK_ATP value: '||fnd_profile.value(name => 'CSP_CHECK_ATP'));
6630: IF fnd_profile.value(name => 'CSP_CHECK_ATP')= 'ALWAYS' OR fnd_profile.value(name => 'CSP_CHECK_ATP')= 'PRONLY' THEN
6631: log('check_parts_availability', 'l_unavailable_list count: '||l_unavailable_list.count);
6629: log('check_parts_availability', 'profile CSP_CHECK_ATP value: '||fnd_profile.value(name => 'CSP_CHECK_ATP'));
6630: IF fnd_profile.value(name => 'CSP_CHECK_ATP')= 'ALWAYS' OR fnd_profile.value(name => 'CSP_CHECK_ATP')= 'PRONLY' THEN
6631: log('check_parts_availability', 'l_unavailable_list count: '||l_unavailable_list.count);
6632: IF l_unavailable_list.count >=1 THEN
6633: log('check_parts_availability', 'calling csp_sch_int_pvt.do_atp_check');
6634: 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);
6635: log('check_parts_availability', 'after csp_sch_int_pvt.do_atp_check');
6636: log('check_parts_availability', 'l_return_status: '||l_return_status);
6637: log('check_parts_availability', 'x_msg_data: '||x_msg_data);
6630: IF fnd_profile.value(name => 'CSP_CHECK_ATP')= 'ALWAYS' OR fnd_profile.value(name => 'CSP_CHECK_ATP')= 'PRONLY' THEN
6631: log('check_parts_availability', 'l_unavailable_list count: '||l_unavailable_list.count);
6632: IF l_unavailable_list.count >=1 THEN
6633: log('check_parts_availability', 'calling csp_sch_int_pvt.do_atp_check');
6634: 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);
6635: log('check_parts_availability', 'after csp_sch_int_pvt.do_atp_check');
6636: log('check_parts_availability', 'l_return_status: '||l_return_status);
6637: log('check_parts_availability', 'x_msg_data: '||x_msg_data);
6638: log('check_parts_availability', 'x_msg_count: '||x_msg_count);
6631: log('check_parts_availability', 'l_unavailable_list count: '||l_unavailable_list.count);
6632: IF l_unavailable_list.count >=1 THEN
6633: log('check_parts_availability', 'calling csp_sch_int_pvt.do_atp_check');
6634: 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);
6635: log('check_parts_availability', 'after csp_sch_int_pvt.do_atp_check');
6636: log('check_parts_availability', 'l_return_status: '||l_return_status);
6637: log('check_parts_availability', 'x_msg_data: '||x_msg_data);
6638: log('check_parts_availability', 'x_msg_count: '||x_msg_count);
6639: END IF;
6648: LOOP
6649: log('check_parts_availability', 'l_available_list('||j||').line_id: '||l_available_list(j).line_id);
6650: log('check_parts_availability', 'p_parts_list('||K||').line_id: '||p_parts_list(K).line_id);
6651: IF l_available_list(J).line_id = p_parts_list(K).line_id THEN
6652: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
6653: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
6654: l_temp_avail_list.extend;
6655: l_temp_avail_list(1) := l_available_list(J);
6656: l_temp_avail_list(1).destination_location_id := p_location_id;
6649: log('check_parts_availability', 'l_available_list('||j||').line_id: '||l_available_list(j).line_id);
6650: log('check_parts_availability', 'p_parts_list('||K||').line_id: '||p_parts_list(K).line_id);
6651: IF l_available_list(J).line_id = p_parts_list(K).line_id THEN
6652: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
6653: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
6654: l_temp_avail_list.extend;
6655: l_temp_avail_list(1) := l_available_list(J);
6656: l_temp_avail_list(1).destination_location_id := p_location_id;
6657: 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);
6653: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
6654: l_temp_avail_list.extend;
6655: l_temp_avail_list(1) := l_available_list(J);
6656: l_temp_avail_list(1).destination_location_id := p_location_id;
6657: 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);
6658: min_cost := NULL;
6659: l_temp_avail_list.trim;
6660: min_arrival_date := NULL;
6661: rec_pointer := NULL;
7014: l_return_status VARCHAR2(128);
7015: l_qr NUMBER;
7016: l_qs NUMBER;
7017: l_temp_reserv_quantity NUMBER;
7018: l_api_name varchar2(60) := 'CSP_SCH_INT_PVT.CHECK_LOCAl_INVENTORY';
7019: l_is_revision_control boolean := null;
7020: BEGIN
7021: IF p_revision IS NOT NULL THEN
7022: l_is_revision_control := TRUE;
7085: ,p_called_by => 1);
7086:
7087:
7088: END TASKS_POST_INSERT;
7089: PROCEDURE strip_into_lines(px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
7090: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
7091: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
7092: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
7093: ,x_msg_data OUT NOCOPY varchar2
7086:
7087:
7088: END TASKS_POST_INSERT;
7089: PROCEDURE strip_into_lines(px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
7090: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
7091: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
7092: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
7093: ,x_msg_data OUT NOCOPY varchar2
7094: ,x_return_status OUT NOCOPY varchar2
7087:
7088: END TASKS_POST_INSERT;
7089: PROCEDURE strip_into_lines(px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
7090: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
7091: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
7092: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
7093: ,x_msg_data OUT NOCOPY varchar2
7094: ,x_return_status OUT NOCOPY varchar2
7095: ,x_msg_count OUT NOCOPY NUMBER) IS
7088: END TASKS_POST_INSERT;
7089: PROCEDURE strip_into_lines(px_options IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE
7090: ,p_ship_count IN CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP
7091: ,p_res_ship_parameters IN CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE
7092: ,px_available_list IN OUT NOCOPY CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE
7093: ,x_msg_data OUT NOCOPY varchar2
7094: ,x_return_status OUT NOCOPY varchar2
7095: ,x_msg_count OUT NOCOPY NUMBER) IS
7096:
7095: ,x_msg_count OUT NOCOPY NUMBER) IS
7096:
7097:
7098: l_org_ship_methode org_ship_methodes_tbl_type ;
7099: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ;
7100: min_cost number;
7101: previous_position NUMBER;
7102: str_length NUMBER;
7103: l_ship_methode_count NUMBER;
7104: g_arrival_date DATE;
7105: current_position NUMBER;
7106:
7107: BEGIN
7108: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE() ;
7109: l_org_ship_methode := org_ship_methodes_tbl_type() ;
7110: FOR I IN 1..px_options.count LOOP
7111: IF I =1 THEN
7112: min_cost := px_options(I).transfer_cost;
7196: END LOOP;
7197: END strip_into_lines;
7198: PROCEDURE ws_Check_engineers_subinv(p_resource_type IN varchar2
7199: ,p_resource_id IN NUMBER
7200: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
7201: ,p_include_alternate IN varchar2
7202: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
7203: ,x_return_status OUT NOCOPY varchar2
7204: ,x_msg_data OUT NOCOPY varchar2
7198: PROCEDURE ws_Check_engineers_subinv(p_resource_type IN varchar2
7199: ,p_resource_id IN NUMBER
7200: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
7201: ,p_include_alternate IN varchar2
7202: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
7203: ,x_return_status OUT NOCOPY varchar2
7204: ,x_msg_data OUT NOCOPY varchar2
7205: ,x_msg_count OUT NOCOPY NUMBER) IS
7206: l_att NUMBER:=0;
7206: l_att NUMBER:=0;
7207: l_onhand NUMBER := 0;
7208: l_org_id NUMBER;
7209: l_subinv_code varchar2(30);
7210: l_items csp_sch_int_pvt.alternate_items_table_type;
7211:
7212: CURSOR get_all_subinventories(l_resource_id number, l_resource_type varchar2) IS
7213: SELECT cil.ORGANIZATION_ID, cil.SUBINVENTORY_CODE
7214: FROM CSP_INV_LOC_ASSIGNMENTS cil,csp_sec_inventories csi
7253: AND organization_id = org_id;
7254: l_primary_uom varchar2(500);
7255:
7256: BEGIN
7257: l_items := csp_sch_int_pvt.alternate_items_table_type();
7258: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7259: OPEN get_default_subinventory(p_resource_id,p_resource_type);
7260: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
7261: CLOSE get_default_subinventory;
7254: l_primary_uom varchar2(500);
7255:
7256: BEGIN
7257: l_items := csp_sch_int_pvt.alternate_items_table_type();
7258: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7259: OPEN get_default_subinventory(p_resource_id,p_resource_type);
7260: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
7261: CLOSE get_default_subinventory;
7262: l_items.extend;
7351: END LOOP;
7352: END LOOP;
7353: CLOSE get_all_subinventories;
7354: END ws_Check_engineers_subinv;
7355: PROCEDURE ws_Check_other_eng_subinv(p_resource_list IN csp_sch_int_pvt.csp_ws_resource_table_type
7356: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
7357: ,p_include_alternate IN varchar2
7358: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
7359: ,x_return_status OUT NOCOPY varchar2
7352: END LOOP;
7353: CLOSE get_all_subinventories;
7354: END ws_Check_engineers_subinv;
7355: PROCEDURE ws_Check_other_eng_subinv(p_resource_list IN csp_sch_int_pvt.csp_ws_resource_table_type
7356: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
7357: ,p_include_alternate IN varchar2
7358: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
7359: ,x_return_status OUT NOCOPY varchar2
7360: ,x_msg_data OUT NOCOPY varchar2
7354: END ws_Check_engineers_subinv;
7355: PROCEDURE ws_Check_other_eng_subinv(p_resource_list IN csp_sch_int_pvt.csp_ws_resource_table_type
7356: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
7357: ,p_include_alternate IN varchar2
7358: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
7359: ,x_return_status OUT NOCOPY varchar2
7360: ,x_msg_data OUT NOCOPY varchar2
7361: ,x_msg_count OUT NOCOPY NUMBER) IS
7362:
7363: l_att NUMBER:=0;
7364: l_onhand NUMBER := 0;
7365: l_org_id NUMBER;
7366: l_subinv_code varchar2(30);
7367: l_items csp_sch_int_pvt.alternate_items_table_type;
7368:
7369: CURSOR get_default_subinventory(l_resource_id number, l_resource_type varchar2) IS
7370: SELECT ORGANIZATION_ID, SUBINVENTORY_CODE
7371: FROM CSP_INV_LOC_ASSIGNMENTS
7400: AND organization_id = org_id;
7401: l_primary_uom varchar2(500);
7402:
7403: BEGIN
7404: l_items := csp_sch_int_pvt.alternate_items_table_type();
7405: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7406: --null;
7407: /* OPEN get_default_subinventory(p_resource_list(1).resource_id,p_resource_list(1).resource_type);
7408: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
7401: l_primary_uom varchar2(500);
7402:
7403: BEGIN
7404: l_items := csp_sch_int_pvt.alternate_items_table_type();
7405: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7406: --null;
7407: /* OPEN get_default_subinventory(p_resource_list(1).resource_id,p_resource_list(1).resource_type);
7408: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
7409: CLOSE get_default_subinventory;
7526: END ws_Check_other_eng_subinv;
7527:
7528: PROCEDURE ws_Check_organizations(p_resource_type IN varchar2
7529: ,p_resource_id IN NUMBER
7530: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
7531: ,p_include_alternate IN varchar2
7532: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
7533: ,x_return_status OUT NOCOPY varchar2
7534: ,x_msg_data OUT NOCOPY varchar2
7528: PROCEDURE ws_Check_organizations(p_resource_type IN varchar2
7529: ,p_resource_id IN NUMBER
7530: ,p_parts_list IN csp_sch_int_pvt.CSP_PARTS_TBL_TYP1
7531: ,p_include_alternate IN varchar2
7532: ,x_available_list OUT NOCOPY csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP
7533: ,x_return_status OUT NOCOPY varchar2
7534: ,x_msg_data OUT NOCOPY varchar2
7535: ,x_msg_count OUT NOCOPY NUMBER) IS
7536:
7537: l_att NUMBER:=0;
7538: l_onhand NUMBER := 0;
7539: l_org_id NUMBER;
7540: l_subinv_code varchar2(30);
7541: l_items csp_sch_int_pvt.alternate_items_table_type;
7542: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7543: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
7544: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7545: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7538: l_onhand NUMBER := 0;
7539: l_org_id NUMBER;
7540: l_subinv_code varchar2(30);
7541: l_items csp_sch_int_pvt.alternate_items_table_type;
7542: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7543: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
7544: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7545: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7546: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7539: l_org_id NUMBER;
7540: l_subinv_code varchar2(30);
7541: l_items csp_sch_int_pvt.alternate_items_table_type;
7542: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7543: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
7544: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7545: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7546: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7547: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7540: l_subinv_code varchar2(30);
7541: l_items csp_sch_int_pvt.alternate_items_table_type;
7542: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7543: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
7544: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7545: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7546: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7547: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7548: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
7541: l_items csp_sch_int_pvt.alternate_items_table_type;
7542: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7543: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
7544: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7545: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7546: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7547: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7548: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
7549: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
7542: l_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7543: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
7544: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7545: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7546: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7547: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7548: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
7549: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
7550: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7543: l_interval CSP_SCH_INT_PVT.csp_sch_interval_rec_typ;
7544: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7545: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7546: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7547: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7548: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
7549: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
7550: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7551: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7544: l_available_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7545: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7546: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7547: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7548: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
7549: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
7550: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7551: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7552: l_return_status varchar2(10);
7545: l_temp_avail_list CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE ;
7546: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7547: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7548: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
7549: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
7550: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7551: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7552: l_return_status varchar2(10);
7553: p_need_by_date date := null ;
7546: l_final_unavailable_list CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE ;
7547: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7548: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
7549: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
7550: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7551: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7552: l_return_status varchar2(10);
7553: p_need_by_date date := null ;
7554: l_atleast_one_rec BOOLEAN;
7547: l_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7548: l_ship_count CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP;
7549: l_res_ship_parameters CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE;
7550: l_temp_options CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7551: l_final_option CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE;
7552: l_return_status varchar2(10);
7553: p_need_by_date date := null ;
7554: l_atleast_one_rec BOOLEAN;
7555: min_cost NUMBER;
7593: where lookup_code = c_ship_method;
7594:
7595: BEGIN
7596:
7597: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
7598: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
7599: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
7600:
7601: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
7594:
7595: BEGIN
7596:
7597: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
7598: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
7599: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
7600:
7601: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
7602: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
7595: BEGIN
7596:
7597: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
7598: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
7599: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
7600:
7601: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
7602: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
7603: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7597: l_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
7598: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
7599: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
7600:
7601: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
7602: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
7603: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7604: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7605: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7598: l_available_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE();
7599: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
7600:
7601: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
7602: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
7603: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7604: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7605: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7606: l_items := csp_sch_int_pvt.alternate_items_table_type();
7599: l_final_unavailable_list := CSP_SCH_INT_PVT.CSP_UNAVAILABILITY_TBL_TYPE() ;
7600:
7601: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
7602: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
7603: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7604: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7605: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7606: l_items := csp_sch_int_pvt.alternate_items_table_type();
7607: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
7600:
7601: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
7602: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
7603: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7604: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7605: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7606: l_items := csp_sch_int_pvt.alternate_items_table_type();
7607: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
7608: OPEN get_default_subinventory(p_resource_id,p_resource_type);
7601: l_ship_count := CSP_SCH_INT_PVT.CSP_SHIP_METHOD_COUNT_TBL_TYP();
7602: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
7603: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7604: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7605: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7606: l_items := csp_sch_int_pvt.alternate_items_table_type();
7607: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
7608: OPEN get_default_subinventory(p_resource_id,p_resource_type);
7609: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
7602: l_res_ship_parameters := CSP_SCH_INT_PVT.CSP_ORGS_SHIP_PARAM_TBL_TYPE();
7603: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7604: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7605: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7606: l_items := csp_sch_int_pvt.alternate_items_table_type();
7607: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
7608: OPEN get_default_subinventory(p_resource_id,p_resource_type);
7609: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
7610: CLOSE get_default_subinventory;
7603: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7604: l_final_option := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7605: x_available_list := csp_sch_int_pvt.ws_AVAILABLE_PARTS_tbl_TYP();
7606: l_items := csp_sch_int_pvt.alternate_items_table_type();
7607: l_temp_avail_list := CSP_SCH_INT_PVT.CSP_AVAILABILITY_TBL_TYPE() ;
7608: OPEN get_default_subinventory(p_resource_id,p_resource_type);
7609: FETCH get_default_subinventory INTO l_org_id, l_subinv_code;
7610: CLOSE get_default_subinventory;
7611: l_items.extend;
7636: l_unavailable_list(l_unavailable_list.count).item_type := l_items(J).relation_type ;
7637: l_unavailable_list(l_unavailable_list.count).line_id := 1;
7638: END LOOP;
7639: IF l_unavailable_list.count >=1 THEN
7640: 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);
7641: END IF;
7642: IF l_available_list.count > 0 THEN
7643: l_atleast_one_rec := FALSE;
7644: FOR J IN 1..l_available_list.count LOOP
7643: l_atleast_one_rec := FALSE;
7644: FOR J IN 1..l_available_list.count LOOP
7645: l_temp_avail_list.extend;
7646: l_temp_avail_list(1) := l_available_list(J);
7647: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
7648: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7649: 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);
7650: l_temp_avail_list.trim;
7651: min_cost := null;
7644: FOR J IN 1..l_available_list.count LOOP
7645: l_temp_avail_list.extend;
7646: l_temp_avail_list(1) := l_available_list(J);
7647: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
7648: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7649: 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);
7650: l_temp_avail_list.trim;
7651: min_cost := null;
7652: min_arrival_date := null;
7645: l_temp_avail_list.extend;
7646: l_temp_avail_list(1) := l_available_list(J);
7647: l_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE();
7648: l_temp_options := CSP_SCH_INT_PVT.CSP_SHIP_PARAMETERS_TBL_TYPE ();
7649: 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);
7650: l_temp_avail_list.trim;
7651: min_cost := null;
7652: min_arrival_date := null;
7653: rec_pointer := null;
7740: END IF;
7741: END LOOP;
7742:
7743: END ws_Check_organizations;
7744: PROCEDURE get_alternates(p_parts_rec IN csp_sch_int_pvt.CSP_PARTS_REC_TYPE
7745: ,p_org_id IN NUMBER
7746: ,px_alternate_items IN OUT NOCOPY csp_sch_int_pvt.alternate_items_table_type
7747: ,x_return_status OUT NOCOPY varchar2
7748: ,x_msg_data OUT NOCOPY varchar2
7742:
7743: END ws_Check_organizations;
7744: PROCEDURE get_alternates(p_parts_rec IN csp_sch_int_pvt.CSP_PARTS_REC_TYPE
7745: ,p_org_id IN NUMBER
7746: ,px_alternate_items IN OUT NOCOPY csp_sch_int_pvt.alternate_items_table_type
7747: ,x_return_status OUT NOCOPY varchar2
7748: ,x_msg_data OUT NOCOPY varchar2
7749: ,x_msg_count OUT NOCOPY NUMBER) IS
7750:
8101: OPEN get_reservations ;
8102: LOOP
8103: FETCH get_reservations INTO l_reserv_id,l_req_details_line_id;
8104: EXIT WHEN get_reservations%NOTFOUND;
8105: CSP_SCH_INT_PVT.CANCEL_RESERVATION(l_reserv_id,l_return_status,x_msg_data,x_msg_count);
8106: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
8107: x_return_status := FND_API.G_RET_STS_SUCCESS;
8108: ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
8109: delete from csp_req_line_details where req_line_detail_id = l_req_details_line_id;
8117: OPEN get_order_status(l_order_id);
8118: FETCH get_order_status INTO l_status;
8119: CLOSE get_order_status;
8120: IF l_status <> 'CANCELLED' THEN
8121: CSP_SCH_INT_PVT.CANCEL_ORDER(l_order_id,l_return_status,x_msg_data,x_msg_count);
8122: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
8123: x_return_status := FND_API.G_RET_STS_SUCCESS;
8124: ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
8125: open get_line_details(l_order_id);
8178: and crld.requirement_line_id = crl.requirement_line_id
8179: and crh.requirement_header_id = crl.requirement_header_id
8180: and crh.task_id is not null;
8181:
8182: l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
8183: l_reservation_id NUMBER;
8184: l_req_line_detali_id NUMBER;
8185:
8186: BEGIN
8193: l_reservation_parts.quantity_needed := grd.transaction_quantity ;
8194: l_reservation_parts.sub_inventory_code := grd.subinv_code ;
8195: l_reservation_parts.line_id := p_transaction_id;
8196: l_reservation_parts.revision := grd.revision;
8197: l_reservation_id := csp_sch_int_pvt.CREATE_RESERVATION(l_reservation_parts
8198: ,x_return_status
8199: ,x_msg_data );
8200: l_req_line_detali_id := NULL;
8201: IF l_reservation_id IS NOT NULL AND l_reservation_id >0 THEN
8384: AND dres.source_id = mr.reservation_id;
8385:
8386: l_trans_record csp_transactions_pub.trans_items_rec_type;
8387: l_trans_tbl csp_transactions_pub.trans_items_tbl_type;
8388: l_reservation_rec csp_sch_int_pvt.reservation_rec_typ;
8389: l_requirement_header csp_requirement_headers_pvt.requirement_header_rec_type;
8390: l_new_reservation_id number;
8391: l_req_line_detail_id number;
8392: l_new_dest_org number := -999;
8454: log('move_parts_on_reassign', 'old_res_record.requirement_line_id=' || old_res_record.requirement_line_id);
8455: log('move_parts_on_reassign', 'old_res_record.req_line_detail_id=' || old_res_record.req_line_detail_id);
8456: log('move_parts_on_reassign', 'old_res_record.requirement_header_id=' || old_res_record.requirement_header_id);
8457:
8458: CSP_SCH_INT_PVT.CANCEL_RESERVATION(old_res_record.source_id, x_return_status, x_msg_data, x_msg_count);
8459:
8460: log('move_parts_on_reassign', 'x_return_status=' || x_return_status);
8461:
8462: if x_return_status <> FND_API.G_RET_STS_SUCCESS then
8528: l_reservation_rec.line_id := old_res_record.requirement_line_id;
8529:
8530: log('move_parts_on_reassign', 'before creating a new reservation...');
8531:
8532: l_new_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(
8533: p_reservation_parts => l_reservation_rec,
8534: x_return_status => x_return_status,
8535: x_msg_data => x_msg_data);
8536:
8608: end loop;
8609:
8610: log('move_parts_on_reassign', 'returning...');
8611: end move_parts_on_reassign;
8612: END CSP_SCH_INT_PVT;