144: --
145: -- API body
146: --
147: IF l_cust_hdr_rec.org_id IS NULL THEN
148: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
149: FND_MESSAGE.set_token('ID', 'Org ID');
150: FND_MSG_PUB.add;
151: RAISE FND_API.G_EXC_ERROR;
152: ELSIF l_cust_hdr_rec.Effective_Start_Date IS NULL THEN
145: -- API body
146: --
147: IF l_cust_hdr_rec.org_id IS NULL THEN
148: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
149: FND_MESSAGE.set_token('ID', 'Org ID');
150: FND_MSG_PUB.add;
151: RAISE FND_API.G_EXC_ERROR;
152: ELSIF l_cust_hdr_rec.Effective_Start_Date IS NULL THEN
153: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
149: FND_MESSAGE.set_token('ID', 'Org ID');
150: FND_MSG_PUB.add;
151: RAISE FND_API.G_EXC_ERROR;
152: ELSIF l_cust_hdr_rec.Effective_Start_Date IS NULL THEN
153: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
154: FND_MESSAGE.set_token('ID', 'Effective Start Date');
155: FND_MSG_PUB.add;
156: RAISE FND_API.G_EXC_ERROR;
157: ELSIF l_cust_hdr_rec.Effective_End_Date IS NULL THEN
150: FND_MSG_PUB.add;
151: RAISE FND_API.G_EXC_ERROR;
152: ELSIF l_cust_hdr_rec.Effective_Start_Date IS NULL THEN
153: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
154: FND_MESSAGE.set_token('ID', 'Effective Start Date');
155: FND_MSG_PUB.add;
156: RAISE FND_API.G_EXC_ERROR;
157: ELSIF l_cust_hdr_rec.Effective_End_Date IS NULL THEN
158: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
154: FND_MESSAGE.set_token('ID', 'Effective Start Date');
155: FND_MSG_PUB.add;
156: RAISE FND_API.G_EXC_ERROR;
157: ELSIF l_cust_hdr_rec.Effective_End_Date IS NULL THEN
158: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
159: FND_MESSAGE.set_token('ID', 'Effective End Date');
160: FND_MSG_PUB.add;
161: RAISE FND_API.G_EXC_ERROR;
162: ELSIF l_cust_hdr_rec.currency_code IS NULL THEN
155: FND_MSG_PUB.add;
156: RAISE FND_API.G_EXC_ERROR;
157: ELSIF l_cust_hdr_rec.Effective_End_Date IS NULL THEN
158: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
159: FND_MESSAGE.set_token('ID', 'Effective End Date');
160: FND_MSG_PUB.add;
161: RAISE FND_API.G_EXC_ERROR;
162: ELSIF l_cust_hdr_rec.currency_code IS NULL THEN
163: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
159: FND_MESSAGE.set_token('ID', 'Effective End Date');
160: FND_MSG_PUB.add;
161: RAISE FND_API.G_EXC_ERROR;
162: ELSIF l_cust_hdr_rec.currency_code IS NULL THEN
163: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
164: FND_MESSAGE.set_token('ID', 'Currency Code');
165: FND_MSG_PUB.add;
166: RAISE FND_API.G_EXC_ERROR;
167: ELSE
160: FND_MSG_PUB.add;
161: RAISE FND_API.G_EXC_ERROR;
162: ELSIF l_cust_hdr_rec.currency_code IS NULL THEN
163: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
164: FND_MESSAGE.set_token('ID', 'Currency Code');
165: FND_MSG_PUB.add;
166: RAISE FND_API.G_EXC_ERROR;
167: ELSE
168: IF l_customer_tbl.EXISTS(1) THEN
167: ELSE
168: IF l_customer_tbl.EXISTS(1) THEN
169: FOR i IN l_customer_tbl.FIRST..l_customer_tbl.LAST LOOP
170: IF l_customer_tbl(i).inventory_item_id IS NULL THEN
171: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
172: FND_MESSAGE.set_token('ID', 'Inventory Item Id');
173: FND_MSG_PUB.add;
174: RAISE FND_API.G_EXC_ERROR;
175: ELSIF l_customer_tbl(i).uom_code IS NULL THEN
168: IF l_customer_tbl.EXISTS(1) THEN
169: FOR i IN l_customer_tbl.FIRST..l_customer_tbl.LAST LOOP
170: IF l_customer_tbl(i).inventory_item_id IS NULL THEN
171: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
172: FND_MESSAGE.set_token('ID', 'Inventory Item Id');
173: FND_MSG_PUB.add;
174: RAISE FND_API.G_EXC_ERROR;
175: ELSIF l_customer_tbl(i).uom_code IS NULL THEN
176: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
172: FND_MESSAGE.set_token('ID', 'Inventory Item Id');
173: FND_MSG_PUB.add;
174: RAISE FND_API.G_EXC_ERROR;
175: ELSIF l_customer_tbl(i).uom_code IS NULL THEN
176: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
177: FND_MESSAGE.set_token('ID', 'UOM Code');
178: FND_MSG_PUB.add;
179: RAISE FND_API.G_EXC_ERROR;
180: ELSE --Inventory item id null
173: FND_MSG_PUB.add;
174: RAISE FND_API.G_EXC_ERROR;
175: ELSIF l_customer_tbl(i).uom_code IS NULL THEN
176: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
177: FND_MESSAGE.set_token('ID', 'UOM Code');
178: FND_MSG_PUB.add;
179: RAISE FND_API.G_EXC_ERROR;
180: ELSE --Inventory item id null
181: l_customer_price_tbl.delete();
213: l_customer_tbl(i).customer_price_tbl := l_customer_price_tbl;
214: END IF; --Inventory item id null
215: END LOOP;
216: ELSE --No line details available
217: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
218: FND_MESSAGE.set_token('ID', 'Line Details');
219: FND_MSG_PUB.add;
220: RAISE FND_API.G_EXC_ERROR;
221: END IF;
214: END IF; --Inventory item id null
215: END LOOP;
216: ELSE --No line details available
217: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
218: FND_MESSAGE.set_token('ID', 'Line Details');
219: FND_MSG_PUB.add;
220: RAISE FND_API.G_EXC_ERROR;
221: END IF;
222:
274:
275: WHEN OTHERS THEN
276: ROLLBACK TO Select_CustomerPrice_PVT;
277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
279: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Select_CustomerPrice');
280: fnd_message.set_token('ERRNO', sqlcode);
281: fnd_message.set_token('REASON', sqlerrm);
282: FND_MSG_PUB.add;
275: WHEN OTHERS THEN
276: ROLLBACK TO Select_CustomerPrice_PVT;
277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
279: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Select_CustomerPrice');
280: fnd_message.set_token('ERRNO', sqlcode);
281: fnd_message.set_token('REASON', sqlerrm);
282: FND_MSG_PUB.add;
283: -- Standard call to get message count and if count=1, get the message
276: ROLLBACK TO Select_CustomerPrice_PVT;
277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
279: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Select_CustomerPrice');
280: fnd_message.set_token('ERRNO', sqlcode);
281: fnd_message.set_token('REASON', sqlerrm);
282: FND_MSG_PUB.add;
283: -- Standard call to get message count and if count=1, get the message
284: FND_MSG_PUB.Count_And_Get (
277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
279: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Select_CustomerPrice');
280: fnd_message.set_token('ERRNO', sqlcode);
281: fnd_message.set_token('REASON', sqlerrm);
282: FND_MSG_PUB.add;
283: -- Standard call to get message count and if count=1, get the message
284: FND_MSG_PUB.Count_And_Get (
285: p_encoded => FND_API.G_FALSE,
392: l_rounding := 4;
393: END IF;
394:
395: IF l_cust_hdr_rec.org_id IS NULL THEN
396: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
397: FND_MESSAGE.set_token('ID', 'Org Id');
398: FND_MSG_PUB.add;
399: RAISE FND_API.G_EXC_ERROR;
400: ELSIF l_cust_hdr_rec.effective_start_date IS NULL THEN
393: END IF;
394:
395: IF l_cust_hdr_rec.org_id IS NULL THEN
396: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
397: FND_MESSAGE.set_token('ID', 'Org Id');
398: FND_MSG_PUB.add;
399: RAISE FND_API.G_EXC_ERROR;
400: ELSIF l_cust_hdr_rec.effective_start_date IS NULL THEN
401: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
397: FND_MESSAGE.set_token('ID', 'Org Id');
398: FND_MSG_PUB.add;
399: RAISE FND_API.G_EXC_ERROR;
400: ELSIF l_cust_hdr_rec.effective_start_date IS NULL THEN
401: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
402: FND_MESSAGE.set_token('ID', 'Effective Start Date');
403: FND_MSG_PUB.add;
404: RAISE FND_API.G_EXC_ERROR;
405: ELSIF l_cust_hdr_rec.effective_end_date IS NULL THEN
398: FND_MSG_PUB.add;
399: RAISE FND_API.G_EXC_ERROR;
400: ELSIF l_cust_hdr_rec.effective_start_date IS NULL THEN
401: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
402: FND_MESSAGE.set_token('ID', 'Effective Start Date');
403: FND_MSG_PUB.add;
404: RAISE FND_API.G_EXC_ERROR;
405: ELSIF l_cust_hdr_rec.effective_end_date IS NULL THEN
406: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
402: FND_MESSAGE.set_token('ID', 'Effective Start Date');
403: FND_MSG_PUB.add;
404: RAISE FND_API.G_EXC_ERROR;
405: ELSIF l_cust_hdr_rec.effective_end_date IS NULL THEN
406: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
407: FND_MESSAGE.set_token('ID', 'Effective End Date');
408: FND_MSG_PUB.add;
409: RAISE FND_API.G_EXC_ERROR;
410: ELSIF l_cust_hdr_rec.currency_code IS NULL THEN
403: FND_MSG_PUB.add;
404: RAISE FND_API.G_EXC_ERROR;
405: ELSIF l_cust_hdr_rec.effective_end_date IS NULL THEN
406: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
407: FND_MESSAGE.set_token('ID', 'Effective End Date');
408: FND_MSG_PUB.add;
409: RAISE FND_API.G_EXC_ERROR;
410: ELSIF l_cust_hdr_rec.currency_code IS NULL THEN
411: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
407: FND_MESSAGE.set_token('ID', 'Effective End Date');
408: FND_MSG_PUB.add;
409: RAISE FND_API.G_EXC_ERROR;
410: ELSIF l_cust_hdr_rec.currency_code IS NULL THEN
411: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
412: FND_MESSAGE.set_token('ID', 'Currency Code');
413: FND_MSG_PUB.add;
414: RAISE FND_API.G_EXC_ERROR;
415: ELSE
408: FND_MSG_PUB.add;
409: RAISE FND_API.G_EXC_ERROR;
410: ELSIF l_cust_hdr_rec.currency_code IS NULL THEN
411: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
412: FND_MESSAGE.set_token('ID', 'Currency Code');
413: FND_MSG_PUB.add;
414: RAISE FND_API.G_EXC_ERROR;
415: ELSE
416: IF l_customer_tbl.EXISTS(1) THEN
417: IF l_profile_option_value = 'Y' OR l_profile_option_value IS NULL THEN
418: FOR i IN l_customer_tbl.FIRST..l_customer_tbl.LAST LOOP
419: l_supp_new_price := 0;
420: IF l_customer_tbl(i).inventory_item_id IS NULL THEN
421: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
422: FND_MESSAGE.set_token('ID', 'Inventory Item Id');
423: FND_MSG_PUB.add;
424: RAISE FND_API.G_EXC_ERROR;
425: ELSE
418: FOR i IN l_customer_tbl.FIRST..l_customer_tbl.LAST LOOP
419: l_supp_new_price := 0;
420: IF l_customer_tbl(i).inventory_item_id IS NULL THEN
421: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
422: FND_MESSAGE.set_token('ID', 'Inventory Item Id');
423: FND_MSG_PUB.add;
424: RAISE FND_API.G_EXC_ERROR;
425: ELSE
426: BEGIN
568: 'N',
569: 'N');
570: EXCEPTION
571: WHEN OTHERS THEN
572: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
573: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Populate_CustomerPrice');
574: fnd_message.set_token('ERRNO', sqlcode);
575: fnd_message.set_token('REASON', sqlerrm);
576: fnd_msg_pub.add;
569: 'N');
570: EXCEPTION
571: WHEN OTHERS THEN
572: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
573: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Populate_CustomerPrice');
574: fnd_message.set_token('ERRNO', sqlcode);
575: fnd_message.set_token('REASON', sqlerrm);
576: fnd_msg_pub.add;
577: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
570: EXCEPTION
571: WHEN OTHERS THEN
572: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
573: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Populate_CustomerPrice');
574: fnd_message.set_token('ERRNO', sqlcode);
575: fnd_message.set_token('REASON', sqlerrm);
576: fnd_msg_pub.add;
577: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
578: END;
571: WHEN OTHERS THEN
572: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
573: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Populate_CustomerPrice');
574: fnd_message.set_token('ERRNO', sqlcode);
575: fnd_message.set_token('REASON', sqlerrm);
576: fnd_msg_pub.add;
577: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
578: END;
579: -- Debug Message
598: l_claim_lines_tbl(i).org_id := l_cust_hdr_rec.org_id;
599: END IF; --If cust account id is not null
600: END LOOP; --customer_price_tbl loop
601: ELSE --customer_price_tbl exists
602: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
603: FND_MESSAGE.set_token('ID', 'Line Details');
604: FND_MSG_PUB.add;
605: RAISE FND_API.G_EXC_ERROR;
606: END IF; --customer_price_tbl exists
599: END IF; --If cust account id is not null
600: END LOOP; --customer_price_tbl loop
601: ELSE --customer_price_tbl exists
602: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
603: FND_MESSAGE.set_token('ID', 'Line Details');
604: FND_MSG_PUB.add;
605: RAISE FND_API.G_EXC_ERROR;
606: END IF; --customer_price_tbl exists
607: END IF; --inventory_item_id is not null
639: dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, l_api_name||': Exe Detail ID: '||l_cust_hdr_rec.Execution_Detail_ID);
640: dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, l_api_name|| ': '||SQL%ROWCOUNT ||' row(s) updated in DPP_EXECUTION_DETAILS.');
641:
642: ELSE
643: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
644: FND_MESSAGE.set_token('ID', 'Line Details');
645: FND_MSG_PUB.add;
646: RAISE FND_API.G_EXC_ERROR;
647: END IF; --l_customer_tbl.EXISTS(1)
640: dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, l_api_name|| ': '||SQL%ROWCOUNT ||' row(s) updated in DPP_EXECUTION_DETAILS.');
641:
642: ELSE
643: FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
644: FND_MESSAGE.set_token('ID', 'Line Details');
645: FND_MSG_PUB.add;
646: RAISE FND_API.G_EXC_ERROR;
647: END IF; --l_customer_tbl.EXISTS(1)
648: END IF; --l_cust_hdr_rec.org_id IS NULL
718:
719: WHEN OTHERS THEN
720: ROLLBACK TO Populate_CustomerPrice_PVT;
721: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
723: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Populate_CustomerPrice');
724: fnd_message.set_token('ERRNO', sqlcode);
725: fnd_message.set_token('REASON', sqlerrm);
726: FND_MSG_PUB.ADD;
719: WHEN OTHERS THEN
720: ROLLBACK TO Populate_CustomerPrice_PVT;
721: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
723: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Populate_CustomerPrice');
724: fnd_message.set_token('ERRNO', sqlcode);
725: fnd_message.set_token('REASON', sqlerrm);
726: FND_MSG_PUB.ADD;
727: dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Error in inserting into DPP_CUSTOMER_CLAIMS_ALL: '||SQLERRM);
720: ROLLBACK TO Populate_CustomerPrice_PVT;
721: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
723: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Populate_CustomerPrice');
724: fnd_message.set_token('ERRNO', sqlcode);
725: fnd_message.set_token('REASON', sqlerrm);
726: FND_MSG_PUB.ADD;
727: dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Error in inserting into DPP_CUSTOMER_CLAIMS_ALL: '||SQLERRM);
728:
721: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722: fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
723: fnd_message.set_token('ROUTINE', 'DPP_CUSTOMERCLAIMS_PVT.Populate_CustomerPrice');
724: fnd_message.set_token('ERRNO', sqlcode);
725: fnd_message.set_token('REASON', sqlerrm);
726: FND_MSG_PUB.ADD;
727: dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Error in inserting into DPP_CUSTOMER_CLAIMS_ALL: '||SQLERRM);
728:
729: -- Standard call to get message count and if count=1, get the message