[Home] [Help]
44: AND a.request_id = p_request_id;
45: EXCEPTION
46: WHEN No_Data_Found
47: THEN
48: qp_bulk_loader_pub.write_log(l_routine || 'no line in qp_interface_list_lines for ' || p_orig_sys_line_ref_a);
49: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
50: WHEN OTHERS
51: THEN
52: qp_bulk_loader_pub.write_log(l_routine || '#100 Unexpected exception ' || SQLERRM);
48: qp_bulk_loader_pub.write_log(l_routine || 'no line in qp_interface_list_lines for ' || p_orig_sys_line_ref_a);
49: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
50: WHEN OTHERS
51: THEN
52: qp_bulk_loader_pub.write_log(l_routine || '#100 Unexpected exception ' || SQLERRM);
53: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
54: END;
55:
56: --fetching dates for second line, that can be either in interface table or setup table
72: AND a.orig_sys_line_ref = p_orig_sys_line_ref_b;
73: EXCEPTION --begin2
74: WHEN No_Data_Found
75: THEN
76: qp_bulk_loader_pub.write_log(l_routine || ' date not found in qp_list_lines and qp_interface_list_lines');
77: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78: END; --begin2
79: END; --begin1
80:
98:
99: EXCEPTION
100: WHEN OTHERS
101: THEN
102: qp_bulk_loader_pub.write_log(l_routine || '#200 Unexpected exception ' || SQLERRM);
103: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104: END CHECK_DATES_OVERLAP; --check_dates_overlap;
105:
106: --mark duplicate line - insert into qp_interface_errors
139:
140: EXCEPTION
141: WHEN OTHERS
142: THEN
143: qp_bulk_loader_pub.write_log(l_routine || 'Unexpected exception ' || SQLERRM);
144: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
145: END mark_dup_line;
146:
147: PROCEDURE dup_line_check(p_request_id NUMBER)
308: l_routine VARCHAR2(230) := 'DUP_LINE_CHECK ';
309: BEGIN
310: /* First we check for duplicates in qp_interface_pricing_attribs(i_mode = 1)
311: * then in qp_pricing_attributes(i_mode = 2) */
312: qp_bulk_loader_pub.write_log('In ' || l_routine);
313: FOR i_mode IN 1..2
314: LOOP
315: qp_bulk_loader_pub.write_log('i_mode = ' || i_mode);
316: IF i_mode = 1
311: * then in qp_pricing_attributes(i_mode = 2) */
312: qp_bulk_loader_pub.write_log('In ' || l_routine);
313: FOR i_mode IN 1..2
314: LOOP
315: qp_bulk_loader_pub.write_log('i_mode = ' || i_mode);
316: IF i_mode = 1
317: THEN
318: OPEN c_dup_line_rec;
319: ELSE
332: l_dup_line_rec.a_orig_sys_line_ref,
333: l_dup_line_rec.b_orig_sys_line_ref
334: LIMIT l_limit;
335:
336: qp_bulk_loader_pub.write_log('count = ' || l_dup_line_rec.a_orig_sys_line_ref.count);
337:
338: EXIT WHEN l_dup_line_rec.a_orig_sys_line_ref.COUNT = 0;
339: ELSIF i_mode = 2 --IF i_mode = 1
340: THEN
343: l_dup_line_rec.a_orig_sys_line_ref,
344: l_dup_line_rec.b_orig_sys_line_ref
345: LIMIT l_limit;
346:
347: qp_bulk_loader_pub.write_log('count = ' || l_dup_line_rec.a_orig_sys_line_ref.count);
348:
349: EXIT WHEN l_dup_line_rec.a_orig_sys_line_ref.COUNT = 0;
350: END IF; --IF i_mode = 1
351:
352: FOR i IN 1..l_dup_line_rec.a_orig_sys_line_ref.Count
353: LOOP
354: IF l_found_duplicate_tbl.EXISTS(l_dup_line_rec.a_orig_sys_line_ref(i)) --IF3
355: THEN
356: qp_bulk_loader_pub.write_log('a = ' || l_dup_line_rec.a_orig_sys_line_ref(i));
357: qp_bulk_loader_pub.write_log('b = ' || l_dup_line_rec.b_orig_sys_line_ref(i));
358: qp_bulk_loader_pub.write_log('already found these are duplicates');
359: ELSE --IF3
360: /* do not process price break */
353: LOOP
354: IF l_found_duplicate_tbl.EXISTS(l_dup_line_rec.a_orig_sys_line_ref(i)) --IF3
355: THEN
356: qp_bulk_loader_pub.write_log('a = ' || l_dup_line_rec.a_orig_sys_line_ref(i));
357: qp_bulk_loader_pub.write_log('b = ' || l_dup_line_rec.b_orig_sys_line_ref(i));
358: qp_bulk_loader_pub.write_log('already found these are duplicates');
359: ELSE --IF3
360: /* do not process price break */
361: IF i_mode = 1 --IF1
354: IF l_found_duplicate_tbl.EXISTS(l_dup_line_rec.a_orig_sys_line_ref(i)) --IF3
355: THEN
356: qp_bulk_loader_pub.write_log('a = ' || l_dup_line_rec.a_orig_sys_line_ref(i));
357: qp_bulk_loader_pub.write_log('b = ' || l_dup_line_rec.b_orig_sys_line_ref(i));
358: qp_bulk_loader_pub.write_log('already found these are duplicates');
359: ELSE --IF3
360: /* do not process price break */
361: IF i_mode = 1 --IF1
362: THEN
478: l_dup_line_rec.a_orig_sys_line_ref(i));
479:
480: --mark it, so we don't have to procss the same line again!!
481: l_found_duplicate_tbl(l_dup_line_rec.a_orig_sys_line_ref(i)) := 'Y';
482: qp_bulk_loader_pub.write_log('Found a duplicate b/w '
483: || l_dup_line_rec.a_orig_sys_line_ref(i) ||
484: ' and ' || l_dup_line_rec.b_orig_sys_line_ref(i));
485: END IF; --IF l_pa_count = 0
486: END IF; --IF l_a_pa_count = l_b_pa_count
496: ELSE
497: CLOSE c_dup_line_rec1;
498: END IF;
499: END LOOP; --FOR i_mode IN 1..2
500: qp_bulk_loader_pub.write_log(l_routine || 'Done, found ' || l_found_duplicate_tbl.COUNT || ' duplicates');
501: EXCEPTION
502: WHEN OTHERS
503: THEN
504: qp_bulk_loader_pub.write_log(l_routine || 'Unexpected Exception ' || SQLERRM);
500: qp_bulk_loader_pub.write_log(l_routine || 'Done, found ' || l_found_duplicate_tbl.COUNT || ' duplicates');
501: EXCEPTION
502: WHEN OTHERS
503: THEN
504: qp_bulk_loader_pub.write_log(l_routine || 'Unexpected Exception ' || SQLERRM);
505: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
506: END dup_line_check; -- dup_line_check
507: --End Bug No: 8854118, shaneed
508:
535: l_count NUMBER := 0;
536: BEGIN
537:
538:
539: qp_bulk_loader_pub.write_log( 'In Desc Flex '||p_flex_name);
540:
541: g_context := NULL;
542: g_attribute1 := NULL;
543: g_attribute2 := NULL;
559:
560:
561: -- Copying values into global variables
562: l_count := fnd_flex_descval.segment_count;
563: qp_bulk_loader_pub.write_log( 'segment count='||to_char(l_count));
564:
565: FOR i IN 1..l_count LOOP
566:
567: qp_bulk_loader_pub.write_log( 'segment col nam='||FND_FLEX_DESCVAL.segment_column_name(i));
563: qp_bulk_loader_pub.write_log( 'segment count='||to_char(l_count));
564:
565: FOR i IN 1..l_count LOOP
566:
567: qp_bulk_loader_pub.write_log( 'segment col nam='||FND_FLEX_DESCVAL.segment_column_name(i));
568: qp_bulk_loader_pub.write_log( 'segment ID='|| FND_FLEX_DESCVAL.segment_id(i));
569:
570: IF FND_FLEX_DESCVAL.segment_column_name(i) = g_context_name THEN
571: g_context := FND_FLEX_DESCVAL.segment_id(i);
564:
565: FOR i IN 1..l_count LOOP
566:
567: qp_bulk_loader_pub.write_log( 'segment col nam='||FND_FLEX_DESCVAL.segment_column_name(i));
568: qp_bulk_loader_pub.write_log( 'segment ID='|| FND_FLEX_DESCVAL.segment_id(i));
569:
570: IF FND_FLEX_DESCVAL.segment_column_name(i) = g_context_name THEN
571: g_context := FND_FLEX_DESCVAL.segment_id(i);
572: ELSIF FND_FLEX_DESCVAL.segment_column_name(i) = g_attribute1_name THEN
615: IF FND_FLEX_DESCVAL.value_error OR
616: FND_FLEX_DESCVAL.unsupported_error
617: THEN
618: -- In case of an expected error return FALSE
619: qp_bulk_loader_pub.write_log( p_flex_name||'Desc Flex value/unsupport error');
620: RETURN FALSE;
621: ELSE
622: -- In case of an unexpected error raise an exception.
623: qp_bulk_loader_pub.write_log( p_flex_name||'Desc Flex unexpected error');
619: qp_bulk_loader_pub.write_log( p_flex_name||'Desc Flex value/unsupport error');
620: RETURN FALSE;
621: ELSE
622: -- In case of an unexpected error raise an exception.
623: qp_bulk_loader_pub.write_log( p_flex_name||'Desc Flex unexpected error');
624: RETURN FALSE;
625: END IF;
626: END IF;
627:
649: RETURN BOOLEAN
650: IS
651: l_column_value VARCHAR2(240) := null;
652: BEGIN
653: qp_bulk_loader_pub.write_log( 'In Init Desc Flex '||p_flex_name);
654:
655: g_context_name := 'CONTEXT';
656: g_attribute1_name := 'ATTRIBUTE1';
657: g_attribute2_name := 'ATTRIBUTE2';
675:
676: ELSE
677:
678: l_column_value := p_attribute1;
679: qp_bulk_loader_pub.write_log( 'Attribute1='||l_column_value);
680:
681: END IF;
682:
683: FND_FLEX_DESCVAL.Set_Column_Value
690:
691: ELSE
692:
693: l_column_value := p_attribute2;
694: qp_bulk_loader_pub.write_log( 'Attribute2='||l_column_value);
695:
696: END IF;
697:
698: FND_FLEX_DESCVAL.Set_Column_Value
705:
706: ELSE
707:
708: l_column_value := p_attribute3;
709: qp_bulk_loader_pub.write_log( 'Attribute3='||l_column_value);
710:
711: END IF;
712:
713: FND_FLEX_DESCVAL.Set_Column_Value
720:
721: ELSE
722:
723: l_column_value := p_attribute4;
724: qp_bulk_loader_pub.write_log( 'Attribute4='||l_column_value);
725:
726: END IF;
727:
728: FND_FLEX_DESCVAL.Set_Column_Value
735:
736: ELSE
737:
738: l_column_value := p_attribute5;
739: qp_bulk_loader_pub.write_log( 'Attribute5='||l_column_value);
740:
741: END IF;
742:
743: FND_FLEX_DESCVAL.Set_Column_Value
750:
751: ELSE
752:
753: l_column_value := p_attribute6;
754: qp_bulk_loader_pub.write_log( 'Attribute6='||l_column_value);
755:
756: END IF;
757:
758: FND_FLEX_DESCVAL.Set_Column_Value
765:
766: ELSE
767:
768: l_column_value := p_attribute7;
769: qp_bulk_loader_pub.write_log( 'Attribute7='||l_column_value);
770:
771: END IF;
772:
773: FND_FLEX_DESCVAL.Set_Column_Value
780:
781: ELSE
782:
783: l_column_value := p_attribute8;
784: qp_bulk_loader_pub.write_log( 'Attribute8='||l_column_value);
785:
786: END IF;
787:
788: FND_FLEX_DESCVAL.Set_Column_Value
795:
796: ELSE
797:
798: l_column_value := p_attribute9;
799: qp_bulk_loader_pub.write_log( 'Attribute9='||l_column_value);
800:
801: END IF;
802:
803: FND_FLEX_DESCVAL.Set_Column_Value
810:
811: ELSE
812:
813: l_column_value := p_attribute10;
814: qp_bulk_loader_pub.write_log( 'Attribute10='||l_column_value);
815:
816: END IF;
817:
818: FND_FLEX_DESCVAL.Set_Column_Value
825:
826: ELSE
827:
828: l_column_value := p_attribute11;
829: qp_bulk_loader_pub.write_log( 'Attribute11='||l_column_value);
830:
831: END IF;
832:
833: FND_FLEX_DESCVAL.Set_Column_Value
840:
841: ELSE
842:
843: l_column_value := p_attribute12;
844: qp_bulk_loader_pub.write_log( 'Attribute12='||l_column_value);
845:
846: END IF;
847:
848: FND_FLEX_DESCVAL.Set_Column_Value
855:
856: ELSE
857:
858: l_column_value := p_attribute13;
859: qp_bulk_loader_pub.write_log( 'Attribute13='||l_column_value);
860:
861: END IF;
862:
863: FND_FLEX_DESCVAL.Set_Column_Value
870:
871: ELSE
872:
873: l_column_value := p_attribute14;
874: qp_bulk_loader_pub.write_log( 'Attribute14='||l_column_value);
875:
876: END IF;
877:
878: FND_FLEX_DESCVAL.Set_Column_Value
885:
886: ELSE
887:
888: l_column_value := p_attribute15;
889: qp_bulk_loader_pub.write_log( 'Attribute15='||l_column_value);
890:
891: END IF;
892:
893: FND_FLEX_DESCVAL.Set_Column_Value
900:
901: ELSE
902:
903: l_column_value := p_context;
904: qp_bulk_loader_pub.write_log( 'Context='||l_column_value);
905:
906: END IF;
907: FND_FLEX_DESCVAL.Set_Context_Value
908: ( context_value => l_column_value);
913: RETURN TRUE;
914: END;
915:
916: PROCEDURE ENTITY_HEADER
917: (p_header_rec IN OUT NOCOPY QP_BULK_LOADER_PUB.HEADER_REC_TYPE)
918:
919: IS
920: l_msg_rec QP_BULK_MSG.Msg_Rec_Type;
921: l_old_PRICE_LIST_rec QP_Price_List_PUB.Price_List_Rec_Type;
948: l_date_length NUMBER;
949: -- Bug 5414062 END RAVI
950:
951: BEGIN
952: qp_bulk_loader_pub.write_log('Entering Entity_header validation');
953:
954: select fnd_date.date_to_canonical(TRUNC(SYSDATE))
955: into l_start_date_active
956: from DUAL;
958: l_qp_pte := FND_PROFILE.VALUE('QP_PRICING_TRANSACTION_ENTITY');
959: l_qp_source_system_code := FND_PROFILE.VALUE('QP_SOURCE_SYSTEM_CODE');
960:
961: l_header_flex_enabled := get_flex_enabled_flag('QP_LIST_HEADERS');
962: qp_bulk_loader_pub.write_log('Flex enabled '||l_header_flex_enabled);
963:
964: FOR I IN 1..p_header_rec.orig_sys_header_ref.COUNT
965: LOOP
966:
1049:
1050: FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
1051: FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CURRENCY_CODE');
1052: QP_BULK_MSG.ADD(l_msg_rec);
1053: qp_bulk_loader_pub.write_log('Currency Validation failed');
1054:
1055: END IF;
1056:
1057: --Name
1447: l_header_id_null := 'Y';
1448: ELSE
1449: l_header_id_null := 'N';
1450: END IF;
1451: qp_bulk_loader_pub.write_log('Is Header Id null: ' || l_header_id_null);
1452:
1453:
1454: IF p_header_rec.interface_action_code(I) = 'UPDATE'
1455: AND p_header_rec.list_header_id(I) IS NULL
1467: orig_sys_header_ref = p_header_rec.orig_sys_header_ref(I))
1468: and nvl(list_source_code,'*') =nvl(p_header_rec.list_source_code(I),'*');
1469:
1470: IF l_exist >1 THEN
1471: qp_bulk_loader_pub.write_log('Entity Header l_exist of orig is: '||l_exist);
1472: p_header_rec.process_status_flag(I):=NULL;
1473: fnd_message.set_name('QP','ORIG_SYS_HEADER_REF_NOT_UNIQUE');
1474: FND_MESSAGE.SET_TOKEN('REF_NO', p_header_rec.orig_sys_header_ref(I));
1475: FND_MESSAGE.SET_TOKEN('LS_CODE', p_header_rec.list_source_code(I));
1475: FND_MESSAGE.SET_TOKEN('LS_CODE', p_header_rec.list_source_code(I));
1476: qp_bulk_msg.add(l_msg_rec);
1477: END IF;
1478: ELSIF p_header_rec.interface_action_code(I) = 'UPDATE' AND l_header_id_null = 'N' THEN
1479: qp_bulk_loader_pub.write_log('checking for unique orig_sys_header_ref: ' || p_header_rec.orig_sys_header_ref(I));
1480: select count(distinct list_header_id) into l_exist
1481: from qp_interface_list_headers
1482: where request_id =p_header_rec.request_id(I)
1483: and orig_sys_header_ref = p_header_rec.orig_sys_header_ref(I)
1503: -- and nvl(list_source_code,'*') =nvl(p_header_rec.list_source_code(I),'*'); */
1504:
1505:
1506: IF l_exist >1 THEN
1507: qp_bulk_loader_pub.write_log('Entity Header l_exist of orig is: '||l_exist);
1508: p_header_rec.process_status_flag(I):=NULL;
1509: fnd_message.set_name('QP','ORIG_SYS_HEADER_REF_NOT_UNIQUE');
1510: FND_MESSAGE.SET_TOKEN('REF_NO', p_header_rec.orig_sys_header_ref(I));
1511: FND_MESSAGE.SET_TOKEN('LS_CODE', p_header_rec.list_source_code(I));
1515: --bug 6961376 end
1516:
1517: --QP_Security_check
1518: IF p_header_rec.interface_action_code(I) = 'UPDATE' THEN
1519: qp_bulk_loader_pub.write_log('Secu. Check list_header_id: '||to_char(p_header_rec.list_header_id(I)));
1520: IF QP_security.check_function( p_function_name => QP_Security.G_FUNCTION_UPDATE,
1521: p_instance_type => QP_Security.G_PRICELIST_OBJECT,
1522: p_instance_pk1 => p_header_rec.list_header_id(I)) <> 'T' THEN
1523: p_header_rec.process_status_flag(I):=NULL;
1528: END IF;
1529:
1530: --Defaulting
1531: -- Basic_Pricing_Condition
1532: IF (QP_BULK_LOADER_PUB.GET_QP_STATUS <> 'I' OR
1533: FND_PROFILE.VALUE('QP_MULTI_CURRENCY_INSTALLED') = 'N')
1534: AND p_header_rec.currency_header_id(I) IS NOT NULL THEN
1535: -- No multi currency in Basic Pricing
1536: p_header_rec.currency_header_id(I) := NULL;
1538:
1539: --Flex field check
1540:
1541: IF l_header_flex_enabled = 'Y' THEN
1542: qp_bulk_loader_pub.write_log( 'Header Flex enabled ');
1543:
1544: IF NOT Init_Desc_Flex
1545: (p_flex_name =>'QP_LIST_HEADERS'
1546: ,p_context => p_header_rec.context(i)
1669: --cannot update Source system code, pte code
1670: IF p_header_rec.interface_action_code(I) = 'UPDATE'
1671: AND p_header_rec.process_status_flag(I) = 'P' THEN
1672:
1673: qp_bulk_loader_pub.write_log( 'Update header no: '||to_char(l_list_header_id));
1674: l_old_PRICE_LIST_rec := QP_Price_List_Util.Query_Row
1675: ( p_list_header_id => l_list_header_id
1676: );
1677:
1674: l_old_PRICE_LIST_rec := QP_Price_List_Util.Query_Row
1675: ( p_list_header_id => l_list_header_id
1676: );
1677:
1678: qp_bulk_loader_pub.write_log( 'Update source_system_code : '||l_old_price_list_rec.source_system_code);
1679: qp_bulk_loader_pub.write_log( 'Update pte_code : '||l_old_price_list_rec.pte_code);
1680: IF l_old_price_list_rec.source_system_code <> p_header_rec.source_system_code(I) or
1681: l_old_price_list_rec.pte_code <> p_header_rec.pte_code(I) THEN
1682: p_header_rec.process_status_flag(I):=NULL;
1675: ( p_list_header_id => l_list_header_id
1676: );
1677:
1678: qp_bulk_loader_pub.write_log( 'Update source_system_code : '||l_old_price_list_rec.source_system_code);
1679: qp_bulk_loader_pub.write_log( 'Update pte_code : '||l_old_price_list_rec.pte_code);
1680: IF l_old_price_list_rec.source_system_code <> p_header_rec.source_system_code(I) or
1681: l_old_price_list_rec.pte_code <> p_header_rec.pte_code(I) THEN
1682: p_header_rec.process_status_flag(I):=NULL;
1683: fnd_message.set_name('QP', 'QP_NO_UPDATE_ATTRIBUTE');
1687: END IF;
1688:
1689: END LOOP;
1690:
1691: qp_bulk_loader_pub.write_log('Leaving Entity_header validation');
1692:
1693: EXCEPTION
1694: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1695: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_HEADER:'||sqlerrm);
1691: qp_bulk_loader_pub.write_log('Leaving Entity_header validation');
1692:
1693: EXCEPTION
1694: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1695: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_HEADER:'||sqlerrm);
1696: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697: WHEN OTHERS THEN
1698: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_HEADER:'||sqlerrm);
1699: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1694: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1695: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_HEADER:'||sqlerrm);
1696: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697: WHEN OTHERS THEN
1698: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_HEADER:'||sqlerrm);
1699: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1700: END ENTITY_HEADER;
1701:
1702:
1700: END ENTITY_HEADER;
1701:
1702:
1703: PROCEDURE ENTITY_LINE
1704: (P_LINE_REC IN OUT NOCOPY QP_BULK_LOADER_PUB.LINE_REC_TYPE)
1705:
1706: IS
1707: l_msg_rec QP_BULK_MSG.Msg_Rec_Type;
1708: l_dummy NUMBER;
1727: l_PBH_end_date DATE;
1728:
1729: BEGIN
1730:
1731: qp_bulk_loader_pub.write_log('Entering Entity Line');
1732:
1733: l_line_flex_enabled := get_flex_enabled_flag('QP_LIST_LINES');
1734: qp_bulk_loader_pub.write_log('Flex enabled '||l_line_flex_enabled);
1735:
1730:
1731: qp_bulk_loader_pub.write_log('Entering Entity Line');
1732:
1733: l_line_flex_enabled := get_flex_enabled_flag('QP_LIST_LINES');
1734: qp_bulk_loader_pub.write_log('Flex enabled '||l_line_flex_enabled);
1735:
1736: FOR I IN 1..p_line_rec.orig_sys_line_ref.count
1737: LOOP
1738:
1736: FOR I IN 1..p_line_rec.orig_sys_line_ref.count
1737: LOOP
1738:
1739: --Initially setting the message context.
1740: --qp_bulk_loader_pub.write_log(' In the loop');
1741: l_msg_rec.REQUEST_ID := P_LINE_REC.REQUEST_ID(i);
1742: l_msg_rec.ENTITY_TYPE :='PRL';
1743: l_msg_rec.TABLE_NAME :='QP_INTERFACE_LIST_LINES';
1744: l_msg_rec.ORIG_SYS_HEADER_REF := p_line_rec.orig_sys_header_ref(I);
1784: END IF;
1785: ELSE
1786: p_line_rec.list_header_id(I) := l_list_header_id;
1787: END IF;
1788: qp_bulk_loader_pub.write_log('Line Start date active'||to_char(l_start_date));
1789:
1790: /*------- Performing required field validation for ---------*/
1791:
1792: --1.orig_sys_line_ref,
1858:
1859: -- Bug# 5246745 RAVI START
1860: --Check if item is servicable
1861: IF p_line_rec.arithmetic_operator(I) = 'PERCENT_PRICE' THEN
1862: qp_bulk_loader_pub.write_log('Percent Price - arithmetic operator check');
1863: l_exist :=0;
1864:
1865: begin
1866: select count(*) into l_exist
1913: end if;
1914: END IF;
1915: END IF; --End arithemetic operator check
1916:
1917: qp_bulk_loader_pub.write_log('After required field check');
1918:
1919: -- Uniqueness of orig_sys_line_ref
1920: l_exist := 0;
1921:
1918:
1919: -- Uniqueness of orig_sys_line_ref
1920: l_exist := 0;
1921:
1922: qp_bulk_loader_pub.write_log('Header ref'|| p_line_rec.orig_sys_header_ref(I));
1923: qp_bulk_loader_pub.write_log('Line ref'|| p_line_rec.orig_sys_line_ref(I));
1924:
1925: BEGIN
1926: select count(*) into l_exist
1919: -- Uniqueness of orig_sys_line_ref
1920: l_exist := 0;
1921:
1922: qp_bulk_loader_pub.write_log('Header ref'|| p_line_rec.orig_sys_header_ref(I));
1923: qp_bulk_loader_pub.write_log('Line ref'|| p_line_rec.orig_sys_line_ref(I));
1924:
1925: BEGIN
1926: select count(*) into l_exist
1927: from qp_list_lines
2015: qp_bulk_msg.add(l_msg_rec);
2016: END IF;
2017: END IF;
2018:
2019: qp_bulk_loader_pub.write_log('check price break ARITHMETIC OPERATOR');
2020: -- check UNIT price break has no BLOCK price break child lines
2021:
2022: l_dummy:= null;
2023: IF p_line_rec.list_line_type_code(I) = 'PBH' then
2160: P_LINE_REC.PROCESS_STATUS_FLAG(I):= NULL; --'E';
2161: FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
2162: FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Recurring Value');
2163: QP_BULK_MSG.ADD(l_msg_rec);
2164: qp_bulk_loader_pub.write_log('PRICE BREAK TYPE CODE NULL');
2165: ELSE
2166: IF p_line_rec.price_break_type_code(I) = l_pb_type_code
2167: THEN
2168: NULL;
2170: P_LINE_REC.PROCESS_STATUS_FLAG(I):= NULL; --'E';
2171: FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
2172: FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Recurring Value');
2173: QP_BULK_MSG.ADD(l_msg_rec);
2174: qp_bulk_loader_pub.write_log('PRICE BREAK TYPE CODE OF PARENT AND CHILD UNEQUAL');
2175: END IF;
2176: END IF;
2177: --Bug 4405737 END RAVI
2178: IF l_servicable_item='N' THEN
2282: null;
2283: END;
2284: END IF;
2285:
2286: qp_bulk_loader_pub.write_log('After pbh check');
2287:
2288: -- check for operand value for the line.
2289: IF p_LINE_rec.list_line_type_code(I) = 'PLL'
2290: and p_LINE_rec.operand(I) IS NULL
2361: and interface_action_code in ('INSERT','UPDATE');
2362: EXCEPTION
2363: WHEN OTHERS
2364: THEN
2365: qp_bulk_loader_pub.write_log( 'ERROR in comparing the Header and Line start Dates (INTERFACE)');
2366: END;
2367:
2368: IF l_header_start_date IS NULL THEN
2369: BEGIN
2373: where orig_system_header_ref=p_line_rec.orig_sys_header_ref(I);
2374: EXCEPTION
2375: WHEN OTHERS
2376: THEN
2377: qp_bulk_loader_pub.write_log( 'ERROR in comparing the Header and Line start Dates (QP)');
2378: END;
2379: END IF;
2380:
2381: IF p_line_rec.start_date_active(I) < l_header_start_date OR
2412: and interface_action_code in ('INSERT','UPDATE');
2413: EXCEPTION
2414: WHEN OTHERS
2415: THEN
2416: qp_bulk_loader_pub.write_log( 'ERROR in comparing the PBH and PBC start Dates (INTERFACE)');
2417: END;
2418:
2419: IF l_PBH_start_date is null THEN
2420: BEGIN
2426: and orig_sys_header_ref=p_LINE_rec.orig_sys_header_ref(I);
2427: EXCEPTION
2428: WHEN OTHERS
2429: THEN
2430: qp_bulk_loader_pub.write_log( 'ERROR in comparing the PBH and PBC start Dates (INTERFACE)');
2431: END;
2432: END IF;
2433:
2434: IF l_PBH_start_date <> p_line_rec.start_date_active(I) OR
2443:
2444: --Flex field check
2445:
2446: IF l_line_flex_enabled = 'Y' THEN
2447: qp_bulk_loader_pub.write_log( 'Line Flex enabled ');
2448:
2449: IF NOT Init_Desc_Flex
2450: (p_flex_name =>'QP_LIST_LINES'
2451: ,p_context => p_line_rec.context(i)
2558: END IF;
2559: END IF;
2560:
2561: -- Basic_Pricing_Condition
2562: IF QP_BULK_LOADER_PUB.GET_QP_STATUS <> 'I' THEN
2563: IF p_line_rec.price_break_type_code(I) IS NOT NULL THEN
2564: p_line_rec.price_break_type_code(I) := NULL;
2565: END IF;
2566: IF p_line_rec.primary_uom_flag(I) = 'Y' THEN
2567: p_line_rec.primary_uom_flag(I) := 'N';
2568: END IF;
2569: END IF;
2570: --start_date_active
2571: qp_bulk_loader_pub.write_log('Line Start date value'||nvl(to_char(p_line_rec.start_date_active(I)), 'NULL'));
2572: qp_bulk_loader_pub.write_log('Line end date value'||nvl(to_char(p_line_rec.end_date_active(I)), 'NULL'));
2573:
2574: --Bug#5353889 RAVI START
2575: --Start date null should be allowed
2568: END IF;
2569: END IF;
2570: --start_date_active
2571: qp_bulk_loader_pub.write_log('Line Start date value'||nvl(to_char(p_line_rec.start_date_active(I)), 'NULL'));
2572: qp_bulk_loader_pub.write_log('Line end date value'||nvl(to_char(p_line_rec.end_date_active(I)), 'NULL'));
2573:
2574: --Bug#5353889 RAVI START
2575: --Start date null should be allowed
2576: /**
2577: IF p_line_rec.start_date_active(I) IS NULL
2578: AND (p_line_rec.end_date_active(I) IS NULL or
2579: p_line_rec.end_date_active(I) <= l_start_date)
2580: THEN
2581: qp_bulk_loader_pub.write_log('Line Start date updated');
2582: p_line_rec.start_date_active(I) := l_start_date;
2583: END IF;
2584: **/
2585: --Bug#5353889 RAVI END
2585: --Bug#5353889 RAVI END
2586:
2587: END LOOP;
2588:
2589: qp_bulk_loader_pub.write_log('Leaving entity line validation');
2590:
2591: EXCEPTION
2592: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2593: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_LINE:'||sqlerrm);
2589: qp_bulk_loader_pub.write_log('Leaving entity line validation');
2590:
2591: EXCEPTION
2592: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2593: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_LINE:'||sqlerrm);
2594: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2595: WHEN OTHERS THEN
2596: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_LINE:'||sqlerrm);
2597: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2592: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2593: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_LINE:'||sqlerrm);
2594: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2595: WHEN OTHERS THEN
2596: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_LINE:'||sqlerrm);
2597: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2598: END ENTITY_LINE;
2599:
2600:
2598: END ENTITY_LINE;
2599:
2600:
2601: PROCEDURE ENTITY_QUALIFIER
2602: (p_qualifier_rec IN OUT NOCOPY QP_BULK_LOADER_PUB.qualifier_rec_type)
2603: IS
2604: l_msg_rec QP_BULK_MSG.Msg_Rec_Type;
2605: l_segment_name VARCHAR2(240);
2606: x_value VARCHAR2(240);
2629: **/
2630: l_list_header_id NUMBER;
2631:
2632: BEGIN
2633: qp_bulk_loader_pub.write_log('Entering Entity Qualifier');
2634:
2635: FOR I IN p_qualifier_rec.orig_sys_qualifier_ref.first
2636: ..p_qualifier_rec.orig_sys_qualifier_ref.last
2637: LOOP
2635: FOR I IN p_qualifier_rec.orig_sys_qualifier_ref.first
2636: ..p_qualifier_rec.orig_sys_qualifier_ref.last
2637: LOOP
2638:
2639: --qp_bulk_loader_pub.write_log('request_id: '||p_qualifier_rec.request_id(I));
2640: --Initially setting the message context.
2641:
2642: l_msg_rec.REQUEST_ID := P_QUALIFIER_REC.REQUEST_ID(I);
2643: l_msg_rec.ENTITY_TYPE :='PRL';
2762: -- Qualifier Start and End Date validation
2763: IF p_qualifier_rec.end_date_active(I) is not null THEN
2764: IF p_qualifier_rec.start_date_active(I) is not null THEN
2765: IF p_qualifier_rec.start_date_active(I) > p_qualifier_rec.end_date_active(I) THEN
2766: qp_bulk_loader_pub.write_log('End Date is before Start Date');
2767: p_qualifier_rec.process_status_flag(I):=NULL;
2768: FND_MESSAGE.SET_NAME('QP', 'QP_STRT_DATE_BFR_END_DATE');
2769: QP_BULK_MSG.Add(l_msg_rec);
2770: END IF;
2768: FND_MESSAGE.SET_NAME('QP', 'QP_STRT_DATE_BFR_END_DATE');
2769: QP_BULK_MSG.Add(l_msg_rec);
2770: END IF;
2771: ELSE
2772: qp_bulk_loader_pub.write_log('Qualifier Start Date is null');
2773: p_qualifier_rec.process_status_flag(I):=NULL;
2774: FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_DATE');
2775: QP_BULK_MSG.Add(l_msg_rec);
2776: END IF;
2828: FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2829: QP_BULK_MSG.ADD(l_msg_rec);
2830: ELSE
2831: IF l_error_code=1 THEN
2832: qp_bulk_loader_pub.write_log('Qualifier Flexfield_name is not passed.');
2833: p_qualifier_rec.process_status_flag(I):=NULL;
2834: FND_MESSAGE.SET_NAME('QP','QP_QLF_FLXFLD_NULL');
2835: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2836: QP_BULK_MSG.ADD(l_msg_rec);
2834: FND_MESSAGE.SET_NAME('QP','QP_QLF_FLXFLD_NULL');
2835: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2836: QP_BULK_MSG.ADD(l_msg_rec);
2837: ELSIF l_error_code=2 THEN
2838: qp_bulk_loader_pub.write_log('Qualifier Context is not passed');
2839: p_qualifier_rec.process_status_flag(I):=NULL;
2840: FND_MESSAGE.SET_NAME('QP','QP_QLF_CNTXT_NULL');
2841: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2842: QP_BULK_MSG.ADD(l_msg_rec);
2840: FND_MESSAGE.SET_NAME('QP','QP_QLF_CNTXT_NULL');
2841: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2842: QP_BULK_MSG.ADD(l_msg_rec);
2843: ELSIF l_error_code=3 THEN
2844: qp_bulk_loader_pub.write_log('Qualifier Attribute is not passed.');
2845: p_qualifier_rec.process_status_flag(I):=NULL;
2846: FND_MESSAGE.SET_NAME('QP','QP_QLF_ATTR_NULL');
2847: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2848: QP_BULK_MSG.ADD(l_msg_rec);
2846: FND_MESSAGE.SET_NAME('QP','QP_QLF_ATTR_NULL');
2847: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2848: QP_BULK_MSG.ADD(l_msg_rec);
2849: ELSIF l_error_code=4 THEN
2850: qp_bulk_loader_pub.write_log('Qualifier Value is not passed');
2851: p_qualifier_rec.process_status_flag(I):=NULL;
2852: FND_MESSAGE.SET_NAME('QP','QP_QLF_ATTR_VAL_NULL');
2853: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2854: QP_BULK_MSG.ADD(l_msg_rec);
2852: FND_MESSAGE.SET_NAME('QP','QP_QLF_ATTR_VAL_NULL');
2853: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2854: QP_BULK_MSG.ADD(l_msg_rec);
2855: ELSIF l_error_code=5 THEN
2856: qp_bulk_loader_pub.write_log('Qualifier Application short name is not passed');
2857: p_qualifier_rec.process_status_flag(I):=NULL;
2858: FND_MESSAGE.SET_NAME('QP','QP_QLF_APP_NAME_NULL');
2859: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2860: QP_BULK_MSG.ADD(l_msg_rec);
2858: FND_MESSAGE.SET_NAME('QP','QP_QLF_APP_NAME_NULL');
2859: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2860: QP_BULK_MSG.ADD(l_msg_rec);
2861: ELSIF l_error_code=6 THEN
2862: qp_bulk_loader_pub.write_log('Qualifier Invalid application short name.');
2863: p_qualifier_rec.process_status_flag(I):=NULL;
2864: FND_MESSAGE.SET_NAME('QP','QP_QLF_INVALID_APP_NAME');
2865: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2866: QP_BULK_MSG.ADD(l_msg_rec);
2864: FND_MESSAGE.SET_NAME('QP','QP_QLF_INVALID_APP_NAME');
2865: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2866: QP_BULK_MSG.ADD(l_msg_rec);
2867: ELSIF l_error_code=7 THEN
2868: qp_bulk_loader_pub.write_log('Qualifier Invalid context passed');
2869: p_qualifier_rec.process_status_flag(I):=NULL;
2870: FND_MESSAGE.SET_NAME('QP','QP_QLF_INVALID_CNTXT');
2871: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2872: QP_BULK_MSG.ADD(l_msg_rec);
2870: FND_MESSAGE.SET_NAME('QP','QP_QLF_INVALID_CNTXT');
2871: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2872: QP_BULK_MSG.ADD(l_msg_rec);
2873: ELSIF l_error_code=8 THEN
2874: qp_bulk_loader_pub.write_log('Qualifier Invalid Attribute passed');
2875: p_qualifier_rec.process_status_flag(I):=NULL;
2876: FND_MESSAGE.SET_NAME('QP','QP_QLF_INVALID_ATTRIB');
2877: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2878: QP_BULK_MSG.ADD(l_msg_rec);
2876: FND_MESSAGE.SET_NAME('QP','QP_QLF_INVALID_ATTRIB');
2877: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2878: QP_BULK_MSG.ADD(l_msg_rec);
2879: ELSIF l_error_code=9 THEN
2880: qp_bulk_loader_pub.write_log('Qualifier Value does not exist');
2881: p_qualifier_rec.process_status_flag(I):=NULL;
2882: FND_MESSAGE.SET_NAME('QP','QP_QLF_INVALID_ATTR_VAL');
2883: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2884: QP_BULK_MSG.ADD(l_msg_rec);
2882: FND_MESSAGE.SET_NAME('QP','QP_QLF_INVALID_ATTR_VAL');
2883: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2884: QP_BULK_MSG.ADD(l_msg_rec);
2885: ELSIF l_error_code=10 THEN
2886: qp_bulk_loader_pub.write_log('Qualifier Invalid Flexfield Name');
2887: p_qualifier_rec.process_status_flag(I):=NULL;
2888: FND_MESSAGE.SET_NAME('QP','QP_QLF_INVALID_FLXFLD_NAME');
2889: --FND_MESSAGE.SET_TOKEN('ATTRIBUTE','QUALIFIER ATTRIBUTE CODE');
2890: QP_BULK_MSG.ADD(l_msg_rec);
2893: END IF;
2894:
2895: --Bug 4929426 END RAVI
2896:
2897: qp_bulk_loader_pub.write_log( 'Qualifier_attr_val:'||p_qualifier_rec.qualifier_attr_value(I));
2898: -- Qualifier Attr Value To
2899: IF p_qualifier_rec.comparison_operator_code(I)= 'BETWEEN' AND
2900: p_qualifier_rec.qualifier_attr_value_to(I) IS NULL THEN
2901:
3011: qp_bulk_msg.add(l_msg_rec);
3012: End;
3013:
3014: --SECONDARY PRICELIST CHECK
3015: qp_bulk_loader_pub.write_log( 'SECONDARY PRICELIST CHECK');
3016: IF p_qualifier_rec.qualifier_context(I) = 'MODLIST'
3017: AND p_qualifier_rec.qualifier_attribute(I) = 'QUALIFIER_ATTRIBUTE4' THEN
3018:
3019: -- Bug 4926775 START RAVI
3028: AND qpilh.ORIG_SYS_HEADER_REF = p_qualifier_rec.ORIG_SYS_HEADER_REF(I)
3029: AND qplh.currency_code = qpilh.currency_code;
3030: EXCEPTION
3031: WHEN NO_DATA_FOUND THEN
3032: qp_bulk_loader_pub.write_log( 'Secondary Price List Currency not compatible with primary price list currency code');
3033: P_QUALIFIER_REC.PROCESS_STATUS_FLAG(I):= NULL; --'E';
3034: FND_MESSAGE.SET_NAME('QP', 'QP_SEC_PRL_CCD_INVALID');
3035: --FND_MESSAGE.SET_TOKEN('PRICELIST_NAME', p_qualifier_rec.qualifier_attr_value(I));
3036: QP_BULK_MSG.ADD(l_msg_rec);
3037: END;
3038: -- Bug 4926775 END RAVI
3039:
3040: IF p_qualifier_rec.qualifier_attr_value(I) IS NULL THEN
3041: qp_bulk_loader_pub.write_log( 'Secondary Price List read from qp_list_header');
3042: BEGIN
3043: SELECT to_char(list_header_id)
3044: INTO p_qualifier_rec.qualifier_attr_value(I)
3045: FROM QP_LIST_HEADERS_TL
3053: QP_BULK_MSG.ADD(l_msg_rec);
3054: END;
3055: END IF;
3056: IF p_qualifier_rec.qualifier_attr_value(I) IS NOT NULL THEN
3057: qp_bulk_loader_pub.write_log( 'Secondary Price List duplicate check');
3058: l_exist:=0;
3059: begin
3060: select 1 into l_exist
3061: from qp_qualifiers
3070: when others then
3071: null;
3072: end;
3073: if l_exist = 0 then
3074: qp_bulk_loader_pub.write_log( 'Secondary Price List dupl. checkin int. table');
3075: begin
3076: select 1 into l_exist
3077: from qp_interface_qualifiers
3078: where request_id = p_qualifier_rec.request_id(I)
3091: FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
3092: FND_MESSAGE.SET_TOKEN('ATTRIBUTE',' Price List');
3093: QP_BULK_MSG.ADD(l_msg_rec);
3094: end if;
3095: qp_bulk_loader_pub.write_log( 'Secondary Price List security check');
3096: IF QP_security.check_function(p_function_name => QP_Security.G_FUNCTION_UPDATE,
3097: p_instance_type => QP_Security.G_PRICELIST_OBJECT,
3098: p_instance_pk1 => to_number(p_qualifier_rec.qualifier_attr_value(I))) <> 'T' THEN
3099: p_qualifier_rec.process_status_flag(I):=NULL;
3105: END IF;
3106:
3107: -- Basic_Pricing_Condition
3108: -- Secondary PricelIst check while basic pricing
3109: IF QP_BULK_LOADER_PUB.GET_QP_STATUS<>'I' THEN
3110: IF p_qualifier_rec.qualifier_context(I) = 'MODLIST'
3111: AND p_qualifier_rec.qualifier_attribute(I) = 'QUALIFIER_ATTRIBUTE4' THEN
3112: l_exist:=0;
3113:
3115: INTO l_exist
3116: FROM QP_SECONDARY_PRICE_LISTS_V
3117: WHERE parent_price_list_id = p_qualifier_rec.qualifier_attr_value(I);
3118:
3119: qp_bulk_loader_pub.write_log( 'l_exist='||l_exist);
3120:
3121: IF l_exist > 0 THEN
3122: P_QUALIFIER_REC.PROCESS_STATUS_FLAG(I):= NULL; --'E';
3123: FND_MESSAGE.SET_NAME('QP','QP_1_SEC_PLST_FOR_BASIC');
3188: END IF;
3189:
3190: --warning messages
3191: IF P_QUALIFIER_REC.PROCESS_STATUS_FLAG(I)='P' THEN --,'*')<> 'E' THEN
3192: qp_bulk_loader_pub.write_log('Entering Qualifier warnings');
3193:
3194: IF p_Qualifier_rec.qualifier_context(I) IS NOT NULL AND
3195: p_Qualifier_rec.qualifier_attribute(I) IS NOT NULL
3196: THEN
3193:
3194: IF p_Qualifier_rec.qualifier_context(I) IS NOT NULL AND
3195: p_Qualifier_rec.qualifier_attribute(I) IS NOT NULL
3196: THEN
3197: qp_bulk_loader_pub.write_log('Context and Attribute OK ');
3198: QP_UTIL.Get_Context_Type('QP_ATTR_DEFNS_QUALIFIER',
3199: p_Qualifier_rec.qualifier_context(I),
3200: l_context_type,
3201: l_error_code);
3208: l_sourcing_enabled,
3209: l_sourcing_status,
3210: l_sourcing_method);
3211:
3212: qp_bulk_loader_pub.write_log('Sourcing method / status / enabled'||l_sourcing_method||l_sourcing_status||l_sourcing_enabled);
3213:
3214: IF l_sourcing_method = 'ATTRIBUTE MAPPING' THEN
3215:
3216: IF l_sourcing_enabled <> 'Y' THEN
3248:
3249: END IF;
3250: END LOOP;
3251:
3252: qp_bulk_loader_pub.write_log('Leaving Entity qualifier validation');
3253:
3254: EXCEPTION
3255: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3256: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_QUALIFIER:'||sqlerrm);
3252: qp_bulk_loader_pub.write_log('Leaving Entity qualifier validation');
3253:
3254: EXCEPTION
3255: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3256: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_QUALIFIER:'||sqlerrm);
3257: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3258: WHEN OTHERS THEN
3259: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_QUALIFIER:'||sqlerrm);
3260: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3255: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3256: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_QUALIFIER:'||sqlerrm);
3257: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3258: WHEN OTHERS THEN
3259: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_QUALIFIER:'||sqlerrm);
3260: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3261:
3262:
3263: END ENTITY_QUALIFIER;
3262:
3263: END ENTITY_QUALIFIER;
3264:
3265: PROCEDURE ENTITY_PRICING_ATTR
3266: (p_pricing_attr_rec IN OUT NOCOPY QP_BULK_LOADER_PUB.pricing_attr_rec_type)
3267: IS
3268: l_msg_rec QP_BULK_MSG.Msg_Rec_Type;
3269: l_exist NUMBER;
3270: l_exist1 NUMBER;
3603: qp_number.canonical_to_number(pa.pricing_attr_value_to) >=
3604: qp_number.canonical_to_number(pb.pricing_attr_value_to));
3605:
3606: BEGIN
3607: qp_bulk_loader_pub.write_log('Entering Entity pricing attribute validation');
3608: FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY',l_pte_code);
3609:
3610: FOR I IN 1..p_pricing_attr_rec.orig_sys_pricing_attr_ref.COUNT
3611: LOOP
3619: l_msg_rec.ORIG_SYS_LINE_REF := p_pricing_attr_rec.orig_sys_line_ref(I);
3620: l_msg_rec.ORIG_SYS_QUALIFIER_REF := NULL;
3621: l_msg_rec.ORIG_SYS_PRICING_ATTR_REF := p_pricing_attr_rec.orig_sys_pricing_attr_ref(I);
3622:
3623: qp_bulk_loader_pub.write_log('Inside entity pricing attr');
3624: -- Internal field population
3625: --List Line ID
3626: BEGIN
3627: select list_line_id
3637: fnd_message.set_name('QP', 'QP_INVALID_LINE_REF');
3638: FND_MESSAGE.SET_TOKEN('REF_NO', p_pricing_attr_rec.orig_sys_line_ref(I));
3639: qp_bulk_msg.add(l_msg_rec);
3640: END;
3641: qp_bulk_loader_pub.write_log('After entity list_line_id pricing attribute');
3642:
3643:
3644: -- Bug# 5246745 RAVI START
3645: --2 If org is Purchasing or PO then the item must be Purchasable or Orderable.
3818: FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Product Attribute Datatype');
3819: qp_bulk_msg.add(l_msg_rec);
3820: END IF;
3821:
3822: qp_bulk_loader_pub.write_log('After entity pricing attribute');
3823:
3824: -- product uom code
3825:
3826: IF P_PRICING_ATTR_REC.PRODUCT_UOM_CODE(I) IS NULL THEN
3831: END IF;
3832:
3833:
3834: --- check for single pricing_attribute record with pricing context/attr/value null
3835: qp_bulk_loader_pub.write_log('Pricing attr ref' || p_pricing_attr_rec.orig_sys_pricing_attr_ref(I));
3836: IF p_pricing_attr_rec.pricing_attribute_context(I) IS NULL
3837: OR p_pricing_attr_rec.pricing_attribute(I) IS NULL THEN
3838: l_exist := 0;
3839: BEGIN
3849: WHEN NO_DATA_FOUND THEN
3850: l_exist := 0;
3851: END;
3852:
3853: qp_bulk_loader_pub.write_log('Count of records in Pricing Attr table with pricing contect/attr/value null' || to_char(l_exist));
3854: l_exist1:=null;
3855: BEGIN
3856: SELECT count(*) INTO l_exist1
3857: FROM qp_interface_pricing_attribs
3866: WHEN NO_DATA_FOUND THEN
3867: l_exist1 := 0;
3868: END;
3869:
3870: qp_bulk_loader_pub.write_log('Count of records in Int table with pricing contect/attr/value null' || to_char(l_exist1));
3871: IF l_exist > 0 THEN
3872: p_pricing_attr_rec.process_status_flag(I):=NULL;
3873: FND_MESSAGE.SET_NAME('QP','QP_BULK_PRC_ATTR_ERROR');
3874: QP_BULK_MSG.ADD(l_msg_rec);
3881:
3882: END IF;
3883:
3884: -- Basic_Pricing_Condition
3885: IF QP_BULK_LOADER_PUB.GET_QP_STATUS <> 'I' THEN
3886: IF p_pricing_attr_rec.pricing_attribute_context(I) IS NOT NULL
3887: AND p_pricing_attr_rec.pricing_attribute(I) IS NOT NULL THEN
3888: l_exist := 0;
3889: BEGIN
3921: END IF;
3922: END IF;
3923:
3924: -- Basic_Pricing_Condition
3925: IF QP_BULK_LOADER_PUB.GET_QP_STATUS <> 'I' THEN
3926: IF p_pricing_attr_rec.pricing_attribute_context(I) IS NOT NULL AND
3927: p_pricing_attr_rec.pricing_attribute(I) IS NOT NULL THEN
3928: BEGIN
3929: select s.availability_in_basic
4230: l_pa_price_break :='PRICE LIST LINE';
4231: END;
4232: END IF;
4233:
4234: -- qp_bulk_loader_pub.write_log('check 5');
4235:
4236: --check for duplicate primary uom flag set
4237: --check for duplicate primary uom flag set --smbalara 9458481/10152629 /10152629 start
4238: IF p_pricing_attr_rec.process_status_flag(I)='P' THEN -- IS NULL THEN
4271: FROM qp_list_lines
4272: WHERE list_line_id = p_PRICING_ATTR_rec.list_line_id(I);
4273: EXCEPTION
4274: WHEN OTHERS THEN
4275: qp_bulk_loader_pub.write_log('9458481/10152629 smbalara 1 ');
4276: l_return_status := FND_API.G_RET_STS_ERROR;
4277: END;
4278:
4279: qp_bulk_loader_pub.write_log('9458481/10152629 smbalara 2 :'||l_primary_uom_flag);
4275: qp_bulk_loader_pub.write_log('9458481/10152629 smbalara 1 ');
4276: l_return_status := FND_API.G_RET_STS_ERROR;
4277: END;
4278:
4279: qp_bulk_loader_pub.write_log('9458481/10152629 smbalara 2 :'||l_primary_uom_flag);
4280: --check for dates overlapping for lines with primary uom flag checked --smbalara 9458481/10152629 START
4281: select start_date_active, end_date_active , revision, list_header_id
4282: into l_start_date_active, l_end_date_active, l_revision, l_list_header_id
4283: from qp_list_lines
4283: from qp_list_lines
4284: where list_line_id = p_PRICING_ATTR_rec.list_line_id(I);
4285:
4286: IF l_primary_uom_flag = 'Y' AND p_PRICING_ATTR_rec.product_attribute(I) <> 'PRICING_ATTRIBUTE3' THEN
4287: qp_bulk_loader_pub.write_log('9458481/10152629 smbalara 3 ');
4288: l_count:=null;
4289: for c1 in
4290: (
4291: select b.list_line_id col2
4314: when no_data_found then
4315: null;
4316: end;
4317:
4318: qp_bulk_loader_pub.write_log('9458481/10152629 smbalara 4');
4319: IF ( nvl(l_Start_Date_Active, l_min_date) <= nvl(l_sdate, l_min_date))
4320: THEN
4321: l_min_date := nvl(l_Start_Date_Active, l_min_date);
4322: ELSE
4329: ELSE
4330: l_max_date := nvl(l_edate, l_max_date);
4331: END IF;
4332: /*
4333: qp_bulk_loader_pub.write_log('printing dates :');
4334: qp_bulk_loader_pub.write_log('9458481/10152629 l_sdate :'||l_sdate);
4335: qp_bulk_loader_pub.write_log('9458481/10152629 l_edate :'||l_edate);
4336: qp_bulk_loader_pub.write_log('9458481/10152629 l_Start_Date_Active :'||l_Start_Date_Active);
4337: qp_bulk_loader_pub.write_log('9458481/10152629 l_End_Date_Active :'||l_End_Date_Active);
4330: l_max_date := nvl(l_edate, l_max_date);
4331: END IF;
4332: /*
4333: qp_bulk_loader_pub.write_log('printing dates :');
4334: qp_bulk_loader_pub.write_log('9458481/10152629 l_sdate :'||l_sdate);
4335: qp_bulk_loader_pub.write_log('9458481/10152629 l_edate :'||l_edate);
4336: qp_bulk_loader_pub.write_log('9458481/10152629 l_Start_Date_Active :'||l_Start_Date_Active);
4337: qp_bulk_loader_pub.write_log('9458481/10152629 l_End_Date_Active :'||l_End_Date_Active);
4338: qp_bulk_loader_pub.write_log('9458481/10152629 l_min_date :'||l_min_date);
4331: END IF;
4332: /*
4333: qp_bulk_loader_pub.write_log('printing dates :');
4334: qp_bulk_loader_pub.write_log('9458481/10152629 l_sdate :'||l_sdate);
4335: qp_bulk_loader_pub.write_log('9458481/10152629 l_edate :'||l_edate);
4336: qp_bulk_loader_pub.write_log('9458481/10152629 l_Start_Date_Active :'||l_Start_Date_Active);
4337: qp_bulk_loader_pub.write_log('9458481/10152629 l_End_Date_Active :'||l_End_Date_Active);
4338: qp_bulk_loader_pub.write_log('9458481/10152629 l_min_date :'||l_min_date);
4339: qp_bulk_loader_pub.write_log('9458481/10152629 l_max_date :'||l_max_date);
4332: /*
4333: qp_bulk_loader_pub.write_log('printing dates :');
4334: qp_bulk_loader_pub.write_log('9458481/10152629 l_sdate :'||l_sdate);
4335: qp_bulk_loader_pub.write_log('9458481/10152629 l_edate :'||l_edate);
4336: qp_bulk_loader_pub.write_log('9458481/10152629 l_Start_Date_Active :'||l_Start_Date_Active);
4337: qp_bulk_loader_pub.write_log('9458481/10152629 l_End_Date_Active :'||l_End_Date_Active);
4338: qp_bulk_loader_pub.write_log('9458481/10152629 l_min_date :'||l_min_date);
4339: qp_bulk_loader_pub.write_log('9458481/10152629 l_max_date :'||l_max_date);
4340: */
4333: qp_bulk_loader_pub.write_log('printing dates :');
4334: qp_bulk_loader_pub.write_log('9458481/10152629 l_sdate :'||l_sdate);
4335: qp_bulk_loader_pub.write_log('9458481/10152629 l_edate :'||l_edate);
4336: qp_bulk_loader_pub.write_log('9458481/10152629 l_Start_Date_Active :'||l_Start_Date_Active);
4337: qp_bulk_loader_pub.write_log('9458481/10152629 l_End_Date_Active :'||l_End_Date_Active);
4338: qp_bulk_loader_pub.write_log('9458481/10152629 l_min_date :'||l_min_date);
4339: qp_bulk_loader_pub.write_log('9458481/10152629 l_max_date :'||l_max_date);
4340: */
4341: if ( trunc(nvl(l_sdate, l_min_date)) between
4334: qp_bulk_loader_pub.write_log('9458481/10152629 l_sdate :'||l_sdate);
4335: qp_bulk_loader_pub.write_log('9458481/10152629 l_edate :'||l_edate);
4336: qp_bulk_loader_pub.write_log('9458481/10152629 l_Start_Date_Active :'||l_Start_Date_Active);
4337: qp_bulk_loader_pub.write_log('9458481/10152629 l_End_Date_Active :'||l_End_Date_Active);
4338: qp_bulk_loader_pub.write_log('9458481/10152629 l_min_date :'||l_min_date);
4339: qp_bulk_loader_pub.write_log('9458481/10152629 l_max_date :'||l_max_date);
4340: */
4341: if ( trunc(nvl(l_sdate, l_min_date)) between
4342: trunc(nvl(l_Start_Date_Active, l_min_date))
4335: qp_bulk_loader_pub.write_log('9458481/10152629 l_edate :'||l_edate);
4336: qp_bulk_loader_pub.write_log('9458481/10152629 l_Start_Date_Active :'||l_Start_Date_Active);
4337: qp_bulk_loader_pub.write_log('9458481/10152629 l_End_Date_Active :'||l_End_Date_Active);
4338: qp_bulk_loader_pub.write_log('9458481/10152629 l_min_date :'||l_min_date);
4339: qp_bulk_loader_pub.write_log('9458481/10152629 l_max_date :'||l_max_date);
4340: */
4341: if ( trunc(nvl(l_sdate, l_min_date)) between
4342: trunc(nvl(l_Start_Date_Active, l_min_date))
4343: and trunc(nvl(l_End_Date_Active, l_max_date)) )
4354: nvl(l_End_Date_Active,l_max_date) )
4355:
4356: THEN
4357: l_count:=1;
4358: qp_bulk_loader_pub.write_log('Dates Overlapping for lines with Primary UOM flag checked' );
4359: exit;
4360: end if;
4361:
4362: end loop;
4476: END IF; -- IF process_status_flag
4477:
4478: l_msg_rec.orig_sys_pricing_attr_ref := p_pricing_attr_rec.orig_sys_pricing_attr_ref(I);
4479:
4480: -- qp_bulk_loader_pub.write_log('check 6');
4481: --Delayed request validation
4482: -- Checking multiple price break attributes
4483:
4484: BEGIN
4548: rb.from_rltd_modifier_id = ra.FROM_RLTD_MODIFIER_ID AND
4549: pb.list_line_id = rb.to_rltd_modifier_id AND
4550: rb.rltd_modifier_grp_type = 'PRICE BREAK');
4551:
4552: --qp_bulk_loader_pub.write_log('multi_attr:'||l_count);
4553:
4554: IF l_count>1 THEN
4555: P_PRICING_ATTR_REC.PROCESS_STATUS_FLAG(I):= NULL; --'E';
4556: FND_MESSAGE.SET_NAME('QP', 'QP_MULT_PRICE_BREAK_ATTRS');
4582: WHEN NO_DATA_FOUND THEN
4583: NULL;
4584: END;
4585:
4586: -- qp_bulk_loader_pub.write_log('check 7: Ref:'|| p_pricing_attr_rec.orig_sys_pricing_attr_ref(I));
4587:
4588: -- Check Overlapping Price break child lines
4589: BEGIN
4590:
4675: CLOSE C_from_value_new;
4676:
4677: IF l_old_break_pa_count > 0 OR l_new_break_pa_count > 0 THEN
4678: --Multiple price breaks
4679: qp_bulk_loader_pub.write_log('The price breaks header has multiple price breaks');
4680: IF l_to_value_old = 1 OR l_to_value_new = 1 OR
4681: to_number(p_pricing_attr_rec.pricing_attr_value_to(I)) = l_break_high_value THEN
4682: qp_bulk_loader_pub.write_log('TO value has a corresponding FROM value or is the max.');
4683: IF l_from_value_old = 1 OR l_from_value_new = 1 OR
4678: --Multiple price breaks
4679: qp_bulk_loader_pub.write_log('The price breaks header has multiple price breaks');
4680: IF l_to_value_old = 1 OR l_to_value_new = 1 OR
4681: to_number(p_pricing_attr_rec.pricing_attr_value_to(I)) = l_break_high_value THEN
4682: qp_bulk_loader_pub.write_log('TO value has a corresponding FROM value or is the max.');
4683: IF l_from_value_old = 1 OR l_from_value_new = 1 OR
4684: to_number(p_pricing_attr_rec.pricing_attr_value_from(I))=0 THEN
4685: null;
4686: qp_bulk_loader_pub.write_log('FROM value has a corresponding TO value or is zero.');
4682: qp_bulk_loader_pub.write_log('TO value has a corresponding FROM value or is the max.');
4683: IF l_from_value_old = 1 OR l_from_value_new = 1 OR
4684: to_number(p_pricing_attr_rec.pricing_attr_value_from(I))=0 THEN
4685: null;
4686: qp_bulk_loader_pub.write_log('FROM value has a corresponding TO value or is zero.');
4687: ELSE
4688: qp_bulk_loader_pub.write_log('ERROR: No Same To_From Value for Price Break');
4689: P_PRICING_ATTR_REC.PROCESS_STATUS_FLAG(I):= NULL ; --'E';
4690: FND_MESSAGE.SET_NAME('QP', 'QP_PBK_CRSPNDNG_TO_FROM');
4684: to_number(p_pricing_attr_rec.pricing_attr_value_from(I))=0 THEN
4685: null;
4686: qp_bulk_loader_pub.write_log('FROM value has a corresponding TO value or is zero.');
4687: ELSE
4688: qp_bulk_loader_pub.write_log('ERROR: No Same To_From Value for Price Break');
4689: P_PRICING_ATTR_REC.PROCESS_STATUS_FLAG(I):= NULL ; --'E';
4690: FND_MESSAGE.SET_NAME('QP', 'QP_PBK_CRSPNDNG_TO_FROM');
4691: QP_BULK_MSG.ADD(l_msg_rec);
4692:
4711: p_pricing_attr_rec.orig_sys_header_ref(I),p_pricing_attr_rec.orig_sys_line_ref(I),null,
4712: null,l_msg_txt);
4713: END IF;
4714: ELSE
4715: qp_bulk_loader_pub.write_log('ERROR: No Same From_To Value for Price Break');
4716: P_PRICING_ATTR_REC.PROCESS_STATUS_FLAG(I):= NULL ; --'E';
4717: FND_MESSAGE.SET_NAME('QP', 'QP_PBK_CRSPNDNG_FROM_TO');
4718: QP_BULK_MSG.ADD(l_msg_rec);
4719:
4747: --Bug# 5528754 RAVI END
4748: IF l_old_break_low_value = 0 OR l_new_break_low_value = 0 THEN
4749: NULL;
4750: ELSE
4751: qp_bulk_loader_pub.write_log('ERROR: No 0 From Value');
4752: P_PRICING_ATTR_REC.PROCESS_STATUS_FLAG(I):= NULL ; --'E';
4753: FND_MESSAGE.SET_NAME('QP', 'QP_PBK_ZERO_FROM');
4754: QP_BULK_MSG.ADD(l_msg_rec);
4755:
4788: CLOSE C_Int_break_lines;
4789: END IF;
4790:
4791: IF l_status = 'OVERLAP' OR l_status1 = 'OVERLAP' THEN
4792: qp_bulk_loader_pub.write_log('Overlapping Price Breaks');
4793: P_PRICING_ATTR_REC.PROCESS_STATUS_FLAG(I):= NULL ; --'E';
4794: FND_MESSAGE.SET_NAME('QP', 'QP_OVERLAP_PRICE_BREAK_RANGE');
4795: QP_BULK_MSG.ADD(l_msg_rec);
4796:
4826: IF ( p_Pricing_Attr_rec.pricing_attribute_context(I) is not null
4827: or p_Pricing_Attr_rec.pricing_attribute(I) is not null ) then
4828:
4829: IF l_pricing_attr_flex_enabled = 'Y' THEN
4830: qp_bulk_loader_pub.write_log( 'Pricing Attr Flex enabled ');
4831:
4832: IF NOT Init_Desc_Flex
4833: (p_flex_name =>'QP_PRICING_ATTRIBUTES'
4834: ,p_context => p_pricing_attr_rec.context(i)
4981: p_Pricing_Attr_rec.product_attribute(I),
4982: l_sourcing_enabled,
4983: l_sourcing_status,
4984: l_sourcing_method);
4985: qp_bulk_loader_pub.write_log('Sourcing method / status / enabled'||l_sourcing_method||l_sourcing_status||l_sourcing_enabled);
4986:
4987: IF l_sourcing_method = 'ATTRIBUTE MAPPING' THEN
4988:
4989: IF l_sourcing_enabled <> 'Y' THEN
5026: l_sourcing_enabled,
5027: l_sourcing_status,
5028: l_sourcing_method);
5029:
5030: qp_bulk_loader_pub.write_log('Sourcing method / status / enabled'||l_sourcing_method||l_sourcing_status||l_sourcing_enabled);
5031:
5032: IF l_sourcing_method = 'ATTRIBUTE MAPPING' THEN
5033:
5034: IF l_sourcing_enabled <> 'Y' THEN
5055: END IF; --If sourcing_method = 'ATTRIBUTE MAPPING'
5056: END IF; -- process_status_flag(I) <> 'E'
5057: END LOOP;
5058:
5059: qp_bulk_loader_pub.write_log( 'Leaving Entity Pricing Attr validation');
5060:
5061: EXCEPTION
5062: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5063: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_PRICING_ATTR:'||sqlerrm);
5059: qp_bulk_loader_pub.write_log( 'Leaving Entity Pricing Attr validation');
5060:
5061: EXCEPTION
5062: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5063: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_PRICING_ATTR:'||sqlerrm);
5064: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5065: WHEN OTHERS THEN
5066: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_PRICING_ATTR:'||sqlerrm);
5067: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5062: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5063: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_PRICING_ATTR:'||sqlerrm);
5064: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5065: WHEN OTHERS THEN
5066: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ENTITY_PRICING_ATTR:'||sqlerrm);
5067: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5068:
5069:
5070: END ENTITY_PRICING_ATTR;
5088: --Name
5089: --PTE
5090: --Source System
5091:
5092: qp_bulk_loader_pub.write_log('Entering Attribute Header validation');
5093: -- 1.Currency Code
5094:
5095: FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
5096: FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CURRENCY CODE');
5143: WHERE qpih.request_id = p_request_id
5144: AND qpih.process_status_flag ='P' --is null
5145: AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
5146: AND qpih.freight_terms_code IS NOT NULL
5147: AND qpih.freight_terms_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
5148: AND qpih.freight_terms IS NOT NULL
5149: AND NOT EXISTS (SELECT freight_terms_code
5150: FROM OE_FRGHT_TERMS_ACTIVE_V
5151: WHERE freight_terms_code = qpih.freight_terms_code
5192: AND qpih.process_status_flag ='P' --is null
5193: AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
5194: AND qpih.freight_terms IS NULL
5195: AND qpih.freight_terms_code IS NOT NULL
5196: AND qpih.freight_terms_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
5197: AND NOT EXISTS (SELECT freight_terms_code
5198: FROM OE_FRGHT_TERMS_ACTIVE_V
5199: WHERE freight_terms_code = qpih.freight_terms_code
5200: AND sysdate BETWEEN nvl(start_date_active, sysdate)
5252: AND qpih.process_status_flag ='P' --is null
5253: AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
5254: AND qpih.ship_method IS NOT NULL
5255: AND qpih.ship_method_code IS NOT NULL
5256: AND qpih.ship_method_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
5257: AND NOT EXISTS (SELECT LOOKUP_CODE
5258: FROM OE_SHIP_METHODS_V
5259: WHERE LOOKUP_TYPE = 'SHIP_METHOD'
5260: AND LOOKUP_CODE = qpih.ship_method_code
5278: AND qpih.process_status_flag ='P' --is null
5279: AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
5280: AND qpih.ship_method IS NULL
5281: AND qpih.ship_method_code IS NOT NULL
5282: AND qpih.ship_method_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
5283: AND NOT EXISTS (SELECT LOOKUP_CODE
5284: FROM OE_SHIP_METHODS_V
5285: WHERE LOOKUP_TYPE = 'SHIP_METHOD'
5286: AND LOOKUP_CODE = qpih.ship_method_code
5335: AND qpih.process_status_flag ='P' --is null
5336: AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
5337: AND qpih.terms_id IS NOT NULL
5338: AND qpih.terms IS NOT NULL
5339: AND qpih.terms <> QP_BULK_LOADER_PUB.G_NULL_CHAR
5340: --Bug#5248659 RAVI
5341: --Use Term_id not terms_id
5342: AND NOT EXISTS (SELECT TERM_ID
5343: FROM RA_TERMS
5387: AND qpih.process_status_flag ='P' --is null
5388: AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
5389: AND qpih.terms_id IS NULL
5390: AND qpih.terms IS NOT NULL
5391: AND qpih.terms <> QP_BULK_LOADER_PUB.G_NULL_CHAR
5392: --Bug#5248659 RAVI
5393: --Use Term_id not terms_id
5394: AND NOT EXISTS (SELECT TERM_ID
5395: FROM RA_TERMS
5425: AND qpih.process_status_flag ='P' --is null
5426: AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
5427: AND qpih.currency_header_id is NOT NULL
5428: AND qpih.currency_header is NOT NULL
5429: AND qpih.currency_header <> QP_BULK_LOADER_PUB.G_NULL_CHAR
5430: AND NOT EXISTS ( SELECT qpclb.currency_header_id
5431: FROM qp_currency_lists_b qpclb, qp_currency_lists_tl qpclt
5432: WHERE qpclb.currency_header_id = qpih.currency_header_id
5433: AND qpclb.base_currency_code = qpih.currency_code
5450: WHERE qpih.request_id = p_request_id
5451: AND qpih.process_status_flag ='P' --is null
5452: AND qpih.interface_action_code IN ('INSERT', 'UPDATE')
5453: AND qpih.currency_header is NOT NULL
5454: AND qpih.currency_header <> QP_BULK_LOADER_PUB.G_NULL_CHAR
5455: AND qpih.currency_header_id is NULL
5456: AND NOT EXISTS ( SELECT qpclb.currency_header_id
5457: FROM qp_currency_lists_b qpclb, qp_currency_lists_tl qpclt
5458: WHERE qpclb.base_currency_code = qpih.currency_code
5511:
5512: fnd_message.set_name('QP', 'SO_OTHER_NAME_ALREADY_IN_USE');
5513: l_msg_txt := FND_MESSAGE.GET;
5514:
5515: qp_bulk_loader_pub.write_log('Name check in TL table '||userenv('LANG')||' '||to_char(p_request_id));
5516:
5517: INSERT INTO QP_INTERFACE_ERRORS
5518: (error_id,last_update_date, last_updated_by, creation_date,
5519: created_by, last_update_login, request_id, program_application_id,
5532: AND EXISTS ( Select qlht.name from qp_list_headers_tl qlht
5533: where qlht.name= qpih.name
5534: and qlht.language = qpih.language);
5535:
5536: qp_bulk_loader_pub.write_log('Count '||SQL%ROWCOUNT);
5537:
5538: -- 9. Name in other Interface header records
5539:
5540: fnd_message.set_name('QP', 'SO_OTHER_NAME_ALREADY_IN_USE');
5560: and qpih1.name = qpih.name
5561: and qpih1.process_status_flag is null
5562: and qpih1.rowid <> qpih.rowid
5563: and qpih1.interface_action_code = 'INSERT');
5564: qp_bulk_loader_pub.write_log('Count 9 '||SQL%ROWCOUNT);
5565:
5566: -- 10. Pricing Transaction Entity
5567:
5568: FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
5718: ) > 1;*/
5719: --Bug#3604226 RAVI END
5720:
5721:
5722: qp_bulk_loader_pub.write_log('Leaving Attribute Header validation');
5723:
5724: EXCEPTION
5725: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5726: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_HEADER:'||sqlerrm);
5722: qp_bulk_loader_pub.write_log('Leaving Attribute Header validation');
5723:
5724: EXCEPTION
5725: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5726: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_HEADER:'||sqlerrm);
5727: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5728: WHEN OTHERS THEN
5729: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_HEADER:'||sqlerrm);
5730: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5725: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5726: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_HEADER:'||sqlerrm);
5727: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5728: WHEN OTHERS THEN
5729: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_HEADER:'||sqlerrm);
5730: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5731:
5732: END ATTRIBUTE_HEADER;
5733:
5741: l_pte_code VARCHAR2(30);
5742:
5743: BEGIN
5744:
5745: qp_bulk_loader_pub.write_log('Entering Attribute qualifier validation');
5746: FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY',l_pte_code);
5747: --1. Comparison Operator
5748:
5749: FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
5802: -- Basic_Pricing_Condition
5803: -- In the case of Basic pricing only the qualifier of type secondary
5804: -- Pricelists are allowed.
5805:
5806: IF QP_BULK_LOADER_PUB.GET_QP_STATUS <> 'I' THEN
5807:
5808: FND_MESSAGE.SET_NAME('QP','QP_QUAL_NA_BASIC');
5809: --Qualifiers not allowed in Basic Pricing
5810: l_msg_txt := FND_MESSAGE.GET;
5876: AND qpiq.process_status_flag ='P' --is null
5877: AND qpiq.interface_action_code IN ('INSERT', 'UPDATE')
5878: AND qpiq.qualifier_attribute is not null
5879: AND qpiq.qualifier_attribute_code is not null
5880: AND qpiq.qualifier_attribute_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
5881: AND NOT EXISTS (
5882: SELECT 'x'
5883: FROM qp_interface_qualifiers b,
5884: qp_prc_contexts_b d,
5910: AND qpiq.process_status_flag ='P' --is null
5911: AND qpiq.interface_action_code IN ('INSERT', 'UPDATE')
5912: AND qpiq.qualifier_attribute is null
5913: AND qpiq.qualifier_attribute_code is not null
5914: AND qpiq.qualifier_attribute_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
5915: AND NOT EXISTS (
5916: SELECT 'x'
5917: FROM qp_interface_qualifiers b,
5918: qp_prc_contexts_b d,
5959: -- AND f.segment_level NOT IN ('LINE') bug#12710486
5960: AND qpiq.rowid = b.rowid);
5961: --Bug# 5456164 END RAVI
5962:
5963: qp_bulk_loader_pub.write_log('Leaving Attribute qualifier validation');
5964:
5965: EXCEPTION
5966: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5967: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_QUALIFIER:'||sqlerrm);
5963: qp_bulk_loader_pub.write_log('Leaving Attribute qualifier validation');
5964:
5965: EXCEPTION
5966: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5967: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_QUALIFIER:'||sqlerrm);
5968: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5969: WHEN OTHERS THEN
5970: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_QUALIFIER:'||sqlerrm);
5971: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5966: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5967: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_QUALIFIER:'||sqlerrm);
5968: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5969: WHEN OTHERS THEN
5970: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_QUALIFIER:'||sqlerrm);
5971: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5972:
5973: END ATTRIBUTE_QUALIFIER;
5974:
6001: l_pte_code VARCHAR2(30);
6002:
6003: BEGIN
6004:
6005: qp_bulk_loader_pub.write_log('Entering Attribute line validation');
6006: FND_PROFILE.GET('QP_PRICING_TRANSACTION_ENTITY',l_pte_code);
6007:
6008: -- 1. LIST LINE TYPE CODE
6009: FND_MESSAGE.SET_NAME('QP','QP_INVALID_ATTRIBUTE');
6235: WHERE qpil.request_id = p_request_id
6236: AND qpil.process_status_flag ='P' --is null
6237: AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
6238: AND qpil.price_by_formula_id is NOT NULL
6239: AND qpil.price_by_formula_id <> QP_BULK_LOADER_PUB.G_NULL_NUMBER
6240: AND qpil.generate_using_formula_id is NOT NULL
6241: AND qpil.generate_using_formula_id <> QP_BULK_LOADER_PUB.G_NULL_NUMBER;
6242: --Bug# 5465109 RAVI END
6243:
6237: AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
6238: AND qpil.price_by_formula_id is NOT NULL
6239: AND qpil.price_by_formula_id <> QP_BULK_LOADER_PUB.G_NULL_NUMBER
6240: AND qpil.generate_using_formula_id is NOT NULL
6241: AND qpil.generate_using_formula_id <> QP_BULK_LOADER_PUB.G_NULL_NUMBER;
6242: --Bug# 5465109 RAVI END
6243:
6244: --5. PRICE BY FORMULA ID
6245:
6265: AND qpil.process_status_flag ='P' --is null
6266: AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
6267: AND qpil.price_by_formula_id is NOT NULL
6268: AND qpil.price_by_formula is NOT NULL
6269: AND qpil.price_by_formula <> QP_BULK_LOADER_PUB.G_NULL_CHAR
6270: AND NOT EXISTS ( SELECT name
6271: FROM qp_price_formulas_vl
6272: WHERE price_formula_id = qpil.price_by_formula_id
6273: AND name = qpil.price_by_formula);
6309: AND qpil.process_status_flag ='P' --is null
6310: AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
6311: AND qpil.price_by_formula_id is NULL
6312: AND qpil.price_by_formula is NOT NULL
6313: AND qpil.price_by_formula <> QP_BULK_LOADER_PUB.G_NULL_CHAR
6314: AND NOT EXISTS ( SELECT name
6315: FROM qp_price_formulas_vl
6316: WHERE name = qpil.price_by_formula);
6317:
6339: AND qpil.process_status_flag ='P' --is null
6340: AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
6341: AND qpil.generate_using_formula_id is NOT NULL
6342: AND qpil.generate_using_formula is NOT NULL
6343: AND qpil.generate_using_formula <> QP_BULK_LOADER_PUB.G_NULL_CHAR
6344: AND NOT EXISTS ( SELECT name
6345: FROM qp_price_formulas_vl
6346: WHERE price_formula_id = qpil.generate_using_formula_id
6347: AND name = qpil.generate_using_formula);
6347: AND name = qpil.generate_using_formula);
6348:
6349:
6350: IF SQL%ROWCOUNT >0 THEN
6351: qp_bulk_loader_pub.write_log('Generate formula ID and Name are incompatible');
6352: END IF;
6353:
6354: --FND_MESSAGE.SET_NAME('QP','QP_INVALID_FORMULA_FOR_PL');
6355: --l_msg_txt := FND_MESSAGE.GET;
6392: AND qpil.process_status_flag ='P' --is null
6393: AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
6394: AND qpil.generate_using_formula_id is NULL
6395: AND qpil.generate_using_formula is NOT NULL
6396: AND qpil.generate_using_formula <> QP_BULK_LOADER_PUB.G_NULL_CHAR
6397: AND NOT EXISTS ( SELECT name
6398: FROM qp_price_formulas_vl
6399: WHERE name = qpil.generate_using_formula);
6400:
6399: WHERE name = qpil.generate_using_formula);
6400:
6401: -- Bug# 5236656 RAVI
6402: IF SQL%ROWCOUNT >0 THEN
6403: qp_bulk_loader_pub.write_log('Generate formula ID is inaccurate');
6404: END IF;
6405:
6406: --6. PBH not allowed in Basic pricing
6407: -- Basic_Pricing_Condition
6405:
6406: --6. PBH not allowed in Basic pricing
6407: -- Basic_Pricing_Condition
6408:
6409: IF QP_BULK_LOADER_PUB.GET_QP_STATUS <> 'I' THEN
6410: FND_MESSAGE.SET_NAME('QP','QP_PBH_NA_BASIC');
6411: --Price Break Header not allowed in Basic Pricing
6412: l_msg_txt := FND_MESSAGE.GET;
6413:
6504: AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
6505: AND qpip.product_attribute_context IS NOT NULL
6506: AND qpip.product_attribute IS NOT NULL
6507: AND qpip.product_attr_code IS NOT NULL
6508: AND qpip.product_attr_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
6509: AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
6510: AND NOT EXISTS ( SELECT 'x'
6511: FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
6512: WHERE b.product_attribute_context = d.prc_context_code
6537: AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
6538: AND qpip.product_attribute_context IS NOT NULL
6539: AND qpip.product_attribute IS NULL
6540: AND qpip.product_attr_code IS NOT NULL
6541: AND qpip.product_attr_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
6542: AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
6543: AND NOT EXISTS ( SELECT 'x'
6544: FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
6545: WHERE b.product_attribute_context = d.prc_context_code
6606: AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
6607: AND qpip.pricing_attribute_context IS NOT NULL
6608: AND qpip.pricing_attribute IS NOT NULL
6609: AND qpip.pricing_attr_code IS NOT NULL
6610: AND qpip.pricing_attr_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
6611: AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
6612: AND NOT EXISTS ( SELECT 'x'
6613: FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
6614: WHERE b.pricing_attribute_context = d.prc_context_code
6639: AND qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
6640: AND qpip.pricing_attribute_context IS NOT NULL
6641: AND qpip.pricing_attribute IS NULL
6642: AND qpip.pricing_attr_code IS NOT NULL
6643: AND qpip.pricing_attr_code <> QP_BULK_LOADER_PUB.G_NULL_CHAR
6644: AND qpil.interface_action_code IN ('INSERT', 'UPDATE')
6645: AND NOT EXISTS ( SELECT 'x'
6646: FROM qp_interface_pricing_attribs b, qp_prc_contexts_b d, qp_segments_b e, qp_pte_segments f
6647: WHERE b.pricing_attribute_context = d.prc_context_code
6683: AND f.pte_code = l_pte_code
6684: AND qpip.rowid = b.rowid);
6685: --Bug# 5456164 END RAVI
6686:
6687: qp_bulk_loader_pub.write_log('Leaving Attribute Line validation');
6688: EXCEPTION
6689: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6690: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_LINE:'||sqlerrm);
6691: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6686:
6687: qp_bulk_loader_pub.write_log('Leaving Attribute Line validation');
6688: EXCEPTION
6689: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6690: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_LINE:'||sqlerrm);
6691: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6692: WHEN OTHERS THEN
6693: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_LINE:'||sqlerrm);
6694: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6689: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6690: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_LINE:'||sqlerrm);
6691: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6692: WHEN OTHERS THEN
6693: qp_bulk_loader_pub.write_log( 'UNEXCPECTED ERROR IN PROCEDURE ATTRIBUTE_LINE:'||sqlerrm);
6694: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6695: END ATTRIBUTE_LINE;
6696:
6697:
6700: p_request_id NUMBER)
6701: IS
6702: BEGIN
6703:
6704: qp_bulk_loader_pub.write_log('Entering Mark errored inreface record');
6705:
6706: IF p_table_type = 'HEADER' THEN
6707:
6708: UPDATE qp_interface_list_headers h
6758: );
6759:
6760: END IF;
6761:
6762: qp_bulk_loader_pub.write_log('Entering Mark errored inreface record');
6763:
6764: EXCEPTION
6765: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6766: qp_bulk_loader_pub.write_log(
6762: qp_bulk_loader_pub.write_log('Entering Mark errored inreface record');
6763:
6764: EXCEPTION
6765: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6766: qp_bulk_loader_pub.write_log(
6767: 'UNEXCPECTED ERROR IN PROCEDURE MARK_ERRORED_INTERFACE_RECORD:'||sqlerrm);
6768: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6769: WHEN OTHERS THEN
6770: qp_bulk_loader_pub.write_log(
6766: qp_bulk_loader_pub.write_log(
6767: 'UNEXCPECTED ERROR IN PROCEDURE MARK_ERRORED_INTERFACE_RECORD:'||sqlerrm);
6768: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6769: WHEN OTHERS THEN
6770: qp_bulk_loader_pub.write_log(
6771: 'UNEXCPECTED ERROR IN PROCEDURE MARK_ERRORED_INTERFACE_RECORD:'||sqlerrm);
6772: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6773:
6774: END MARK_ERRORED_INTERFACE_RECORD;