[Home] [Help]
1: PACKAGE BODY GMF_get_transfer_price_PUB AS
2: /* $Header: GMFGXFRB.pls 120.19.12020000.4 2012/10/18 10:20:38 smukalla ship $ */
3:
4: G_PACKAGE_NAME VARCHAR2(50) := 'GMF_get_transfer_price_PUB';
5: --===================================================================
1: PACKAGE BODY GMF_get_transfer_price_PUB AS
2: /* $Header: GMFGXFRB.pls 120.19.12020000.4 2012/10/18 10:20:38 smukalla ship $ */
3:
4: G_PACKAGE_NAME VARCHAR2(50) := 'GMF_get_transfer_price_PUB';
5: --===================================================================
6: --
7: -- Following G_ global variables will be used for Advanced Pricing
8: --
197:
198: l_transaction_date DATE DEFAULT NULL; -- Bug13626813
199: BEGIN
200:
201: l_api_name := 'GMF_get_transfer_price_PUB.get_transfer_price';
202: l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
203: x_return_status := FND_API.G_RET_STS_SUCCESS;
204:
205: print_debug(l_api_name || ' Begin Input Parameters ');
512: --===================================================================
513:
514: print_debug(l_api_name || ': Calling Basic Pricing...');
515:
516: GMF_get_transfer_price_PUB.get_xfer_price_basic (
517: x_transfer_price => l_transfer_price
518: , x_transfer_price_code => l_transfer_price_code
519: , x_pricelist_currency => l_pricelist_currency
520: , x_return_status => x_return_status
527: THEN
528:
529: x_msg_data := FND_MESSAGE.get;
530: IF (l_debug = 1) THEN
531: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_basic: Error = '|| x_msg_data );
532: END IF;
533: RAISE FND_API.G_EXC_ERROR;
534:
535: ELSE
535: ELSE
536:
537: IF l_transfer_price IS NULL
538: THEN
539: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_basic: Transfer Price is NULL: '|| x_msg_data );
540: RAISE e_transfer_price_null_error;
541: END IF;
542:
543: END IF;
555: --
556: --===================================================================
557:
558: print_debug(l_api_name || ': Calling Advanced Pricing...');
559: GMF_get_transfer_price_PUB.get_xfer_price_qp (
560: x_transfer_price => l_transfer_price
561: , x_currency_code => l_pricelist_currency
562: , x_transfer_price_code => l_transfer_price_code
563: , x_return_status => x_return_status
570: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
571: THEN
572:
573: IF (l_debug = 1) THEN
574: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Error = '|| x_msg_data );
575: END IF;
576: RAISE FND_API.G_EXC_ERROR;
577:
578: ELSE
578: ELSE
579:
580: IF l_transfer_price IS NULL
581: THEN
582: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Transfer Price is NULL: '|| x_msg_data );
583: RAISE e_transfer_price_null_error;
584: END IF;
585:
586: END IF;
737: x_return_status := FND_API.G_RET_STS_ERROR;
738:
739: WHEN e_currency_conversion_error
740: THEN
741: print_debug('GMF_get_transfer_price_PUB.get_transfer_price: currency conversion error');
742: x_return_status := FND_API.G_RET_STS_ERROR;
743: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
744: x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
745:
745:
746:
747: WHEN FND_API.G_EXC_UNEXPECTED_ERROR
748: THEN
749: print_debug('Exc_Unexpected_Error in GMF_get_transfer_price_PUB.get_transfer_price');
750: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
751: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
752: x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
753:
752: x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
753:
754: WHEN FND_API.G_EXC_ERROR
755: THEN
756: print_debug('EXC_ERROR in GMF_get_transfer_price_PUB.get_transfer_price: ' || x_msg_data);
757: x_return_status := FND_API.G_RET_STS_ERROR;
758: IF x_msg_data IS NULL
759: THEN
760: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
794:
795: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
796: FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, l_api_name);
797: end if;
798: print_debug('When Other in GMF_get_transfer_price_PUB.get_transfer_price (sqlerrm): ' || sqlerrm);
799: print_debug('When Other in GMF_get_transfer_price_PUB.get_transfer_price (backtrace): ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
800:
801: END get_transfer_price;
802:
795: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
796: FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, l_api_name);
797: end if;
798: print_debug('When Other in GMF_get_transfer_price_PUB.get_transfer_price (sqlerrm): ' || sqlerrm);
799: print_debug('When Other in GMF_get_transfer_price_PUB.get_transfer_price (backtrace): ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
800:
801: END get_transfer_price;
802:
803: --
868: e_item_not_on_pricelist exception;
869:
870: BEGIN
871:
872: l_api_name := 'GMF_get_transfer_price_PUB.get_xfer_price_basic';
873:
874: print_debug(' ' || l_api_name || ': Basic pricing. Get price for item: ' || g_inventory_item_id ||
875: ' org: ' || g_from_organization_id);
876: --
1114:
1115: begin
1116:
1117: IF (l_debug = 1) THEN
1118: Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: xfr source: ' || g_xfer_source);
1119: END IF;
1120:
1121: -- QP: Pricing Transaction Entity => Inter Organization Transfers for Internal Orders
1122: -- QP: Source System Code => Oracle Inventory
1138:
1139: INV_IC_ORDER_PUB.G_LINE.from_organization_id := g_from_organization_id;
1140: INV_IC_ORDER_PUB.G_LINE.to_organization_id := g_to_organization_id;
1141:
1142: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Calling MTL_QP_PRICE.get_transfer_price' ||
1143: ' with From Orgn: ' || g_from_organization_id ||
1144: ' To Orgn: ' || g_to_organization_id ||
1145: ' for order line id: ' || g_transaction_id ||
1146: ' itemID: ' || g_inventory_item_id);
1166: );
1167:
1168: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1169: IF (l_debug = 1) THEN
1170: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: MTL_QP_PRICE.get_transfer_price error ' );
1171: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Error ='|| x_msg_data );
1172: END IF;
1173: RAISE FND_API.G_EXC_ERROR;
1174: END IF;
1167:
1168: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1169: IF (l_debug = 1) THEN
1170: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: MTL_QP_PRICE.get_transfer_price error ' );
1171: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Error ='|| x_msg_data );
1172: END IF;
1173: RAISE FND_API.G_EXC_ERROR;
1174: END IF;
1175:
1172: END IF;
1173: RAISE FND_API.G_EXC_ERROR;
1174: END IF;
1175:
1176: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: ' ||
1177: 'Transfer Price from MTL_QP_PRICE.get_transfer_price: ' || x_transfer_price);
1178:
1179: ELSIF g_xfer_source in ('INTORG', 'INTREQ')
1180: THEN
1186:
1187: -- l_request_type_code := 'INVXFR';
1188:
1189: IF (l_debug = 1) THEN
1190: Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp:: Selecting Line Identifier...');
1191: END IF;
1192:
1193: -- l_organization_id := g_from_organization_id;
1194: l_transaction_uom := g_transaction_uom;
1205: , p_Request_Type_Code => l_request_type_code
1206: , px_line_index => l_line_index );
1207:
1208: IF (l_debug = 1) THEN
1209: Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Build Context for header...');
1210: END IF;
1211:
1212: QP_Attr_Mapping_PUB.Build_Contexts(
1213: p_request_type_code => l_request_type_code
1215: , p_line_index => INV_IC_ORDER_PUB.g_txn_hdr.dummy );
1216: */
1217:
1218: IF (l_debug = 1) THEN
1219: Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: calling G_Line_Initialize...');
1220: END IF;
1221:
1222: G_Line_Initialize;
1223: QP_price_request_context.set_request_id;
1222: G_Line_Initialize;
1223: QP_price_request_context.set_request_id;
1224:
1225: IF (l_debug = 1) THEN
1226: Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: calling copy_Line_to_request...');
1227: END IF;
1228:
1229: --
1230: -- Copy line to request
1238: --
1239: -- Build Context
1240: --
1241: IF (l_debug = 1) THEN
1242: Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Build Context for line...');
1243: END IF;
1244:
1245: QP_Attr_Mapping_PUB.Build_Contexts(
1246: p_request_type_code => l_request_type_code
1252: -- Populate temp table
1253: --
1254: IF l_line_index > 0 THEN
1255: IF (l_debug = 1) THEN
1256: Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Populating Lines temp table...');
1257: END IF;
1258: Populate_Temp_Table(x_return_status);
1259: END IF;
1260:
1261: --
1262: -- Initializing control record
1263: --
1264: IF (l_debug = 1) THEN
1265: Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Initializing control record...');
1266: END IF;
1267:
1268: l_control_rec.pricing_event := l_pricing_event;
1269: l_control_rec.calculate_flag := qp_preq_grp.G_SEARCH_N_CALCULATE;
1275: --
1276: -- All set. Call Adv. Pricing Engine.
1277: --
1278: IF (l_debug = 1) THEN
1279: Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Calling QP:Price Request routine ...');
1280: END IF;
1281:
1282: QP_PREQ_PUB.PRICE_REQUEST( p_control_rec => l_control_rec
1283: , x_return_status => x_return_status
1284: , x_return_status_Text => l_return_status_Text);
1285:
1286: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1287: IF (l_debug = 1) THEN
1288: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: QP_PREQ_PUB.PRICE_REQUEST error ' );
1289: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: x_return_status_text='|| l_return_status_text );
1290: END IF;
1291: fnd_message.set_name('INV', 'INV_UNHANDLED_ERR');
1292: fnd_message.set_token('ENTITY1', 'QP_PREQ_PUB.PRICE_REQUEST');
1285:
1286: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1287: IF (l_debug = 1) THEN
1288: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: QP_PREQ_PUB.PRICE_REQUEST error ' );
1289: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: x_return_status_text='|| l_return_status_text );
1290: END IF;
1291: fnd_message.set_name('INV', 'INV_UNHANDLED_ERR');
1292: fnd_message.set_token('ENTITY1', 'QP_PREQ_PUB.PRICE_REQUEST');
1293: fnd_message.set_token('ENTITY2', substr(l_return_status_text, 1, 150) );
1294: RAISE FND_API.G_EXC_ERROR;
1295: END IF;
1296:
1297: IF (l_debug = 1) THEN
1298: Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Populating QP results ...');
1299: END IF;
1300:
1301: --
1302: -- Populating results
1335: WHEN NO_DATA_FOUND
1336: THEN
1337: IF (l_debug = 1)
1338: THEN
1339: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: EXCEP NO_DATA_FOUND ' );
1340: END IF;
1341: fnd_message.set_name('INV', 'INV_NO_DATA_EXISTS');
1342:
1343: WHEN FND_API.G_EXC_ERROR
1343: WHEN FND_API.G_EXC_ERROR
1344: THEN
1345: IF (l_debug = 1)
1346: THEN
1347: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: EXCEP G_EXC_ERROR ' );
1348: END IF;
1349: WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1350: THEN
1351: IF (l_debug = 1)
1349: WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1350: THEN
1351: IF (l_debug = 1)
1352: THEN
1353: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: EXCEP G_EXC_UNEXPECTED_ERROR ' );
1354: END IF;
1355: WHEN OTHERS
1356: THEN
1357: IF (l_debug = 1)
1355: WHEN OTHERS
1356: THEN
1357: IF (l_debug = 1)
1358: THEN
1359: print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: EXCEP UNEXP OTHERS - ' || sqlerrm);
1360: END IF;
1361: END get_xfer_price_qp;
1362:
1363: --
1370: -- x_return_status := FND_API.G_RET_STS_SUCCESS;
1371:
1372: -- Header population
1373: IF (l_debug = 1) THEN
1374: print_debug('GMF_get_transfer_price_PUB.G_Hdr_Initialize: Populating G_HDR...');
1375: END IF;
1376:
1377: --
1378: -- For InterOrg Transfers header is not supported.
1384: EXCEPTION
1385: WHEN OTHERS THEN
1386: -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1387: IF (l_debug = 1) THEN
1388: print_debug('GMF_get_transfer_price_PUB.G_Hdr_Initialize: EXCEP UNEXP OTHERS - ' || sqlerrm);
1389: END IF;
1390: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1391:
1392: END G_Hdr_Initialize;
1397: BEGIN
1398:
1399: -- Line population
1400: IF (l_debug = 1) THEN
1401: print_debug('GMF_get_transfer_price_PUB.G_Line_Initialize: Populating G_LINE...');
1402: END IF;
1403:
1404: INV_IC_ORDER_PUB.G_LINE.from_organization_id := G_from_organization_id;
1405: INV_IC_ORDER_PUB.G_LINE.to_organization_id := G_to_organization_id;
1414:
1415: INV_IC_ORDER_PUB.G_LINE.calculate_price_flag := 'Y';
1416:
1417: IF (l_debug = 1) THEN
1418: print_debug('GMF_get_transfer_price_PUB.G_Line_Initialize: item/qty/uom: ' ||
1419: INV_IC_ORDER_PUB.G_LINE.inventory_item_id ||'/'|| INV_IC_ORDER_PUB.G_LINE.ordered_quantity ||'/'||
1420: INV_IC_ORDER_PUB.G_LINE.order_quantity_uom);
1421: END IF;
1422:
1424:
1425: WHEN NO_DATA_FOUND THEN
1426: -- x_return_status := FND_API.G_RET_STS_ERROR;
1427: IF (l_debug = 1) THEN
1428: print_debug('GMF_get_transfer_price_PUB.G_Line_Initialize: EXCEP NO_DATA_FOUND ' );
1429: END IF;
1430: RAISE NO_DATA_FOUND;
1431: WHEN OTHERS THEN
1432: -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1430: RAISE NO_DATA_FOUND;
1431: WHEN OTHERS THEN
1432: -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1433: IF (l_debug = 1) THEN
1434: print_debug('GMF_get_transfer_price_PUB.G_Line_Initialize: EXCEP UNEXP OTHERS - ' || sqlerrm);
1435: END IF;
1436: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1437:
1438: END G_Line_Initialize;
1605: END IF;
1606: END LOOP;
1607:
1608: IF (l_debug = 1) THEN
1609: print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: Calling QP:Bulk insert routine...' );
1610: END IF;
1611:
1612: QP_PREQ_GRP.INSERT_LINES2 (
1613: p_LINE_INDEX => G_LINE_INDEX_TBL,
1645: p_LINE_CATEGORY => G_LINE_CATEGORY_TBL,
1646: x_status_code => l_return_status,
1647: x_status_text => l_return_status_text );
1648:
1649: print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: after QP:Bulk insert routine...' );
1650:
1651: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1652: IF (l_debug = 1) THEN
1653: print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: QP_PREQ_GRP.INSERT_LINES2 error ' );
1649: print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: after QP:Bulk insert routine...' );
1650:
1651: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1652: IF (l_debug = 1) THEN
1653: print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: QP_PREQ_GRP.INSERT_LINES2 error ' );
1654: print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: x_return_status_text='|| l_return_status_text );
1655: END IF;
1656: x_return_status := l_return_status;
1657: fnd_message.set_name('INV', 'INV_UNHANDLED_ERR');
1650:
1651: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1652: IF (l_debug = 1) THEN
1653: print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: QP_PREQ_GRP.INSERT_LINES2 error ' );
1654: print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: x_return_status_text='|| l_return_status_text );
1655: END IF;
1656: x_return_status := l_return_status;
1657: fnd_message.set_name('INV', 'INV_UNHANDLED_ERR');
1658: fnd_message.set_token('ENTITY1', 'QP_PREQ_GRP.INSERT_LINES2');
1664: INTO i
1665: FROM qp_preq_lines_tmp;
1666:
1667: IF (l_debug = 1) THEN
1668: print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: No. of records inserted in QP_PREQ_LINES_TMP=' || to_char(i));
1669: END IF;
1670:
1671: EXCEPTION
1672: WHEN FND_API.G_EXC_ERROR THEN
1673: RAISE FND_API.G_EXC_ERROR;
1674: WHEN OTHERS THEN
1675: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1676: IF (l_debug = 1) THEN
1677: print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: EXCEP UNEXP OTHERS - ' || sqlerrm);
1678: END IF;
1679:
1680: END Populate_Temp_Table;
1681:
1727: WHEN NO_DATA_FOUND THEN
1728: IF G_LINE_TYPE_CODE_TBL(i) = 'LINE' THEN
1729: x_return_status := FND_API.G_RET_STS_ERROR;
1730: IF (l_debug = 1) THEN
1731: print_debug('GMF_get_transfer_price_PUB.Populate_Results: UNIT PRICE NOT POPULATED');
1732: END IF;
1733: ELSE
1734: IF (l_debug = 1) THEN
1735: print_debug('GMF_get_transfer_price_PUB.Populate_Results: ' || G_LINE_TYPE_CODE_TBL(i) || ' NO_DATA_FOUND');
1731: print_debug('GMF_get_transfer_price_PUB.Populate_Results: UNIT PRICE NOT POPULATED');
1732: END IF;
1733: ELSE
1734: IF (l_debug = 1) THEN
1735: print_debug('GMF_get_transfer_price_PUB.Populate_Results: ' || G_LINE_TYPE_CODE_TBL(i) || ' NO_DATA_FOUND');
1736: END IF;
1737: END IF;
1738:
1739: WHEN OTHERS THEN
1738:
1739: WHEN OTHERS THEN
1740: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1741: IF (l_debug = 1) THEN
1742: print_debug('GMF_get_transfer_price_PUB.Populate_Results: ' || sqlerrm );
1743: END IF;
1744: RAISE;
1745: END;
1746:
1746:
1747: IF G_LINE_TYPE_CODE_TBL(i) = 'LINE' THEN
1748: IF G_PRICING_STATUS_CODE_TBL(i) = QP_PREQ_GRP.G_STATUS_UPDATED THEN
1749: IF (l_debug = 1) THEN
1750: print_debug('GMF_get_transfer_price_PUB.Populate_Results: Unit_Price=' || G_UNIT_SELLING_PRICE_TBL(i));
1751: END IF;
1752: ELSE
1753: x_return_status := FND_API.G_RET_STS_ERROR;
1754: x_msg_data := G_PRICING_STATUS_TEXT_TBL(i);
1753: x_return_status := FND_API.G_RET_STS_ERROR;
1754: x_msg_data := G_PRICING_STATUS_TEXT_TBL(i);
1755:
1756: IF (l_debug = 1) THEN
1757: print_debug('GMF_get_transfer_price_PUB.Populate_Results: Status_Code=' || G_PRICING_STATUS_CODE_TBL(i) ||
1758: ' Status_Text=' || G_PRICING_STATUS_TEXT_TBL(i));
1759: END IF;
1760: END IF;
1761: END IF;
1788:
1789: EXCEPTION
1790: WHEN FND_API.G_EXC_ERROR
1791: THEN
1792: print_debug('EXC_ERROR in GMF_get_transfer_price_PUB.Populate_Results');
1793: x_return_status := FND_API.G_RET_STS_ERROR;
1794: RAISE FND_API.G_EXC_ERROR;
1795:
1796: WHEN OTHERS
1796: WHEN OTHERS
1797: THEN
1798: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1799: IF (l_debug = 1) THEN
1800: print_debug('GMF_get_transfer_price_PUB.Populate_Results: EXCEP UNEXP OTHERS - ' || sqlerrm);
1801: END IF;
1802: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1803: END Populate_Results;
1804:
1801: END IF;
1802: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1803: END Populate_Results;
1804:
1805: END GMF_get_transfer_price_PUB;