[Home] [Help]
1: PACKAGE BODY OZF_RESALE_COMMON_PVT AS
2: /* $Header: ozfvrscb.pls 120.36.12020000.3 2013/02/12 09:23:28 annsrini ship $ */
3: -------------------------------------------------------------------------------
4: -- PACKAGE:
5: -- OZF_RESALE_COMMON_PVT
1: PACKAGE BODY OZF_RESALE_COMMON_PVT AS
2: /* $Header: ozfvrscb.pls 120.36.12020000.3 2013/02/12 09:23:28 annsrini ship $ */
3: -------------------------------------------------------------------------------
4: -- PACKAGE:
5: -- OZF_RESALE_COMMON_PVT
6: --
7: -- PURPOSE:
8: -- Private API for common resale functionality across all IDSM batches.
9: --
21: -- 2/17/2010 nepanda Bug 9131648 : multi currency changes
22: --06-Nov-2011 BKUNJAN ER 13333298 Added OUT parameter x_utilization_id in Procedure Create_Adj_And_Utilization
23: -------------------------------------------------------------------------------
24:
25: G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_RESALE_COMMON_PVT';
26: G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvscb.pls';
27:
28: OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_debug_high);
29: OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_debug_low);
92: CLOSE g_log_id_csr;
93:
94: -- julou bug 6317120. get org_id from table
95: IF p_id_type = 'BATCH' THEN
96: OPEN OZF_RESALE_COMMON_PVT.gc_batch_org_id(p_id_value);
97: FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
98: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
99: ELSIF p_id_type = 'LINE' THEN
100: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
93:
94: -- julou bug 6317120. get org_id from table
95: IF p_id_type = 'BATCH' THEN
96: OPEN OZF_RESALE_COMMON_PVT.gc_batch_org_id(p_id_value);
97: FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
98: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
99: ELSIF p_id_type = 'LINE' THEN
100: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
101: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
94: -- julou bug 6317120. get org_id from table
95: IF p_id_type = 'BATCH' THEN
96: OPEN OZF_RESALE_COMMON_PVT.gc_batch_org_id(p_id_value);
97: FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
98: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
99: ELSIF p_id_type = 'LINE' THEN
100: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
101: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
102: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
96: OPEN OZF_RESALE_COMMON_PVT.gc_batch_org_id(p_id_value);
97: FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
98: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
99: ELSIF p_id_type = 'LINE' THEN
100: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
101: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
102: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
103: ELSIF p_id_type = 'IFACE' THEN
104: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
97: FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
98: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
99: ELSIF p_id_type = 'LINE' THEN
100: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
101: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
102: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
103: ELSIF p_id_type = 'IFACE' THEN
104: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
105: FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
98: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
99: ELSIF p_id_type = 'LINE' THEN
100: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
101: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
102: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
103: ELSIF p_id_type = 'IFACE' THEN
104: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
105: FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
106: CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
100: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
101: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
102: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
103: ELSIF p_id_type = 'IFACE' THEN
104: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
105: FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
106: CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
107: END IF;
108:
101: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
102: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
103: ELSIF p_id_type = 'IFACE' THEN
104: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
105: FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
106: CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
107: END IF;
108:
109: BEGIN
102: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
103: ELSIF p_id_type = 'IFACE' THEN
104: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
105: FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
106: CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
107: END IF;
108:
109: BEGIN
110: OZF_RESALE_LOGS_PKG.Insert_Row(
114: p_error_code => p_error_code,
115: p_error_message => nvl(p_error_message, fnd_message.get_string('OZF',p_error_code)),
116: p_column_name => p_column_name,
117: p_column_value => p_column_value,
118: --px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
119: px_org_id => l_org_id
120: );
121: EXCEPTION
122: WHEN OTHERS THEN
652: --
653: CURSOR tolerance_line_count_csr (p_id NUMBER)IS
654: SELECT count(1)
655: FROM ozf_resale_lines_int
656: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
657: AND tolerance_flag = 'T'
658: AND resale_batch_id = p_id;
659:
660: CURSOR invalid_line_count_csr (p_id NUMBER)IS
659:
660: CURSOR invalid_line_count_csr (p_id NUMBER)IS
661: SELECT count(1)
662: FROM ozf_resale_lines_int
663: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED
664: AND dispute_code = OZF_RESALE_COMMON_PVT.G_INVALD_DISPUTE_CODE
665: AND resale_batch_id = p_id;
666:
667: CURSOR header_tolerance_csr(p_id in NUMBER) IS
660: CURSOR invalid_line_count_csr (p_id NUMBER)IS
661: SELECT count(1)
662: FROM ozf_resale_lines_int
663: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED
664: AND dispute_code = OZF_RESALE_COMMON_PVT.G_INVALD_DISPUTE_CODE
665: AND resale_batch_id = p_id;
666:
667: CURSOR header_tolerance_csr(p_id in NUMBER) IS
668: SELECT header_tolerance_operand, header_tolerance_calc_code
702: -- Initialize API return status to sucess
703: x_return_status := FND_API.G_RET_STS_SUCCESS;
704:
705: -- get data regard thIS process
706: OPEN OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr (p_resale_batch_id);
707: FETCH OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr INTO l_lines_disputed;
708: CLOSE OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr;
709:
710: OPEN tolerance_line_count_csr (p_resale_batch_id);
703: x_return_status := FND_API.G_RET_STS_SUCCESS;
704:
705: -- get data regard thIS process
706: OPEN OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr (p_resale_batch_id);
707: FETCH OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr INTO l_lines_disputed;
708: CLOSE OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr;
709:
710: OPEN tolerance_line_count_csr (p_resale_batch_id);
711: FETCH tolerance_line_count_csr INTO l_lines_w_tolerance;
704:
705: -- get data regard thIS process
706: OPEN OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr (p_resale_batch_id);
707: FETCH OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr INTO l_lines_disputed;
708: CLOSE OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr;
709:
710: OPEN tolerance_line_count_csr (p_resale_batch_id);
711: FETCH tolerance_line_count_csr INTO l_lines_w_tolerance;
712: CLOSE tolerance_line_count_csr;
719: OPEN invalid_line_count_csr(p_resale_batch_id);
720: FETCH invalid_line_count_csr INTO l_lines_invalid;
721: CLOSE invalid_line_count_csr;
722:
723: OPEN OZF_RESALE_COMMON_PVT.g_total_amount_csr (p_resale_batch_id);
724: FETCH OZF_RESALE_COMMON_PVT.g_total_amount_csr INTO l_calculated_amount,
725: l_total_claimed_amount,
726: l_total_accepted_amount,
727: l_total_allowed_amount,
720: FETCH invalid_line_count_csr INTO l_lines_invalid;
721: CLOSE invalid_line_count_csr;
722:
723: OPEN OZF_RESALE_COMMON_PVT.g_total_amount_csr (p_resale_batch_id);
724: FETCH OZF_RESALE_COMMON_PVT.g_total_amount_csr INTO l_calculated_amount,
725: l_total_claimed_amount,
726: l_total_accepted_amount,
727: l_total_allowed_amount,
728: l_total_disputed_amount;
725: l_total_claimed_amount,
726: l_total_accepted_amount,
727: l_total_allowed_amount,
728: l_total_disputed_amount;
729: CLOSE OZF_RESALE_COMMON_PVT.g_total_amount_csr;
730:
731: --bug # 6134121 fixed by ateotia(+)
732: OPEN csr_duplicated_dispute_amount(p_resale_batch_id);
733: FETCH csr_duplicated_dispute_amount INTO l_lines_duplicated
886: -- NOTES
887: --
888: ---------------------------------------------------------------------
889: PROCEDURE Update_Line_Calculations(
890: p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE,
891: p_unit_price IN NUMBER,
892: p_line_quantity IN NUMBER,
893: p_allowed_amount IN NUMBER,
894: x_return_status OUT NOCOPY VARCHAR2
957: --IF abs(p_allowed_amount) = p_resale_line_int_rec.claimed_amount THEN
958: IF ABS(p_allowed_amount) = ABS(p_resale_line_int_rec.claimed_amount) THEN
959: --Bug# 8418811 fixed by muthsubr(-)
960: -- No dispute in line as allowed and claimed are same
961: l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
962: l_line_tolerance_amount := 0;
963: l_tolerance_flag := 'F';
964: l_accepted_amount := p_allowed_amount;
965: ELSE
985: p_allowed_amount + l_tolerance >= p_resale_line_int_rec.claimed_amount THEN*/
986: IF ABS(p_allowed_amount) - l_tolerance <= ABS(p_resale_line_int_rec.claimed_amount) AND
987: ABS(p_allowed_amount) + l_tolerance >= ABS(p_resale_line_int_rec.claimed_amount) THEN
988: --Bug# 8418811 fixed by muthsubr(-)
989: l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
990: l_line_tolerance_amount := l_tolerance;
991: l_tolerance_flag := 'T';
992:
993: -- use system parameter flag to determine with amount assign to accepted amount
1005: ELSE
1006: l_accepted_amount := p_resale_line_int_rec.claimed_amount;
1007: END IF;
1008: ELSE
1009: l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED;
1010: l_line_tolerance_amount := l_tolerance;
1011: l_tolerance_flag := 'F';
1012: l_dispute_code := 'OZF_AMT_NOT_MATCH';
1013: l_followup_action_code := 'C';
1035: l_net_adjusted_amount := ABS(p_resale_line_int_rec.claimed_amount) - ABS(l_accepted_amount);
1036: --Bug# 8418811 fixed by muthsubr(-)
1037: ELSE
1038: --Here user did not specific the claimed amount, I will calculate it based on the request
1039: l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
1040: l_accepted_amount := p_allowed_amount;
1041: l_line_tolerance_amount := null;
1042: l_tolerance_flag := 'F';
1043: l_net_adjusted_amount := NULL;
1092: line_tolerance_amount = l_line_tolerance_amount,
1093: tolerance_flag = l_tolerance_flag,
1094: followup_action_code = l_followup_action_code,
1095: response_type = l_response_type,
1096: response_code = decode(l_status_code, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED, 'N',
1097: OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED, 'Y')
1098: WHERE resale_line_int_id = p_resale_line_int_rec.resale_line_int_id;
1099: EXCEPTION
1100: WHEN OTHERS THEN
1093: tolerance_flag = l_tolerance_flag,
1094: followup_action_code = l_followup_action_code,
1095: response_type = l_response_type,
1096: response_code = decode(l_status_code, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED, 'N',
1097: OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED, 'Y')
1098: WHERE resale_line_int_id = p_resale_line_int_rec.resale_line_int_id;
1099: EXCEPTION
1100: WHEN OTHERS THEN
1101: IF OZF_UNEXP_ERROR THEN
1271: --AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
1272: AND NVL(orlo.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
1273: AND orlo.claimed_amount = orli.claimed_amount
1274: AND NVL(orlo.bill_to_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.bill_to_cust_account_id,FND_API.G_MISS_NUM)
1275: AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
1276: AND orlo.resale_line_int_id <> p_resale_line_int_id
1277: AND orlo.resale_batch_id <> orli.resale_batch_id
1278: AND orli.resale_line_int_id = p_resale_line_int_id
1279: AND orlo.duplicated_line_id IS NULL
1308: --AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
1309: AND NVL(orlo.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
1310: AND orlo.claimed_amount = orli.claimed_amount
1311: AND NVL(orlo.bill_to_party_name,FND_API.G_MISS_NUM) = NVL(orli.bill_to_party_name,FND_API.G_MISS_NUM)
1312: AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
1313: AND orlo.resale_line_int_id <> p_resale_line_int_id
1314: AND orlo.resale_batch_id <> orli.resale_batch_id
1315: AND orli.resale_line_int_id = p_resale_line_int_id
1316: AND orlo.duplicated_line_id IS NULL
1378: AND orlo.claimed_amount = orli.claimed_amount
1379: AND orlo.bill_to_cust_account_id is null
1380: AND orlo.bill_to_party_name is null
1381: AND orlo.direct_customer_flag is null
1382: AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
1383: AND orlo.resale_line_int_id <> p_resale_line_int_id
1384: AND orlo.resale_batch_id <> orli.resale_batch_id
1385: AND orli.resale_line_int_id = p_resale_line_int_id
1386: AND orlo.duplicated_line_id IS NULL
1525: AND orlo.uom_code = orli.uom_code
1526: AND NVL(orlo.sold_from_cust_account_id, '||FND_API.G_MISS_NUM||' ) = NVL(orli.sold_from_cust_account_id, '||FND_API.G_MISS_NUM||' )
1527: AND NVL(orlo.ship_from_cust_account_id, '||FND_API.G_MISS_NUM||' ) = NVL(orli.ship_from_cust_account_id, '||FND_API.G_MISS_NUM||' )
1528: AND orlo.claimed_amount = orli.claimed_amount
1529: AND orlo.status_code in ( '''||OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN||''', '''||OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED||''' )
1530: AND orlo.duplicated_line_id IS NULL
1531: AND orlo.duplicated_adjustment_id IS NULL
1532: AND orlo.creation_date <= orli.creation_date ' ;
1533: -- For Bug#9447673 SSD IDSM ER(-)
1606: x_reprocessing := false;
1607: IF l_line_id IS NOT NULL THEN
1608: --Bug# 8414563 fixed by ateotia(+)
1609: --AMITAMKU, Fix for bug 14685303 - Added Condition for TP_ACCRUAL Batch.
1610: IF (p_batch_type = G_TRACING OR p_batch_type = OZF_RESALE_COMMON_PVT.G_TP_ACCRUAL) THEN
1611: x_reprocessing := true;
1612: -- -2 to indicate that it's a tracing batch and current line is duplicate of a resale line
1613: l_adjustment_id := -2;
1614: ELSE
1725: x_reprocessing := false;
1726: IF l_line_id IS NOT NULL THEN
1727: --Bug# 8414563 fixed by ateotia(+)
1728: --AMITAMKU, Fix for bug 14685303 - Added Condition for TP_ACCRUAL Batch.
1729: IF (p_batch_type = G_TRACING OR p_batch_type = OZF_RESALE_COMMON_PVT.G_TP_ACCRUAL) THEN
1730: x_reprocessing := true;
1731: -- -2 to indicate that it's a tracing batch and current line is duplicate of a resale line
1732: l_adjustment_id := -2;
1733: ELSE
1844: x_reprocessing := false;
1845: IF l_line_id IS NOT NULL THEN
1846: --Bug# 8414563 fixed by ateotia(+)
1847: --AMITAMKU, Fix for bug 14685303 - Added Condition for TP_ACCRUAL Batch.
1848: IF (p_batch_type = G_TRACING OR p_batch_type = OZF_RESALE_COMMON_PVT.G_TP_ACCRUAL) THEN
1849: x_reprocessing := true;
1850: -- -2 to indicate that it's a tracing batch and current line is duplicate of a resale line
1851: l_adjustment_id := -2;
1852: ELSE
2036: SELECT resale_line_int_id, direct_customer_flag, claimed_amount
2037: FROM ozf_resale_lines_int
2038: WHERE resale_batch_id = p_id
2039: --AMITAMKU, Fix for bug 14685303 - Added condition to avoid processing disputed line for duplicate check
2040: AND status_code <> OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED;
2041:
2042: CURSOR batch_count_csr(pc_batch_id NUMBER) IS
2043: SELECT NVL(batch_count,0), batch_number
2044: FROM ozf_resale_batches
2046:
2047: CURSOR duplicate_count_csr(p_id NUMBER) IS
2048: SELECT count(1)
2049: FROM ozf_resale_lines_int
2050: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
2051: AND resale_batch_id = p_id;
2052:
2053: BEGIN
2054: SAVEPOINT Update_Duplicates;
2089: , duplicated_adjustment_id = NULL
2090: WHERE resale_line_int_id = l_open_lines_tbl(i);
2091: -- BUG 4670154 (-)
2092:
2093: OZF_RESALE_COMMON_PVT.Check_Duplicate_Line (
2094: p_api_version_number => 1.0
2095: ,p_init_msg_LIST => FND_API.G_FALSE
2096: ,p_commit => FND_API.G_FALSE
2097: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2112: ELSE
2113: IF l_dup_adjustment_id IS NOT NULL AND l_reprocessing THEN
2114: -- Set the line and adjustment as duplicates
2115: UPDATE ozf_resale_lines_int
2116: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
2117: , duplicated_line_id = l_dup_line_id
2118: , duplicated_adjustment_id = l_dup_adjustment_id
2119: , dispute_code = 'OZF_RESALE_DUP'
2120: WHERE resale_line_int_id = l_open_lines_tbl(i);
2151: CLOSE batch_count_csr;
2152:
2153: IF l_duplicate_count = l_batch_count THEN
2154: -- Reject batch if all lines are duplicates
2155: x_batch_status := OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED ;
2156: --
2157: UPDATE ozf_resale_batches_all
2158: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED
2159: WHERE resale_batch_id = p_resale_batch_id;
2154: -- Reject batch if all lines are duplicates
2155: x_batch_status := OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED ;
2156: --
2157: UPDATE ozf_resale_batches_all
2158: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED
2159: WHERE resale_batch_id = p_resale_batch_id;
2160: --AMITAMKU, Fix for bug 14685303 - Added code to print the details of rejected batch in output file.
2161: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Batch# ' || l_batch_number || ' is rejected since all the lines are duplicates.');
2162:
2162:
2163: --bug # 6134121 fixed by ateotia(+)
2164: ELSIF (l_duplicate_count >=1) THEN
2165: --dispute the batch if lines are duplicated within in the same batch
2166: x_batch_status := OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED ;
2167: UPDATE ozf_resale_batches_all
2168: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
2169: WHERE resale_batch_id = p_resale_batch_id;
2170: --AMITAMKU, Fix for bug 14685303 - Added code to print the details of disputed batch in output file.
2164: ELSIF (l_duplicate_count >=1) THEN
2165: --dispute the batch if lines are duplicated within in the same batch
2166: x_batch_status := OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED ;
2167: UPDATE ozf_resale_batches_all
2168: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
2169: WHERE resale_batch_id = p_resale_batch_id;
2170: --AMITAMKU, Fix for bug 14685303 - Added code to print the details of disputed batch in output file.
2171: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Batch# ' || l_batch_number || ' is disputed since it has duplicate lines.');
2172: --bug # 6134121 fixed by ateotia(-)
2837: l_pric_act_util_rec.utilization_type := p_utilization_type;
2838: l_pric_act_util_rec.adjustment_type_id := p_adjustment_type_id;
2839:
2840: -- Reference for batch
2841: l_pric_act_util_rec.reference_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
2842: l_pric_act_util_rec.reference_id := p_line_int_rec.resale_batch_id;
2843:
2844: -- Add gl_date
2845: l_pric_act_util_rec.gl_date := p_line_int_rec.date_shipped;
2889: IF OZF_DEBUG_LOW_ON THEN
2890: OZF_UTILITY_PVT.debug_message('in exchange');
2891: END IF;
2892: -- get convertion type
2893: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2894: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_exchange_type;
2895: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2896:
2897: OZF_UTILITY_PVT.Convert_Currency (
2890: OZF_UTILITY_PVT.debug_message('in exchange');
2891: END IF;
2892: -- get convertion type
2893: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2894: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_exchange_type;
2895: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2896:
2897: OZF_UTILITY_PVT.Convert_Currency (
2898: p_from_currency => p_line_int_rec.currency_code
2891: END IF;
2892: -- get convertion type
2893: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2894: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_exchange_type;
2895: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2896:
2897: OZF_UTILITY_PVT.Convert_Currency (
2898: p_from_currency => p_line_int_rec.currency_code
2899: ,p_to_currency => l_pric_act_budgets_rec.parent_src_curr
2928: l_pric_act_budgets_rec.approver_id := OZF_UTILITY_PVT.get_resource_id (p_approver_id);
2929: l_pric_act_budgets_rec.requester_id := OZF_UTILITY_PVT.get_resource_id (p_approver_id);
2930:
2931: -- Insert INTO ozf_adjustment TABLE.
2932: OPEN OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2933: FETCH OZF_RESALE_COMMON_PVT.g_adjustment_id_csr INTO l_adjustment_id;
2934: CLOSE OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2935:
2936: l_pric_price_adj_rec.resale_adjustment_id := l_adjustment_id;
2929: l_pric_act_budgets_rec.requester_id := OZF_UTILITY_PVT.get_resource_id (p_approver_id);
2930:
2931: -- Insert INTO ozf_adjustment TABLE.
2932: OPEN OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2933: FETCH OZF_RESALE_COMMON_PVT.g_adjustment_id_csr INTO l_adjustment_id;
2934: CLOSE OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2935:
2936: l_pric_price_adj_rec.resale_adjustment_id := l_adjustment_id;
2937: l_pric_price_adj_rec.resale_batch_id := p_line_int_rec.resale_batch_id;
2930:
2931: -- Insert INTO ozf_adjustment TABLE.
2932: OPEN OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2933: FETCH OZF_RESALE_COMMON_PVT.g_adjustment_id_csr INTO l_adjustment_id;
2934: CLOSE OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2935:
2936: l_pric_price_adj_rec.resale_adjustment_id := l_adjustment_id;
2937: l_pric_price_adj_rec.resale_batch_id := p_line_int_rec.resale_batch_id;
2938: l_pric_price_adj_rec.resale_line_id := p_line_id;
2935:
2936: l_pric_price_adj_rec.resale_adjustment_id := l_adjustment_id;
2937: l_pric_price_adj_rec.resale_batch_id := p_line_int_rec.resale_batch_id;
2938: l_pric_price_adj_rec.resale_line_id := p_line_id;
2939: l_pric_price_adj_rec.status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED;
2940: l_pric_price_adj_rec.orig_system_agreement_uom := p_line_int_rec.orig_system_agreement_uom;
2941: l_pric_price_adj_rec.orig_system_agreement_name := p_line_int_rec.orig_system_agreement_name;
2942: l_pric_price_adj_rec.orig_system_agreement_type := p_line_int_rec.orig_system_agreement_type;
2943: l_pric_price_adj_rec.orig_system_agreement_status := p_line_int_rec.orig_system_agreement_status;
2979: -- and px_ozf_funds_old_rectype for taking the funds old values
2980: -- For JMS ER Bug#9614703
2981: -- and changed px_ozf_funds_new_rectype to px_ozf_funds_new_tbl.
2982:
2983: OZF_RESALE_COMMON_PVT.Create_Adj_And_Utilization(
2984: p_api_version => 1
2985: ,p_init_msg_list => FND_API.G_FALSE
2986: ,p_commit => FND_API.G_FALSE
2987: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3628: l_msg_count NUMBER;
3629: --
3630: l_dup_adjustment_id NUMBER;
3631: l_dup_total_accepted_amount NUMBER;
3632: l_line_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE := p_line_int_rec;
3633:
3634: CURSOR dup_adj_csr(p_line_id in NUMBER, p_batch_type in VARCHAR2) IS
3635: SELECT a.resale_adjustment_id,
3636: a.total_accepted_amount
3639: ozf_resale_lines c
3640: WHERE a.resale_line_id = p_line_id
3641: AND a.resale_batch_id = b.resale_batch_id
3642: AND b.batch_type = p_batch_type
3643: AND b.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
3644: AND c.resale_line_id = a.resale_line_id
3645: AND c.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
3646: -- BUG 4670154 (+)
3647: AND a.list_header_id IS NULL
3641: AND a.resale_batch_id = b.resale_batch_id
3642: AND b.batch_type = p_batch_type
3643: AND b.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
3644: AND c.resale_line_id = a.resale_line_id
3645: AND c.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
3646: -- BUG 4670154 (+)
3647: AND a.list_header_id IS NULL
3648: AND a.list_line_id IS NULL;
3649: -- BUG 4670154 (-)
3935: ELSE
3936: -- This IS a duplicate. No need to create utilization
3937: -- SLKRISHN move update to resale common pvt
3938: UPDATE ozf_resale_lines_int_all
3939: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED,
3940: duplicated_line_id = p_line_id,
3941: duplicated_adjustment_id = l_dup_adjustment_id
3942: WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
3943: END IF;
5353: -- Build Global Resale Record for Pricing Simulation
5354: --
5355: -- PARAMETERS
5356: -- p_caller_type IN VARCHAR2
5357: -- p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE
5358: -- p_resale_line_rec IN OZF_RESALE_LINES%ROWTYPE
5359: --
5360: -- NOTES
5361: --
5366: ,p_commit IN VARCHAR2
5367: ,p_validation_level IN NUMBER
5368: ,p_caller_type IN VARCHAR2
5369: ,p_line_index IN NUMBER
5370: ,p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE
5371: ,p_resale_header_rec IN OZF_RESALE_HEADERS%ROWTYPE
5372: ,p_resale_line_rec IN OZF_RESALE_LINES%ROWTYPE
5373: ,x_return_status OUT NOCOPY VARCHAR2
5374: ,x_msg_count OUT NOCOPY NUMBER
5641: END IF;
5642:
5643: x_return_status := FND_API.g_ret_sts_success;
5644:
5645: OZF_RESALE_COMMON_PVT.Derive_Bill_To_Party
5646: ( p_api_version => 1.0
5647: ,p_init_msg_list => FND_API.G_FALSE
5648: ,p_commit => FND_API.G_FALSE
5649: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
5658: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5659: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5660: END IF;
5661:
5662: OZF_RESALE_COMMON_PVT.Derive_Ship_To_Party
5663: ( p_api_version => 1.0
5664: ,p_init_msg_list => FND_API.G_FALSE
5665: ,p_commit => FND_API.G_FALSE
5666: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
5675: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5676: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5677: END IF;
5678:
5679: OZF_RESALE_COMMON_PVT.Derive_End_Cust_Party
5680: ( p_api_version => 1.0
5681: ,p_init_msg_list => FND_API.G_FALSE
5682: ,p_commit => FND_API.G_FALSE
5683: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
5778: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
5779: l_return_status VARCHAR2(30);
5780: l_msg_data VARCHAR2(2000);
5781: l_msg_count NUMBER;
5782: l_new_party_rec OZF_RESALE_COMMON_PVT.party_rec_type;
5783: l_orig_billto_count NUMBER;
5784: l_exist_billto_party_id NUMBER;
5785:
5786: CURSOR csr_orig_billto_count (cv_resale_batch_id IN NUMBER) IS
5889: l_new_party_rec.partner_party_id := NULL;
5890: END IF;
5891: END IF;
5892:
5893: OZF_RESALE_COMMON_PVT.Create_Party(
5894: p_api_version => 1.0
5895: ,p_init_msg_list => FND_API.G_FALSE
5896: ,p_commit => FND_API.G_FALSE
5897: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
6009: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
6010: l_return_status VARCHAR2(30);
6011: l_msg_data VARCHAR2(2000);
6012: l_msg_count NUMBER;
6013: l_new_party_rec OZF_RESALE_COMMON_PVT.party_rec_type;
6014: l_orig_shipto_count NUMBER;
6015: l_exist_shipto_party_id NUMBER;
6016: l_exist_billto_party_id NUMBER;
6017: l_exist_billto_party_site_id NUMBER;
6201: l_new_party_rec.postal_Code := l_orig_shipto_cust_tbl(i).ship_to_postal_code;
6202: l_new_party_rec.country := l_orig_shipto_cust_tbl(i).ship_to_country;
6203: l_new_party_rec.site_Use_Code := 'SHIP_TO';
6204:
6205: OZF_RESALE_COMMON_PVT.Create_Party(
6206: p_api_version => 1.0
6207: ,p_init_msg_list => FND_API.G_FALSE
6208: ,p_commit => FND_API.G_FALSE
6209: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
6321: l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
6322: l_return_status VARCHAR2(30);
6323: l_msg_data VARCHAR2(2000);
6324: l_msg_count NUMBER;
6325: l_new_party_rec OZF_RESALE_COMMON_PVT.party_rec_type;
6326: l_orig_end_cust_count NUMBER;
6327: l_exist_end_cust_party_id NUMBER;
6328:
6329: CURSOR csr_orig_end_cust_count (cv_resale_batch_id IN NUMBER) IS
6431: l_new_party_rec.partner_party_id := NULL;
6432: END IF;
6433: END IF;
6434:
6435: OZF_RESALE_COMMON_PVT.Create_Party(
6436: p_api_version => 1.0
6437: ,p_init_msg_list => FND_API.G_FALSE
6438: ,p_commit => FND_API.G_FALSE
6439: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
7319:
7320: ozf_utility_pvt.write_conc_log('updating batch to Closed status - resale_batch_id: ' || batch_id_int_rec.RESALE_BATCH_ID);
7321:
7322: UPDATE ozf_resale_batches_all
7323: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED,
7324: last_updated_by = NVL(fnd_global.user_id, -1),
7325: last_update_date = SYSDATE
7326: WHERE resale_batch_id = batch_id_int_rec.RESALE_BATCH_ID
7327: AND status_code in ('PENDING_CLOSE','PENDING_ACCRUALS');
7822: -- Updating the status of the batch as CLOSED in ozf_resale_batches_all table.
7823: ozf_utility_pvt.write_conc_log('Updating the status of the batch as CLOSED for Batch Id='||p_batch_id);
7824:
7825: UPDATE ozf_resale_batches_all
7826: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED,
7827: last_updated_by = NVL(fnd_global.user_id, -1),
7828: last_update_date = SYSDATE
7829: WHERE resale_batch_id = p_batch_id;
7830:
8158:
8159: END Create_Draft_Utilization;
8160:
8161:
8162: END OZF_RESALE_COMMON_PVT;