[Home] [Help]
1: PACKAGE BODY OZF_RESALE_COMMON_PVT AS
2: /* $Header: ozfvrscb.pls 120.20 2008/02/19 23:02:59 nirprasa ship $ */
3:
4: -- Package name : OZF_RESALE_COMMON_PVT
5: -- Purpose :
1: PACKAGE BODY OZF_RESALE_COMMON_PVT AS
2: /* $Header: ozfvrscb.pls 120.20 2008/02/19 23:02:59 nirprasa ship $ */
3:
4: -- Package name : OZF_RESALE_COMMON_PVT
5: -- Purpose :
6: -- HISTORY :
7: -- notE :
8: -- END of Comments
6: -- HISTORY :
7: -- notE :
8: -- END of Comments
9:
10: G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_RESALE_COMMON_PVT';
11: G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvscb.pls';
12:
13: OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_debug_high);
14: OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_debug_low);
78: CLOSE g_log_id_csr;
79:
80: -- julou bug 6317120. get org_id from table
81: IF p_id_type = 'BATCH' THEN
82: OPEN OZF_RESALE_COMMON_PVT.gc_batch_org_id(p_id_value);
83: FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
84: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
85: ELSIF p_id_type = 'LINE' THEN
86: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
79:
80: -- julou bug 6317120. get org_id from table
81: IF p_id_type = 'BATCH' THEN
82: OPEN OZF_RESALE_COMMON_PVT.gc_batch_org_id(p_id_value);
83: FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
84: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
85: ELSIF p_id_type = 'LINE' THEN
86: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
87: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
80: -- julou bug 6317120. get org_id from table
81: IF p_id_type = 'BATCH' THEN
82: OPEN OZF_RESALE_COMMON_PVT.gc_batch_org_id(p_id_value);
83: FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
84: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
85: ELSIF p_id_type = 'LINE' THEN
86: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
87: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
88: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
82: OPEN OZF_RESALE_COMMON_PVT.gc_batch_org_id(p_id_value);
83: FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
84: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
85: ELSIF p_id_type = 'LINE' THEN
86: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
87: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
88: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
89: ELSIF p_id_type = 'IFACE' THEN
90: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
83: FETCH OZF_RESALE_COMMON_PVT.gc_batch_org_id INTO l_org_id;
84: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
85: ELSIF p_id_type = 'LINE' THEN
86: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
87: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
88: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
89: ELSIF p_id_type = 'IFACE' THEN
90: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
91: FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
84: CLOSE OZF_RESALE_COMMON_PVT.gc_batch_org_id;
85: ELSIF p_id_type = 'LINE' THEN
86: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
87: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
88: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
89: ELSIF p_id_type = 'IFACE' THEN
90: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
91: FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
92: CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
86: OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_id_value);
87: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
88: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
89: ELSIF p_id_type = 'IFACE' THEN
90: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
91: FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
92: CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
93: END IF;
94:
87: FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
88: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
89: ELSIF p_id_type = 'IFACE' THEN
90: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
91: FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
92: CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
93: END IF;
94:
95: BEGIN
88: CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
89: ELSIF p_id_type = 'IFACE' THEN
90: OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_id_value);
91: FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
92: CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
93: END IF;
94:
95: BEGIN
96: OZF_RESALE_LOGS_PKG.Insert_Row(
100: p_error_code => p_error_code,
101: p_error_message => nvl(p_error_message, fnd_message.get_string('OZF',p_error_code)),
102: p_column_name => p_column_name,
103: p_column_value => p_column_value,
104: --px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
105: px_org_id => l_org_id
106: );
107: EXCEPTION
108: WHEN OTHERS THEN
638: --
639: CURSOR tolerance_line_count_csr (p_id NUMBER)IS
640: SELECT count(1)
641: FROM ozf_resale_lines_int
642: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
643: AND tolerance_flag = 'T'
644: AND resale_batch_id = p_id;
645:
646: CURSOR invalid_line_count_csr (p_id NUMBER)IS
645:
646: CURSOR invalid_line_count_csr (p_id NUMBER)IS
647: SELECT count(1)
648: FROM ozf_resale_lines_int
649: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED
650: AND dispute_code = OZF_RESALE_COMMON_PVT.G_INVALD_DISPUTE_CODE
651: AND resale_batch_id = p_id;
652:
653: CURSOR header_tolerance_csr(p_id in NUMBER) IS
646: CURSOR invalid_line_count_csr (p_id NUMBER)IS
647: SELECT count(1)
648: FROM ozf_resale_lines_int
649: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED
650: AND dispute_code = OZF_RESALE_COMMON_PVT.G_INVALD_DISPUTE_CODE
651: AND resale_batch_id = p_id;
652:
653: CURSOR header_tolerance_csr(p_id in NUMBER) IS
654: SELECT header_tolerance_operand, header_tolerance_calc_code
688: -- Initialize API return status to sucess
689: x_return_status := FND_API.G_RET_STS_SUCCESS;
690:
691: -- get data regard thIS process
692: OPEN OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr (p_resale_batch_id);
693: FETCH OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr INTO l_lines_disputed;
694: CLOSE OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr;
695:
696: OPEN tolerance_line_count_csr (p_resale_batch_id);
689: x_return_status := FND_API.G_RET_STS_SUCCESS;
690:
691: -- get data regard thIS process
692: OPEN OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr (p_resale_batch_id);
693: FETCH OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr INTO l_lines_disputed;
694: CLOSE OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr;
695:
696: OPEN tolerance_line_count_csr (p_resale_batch_id);
697: FETCH tolerance_line_count_csr INTO l_lines_w_tolerance;
690:
691: -- get data regard thIS process
692: OPEN OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr (p_resale_batch_id);
693: FETCH OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr INTO l_lines_disputed;
694: CLOSE OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr;
695:
696: OPEN tolerance_line_count_csr (p_resale_batch_id);
697: FETCH tolerance_line_count_csr INTO l_lines_w_tolerance;
698: CLOSE tolerance_line_count_csr;
705: OPEN invalid_line_count_csr(p_resale_batch_id);
706: FETCH invalid_line_count_csr INTO l_lines_invalid;
707: CLOSE invalid_line_count_csr;
708:
709: OPEN OZF_RESALE_COMMON_PVT.g_total_amount_csr (p_resale_batch_id);
710: FETCH OZF_RESALE_COMMON_PVT.g_total_amount_csr INTO l_calculated_amount,
711: l_total_claimed_amount,
712: l_total_accepted_amount,
713: l_total_allowed_amount,
706: FETCH invalid_line_count_csr INTO l_lines_invalid;
707: CLOSE invalid_line_count_csr;
708:
709: OPEN OZF_RESALE_COMMON_PVT.g_total_amount_csr (p_resale_batch_id);
710: FETCH OZF_RESALE_COMMON_PVT.g_total_amount_csr INTO l_calculated_amount,
711: l_total_claimed_amount,
712: l_total_accepted_amount,
713: l_total_allowed_amount,
714: l_total_disputed_amount;
711: l_total_claimed_amount,
712: l_total_accepted_amount,
713: l_total_allowed_amount,
714: l_total_disputed_amount;
715: CLOSE OZF_RESALE_COMMON_PVT.g_total_amount_csr;
716:
717: --bug # 6134121 fixed by ateotia(+)
718: OPEN csr_duplicated_dispute_amount(p_resale_batch_id);
719: FETCH csr_duplicated_dispute_amount INTO l_lines_duplicated
863: -- NOTES
864: --
865: ---------------------------------------------------------------------
866: PROCEDURE Update_Line_Calculations(
867: p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE,
868: p_unit_price IN NUMBER,
869: p_line_quantity IN NUMBER,
870: p_allowed_amount IN NUMBER,
871: x_return_status OUT NOCOPY VARCHAR2
935: --bug 6790803 for negative quantity
936: --IF p_allowed_amount = p_resale_line_int_rec.claimed_amount THEN
937: IF abs(p_allowed_amount) = p_resale_line_int_rec.claimed_amount THEN
938: -- No dispute in line as allowed and claimed are same
939: l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
940: l_line_tolerance_amount := 0;
941: l_tolerance_flag := 'F';
942: l_accepted_amount := p_allowed_amount;
943: ELSE
959:
960: -- Set lines that do not fall INTO tolerence as DISPUTED
961: IF p_allowed_amount - l_tolerance <= p_resale_line_int_rec.claimed_amount AND
962: p_allowed_amount + l_tolerance >= p_resale_line_int_rec.claimed_amount THEN
963: l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
964: l_line_tolerance_amount := l_tolerance;
965: l_tolerance_flag := 'T';
966:
967: -- use system parameter flag to determine with amount assign to accepted amount
979: ELSE
980: l_accepted_amount := p_resale_line_int_rec.claimed_amount;
981: END IF;
982: ELSE
983: l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED;
984: l_line_tolerance_amount := l_tolerance;
985: l_tolerance_flag := 'F';
986: l_dispute_code := 'OZF_AMT_NOT_MATCH';
987: l_followup_action_code := 'C';
1006: END IF;
1007: l_net_adjusted_amount := p_resale_line_int_rec.claimed_amount - l_accepted_amount;
1008: ELSE
1009: --Here user did not specific the claimed amount, I will calculate it based on the request
1010: l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
1011: l_accepted_amount := p_allowed_amount;
1012: l_line_tolerance_amount := null;
1013: l_tolerance_flag := 'F';
1014: l_net_adjusted_amount := NULL;
1054: line_tolerance_amount = l_line_tolerance_amount,
1055: tolerance_flag = l_tolerance_flag,
1056: followup_action_code = l_followup_action_code,
1057: response_type = l_response_type,
1058: response_code = decode(l_status_code, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED, 'N',
1059: OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED, 'Y')
1060: WHERE resale_line_int_id = p_resale_line_int_rec.resale_line_int_id;
1061: EXCEPTION
1062: WHEN OTHERS THEN
1055: tolerance_flag = l_tolerance_flag,
1056: followup_action_code = l_followup_action_code,
1057: response_type = l_response_type,
1058: response_code = decode(l_status_code, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED, 'N',
1059: OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED, 'Y')
1060: WHERE resale_line_int_id = p_resale_line_int_rec.resale_line_int_id;
1061: EXCEPTION
1062: WHEN OTHERS THEN
1063: IF OZF_UNEXP_ERROR THEN
1227: --AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
1228: AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
1229: AND orlo.claimed_amount = orli.claimed_amount
1230: AND orlo.bill_to_cust_account_id = orli.bill_to_cust_account_id
1231: AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
1232: AND orlo.resale_line_int_id <> p_resale_line_int_id
1233: AND orlo.resale_batch_id <> orli.resale_batch_id
1234: AND orli.resale_line_int_id = p_resale_line_int_id
1235: AND orlo.duplicated_line_id IS NULL
1264: --AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
1265: AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
1266: AND orlo.claimed_amount = orli.claimed_amount
1267: AND orlo.bill_to_party_name = orli.bill_to_party_name
1268: AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
1269: AND orlo.resale_line_int_id <> p_resale_line_int_id
1270: AND orlo.resale_batch_id <> orli.resale_batch_id
1271: AND orli.resale_line_int_id = p_resale_line_int_id
1272: AND orlo.duplicated_line_id IS NULL
1332: AND orlo.claimed_amount = orli.claimed_amount
1333: AND orlo.bill_to_cust_account_id is null
1334: AND orlo.bill_to_party_name is null
1335: AND orlo.direct_customer_flag is null
1336: AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
1337: AND orlo.resale_line_int_id <> p_resale_line_int_id
1338: AND orlo.resale_batch_id <> orli.resale_batch_id
1339: AND orli.resale_line_int_id = p_resale_line_int_id
1340: AND orlo.duplicated_line_id IS NULL
1646: --
1647: CURSOR open_lines_csr(p_id IN NUMBER) IS
1648: SELECT resale_line_int_id, direct_customer_flag, claimed_amount
1649: FROM ozf_resale_lines_int
1650: -- WHERE status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1651: WHERE resale_batch_id = p_id;
1652:
1653: CURSOR batch_count_csr(pc_batch_id NUMBER) IS
1654: SELECT NVL(batch_count,0)
1657:
1658: CURSOR duplicate_count_csr(p_id NUMBER) IS
1659: SELECT count(1)
1660: FROM ozf_resale_lines_int
1661: WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
1662: AND resale_batch_id = p_id;
1663:
1664: BEGIN
1665: SAVEPOINT Update_Duplicates;
1700: , duplicated_adjustment_id = NULL
1701: WHERE resale_line_int_id = l_open_lines_tbl(i);
1702: -- BUG 4670154 (-)
1703:
1704: OZF_RESALE_COMMON_PVT.Check_Duplicate_Line (
1705: p_api_version_number => 1.0
1706: ,p_init_msg_LIST => FND_API.G_FALSE
1707: ,p_commit => FND_API.G_FALSE
1708: ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1723: ELSE
1724: IF l_dup_adjustment_id IS NOT NULL AND l_reprocessing THEN
1725: -- Set the line and adjustment as duplicates
1726: UPDATE ozf_resale_lines_int
1727: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
1728: , duplicated_line_id = l_dup_line_id
1729: , duplicated_adjustment_id = l_dup_adjustment_id
1730: , dispute_code = 'OZF_RESALE_DUP'
1731: WHERE resale_line_int_id = l_open_lines_tbl(i);
1762: CLOSE batch_count_csr;
1763:
1764: IF l_duplicate_count = l_batch_count THEN
1765: -- Reject batch if all lines are duplicates
1766: x_batch_status := OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED ;
1767: --
1768: UPDATE ozf_resale_batches_all
1769: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED
1770: WHERE resale_batch_id = p_resale_batch_id;
1765: -- Reject batch if all lines are duplicates
1766: x_batch_status := OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED ;
1767: --
1768: UPDATE ozf_resale_batches_all
1769: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED
1770: WHERE resale_batch_id = p_resale_batch_id;
1771: --
1772: --bug # 6134121 fixed by ateotia(+)
1773: ELSIF (l_duplicate_count >=1) THEN
1771: --
1772: --bug # 6134121 fixed by ateotia(+)
1773: ELSIF (l_duplicate_count >=1) THEN
1774: --dispute the batch if lines are duplicated within in the same batch
1775: x_batch_status := OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED ;
1776: UPDATE ozf_resale_batches_all
1777: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
1778: WHERE resale_batch_id = p_resale_batch_id;
1779: --bug # 6134121 fixed by ateotia(-)
1773: ELSIF (l_duplicate_count >=1) THEN
1774: --dispute the batch if lines are duplicated within in the same batch
1775: x_batch_status := OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED ;
1776: UPDATE ozf_resale_batches_all
1777: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
1778: WHERE resale_batch_id = p_resale_batch_id;
1779: --bug # 6134121 fixed by ateotia(-)
1780: ELSE
1781: -- JXWU In this case we just keep the current status
1788: -- set DISPUTED_code to NULL for the lines to be processed.
1789: UPDATE ozf_resale_lines_int
1790: SET dispute_code = NULL
1791: WHERE resale_batch_id = p_resale_batch_id
1792: AND status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
1793:
1794: -- UPDATE tracing order lines to processed for this order to be processed
1795: UPDATE ozf_resale_lines_int
1796: SET status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
1792: AND status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
1793:
1794: -- UPDATE tracing order lines to processed for this order to be processed
1795: UPDATE ozf_resale_lines_int
1796: SET status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
1797: WHERE resale_batch_id = p_resale_batch_id
1798: AND status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1799: AND tracing_flag = 'T';
1800: --
1794: -- UPDATE tracing order lines to processed for this order to be processed
1795: UPDATE ozf_resale_lines_int
1796: SET status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
1797: WHERE resale_batch_id = p_resale_batch_id
1798: AND status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1799: AND tracing_flag = 'T';
1800: --
1801: EXCEPTION
1802: WHEN OTHERS THEN
2460: l_pric_act_util_rec.utilization_type := p_utilization_type;
2461: l_pric_act_util_rec.adjustment_type_id := p_adjustment_type_id;
2462:
2463: -- Reference for batch
2464: l_pric_act_util_rec.reference_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
2465: l_pric_act_util_rec.reference_id := p_line_int_rec.resale_batch_id;
2466:
2467: -- Add gl_date
2468: l_pric_act_util_rec.gl_date := p_line_int_rec.date_shipped;
2506: IF OZF_DEBUG_LOW_ON THEN
2507: OZF_UTILITY_PVT.debug_message('in exchange');
2508: END IF;
2509: -- get convertion type
2510: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2511: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_exchange_type;
2512: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2513:
2514: OZF_UTILITY_PVT.Convert_Currency (
2507: OZF_UTILITY_PVT.debug_message('in exchange');
2508: END IF;
2509: -- get convertion type
2510: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2511: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_exchange_type;
2512: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2513:
2514: OZF_UTILITY_PVT.Convert_Currency (
2515: p_from_currency => p_line_int_rec.currency_code
2508: END IF;
2509: -- get convertion type
2510: OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2511: FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_exchange_type;
2512: CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
2513:
2514: OZF_UTILITY_PVT.Convert_Currency (
2515: p_from_currency => p_line_int_rec.currency_code
2516: ,p_to_currency => l_pric_act_budgets_rec.parent_src_curr
2545: l_pric_act_budgets_rec.approver_id := OZF_UTILITY_PVT.get_resource_id (p_approver_id);
2546: l_pric_act_budgets_rec.requester_id := OZF_UTILITY_PVT.get_resource_id (p_approver_id);
2547:
2548: -- Insert INTO ozf_adjustment TABLE.
2549: OPEN OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2550: FETCH OZF_RESALE_COMMON_PVT.g_adjustment_id_csr INTO l_adjustment_id;
2551: CLOSE OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2552:
2553: l_pric_price_adj_rec.resale_adjustment_id := l_adjustment_id;
2546: l_pric_act_budgets_rec.requester_id := OZF_UTILITY_PVT.get_resource_id (p_approver_id);
2547:
2548: -- Insert INTO ozf_adjustment TABLE.
2549: OPEN OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2550: FETCH OZF_RESALE_COMMON_PVT.g_adjustment_id_csr INTO l_adjustment_id;
2551: CLOSE OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2552:
2553: l_pric_price_adj_rec.resale_adjustment_id := l_adjustment_id;
2554: l_pric_price_adj_rec.resale_batch_id := p_line_int_rec.resale_batch_id;
2547:
2548: -- Insert INTO ozf_adjustment TABLE.
2549: OPEN OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2550: FETCH OZF_RESALE_COMMON_PVT.g_adjustment_id_csr INTO l_adjustment_id;
2551: CLOSE OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
2552:
2553: l_pric_price_adj_rec.resale_adjustment_id := l_adjustment_id;
2554: l_pric_price_adj_rec.resale_batch_id := p_line_int_rec.resale_batch_id;
2555: l_pric_price_adj_rec.resale_line_id := p_line_id;
2552:
2553: l_pric_price_adj_rec.resale_adjustment_id := l_adjustment_id;
2554: l_pric_price_adj_rec.resale_batch_id := p_line_int_rec.resale_batch_id;
2555: l_pric_price_adj_rec.resale_line_id := p_line_id;
2556: l_pric_price_adj_rec.status_code := OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED;
2557: l_pric_price_adj_rec.orig_system_agreement_uom := p_line_int_rec.orig_system_agreement_uom;
2558: l_pric_price_adj_rec.orig_system_agreement_name := p_line_int_rec.orig_system_agreement_name;
2559: l_pric_price_adj_rec.orig_system_agreement_type := p_line_int_rec.orig_system_agreement_type;
2560: l_pric_price_adj_rec.orig_system_agreement_status := p_line_int_rec.orig_system_agreement_status;
2589: l_pric_price_adj_rec.priced_unit_price := p_line_int_rec.calculated_price;
2590: l_pric_price_adj_rec.liSt_header_id := NULL;
2591: l_pric_price_adj_rec.liSt_line_id := NULL;
2592:
2593: OZF_RESALE_COMMON_PVT.Create_Adj_And_Utilization(
2594: p_api_version => 1
2595: ,p_init_msg_list => FND_API.G_FALSE
2596: ,p_commit => FND_API.G_FALSE
2597: ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
2945: l_msg_count NUMBER;
2946: --
2947: l_dup_adjustment_id NUMBER;
2948: l_dup_total_accepted_amount NUMBER;
2949: l_line_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE := p_line_int_rec;
2950:
2951: CURSOR dup_adj_csr(p_line_id in NUMBER, p_batch_type in VARCHAR2) IS
2952: SELECT a.resale_adjustment_id,
2953: a.total_accepted_amount
2956: ozf_resale_lines c
2957: WHERE a.resale_line_id = p_line_id
2958: AND a.resale_batch_id = b.resale_batch_id
2959: AND b.batch_type = p_batch_type
2960: AND b.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
2961: AND c.resale_line_id = a.resale_line_id
2962: AND c.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
2963: -- BUG 4670154 (+)
2964: AND a.list_header_id IS NULL
2958: AND a.resale_batch_id = b.resale_batch_id
2959: AND b.batch_type = p_batch_type
2960: AND b.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
2961: AND c.resale_line_id = a.resale_line_id
2962: AND c.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
2963: -- BUG 4670154 (+)
2964: AND a.list_header_id IS NULL
2965: AND a.list_line_id IS NULL;
2966: -- BUG 4670154 (-)
3175: ELSE
3176: -- This IS a duplicate. No need to create utilization
3177: -- SLKRISHN move update to resale common pvt
3178: UPDATE ozf_resale_lines_int_all
3179: SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED,
3180: duplicated_line_id = p_line_id,
3181: duplicated_adjustment_id = l_dup_adjustment_id
3182: WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
3183: END IF;
4526: -- Build Global Resale Record for Pricing Simulation
4527: --
4528: -- PARAMETERS
4529: -- p_caller_type IN VARCHAR2
4530: -- p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE
4531: -- p_resale_line_rec IN OZF_RESALE_LINES%ROWTYPE
4532: --
4533: -- NOTES
4534: --
4539: ,p_commit IN VARCHAR2
4540: ,p_validation_level IN NUMBER
4541: ,p_caller_type IN VARCHAR2
4542: ,p_line_index IN NUMBER
4543: ,p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE
4544: ,p_resale_header_rec IN OZF_RESALE_HEADERS%ROWTYPE
4545: ,p_resale_line_rec IN OZF_RESALE_LINES%ROWTYPE
4546: ,x_return_status OUT NOCOPY VARCHAR2
4547: ,x_msg_count OUT NOCOPY NUMBER
4751: p_data => x_msg_data
4752: );
4753: END Build_Global_Resale_Rec;
4754:
4755: END OZF_RESALE_COMMON_PVT;