[Home] [Help]
83: l_report_start_date DATE;
84: l_report_end_date DATE;
85: l_batch_number VARCHAR2(30);
86: l_last_updated_by NUMBER(15);
87: x_fund_table_status VARCHAR2(30) := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
88: x_budget_table_status VARCHAR2(30) := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
89:
90: --
91: l_count NUMBER;
84: l_report_end_date DATE;
85: l_batch_number VARCHAR2(30);
86: l_last_updated_by NUMBER(15);
87: x_fund_table_status VARCHAR2(30) := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
88: x_budget_table_status VARCHAR2(30) := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
89:
90: --
91: l_count NUMBER;
92: TYPE id_type IS RECORD (id NUMBER);
91: l_count NUMBER;
92: TYPE id_type IS RECORD (id NUMBER);
93: TYPE id_tbl_type IS TABLE OF id_type INDEX BY BINARY_INTEGER;
94:
95: l_line_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
96: l_valid_line_id_tbl id_tbl_type;
97: i NUMBER;
98: l_chargeback_fund_id NUMBER;
99: l_header_id NUMBER;
112: l_inventory_level_valid BOOLEAN;
113: l_sales_transaction_id NUMBER;
114: l_currency_code VARCHAR2(15);
115:
116: l_dup_header_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
117: --
118:
119: -- For Bug#8867381
120: l_budget_amount_tc NUMBER;
165: CURSOR open_line_count_csr (p_id IN NUMBER) IS
166: SELECT count(1)
167: FROM ozf_resale_lines_int
168: WHERE resale_batch_id = p_id
169: AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
170:
171: CURSOR valid_line_id_csr(p_id IN NUMBER,
172: p_order_number IN VARCHAR2,
173: p_cust_id IN NUMBER,
178: AND order_number = p_order_number
179: AND sold_from_cust_account_id = p_cust_id
180: AND date_ordered = p_date
181: AND status_code = 'PROCESSED';
182: -- AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
183: -- AND duplicated_adjustment_id <> -1;
184:
185: CURSOR dup_header_id_csr( p_id IN NUMBER
186: , p_order_number IN VARCHAR2
206: date_ordered
207: FROM ozf_resale_lines_int
208: WHERE resale_batch_id = p_id
209: AND status_code = 'PROCESSED'
210: -- AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
211: -- AND duplicated_adjustment_id <> -1
212: ORDER BY date_ordered;
213:
214: TYPE order_num_tbl_type IS TABLE of batch_order_num_csr%ROWTYPE
236: error_no_rollback EXCEPTION;
237:
238: --Bug# 8489216 fixed by ateotia(+)
239: /*
240: l_new_party_rec OZF_RESALE_COMMON_PVT.party_rec_type;
241:
242: CURSOR csr_orig_billto_cust(cv_resale_batch_id IN NUMBER) IS
243: SELECT DISTINCT bill_to_party_name
244: , bill_to_address
367: -- MO_GLOBAL.set_policy_context( p_access_mode => 'S', p_org_id => l_org_id);
368:
369: IF OZF_DEBUG_LOW_ON THEN
370: OZF_UTILITY_PVT.debug_message('status ' ||l_batch_status);
371: OZF_UTILITY_PVT.debug_message('compare to ' ||OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT);
372: OZF_UTILITY_PVT.debug_message('Batch Type ' ||l_batch_type);
373: OZF_UTILITY_PVT.debug_message('Staging Tables ' ||l_use_fund_staging_tables);
374: END IF;
375:
378: -- 1. Batch Status is not in Pending Payment
379: -- 2. There are open line(s) exists in the batch
380: -- --------------------------------------------------
381:
382: IF l_batch_status <> OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT THEN
383: -- Only DISPUTED AND PROCESSED batch can be paid.
384: OZF_UTILITY_PVT.error_message('OZF_RESALE_WRONG_STAUS_TO_PAY');
385: RAISE FND_API.g_exc_error;
386: END IF;
395: RAISE FND_API.g_exc_error;
396: END IF;
397:
398:
399: IF l_batch_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT THEN
400: -- Get the budget where accruals are posted
401: l_chargeback_fund_id := FND_PROFILE.value('OZF_CHARGEBACK_BUDGET');
402: --
403: IF l_chargeback_fund_id IS NULL THEN
402: --
403: IF l_chargeback_fund_id IS NULL THEN
404: --OZF_UTILITY_PVT.error_message('OZF_CHBK_BUDGET_NULL');
405:
406: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
407: p_id_value => p_resale_batch_id
408: ,p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_BATCH
409: ,p_error_code => 'OZF_CHBK_BUDGET_NULL'
410: ,p_column_name => NULL
404: --OZF_UTILITY_PVT.error_message('OZF_CHBK_BUDGET_NULL');
405:
406: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
407: p_id_value => p_resale_batch_id
408: ,p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_BATCH
409: ,p_error_code => 'OZF_CHBK_BUDGET_NULL'
410: ,p_column_name => NULL
411: ,p_column_value => NULL
412: ,x_return_status => l_return_status
417: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
418: END IF;
419:
420: UPDATE ozf_resale_batches_all
421: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
422: WHERE resale_batch_id = p_resale_batch_id;
423:
424: RAISE error_no_rollback;
425: --RAISE FND_API.g_exc_error;
462: OZF_UTILITY_PVT.debug_message('Chargeback Budget: ' || l_chargeback_fund_id);
463: END IF;
464:
465: -- Check whether there is a need to do inventory_verification
466: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
467: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
468: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
469:
470: IF l_inventory_tracking = 'T' THEN
463: END IF;
464:
465: -- Check whether there is a need to do inventory_verification
466: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
467: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
468: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
469:
470: IF l_inventory_tracking = 'T' THEN
471: OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
464:
465: -- Check whether there is a need to do inventory_verification
466: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
467: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
468: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
469:
470: IF l_inventory_tracking = 'T' THEN
471: OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
472: p_api_version => 1.0
494:
495: IF l_end_cust_relation_flag = 'T' THEN
496: --Bug# 8489216 fixed by ateotia(+)
497: --Moved the logic of End Customer/Bill_To/Ship_To Party creation to Common Resale API.
498: OZF_RESALE_COMMON_PVT.Derive_Orig_Parties
499: ( p_api_version => 1.0
500: ,p_init_msg_list => FND_API.G_FALSE
501: ,p_commit => FND_API.G_FALSE
502: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
530: l_new_party_rec.postal_Code := l_orig_billto_cust_tbl(i).bill_to_postal_code;
531: l_new_party_rec.country := l_orig_billto_cust_tbl(i).bill_to_country;
532: l_new_party_rec.site_Use_Code := 'BILL_TO';
533:
534: OZF_RESALE_COMMON_PVT.Create_Party(
535: p_api_version => 1.0
536: ,p_init_msg_list => FND_API.G_FALSE
537: ,p_commit => FND_API.G_FALSE
538: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
585: l_new_party_rec.postal_code := l_orig_end_cust_tbl(i).end_cust_postal_code;
586: l_new_party_rec.country := l_orig_end_cust_tbl(i).end_cust_country;
587: l_new_party_rec.site_use_code := l_orig_end_cust_tbl(i).end_cust_site_use_code;
588:
589: OZF_RESALE_COMMON_PVT.Create_Party
590: ( p_api_version => 1.0
591: ,p_init_msg_list => FND_API.G_FALSE
592: ,p_commit => FND_API.G_FALSE
593: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
677: -- There is something wrong with this order. dispute all the orders
678: -- and move to the next one.
679: -- JXWU move update to common pvt
680: UPDATE ozf_resale_lines_int_all
681: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
682: dispute_code = 'OZF_RESALE_MULTI_HEADER',
683: followup_action_code = 'C',
684: response_type = 'CA',
685: response_code = 'N'
686: WHERE resale_batch_id = p_resale_batch_id
687: AND order_number = l_order_num_tbl(k).order_number
688: AND sold_from_cust_account_id = l_order_num_tbl(k).sold_from_cust_account_id
689: AND date_ordered = l_order_num_tbl(k).date_ordered
690: AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
691: --AND duplicated_adjustment_id <> -1;
692:
693: GOTO END_ORDER_HEADER;
694: ELSE
728: IF OZF_DEBUG_LOW_ON THEN
729: OZF_UTILITY_PVT.debug_message('Current line_int_id:' || l_valid_line_id_tbl(j).id);
730: END IF;
731:
732: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
733: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
734: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
735:
736: -- Need to check against inventory
729: OZF_UTILITY_PVT.debug_message('Current line_int_id:' || l_valid_line_id_tbl(j).id);
730: END IF;
731:
732: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
733: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
734: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
735:
736: -- Need to check against inventory
737: -- Only need to check inventory for non-duplicate resale lines
730: END IF;
731:
732: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
733: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
734: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
735:
736: -- Need to check against inventory
737: -- Only need to check inventory for non-duplicate resale lines
738: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
734: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
735:
736: -- Need to check against inventory
737: -- Only need to check inventory for non-duplicate resale lines
738: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
739: ( l_line_int_rec.duplicated_adjustment_id IS NULL
740: OR l_line_int_rec.duplicated_adjustment_id = -1 ) AND
741: l_inventory_tracking = 'T' THEN
742: -- Check inventory level first
755: OZF_UTILITY_PVT.debug_message('Did NOT pass inventory checking');
756: END IF;
757: -- Here turn thIS line to DISPUTED AND create a dISput code FOR it.
758: UPDATE ozf_resale_lines_int
759: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
760: dispute_code = 'OZF_LT_INVT',
761: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
762: response_type = 'CA',
763: response_code = 'N'
764: WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
765:
766: -- SET Batch as DISPUTED
767: UPDATE ozf_resale_batches
768: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
769: WHERE resale_batch_id = l_line_int_rec.resale_batch_id;
770:
771: GOTO END_ORDER_LINE;
772: END IF;
778: -- see jxwu header_fix.
779: IF j = 1 THEN
780: -- Determin header id
781: IF l_create_order_header THEN
782: OZF_RESALE_COMMON_PVT.Insert_Resale_Header(
783: p_api_version => 1
784: ,p_init_msg_list => FND_API.G_FALSE
785: ,p_commit => FND_API.G_FALSE
786: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
804: IF OZF_DEBUG_LOW_ON THEN
805: OZF_UTILITY_PVT.debug_message('header_id is '|| l_header_id);
806: END IF;
807:
808: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED THEN
809: IF (l_line_int_rec.duplicated_line_id IS NULL) OR
810: (l_line_int_rec.duplicated_line_id IS NOT NULL AND
811: l_line_int_rec.duplicated_adjustment_id = -1) THEN
812: -- We need to create party id for bill_to and end customer if
812: -- We need to create party id for bill_to and end customer if
813: -- users wants us to do it.
814:
815: -- No problem so far. Insert INTO batch_lines TABLE
816: OZF_RESALE_COMMON_PVT.Insert_Resale_Line(
817: p_api_version => 1
818: ,p_init_msg_list => FND_API.G_FALSE
819: ,p_commit => FND_API.G_FALSE
820: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
831: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
832: END IF;
833:
834: -- FOR processed order line, I need to create a transaction FOR it.
835: OZF_RESALE_COMMON_PVT.Create_Sales_Transaction (
836: p_api_version => 1.0
837: ,p_init_msg_list => FND_API.G_FALSE
838: ,p_commit => FND_API.G_FALSE
839: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
877: ELSE
878: l_line_id := l_line_int_rec.duplicated_line_id;
879: END IF;
880:
881: ELSIF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
882: l_line_id := l_line_int_rec.duplicated_line_id;
883: END IF;
884:
885: IF OZF_DEBUG_LOW_ON THEN
887: END IF;
888:
889: IF l_line_int_rec.status_code = 'PROCESSED' THEN
890: -- Create Resale Line Mappings only for Processed Lines
891: -- l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
892: -- -- only create mapping FOR the lines that are processed or
893: -- -- duplicated, yet the adjustment IS new then
894: OZF_RESALE_COMMON_PVT.Insert_Resale_Line_Mapping(
895: p_api_version => 1
890: -- Create Resale Line Mappings only for Processed Lines
891: -- l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
892: -- -- only create mapping FOR the lines that are processed or
893: -- -- duplicated, yet the adjustment IS new then
894: OZF_RESALE_COMMON_PVT.Insert_Resale_Line_Mapping(
895: p_api_version => 1
896: ,p_init_msg_list => FND_API.G_FALSE
897: ,p_commit => FND_API.G_FALSE
898: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
913: -- and px_ozf_act_budgets_tbl for PLSQL table impln of storing budgets value.
914: -- and px_ozf_funds_old_rectype for taking the funds old values
915: -- and px_ozf_funds_new_rectype for taking the new values after updation.
916:
917: IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
918: l_line_int_rec.tracing_flag = 'F' AND
919: --Bug# 8632964 fixed by anuj and muthu (+)
920: l_accrual_flag = 'T' THEN
921: --Bug# 8632964 fixed by anuj and muthu (-)
918: l_line_int_rec.tracing_flag = 'F' AND
919: --Bug# 8632964 fixed by anuj and muthu (+)
920: l_accrual_flag = 'T' THEN
921: --Bug# 8632964 fixed by anuj and muthu (-)
922: OZF_RESALE_COMMON_PVT.Create_Utilization(
923: p_api_version => 1.0
924: ,p_init_msg_LIST => FND_API.G_FALSE
925: ,p_commit => FND_API.G_FALSE
926: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
987:
988: END IF;
989: -- For Bug#8867381(-)
990:
991: OZF_RESALE_COMMON_PVT.Update_Batch_Calculations (
992: p_api_version => 1.0
993: ,p_init_msg_list => FND_API.G_FALSE
994: ,p_commit => FND_API.G_FALSE
995: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1005: END IF;
1006:
1007: -- After all the processes are done, call claim autopay
1008: l_claim_rec.cust_account_id := l_partner_cust_account_id;
1009: l_claim_rec.source_object_class := OZF_RESALE_COMMON_PVT.G_BATCH_OBJECT_CLASS;
1010: l_claim_rec.source_object_id := p_resale_batch_id;
1011: l_claim_rec.source_object_number := l_batch_number;
1012: l_claim_rec.batch_id := p_resale_batch_id;
1013: l_claim_rec.batch_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
1009: l_claim_rec.source_object_class := OZF_RESALE_COMMON_PVT.G_BATCH_OBJECT_CLASS;
1010: l_claim_rec.source_object_id := p_resale_batch_id;
1011: l_claim_rec.source_object_number := l_batch_number;
1012: l_claim_rec.batch_id := p_resale_batch_id;
1013: l_claim_rec.batch_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
1014: l_claim_rec.customer_ref_number := l_partner_claim_num;
1015: l_claim_rec.currency_code := l_currency_code;
1016: l_funds_util_flt.cust_account_id := l_partner_cust_account_id;
1017: l_funds_util_flt.utiz_currency_code := l_currency_code ; --Fix for 9662148
1016: l_funds_util_flt.cust_account_id := l_partner_cust_account_id;
1017: l_funds_util_flt.utiz_currency_code := l_currency_code ; --Fix for 9662148
1018: --Bug# 8632964 fixed by anuj and muthu (+)
1019: IF l_accrual_flag = 'T' THEN
1020: l_funds_util_flt.reference_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
1021: l_funds_util_flt.reference_id := p_resale_batch_id;
1022: l_funds_util_flt.utilization_type:= G_CHBK_UTIL_TYPE;
1023: IF OZF_DEBUG_HIGH_ON THEN
1024: OZF_UTILITY_PVT.debug_message('Accruals created for this batch should be used.');
1062:
1063: BEGIN
1064: -- BUG 4395931 (+)
1065: UPDATE ozf_resale_lines_int
1066: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
1067: WHERE resale_batch_id = p_resale_batch_id
1068: AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
1069:
1070: /*
1064: -- BUG 4395931 (+)
1065: UPDATE ozf_resale_lines_int
1066: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
1067: WHERE resale_batch_id = p_resale_batch_id
1068: AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
1069:
1070: /*
1071: -- SLKRISHN change to common procedure
1072: UPDATE ozf_resale_lines_int
1069:
1070: /*
1071: -- SLKRISHN change to common procedure
1072: UPDATE ozf_resale_lines_int
1073: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
1074: WHERE resale_batch_id = p_resale_batch_id
1075: AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
1076: --AND duplicated_adjustment_id <> -1;
1077: */
1071: -- SLKRISHN change to common procedure
1072: UPDATE ozf_resale_lines_int
1073: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
1074: WHERE resale_batch_id = p_resale_batch_id
1075: AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
1076: --AND duplicated_adjustment_id <> -1;
1077: */
1078: -- BUG 4395931 (-)
1079:
1079:
1080: -- For Bug#8867381 (+)
1081: -- UPDATE batch status to PENDING_CLOSE as the records are stored in the staging tables
1082: -- and run the concurrent prgm to update the main tables.
1083: IF x_fund_table_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE
1084: OR x_budget_table_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE THEN
1085: UPDATE ozf_resale_batches
1086: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE,
1087: paid_amount = l_amount_claimed,
1080: -- For Bug#8867381 (+)
1081: -- UPDATE batch status to PENDING_CLOSE as the records are stored in the staging tables
1082: -- and run the concurrent prgm to update the main tables.
1083: IF x_fund_table_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE
1084: OR x_budget_table_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE THEN
1085: UPDATE ozf_resale_batches
1086: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE,
1087: paid_amount = l_amount_claimed,
1088: last_updated_by = NVL(fnd_global.user_id, -1),
1082: -- and run the concurrent prgm to update the main tables.
1083: IF x_fund_table_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE
1084: OR x_budget_table_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE THEN
1085: UPDATE ozf_resale_batches
1086: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE,
1087: paid_amount = l_amount_claimed,
1088: last_updated_by = NVL(fnd_global.user_id, -1),
1089: last_update_date = SYSDATE
1090: WHERE resale_batch_id = p_resale_batch_id;
1091:
1092: ELSE
1093: -- UPDATE batch status to closed.
1094: UPDATE ozf_resale_batches
1095: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED,
1096: paid_amount = l_amount_claimed,
1097: last_updated_by = NVL(fnd_global.user_id, -1),
1098: last_update_date = SYSDATE
1099: WHERE resale_batch_id = p_resale_batch_id;
1197: l_api_name CONSTANT VARCHAR2(30) := 'Validate_Order_Record';
1198: l_api_version CONSTANT NUMBER := 1.0;
1199: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1200: --
1201: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1202: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1203: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1204: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1205: l_return_status VARCHAR2(1);
1198: l_api_version CONSTANT NUMBER := 1.0;
1199: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1200: --
1201: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1202: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1203: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1204: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1205: l_return_status VARCHAR2(1);
1206: --
1199: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1200: --
1201: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1202: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1203: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1204: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1205: l_return_status VARCHAR2(1);
1206: --
1207: CURSOR null_columns_csr (p_batch_id in number) IS
1200: --
1201: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1202: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1203: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1204: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1205: l_return_status VARCHAR2(1);
1206: --
1207: CURSOR null_columns_csr (p_batch_id in number) IS
1208: SELECT resale_line_int_id,
1210: 'PURCHASE_PRICE',
1211: NULL
1212: FROM ozf_resale_lines_int_all
1213: WHERE resale_batch_id = p_batch_id
1214: AND status_code IN (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED)
1215: AND tracing_flag = 'F'
1216: AND purchase_price IS NULL;
1217:
1218: --
1258: CLOSE null_columns_csr;
1259: --
1260:
1261: -- log disputed lines
1262: OZF_RESALE_COMMON_PVT.Bulk_Insert_Resale_Log (
1263: p_id_value => l_id_tbl,
1264: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1265: p_error_code => l_err_tbl,
1266: p_column_name => l_col_tbl,
1260:
1261: -- log disputed lines
1262: OZF_RESALE_COMMON_PVT.Bulk_Insert_Resale_Log (
1263: p_id_value => l_id_tbl,
1264: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1265: p_error_code => l_err_tbl,
1266: p_column_name => l_col_tbl,
1267: p_column_value => l_val_tbl,
1268: p_batch_id => p_resale_batch_id, --bug # 5997978 fixed
1275: RAISE FND_API.g_exc_unexpected_error;
1276: END IF;
1277: --
1278:
1279: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
1280: p_batch_id => p_resale_batch_id,
1281: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
1282: x_return_status => l_return_status
1283: );
1277: --
1278:
1279: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
1280: p_batch_id => p_resale_batch_id,
1281: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
1282: x_return_status => l_return_status
1283: );
1284: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1285: RAISE FND_API.g_exc_error;
1366: l_cust_account_id NUMBER;
1367: l_date_ordered DATE;
1368: l_has_error BOOLEAN:=false;
1369: l_log_id NUMBER;
1370: l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
1371: l_header_id NUMBER;
1372: l_line_id NUMBER;
1373: l_object_version_number NUMBER :=1;
1374:
1382: l_IS_error BOOLEAN:=FALSE;
1383: l_inventory_level_valid BOOLEAN;
1384: l_allowed_amount NUMBER;
1385:
1386: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1387: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1388: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1389: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1390:
1383: l_inventory_level_valid BOOLEAN;
1384: l_allowed_amount NUMBER;
1385:
1386: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1387: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1388: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1389: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1390:
1391: l_pricing_error BOOLEAN:= FALSE;
1384: l_allowed_amount NUMBER;
1385:
1386: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1387: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1388: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1389: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1390:
1391: l_pricing_error BOOLEAN:= FALSE;
1392: --
1385:
1386: l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1387: l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1388: l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1389: l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1390:
1391: l_pricing_error BOOLEAN:= FALSE;
1392: --
1393: CURSOR order_identifiers_csr (p_id IN NUMBER) IS
1408: p_date_ordered DATE,
1409: p_cust_account_id NUMBER )IS
1410: SELECT resale_line_int_id, 'OZF_RESALE_PRICE_ERROR', NULL, NULL
1411: FROM ozf_resale_lines_int
1412: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1413: AND order_number = l_order_number
1414: AND date_ordered = l_date_ordered
1415: AND sold_from_cust_account_id =l_cust_account_id
1416: AND dispute_code is null
1454: OZF_UTILITY_PVT.debug_message('line '||p_line_tbl(i).chargeback_int_id || ' has pricing error' );
1455: END IF;
1456: BEGIN
1457: update ozf_resale_lines_int
1458: set status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1459: dispute_code = 'OZF_RESALE_PRICE_ERROR',
1460: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1461: response_type = 'CA',
1462: response_code = 'N'
1466: ozf_utility_pvt.error_message('OZF_UPD_RESALE_INT_WRG');
1467: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1468: END;
1469:
1470: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1471: p_id_value => p_line_tbl(i).chargeback_int_id,
1472: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1473: p_error_code => p_line_tbl(i).pricing_status_code,
1474: p_error_message => p_line_tbl(i).pricing_status_text,
1468: END;
1469:
1470: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1471: p_id_value => p_line_tbl(i).chargeback_int_id,
1472: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1473: p_error_code => p_line_tbl(i).pricing_status_code,
1474: p_error_message => p_line_tbl(i).pricing_status_text,
1475: p_column_name => NULL,
1476: p_column_value => NULL,
1481: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1482: RAISE FND_API.g_exc_unexpected_error;
1483: END IF;
1484:
1485: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1486: p_id_value => p_line_tbl(i).chargeback_int_id,
1487: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1488: p_error_code => 'OZF_RESALE_PRICE_ERROR',
1489: p_column_name => NULL,
1483: END IF;
1484:
1485: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1486: p_id_value => p_line_tbl(i).chargeback_int_id,
1487: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1488: p_error_code => 'OZF_RESALE_PRICE_ERROR',
1489: p_column_name => NULL,
1490: p_column_value => NULL,
1491: x_return_status => l_return_status);
1521: FETCH open_lines_csr BULK COLLECT INTO l_id_tbl, l_err_tbl, l_col_tbl, l_val_tbl;
1522: CLOSE open_lines_csr;
1523:
1524: -- log disputed lines
1525: OZF_RESALE_COMMON_PVT.Bulk_Insert_Resale_Log (
1526: p_id_value => l_id_tbl,
1527: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1528: p_error_code => l_err_tbl,
1529: p_column_name => l_col_tbl,
1523:
1524: -- log disputed lines
1525: OZF_RESALE_COMMON_PVT.Bulk_Insert_Resale_Log (
1526: p_id_value => l_id_tbl,
1527: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1528: p_error_code => l_err_tbl,
1529: p_column_name => l_col_tbl,
1530: p_column_value => l_val_tbl,
1531: p_batch_id => p_resale_batch_id, --bug # 5997978 fixed
1538: END IF;
1539:
1540: BEGIN
1541: UPDATE ozf_resale_lines_int_all
1542: SET status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1543: dispute_code = 'OZF_RESALE_PRICE_ERROR',
1544: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1545: response_type = 'CA',
1546: response_code = 'N'
1543: dispute_code = 'OZF_RESALE_PRICE_ERROR',
1544: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1545: response_type = 'CA',
1546: response_code = 'N'
1547: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1548: AND order_number = l_order_number
1549: AND date_ordered = l_date_ordered
1550: AND sold_from_cust_account_id =l_cust_account_id
1551: AND dispute_code is null
1567: OPEN func_currency_cd_csr;
1568: FETCH func_currency_cd_csr INTO l_func_currency_code;
1569: CLOSE func_currency_cd_csr;
1570:
1571: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1572: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_default_exchange_type;
1573: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1574:
1575: -- FOR each chargeback of the line, we will UPDATE the line
1568: FETCH func_currency_cd_csr INTO l_func_currency_code;
1569: CLOSE func_currency_cd_csr;
1570:
1571: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1572: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_default_exchange_type;
1573: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1574:
1575: -- FOR each chargeback of the line, we will UPDATE the line
1576: --//Bugfix : 12719436 - added logic for l_pricing_error
1569: CLOSE func_currency_cd_csr;
1570:
1571: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1572: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_default_exchange_type;
1573: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1574:
1575: -- FOR each chargeback of the line, we will UPDATE the line
1576: --//Bugfix : 12719436 - added logic for l_pricing_error
1577: FOR i in 1..p_line_tbl.LAST LOOP
1580: p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_UNCHANGED AND
1581: p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_UPDATED;
1582:
1583: IF p_line_tbl(i).line_type_code = 'LINE' AND NOT l_pricing_error THEN
1584: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(p_line_tbl(i).chargeback_int_id);
1585: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1586: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1587:
1588: -- Check inventory level FOR thIS order.
1581: p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_UPDATED;
1582:
1583: IF p_line_tbl(i).line_type_code = 'LINE' AND NOT l_pricing_error THEN
1584: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(p_line_tbl(i).chargeback_int_id);
1585: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1586: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1587:
1588: -- Check inventory level FOR thIS order.
1589: -- If inventory level IS lower than the asked, then there IS no need to
1582:
1583: IF p_line_tbl(i).line_type_code = 'LINE' AND NOT l_pricing_error THEN
1584: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(p_line_tbl(i).chargeback_int_id);
1585: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1586: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1587:
1588: -- Check inventory level FOR thIS order.
1589: -- If inventory level IS lower than the asked, then there IS no need to
1590: -- continue processing
1612: IF OZF_DEBUG_LOW_ON THEN
1613: OZF_UTILITY_PVT.debug_message('Did not pass inventory checking');
1614: END IF;
1615: --
1616: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1617: p_id_value => p_line_tbl(i).chargeback_int_id,
1618: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1619: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1620: p_column_name => NULL,
1614: END IF;
1615: --
1616: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1617: p_id_value => p_line_tbl(i).chargeback_int_id,
1618: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1619: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1620: p_column_name => NULL,
1621: p_column_value => NULL,
1622: x_return_status => l_return_status);
1629: --
1630: -- SET Batch as DISPUTED
1631: -- SLKRISHN change to common procedure
1632: UPDATE ozf_resale_lines_int
1633: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1634: dispute_code = 'OZF_LT_INVT',
1635: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1636: response_type = 'CA',
1637: response_code = 'N'
1637: response_code = 'N'
1638: WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
1639:
1640: UPDATE ozf_resale_batches
1641: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
1642: WHERE resale_batch_id = l_resale_int_rec.resale_batch_id;
1643: --
1644: GOTO END_LOOP3;
1645: ELSE
1764: OZF_UTILITY_PVT.debug_message('Allowed amount:' || l_allowed_amount);
1765: END IF;
1766:
1767: -- Update the results of Chargeback Calculation
1768: OZF_RESALE_COMMON_PVT.Update_Line_Calculations(
1769: p_resale_line_int_rec => l_resale_int_rec,
1770: p_unit_price => p_line_tbl(i).unit_price,
1771: p_line_quantity => p_line_tbl(i).line_quantity,
1772: p_allowed_amount => l_allowed_amount,
1783: NULL;
1784: END LOOP; -- END LOOP through lines
1785:
1786: -- Dispute all the lines from this order
1787: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
1788: p_batch_id => p_resale_batch_id,
1789: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
1790: x_return_status => l_return_status
1791: );
1785:
1786: -- Dispute all the lines from this order
1787: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
1788: p_batch_id => p_resale_batch_id,
1789: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
1790: x_return_status => l_return_status
1791: );
1792: --
1793: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1874: WHERE order_number = p_order_number
1875: AND sold_from_cust_account_id= p_id
1876: AND date_ordered = p_date
1877: AND status_code = 'OPEN'
1878: -- AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
1879: -- AND duplicated_adjustment_id IS NULL
1880: -- bug # 6821886 fixed by ateotia (+)
1881: AND resale_batch_id = p_batch_id;
1882: --AND tracing_flag = 'F';
1904:
1905: l_order_set_trc_tbl order_set_trc_tbl;
1906: l_inventory_tracking VARCHAR2(2);
1907: l_inventory_level_valid BOOLEAN;
1908: l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
1909: -- 6511302 (-)
1910:
1911: --
1912: BEGIN
1920: -- Initialize API return status to sucess
1921: x_return_status := FND_API.G_RET_STS_SUCCESS;
1922:
1923: -- bug 6511302 (+) validate inventory for tracing lines
1924: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1925: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
1926: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1927:
1928: IF l_inventory_tracking = 'T' THEN -- validate inventory for tracing lines. order lines will be validated during pricing phase
1921: x_return_status := FND_API.G_RET_STS_SUCCESS;
1922:
1923: -- bug 6511302 (+) validate inventory for tracing lines
1924: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1925: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
1926: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1927:
1928: IF l_inventory_tracking = 'T' THEN -- validate inventory for tracing lines. order lines will be validated during pricing phase
1929: OPEN c_order_set_trc(p_order_number,
1922:
1923: -- bug 6511302 (+) validate inventory for tracing lines
1924: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1925: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
1926: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1927:
1928: IF l_inventory_tracking = 'T' THEN -- validate inventory for tracing lines. order lines will be validated during pricing phase
1929: OPEN c_order_set_trc(p_order_number,
1930: p_sold_from_cust_acct_id,
1935:
1936: IF l_order_set_trc_tbl.COUNT > 0 THEN
1937: FOR i IN 1..l_order_set_trc_tbl.COUNT LOOP
1938: OZF_UTILITY_PVT.debug_message(l_full_name || ' Validating tracing line ' || l_order_set_trc_tbl(i).resale_line_int_id);
1939: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_trc_tbl(i).resale_line_int_id);
1940: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1941: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1942:
1943: IF l_resale_int_rec.duplicated_adjustment_id IS NULL OR l_resale_int_rec.duplicated_adjustment_id = -1 THEN
1936: IF l_order_set_trc_tbl.COUNT > 0 THEN
1937: FOR i IN 1..l_order_set_trc_tbl.COUNT LOOP
1938: OZF_UTILITY_PVT.debug_message(l_full_name || ' Validating tracing line ' || l_order_set_trc_tbl(i).resale_line_int_id);
1939: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_trc_tbl(i).resale_line_int_id);
1940: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1941: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1942:
1943: IF l_resale_int_rec.duplicated_adjustment_id IS NULL OR l_resale_int_rec.duplicated_adjustment_id = -1 THEN
1944: -- Check inventory level first
1937: FOR i IN 1..l_order_set_trc_tbl.COUNT LOOP
1938: OZF_UTILITY_PVT.debug_message(l_full_name || ' Validating tracing line ' || l_order_set_trc_tbl(i).resale_line_int_id);
1939: OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_trc_tbl(i).resale_line_int_id);
1940: FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1941: CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1942:
1943: IF l_resale_int_rec.duplicated_adjustment_id IS NULL OR l_resale_int_rec.duplicated_adjustment_id = -1 THEN
1944: -- Check inventory level first
1945: OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
1958: OZF_UTILITY_PVT.debug_message('Did NOT pass inventory checking');
1959: END IF;
1960:
1961: -- log
1962: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1963: p_id_value => l_order_set_trc_tbl(i).resale_line_int_id,
1964: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1965: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1966: p_column_name => NULL,
1960:
1961: -- log
1962: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1963: p_id_value => l_order_set_trc_tbl(i).resale_line_int_id,
1964: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1965: p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1966: p_column_name => NULL,
1967: p_column_value => NULL,
1968: x_return_status => l_return_status);
1974: END IF;
1975:
1976: -- Set line to DISPUTED
1977: UPDATE ozf_resale_lines_int
1978: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1979: dispute_code = 'OZF_LT_INVT',
1980: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1981: response_type = 'CA',
1982: response_code = 'N'
1983: WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
1984:
1985: -- SET Batch as DISPUTED
1986: UPDATE ozf_resale_batches
1987: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
1988: WHERE resale_batch_id = l_resale_int_rec.resale_batch_id;
1989: END IF;
1990: END IF;
1991: END LOOP;
2055: BEGIN
2056: -- SLKRISHN move to a common procedure
2057: DELETE FROM ozf_resale_logs
2058: WHERE resale_id = l_order_set_tbl(J).resale_line_int_id
2059: AND resale_id_type = OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE;
2060: EXCEPTION
2061: WHEN OTHERS THEN
2062: IF OZF_UNEXP_ERROR THEN
2063: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2361: SELECT DISTINCT order_number,
2362: sold_from_cust_account_id,
2363: date_ordered
2364: FROM ozf_resale_lines_int
2365: WHERE status_code = 'OPEN' --(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
2366: AND resale_batch_id = p_resale_batch_id
2367: --AND (duplicated_adjustment_id IS NULL OR
2368: -- duplicated_adjustment_id = -1 )
2369: ORDER BY date_ordered;
2367: --AND (duplicated_adjustment_id IS NULL OR
2368: -- duplicated_adjustment_id = -1 )
2369: ORDER BY date_ordered;
2370:
2371: l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2372: l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2373: l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2374:
2375: l_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
2368: -- duplicated_adjustment_id = -1 )
2369: ORDER BY date_ordered;
2370:
2371: l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2372: l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2373: l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2374:
2375: l_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
2376: l_ldets_tbl OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE;
2369: ORDER BY date_ordered;
2370:
2371: l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2372: l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2373: l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2374:
2375: l_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
2376: l_ldets_tbl OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE;
2377: l_related_lines_tbl OZF_ORDER_PRICE_PVT.RLTD_LINE_TBL_TYPE;
2415: x_return_status := FND_API.G_RET_STS_SUCCESS;
2416:
2417: -- Move Delete logs to resale_pre_process
2418: -- Delete the logs for the current batch
2419: OZF_RESALE_COMMON_PVT.Delete_Log(
2420: p_api_version => 1.0
2421: ,p_init_msg_list => FND_API.G_FALSE
2422: ,p_commit => FND_API.G_FALSE
2423: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2432: RAISE FND_API.g_exc_unexpected_error;
2433: END IF;
2434:
2435: -- Check whether there IS a need to do inventory_verification
2436: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2437: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
2438: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2439: OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': inventory flag = ' || l_inventory_tracking);
2440: -- populates the temp tables
2433: END IF;
2434:
2435: -- Check whether there IS a need to do inventory_verification
2436: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2437: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
2438: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2439: OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': inventory flag = ' || l_inventory_tracking);
2440: -- populates the temp tables
2441: IF l_inventory_tracking = 'T' THEN
2434:
2435: -- Check whether there IS a need to do inventory_verification
2436: OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2437: FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
2438: CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2439: OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': inventory flag = ' || l_inventory_tracking);
2440: -- populates the temp tables
2441: IF l_inventory_tracking = 'T' THEN
2442: OPEN batch_info_csr(p_resale_batch_id);
2509:
2510: IF OZF_DEBUG_LOW_ON THEN
2511: OZF_UTILITY_PVT.debug_message('/*--- before get_order_price Insert resale log done ---*/');
2512: OZF_UTILITY_PVT.debug_message('/*--- chargeback int id:' || l_line_tbl(j).chargeback_int_id);
2513: OZF_UTILITY_PVT.debug_message('/*--- Message type: '||OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE);
2514: END IF;
2515:
2516: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2517: p_id_value => l_line_tbl(j).chargeback_int_id,
2512: OZF_UTILITY_PVT.debug_message('/*--- chargeback int id:' || l_line_tbl(j).chargeback_int_id);
2513: OZF_UTILITY_PVT.debug_message('/*--- Message type: '||OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE);
2514: END IF;
2515:
2516: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2517: p_id_value => l_line_tbl(j).chargeback_int_id,
2518: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
2519: p_error_code => 'OZF_GET_ORDER_PRIC_ERR',
2520: p_column_name => NULL,
2514: END IF;
2515:
2516: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2517: p_id_value => l_line_tbl(j).chargeback_int_id,
2518: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
2519: p_error_code => 'OZF_GET_ORDER_PRIC_ERR',
2520: p_column_name => NULL,
2521: p_column_value => NULL,
2522: x_return_status => l_return_status);
2532: OZF_UTILITY_PVT.debug_message('/*--- After get_order_price Insert resale log done ---*/');
2533: END IF;
2534:
2535: -- Dispute all the lines from this order
2536: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
2537: p_batch_id => p_resale_batch_id,
2538: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
2539: x_return_status => l_return_status
2540: );
2534:
2535: -- Dispute all the lines from this order
2536: OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
2537: p_batch_id => p_resale_batch_id,
2538: p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
2539: x_return_status => l_return_status
2540: );
2541: --
2542: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2573: -- insert error messages into error stack
2574: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2575: FOR j in 1..l_line_tbl.LAST
2576: LOOP
2577: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2578: p_id_value => l_line_tbl(j).chargeback_int_id,
2579: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
2580: p_error_code => 'OZF_PROC_PRIC_RESLT_ERR',
2581: p_column_name => NULL,
2575: FOR j in 1..l_line_tbl.LAST
2576: LOOP
2577: OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2578: p_id_value => l_line_tbl(j).chargeback_int_id,
2579: p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
2580: p_error_code => 'OZF_PROC_PRIC_RESLT_ERR',
2581: p_column_name => NULL,
2582: p_column_value => NULL,
2583: x_return_status => l_return_status);
2595: END IF;
2596:
2597: BEGIN
2598: UPDATE ozf_resale_lines_int
2599: SET status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
2600: dispute_code = 'OZF_PROC_PRIC_RESLT_ERR',
2601: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
2602: response_type = 'CA',
2603: response_code = 'N'
2600: dispute_code = 'OZF_PROC_PRIC_RESLT_ERR',
2601: followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
2602: response_type = 'CA',
2603: response_code = 'N'
2604: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
2605: AND order_number = l_order_num_tbl(i)
2606: AND date_ordered = l_order_date_tbl(i)
2607: AND sold_from_cust_account_id =l_cust_account_id_tbl(i)
2608: AND dispute_code is null
2644: END IF;
2645: END IF;
2646:
2647: -- Update Chargeback header with processing detail
2648: OZF_RESALE_COMMON_PVT.Update_Batch_Calculations (
2649: p_api_version => 1.0
2650: ,p_init_msg_list => FND_API.G_FALSE
2651: ,p_commit => FND_API.G_FALSE
2652: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL