[Home] [Help]
287: CLOSE c_no_groups;
288:
289: IF G_DEBUG_LOW
290: THEN
291: ozf_utility_pvt.write_conc_log('Number of Market Eligibilites: '||l_no_groups);
292: END IF;
293:
294: IF l_no_groups > 0
295: THEN
367:
368: IF G_DEBUG_LOW
369: THEN
370: --
371: ozf_utility_pvt.write_conc_log('1:'||substr(x_party_stmt,945,250));
372: ozf_utility_pvt.write_conc_log('2:'||substr(x_party_stmt,1195,250));
373: ozf_utility_pvt.write_conc_log('3:'||substr(x_party_stmt,1445,250));
374: --
375: END IF;
368: IF G_DEBUG_LOW
369: THEN
370: --
371: ozf_utility_pvt.write_conc_log('1:'||substr(x_party_stmt,945,250));
372: ozf_utility_pvt.write_conc_log('2:'||substr(x_party_stmt,1195,250));
373: ozf_utility_pvt.write_conc_log('3:'||substr(x_party_stmt,1445,250));
374: --
375: END IF;
376:
369: THEN
370: --
371: ozf_utility_pvt.write_conc_log('1:'||substr(x_party_stmt,945,250));
372: ozf_utility_pvt.write_conc_log('2:'||substr(x_party_stmt,1195,250));
373: ozf_utility_pvt.write_conc_log('3:'||substr(x_party_stmt,1445,250));
374: --
375: END IF;
376:
377: EXCEPTION
430: FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');
431: FND_DSQL.add_text(' FROM (');
432:
433:
434: ozf_utility_pvt.write_conc_log('-- Refresh_Parties (+)');
435:
436: /* refresh parties would get all the parties for the offer_id and add to FND_DSQL*/
437: refresh_parties(p_offer_id => p_offer_id,
438: p_calling_from_den => 'Y',
440: x_msg_count => x_msg_count,
441: x_msg_data => x_msg_data,
442: x_party_stmt => l_stmt_offer);
443:
444: ozf_utility_pvt.write_conc_log('-- Refresh_Parties (-) With Status: ' || x_return_status );
445:
446: IF x_return_status = FND_API.g_ret_sts_unexp_error
447: THEN
448: RAISE FND_API.g_exc_unexpected_error;
477: EXCEPTION
478: WHEN OTHERS THEN
479: x_return_status := FND_API.g_ret_sts_unexp_error;
480:
481: ozf_utility_pvt.write_conc_log(l_stmt_debug);
482: ozf_utility_pvt.write_conc_log(SQLERRM);
483:
484: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
485: THEN
478: WHEN OTHERS THEN
479: x_return_status := FND_API.g_ret_sts_unexp_error;
480:
481: ozf_utility_pvt.write_conc_log(l_stmt_debug);
482: ozf_utility_pvt.write_conc_log(SQLERRM);
483:
484: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
485: THEN
486: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
584: l_func_area_id := get_func_area(l_product.product_id);
585:
586: IF G_DEBUG_LOW THEN
587: --
588: ozf_utility_pvt.write_conc_log('Functional Area for category: ' || l_func_area_id);
589: ozf_utility_pvt.write_conc_log('Off_Discount_Product_Id:' || l_product.off_discount_product_id);
590: --
591: END IF;
592: --
585:
586: IF G_DEBUG_LOW THEN
587: --
588: ozf_utility_pvt.write_conc_log('Functional Area for category: ' || l_func_area_id);
589: ozf_utility_pvt.write_conc_log('Off_Discount_Product_Id:' || l_product.off_discount_product_id);
590: --
591: END IF;
592: --
593: END IF;
642:
643: FOR l_exclusion IN c_exclusion(l_product.off_discount_product_id)
644: LOOP
645: --
646: ozf_utility_pvt.write_conc_log('Exclusion Loop Start');
647: ozf_utility_pvt.write_conc_log('l_exclusion.product_level: ' || l_exclusion.product_level);
648: ozf_utility_pvt.write_conc_log('l_exclusion.product_id: ' || l_exclusion.product_id);
649:
650: --kdass added for bug 13510229 - get functional area of the excluded product for the excluded product query
643: FOR l_exclusion IN c_exclusion(l_product.off_discount_product_id)
644: LOOP
645: --
646: ozf_utility_pvt.write_conc_log('Exclusion Loop Start');
647: ozf_utility_pvt.write_conc_log('l_exclusion.product_level: ' || l_exclusion.product_level);
648: ozf_utility_pvt.write_conc_log('l_exclusion.product_id: ' || l_exclusion.product_id);
649:
650: --kdass added for bug 13510229 - get functional area of the excluded product for the excluded product query
651: IF l_product.product_level = 'FAMILY' THEN
644: LOOP
645: --
646: ozf_utility_pvt.write_conc_log('Exclusion Loop Start');
647: ozf_utility_pvt.write_conc_log('l_exclusion.product_level: ' || l_exclusion.product_level);
648: ozf_utility_pvt.write_conc_log('l_exclusion.product_id: ' || l_exclusion.product_id);
649:
650: --kdass added for bug 13510229 - get functional area of the excluded product for the excluded product query
651: IF l_product.product_level = 'FAMILY' THEN
652: --
651: IF l_product.product_level = 'FAMILY' THEN
652: --
653: l_excl_func_area_id := get_func_area(l_exclusion.product_id);
654:
655: ozf_utility_pvt.write_conc_log('l_excl_func_area_id: ' || l_excl_func_area_id);
656: ozf_utility_pvt.write_conc_log('l_func_area_id2: ' || l_func_area_id);
657: --
658: END IF;
659:
652: --
653: l_excl_func_area_id := get_func_area(l_exclusion.product_id);
654:
655: ozf_utility_pvt.write_conc_log('l_excl_func_area_id: ' || l_excl_func_area_id);
656: ozf_utility_pvt.write_conc_log('l_func_area_id2: ' || l_func_area_id);
657: --
658: END IF;
659:
660: FND_DSQL.add_text(' MINUS ');
679: FND_DSQL.add_bind(l_org_id);
680: FND_DSQL.add_text(' AND epdhv.parent_id = ');
681: FND_DSQL.add_bind(l_exclusion.product_id);
682: --
683: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
684: ELSE
685: -- Functional Area id OMFA
686: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
687: FND_DSQL.add_bind(l_org_id);
687: FND_DSQL.add_bind(l_org_id);
688: FND_DSQL.add_text(' AND category_id = ');
689: FND_DSQL.add_bind(l_exclusion.product_id);
690: --
691: ozf_utility_pvt.write_conc_log('l_excl_func_area_id2: ' || l_excl_func_area_id);
692: END IF;
693: --
694: END IF;
695: --
711: EXCEPTION
712: WHEN OTHERS THEN
713: x_return_status := FND_API.g_ret_sts_unexp_error;
714:
715: ozf_utility_pvt.write_conc_log(l_stmt_debug);
716: ozf_utility_pvt.write_conc_log(SQLERRM);
717:
718: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
719: THEN
712: WHEN OTHERS THEN
713: x_return_status := FND_API.g_ret_sts_unexp_error;
714:
715: ozf_utility_pvt.write_conc_log(l_stmt_debug);
716: ozf_utility_pvt.write_conc_log(SQLERRM);
717:
718: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
719: THEN
720: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
777: BEGIN
778: x_return_status := FND_API.g_ret_sts_success;
779:
780: l_org_id := fnd_profile.value('QP_ORGANIZATION_ID');
781: ozf_utility_pvt.write_conc_log(l_api_name);
782:
783: ozf_utility_pvt.write_conc_log('l_org_id: ' || l_org_id);
784:
785: FOR l_product IN c_product
779:
780: l_org_id := fnd_profile.value('QP_ORGANIZATION_ID');
781: ozf_utility_pvt.write_conc_log(l_api_name);
782:
783: ozf_utility_pvt.write_conc_log('l_org_id: ' || l_org_id);
784:
785: FOR l_product IN c_product
786: LOOP
787: --
784:
785: FOR l_product IN c_product
786: LOOP
787: --
788: ozf_utility_pvt.write_conc_log('l_product.product_level: ' || l_product.product_level);
789: ozf_utility_pvt.write_conc_log('l_product.product_id: ' || l_product.product_id);
790:
791: IF l_product.product_level = 'FAMILY'
792: THEN
785: FOR l_product IN c_product
786: LOOP
787: --
788: ozf_utility_pvt.write_conc_log('l_product.product_level: ' || l_product.product_level);
789: ozf_utility_pvt.write_conc_log('l_product.product_id: ' || l_product.product_id);
790:
791: IF l_product.product_level = 'FAMILY'
792: THEN
793: --
794:
795:
796: l_func_area_id := get_func_area(l_product.product_id);
797: --
798: ozf_utility_pvt.write_conc_log('l_func_area_id: ' || l_func_area_id);
799:
800: END IF;
801:
802: FOR l_discount IN c_discount
803: LOOP
804: --
805: -- IF G_DEBUG_LOW THEN
806: --
807: ozf_utility_pvt.write_conc_log('off_discount_product_id:' || l_product.off_discount_product_id);
808: ozf_utility_pvt.write_conc_log('Functional Area for category: ' || l_func_area_id);
809: --
810: -- END IF;
811:
804: --
805: -- IF G_DEBUG_LOW THEN
806: --
807: ozf_utility_pvt.write_conc_log('off_discount_product_id:' || l_product.off_discount_product_id);
808: ozf_utility_pvt.write_conc_log('Functional Area for category: ' || l_func_area_id);
809: --
810: -- END IF;
811:
812: FND_DSQL.init;
861: FOR l_exclusion IN c_exclusion(l_product.off_discount_product_id)
862: LOOP
863: --
864:
865: ozf_utility_pvt.write_conc_log('Exclusion Loop Start');
866: ozf_utility_pvt.write_conc_log('l_exclusion.product_level: ' || l_exclusion.product_level);
867: ozf_utility_pvt.write_conc_log('l_exclusion.product_id: ' || l_exclusion.product_id);
868:
869: --kdass added for bug 13510229 - get functional area of the excluded product for the excluded product query
862: LOOP
863: --
864:
865: ozf_utility_pvt.write_conc_log('Exclusion Loop Start');
866: ozf_utility_pvt.write_conc_log('l_exclusion.product_level: ' || l_exclusion.product_level);
867: ozf_utility_pvt.write_conc_log('l_exclusion.product_id: ' || l_exclusion.product_id);
868:
869: --kdass added for bug 13510229 - get functional area of the excluded product for the excluded product query
870: IF l_product.product_level = 'FAMILY' THEN
863: --
864:
865: ozf_utility_pvt.write_conc_log('Exclusion Loop Start');
866: ozf_utility_pvt.write_conc_log('l_exclusion.product_level: ' || l_exclusion.product_level);
867: ozf_utility_pvt.write_conc_log('l_exclusion.product_id: ' || l_exclusion.product_id);
868:
869: --kdass added for bug 13510229 - get functional area of the excluded product for the excluded product query
870: IF l_product.product_level = 'FAMILY' THEN
871: --
870: IF l_product.product_level = 'FAMILY' THEN
871: --
872: l_excl_func_area_id := get_func_area(l_exclusion.product_id);
873:
874: ozf_utility_pvt.write_conc_log('l_excl_func_area_id: ' || l_excl_func_area_id);
875: ozf_utility_pvt.write_conc_log('l_func_area_id2: ' || l_func_area_id);
876: --
877: END IF;
878:
871: --
872: l_excl_func_area_id := get_func_area(l_exclusion.product_id);
873:
874: ozf_utility_pvt.write_conc_log('l_excl_func_area_id: ' || l_excl_func_area_id);
875: ozf_utility_pvt.write_conc_log('l_func_area_id2: ' || l_func_area_id);
876: --
877: END IF;
878:
879: FND_DSQL.add_text(' MINUS ');
897: FND_DSQL.add_bind(l_org_id);
898: FND_DSQL.add_text(' AND epdhv.parent_id = ');
899: FND_DSQL.add_bind(l_exclusion.product_id);
900: --
901: ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
902: ELSE
903: -- Functional Area id OMFA
904: FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
905: FND_DSQL.add_bind(l_org_id);
905: FND_DSQL.add_bind(l_org_id);
906: FND_DSQL.add_text(' AND category_id = ');
907: FND_DSQL.add_bind(l_exclusion.product_id);
908: --
909: ozf_utility_pvt.write_conc_log('l_excl_func_area_id2: ' || l_excl_func_area_id);
910: END IF;
911: --
912: END IF;
913: --
938: EXCEPTION
939: WHEN OTHERS THEN
940: x_return_status := FND_API.g_ret_sts_unexp_error;
941:
942: ozf_utility_pvt.write_conc_log(l_stmt_debug);
943: ozf_utility_pvt.write_conc_log(SQLERRM);
944:
945: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
946: THEN
939: WHEN OTHERS THEN
940: x_return_status := FND_API.g_ret_sts_unexp_error;
941:
942: ozf_utility_pvt.write_conc_log(l_stmt_debug);
943: ozf_utility_pvt.write_conc_log(SQLERRM);
944:
945: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
946: THEN
947: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1075: l_max_accrual:= l_line_accrual;
1076: END IF;
1077:
1078: -- IF G_DEBUG_LOW THEN
1079: ozf_utility_pvt.write_conc_log('Product belongs to a Category on the Offer');
1080: ozf_utility_pvt.write_conc_log('ItmId/Qty/Amt/VolType/DiscType/disc/VolQual?');
1081: ozf_utility_pvt.write_conc_log(p_product_id || '/' ||
1082: p_quantity || '/' ||
1083: p_line_amt || '/' ||
1076: END IF;
1077:
1078: -- IF G_DEBUG_LOW THEN
1079: ozf_utility_pvt.write_conc_log('Product belongs to a Category on the Offer');
1080: ozf_utility_pvt.write_conc_log('ItmId/Qty/Amt/VolType/DiscType/disc/VolQual?');
1081: ozf_utility_pvt.write_conc_log(p_product_id || '/' ||
1082: p_quantity || '/' ||
1083: p_line_amt || '/' ||
1084: l_discount_for_cat.volume_type || '/' ||
1077:
1078: -- IF G_DEBUG_LOW THEN
1079: ozf_utility_pvt.write_conc_log('Product belongs to a Category on the Offer');
1080: ozf_utility_pvt.write_conc_log('ItmId/Qty/Amt/VolType/DiscType/disc/VolQual?');
1081: ozf_utility_pvt.write_conc_log(p_product_id || '/' ||
1082: p_quantity || '/' ||
1083: p_line_amt || '/' ||
1084: l_discount_for_cat.volume_type || '/' ||
1085: l_discount_for_cat.discount_type || '/' ||
1092: ELSE
1093: -- discount for the item exists. take this value as accrualed discount
1094: FOR l_discount_for_item IN c_discount_for_item
1095: LOOP
1096: ozf_utility_pvt.write_conc_log('l_volume_qualified '||l_volume_qualified);
1097: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_from '|| l_discount_for_item.volume_from);
1098: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_to '|| l_discount_for_item.volume_to);
1099: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_type '|| l_discount_for_item.volume_type);
1100: ozf_utility_pvt.write_conc_log('p_line_amt '|| p_line_amt);
1093: -- discount for the item exists. take this value as accrualed discount
1094: FOR l_discount_for_item IN c_discount_for_item
1095: LOOP
1096: ozf_utility_pvt.write_conc_log('l_volume_qualified '||l_volume_qualified);
1097: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_from '|| l_discount_for_item.volume_from);
1098: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_to '|| l_discount_for_item.volume_to);
1099: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_type '|| l_discount_for_item.volume_type);
1100: ozf_utility_pvt.write_conc_log('p_line_amt '|| p_line_amt);
1101: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount '|| l_discount_for_item.discount);
1094: FOR l_discount_for_item IN c_discount_for_item
1095: LOOP
1096: ozf_utility_pvt.write_conc_log('l_volume_qualified '||l_volume_qualified);
1097: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_from '|| l_discount_for_item.volume_from);
1098: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_to '|| l_discount_for_item.volume_to);
1099: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_type '|| l_discount_for_item.volume_type);
1100: ozf_utility_pvt.write_conc_log('p_line_amt '|| p_line_amt);
1101: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount '|| l_discount_for_item.discount);
1102: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount_type '|| l_discount_for_item.discount_type);
1095: LOOP
1096: ozf_utility_pvt.write_conc_log('l_volume_qualified '||l_volume_qualified);
1097: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_from '|| l_discount_for_item.volume_from);
1098: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_to '|| l_discount_for_item.volume_to);
1099: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_type '|| l_discount_for_item.volume_type);
1100: ozf_utility_pvt.write_conc_log('p_line_amt '|| p_line_amt);
1101: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount '|| l_discount_for_item.discount);
1102: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount_type '|| l_discount_for_item.discount_type);
1103: --
1096: ozf_utility_pvt.write_conc_log('l_volume_qualified '||l_volume_qualified);
1097: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_from '|| l_discount_for_item.volume_from);
1098: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_to '|| l_discount_for_item.volume_to);
1099: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_type '|| l_discount_for_item.volume_type);
1100: ozf_utility_pvt.write_conc_log('p_line_amt '|| p_line_amt);
1101: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount '|| l_discount_for_item.discount);
1102: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount_type '|| l_discount_for_item.discount_type);
1103: --
1104: l_line_accrual := 0;
1097: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_from '|| l_discount_for_item.volume_from);
1098: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_to '|| l_discount_for_item.volume_to);
1099: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_type '|| l_discount_for_item.volume_type);
1100: ozf_utility_pvt.write_conc_log('p_line_amt '|| p_line_amt);
1101: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount '|| l_discount_for_item.discount);
1102: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount_type '|| l_discount_for_item.discount_type);
1103: --
1104: l_line_accrual := 0;
1105: l_volume_qualified := 'N';
1098: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_to '|| l_discount_for_item.volume_to);
1099: ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_type '|| l_discount_for_item.volume_type);
1100: ozf_utility_pvt.write_conc_log('p_line_amt '|| p_line_amt);
1101: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount '|| l_discount_for_item.discount);
1102: ozf_utility_pvt.write_conc_log('l_discount_for_item.discount_type '|| l_discount_for_item.discount_type);
1103: --
1104: l_line_accrual := 0;
1105: l_volume_qualified := 'N';
1106: -- check if order satisfies amt/qty requirement
1168: --
1169: END IF;
1170:
1171: IF G_DEBUG_LOW THEN
1172: ozf_utility_pvt.write_conc_log('ItmId/Qty/Amt/VolType/DiscType/disc/VolQual?/MaxAccr');
1173: ozf_utility_pvt.write_conc_log(p_product_id || '/' ||
1174: p_quantity || '/' ||
1175: p_line_amt || '/' ||
1176: l_discount_for_item.volume_type || '/' ||
1169: END IF;
1170:
1171: IF G_DEBUG_LOW THEN
1172: ozf_utility_pvt.write_conc_log('ItmId/Qty/Amt/VolType/DiscType/disc/VolQual?/MaxAccr');
1173: ozf_utility_pvt.write_conc_log(p_product_id || '/' ||
1174: p_quantity || '/' ||
1175: p_line_amt || '/' ||
1176: l_discount_for_item.volume_type || '/' ||
1177: l_discount_for_item.discount_type || '/' ||
1246: l_stmt := l_stmt || ' AND b.product_id = epdhv.parent_id ';
1247: l_stmt := l_stmt || ' AND epdhv.parent_id = c.category_id';
1248:
1249: IF G_DEBUG_LOW THEN
1250: ozf_utility_pvt.write_conc_log('Statement is : ' || l_stmt);
1251: ozf_utility_pvt.write_conc_log('Bind var is : ' || p_product_id);
1252: END IF;
1253:
1254: EXECUTE IMMEDIATE l_stmt INTO l_discount, l_category_id USING p_offer_id, p_product_id, p_org_id;
1247: l_stmt := l_stmt || ' AND epdhv.parent_id = c.category_id';
1248:
1249: IF G_DEBUG_LOW THEN
1250: ozf_utility_pvt.write_conc_log('Statement is : ' || l_stmt);
1251: ozf_utility_pvt.write_conc_log('Bind var is : ' || p_product_id);
1252: END IF;
1253:
1254: EXECUTE IMMEDIATE l_stmt INTO l_discount, l_category_id USING p_offer_id, p_product_id, p_org_id;
1255:
1253:
1254: EXECUTE IMMEDIATE l_stmt INTO l_discount, l_category_id USING p_offer_id, p_product_id, p_org_id;
1255:
1256: IF G_DEBUG_LOW THEN
1257: ozf_utility_pvt.write_conc_log('Discount : ' || l_discount);
1258: ozf_utility_pvt.write_conc_log('Category_Id : ' || l_category_id);
1259: END IF;
1260:
1261: IF ( l_discount IS NOT NULL AND l_category_id IS NOT NULL )
1254: EXECUTE IMMEDIATE l_stmt INTO l_discount, l_category_id USING p_offer_id, p_product_id, p_org_id;
1255:
1256: IF G_DEBUG_LOW THEN
1257: ozf_utility_pvt.write_conc_log('Discount : ' || l_discount);
1258: ozf_utility_pvt.write_conc_log('Category_Id : ' || l_category_id);
1259: END IF;
1260:
1261: IF ( l_discount IS NOT NULL AND l_category_id IS NOT NULL )
1262: THEN
1267: FETCH c_maximum_compensation INTO l_max_compensation;
1268: CLOSE c_maximum_compensation;
1269:
1270: IF G_DEBUG_LOW THEN
1271: ozf_utility_pvt.write_conc_log('accrual for order line:'||l_acc_amt_order);
1272: ozf_utility_pvt.write_conc_log('max compensation:'||l_max_compensation);
1273: END IF;
1274:
1275: FOR i IN c_existing_accruals
1268: CLOSE c_maximum_compensation;
1269:
1270: IF G_DEBUG_LOW THEN
1271: ozf_utility_pvt.write_conc_log('accrual for order line:'||l_acc_amt_order);
1272: ozf_utility_pvt.write_conc_log('max compensation:'||l_max_compensation);
1273: END IF;
1274:
1275: FOR i IN c_existing_accruals
1276: LOOP
1321:
1322: l_na_conc_exception_id NUMBER;
1323: l_pk_exist NUMBER;
1324: BEGIN
1325: ozf_utility_pvt.write_conc_log('Writing exception log for offer ' || p_act_budgets_rec.act_budget_used_by_id);
1326: LOOP
1327: l_pk_exist := NULL;
1328:
1329: OPEN c_na_conc_exception_id;
1416: LOOP
1417: --
1418: IF G_DEBUG_LOW
1419: THEN
1420: ozf_utility_pvt.write_conc_log('Processing exception Id = ' || l_exception_rec.na_conc_exception_id);
1421: END IF;
1422:
1423: l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
1424:
1460: ,x_utilized_amount => l_utilized_amount);
1461:
1462: IF G_DEBUG_LOW
1463: THEN
1464: ozf_utility_pvt.write_conc_log('Exception_id - Status: ' || l_exception_rec.na_conc_exception_id
1465: || ' - '
1466: || l_return_status);
1467: ozf_utility_pvt.write_conc_log('Utilization Amount Created: ' || l_utilized_amount);
1468: END IF;
1463: THEN
1464: ozf_utility_pvt.write_conc_log('Exception_id - Status: ' || l_exception_rec.na_conc_exception_id
1465: || ' - '
1466: || l_return_status);
1467: ozf_utility_pvt.write_conc_log('Utilization Amount Created: ' || l_utilized_amount);
1468: END IF;
1469:
1470: IF l_return_status = FND_API.g_ret_sts_success
1471: THEN
2209: -- initialize multi org
2210: MO_GLOBAL.init('OZF');
2211: MO_GLOBAL.set_policy_context('M',null);
2212:
2213: ozf_utility_pvt.write_conc_log('-- Start Processing : ' || to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
2214:
2215: ozf_utility_pvt.write_conc_log('-- Process_Exceptions (+) ');
2216: --
2217: process_exceptions();
2211: MO_GLOBAL.set_policy_context('M',null);
2212:
2213: ozf_utility_pvt.write_conc_log('-- Start Processing : ' || to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
2214:
2215: ozf_utility_pvt.write_conc_log('-- Process_Exceptions (+) ');
2216: --
2217: process_exceptions();
2218: --
2219: ozf_utility_pvt.write_conc_log('-- Process_Exceptions (-) ');
2215: ozf_utility_pvt.write_conc_log('-- Process_Exceptions (+) ');
2216: --
2217: process_exceptions();
2218: --
2219: ozf_utility_pvt.write_conc_log('-- Process_Exceptions (-) ');
2220: --
2221: l_date_from_input := TRUNC(TO_DATE(p_as_of_date, 'YYYY/MM/DD HH24:MI:SS'));
2222: ozf_utility_pvt.write_conc_log('-- Date Converted : ' || l_date_from_input);
2223:
2218: --
2219: ozf_utility_pvt.write_conc_log('-- Process_Exceptions (-) ');
2220: --
2221: l_date_from_input := TRUNC(TO_DATE(p_as_of_date, 'YYYY/MM/DD HH24:MI:SS'));
2222: ozf_utility_pvt.write_conc_log('-- Date Converted : ' || l_date_from_input);
2223:
2224: IF (l_date_from_input IS NULL)
2225: OR
2226: (TRUNC(l_date_from_input) >= TRUNC(SYSDATE))
2236:
2237: --ninarasi fix for bug 14798341 - if end date is not passed then use sysdate as gl date
2238: l_gldate := NVL(l_date_from_input, TRUNC(SYSDATE));
2239:
2240: ozf_utility_pvt.write_conc_log('gl date: ' || l_gldate);
2241:
2242: IF l_orig_batch_mode IS NULL
2243: THEN
2244: l_orig_batch_mode := 'NO';
2243: THEN
2244: l_orig_batch_mode := 'NO';
2245: END IF;
2246:
2247: ozf_utility_pvt.write_conc_log('OZF: Process Net Accrual In Batch Mode: '||l_orig_batch_mode);
2248: ozf_utility_pvt.write_conc_log('QP: Item Validation Organization: '||l_org_id);
2249:
2250: ozf_utility_pvt.write_conc_log('-- Start Processing Net Accrual Offers (+) ');
2251:
2244: l_orig_batch_mode := 'NO';
2245: END IF;
2246:
2247: ozf_utility_pvt.write_conc_log('OZF: Process Net Accrual In Batch Mode: '||l_orig_batch_mode);
2248: ozf_utility_pvt.write_conc_log('QP: Item Validation Organization: '||l_org_id);
2249:
2250: ozf_utility_pvt.write_conc_log('-- Start Processing Net Accrual Offers (+) ');
2251:
2252: ----------------------------------------------------
2246:
2247: ozf_utility_pvt.write_conc_log('OZF: Process Net Accrual In Batch Mode: '||l_orig_batch_mode);
2248: ozf_utility_pvt.write_conc_log('QP: Item Validation Organization: '||l_org_id);
2249:
2250: ozf_utility_pvt.write_conc_log('-- Start Processing Net Accrual Offers (+) ');
2251:
2252: ----------------------------------------------------
2253: FOR l_net_accrual_offers IN c_net_accrual_offers
2254: LOOP
2265: SAVEPOINT net_accrual_offer;
2266: --
2267: l_return_status := FND_API.g_ret_sts_success;
2268:
2269: ozf_utility_pvt.write_conc_log('-----------------------------------------');
2270: ozf_utility_pvt.write_conc_log('--');
2271: ozf_utility_pvt.write_conc_log('-------- Processing Offer: '|| l_net_accrual_offers.offer_name);
2272: ozf_utility_pvt.write_conc_log(' Offer_Id / List_Header_Id / Custom_Setup_Id / Orig_Org_Id: '
2273: || l_net_accrual_offers.offer_id || ' / '
2266: --
2267: l_return_status := FND_API.g_ret_sts_success;
2268:
2269: ozf_utility_pvt.write_conc_log('-----------------------------------------');
2270: ozf_utility_pvt.write_conc_log('--');
2271: ozf_utility_pvt.write_conc_log('-------- Processing Offer: '|| l_net_accrual_offers.offer_name);
2272: ozf_utility_pvt.write_conc_log(' Offer_Id / List_Header_Id / Custom_Setup_Id / Orig_Org_Id: '
2273: || l_net_accrual_offers.offer_id || ' / '
2274: || l_net_accrual_offers.qp_list_header_id || ' / '
2267: l_return_status := FND_API.g_ret_sts_success;
2268:
2269: ozf_utility_pvt.write_conc_log('-----------------------------------------');
2270: ozf_utility_pvt.write_conc_log('--');
2271: ozf_utility_pvt.write_conc_log('-------- Processing Offer: '|| l_net_accrual_offers.offer_name);
2272: ozf_utility_pvt.write_conc_log(' Offer_Id / List_Header_Id / Custom_Setup_Id / Orig_Org_Id: '
2273: || l_net_accrual_offers.offer_id || ' / '
2274: || l_net_accrual_offers.qp_list_header_id || ' / '
2275: || l_net_accrual_offers.custom_setup_id || ' / '
2268:
2269: ozf_utility_pvt.write_conc_log('-----------------------------------------');
2270: ozf_utility_pvt.write_conc_log('--');
2271: ozf_utility_pvt.write_conc_log('-------- Processing Offer: '|| l_net_accrual_offers.offer_name);
2272: ozf_utility_pvt.write_conc_log(' Offer_Id / List_Header_Id / Custom_Setup_Id / Orig_Org_Id: '
2273: || l_net_accrual_offers.offer_id || ' / '
2274: || l_net_accrual_offers.qp_list_header_id || ' / '
2275: || l_net_accrual_offers.custom_setup_id || ' / '
2276: || l_net_accrual_offers.orig_org_id );
2273: || l_net_accrual_offers.offer_id || ' / '
2274: || l_net_accrual_offers.qp_list_header_id || ' / '
2275: || l_net_accrual_offers.custom_setup_id || ' / '
2276: || l_net_accrual_offers.orig_org_id );
2277: ozf_utility_pvt.write_conc_log('--');
2278:
2279: -------- Derive Program Start and End Date Range ----------
2280: l_latest_comp_date := l_net_accrual_offers.latest_na_completion_date;
2281: l_start_date := l_net_accrual_offers.start_date_active;
2291: THEN
2292: l_end_date := l_as_of_date;
2293: END IF;
2294:
2295: ozf_utility_pvt.write_conc_log('Accrual Start Period: '||to_char(l_start_date,'MM/DD/YY HH:MI:SS AM'));
2296: ozf_utility_pvt.write_conc_log('Accrual End Date: ' ||to_char(l_end_date,'MM/DD/YY HH:MI:SS AM'));
2297: ozf_utility_pvt.write_conc_log('Resale Line Processed: ' || l_idsm_line_processed);
2298:
2299: IF l_start_date > l_end_date
2292: l_end_date := l_as_of_date;
2293: END IF;
2294:
2295: ozf_utility_pvt.write_conc_log('Accrual Start Period: '||to_char(l_start_date,'MM/DD/YY HH:MI:SS AM'));
2296: ozf_utility_pvt.write_conc_log('Accrual End Date: ' ||to_char(l_end_date,'MM/DD/YY HH:MI:SS AM'));
2297: ozf_utility_pvt.write_conc_log('Resale Line Processed: ' || l_idsm_line_processed);
2298:
2299: IF l_start_date > l_end_date
2300: THEN
2293: END IF;
2294:
2295: ozf_utility_pvt.write_conc_log('Accrual Start Period: '||to_char(l_start_date,'MM/DD/YY HH:MI:SS AM'));
2296: ozf_utility_pvt.write_conc_log('Accrual End Date: ' ||to_char(l_end_date,'MM/DD/YY HH:MI:SS AM'));
2297: ozf_utility_pvt.write_conc_log('Resale Line Processed: ' || l_idsm_line_processed);
2298:
2299: IF l_start_date > l_end_date
2300: THEN
2301: -- This offer has been completely processed. Skip OM, Continue Process IDSM.
2298:
2299: IF l_start_date > l_end_date
2300: THEN
2301: -- This offer has been completely processed. Skip OM, Continue Process IDSM.
2302: ozf_utility_pvt.write_conc_log('This Offer has been completely processed for OM. Skipping to IDSM. ');
2303: GOTO IDSM;
2304: END IF;
2305: --------------------------------------------------------------
2306:
2334: THEN
2335: -- No countries defined for a PRM Offer
2336: -- No point processing this offer. Skip it offer
2337: -- If implementation is correct, this will never happen
2338: ozf_utility_pvt.write_conc_log('-- No country qualifiers provided for PRM Offer. Not Processing it ..');
2339: GOTO NEXT_OFFER;
2340: END IF;
2341: --
2342: IF G_DEBUG_LOW
2343: THEN
2344: --
2345: FOR c IN l_terr_countries_tbl.FIRST..l_terr_countries_tbl.LAST
2346: LOOP
2347: ozf_utility_pvt.write_conc_log('Country Code: '|| l_terr_countries_tbl(c) );
2348: END LOOP;
2349: --
2350: END IF;
2351: --
2353: --
2354: -- For all other Offers, populate the ozf_na_customers_temp denom table
2355: --
2356:
2357: ozf_utility_pvt.write_conc_log('Populate_Customers (+)');
2358:
2359: populate_customers(l_net_accrual_offers.offer_id
2360: ,l_return_status
2361: ,l_msg_count
2360: ,l_return_status
2361: ,l_msg_count
2362: ,l_msg_data);
2363:
2364: ozf_utility_pvt.write_conc_log('Populate_Customers (-) With Status: ' ||l_return_status);
2365:
2366: IF l_return_status = Fnd_Api.g_ret_sts_error
2367: THEN
2368: RAISE Fnd_Api.g_exc_error;
2377: --------------- Denorm Products ----------------------
2378: IF l_net_accrual_offers.tier_level = 'LINE'
2379: THEN
2380: --
2381: ozf_utility_pvt.write_conc_log('Populate_Prod_Line (+)');
2382:
2383: populate_prod_line(l_net_accrual_offers.offer_id
2384: ,l_return_status
2385: ,l_msg_count
2384: ,l_return_status
2385: ,l_msg_count
2386: ,l_msg_data);
2387:
2388: ozf_utility_pvt.write_conc_log('Populate_Prod_Line (-) With Status: '||l_return_status);
2389: --
2390: ELSIF l_net_accrual_offers.tier_level = 'HEADER'
2391: THEN
2392: --
2389: --
2390: ELSIF l_net_accrual_offers.tier_level = 'HEADER'
2391: THEN
2392: --
2393: ozf_utility_pvt.write_conc_log('Populate_Prod_Tier (+)');
2394:
2395: populate_prod_tier(l_net_accrual_offers.offer_id
2396: ,l_return_status
2397: ,l_msg_count
2396: ,l_return_status
2397: ,l_msg_count
2398: ,l_msg_data);
2399:
2400: ozf_utility_pvt.write_conc_log('Populate_Prod_Tier (-) With Status: '||l_return_status);
2401: --
2402: END IF;
2403: ---------------------------------------------------------
2404:
2415:
2416: SELECT count(*) INTO l_tot_products
2417: FROM OZF_NA_UNIQUE_PRODUCTS_TEMP;
2418:
2419: ozf_utility_pvt.write_conc_log('Total Number of Unique Products: '||l_tot_products);
2420:
2421: SELECT count(*) INTO l_tot_customers
2422: FROM ozf_na_customers_temp;
2423:
2420:
2421: SELECT count(*) INTO l_tot_customers
2422: FROM ozf_na_customers_temp;
2423:
2424: ozf_utility_pvt.write_conc_log('Total Number of Customers: '||l_tot_customers);
2425:
2426: --Bug 11889816 : end
2427:
2428: -- Catch Weight ER - start
2425:
2426: --Bug 11889816 : end
2427:
2428: -- Catch Weight ER - start
2429: ozf_utility_pvt.write_conc_log('Catch Weight ER b4 c_offer_uom_code l_net_accrual_offers.offer_id : '||l_net_accrual_offers.offer_id);
2430: OPEN c_offer_uom_code(l_net_accrual_offers.offer_id);
2431: FETCH c_offer_uom_code INTO l_offer_uom_code;
2432: CLOSE c_offer_uom_code;
2433: ozf_utility_pvt.write_conc_log('Catch Weight ER l_offer_uom_code : '||l_offer_uom_code);
2429: ozf_utility_pvt.write_conc_log('Catch Weight ER b4 c_offer_uom_code l_net_accrual_offers.offer_id : '||l_net_accrual_offers.offer_id);
2430: OPEN c_offer_uom_code(l_net_accrual_offers.offer_id);
2431: FETCH c_offer_uom_code INTO l_offer_uom_code;
2432: CLOSE c_offer_uom_code;
2433: ozf_utility_pvt.write_conc_log('Catch Weight ER l_offer_uom_code : '||l_offer_uom_code);
2434: -- Catch Weight ER - end
2435: --------------- Start Processing Orders ------------------------
2436: ozf_utility_pvt.write_conc_log('-- Start Processing Orders -- ');
2437:
2432: CLOSE c_offer_uom_code;
2433: ozf_utility_pvt.write_conc_log('Catch Weight ER l_offer_uom_code : '||l_offer_uom_code);
2434: -- Catch Weight ER - end
2435: --------------- Start Processing Orders ------------------------
2436: ozf_utility_pvt.write_conc_log('-- Start Processing Orders -- ');
2437:
2438: --------------- Start Processing OM lines ------------------------
2439: IF l_net_accrual_offers.sales_method_flag IS NULL OR l_net_accrual_offers.sales_method_flag = 'D' THEN
2440: --
2437:
2438: --------------- Start Processing OM lines ------------------------
2439: IF l_net_accrual_offers.sales_method_flag IS NULL OR l_net_accrual_offers.sales_method_flag = 'D' THEN
2440: --
2441: ozf_utility_pvt.write_conc_log('Processing OM lines');
2442: ozf_utility_pvt.write_conc_log('l_start_date '|| l_start_date);
2443: ozf_utility_pvt.write_conc_log('l_end_date '|| l_end_date);
2444: ozf_utility_pvt.write_conc_log('l_offer_org_id '|| l_offer_org_id);
2445: l_order_line_tbl.delete;
2438: --------------- Start Processing OM lines ------------------------
2439: IF l_net_accrual_offers.sales_method_flag IS NULL OR l_net_accrual_offers.sales_method_flag = 'D' THEN
2440: --
2441: ozf_utility_pvt.write_conc_log('Processing OM lines');
2442: ozf_utility_pvt.write_conc_log('l_start_date '|| l_start_date);
2443: ozf_utility_pvt.write_conc_log('l_end_date '|| l_end_date);
2444: ozf_utility_pvt.write_conc_log('l_offer_org_id '|| l_offer_org_id);
2445: l_order_line_tbl.delete;
2446: l_accrual_amount := 0;
2439: IF l_net_accrual_offers.sales_method_flag IS NULL OR l_net_accrual_offers.sales_method_flag = 'D' THEN
2440: --
2441: ozf_utility_pvt.write_conc_log('Processing OM lines');
2442: ozf_utility_pvt.write_conc_log('l_start_date '|| l_start_date);
2443: ozf_utility_pvt.write_conc_log('l_end_date '|| l_end_date);
2444: ozf_utility_pvt.write_conc_log('l_offer_org_id '|| l_offer_org_id);
2445: l_order_line_tbl.delete;
2446: l_accrual_amount := 0;
2447:
2440: --
2441: ozf_utility_pvt.write_conc_log('Processing OM lines');
2442: ozf_utility_pvt.write_conc_log('l_start_date '|| l_start_date);
2443: ozf_utility_pvt.write_conc_log('l_end_date '|| l_end_date);
2444: ozf_utility_pvt.write_conc_log('l_offer_org_id '|| l_offer_org_id);
2445: l_order_line_tbl.delete;
2446: l_accrual_amount := 0;
2447:
2448: --Bugfix: 11889816 - Added l_offer_org_id check
2472: -- To handle NO DATA FOUND for c_order_line CURSOR
2473: IF l_order_line_tbl.FIRST IS NULL
2474: THEN
2475: --
2476: ozf_utility_pvt.write_conc_log('No Data found in c_order_line CURSOR');
2477: EXIT;
2478: --
2479: END IF;
2480: --
2496: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2497: FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
2498: FND_MSG_PUB.add;
2499: END IF;
2500: ozf_utility_pvt.write_conc_log;
2501: RAISE FND_API.G_EXC_ERROR;
2502: END IF;
2503:
2504: l_return_status := FND_API.g_ret_sts_success;
2503:
2504: l_return_status := FND_API.g_ret_sts_success;
2505:
2506: IF G_DEBUG_LOW THEN
2507: ozf_utility_pvt.write_conc_log('Order Line_Id: '||l_order_line_tbl(i).line_id);
2508: END IF;
2509:
2510: l_line_amount := ( NVL(l_order_line_tbl(i).shipped_quantity,l_order_line_tbl(i).fulfilled_quantity)
2511: * l_order_line_tbl(i).unit_selling_price );
2563: --
2564: END IF; -- Done qualfiying the customer
2565:
2566: IF G_DEBUG_LOW THEN
2567: ozf_utility_pvt.write_conc_log('Did Customer qualify: '||l_customer_qualified);
2568: END IF;
2569:
2570: -- Fetch Currency Code on the Order
2571: l_order_curr_code := l_order_line_tbl(i).transactional_curr_code ;
2568: END IF;
2569:
2570: -- Fetch Currency Code on the Order
2571: l_order_curr_code := l_order_line_tbl(i).transactional_curr_code ;
2572: ozf_utility_pvt.write_conc_log('l_order_curr_code: '|| l_order_curr_code);
2573: ozf_utility_pvt.write_conc_log('l_net_accrual_offers.fund_request_curr_code: '|| l_net_accrual_offers.fund_request_curr_code);
2574:
2575: IF l_customer_qualified = 'Y'
2576: THEN
2569:
2570: -- Fetch Currency Code on the Order
2571: l_order_curr_code := l_order_line_tbl(i).transactional_curr_code ;
2572: ozf_utility_pvt.write_conc_log('l_order_curr_code: '|| l_order_curr_code);
2573: ozf_utility_pvt.write_conc_log('l_net_accrual_offers.fund_request_curr_code: '|| l_net_accrual_offers.fund_request_curr_code);
2574:
2575: IF l_customer_qualified = 'Y'
2576: THEN
2577: --
2580: --
2581: l_new_amount := 0;
2582:
2583: --Added for bug 7030415
2584: ozf_utility_pvt.write_conc_log('l_order_line_tbl(i).org_id: '|| l_order_line_tbl(i).org_id);
2585: IF l_batch_mode = 'NO' THEN
2586: OPEN c_get_conversion_type(l_order_line_tbl(i).org_id);
2587: FETCH c_get_conversion_type INTO l_exchange_rate_type;
2588: CLOSE c_get_conversion_type;
2585: IF l_batch_mode = 'NO' THEN
2586: OPEN c_get_conversion_type(l_order_line_tbl(i).org_id);
2587: FETCH c_get_conversion_type INTO l_exchange_rate_type;
2588: CLOSE c_get_conversion_type;
2589: ozf_utility_pvt.write_conc_log('l_exchange_rate_type: '|| l_exchange_rate_type);
2590: ozf_utility_pvt.write_conc_log('l_line_amount: '|| l_line_amount);
2591: END IF;
2592:
2593: ozf_utility_pvt.convert_currency(x_return_status => l_return_status
2586: OPEN c_get_conversion_type(l_order_line_tbl(i).org_id);
2587: FETCH c_get_conversion_type INTO l_exchange_rate_type;
2588: CLOSE c_get_conversion_type;
2589: ozf_utility_pvt.write_conc_log('l_exchange_rate_type: '|| l_exchange_rate_type);
2590: ozf_utility_pvt.write_conc_log('l_line_amount: '|| l_line_amount);
2591: END IF;
2592:
2593: ozf_utility_pvt.convert_currency(x_return_status => l_return_status
2594: ,p_from_currency => l_order_curr_code
2589: ozf_utility_pvt.write_conc_log('l_exchange_rate_type: '|| l_exchange_rate_type);
2590: ozf_utility_pvt.write_conc_log('l_line_amount: '|| l_line_amount);
2591: END IF;
2592:
2593: ozf_utility_pvt.convert_currency(x_return_status => l_return_status
2594: ,p_from_currency => l_order_curr_code
2595: ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
2596: ,p_conv_type => l_exchange_rate_type
2597: --,p_conv_date => l_order_line_tbl(i).conv_date
2604: --offer and order currencies are different.
2605: IF l_batch_mode = 'YES' OR l_net_accrual_offers.transaction_currency_code IS NOT NULL THEN
2606: l_line_amount := l_new_amount;
2607: END IF;
2608: ozf_utility_pvt.write_conc_log('l_line_amount converted : '|| l_line_amount);
2609:
2610: IF l_return_status = Fnd_Api.g_ret_sts_error
2611: THEN
2612: ozf_utility_pvt.write_conc_log('Exp Error from Convert_Currency: ' || l_return_status);
2608: ozf_utility_pvt.write_conc_log('l_line_amount converted : '|| l_line_amount);
2609:
2610: IF l_return_status = Fnd_Api.g_ret_sts_error
2611: THEN
2612: ozf_utility_pvt.write_conc_log('Exp Error from Convert_Currency: ' || l_return_status);
2613: RAISE Fnd_Api.g_exc_error;
2614: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2615: THEN
2616: ozf_utility_pvt.write_conc_log('Unexp Error from Convert_Currency: ' || l_return_status);
2612: ozf_utility_pvt.write_conc_log('Exp Error from Convert_Currency: ' || l_return_status);
2613: RAISE Fnd_Api.g_exc_error;
2614: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2615: THEN
2616: ozf_utility_pvt.write_conc_log('Unexp Error from Convert_Currency: ' || l_return_status);
2617: RAISE Fnd_Api.g_exc_unexpected_error;
2618: END IF;
2619: --
2620: END IF;
2623: IF l_net_accrual_offers.custom_setup_id = 105
2624: THEN
2625: --
2626: IF G_DEBUG_LOW THEN
2627: ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (+)');
2628: END IF;
2629: pv_referral_comp_pub.get_beneficiary (p_api_version => 1.0,
2630: p_init_msg_list => FND_API.g_true,
2631: p_commit => FND_API.g_false,
2638: x_return_status => l_return_status,
2639: x_msg_count => l_msg_count,
2640: x_msg_data => l_msg_data);
2641: IF G_DEBUG_LOW THEN
2642: ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (-) With Status: '||l_return_status);
2643: ozf_utility_pvt.write_conc_log('l_benificiary_id / l_referral_id: '||l_beneficiary_id || ' / ' || l_referral_id);
2644: END IF;
2645:
2646: IF l_return_status = Fnd_Api.g_ret_sts_error
2639: x_msg_count => l_msg_count,
2640: x_msg_data => l_msg_data);
2641: IF G_DEBUG_LOW THEN
2642: ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (-) With Status: '||l_return_status);
2643: ozf_utility_pvt.write_conc_log('l_benificiary_id / l_referral_id: '||l_beneficiary_id || ' / ' || l_referral_id);
2644: END IF;
2645:
2646: IF l_return_status = Fnd_Api.g_ret_sts_error
2647: THEN
2644: END IF;
2645:
2646: IF l_return_status = Fnd_Api.g_ret_sts_error
2647: THEN
2648: ozf_utility_pvt.write_conc_log('Exp Error from Get_Beneficiary: ' || l_return_status);
2649: RAISE Fnd_Api.g_exc_error;
2650: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2651: THEN
2652: ozf_utility_pvt.write_conc_log('Unexp Error from Get_Beneficiary: ' || l_return_status);
2648: ozf_utility_pvt.write_conc_log('Exp Error from Get_Beneficiary: ' || l_return_status);
2649: RAISE Fnd_Api.g_exc_error;
2650: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2651: THEN
2652: ozf_utility_pvt.write_conc_log('Unexp Error from Get_Beneficiary: ' || l_return_status);
2653: RAISE Fnd_Api.g_exc_unexpected_error;
2654: END IF;
2655: --
2656:
2657: IF ( l_beneficiary_id IS NOT NULL )
2658: THEN
2659: --------------------------- Derive Accrual Amount -------------------------
2660: IF G_DEBUG_LOW THEN
2661: ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (+)');
2662: END IF;
2663:
2664: l_line_acc_amount := get_pv_accrual_amount(p_product_id => l_order_line_tbl(i).inventory_item_id
2665: ,p_line_amt => l_line_amount
2668: ,p_list_hdr_id => l_net_accrual_offers.qp_list_header_id
2669: ,p_referral_id => l_referral_id
2670: ,p_order_hdr_id => l_order_line_tbl(i).header_id);
2671: IF G_DEBUG_LOW THEN
2672: ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (-) With l_line_acc_amount: '|| l_line_acc_amount);
2673: END IF;
2674: --
2675: ELSE
2676: --
2673: END IF;
2674: --
2675: ELSE
2676: --
2677: ozf_utility_pvt.write_conc_log('No Beneficiary derived from PV_Referral_Comp_Pub. Utilization will not be created');
2678: --
2679: END IF;
2680: --
2681: l_utilization_type := 'LEAD_ACCRUAL';
2684: ELSE
2685: --
2686: --------------------------- Derive Accrual Amount -------------------------
2687: -- IF G_DEBUG_LOW THEN
2688: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
2689: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).inventory_item_id = '|| l_order_line_tbl(i).inventory_item_id);
2690: ozf_utility_pvt.write_conc_log('Catch Weight - l_line_amount = '|| l_line_amount);
2691: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity = '|| l_order_line_tbl(i).pricing_quantity);
2692: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity_uom = '|| l_order_line_tbl(i).pricing_quantity_uom);
2685: --
2686: --------------------------- Derive Accrual Amount -------------------------
2687: -- IF G_DEBUG_LOW THEN
2688: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
2689: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).inventory_item_id = '|| l_order_line_tbl(i).inventory_item_id);
2690: ozf_utility_pvt.write_conc_log('Catch Weight - l_line_amount = '|| l_line_amount);
2691: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity = '|| l_order_line_tbl(i).pricing_quantity);
2692: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity_uom = '|| l_order_line_tbl(i).pricing_quantity_uom);
2693: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity = '|| l_order_line_tbl(i).shipping_quantity);
2686: --------------------------- Derive Accrual Amount -------------------------
2687: -- IF G_DEBUG_LOW THEN
2688: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
2689: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).inventory_item_id = '|| l_order_line_tbl(i).inventory_item_id);
2690: ozf_utility_pvt.write_conc_log('Catch Weight - l_line_amount = '|| l_line_amount);
2691: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity = '|| l_order_line_tbl(i).pricing_quantity);
2692: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity_uom = '|| l_order_line_tbl(i).pricing_quantity_uom);
2693: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity = '|| l_order_line_tbl(i).shipping_quantity);
2694: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity_uom = '|| l_order_line_tbl(i).shipping_quantity_uom);
2687: -- IF G_DEBUG_LOW THEN
2688: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
2689: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).inventory_item_id = '|| l_order_line_tbl(i).inventory_item_id);
2690: ozf_utility_pvt.write_conc_log('Catch Weight - l_line_amount = '|| l_line_amount);
2691: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity = '|| l_order_line_tbl(i).pricing_quantity);
2692: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity_uom = '|| l_order_line_tbl(i).pricing_quantity_uom);
2693: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity = '|| l_order_line_tbl(i).shipping_quantity);
2694: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity_uom = '|| l_order_line_tbl(i).shipping_quantity_uom);
2695: ozf_utility_pvt.write_conc_log('Catch Weight - l_offer_uom_code = '|| l_offer_uom_code);
2688: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
2689: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).inventory_item_id = '|| l_order_line_tbl(i).inventory_item_id);
2690: ozf_utility_pvt.write_conc_log('Catch Weight - l_line_amount = '|| l_line_amount);
2691: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity = '|| l_order_line_tbl(i).pricing_quantity);
2692: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity_uom = '|| l_order_line_tbl(i).pricing_quantity_uom);
2693: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity = '|| l_order_line_tbl(i).shipping_quantity);
2694: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity_uom = '|| l_order_line_tbl(i).shipping_quantity_uom);
2695: ozf_utility_pvt.write_conc_log('Catch Weight - l_offer_uom_code = '|| l_offer_uom_code);
2696: -- END IF;
2689: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).inventory_item_id = '|| l_order_line_tbl(i).inventory_item_id);
2690: ozf_utility_pvt.write_conc_log('Catch Weight - l_line_amount = '|| l_line_amount);
2691: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity = '|| l_order_line_tbl(i).pricing_quantity);
2692: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity_uom = '|| l_order_line_tbl(i).pricing_quantity_uom);
2693: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity = '|| l_order_line_tbl(i).shipping_quantity);
2694: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity_uom = '|| l_order_line_tbl(i).shipping_quantity_uom);
2695: ozf_utility_pvt.write_conc_log('Catch Weight - l_offer_uom_code = '|| l_offer_uom_code);
2696: -- END IF;
2697: --[ Catch Weight Example
2690: ozf_utility_pvt.write_conc_log('Catch Weight - l_line_amount = '|| l_line_amount);
2691: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity = '|| l_order_line_tbl(i).pricing_quantity);
2692: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity_uom = '|| l_order_line_tbl(i).pricing_quantity_uom);
2693: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity = '|| l_order_line_tbl(i).shipping_quantity);
2694: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity_uom = '|| l_order_line_tbl(i).shipping_quantity_uom);
2695: ozf_utility_pvt.write_conc_log('Catch Weight - l_offer_uom_code = '|| l_offer_uom_code);
2696: -- END IF;
2697: --[ Catch Weight Example
2698: -- Order Booked For 3 Case = 36 Ea = 360 Pounds
2691: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity = '|| l_order_line_tbl(i).pricing_quantity);
2692: ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity_uom = '|| l_order_line_tbl(i).pricing_quantity_uom);
2693: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity = '|| l_order_line_tbl(i).shipping_quantity);
2694: --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity_uom = '|| l_order_line_tbl(i).shipping_quantity_uom);
2695: ozf_utility_pvt.write_conc_log('Catch Weight - l_offer_uom_code = '|| l_offer_uom_code);
2696: -- END IF;
2697: --[ Catch Weight Example
2698: -- Order Booked For 3 Case = 36 Ea = 360 Pounds
2699: -- Shipped with Catch Weight = 320 Pounds
2702: -- NE_CW_NETACC6 -- Discount - 10% : UOM - Pounds -- Accrual = 10% * line_amount = 10% * 266.67 = 26.67
2703: -- NE_CW_NETACC7 -- Discount - 10 AMT : UOM - CS -- Accrual = 10 AMT per Case = 10 * (3/360)* 320 = 26.67
2704: -- NE_CW_NETACC8 -- Discount - 10 AMT : UOM - Pounds -- Accrual = 10 AMOT per Pound = 10 * 320 = 3200]
2705: -- Catch Weight ER - start
2706: /* OZF_UTILITY_PVT.get_catch_weight_quantity (
2707: p_inventory_item_id => l_order_line_tbl(i).inventory_item_id,
2708: p_order_line_id => l_order_line_tbl(i).line_id,
2709: x_return_status => l_return_status,
2710: x_cw_quantity => l_cw_quantity,
2737: ,NULL -- from_name
2738: ,NULL -- to_name
2739: );
2740: IF (l_ship_quantity = -99999) THEN
2741: ozf_utility_pvt.write_conc_log ('Error in UOM conversion');
2742: END IF;
2743: ELSE
2744: l_ship_quantity := l_cw_quantity ;
2745: END IF;
2742: END IF;
2743: ELSE
2744: l_ship_quantity := l_cw_quantity ;
2745: END IF;
2746: ozf_utility_pvt.write_conc_log('Catch Weight - l_ship_quantity = '|| l_ship_quantity);
2747: -- Catch Weight ER - end
2748: l_line_acc_amount := get_accrualed_amount(p_product_id => l_order_line_tbl(i).inventory_item_id
2749: ,p_line_amt => l_line_amount
2750: ,p_quantity => l_ship_quantity -- Catch Weight ER : Passing the shipping quantity instead of pricing quantity
2749: ,p_line_amt => l_line_amount
2750: ,p_quantity => l_ship_quantity -- Catch Weight ER : Passing the shipping quantity instead of pricing quantity
2751: ,p_uom => l_order_line_tbl(i).pricing_quantity_uom); -- even if passed, pricing_quantity_uom is not used, hence not changing.
2752: -- IF G_DEBUG_LOW THEN
2753: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (-) With l_line_acc_amount: '|| l_line_acc_amount);
2754: -- END IF;
2755: --
2756:
2757: --
2798: --nirprasa,12.2 ER 8399135.
2799: --l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
2800: l_act_budgets_rec.approved_in_currency := l_act_budgets_rec.request_currency;
2801: l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
2802: l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
2803: l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
2804: l_act_budgets_rec.transfer_type := 'UTILIZED';
2805: l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
2806:
2836: ,x_act_budget_id => l_act_budget_id
2837: ,x_utilized_amount => l_utilized_amount);
2838: --
2839: IF G_DEBUG_LOW THEN
2840: ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
2841: END IF;
2842:
2843: IF l_return_status = Fnd_Api.g_ret_sts_error
2844: THEN
2841: END IF;
2842:
2843: IF l_return_status = Fnd_Api.g_ret_sts_error
2844: THEN
2845: ozf_utility_pvt.write_conc_log('Exp Error: 111 Process_Act_Budgets: line_id ( '||l_order_line_tbl(i).line_id
2846: || ' ) Error: '||l_msg_data);
2847: log_exception(l_act_budgets_rec, l_act_util_rec);
2848: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2849: THEN
2846: || ' ) Error: '||l_msg_data);
2847: log_exception(l_act_budgets_rec, l_act_util_rec);
2848: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2849: THEN
2850: ozf_utility_pvt.write_conc_log('UnExp Error: 222 Process_Act_Budgets: line_id ( '||l_order_line_tbl(i).line_id
2851: || ' ) Error: '||l_msg_data);
2852: log_exception(l_act_budgets_rec, l_act_util_rec);
2853: END IF;
2854:
2928: l_act_budgets_rec.status_code := 'APPROVED';
2929: l_act_budgets_rec.approved_amount := l_accrual_amount;
2930: l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
2931: l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
2932: l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
2933: l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
2934: l_act_budgets_rec.transfer_type := 'UTILIZED';
2935: l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
2936:
2956: ,x_act_budget_id => l_act_budget_id
2957: ,x_utilized_amount => l_utilized_amount);
2958:
2959: IF G_DEBUG_LOW THEN
2960: ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
2961: END IF;
2962:
2963: IF l_return_status = Fnd_Api.g_ret_sts_error
2964: THEN
2961: END IF;
2962:
2963: IF l_return_status = Fnd_Api.g_ret_sts_error
2964: THEN
2965: ozf_utility_pvt.write_conc_log('Exp Error: 333 Process_Act_Budgets Error: '||l_msg_data );
2966: ozf_utility_pvt.write_conc_log('Exp Error: 333 Process_Act_Budgets Error: '|| SQLERRM );
2967: log_exception(l_act_budgets_rec, l_act_util_rec);
2968: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2969: THEN
2962:
2963: IF l_return_status = Fnd_Api.g_ret_sts_error
2964: THEN
2965: ozf_utility_pvt.write_conc_log('Exp Error: 333 Process_Act_Budgets Error: '||l_msg_data );
2966: ozf_utility_pvt.write_conc_log('Exp Error: 333 Process_Act_Budgets Error: '|| SQLERRM );
2967: log_exception(l_act_budgets_rec, l_act_util_rec);
2968: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2969: THEN
2970: ozf_utility_pvt.write_conc_log('UnExp 444 Error: Process_Act_Budgets Error: '||l_msg_data );
2966: ozf_utility_pvt.write_conc_log('Exp Error: 333 Process_Act_Budgets Error: '|| SQLERRM );
2967: log_exception(l_act_budgets_rec, l_act_util_rec);
2968: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2969: THEN
2970: ozf_utility_pvt.write_conc_log('UnExp 444 Error: Process_Act_Budgets Error: '||l_msg_data );
2971: log_exception(l_act_budgets_rec, l_act_util_rec);
2972: END IF;
2973:
2974: l_utilized_amount := 0;
2982: END IF; -- end l_batch_mode = 'YES'
2983: --
2984: END IF; -- End OM lines
2985:
2986: ozf_utility_pvt.write_conc_log('-- Done Processing Orders -- ');
2987:
2988: --------------- Done Processing Orders ------------------------
2989:
2990: ozf_utility_pvt.write_conc_log('-- Start Processing Deduction Rules -- ');
2986: ozf_utility_pvt.write_conc_log('-- Done Processing Orders -- ');
2987:
2988: --------------- Done Processing Orders ------------------------
2989:
2990: ozf_utility_pvt.write_conc_log('-- Start Processing Deduction Rules -- ');
2991:
2992: FOR l_na_rule_line IN c_na_rule_lines(l_net_accrual_offers.na_rule_header_id)
2993: LOOP
2994: --
2997: OPEN c_na_deduction_rule(l_na_rule_line.na_deduction_rule_id);
2998: FETCH c_na_deduction_rule INTO l_na_deduction_rule;
2999: CLOSE c_na_deduction_rule;
3000:
3001: ozf_utility_pvt.write_conc_log('Name / Type / Id / Org : '||
3002: l_na_deduction_rule.name || ' / ' ||
3003: l_na_deduction_rule.transaction_type_code || ' / ' ||
3004: l_na_deduction_rule.deduction_identifier_id || ' / ' ||
3005: l_na_deduction_rule.deduction_identifier_org_id );
3003: l_na_deduction_rule.transaction_type_code || ' / ' ||
3004: l_na_deduction_rule.deduction_identifier_id || ' / ' ||
3005: l_na_deduction_rule.deduction_identifier_org_id );
3006:
3007: ozf_utility_pvt.write_conc_log('l_na_deduction_rule.transaction_source_code : ' || l_na_deduction_rule.transaction_source_code);
3008:
3009:
3010:
3011: ---------------------------------------------------------------
3040: -- To handle NO DATA FOUND for c_ar_trx_line CURSOR
3041: IF l_ar_trx_line_tbl.FIRST IS NULL
3042: THEN
3043: --
3044: ozf_utility_pvt.write_conc_log('No Data found in c_ar_trx_line_details CURSOR');
3045: EXIT;
3046: --
3047: END IF;
3048: -- Exit after finishing processing is before END LOOP
3063: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3064: FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
3065: FND_MSG_PUB.add;
3066: END IF;
3067: ozf_utility_pvt.write_conc_log;
3068: RAISE FND_API.G_EXC_ERROR;
3069: END IF;
3070: END IF;
3071:
3072: l_customer_qualified := validate_customer(l_ar_trx_line_tbl(i).bill_to_site_use_id,
3073: l_ar_trx_line_tbl(i).ship_to_site_use_id,
3074: l_ar_trx_line_tbl(i).sold_to_customer_id);
3075:
3076: ozf_utility_pvt.write_conc_log('Cust_Trx_Line_Id / Customer Qualifier ? : ' ||
3077: l_ar_trx_line_tbl(i).customer_trx_line_id || '/' ||l_customer_qualified );
3078:
3079: IF l_customer_qualified = 'Y'
3080: THEN
3090: --only those records are picked for which the org_id=l_na_deduction_rule.deduction_identifier_org_id
3091: OPEN c_get_conversion_type(l_na_deduction_rule.deduction_identifier_org_id);
3092: FETCH c_get_conversion_type INTO l_exchange_rate_type;
3093: CLOSE c_get_conversion_type;
3094: ozf_utility_pvt.convert_currency(
3095: x_return_status => l_return_status
3096: ,p_from_currency => l_ar_trx_line_tbl(i).invoice_currency_code
3097: ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
3098: ,p_conv_type => l_exchange_rate_type
3111: END IF;
3112:
3113: IF l_return_status = Fnd_Api.g_ret_sts_error
3114: THEN
3115: ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3116: RAISE Fnd_Api.g_exc_error;
3117: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3118: THEN
3119: ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3115: ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3116: RAISE Fnd_Api.g_exc_error;
3117: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3118: THEN
3119: ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3120: RAISE Fnd_Api.g_exc_unexpected_error;
3121: END IF;
3122: --
3123: END IF;
3144: END IF;
3145:
3146: IF G_DEBUG_LOW
3147: THEN
3148: ozf_utility_pvt.write_conc_log('Sign of the Credit Memo : '||l_sign);
3149: ozf_utility_pvt.write_conc_log('quantity_credited : '||l_ar_trx_line_tbl(i).quantity_credited );
3150: END IF;
3151: -- Catch Weight ER - start
3152: IF l_ar_trx_line_tbl(i).uom_code IS NOT NULL AND l_ar_trx_line_tbl(i).uom_code <> l_offer_uom_code THEN
3145:
3146: IF G_DEBUG_LOW
3147: THEN
3148: ozf_utility_pvt.write_conc_log('Sign of the Credit Memo : '||l_sign);
3149: ozf_utility_pvt.write_conc_log('quantity_credited : '||l_ar_trx_line_tbl(i).quantity_credited );
3150: END IF;
3151: -- Catch Weight ER - start
3152: IF l_ar_trx_line_tbl(i).uom_code IS NOT NULL AND l_ar_trx_line_tbl(i).uom_code <> l_offer_uom_code THEN
3153: l_quantity :=
3159: ,NULL -- from_name
3160: ,NULL -- to_name
3161: );
3162: IF (l_quantity = -99999) THEN
3163: ozf_utility_pvt.write_conc_log ('Error in UOM conversion');
3164: END IF;
3165: END IF;
3166: ozf_utility_pvt.write_conc_log('Catch Weight - l_shipping_quantity = '|| l_shipping_quantity);
3167: -- Catch Weight ER - end
3162: IF (l_quantity = -99999) THEN
3163: ozf_utility_pvt.write_conc_log ('Error in UOM conversion');
3164: END IF;
3165: END IF;
3166: ozf_utility_pvt.write_conc_log('Catch Weight - l_shipping_quantity = '|| l_shipping_quantity);
3167: -- Catch Weight ER - end
3168:
3169: -- Always send positive values for calculation
3170: l_ar_dedu_line_amt := get_accrualed_amount(
3193: -- l_existing_util_amt will return as 0 if no utilziations already exist
3194: -- since the cursor c_get_util_amt has a NVL
3195:
3196: IF G_DEBUG_LOW THEN
3197: ozf_utility_pvt.write_conc_log('l_ar_dedu_line_amt (A) : '||l_ar_dedu_line_amt);
3198: ozf_utility_pvt.write_conc_log('l_existing_util_amt (B) : '||l_existing_util_amt);
3199: ozf_utility_pvt.write_conc_log('(A) - (B) : '|| (l_ar_dedu_line_amt - l_existing_util_amt));
3200: END IF;
3201:
3194: -- since the cursor c_get_util_amt has a NVL
3195:
3196: IF G_DEBUG_LOW THEN
3197: ozf_utility_pvt.write_conc_log('l_ar_dedu_line_amt (A) : '||l_ar_dedu_line_amt);
3198: ozf_utility_pvt.write_conc_log('l_existing_util_amt (B) : '||l_existing_util_amt);
3199: ozf_utility_pvt.write_conc_log('(A) - (B) : '|| (l_ar_dedu_line_amt - l_existing_util_amt));
3200: END IF;
3201:
3202: -- If utilizations do not exist l_existing_util_amt will be 0
3195:
3196: IF G_DEBUG_LOW THEN
3197: ozf_utility_pvt.write_conc_log('l_ar_dedu_line_amt (A) : '||l_ar_dedu_line_amt);
3198: ozf_utility_pvt.write_conc_log('l_existing_util_amt (B) : '||l_existing_util_amt);
3199: ozf_utility_pvt.write_conc_log('(A) - (B) : '|| (l_ar_dedu_line_amt - l_existing_util_amt));
3200: END IF;
3201:
3202: -- If utilizations do not exist l_existing_util_amt will be 0
3203: -- A - B will be = A
3227: l_act_budgets_rec.approved_amount := l_ar_dedu_line_amt;
3228: --nirprasa,12.2 ER 8399135.l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3229: l_act_budgets_rec.approved_in_currency := l_act_budgets_rec.request_currency;
3230: l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
3231: l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3232: l_act_budgets_rec.justification := 'NA: AR DEDUCTION' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
3233: l_act_budgets_rec.transfer_type := 'UTILIZED';
3234: l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3235:
3263: ,x_act_budget_id => l_act_budget_id
3264: ,x_utilized_amount => l_utilized_amount);
3265:
3266: IF G_DEBUG_LOW THEN
3267: ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
3268: END IF;
3269:
3270: l_utilized_amount := 0;
3271:
3270: l_utilized_amount := 0;
3271:
3272: IF l_return_status = Fnd_Api.g_ret_sts_error
3273: THEN
3274: ozf_utility_pvt.write_conc_log('Exceptin : Msg from Budget API 3 : '||l_msg_data);
3275: log_exception(l_act_budgets_rec, l_act_util_rec);
3276: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3277: THEN
3278: ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 3 : '||l_msg_data);
3274: ozf_utility_pvt.write_conc_log('Exceptin : Msg from Budget API 3 : '||l_msg_data);
3275: log_exception(l_act_budgets_rec, l_act_util_rec);
3276: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3277: THEN
3278: ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 3 : '||l_msg_data);
3279: log_exception(l_act_budgets_rec, l_act_util_rec);
3280: END IF;
3281: --
3282: END IF; -- End Amount <> 0
3316: l_act_budgets_rec.status_code := 'APPROVED';
3317: l_act_budgets_rec.approved_amount := l_ar_dedu_amount;
3318: l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3319: l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
3320: l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3321: l_act_budgets_rec.justification := 'NA: AR DEDUCTION' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
3322: l_act_budgets_rec.transfer_type := 'UTILIZED';
3323: l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3324:
3334: l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
3335: --nirprasa,12.2
3336:
3337: IF G_DEBUG_LOW THEN
3338: ozf_utility_pvt.write_conc_log('Accrual log: AR Deduction BATCH_MODE = Y');
3339: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
3340: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
3341: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
3342: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
3335: --nirprasa,12.2
3336:
3337: IF G_DEBUG_LOW THEN
3338: ozf_utility_pvt.write_conc_log('Accrual log: AR Deduction BATCH_MODE = Y');
3339: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
3340: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
3341: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
3342: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
3343: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
3336:
3337: IF G_DEBUG_LOW THEN
3338: ozf_utility_pvt.write_conc_log('Accrual log: AR Deduction BATCH_MODE = Y');
3339: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
3340: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
3341: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
3342: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
3343: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
3344: END IF;
3337: IF G_DEBUG_LOW THEN
3338: ozf_utility_pvt.write_conc_log('Accrual log: AR Deduction BATCH_MODE = Y');
3339: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
3340: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
3341: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
3342: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
3343: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
3344: END IF;
3345:
3338: ozf_utility_pvt.write_conc_log('Accrual log: AR Deduction BATCH_MODE = Y');
3339: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
3340: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
3341: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
3342: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
3343: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
3344: END IF;
3345:
3346: ozf_fund_adjustment_pvt.process_act_budgets(
3339: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
3340: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
3341: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
3342: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
3343: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
3344: END IF;
3345:
3346: ozf_fund_adjustment_pvt.process_act_budgets(
3347: x_return_status => l_return_status
3352: ,x_act_budget_id => l_act_budget_id
3353: ,x_utilized_amount => l_utilized_amount);
3354:
3355: IF G_DEBUG_LOW THEN
3356: ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
3357: END IF;
3358:
3359: l_utilized_amount := 0;
3360:
3359: l_utilized_amount := 0;
3360:
3361: IF l_return_status = Fnd_Api.g_ret_sts_error
3362: THEN
3363: ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 4 : '||l_msg_data);
3364: log_exception(l_act_budgets_rec, l_act_util_rec);
3365: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3366: ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 4 : '||l_msg_data);
3367: log_exception(l_act_budgets_rec, l_act_util_rec);
3362: THEN
3363: ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 4 : '||l_msg_data);
3364: log_exception(l_act_budgets_rec, l_act_util_rec);
3365: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3366: ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 4 : '||l_msg_data);
3367: log_exception(l_act_budgets_rec, l_act_util_rec);
3368: END IF;
3369:
3370: l_act_budgets_rec := NULL;
3393: -- To handle NO DATA FOUND for c_return_line CURSOR
3394: IF l_return_line_tbl.FIRST IS NULL
3395: THEN
3396: --
3397: ozf_utility_pvt.write_conc_log('No Data found in c_return_line CURSOR');
3398: EXIT;
3399: --
3400: END IF;
3401: --
3415: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3416: FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
3417: FND_MSG_PUB.add;
3418: END IF;
3419: ozf_utility_pvt.write_conc_log;
3420: RAISE FND_API.G_EXC_ERROR;
3421: END IF;
3422: END IF;
3423:
3491: OPEN c_get_conversion_type(l_return_line_tbl(i).org_id);
3492: FETCH c_get_conversion_type INTO l_exchange_rate_type;
3493: CLOSE c_get_conversion_type;
3494:
3495: ozf_utility_pvt.convert_currency(
3496: x_return_status => l_return_status
3497: ,p_from_currency => l_order_curr_code
3498: ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
3499: ,p_conv_type => l_exchange_rate_type
3509: END IF;
3510:
3511: IF l_return_status = Fnd_Api.g_ret_sts_error
3512: THEN
3513: ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3514: RAISE Fnd_Api.g_exc_error;
3515: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3516: THEN
3517: ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3513: ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3514: RAISE Fnd_Api.g_exc_error;
3515: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3516: THEN
3517: ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3518: RAISE Fnd_Api.g_exc_unexpected_error;
3519: END IF;
3520: --
3521: END IF;
3531: ,NULL -- from_name
3532: ,NULL -- to_name
3533: );
3534: IF (l_return_line_tbl(i).invoiced_quantity = -99999) THEN
3535: ozf_utility_pvt.write_conc_log ('Error in UOM conversion');
3536: END IF;
3537: END IF;
3538: ozf_utility_pvt.write_conc_log('Catch Weight - l_shipping_quantity = '|| l_shipping_quantity);
3539: -- Catch Weight ER - end
3534: IF (l_return_line_tbl(i).invoiced_quantity = -99999) THEN
3535: ozf_utility_pvt.write_conc_log ('Error in UOM conversion');
3536: END IF;
3537: END IF;
3538: ozf_utility_pvt.write_conc_log('Catch Weight - l_shipping_quantity = '|| l_shipping_quantity);
3539: -- Catch Weight ER - end
3540: l_om_dedu_line_amt := get_accrualed_amount(
3541: p_product_id => l_return_line_tbl(i).inventory_item_id
3542: ,p_line_amt => l_line_amount
3601: l_act_budgets_rec.approved_amount := l_om_dedu_line_amt;
3602: --nirprasa,12.2 ER 8399135. l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3603: l_act_budgets_rec.approved_in_currency := l_act_budgets_rec.request_currency;
3604: l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
3605: l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3606: l_act_budgets_rec.justification := 'NA: OM Deduction' || TO_CHAR(l_sysdate, 'MON-DD-YYYY');
3607: l_act_budgets_rec.transfer_type := 'UTILIZED';
3608: l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3609:
3638: ,x_act_budget_id => l_act_budget_id
3639: ,x_utilized_amount => l_utilized_amount);
3640:
3641: IF G_DEBUG_LOW THEN
3642: ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
3643: END IF;
3644:
3645: l_utilized_amount := 0;
3646:
3644:
3645: l_utilized_amount := 0;
3646:
3647: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3648: ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 5 : '||l_msg_data);
3649: log_exception(l_act_budgets_rec, l_act_util_rec);
3650: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3651: ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 5 : '||l_msg_data);
3652: log_exception(l_act_budgets_rec, l_act_util_rec);
3647: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3648: ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 5 : '||l_msg_data);
3649: log_exception(l_act_budgets_rec, l_act_util_rec);
3650: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3651: ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 5 : '||l_msg_data);
3652: log_exception(l_act_budgets_rec, l_act_util_rec);
3653: END IF;
3654: --
3655: END IF; -- end amount <> 0
3692: l_act_budgets_rec.status_code := 'APPROVED';
3693: l_act_budgets_rec.approved_amount := l_om_dedu_amount;
3694: l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3695: l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
3696: l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3697: l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(l_sysdate, 'MON-DD-YYYY');
3698: l_act_budgets_rec.transfer_type := 'UTILIZED';
3699: l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3700:
3719: ,x_act_budget_id => l_act_budget_id
3720: ,x_utilized_amount => l_utilized_amount);
3721:
3722: IF G_DEBUG_LOW THEN
3723: ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
3724: END IF;
3725:
3726: l_utilized_amount := 0;
3727:
3725:
3726: l_utilized_amount := 0;
3727:
3728: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3729: ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 6 : '||l_msg_data);
3730: log_exception(l_act_budgets_rec, l_act_util_rec);
3731: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3732: ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 6 : '||l_msg_data);
3733: log_exception(l_act_budgets_rec, l_act_util_rec);
3728: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3729: ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 6 : '||l_msg_data);
3730: log_exception(l_act_budgets_rec, l_act_util_rec);
3731: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3732: ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 6 : '||l_msg_data);
3733: log_exception(l_act_budgets_rec, l_act_util_rec);
3734: END IF;
3735:
3736: l_act_budgets_rec := NULL;
3753: l_reduce_line_amount := FALSE ;
3754: l_line_amount_old := NULL ;
3755:
3756: IF G_DEBUG_LOW THEN
3757: ozf_utility_pvt.write_conc_log('l_na_deduction_rule.deduction_identifier_id: ' || l_na_deduction_rule.deduction_identifier_id);
3758: ozf_utility_pvt.write_conc_log('l_start_date ' || l_start_date);
3759: ozf_utility_pvt.write_conc_log('l_end_date ' || l_end_date);
3760: ozf_utility_pvt.write_conc_log('l_net_accrual_offers.qp_list_header_id ' || l_net_accrual_offers.qp_list_header_id);
3761: END IF;
3754: l_line_amount_old := NULL ;
3755:
3756: IF G_DEBUG_LOW THEN
3757: ozf_utility_pvt.write_conc_log('l_na_deduction_rule.deduction_identifier_id: ' || l_na_deduction_rule.deduction_identifier_id);
3758: ozf_utility_pvt.write_conc_log('l_start_date ' || l_start_date);
3759: ozf_utility_pvt.write_conc_log('l_end_date ' || l_end_date);
3760: ozf_utility_pvt.write_conc_log('l_net_accrual_offers.qp_list_header_id ' || l_net_accrual_offers.qp_list_header_id);
3761: END IF;
3762:
3755:
3756: IF G_DEBUG_LOW THEN
3757: ozf_utility_pvt.write_conc_log('l_na_deduction_rule.deduction_identifier_id: ' || l_na_deduction_rule.deduction_identifier_id);
3758: ozf_utility_pvt.write_conc_log('l_start_date ' || l_start_date);
3759: ozf_utility_pvt.write_conc_log('l_end_date ' || l_end_date);
3760: ozf_utility_pvt.write_conc_log('l_net_accrual_offers.qp_list_header_id ' || l_net_accrual_offers.qp_list_header_id);
3761: END IF;
3762:
3763: l_counter := 0;
3756: IF G_DEBUG_LOW THEN
3757: ozf_utility_pvt.write_conc_log('l_na_deduction_rule.deduction_identifier_id: ' || l_na_deduction_rule.deduction_identifier_id);
3758: ozf_utility_pvt.write_conc_log('l_start_date ' || l_start_date);
3759: ozf_utility_pvt.write_conc_log('l_end_date ' || l_end_date);
3760: ozf_utility_pvt.write_conc_log('l_net_accrual_offers.qp_list_header_id ' || l_net_accrual_offers.qp_list_header_id);
3761: END IF;
3762:
3763: l_counter := 0;
3764: FOR l_tm_line IN c_tm_lines(l_na_deduction_rule.deduction_identifier_id
3776:
3777: -- nepanda : Fix for Bug 09204988
3778: -- Fix for 8772550 : calling get_accrualed_amount to apply the Net Acrrual Offers discount
3779: IF G_DEBUG_LOW THEN
3780: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
3781: ozf_utility_pvt.write_conc_log('l_tm_line.utilization_id: ' || l_tm_line.order_line_id);
3782: ozf_utility_pvt.write_conc_log('l_tm_line.line_amount: ' || l_tm_line.line_amount);
3783: ozf_utility_pvt.write_conc_log('l_tm_line.object_type: ' || l_tm_line.object_type);
3784: END IF;
3777: -- nepanda : Fix for Bug 09204988
3778: -- Fix for 8772550 : calling get_accrualed_amount to apply the Net Acrrual Offers discount
3779: IF G_DEBUG_LOW THEN
3780: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
3781: ozf_utility_pvt.write_conc_log('l_tm_line.utilization_id: ' || l_tm_line.order_line_id);
3782: ozf_utility_pvt.write_conc_log('l_tm_line.line_amount: ' || l_tm_line.line_amount);
3783: ozf_utility_pvt.write_conc_log('l_tm_line.object_type: ' || l_tm_line.object_type);
3784: END IF;
3785: -- Fix for 9296109
3778: -- Fix for 8772550 : calling get_accrualed_amount to apply the Net Acrrual Offers discount
3779: IF G_DEBUG_LOW THEN
3780: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
3781: ozf_utility_pvt.write_conc_log('l_tm_line.utilization_id: ' || l_tm_line.order_line_id);
3782: ozf_utility_pvt.write_conc_log('l_tm_line.line_amount: ' || l_tm_line.line_amount);
3783: ozf_utility_pvt.write_conc_log('l_tm_line.object_type: ' || l_tm_line.object_type);
3784: END IF;
3785: -- Fix for 9296109
3786: l_inventory_item_id := null;
3779: IF G_DEBUG_LOW THEN
3780: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
3781: ozf_utility_pvt.write_conc_log('l_tm_line.utilization_id: ' || l_tm_line.order_line_id);
3782: ozf_utility_pvt.write_conc_log('l_tm_line.line_amount: ' || l_tm_line.line_amount);
3783: ozf_utility_pvt.write_conc_log('l_tm_line.object_type: ' || l_tm_line.object_type);
3784: END IF;
3785: -- Fix for 9296109
3786: l_inventory_item_id := null;
3787: l_pricing_quantity := null;
3804: FETCH c_get_order_details INTO l_inventory_item_id, l_pricing_quantity, l_pricing_quantity_uom, l_order_line_id, l_order_org_id,
3805: l_shipping_quantity, l_shipping_quantity_uom, l_shipping_quantity2, l_shipping_quantity_uom2, l_fulfillment_base; -- catch weight ER
3806: CLOSE c_get_order_details;
3807: -- Catch Weight ER - start
3808: /*OZF_UTILITY_PVT.get_catch_weight_quantity (
3809: p_inventory_item_id => l_inventory_item_id,
3810: p_order_line_id => l_order_line_id,
3811: x_return_status => l_return_status,
3812: x_cw_quantity => l_cw_quantity,
3837: ,NULL -- from_name
3838: ,NULL -- to_name
3839: );
3840: IF (l_shipping_quantity = -99999) THEN
3841: ozf_utility_pvt.write_conc_log ('Error in UOM conversion');
3842: END IF;
3843: ELSE
3844: l_pricing_quantity := l_cw_quantity ;
3845: END IF;
3842: END IF;
3843: ELSE
3844: l_pricing_quantity := l_cw_quantity ;
3845: END IF;
3846: ozf_utility_pvt.write_conc_log('Catch Weight - l_pricing_quantity = '|| l_pricing_quantity);
3847: -- Catch Weight ER - end
3848: END IF; --IF l_tm_line.object_type = 'CM'
3849:
3850: IF l_net_accrual_offers.orig_org_id IS NULL THEN
3866: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3867: FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
3868: FND_MSG_PUB.add;
3869: END IF;
3870: ozf_utility_pvt.write_conc_log;
3871: RAISE FND_API.G_EXC_ERROR;
3872: END IF;
3873: END IF;
3874:
3872: END IF;
3873: END IF;
3874:
3875: IF G_DEBUG_LOW THEN
3876: ozf_utility_pvt.write_conc_log('l_inventory_item_id: ' || l_inventory_item_id);
3877: ozf_utility_pvt.write_conc_log('l_pricing_quantity: ' || l_pricing_quantity);
3878: ozf_utility_pvt.write_conc_log('l_pricing_quantity_uom: ' || l_pricing_quantity_uom);
3879: ozf_utility_pvt.write_conc_log('l_order_line_id: ' || l_order_line_id);
3880: ozf_utility_pvt.write_conc_log('l_order_org_id ' || l_order_org_id);
3873: END IF;
3874:
3875: IF G_DEBUG_LOW THEN
3876: ozf_utility_pvt.write_conc_log('l_inventory_item_id: ' || l_inventory_item_id);
3877: ozf_utility_pvt.write_conc_log('l_pricing_quantity: ' || l_pricing_quantity);
3878: ozf_utility_pvt.write_conc_log('l_pricing_quantity_uom: ' || l_pricing_quantity_uom);
3879: ozf_utility_pvt.write_conc_log('l_order_line_id: ' || l_order_line_id);
3880: ozf_utility_pvt.write_conc_log('l_order_org_id ' || l_order_org_id);
3881: END IF;
3874:
3875: IF G_DEBUG_LOW THEN
3876: ozf_utility_pvt.write_conc_log('l_inventory_item_id: ' || l_inventory_item_id);
3877: ozf_utility_pvt.write_conc_log('l_pricing_quantity: ' || l_pricing_quantity);
3878: ozf_utility_pvt.write_conc_log('l_pricing_quantity_uom: ' || l_pricing_quantity_uom);
3879: ozf_utility_pvt.write_conc_log('l_order_line_id: ' || l_order_line_id);
3880: ozf_utility_pvt.write_conc_log('l_order_org_id ' || l_order_org_id);
3881: END IF;
3882:
3875: IF G_DEBUG_LOW THEN
3876: ozf_utility_pvt.write_conc_log('l_inventory_item_id: ' || l_inventory_item_id);
3877: ozf_utility_pvt.write_conc_log('l_pricing_quantity: ' || l_pricing_quantity);
3878: ozf_utility_pvt.write_conc_log('l_pricing_quantity_uom: ' || l_pricing_quantity_uom);
3879: ozf_utility_pvt.write_conc_log('l_order_line_id: ' || l_order_line_id);
3880: ozf_utility_pvt.write_conc_log('l_order_org_id ' || l_order_org_id);
3881: END IF;
3882:
3883: -- Fix for Bug 16301672
3876: ozf_utility_pvt.write_conc_log('l_inventory_item_id: ' || l_inventory_item_id);
3877: ozf_utility_pvt.write_conc_log('l_pricing_quantity: ' || l_pricing_quantity);
3878: ozf_utility_pvt.write_conc_log('l_pricing_quantity_uom: ' || l_pricing_quantity_uom);
3879: ozf_utility_pvt.write_conc_log('l_order_line_id: ' || l_order_line_id);
3880: ozf_utility_pvt.write_conc_log('l_order_org_id ' || l_order_org_id);
3881: END IF;
3882:
3883: -- Fix for Bug 16301672
3884: IF l_order_line_id_old IS NOT NULL THEN
3909: ,p_quantity => l_tm_line.pricing_quantity
3910: ,p_uom => l_tm_line.pricing_quantity_uom);*/
3911:
3912: IF G_DEBUG_LOW THEN
3913: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (-) With l_line_acc_amount: '|| l_line_acc_amount);
3914: END IF;
3915:
3916: l_tm_line.line_amount := l_line_acc_amount;
3917:
3923: --Added for bug 7030415
3924: OPEN c_get_conversion_type(l_tm_line.org_id);
3925: FETCH c_get_conversion_type INTO l_exchange_rate_type;
3926: CLOSE c_get_conversion_type;
3927: ozf_utility_pvt.convert_currency(
3928: x_return_status => l_return_status
3929: ,p_from_currency => l_tm_line.currency_code
3930: ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
3931: ,p_conv_type => l_exchange_rate_type
3937: --
3938: IF l_return_status = Fnd_Api.g_ret_sts_error
3939: THEN
3940: --
3941: ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3942: RAISE Fnd_Api.g_exc_error;
3943: --
3944: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3945: THEN
3943: --
3944: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3945: THEN
3946: --
3947: ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3948: RAISE Fnd_Api.g_exc_unexpected_error;
3949: --
3950: END IF;
3951: --added for bug 8688281
3973: END IF;
3974:
3975: --
3976: IF G_DEBUG_LOW THEN
3977: ozf_utility_pvt.write_conc_log('l_tm_dedu_amount ' || l_tm_dedu_amount);
3978: ozf_utility_pvt.write_conc_log('l_inventory_item_id_old ' || l_inventory_item_id_old);
3979: ozf_utility_pvt.write_conc_log('l_inventory_item_id ' || l_inventory_item_id);
3980: ozf_utility_pvt.write_conc_log('l_index ' || l_index);
3981: END IF;
3974:
3975: --
3976: IF G_DEBUG_LOW THEN
3977: ozf_utility_pvt.write_conc_log('l_tm_dedu_amount ' || l_tm_dedu_amount);
3978: ozf_utility_pvt.write_conc_log('l_inventory_item_id_old ' || l_inventory_item_id_old);
3979: ozf_utility_pvt.write_conc_log('l_inventory_item_id ' || l_inventory_item_id);
3980: ozf_utility_pvt.write_conc_log('l_index ' || l_index);
3981: END IF;
3982:
3975: --
3976: IF G_DEBUG_LOW THEN
3977: ozf_utility_pvt.write_conc_log('l_tm_dedu_amount ' || l_tm_dedu_amount);
3978: ozf_utility_pvt.write_conc_log('l_inventory_item_id_old ' || l_inventory_item_id_old);
3979: ozf_utility_pvt.write_conc_log('l_inventory_item_id ' || l_inventory_item_id);
3980: ozf_utility_pvt.write_conc_log('l_index ' || l_index);
3981: END IF;
3982:
3983: IF l_tm_dedu_amount <> 0 THEN
3976: IF G_DEBUG_LOW THEN
3977: ozf_utility_pvt.write_conc_log('l_tm_dedu_amount ' || l_tm_dedu_amount);
3978: ozf_utility_pvt.write_conc_log('l_inventory_item_id_old ' || l_inventory_item_id_old);
3979: ozf_utility_pvt.write_conc_log('l_inventory_item_id ' || l_inventory_item_id);
3980: ozf_utility_pvt.write_conc_log('l_index ' || l_index);
3981: END IF;
3982:
3983: IF l_tm_dedu_amount <> 0 THEN
3984:
3993:
3994: IF l_act_budgets_rec_tbl(l_index-1).parent_src_curr <> l_act_budgets_rec_tbl(l_index-1).request_currency THEN
3995: l_parent_src_apprvd_amt := 0;
3996:
3997: ozf_utility_pvt.convert_currency (x_return_status => l_return_status
3998: ,p_from_currency => l_act_budgets_rec_tbl(l_index-1).request_currency
3999: ,p_to_currency => l_act_budgets_rec_tbl(l_index-1).parent_src_curr
4000: ,p_conv_date => l_sysdate
4001: ,p_from_amount => l_tm_dedu_amount
4003: );
4004: l_act_budgets_rec_tbl(l_index-1).parent_src_apprvd_amt := l_act_budgets_rec_tbl(l_index-1).parent_src_apprvd_amt + (-1 * l_parent_src_apprvd_amt);
4005:
4006: IF G_DEBUG_LOW THEN
4007: ozf_utility_pvt.write_conc_log('request_currency '|| l_act_budgets_rec_tbl(l_index).request_currency);
4008: END IF;
4009: END IF;
4010: END IF;
4011:
4022: l_act_budgets_rec_tbl(l_index).status_code := 'APPROVED';
4023: l_act_budgets_rec_tbl(l_index).approved_amount := -1 * l_tm_dedu_amount;
4024: l_act_budgets_rec_tbl(l_index).approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
4025: l_act_budgets_rec_tbl(l_index).approval_date := l_sysdate;--nepanda : fix for bug 8766564
4026: l_act_budgets_rec_tbl(l_index).approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
4027: l_act_budgets_rec_tbl(l_index).justification := 'NA: TM DEDUCTION' || TO_CHAR(l_sysdate, 'MON-DD-YYYY');
4028: l_act_budgets_rec_tbl(l_index).transfer_type := 'UTILIZED';
4029: l_act_budgets_rec_tbl(l_index).requester_id := l_net_accrual_offers.owner_id;
4030: IF l_net_accrual_offers.orig_org_id IS NULL THEN
4051:
4052: END LOOP;--FOR l_tm_line IN c_tm_lines
4053:
4054: IF G_DEBUG_LOW THEN
4055: ozf_utility_pvt.write_conc_log('l_act_budgets_rec_tbl.count ' || l_act_budgets_rec_tbl.count);
4056: END IF;
4057:
4058: IF l_act_budgets_rec_tbl.count > 0 THEN
4059: FOR c IN l_act_budgets_rec_tbl.FIRST..l_act_budgets_rec_tbl.LAST
4067: l_act_budgets_rec.budget_source_id := l_act_budgets_rec_tbl(c).budget_source_id;
4068: l_act_budgets_rec.request_amount := l_act_budgets_rec_tbl(c).request_amount;
4069:
4070: IF G_DEBUG_LOW THEN
4071: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.request_amount '|| l_act_budgets_rec.request_amount);
4072: ozf_utility_pvt.write_conc_log('l_act_budgets_rec_tbl(c).request_amount '|| l_act_budgets_rec_tbl(c).request_amount);
4073: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.budget_source_id '|| l_act_budgets_rec.budget_source_id);
4074: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.act_budget_used_by_id '|| l_act_budgets_rec.act_budget_used_by_id);
4075: END IF;
4068: l_act_budgets_rec.request_amount := l_act_budgets_rec_tbl(c).request_amount;
4069:
4070: IF G_DEBUG_LOW THEN
4071: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.request_amount '|| l_act_budgets_rec.request_amount);
4072: ozf_utility_pvt.write_conc_log('l_act_budgets_rec_tbl(c).request_amount '|| l_act_budgets_rec_tbl(c).request_amount);
4073: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.budget_source_id '|| l_act_budgets_rec.budget_source_id);
4074: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.act_budget_used_by_id '|| l_act_budgets_rec.act_budget_used_by_id);
4075: END IF;
4076:
4069:
4070: IF G_DEBUG_LOW THEN
4071: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.request_amount '|| l_act_budgets_rec.request_amount);
4072: ozf_utility_pvt.write_conc_log('l_act_budgets_rec_tbl(c).request_amount '|| l_act_budgets_rec_tbl(c).request_amount);
4073: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.budget_source_id '|| l_act_budgets_rec.budget_source_id);
4074: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.act_budget_used_by_id '|| l_act_budgets_rec.act_budget_used_by_id);
4075: END IF;
4076:
4077: l_act_budgets_rec.request_currency := l_act_budgets_rec_tbl(c).request_currency;
4070: IF G_DEBUG_LOW THEN
4071: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.request_amount '|| l_act_budgets_rec.request_amount);
4072: ozf_utility_pvt.write_conc_log('l_act_budgets_rec_tbl(c).request_amount '|| l_act_budgets_rec_tbl(c).request_amount);
4073: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.budget_source_id '|| l_act_budgets_rec.budget_source_id);
4074: ozf_utility_pvt.write_conc_log('l_act_budgets_rec.act_budget_used_by_id '|| l_act_budgets_rec.act_budget_used_by_id);
4075: END IF;
4076:
4077: l_act_budgets_rec.request_currency := l_act_budgets_rec_tbl(c).request_currency;
4078: l_act_budgets_rec.request_date := l_sysdate;
4102: l_act_util_rec.order_line_id := l_act_util_rec_tbl(c).order_line_id;
4103: l_act_util_rec.org_id := l_act_util_rec_tbl(c).org_id; --- nepanda forward port
4104:
4105:
4106: ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4107: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4108: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4109: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4110: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4103: l_act_util_rec.org_id := l_act_util_rec_tbl(c).org_id; --- nepanda forward port
4104:
4105:
4106: ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4107: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4108: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4109: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4110: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4111: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4104:
4105:
4106: ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4107: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4108: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4109: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4110: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4111: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4112: --nirprasa,12.2 ER 8399135.
4105:
4106: ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4107: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4108: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4109: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4110: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4111: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4112: --nirprasa,12.2 ER 8399135.
4113: l_act_util_rec.plan_currency_code := l_net_accrual_offers.fund_request_curr_code;
4106: ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4107: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4108: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4109: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4110: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4111: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4112: --nirprasa,12.2 ER 8399135.
4113: l_act_util_rec.plan_currency_code := l_net_accrual_offers.fund_request_curr_code;
4114: l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
4107: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4108: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4109: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4110: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4111: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4112: --nirprasa,12.2 ER 8399135.
4113: l_act_util_rec.plan_currency_code := l_net_accrual_offers.fund_request_curr_code;
4114: l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
4115: l_act_util_rec.fund_request_amount := -1 * l_tm_dedu_amount;
4125: ,x_act_budget_id => l_act_budget_id
4126: ,x_utilized_amount => l_utilized_amount);
4127:
4128: IF G_DEBUG_LOW THEN
4129: --ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
4130: ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec_tbl(c).request_currency || '/' || l_act_budgets_rec_tbl(c).request_amount || '/' || l_utilized_amount);
4131: END IF;
4132:
4133: l_utilized_amount := 0;
4126: ,x_utilized_amount => l_utilized_amount);
4127:
4128: IF G_DEBUG_LOW THEN
4129: --ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
4130: ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec_tbl(c).request_currency || '/' || l_act_budgets_rec_tbl(c).request_amount || '/' || l_utilized_amount);
4131: END IF;
4132:
4133: l_utilized_amount := 0;
4134:
4131: END IF;
4132:
4133: l_utilized_amount := 0;
4134:
4135: ozf_utility_pvt.write_conc_log('Msg from Budget API : '||l_msg_data);
4136:
4137: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4138: ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 7 : '||l_msg_data);
4139: log_exception(l_act_budgets_rec, l_act_util_rec);
4134:
4135: ozf_utility_pvt.write_conc_log('Msg from Budget API : '||l_msg_data);
4136:
4137: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4138: ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 7 : '||l_msg_data);
4139: log_exception(l_act_budgets_rec, l_act_util_rec);
4140: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4141: ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 7 : '||l_msg_data);
4142: log_exception(l_act_budgets_rec, l_act_util_rec);
4137: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4138: ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 7 : '||l_msg_data);
4139: log_exception(l_act_budgets_rec, l_act_util_rec);
4140: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4141: ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 7 : '||l_msg_data);
4142: log_exception(l_act_budgets_rec, l_act_util_rec);
4143: END IF;
4144:
4145: END IF; --IF l_act_budgets_rec_tbl(c).request_amount <> 0 THEN
4152: l_act_budgets_rec_tbl.DELETE;
4153:
4154:
4155: IF G_DEBUG_LOW THEN
4156: ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4157: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4158: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4159: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4160: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4153:
4154:
4155: IF G_DEBUG_LOW THEN
4156: ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4157: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4158: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4159: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4160: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4161: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4154:
4155: IF G_DEBUG_LOW THEN
4156: ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4157: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4158: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4159: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4160: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4161: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4162: END IF; -- end amount <> 0
4155: IF G_DEBUG_LOW THEN
4156: ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4157: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4158: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4159: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4160: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4161: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4162: END IF; -- end amount <> 0
4163:
4156: ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4157: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4158: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4159: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4160: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4161: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4162: END IF; -- end amount <> 0
4163:
4164: l_act_budgets_rec := NULL;
4157: ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4158: ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4159: ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4160: ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4161: ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4162: END IF; -- end amount <> 0
4163:
4164: l_act_budgets_rec := NULL;
4165: l_act_util_rec := NULL;
4167: END IF; --ELSIF l_na_deduction_rule.transaction_source_code = 'TM' THEN
4168:
4169: END LOOP; -- end l_na_rule_line
4170:
4171: ozf_utility_pvt.write_conc_log('-- Done Processing Deduction Rules -- ');
4172: ozf_utility_pvt.write_conc_log('--------------------------------------');
4173:
4174: IF l_net_accrual_offers.latest_na_completion_date IS NULL OR l_net_accrual_offers.latest_na_completion_date < l_as_of_date THEN
4175: UPDATE ozf_offers
4168:
4169: END LOOP; -- end l_na_rule_line
4170:
4171: ozf_utility_pvt.write_conc_log('-- Done Processing Deduction Rules -- ');
4172: ozf_utility_pvt.write_conc_log('--------------------------------------');
4173:
4174: IF l_net_accrual_offers.latest_na_completion_date IS NULL OR l_net_accrual_offers.latest_na_completion_date < l_as_of_date THEN
4175: UPDATE ozf_offers
4176: SET latest_na_completion_date = l_as_of_date
4180: <
4181: --------------- Start Processing IDSM lines ------------------------
4182: IF l_net_accrual_offers.sales_method_flag IS NULL OR l_net_accrual_offers.sales_method_flag = 'I' THEN
4183: --
4184: ozf_utility_pvt.write_conc_log('Start Processing IDSM Lines');
4185: l_idsm_line_tbl.delete;
4186: l_accrual_amount := 0;
4187:
4188: OPEN c_idsm_line(l_net_accrual_offers.start_date_active, l_net_accrual_offers.end_date_active, l_offer_org_id, l_net_accrual_offers.resale_line_id_processed);
4194: -- To handle NO DATA FOUND for c_idsm_line CURSOR
4195: IF l_idsm_line_tbl.FIRST IS NULL
4196: THEN
4197: --
4198: ozf_utility_pvt.write_conc_log('No Data found in c_idsm_line CURSOR');
4199: EXIT;
4200: --
4201: END IF;
4202: --
4223: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4224: FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
4225: FND_MSG_PUB.add;
4226: END IF;
4227: ozf_utility_pvt.write_conc_log;
4228: RAISE FND_API.G_EXC_ERROR;
4229: END IF;
4230: END IF;
4231:
4233:
4234: l_idsm_line_processed := l_idsm_line_tbl(i).line_id;
4235:
4236: IF G_DEBUG_LOW THEN
4237: ozf_utility_pvt.write_conc_log('Resale Line_Id: ' || l_idsm_line_tbl(i).line_id);
4238: END IF;
4239:
4240: l_line_amount := ( NVL(l_idsm_line_tbl(i).shipped_quantity,l_idsm_line_tbl(i).fulfilled_quantity)
4241: * l_idsm_line_tbl(i).unit_selling_price );
4293: --
4294: END IF; -- Done qualfiying the customer
4295:
4296: IF G_DEBUG_LOW THEN
4297: ozf_utility_pvt.write_conc_log('Did Customer qualify: ' || l_customer_qualified);
4298: END IF;
4299:
4300: -- Fetch Currency Code on the IDSM
4301: l_order_curr_code := l_idsm_line_tbl(i).transactional_curr_code ;
4310: --Added for bug 7030415
4311: OPEN c_get_conversion_type(l_idsm_line_tbl(i).org_id);
4312: FETCH c_get_conversion_type INTO l_exchange_rate_type;
4313: CLOSE c_get_conversion_type;
4314: ozf_utility_pvt.convert_currency(x_return_status => l_return_status
4315: ,p_from_currency => l_order_curr_code
4316: ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
4317: ,p_conv_type => l_exchange_rate_type
4318: --,p_conv_date => l_idsm_line_tbl(i).conv_date
4327: END IF;
4328:
4329: IF l_return_status = Fnd_Api.g_ret_sts_error
4330: THEN
4331: ozf_utility_pvt.write_conc_log('Exp Error from Convert_Currency: ' || l_return_status);
4332: RAISE Fnd_Api.g_exc_error;
4333: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4334: THEN
4335: ozf_utility_pvt.write_conc_log('Unexp Error from Convert_Currency: ' || l_return_status);
4331: ozf_utility_pvt.write_conc_log('Exp Error from Convert_Currency: ' || l_return_status);
4332: RAISE Fnd_Api.g_exc_error;
4333: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4334: THEN
4335: ozf_utility_pvt.write_conc_log('Unexp Error from Convert_Currency: ' || l_return_status);
4336: RAISE Fnd_Api.g_exc_unexpected_error;
4337: END IF;
4338: --
4339: END IF;
4342: IF l_net_accrual_offers.custom_setup_id = 105
4343: THEN
4344: --
4345: IF G_DEBUG_LOW THEN
4346: ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (+)');
4347: END IF;
4348: pv_referral_comp_pub.get_beneficiary (p_api_version => 1.0,
4349: p_init_msg_list => FND_API.g_true,
4350: p_commit => FND_API.g_false,
4357: x_return_status => l_return_status,
4358: x_msg_count => l_msg_count,
4359: x_msg_data => l_msg_data);
4360: IF G_DEBUG_LOW THEN
4361: ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (-) With Status: ' || l_return_status);
4362: ozf_utility_pvt.write_conc_log('l_benificiary_id / l_referral_id: ' || l_beneficiary_id || ' / ' || l_referral_id);
4363: END IF;
4364:
4365: IF l_return_status = Fnd_Api.g_ret_sts_error
4358: x_msg_count => l_msg_count,
4359: x_msg_data => l_msg_data);
4360: IF G_DEBUG_LOW THEN
4361: ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (-) With Status: ' || l_return_status);
4362: ozf_utility_pvt.write_conc_log('l_benificiary_id / l_referral_id: ' || l_beneficiary_id || ' / ' || l_referral_id);
4363: END IF;
4364:
4365: IF l_return_status = Fnd_Api.g_ret_sts_error
4366: THEN
4363: END IF;
4364:
4365: IF l_return_status = Fnd_Api.g_ret_sts_error
4366: THEN
4367: ozf_utility_pvt.write_conc_log('Exp Error from Get_Beneficiary: ' || l_return_status);
4368: RAISE Fnd_Api.g_exc_error;
4369: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4370: THEN
4371: ozf_utility_pvt.write_conc_log('Unexp Error from Get_Beneficiary: ' || l_return_status);
4367: ozf_utility_pvt.write_conc_log('Exp Error from Get_Beneficiary: ' || l_return_status);
4368: RAISE Fnd_Api.g_exc_error;
4369: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4370: THEN
4371: ozf_utility_pvt.write_conc_log('Unexp Error from Get_Beneficiary: ' || l_return_status);
4372: RAISE Fnd_Api.g_exc_unexpected_error;
4373: END IF;
4374: --
4375:
4376: IF ( l_beneficiary_id IS NOT NULL )
4377: THEN
4378: --------------------------- Derive Accrual Amount -------------------------
4379: IF G_DEBUG_LOW THEN
4380: ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (+)');
4381: END IF;
4382:
4383: l_line_acc_amount := get_pv_accrual_amount(p_product_id => l_idsm_line_tbl(i).inventory_item_id
4384: ,p_line_amt => l_line_amount
4387: ,p_list_hdr_id => l_net_accrual_offers.qp_list_header_id
4388: ,p_referral_id => l_referral_id
4389: ,p_order_hdr_id => l_idsm_line_tbl(i).header_id);
4390: IF G_DEBUG_LOW THEN
4391: ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (-) With l_line_acc_amount: ' || l_line_acc_amount);
4392: END IF;
4393: --
4394: ELSE
4395: --
4392: END IF;
4393: --
4394: ELSE
4395: --
4396: ozf_utility_pvt.write_conc_log('No Beneficiary derived from PV_Referral_Comp_Pub. Utilization will not be created');
4397: --
4398: END IF;
4399: --
4400: l_utilization_type := 'LEAD_ACCRUAL';
4403: ELSE
4404: --
4405: --------------------------- Derive Accrual Amount -------------------------
4406: IF G_DEBUG_LOW THEN
4407: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
4408: END IF;
4409: l_line_acc_amount := get_accrualed_amount(p_product_id => l_idsm_line_tbl(i).inventory_item_id
4410: ,p_line_amt => l_line_amount
4411: ,p_quantity => l_idsm_line_tbl(i).pricing_quantity
4410: ,p_line_amt => l_line_amount
4411: ,p_quantity => l_idsm_line_tbl(i).pricing_quantity
4412: ,p_uom => l_idsm_line_tbl(i).pricing_quantity_uom);
4413: IF G_DEBUG_LOW THEN
4414: ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (-) With l_line_acc_amount: ' || l_line_acc_amount);
4415: END IF;
4416: --
4417:
4418: --
4455: l_act_budgets_rec.status_code := 'APPROVED';
4456: l_act_budgets_rec.approved_amount := l_line_acc_amount;
4457: l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
4458: l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
4459: l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
4460: l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
4461: l_act_budgets_rec.transfer_type := 'UTILIZED';
4462: l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
4463:
4489: ,x_act_budget_id => l_act_budget_id
4490: ,x_utilized_amount => l_utilized_amount);
4491: --
4492: IF G_DEBUG_LOW THEN
4493: ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
4494: END IF;
4495:
4496: IF l_return_status = Fnd_Api.g_ret_sts_error
4497: THEN
4494: END IF;
4495:
4496: IF l_return_status = Fnd_Api.g_ret_sts_error
4497: THEN
4498: ozf_utility_pvt.write_conc_log('Exp Error 8 : Process_Act_Budgets: Resale line_id ( ' || l_idsm_line_tbl(i).line_id
4499: || ' ) Error: ' || l_msg_data);
4500: log_exception(l_act_budgets_rec, l_act_util_rec);
4501: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4502: THEN
4499: || ' ) Error: ' || l_msg_data);
4500: log_exception(l_act_budgets_rec, l_act_util_rec);
4501: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4502: THEN
4503: ozf_utility_pvt.write_conc_log('UnExp Error 8 : Process_Act_Budgets: Resale line_id ( ' || l_idsm_line_tbl(i).line_id
4504: || ' ) Error: ' || l_msg_data);
4505: log_exception(l_act_budgets_rec, l_act_util_rec);
4506: END IF;
4507:
4563: l_act_budgets_rec.status_code := 'APPROVED';
4564: l_act_budgets_rec.approved_amount := l_accrual_amount;
4565: l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
4566: l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
4567: l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
4568: l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
4569: l_act_budgets_rec.transfer_type := 'UTILIZED';
4570: l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
4571:
4591: ,x_act_budget_id => l_act_budget_id
4592: ,x_utilized_amount => l_utilized_amount);
4593:
4594: IF G_DEBUG_LOW THEN
4595: ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
4596: END IF;
4597:
4598: IF l_return_status = Fnd_Api.g_ret_sts_error
4599: THEN
4596: END IF;
4597:
4598: IF l_return_status = Fnd_Api.g_ret_sts_error
4599: THEN
4600: ozf_utility_pvt.write_conc_log('Exp Error 9 : Process_Act_Budgets Error: ' || l_msg_data );
4601: log_exception(l_act_budgets_rec, l_act_util_rec);
4602: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4603: THEN
4604: ozf_utility_pvt.write_conc_log('UnExp Error 9 : Process_Act_Budgets Error: ' || l_msg_data );
4600: ozf_utility_pvt.write_conc_log('Exp Error 9 : Process_Act_Budgets Error: ' || l_msg_data );
4601: log_exception(l_act_budgets_rec, l_act_util_rec);
4602: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4603: THEN
4604: ozf_utility_pvt.write_conc_log('UnExp Error 9 : Process_Act_Budgets Error: ' || l_msg_data );
4605: log_exception(l_act_budgets_rec, l_act_util_rec);
4606: END IF;
4607:
4608: l_utilized_amount := 0;
4657: RETCODE := '1';
4658: END; --End of BEGIN/EXCEPTION/END Block
4659:
4660: <
4661: ozf_utility_pvt.write_conc_log('-- NEXT_OFFER -- ');
4662: END LOOP; --FOR l_net_accrual_offers IN c_net_accrual_offers
4663:
4664: --AMITAMKU bug 14692296 - moved truncate out of the loop to end of the program
4665: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_customers_temp';
4665: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_customers_temp';
4666: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_products_temp';
4667: EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.OZF_NA_UNIQUE_PRODUCTS_TEMP';
4668:
4669: ozf_utility_pvt.write_conc_log('-- Done -- ' || to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
4670:
4671: Fnd_Msg_Pub.Count_AND_Get(p_count => l_msg_count,
4672: p_data => l_msg_data,
4673: p_encoded => Fnd_Api.G_FALSE);
4673: p_encoded => Fnd_Api.G_FALSE);
4674:
4675: EXCEPTION
4676:
4677: WHEN OZF_Utility_PVT.resource_locked THEN
4678: OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
4679:
4680: WHEN FND_API.G_EXC_ERROR THEN
4681: --ROLLBACK TO net_accrual_engine;
4674:
4675: EXCEPTION
4676:
4677: WHEN OZF_Utility_PVT.resource_locked THEN
4678: OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
4679:
4680: WHEN FND_API.G_EXC_ERROR THEN
4681: --ROLLBACK TO net_accrual_engine;
4682: ROLLBACK;