[Home] [Help]
60: l_count NUMBER;
61: TYPE id_type IS RECORD (id NUMBER);
62: TYPE id_tbl_type IS TABLE OF id_type INDEX BY BINARY_INTEGER;
63:
64: l_line_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
65: l_valid_line_id_tbl id_tbl_type;
66: i NUMBER;
67: l_chargeback_fund_id NUMBER;
68: l_header_id NUMBER;
81: l_inventory_level_valid BOOLEAN;
82: l_sales_transaction_id NUMBER;
83: l_currency_code VARCHAR2(15);
84:
85: l_dup_header_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
86: --
87: CURSOR batch_info_csr (p_id IN NUMBER) IS
88: SELECT status_code,
89: batch_type,
101: CURSOR open_line_count_csr (p_id IN NUMBER) IS
102: SELECT count(1)
103: FROM ozf_resale_lines_int
104: WHERE resale_batch_id = p_id
105: AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
106:
107: CURSOR valid_line_id_csr(p_id IN NUMBER,
108: p_order_number IN VARCHAR2,
109: p_cust_id IN NUMBER,
114: AND order_number = p_order_number
115: AND sold_from_cust_account_id = p_cust_id
116: AND date_ordered = p_date
117: AND status_code = 'PROCESSED';
118: -- AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
119: -- AND duplicated_adjustment_id <> -1;
120:
121: CURSOR dup_header_id_csr( p_id IN NUMBER
122: , p_order_number IN VARCHAR2
142: date_ordered
143: FROM ozf_resale_lines_int
144: WHERE resale_batch_id = p_id
145: AND status_code = 'PROCESSED'
146: -- AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
147: -- AND duplicated_adjustment_id <> -1
148: ORDER BY date_ordered;
149:
150: TYPE order_num_tbl_type IS TABLE of batch_order_num_csr%ROWTYPE
167: FROM ozf_sys_parameters;
168: -- BUG 4992408 (-)
169:
170: l_end_cust_relation_flag VARCHAR2(30);
171: l_new_party_rec OZF_RESALE_COMMON_PVT.party_rec_type;
172: l_partner_claim_num VARCHAR2(30);
173:
174: error_no_rollback EXCEPTION;
175:
255: CLOSE batch_info_csr;
256:
257: IF OZF_DEBUG_LOW_ON THEN
258: OZF_UTILITY_PVT.debug_message('status ' ||l_batch_status);
259: OZF_UTILITY_PVT.debug_message('compare to ' ||OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT);
260: END IF;
261:
262: -- --------------------------------------------------
263: -- Stop Initiate_Payment if
264: -- 1. Batch Status is not in Pending Payment
265: -- 2. There are open line(s) exists in the batch
266: -- --------------------------------------------------
267:
268: IF l_batch_status <> OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT THEN
269: -- Only DISPUTED AND PROCESSED batch can be paid.
270: OZF_UTILITY_PVT.error_message('OZF_RESALE_WRONG_STAUS_TO_PAY');
271: RAISE FND_API.g_exc_error;
272: END IF;
281: RAISE FND_API.g_exc_error;
282: END IF;
283:
284:
285: IF l_batch_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT THEN
286: -- Get the budget where accruals are posted
287: l_chargeback_fund_id := FND_PROFILE.value('OZF_CHARGEBACK_BUDGET');
288: --
289: IF l_chargeback_fund_id IS NULL THEN
288: --
289: IF l_chargeback_fund_id IS NULL THEN
290: --OZF_UTILITY_PVT.error_message('OZF_CHBK_BUDGET_NULL');
291:
292: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
293: p_id_value => p_resale_batch_id
294: ,p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_BATCH
295: ,p_error_code => 'OZF_CHBK_BUDGET_NULL'
296: ,p_column_name => NULL
290: --OZF_UTILITY_PVT.error_message('OZF_CHBK_BUDGET_NULL');
291:
292: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
293: p_id_value => p_resale_batch_id
294: ,p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_BATCH
295: ,p_error_code => 'OZF_CHBK_BUDGET_NULL'
296: ,p_column_name => NULL
297: ,p_column_value => NULL
298: ,x_return_status => l_return_status
303: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304: END IF;
305:
306: UPDATE ozf_resale_batches_all
307: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
308: WHERE resale_batch_id = p_resale_batch_id;
309:
310: RAISE error_no_rollback;
311: --RAISE FND_API.g_exc_error;
315: OZF_UTILITY_PVT.debug_message('Chargeback Budget: ' || l_chargeback_fund_id);
316: END IF;
317:
318: -- Check whether there is a need to do inventory_verification
319: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
320: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
321: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
322:
323: IF l_inventory_tracking = 'T' THEN
316: END IF;
317:
318: -- Check whether there is a need to do inventory_verification
319: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
320: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
321: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
322:
323: IF l_inventory_tracking = 'T' THEN
324: OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
317:
318: -- Check whether there is a need to do inventory_verification
319: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
320: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
321: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
322:
323: IF l_inventory_tracking = 'T' THEN
324: OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
325: p_api_version => 1.0
364: l_new_party_rec.postal_Code := l_orig_billto_cust_tbl(i).bill_to_postal_code;
365: l_new_party_rec.country := l_orig_billto_cust_tbl(i).bill_to_country;
366: l_new_party_rec.site_Use_Code := 'BILL_TO';
367:
368: OZF_RESALE_COMMON_PVT.Create_Party(
369: p_api_version => 1.0
370: ,p_init_msg_list => FND_API.G_FALSE
371: ,p_commit => FND_API.G_FALSE
372: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
419: l_new_party_rec.postal_code := l_orig_end_cust_tbl(i).end_cust_postal_code;
420: l_new_party_rec.country := l_orig_end_cust_tbl(i).end_cust_country;
421: l_new_party_rec.site_use_code := l_orig_end_cust_tbl(i).end_cust_site_use_code;
422:
423: OZF_RESALE_COMMON_PVT.Create_Party
424: ( p_api_version => 1.0
425: ,p_init_msg_list => FND_API.G_FALSE
426: ,p_commit => FND_API.G_FALSE
427: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
500: -- There is something wrong with this order. dispute all the orders
501: -- and move to the next one.
502: -- JXWU move update to common pvt
503: UPDATE ozf_resale_lines_int_all
504: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
505: dispute_code = 'OZF_RESALE_MULTI_HEADER',
506: followup_action_code = 'C',
507: response_type = 'CA',
508: response_code = 'N'
509: WHERE resale_batch_id = p_resale_batch_id
510: AND order_number = l_order_num_tbl(k).order_number
511: AND sold_from_cust_account_id = l_order_num_tbl(k).sold_from_cust_account_id
512: AND date_ordered = l_order_num_tbl(k).date_ordered
513: AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
514: --AND duplicated_adjustment_id <> -1;
515:
516: GOTO END_ORDER_HEADER;
517: ELSE
551: IF OZF_DEBUG_LOW_ON THEN
552: OZF_UTILITY_PVT.debug_message('Current line_int_id:' || l_valid_line_id_tbl(j).id);
553: END IF;
554:
555: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
556: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
557: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
558:
559: -- Need to check against inventory
552: OZF_UTILITY_PVT.debug_message('Current line_int_id:' || l_valid_line_id_tbl(j).id);
553: END IF;
554:
555: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
556: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
557: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
558:
559: -- Need to check against inventory
560: -- Only need to check inventory for non-duplicate resale lines
553: END IF;
554:
555: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
556: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
557: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
558:
559: -- Need to check against inventory
560: -- Only need to check inventory for non-duplicate resale lines
561: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
557: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
558:
559: -- Need to check against inventory
560: -- Only need to check inventory for non-duplicate resale lines
561: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
562: ( l_line_int_rec.duplicated_adjustment_id IS NULL
563: OR l_line_int_rec.duplicated_adjustment_id = -1 ) AND
564: l_inventory_tracking = 'T' THEN
565: -- Check inventory level first
578: OZF_UTILITY_PVT.debug_message('Did NOT pass inventory checking');
579: END IF;
580: -- Here turn thIS line to DISPUTED AND create a dISput code FOR it.
581: UPDATE ozf_resale_lines_int
582: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
583: dispute_code = 'OZF_LT_INVT',
584: followup_action_code = 'C',
585: response_type = 'CA',
586: response_code = 'N'
587: WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
588:
589: -- SET Batch as DISPUTED
590: UPDATE ozf_resale_batches
591: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
592: WHERE resale_batch_id = l_line_int_rec.resale_batch_id;
593:
594: GOTO END_ORDER_LINE;
595: END IF;
601: -- see jxwu header_fix.
602: IF j = 1 THEN
603: -- Determin header id
604: IF l_create_order_header THEN
605: OZF_RESALE_COMMON_PVT.Insert_Resale_Header(
606: p_api_version => 1
607: ,p_init_msg_list => FND_API.G_FALSE
608: ,p_commit => FND_API.G_FALSE
609: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
627: IF OZF_DEBUG_LOW_ON THEN
628: OZF_UTILITY_PVT.debug_message('header_id is '|| l_header_id);
629: END IF;
630:
631: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED THEN
632: IF (l_line_int_rec.duplicated_line_id IS NULL) OR
633: (l_line_int_rec.duplicated_line_id IS NOT NULL AND
634: l_line_int_rec.duplicated_adjustment_id = -1) THEN
635: -- We need to create party id for bill_to and end customer if
635: -- We need to create party id for bill_to and end customer if
636: -- users wants us to do it.
637:
638: -- No problem so far. Insert INTO batch_lines TABLE
639: OZF_RESALE_COMMON_PVT.Insert_Resale_Line(
640: p_api_version => 1
641: ,p_init_msg_list => FND_API.G_FALSE
642: ,p_commit => FND_API.G_FALSE
643: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
654: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
655: END IF;
656:
657: -- FOR processed order line, I need to create a transaction FOR it.
658: OZF_RESALE_COMMON_PVT.Create_Sales_Transaction (
659: p_api_version => 1.0
660: ,p_init_msg_list => FND_API.G_FALSE
661: ,p_commit => FND_API.G_FALSE
662: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
700: ELSE
701: l_line_id := l_line_int_rec.duplicated_line_id;
702: END IF;
703:
704: ELSIF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
705: l_line_id := l_line_int_rec.duplicated_line_id;
706: END IF;
707:
708: IF OZF_DEBUG_LOW_ON THEN
710: END IF;
711:
712: IF l_line_int_rec.status_code = 'PROCESSED' THEN
713: -- Create Resale Line Mappings only for Processed Lines
714: -- l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
715: -- -- only create mapping FOR the lines that are processed or
716: -- -- duplicated, yet the adjustment IS new then
717: OZF_RESALE_COMMON_PVT.Insert_Resale_Line_Mapping(
718: p_api_version => 1
713: -- Create Resale Line Mappings only for Processed Lines
714: -- l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
715: -- -- only create mapping FOR the lines that are processed or
716: -- -- duplicated, yet the adjustment IS new then
717: OZF_RESALE_COMMON_PVT.Insert_Resale_Line_Mapping(
718: p_api_version => 1
719: ,p_init_msg_list => FND_API.G_FALSE
720: ,p_commit => FND_API.G_FALSE
721: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
731: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732: END IF;
733: END IF;
734:
735: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
736: l_line_int_rec.tracing_flag = 'F' THEN
737: --
738: OZF_RESALE_COMMON_PVT.Create_Utilization(
739: p_api_version => 1.0
734:
735: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
736: l_line_int_rec.tracing_flag = 'F' THEN
737: --
738: OZF_RESALE_COMMON_PVT.Create_Utilization(
739: p_api_version => 1.0
740: ,p_init_msg_LIST => FND_API.G_FALSE
741: ,p_commit => FND_API.G_FALSE
742: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
764: NULL;
765: END LOOP; -- END LOOP FOR the batch FOR C
766: END IF; -- END order_num EXISTS IF B
767:
768: OZF_RESALE_COMMON_PVT.Update_Batch_Calculations (
769: p_api_version => 1.0
770: ,p_init_msg_list => FND_API.G_FALSE
771: ,p_commit => FND_API.G_FALSE
772: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
782: END IF;
783:
784: -- After all the processes are done, call claim autopay
785: l_claim_rec.cust_account_id := l_partner_cust_account_id;
786: l_claim_rec.source_object_class := OZF_RESALE_COMMON_PVT.G_BATCH_OBJECT_CLASS;
787: l_claim_rec.source_object_id := p_resale_batch_id;
788: l_claim_rec.source_object_number := l_batch_number;
789: l_claim_rec.batch_id := p_resale_batch_id;
790: l_claim_rec.batch_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
786: l_claim_rec.source_object_class := OZF_RESALE_COMMON_PVT.G_BATCH_OBJECT_CLASS;
787: l_claim_rec.source_object_id := p_resale_batch_id;
788: l_claim_rec.source_object_number := l_batch_number;
789: l_claim_rec.batch_id := p_resale_batch_id;
790: l_claim_rec.batch_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
791: l_claim_rec.customer_ref_number := l_partner_claim_num;
792: l_claim_rec.currency_code := l_currency_code;
793: l_funds_util_flt.cust_account_id := l_partner_cust_account_id;
794: l_funds_util_flt.reference_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
790: l_claim_rec.batch_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
791: l_claim_rec.customer_ref_number := l_partner_claim_num;
792: l_claim_rec.currency_code := l_currency_code;
793: l_funds_util_flt.cust_account_id := l_partner_cust_account_id;
794: l_funds_util_flt.reference_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
795: l_funds_util_flt.reference_id := p_resale_batch_id;
796: l_funds_util_flt.utilization_type:= G_CHBK_UTIL_TYPE;
797:
798: OZF_Claim_Accrual_PVT.Pay_Claim_for_Accruals(
825:
826: BEGIN
827: -- BUG 4395931 (+)
828: UPDATE ozf_resale_lines_int
829: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
830: WHERE resale_batch_id = p_resale_batch_id
831: AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
832:
833: /*
827: -- BUG 4395931 (+)
828: UPDATE ozf_resale_lines_int
829: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
830: WHERE resale_batch_id = p_resale_batch_id
831: AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
832:
833: /*
834: -- SLKRISHN change to common procedure
835: UPDATE ozf_resale_lines_int
832:
833: /*
834: -- SLKRISHN change to common procedure
835: UPDATE ozf_resale_lines_int
836: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
837: WHERE resale_batch_id = p_resale_batch_id
838: AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
839: --AND duplicated_adjustment_id <> -1;
840: */
834: -- SLKRISHN change to common procedure
835: UPDATE ozf_resale_lines_int
836: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
837: WHERE resale_batch_id = p_resale_batch_id
838: AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
839: --AND duplicated_adjustment_id <> -1;
840: */
841: -- BUG 4395931 (-)
842:
841: -- BUG 4395931 (-)
842:
843: -- UPDATE batch status to closed -- might change later.
844: UPDATE ozf_resale_batches
845: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED,
846: paid_amount = l_amount_claimed
847: WHERE resale_batch_id = p_resale_batch_id;
848:
849: EXCEPTION
943: l_api_name CONSTANT VARCHAR2(30) := 'Validate_Order_Record';
944: l_api_version CONSTANT NUMBER := 1.0;
945: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
946: --
947: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
948: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
949: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
950: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
951: l_return_status VARCHAR2(1);
944: l_api_version CONSTANT NUMBER := 1.0;
945: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
946: --
947: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
948: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
949: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
950: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
951: l_return_status VARCHAR2(1);
952: --
945: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
946: --
947: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
948: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
949: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
950: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
951: l_return_status VARCHAR2(1);
952: --
953: CURSOR null_columns_csr (p_batch_id in number) IS
946: --
947: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
948: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
949: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
950: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
951: l_return_status VARCHAR2(1);
952: --
953: CURSOR null_columns_csr (p_batch_id in number) IS
954: SELECT resale_line_int_id,
956: 'PURCHASE_PRICE',
957: NULL
958: FROM ozf_resale_lines_int_all
959: WHERE resale_batch_id = p_batch_id
960: AND status_code IN (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED)
961: AND tracing_flag = 'F'
962: AND purchase_price IS NULL;
963:
964: --
1004: CLOSE null_columns_csr;
1005: --
1006:
1007: -- log disputed lines
1008: OZF_RESALE_COMMON_PVT.Bulk_Insert_Resale_Log (
1009: p_id_value => l_id_tbl,
1010: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1011: p_error_code => l_err_tbl,
1012: p_column_name => l_col_tbl,
1006:
1007: -- log disputed lines
1008: OZF_RESALE_COMMON_PVT.Bulk_Insert_Resale_Log (
1009: p_id_value => l_id_tbl,
1010: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1011: p_error_code => l_err_tbl,
1012: p_column_name => l_col_tbl,
1013: p_column_value => l_val_tbl,
1014: p_batch_id => p_resale_batch_id, --bug # 5997978 fixed
1021: RAISE FND_API.g_exc_unexpected_error;
1022: END IF;
1023: --
1024:
1025: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
1026: p_batch_id => p_resale_batch_id,
1027: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
1028: x_return_status => l_return_status
1029: );
1023: --
1024:
1025: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
1026: p_batch_id => p_resale_batch_id,
1027: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
1028: x_return_status => l_return_status
1029: );
1030: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1031: RAISE FND_API.g_exc_error;
1112: l_cust_account_id NUMBER;
1113: l_date_ordered DATE;
1114: l_has_error BOOLEAN:=false;
1115: l_log_id NUMBER;
1116: l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
1117: l_header_id NUMBER;
1118: l_line_id NUMBER;
1119: l_object_version_number NUMBER :=1;
1120:
1128: l_IS_error BOOLEAN:=FALSE;
1129: l_inventory_level_valid BOOLEAN;
1130: l_allowed_amount NUMBER;
1131:
1132: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1133: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1134: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1135: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1136: --
1129: l_inventory_level_valid BOOLEAN;
1130: l_allowed_amount NUMBER;
1131:
1132: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1133: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1134: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1135: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1136: --
1137: CURSOR order_identifiers_csr (p_id IN NUMBER) IS
1130: l_allowed_amount NUMBER;
1131:
1132: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1133: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1134: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1135: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1136: --
1137: CURSOR order_identifiers_csr (p_id IN NUMBER) IS
1138: SELECT order_number
1131:
1132: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1133: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1134: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1135: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1136: --
1137: CURSOR order_identifiers_csr (p_id IN NUMBER) IS
1138: SELECT order_number
1139: , sold_from_cust_account_id
1152: p_date_ordered DATE,
1153: p_cust_account_id NUMBER )IS
1154: SELECT resale_line_int_id, 'OZF_RESALE_PRICE_ERROR', NULL, NULL
1155: FROM ozf_resale_lines_int
1156: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1157: AND order_number = l_order_number
1158: AND date_ordered = l_date_ordered
1159: AND sold_from_cust_account_id =l_cust_account_id
1160: AND dispute_code is null
1198: OZF_UTILITY_PVT.debug_message('line '||p_line_tbl(i).chargeback_int_id || ' has pricing error' );
1199: END IF;
1200: BEGIN
1201: update ozf_resale_lines_int
1202: set status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1203: dispute_code = 'OZF_RESALE_PRICE_ERROR',
1204: followup_action_code = 'C',
1205: response_type = 'CA',
1206: response_code = 'N'
1210: ozf_utility_pvt.error_message('OZF_UPD_RESALE_INT_WRG');
1211: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1212: END;
1213:
1214: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1215: p_id_value => p_line_tbl(i).chargeback_int_id,
1216: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1217: p_error_code => p_line_tbl(i).pricing_status_code,
1218: p_error_message => p_line_tbl(i).pricing_status_text,
1212: END;
1213:
1214: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1215: p_id_value => p_line_tbl(i).chargeback_int_id,
1216: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1217: p_error_code => p_line_tbl(i).pricing_status_code,
1218: p_error_message => p_line_tbl(i).pricing_status_text,
1219: p_column_name => NULL,
1220: p_column_value => NULL,
1225: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1226: RAISE FND_API.g_exc_unexpected_error;
1227: END IF;
1228:
1229: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1230: p_id_value => p_line_tbl(i).chargeback_int_id,
1231: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1232: p_error_code => 'OZF_RESALE_PRICE_ERROR',
1233: p_column_name => NULL,
1227: END IF;
1228:
1229: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1230: p_id_value => p_line_tbl(i).chargeback_int_id,
1231: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1232: p_error_code => 'OZF_RESALE_PRICE_ERROR',
1233: p_column_name => NULL,
1234: p_column_value => NULL,
1235: x_return_status => l_return_status);
1263: FETCH open_lines_csr BULK COLLECT INTO l_id_tbl, l_err_tbl, l_col_tbl, l_val_tbl;
1264: CLOSE open_lines_csr;
1265:
1266: -- log disputed lines
1267: OZF_RESALE_COMMON_PVT.Bulk_Insert_Resale_Log (
1268: p_id_value => l_id_tbl,
1269: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1270: p_error_code => l_err_tbl,
1271: p_column_name => l_col_tbl,
1265:
1266: -- log disputed lines
1267: OZF_RESALE_COMMON_PVT.Bulk_Insert_Resale_Log (
1268: p_id_value => l_id_tbl,
1269: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1270: p_error_code => l_err_tbl,
1271: p_column_name => l_col_tbl,
1272: p_column_value => l_val_tbl,
1273: p_batch_id => p_resale_batch_id, --bug # 5997978 fixed
1280: END IF;
1281:
1282: BEGIN
1283: UPDATE ozf_resale_lines_int_all
1284: SET status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1285: dispute_code = 'OZF_RESALE_PRICE_ERROR',
1286: followup_action_code = 'C',
1287: response_type = 'CA',
1288: response_code = 'N'
1285: dispute_code = 'OZF_RESALE_PRICE_ERROR',
1286: followup_action_code = 'C',
1287: response_type = 'CA',
1288: response_code = 'N'
1289: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1290: AND order_number = l_order_number
1291: AND date_ordered = l_date_ordered
1292: AND sold_from_cust_account_id =l_cust_account_id
1293: AND dispute_code is null
1308: OPEN func_currency_cd_csr;
1309: FETCH func_currency_cd_csr INTO l_func_currency_code;
1310: CLOSE func_currency_cd_csr;
1311:
1312: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1313: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_default_exchange_type;
1314: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1315:
1316: -- FOR each chargeback of the line, we will UPDATE the line
1309: FETCH func_currency_cd_csr INTO l_func_currency_code;
1310: CLOSE func_currency_cd_csr;
1311:
1312: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1313: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_default_exchange_type;
1314: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1315:
1316: -- FOR each chargeback of the line, we will UPDATE the line
1317: FOR i in 1..p_line_tbl.LAST LOOP
1310: CLOSE func_currency_cd_csr;
1311:
1312: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1313: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_default_exchange_type;
1314: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1315:
1316: -- FOR each chargeback of the line, we will UPDATE the line
1317: FOR i in 1..p_line_tbl.LAST LOOP
1318: IF p_line_tbl(i).line_type_code = 'LINE' THEN
1315:
1316: -- FOR each chargeback of the line, we will UPDATE the line
1317: FOR i in 1..p_line_tbl.LAST LOOP
1318: IF p_line_tbl(i).line_type_code = 'LINE' THEN
1319: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(p_line_tbl(i).chargeback_int_id);
1320: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1321: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1322:
1323: -- Check inventory level FOR thIS order.
1316: -- FOR each chargeback of the line, we will UPDATE the line
1317: FOR i in 1..p_line_tbl.LAST LOOP
1318: IF p_line_tbl(i).line_type_code = 'LINE' THEN
1319: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(p_line_tbl(i).chargeback_int_id);
1320: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1321: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1322:
1323: -- Check inventory level FOR thIS order.
1324: -- If inventory level IS lower than the asked, then there IS no need to
1317: FOR i in 1..p_line_tbl.LAST LOOP
1318: IF p_line_tbl(i).line_type_code = 'LINE' THEN
1319: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(p_line_tbl(i).chargeback_int_id);
1320: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1321: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1322:
1323: -- Check inventory level FOR thIS order.
1324: -- If inventory level IS lower than the asked, then there IS no need to
1325: -- continue processing
1347: IF OZF_DEBUG_LOW_ON THEN
1348: OZF_UTILITY_PVT.debug_message('Did not pass inventory checking');
1349: END IF;
1350: --
1351: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1352: p_id_value => p_line_tbl(i).chargeback_int_id,
1353: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1354: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1355: p_column_name => NULL,
1349: END IF;
1350: --
1351: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1352: p_id_value => p_line_tbl(i).chargeback_int_id,
1353: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1354: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1355: p_column_name => NULL,
1356: p_column_value => NULL,
1357: x_return_status => l_return_status);
1364: --
1365: -- SET Batch as DISPUTED
1366: -- SLKRISHN change to common procedure
1367: UPDATE ozf_resale_lines_int
1368: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1369: dispute_code = 'OZF_LT_INVT',
1370: followup_action_code = 'C',
1371: response_type = 'CA',
1372: response_code = 'N'
1372: response_code = 'N'
1373: WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
1374:
1375: UPDATE ozf_resale_batches
1376: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
1377: WHERE resale_batch_id = l_resale_int_rec.resale_batch_id;
1378: --
1379: GOTO END_LOOP3;
1380: ELSE
1499: OZF_UTILITY_PVT.debug_message('Allowed amount:' || l_allowed_amount);
1500: END IF;
1501:
1502: -- Update the results of Chargeback Calculation
1503: OZF_RESALE_COMMON_PVT.Update_Line_Calculations(
1504: p_resale_line_int_rec => l_resale_int_rec,
1505: p_unit_price => p_line_tbl(i).unit_price,
1506: p_line_quantity => p_line_tbl(i).line_quantity,
1507: p_allowed_amount => l_allowed_amount,
1518: NULL;
1519: END LOOP; -- END LOOP through lines
1520:
1521: -- Dispute all the lines from this order
1522: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
1523: p_batch_id => p_resale_batch_id,
1524: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
1525: x_return_status => l_return_status
1526: );
1520:
1521: -- Dispute all the lines from this order
1522: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
1523: p_batch_id => p_resale_batch_id,
1524: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
1525: x_return_status => l_return_status
1526: );
1527: --
1528: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1609: WHERE order_number = p_order_number
1610: AND sold_from_cust_account_id= p_id
1611: AND date_ordered = p_date
1612: AND status_code = 'OPEN'
1613: -- AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
1614: -- AND duplicated_adjustment_id IS NULL
1615: -- bug # 6822541 fixed by ateotia (+)
1616: AND resale_batch_id = p_batch_id;
1617: --AND tracing_flag = 'F';
1639:
1640: l_order_set_trc_tbl order_set_trc_tbl;
1641: l_inventory_tracking VARCHAR2(2);
1642: l_inventory_level_valid BOOLEAN;
1643: l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
1644: -- 6511302 (-)
1645:
1646: --
1647: BEGIN
1655: -- Initialize API return status to sucess
1656: x_return_status := FND_API.G_RET_STS_SUCCESS;
1657:
1658: -- bug 6511302 (+) validate inventory for tracing lines
1659: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1660: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
1661: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1662:
1663: IF l_inventory_tracking = 'T' THEN -- validate inventory for tracing lines. order lines will be validated during pricing phase
1656: x_return_status := FND_API.G_RET_STS_SUCCESS;
1657:
1658: -- bug 6511302 (+) validate inventory for tracing lines
1659: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1660: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
1661: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1662:
1663: IF l_inventory_tracking = 'T' THEN -- validate inventory for tracing lines. order lines will be validated during pricing phase
1664: OPEN c_order_set_trc(p_order_number,
1657:
1658: -- bug 6511302 (+) validate inventory for tracing lines
1659: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1660: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
1661: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1662:
1663: IF l_inventory_tracking = 'T' THEN -- validate inventory for tracing lines. order lines will be validated during pricing phase
1664: OPEN c_order_set_trc(p_order_number,
1665: p_sold_from_cust_acct_id,
1670:
1671: IF l_order_set_trc_tbl.COUNT > 0 THEN
1672: FOR i IN 1..l_order_set_trc_tbl.COUNT LOOP
1673: OZF_UTILITY_PVT.debug_message(l_full_name || ' Validating tracing line ' || l_order_set_trc_tbl(i).resale_line_int_id);
1674: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_trc_tbl(i).resale_line_int_id);
1675: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1676: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1677:
1678: IF l_resale_int_rec.duplicated_adjustment_id IS NULL OR l_resale_int_rec.duplicated_adjustment_id = -1 THEN
1671: IF l_order_set_trc_tbl.COUNT > 0 THEN
1672: FOR i IN 1..l_order_set_trc_tbl.COUNT LOOP
1673: OZF_UTILITY_PVT.debug_message(l_full_name || ' Validating tracing line ' || l_order_set_trc_tbl(i).resale_line_int_id);
1674: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_trc_tbl(i).resale_line_int_id);
1675: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1676: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1677:
1678: IF l_resale_int_rec.duplicated_adjustment_id IS NULL OR l_resale_int_rec.duplicated_adjustment_id = -1 THEN
1679: -- Check inventory level first
1672: FOR i IN 1..l_order_set_trc_tbl.COUNT LOOP
1673: OZF_UTILITY_PVT.debug_message(l_full_name || ' Validating tracing line ' || l_order_set_trc_tbl(i).resale_line_int_id);
1674: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_trc_tbl(i).resale_line_int_id);
1675: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1676: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1677:
1678: IF l_resale_int_rec.duplicated_adjustment_id IS NULL OR l_resale_int_rec.duplicated_adjustment_id = -1 THEN
1679: -- Check inventory level first
1680: OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
1693: OZF_UTILITY_PVT.debug_message('Did NOT pass inventory checking');
1694: END IF;
1695:
1696: -- log
1697: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1698: p_id_value => l_order_set_trc_tbl(i).resale_line_int_id,
1699: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1700: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1701: p_column_name => NULL,
1695:
1696: -- log
1697: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1698: p_id_value => l_order_set_trc_tbl(i).resale_line_int_id,
1699: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1700: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1701: p_column_name => NULL,
1702: p_column_value => NULL,
1703: x_return_status => l_return_status);
1709: END IF;
1710:
1711: -- Set line to DISPUTED
1712: UPDATE ozf_resale_lines_int
1713: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1714: dispute_code = 'OZF_LT_INVT',
1715: followup_action_code = 'C',
1716: response_type = 'CA',
1717: response_code = 'N'
1718: WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
1719:
1720: -- SET Batch as DISPUTED
1721: UPDATE ozf_resale_batches
1722: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
1723: WHERE resale_batch_id = l_resale_int_rec.resale_batch_id;
1724: END IF;
1725: END IF;
1726: END LOOP;
1790: BEGIN
1791: -- SLKRISHN move to a common procedure
1792: DELETE FROM ozf_resale_logs
1793: WHERE resale_id = l_order_set_tbl(J).resale_line_int_id
1794: AND resale_id_type = OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE;
1795: EXCEPTION
1796: WHEN OTHERS THEN
1797: IF OZF_UNEXP_ERROR THEN
1798: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2096: SELECT DISTINCT order_number,
2097: sold_from_cust_account_id,
2098: date_ordered
2099: FROM ozf_resale_lines_int
2100: WHERE status_code = 'OPEN' --(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
2101: AND resale_batch_id = p_resale_batch_id
2102: --AND (duplicated_adjustment_id IS NULL OR
2103: -- duplicated_adjustment_id = -1 )
2104: ORDER BY date_ordered;
2102: --AND (duplicated_adjustment_id IS NULL OR
2103: -- duplicated_adjustment_id = -1 )
2104: ORDER BY date_ordered;
2105:
2106: l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2107: l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2108: l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2109:
2110: l_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
2103: -- duplicated_adjustment_id = -1 )
2104: ORDER BY date_ordered;
2105:
2106: l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2107: l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2108: l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2109:
2110: l_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
2111: l_ldets_tbl OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE;
2104: ORDER BY date_ordered;
2105:
2106: l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2107: l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2108: l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2109:
2110: l_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
2111: l_ldets_tbl OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE;
2112: l_related_lines_tbl OZF_ORDER_PRICE_PVT.RLTD_LINE_TBL_TYPE;
2150: x_return_status := FND_API.G_RET_STS_SUCCESS;
2151:
2152: -- Move Delete logs to resale_pre_process
2153: -- Delete the logs for the current batch
2154: OZF_RESALE_COMMON_PVT.Delete_Log(
2155: p_api_version => 1.0
2156: ,p_init_msg_list => FND_API.G_FALSE
2157: ,p_commit => FND_API.G_FALSE
2158: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2167: RAISE FND_API.g_exc_unexpected_error;
2168: END IF;
2169:
2170: -- Check whether there IS a need to do inventory_verification
2171: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2172: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
2173: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2174: OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': inventory flag = ' || l_inventory_tracking);
2175: -- populates the temp tables
2168: END IF;
2169:
2170: -- Check whether there IS a need to do inventory_verification
2171: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2172: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
2173: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2174: OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': inventory flag = ' || l_inventory_tracking);
2175: -- populates the temp tables
2176: IF l_inventory_tracking = 'T' THEN
2169:
2170: -- Check whether there IS a need to do inventory_verification
2171: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2172: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
2173: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2174: OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': inventory flag = ' || l_inventory_tracking);
2175: -- populates the temp tables
2176: IF l_inventory_tracking = 'T' THEN
2177: OPEN batch_info_csr(p_resale_batch_id);
2244:
2245: IF OZF_DEBUG_LOW_ON THEN
2246: OZF_UTILITY_PVT.debug_message('/*--- before get_order_price Insert resale log done ---*/');
2247: OZF_UTILITY_PVT.debug_message('/*--- chargeback int id:' || l_line_tbl(j).chargeback_int_id);
2248: OZF_UTILITY_PVT.debug_message('/*--- Message type: '||OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE);
2249: END IF;
2250:
2251: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2252: p_id_value => l_line_tbl(j).chargeback_int_id,
2247: OZF_UTILITY_PVT.debug_message('/*--- chargeback int id:' || l_line_tbl(j).chargeback_int_id);
2248: OZF_UTILITY_PVT.debug_message('/*--- Message type: '||OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE);
2249: END IF;
2250:
2251: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2252: p_id_value => l_line_tbl(j).chargeback_int_id,
2253: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
2254: p_error_code => 'OZF_GET_ORDER_PRIC_ERR',
2255: p_column_name => NULL,
2249: END IF;
2250:
2251: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2252: p_id_value => l_line_tbl(j).chargeback_int_id,
2253: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
2254: p_error_code => 'OZF_GET_ORDER_PRIC_ERR',
2255: p_column_name => NULL,
2256: p_column_value => NULL,
2257: x_return_status => l_return_status);
2267: OZF_UTILITY_PVT.debug_message('/*--- After get_order_price Insert resale log done ---*/');
2268: END IF;
2269:
2270: -- Dispute all the lines from this order
2271: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
2272: p_batch_id => p_resale_batch_id,
2273: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
2274: x_return_status => l_return_status
2275: );
2269:
2270: -- Dispute all the lines from this order
2271: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
2272: p_batch_id => p_resale_batch_id,
2273: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
2274: x_return_status => l_return_status
2275: );
2276: --
2277: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2308: -- insert error messages into error stack
2309: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2310: FOR j in 1..l_line_tbl.LAST
2311: LOOP
2312: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2313: p_id_value => l_line_tbl(j).chargeback_int_id,
2314: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
2315: p_error_code => 'OZF_PROC_PRIC_RESLT_ERR',
2316: p_column_name => NULL,
2310: FOR j in 1..l_line_tbl.LAST
2311: LOOP
2312: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2313: p_id_value => l_line_tbl(j).chargeback_int_id,
2314: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
2315: p_error_code => 'OZF_PROC_PRIC_RESLT_ERR',
2316: p_column_name => NULL,
2317: p_column_value => NULL,
2318: x_return_status => l_return_status);
2330: END IF;
2331:
2332: BEGIN
2333: UPDATE ozf_resale_lines_int
2334: SET status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
2335: dispute_code = 'OZF_PROC_PRIC_RESLT_ERR',
2336: followup_action_code = 'C',
2337: response_type = 'CA',
2338: response_code = 'N'
2335: dispute_code = 'OZF_PROC_PRIC_RESLT_ERR',
2336: followup_action_code = 'C',
2337: response_type = 'CA',
2338: response_code = 'N'
2339: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
2340: AND order_number = l_order_num_tbl(i)
2341: AND date_ordered = l_order_date_tbl(i)
2342: AND sold_from_cust_account_id =l_cust_account_id_tbl(i)
2343: AND dispute_code is null
2379: END IF;
2380: END IF;
2381:
2382: -- Update Chargeback header with processing detail
2383: OZF_RESALE_COMMON_PVT.Update_Batch_Calculations (
2384: p_api_version => 1.0
2385: ,p_init_msg_list => FND_API.G_FALSE
2386: ,p_commit => FND_API.G_FALSE
2387: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL