[Home] [Help]
85: CURSOR OPEN_line_count_csr (p_id in NUMBER) IS
86: SELECT count(1)
87: From ozf_resale_lines_int
88: WHERE resale_batch_id = p_id
89: AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
90: l_count NUMBER;
91:
92: TYPE id_type IS RECORD (
93: id NUMBER
94: );
95:
96: TYPE id_tbl_type is TABLE OF id_type INDEX BY binary_integer;
97:
98: l_line_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype;
99:
100: CURSOR valid_line_id_csr(p_id in NUMBER,
101: p_order_number in VARCHAR2,
102: p_cust_id in NUMBER,
107: AND order_number = p_order_number
108: AND sold_from_cust_account_id = p_cust_id
109: AND date_ordered = p_date
110: AND status_code = 'PROCESSED';
111: --AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED) ;
112:
113: l_valid_line_id_tbl id_tbl_type;
114:
115: i NUMBER;
127: date_ordered
128: FROM ozf_resale_lines_int
129: WHERE resale_batch_id = p_id
130: AND status_code = 'PROCESSED'
131: --AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
132: ORDER BY date_ordered;
133:
134: TYPE order_num_tbl_type IS TABLE OF batch_order_num_csr%rowtype INDEX BY binary_integer;
135: l_order_num_tbl order_num_tbl_type;
200: AND b.status_code IN ('DUPLICATED', 'PROCESSED')
201: AND b.duplicated_line_id = c.resale_line_id
202: AND c.resale_header_id = a.resale_header_id;
203:
204: l_dup_header_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
205:
206: --Start POS Batch Processing Changes
207: CURSOR csr_batch_request(cv_batch_id IN NUMBER, cv_partner_id IN NUMBER) IS
208: SELECT count(1)
263: l_batch_number,
264: l_last_updated_by;
265: CLOSE batch_info_csr;
266:
267: IF l_batch_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT THEN
268:
269: OPEN OPEN_line_count_csr(p_resale_batch_id);
270: FETCH OPEN_line_count_csr INTO l_count;
271: CLOSE OPEN_line_count_csr;
278: -- There is no need to find a fund id for the SPECIAL PRICING REQUEST
279: l_chargeback_fund_id := NULL;
280:
281: -- Check whether there is a need to do inventory_verification
282: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
283: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
284: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
285:
286: IF l_inventory_tracking = 'T' THEN
279: l_chargeback_fund_id := NULL;
280:
281: -- Check whether there is a need to do inventory_verification
282: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
283: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
284: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
285:
286: IF l_inventory_tracking = 'T' THEN
287: -- Bug 4380203 (+)
280:
281: -- Check whether there is a need to do inventory_verification
282: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
283: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
284: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
285:
286: IF l_inventory_tracking = 'T' THEN
287: -- Bug 4380203 (+)
288: OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
323: END IF;
324: /*
325: -- SLKRISHN change to use Update_Duplicates
326: -- Check for Duplicates
327: OZF_RESALE_COMMON_PVT.Update_Duplicates (
328: p_api_version => 1.0,
329: p_init_msg_list => FND_API.G_FALSE,
330: p_commit => FND_API.G_FALSE,
331: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
342: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
343: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344: END IF;
345:
346: IF l_batch_status = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED THEN
347: IF OZF_DEBUG_LOW_ON THEN
348: OZF_UTILITY_PVT.debug_message('OZF_RESALE_REJECTED');
349: END IF;
350: ELSE
389: -- There is something wrong with this order. dispute all the orders
390: -- and move to the next one.
391: -- JXWU move update to common pvt
392: UPDATE ozf_resale_lines_int_all
393: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
394: dispute_code = 'OZF_RESALE_MULTI_HEADER',
395: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
396: response_type = 'CA',
397: response_code = 'N'
398: WHERE resale_batch_id = p_resale_batch_id
399: AND order_number = l_order_num_tbl(k).order_number
400: AND sold_from_cust_account_id = l_order_num_tbl(k).sold_from_cust_account_id
401: AND date_ordered = l_order_num_tbl(k).date_ordered
402: AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
403:
404: GOTO END_ORDER_HEADER;
405: ELSE
406: l_create_order_header := false;
439: IF OZF_DEBUG_LOW_ON THEN
440: ozf_utility_PVT.debug_message('Current line_int_id:' || l_valid_line_id_tbl(j).id);
441: END IF;
442:
443: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
444: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
445: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
446:
447: -- Need to check against inventory
440: ozf_utility_PVT.debug_message('Current line_int_id:' || l_valid_line_id_tbl(j).id);
441: END IF;
442:
443: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
444: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
445: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
446:
447: -- Need to check against inventory
448: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
441: END IF;
442:
443: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
444: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
445: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
446:
447: -- Need to check against inventory
448: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
449: l_inventory_tracking = 'T' THEN
444: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
445: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
446:
447: -- Need to check against inventory
448: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
449: l_inventory_tracking = 'T' THEN
450:
451: -- Check inventory level first
452: OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
466: END IF;
467:
468: -- Here turn this line to disputed and create a disput code for it.
469: UPDATE ozf_resale_lines_int
470: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
471: dispute_code = 'OZF_LT_INVT',
472: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
473: response_type = 'CA',
474: response_code = 'N'
475: WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
476:
477: -- SET Batch as disputed
478: UPDATE ozf_resale_batches
479: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
480: WHERE resale_batch_id = l_line_int_rec.resale_batch_id;
481:
482: goto END_LOOP2;
483: END IF;
489: -- First, check whether there is need to create a header for this order
490: IF j = 1 THEN
491: -- Determin header id
492: IF l_create_order_header THEN
493: OZF_RESALE_COMMON_PVT.Insert_resale_header(
494: p_api_version => 1
495: ,p_init_msg_list => FND_API.G_FALSE
496: ,p_commit => FND_API.G_FALSE
497: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
511: -- We should have the l_header_id FROM the order level
512: END IF;
513: END IF;
514:
515: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED THEN
516: IF l_line_int_rec.duplicated_line_id is NULL THEN
517:
518: -- No problem so far. Insert INTO batch_lines table
519: OZF_RESALE_COMMON_PVT.Insert_resale_line(
515: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED THEN
516: IF l_line_int_rec.duplicated_line_id is NULL THEN
517:
518: -- No problem so far. Insert INTO batch_lines table
519: OZF_RESALE_COMMON_PVT.Insert_resale_line(
520: p_api_version => 1
521: ,p_init_msg_list => FND_API.G_FALSE
522: ,p_commit => FND_API.G_FALSE
523: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
534: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535: END IF;
536:
537: -- FOR processed order line, I need to create a transaction FOR it.
538: OZF_RESALE_COMMON_PVT.Create_Sales_Transaction (
539: p_api_version => 1.0
540: ,p_init_msg_list => FND_API.G_FALSE
541: ,p_commit => FND_API.G_FALSE
542: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
576: -- Bug 4380203 Fixing (-)
577: ELSE
578: l_line_id := l_line_int_rec.duplicated_line_id;
579: END IF;
580: ELSIF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
581: l_line_id := l_line_int_rec.duplicated_line_id;
582: END IF;
583: IF OZF_DEBUG_LOW_ON THEN
584: OZF_UTILITY_PVT.debug_message('line_id is '|| l_line_id);
583: IF OZF_DEBUG_LOW_ON THEN
584: OZF_UTILITY_PVT.debug_message('line_id is '|| l_line_id);
585: END IF;
586:
587: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED OR
588: l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
589:
590: -- only create mapping for the lines that are processed or
591: -- duplicated, yet the adjustment is new then
584: OZF_UTILITY_PVT.debug_message('line_id is '|| l_line_id);
585: END IF;
586:
587: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED OR
588: l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
589:
590: -- only create mapping for the lines that are processed or
591: -- duplicated, yet the adjustment is new then
592: OZF_RESALE_COMMON_PVT.Insert_resale_line_mapping(
588: l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
589:
590: -- only create mapping for the lines that are processed or
591: -- duplicated, yet the adjustment is new then
592: OZF_RESALE_COMMON_PVT.Insert_resale_line_mapping(
593: p_api_version => 1
594: ,p_init_msg_list => FND_API.G_FALSE
595: ,p_commit => FND_API.G_FALSE
596: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
606: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
607: END IF;
608: END IF;
609:
610: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
611: l_line_int_rec.tracing_flag = 'F' THEN
612: OZF_RESALE_COMMON_PVT.Create_Utilization(
613: p_api_version => 1.0
614: ,p_init_msg_LIST => FND_API.G_FALSE
608: END IF;
609:
610: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
611: l_line_int_rec.tracing_flag = 'F' THEN
612: OZF_RESALE_COMMON_PVT.Create_Utilization(
613: p_api_version => 1.0
614: ,p_init_msg_LIST => FND_API.G_FALSE
615: ,p_commit => FND_API.G_FALSE
616: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
675:
676: -- IF anything is paid, UPDATE batch line status to CLOSEd for each OPEN and duplicated lines.
677: BEGIN
678: UPDATE ozf_resale_lines_int
679: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
680: WHERE resale_batch_id = p_resale_batch_id
681: AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED) ;
682:
683: -- UPDATE batch status to CLOSEd -- might change later.
677: BEGIN
678: UPDATE ozf_resale_lines_int
679: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
680: WHERE resale_batch_id = p_resale_batch_id
681: AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED) ;
682:
683: -- UPDATE batch status to CLOSEd -- might change later.
684: UPDATE ozf_resale_batches
685: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED,
681: AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED) ;
682:
683: -- UPDATE batch status to CLOSEd -- might change later.
684: UPDATE ozf_resale_batches
685: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED,
686: paid_amount = l_amount_claimed
687: WHERE resale_batch_id = p_resale_batch_id;
688: EXCEPTION
689: WHEN OTHERS THEN
695: BEGIN
696: -- UPDATE batch line status to CLOSED for duplicated and processed lines
697:
698: UPDATE ozf_resale_lines_int
699: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
700: WHERE resale_batch_id = p_resale_batch_id
701: AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED) ;
702:
703:
697:
698: UPDATE ozf_resale_lines_int
699: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
700: WHERE resale_batch_id = p_resale_batch_id
701: AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED) ;
702:
703:
704: -- UPDATE batch status to CLOSED
705: UPDATE ozf_resale_batches
702:
703:
704: -- UPDATE batch status to CLOSED
705: UPDATE ozf_resale_batches
706: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
707: WHERE resale_batch_id = p_resale_batch_id;
708: EXCEPTION
709: WHEN OTHERS THEN
710: ozf_utility_pvt.error_message('OZF_UPD_RESALE_WRG','TEXT',l_full_name||': END');
888: ORG_ID
889: ) SELECT
890: ozf_resale_logs_all_s.nextval,
891: resale_line_int_id,
892: OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
893: 'OZF_RESALE_PUR_PRICE_MISSING',
894: fnd_message.get_string('OZF','OZF_RESALE_PUR_PRICE_MISSING'),
895: 'PURCHASE_PRICE',
896: NULL,
1011: WHERE order_number = p_order_number
1012: AND sold_from_cust_account_id= p_id
1013: AND date_ordered = p_date
1014: AND status_code = 'OPEN'
1015: --AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
1016: AND duplicated_adjustment_id is NULL
1017: AND resale_batch_id = p_resale_id
1018: AND tracing_flag = 'F';
1019:
1167: l_dispute_code varchar2(30) := null;
1168: l_status_code varchar2(30);
1169: l_tolerance NUMBER;
1170:
1171: l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
1172: l_inventory_level_valid BOOLEAN;
1173:
1174:
1175: BEGIN
1199:
1200: IF l_all_order_set_tbl.exists(1) THEN
1201: For i in 1..l_all_order_set_tbl.LAST LOOP
1202:
1203: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_all_order_set_tbl(i).resale_line_int_id);
1204: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1205: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1206:
1207: IF OZF_DEBUG_LOW_ON THEN
1200: IF l_all_order_set_tbl.exists(1) THEN
1201: For i in 1..l_all_order_set_tbl.LAST LOOP
1202:
1203: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_all_order_set_tbl(i).resale_line_int_id);
1204: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1205: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1206:
1207: IF OZF_DEBUG_LOW_ON THEN
1208: ozf_utility_PVT.debug_message(l_full_name || ' checking int line ' || l_all_order_set_tbl(i).resale_line_int_id);
1201: For i in 1..l_all_order_set_tbl.LAST LOOP
1202:
1203: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_all_order_set_tbl(i).resale_line_int_id);
1204: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1205: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1206:
1207: IF OZF_DEBUG_LOW_ON THEN
1208: ozf_utility_PVT.debug_message(l_full_name || ' checking int line ' || l_all_order_set_tbl(i).resale_line_int_id);
1209: ozf_utility_PVT.debug_message(l_full_name || ' inventory tracking ' || g_inventory_tracking);
1226: IF OZF_DEBUG_LOW_ON THEN
1227: ozf_utility_PVT.debug_message(l_full_name || ' inventory checking not passed!!!');
1228: END IF;
1229:
1230: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1231: p_id_value => l_all_order_set_tbl(i).resale_line_int_id,
1232: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1233: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1234: p_column_name => NULL,
1228: END IF;
1229:
1230: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1231: p_id_value => l_all_order_set_tbl(i).resale_line_int_id,
1232: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1233: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1234: p_column_name => NULL,
1235: p_column_value => NULL,
1236: x_return_status => l_return_status);
1317: -- Bug#9926288(-)
1318:
1319: -- bug # 6821886 fixed by ateotia (+)
1320: /*-- Bug 4616588 (+)
1321: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_tbl(J).resale_line_int_id);
1322: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1323: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1324:
1325: IF g_inventory_tracking = 'T' THEN
1318:
1319: -- bug # 6821886 fixed by ateotia (+)
1320: /*-- Bug 4616588 (+)
1321: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_tbl(J).resale_line_int_id);
1322: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1323: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1324:
1325: IF g_inventory_tracking = 'T' THEN
1326: -- Check inventory level first
1319: -- bug # 6821886 fixed by ateotia (+)
1320: /*-- Bug 4616588 (+)
1321: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_tbl(J).resale_line_int_id);
1322: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1323: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1324:
1325: IF g_inventory_tracking = 'T' THEN
1326: -- Check inventory level first
1327: OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
1335: ,x_msg_data => l_msg_data
1336: );
1337:
1338: IF NOT l_inventory_level_valid THEN
1339: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1340: p_id_value => l_order_set_tbl(J).resale_line_int_id,
1341: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1342: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1343: p_column_name => NULL,
1337:
1338: IF NOT l_inventory_level_valid THEN
1339: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1340: p_id_value => l_order_set_tbl(J).resale_line_int_id,
1341: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1342: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1343: p_column_name => NULL,
1344: p_column_value => NULL,
1345: x_return_status => l_return_status);
1389: END IF;
1390: IF l_header_start_date IS NULL OR
1391: l_request_type_code IS NULL OR
1392: l_header_currency_code is NULL THEN
1393: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1394: p_id_value => l_order_set_tbl(J).resale_line_int_id,
1395: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1396: p_error_code => 'OZF_RESALE_AGRM_WNG',
1397: p_column_name => NULL,
1391: l_request_type_code IS NULL OR
1392: l_header_currency_code is NULL THEN
1393: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1394: p_id_value => l_order_set_tbl(J).resale_line_int_id,
1395: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1396: p_error_code => 'OZF_RESALE_AGRM_WNG',
1397: p_column_name => NULL,
1398: p_column_value => NULL,
1399: x_return_status => l_return_status
1405: END IF;
1406:
1407: BEGIN
1408: UPDATE ozf_resale_lines_int
1409: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1410: dispute_code = 'OZF_RESALE_AGRM_WNG',
1411: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1412: response_type = 'CA',
1413: response_code = 'N'
1428:
1429: IF l_order_set_tbl(J).date_ordered < l_header_start_date OR
1430: (l_header_end_date is not null AND
1431: l_order_set_tbl(J).date_ordered > l_header_end_date) THEN
1432: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1433: p_id_value => l_order_set_tbl(J).resale_line_int_id,
1434: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1435: p_error_code => 'OZF_RESALE_AGRM_RANG_WNG',
1436: p_column_name => NULL,
1430: (l_header_end_date is not null AND
1431: l_order_set_tbl(J).date_ordered > l_header_end_date) THEN
1432: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1433: p_id_value => l_order_set_tbl(J).resale_line_int_id,
1434: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1435: p_error_code => 'OZF_RESALE_AGRM_RANG_WNG',
1436: p_column_name => NULL,
1437: p_column_value => NULL,
1438: x_return_status => l_return_status
1444: END IF;
1445:
1446: BEGIN
1447: UPDATE ozf_resale_lines_int
1448: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1449: dispute_code = 'OZF_RESALE_AGRM_RANG_WNG',
1450: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1451: response_type = 'CA',
1452: response_code = 'N'
1465: END IF;
1466: IF l_order_set_tbl(J).currency_code <> l_header_currency_code THEN
1467: BEGIN
1468: UPDATE ozf_resale_lines_int
1469: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1470: dispute_code = 'OZF_RESALE_AGRM_CURRENCY_WNG',
1471: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1472: response_type = 'CA',
1473: response_code = 'N'
1504: NULL;
1505: ELSE
1506: BEGIN
1507: UPDATE ozf_resale_lines_int
1508: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1509: dispute_code = 'OZF_RESALE_AGRM_END_CUST_WNG',
1510: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1511: response_type = 'CA',
1512: response_code = 'N'
1552: NULL;
1553: ELSE
1554: BEGIN
1555: UPDATE ozf_resale_lines_int
1556: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1557: dispute_code = 'OZF_RESALE_AGRM_RESELL_WNG',
1558: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1559: response_type = 'CA',
1560: response_code = 'N'
1570: END IF;
1571: ELSE
1572: BEGIN
1573: UPDATE ozf_resale_lines_int
1574: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1575: dispute_code = 'OZF_RESALE_AGRM_RESELL_WNG',
1576: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1577: response_type = 'CA',
1578: response_code = 'N'
1595: NULL;
1596: ELSE
1597: BEGIN
1598: UPDATE ozf_resale_lines_int
1599: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1600: dispute_code = 'OZF_RESALE_AGRM_RESELL_WNG',
1601: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1602: response_type = 'CA',
1603: response_code = 'N'
1620: OZF_UTILITY_PVT.debug_message('In Offer id null' );
1621: END IF;
1622: BEGIN
1623: UPDATE ozf_resale_lines_int
1624: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1625: dispute_code = 'OZF_RESALE_AGRM_OFF_NULL',
1626: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1627: response_type = 'CA',
1628: response_code = 'N'
1640: OZF_UTILITY_PVT.debug_message('In Off invoice offer' );
1641: END IF;
1642: BEGIN
1643: UPDATE ozf_resale_lines_int
1644: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1645: dispute_code = 'OZF_RESALE_AGRM_OFF_OFF_INV',
1646: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1647: response_type = 'CA',
1648: response_code = 'N'
1686: OZF_UTILITY_PVT.debug_message('request line approved max:' || l_request_line_apprv_max_qty );
1687: OZF_UTILITY_PVT.debug_message('request line approved min' || l_request_line_apprv_min_qty );
1688: END IF;
1689: IF l_request_line_id is NULL THEN
1690: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1691: p_id_value => l_order_set_tbl(J).resale_line_int_id,
1692: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1693: p_error_code => 'OZF_RESALE_AGRM_PROD_WNG',
1694: p_column_name => 'ITEM_NUMBER',
1688: END IF;
1689: IF l_request_line_id is NULL THEN
1690: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1691: p_id_value => l_order_set_tbl(J).resale_line_int_id,
1692: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1693: p_error_code => 'OZF_RESALE_AGRM_PROD_WNG',
1694: p_column_name => 'ITEM_NUMBER',
1695: p_column_value => l_order_set_tbl(J).item_number,
1696: x_return_status => l_return_status
1702: END IF;
1703:
1704: BEGIN
1705: UPDATE ozf_resale_lines_int
1706: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1707: dispute_code = 'OZF_RESALE_AGRM_LINE_WNG',
1708: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1709: response_type = 'CA',
1710: response_code = 'N'
1720:
1721:
1722: /*
1723: -- get current quantity and amount FROM tmp table
1724: OZF_RESALE_COMMON_PVT.get_available_quantity(
1725: p_api_version_number => 1.0,
1726: p_init_msg_list => FND_API.G_FALSE,
1727: p_commit => FND_API.G_FALSE,
1728: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1786: NULL, NULL);
1787: IF l_request_line_apprv_max_qty = -99999 THEN
1788: BEGIN
1789: UPDATE ozf_resale_lines_int
1790: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1791: dispute_code = 'OZF_SPP_NO_UOM_CONV_MAX',
1792: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1793: response_type = 'CA',
1794: response_code = 'N'
1813: NULL, NULL);
1814: IF l_request_line_apprv_min_qty = -99999 THEN
1815: BEGIN
1816: UPDATE ozf_resale_lines_int
1817: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1818: dispute_code = 'OZF_SPP_NO_UOM_CONV_MIN',
1819: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1820: response_type = 'CA',
1821: response_code = 'N'
1843: NULL, NULL);
1844: IF l_current_quantity = -99999 THEN
1845: BEGIN
1846: UPDATE ozf_resale_lines_int
1847: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1848: dispute_code = 'OZF_SPP_NO_UOM_CONV_CURR',
1849: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1850: response_type = 'CA',
1851: response_code = 'N'
1867: IF l_request_line_apprv_max_qty is not NULL AND
1868: l_current_quantity + l_used_quantity > l_request_line_apprv_max_qty THEN
1869: BEGIN
1870: UPDATE ozf_resale_lines_int
1871: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1872: dispute_code = 'OZF_RESALE_AGRM_QUN_GT_MAX',
1873: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1874: response_type = 'CA',
1875: response_code = 'N'
1886: IF l_request_line_apprv_min_qty is not NULL AND
1887: l_current_quantity + l_used_quantity < l_request_line_apprv_min_qty THEN
1888: BEGIN
1889: UPDATE ozf_resale_lines_int
1890: SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1891: dispute_code = 'OZF_RESALE_AGRM_QUN_LT_MIN',
1892: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1893: response_type = 'CA',
1894: response_code = 'N'
1921:
1922:
1923: -- allowed amount should be based on the request.
1924: -- Update the results of Special Pricing Calculation
1925: OZF_RESALE_COMMON_PVT.Update_Line_Calculations(
1926: p_resale_line_int_rec => l_order_set_tbl(J),
1927: p_unit_price => l_reqeust_line_item_price,
1928: p_line_quantity => l_current_quantity,
1929: p_allowed_amount => l_request_line_apprv_amt,
2005: sold_from_cust_account_id,
2006: date_ordered
2007: FROM ozf_resale_lines_int
2008: WHERE status_code = 'OPEN'
2009: --status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
2010: AND duplicated_adjustment_id IS NULL
2011: AND resale_batch_id = p_resale_batch_id
2012: ORDER BY date_ordered;
2013:
2010: AND duplicated_adjustment_id IS NULL
2011: AND resale_batch_id = p_resale_batch_id
2012: ORDER BY date_ordered;
2013:
2014: l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2015: l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2016: l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2017:
2018: l_report_start_date DATE;
2011: AND resale_batch_id = p_resale_batch_id
2012: ORDER BY date_ordered;
2013:
2014: l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2015: l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2016: l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2017:
2018: l_report_start_date DATE;
2019: l_report_end_date DATE;
2012: ORDER BY date_ordered;
2013:
2014: l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2015: l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2016: l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2017:
2018: l_report_start_date DATE;
2019: l_report_end_date DATE;
2020:
2044: -- Initialize API return status to sucess
2045: x_return_status := FND_API.G_RET_STS_SUCCESS;
2046:
2047: -- Delete the logs for the current batch
2048: OZF_RESALE_COMMON_PVT.Delete_Log(
2049: p_api_version => 1.0
2050: ,p_init_msg_list => FND_API.G_FALSE
2051: ,p_commit => FND_API.G_FALSE
2052: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2070:
2071:
2072: -- Bug 4616588 (+)
2073: -- Check whether there is a need to do inventory_verification
2074: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2075: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO g_inventory_tracking;
2076: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2077:
2078: -- populates the temp tables
2071:
2072: -- Bug 4616588 (+)
2073: -- Check whether there is a need to do inventory_verification
2074: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2075: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO g_inventory_tracking;
2076: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2077:
2078: -- populates the temp tables
2079: IF g_inventory_tracking = 'T' THEN
2072: -- Bug 4616588 (+)
2073: -- Check whether there is a need to do inventory_verification
2074: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2075: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO g_inventory_tracking;
2076: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2077:
2078: -- populates the temp tables
2079: IF g_inventory_tracking = 'T' THEN
2080: OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
2120:
2121: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2122: BEGIN
2123: update ozf_resale_lines_int
2124: set status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
2125: dispute_code = 'OZF_PROC_PRIC_RESLT_ERR',
2126: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
2127: response_type = 'CA',
2128: response_code = 'N'
2125: dispute_code = 'OZF_PROC_PRIC_RESLT_ERR',
2126: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
2127: response_type = 'CA',
2128: response_code = 'N'
2129: where status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
2130: and order_number = l_order_num_tbl(i)
2131: and sold_from_cust_account_id =l_cust_account_id_tbl(i)
2132: and date_ordered = l_order_date_tbl(i)
2133: and resale_batch_id = p_resale_batch_id;
2144: END LOOP; -- END LOOP for l_order_num_tbl
2145: END IF;
2146:
2147: -- Update Chargeback header with processing detail
2148: OZF_RESALE_COMMON_PVT.Update_Batch_Calculations (
2149: p_api_version => 1.0
2150: ,p_init_msg_list => FND_API.G_FALSE
2151: ,p_commit => FND_API.G_FALSE
2152: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL