eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OZF_CLAIM_ACCRUAL_PVT dependencies on FND_DSQL

Line 646: -- 05-MAY-2003 yizhang Use FND_DSQL for dynamic sql and bind vars

642: -- p_select_cust_children_flag : Include all member earnings
643: --
644: -- HISTORY
645: -- 14-FEB-2003 yizhang Create.
646: -- 05-MAY-2003 yizhang Use FND_DSQL for dynamic sql and bind vars
647: -- 28-feb-06 azahmed modified for bugfix 4958714
648: ---------------------------------------------------------------------
649: PROCEDURE Get_Customer_For_Earnings(
650: p_cust_account_id IN NUMBER

Line 678: FND_DSQL.add_text(' IN (SELECT c.cust_account_id FROM hz_cust_accounts c');

674: p_select_cust_children_flag = 'F' OR
675: p_select_cust_children_flag = 'N' THEN
676: -- not to include member earnings
677: IF p_buy_group_party_id IS NOT NULL THEN
678: FND_DSQL.add_text(' IN (SELECT c.cust_account_id FROM hz_cust_accounts c');
679: FND_DSQL.add_text(' WHERE c.party_id = ');
680: FND_DSQL.add_bind(p_buy_group_party_id);
681: FND_DSQL.add_text(')');
682: ELSIF p_relationship_type IS NOT NULL AND

Line 679: FND_DSQL.add_text(' WHERE c.party_id = ');

675: p_select_cust_children_flag = 'N' THEN
676: -- not to include member earnings
677: IF p_buy_group_party_id IS NOT NULL THEN
678: FND_DSQL.add_text(' IN (SELECT c.cust_account_id FROM hz_cust_accounts c');
679: FND_DSQL.add_text(' WHERE c.party_id = ');
680: FND_DSQL.add_bind(p_buy_group_party_id);
681: FND_DSQL.add_text(')');
682: ELSIF p_relationship_type IS NOT NULL AND
683: p_related_cust_account_id IS NULL

Line 680: FND_DSQL.add_bind(p_buy_group_party_id);

676: -- not to include member earnings
677: IF p_buy_group_party_id IS NOT NULL THEN
678: FND_DSQL.add_text(' IN (SELECT c.cust_account_id FROM hz_cust_accounts c');
679: FND_DSQL.add_text(' WHERE c.party_id = ');
680: FND_DSQL.add_bind(p_buy_group_party_id);
681: FND_DSQL.add_text(')');
682: ELSIF p_relationship_type IS NOT NULL AND
683: p_related_cust_account_id IS NULL
684: THEN

Line 681: FND_DSQL.add_text(')');

677: IF p_buy_group_party_id IS NOT NULL THEN
678: FND_DSQL.add_text(' IN (SELECT c.cust_account_id FROM hz_cust_accounts c');
679: FND_DSQL.add_text(' WHERE c.party_id = ');
680: FND_DSQL.add_bind(p_buy_group_party_id);
681: FND_DSQL.add_text(')');
682: ELSIF p_relationship_type IS NOT NULL AND
683: p_related_cust_account_id IS NULL
684: THEN
685: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');

Line 685: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');

681: FND_DSQL.add_text(')');
682: ELSIF p_relationship_type IS NOT NULL AND
683: p_related_cust_account_id IS NULL
684: THEN
685: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
686: FND_DSQL.add_text(' WHERE cust_account_id = ');
687: FND_DSQL.add_bind(p_cust_account_id);
688: FND_DSQL.add_text(' AND NVL(relationship_type,''ALL'') = ');--For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
689: FND_DSQL.add_bind(p_relationship_type);

Line 686: FND_DSQL.add_text(' WHERE cust_account_id = ');

682: ELSIF p_relationship_type IS NOT NULL AND
683: p_related_cust_account_id IS NULL
684: THEN
685: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
686: FND_DSQL.add_text(' WHERE cust_account_id = ');
687: FND_DSQL.add_bind(p_cust_account_id);
688: FND_DSQL.add_text(' AND NVL(relationship_type,''ALL'') = ');--For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
689: FND_DSQL.add_bind(p_relationship_type);
690: FND_DSQL.add_text(')');

Line 687: FND_DSQL.add_bind(p_cust_account_id);

683: p_related_cust_account_id IS NULL
684: THEN
685: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
686: FND_DSQL.add_text(' WHERE cust_account_id = ');
687: FND_DSQL.add_bind(p_cust_account_id);
688: FND_DSQL.add_text(' AND NVL(relationship_type,''ALL'') = ');--For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
689: FND_DSQL.add_bind(p_relationship_type);
690: FND_DSQL.add_text(')');
691: ELSE

Line 688: FND_DSQL.add_text(' AND NVL(relationship_type,''ALL'') = ');--For Bug 13489587 to search as ALL for having relationship_type of related customer as null.

684: THEN
685: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
686: FND_DSQL.add_text(' WHERE cust_account_id = ');
687: FND_DSQL.add_bind(p_cust_account_id);
688: FND_DSQL.add_text(' AND NVL(relationship_type,''ALL'') = ');--For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
689: FND_DSQL.add_bind(p_relationship_type);
690: FND_DSQL.add_text(')');
691: ELSE
692: FND_DSQL.add_text(' = ');

Line 689: FND_DSQL.add_bind(p_relationship_type);

685: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
686: FND_DSQL.add_text(' WHERE cust_account_id = ');
687: FND_DSQL.add_bind(p_cust_account_id);
688: FND_DSQL.add_text(' AND NVL(relationship_type,''ALL'') = ');--For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
689: FND_DSQL.add_bind(p_relationship_type);
690: FND_DSQL.add_text(')');
691: ELSE
692: FND_DSQL.add_text(' = ');
693: IF p_related_cust_account_id IS NOT NULL THEN

Line 690: FND_DSQL.add_text(')');

686: FND_DSQL.add_text(' WHERE cust_account_id = ');
687: FND_DSQL.add_bind(p_cust_account_id);
688: FND_DSQL.add_text(' AND NVL(relationship_type,''ALL'') = ');--For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
689: FND_DSQL.add_bind(p_relationship_type);
690: FND_DSQL.add_text(')');
691: ELSE
692: FND_DSQL.add_text(' = ');
693: IF p_related_cust_account_id IS NOT NULL THEN
694: FND_DSQL.add_bind(p_related_cust_account_id);

Line 692: FND_DSQL.add_text(' = ');

688: FND_DSQL.add_text(' AND NVL(relationship_type,''ALL'') = ');--For Bug 13489587 to search as ALL for having relationship_type of related customer as null.
689: FND_DSQL.add_bind(p_relationship_type);
690: FND_DSQL.add_text(')');
691: ELSE
692: FND_DSQL.add_text(' = ');
693: IF p_related_cust_account_id IS NOT NULL THEN
694: FND_DSQL.add_bind(p_related_cust_account_id);
695: ELSE
696: FND_DSQL.add_bind(p_cust_account_id);

Line 694: FND_DSQL.add_bind(p_related_cust_account_id);

690: FND_DSQL.add_text(')');
691: ELSE
692: FND_DSQL.add_text(' = ');
693: IF p_related_cust_account_id IS NOT NULL THEN
694: FND_DSQL.add_bind(p_related_cust_account_id);
695: ELSE
696: FND_DSQL.add_bind(p_cust_account_id);
697: END IF;
698: END IF;

Line 696: FND_DSQL.add_bind(p_cust_account_id);

692: FND_DSQL.add_text(' = ');
693: IF p_related_cust_account_id IS NOT NULL THEN
694: FND_DSQL.add_bind(p_related_cust_account_id);
695: ELSE
696: FND_DSQL.add_bind(p_cust_account_id);
697: END IF;
698: END IF;
699:
700: ELSIF p_select_cust_children_flag = 'T' OR p_select_cust_children_flag = 'Y' THEN

Line 719: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');

715: END IF;
716: END IF;
717:
718: IF l_bg_is_parent_of_cust = 1 OR l_bg_is_parent_of_relcust = 1 THEN
719: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
720: FND_DSQL.add_text(' FROM hz_cust_accounts c ');
721: FND_DSQL.add_text(' WHERE c.party_id = ');
722: FND_DSQL.add_bind(p_buy_group_party_id);
723: FND_DSQL.add_text(' OR c.cust_account_id = ');

Line 720: FND_DSQL.add_text(' FROM hz_cust_accounts c ');

716: END IF;
717:
718: IF l_bg_is_parent_of_cust = 1 OR l_bg_is_parent_of_relcust = 1 THEN
719: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
720: FND_DSQL.add_text(' FROM hz_cust_accounts c ');
721: FND_DSQL.add_text(' WHERE c.party_id = ');
722: FND_DSQL.add_bind(p_buy_group_party_id);
723: FND_DSQL.add_text(' OR c.cust_account_id = ');
724: IF p_related_cust_account_id is not NULL THEN

Line 721: FND_DSQL.add_text(' WHERE c.party_id = ');

717:
718: IF l_bg_is_parent_of_cust = 1 OR l_bg_is_parent_of_relcust = 1 THEN
719: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
720: FND_DSQL.add_text(' FROM hz_cust_accounts c ');
721: FND_DSQL.add_text(' WHERE c.party_id = ');
722: FND_DSQL.add_bind(p_buy_group_party_id);
723: FND_DSQL.add_text(' OR c.cust_account_id = ');
724: IF p_related_cust_account_id is not NULL THEN
725: FND_DSQL.add_bind(p_related_cust_account_id);

Line 722: FND_DSQL.add_bind(p_buy_group_party_id);

718: IF l_bg_is_parent_of_cust = 1 OR l_bg_is_parent_of_relcust = 1 THEN
719: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
720: FND_DSQL.add_text(' FROM hz_cust_accounts c ');
721: FND_DSQL.add_text(' WHERE c.party_id = ');
722: FND_DSQL.add_bind(p_buy_group_party_id);
723: FND_DSQL.add_text(' OR c.cust_account_id = ');
724: IF p_related_cust_account_id is not NULL THEN
725: FND_DSQL.add_bind(p_related_cust_account_id);
726: ELSE

Line 723: FND_DSQL.add_text(' OR c.cust_account_id = ');

719: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
720: FND_DSQL.add_text(' FROM hz_cust_accounts c ');
721: FND_DSQL.add_text(' WHERE c.party_id = ');
722: FND_DSQL.add_bind(p_buy_group_party_id);
723: FND_DSQL.add_text(' OR c.cust_account_id = ');
724: IF p_related_cust_account_id is not NULL THEN
725: FND_DSQL.add_bind(p_related_cust_account_id);
726: ELSE
727: FND_DSQL.add_bind(p_cust_account_id);

Line 725: FND_DSQL.add_bind(p_related_cust_account_id);

721: FND_DSQL.add_text(' WHERE c.party_id = ');
722: FND_DSQL.add_bind(p_buy_group_party_id);
723: FND_DSQL.add_text(' OR c.cust_account_id = ');
724: IF p_related_cust_account_id is not NULL THEN
725: FND_DSQL.add_bind(p_related_cust_account_id);
726: ELSE
727: FND_DSQL.add_bind(p_cust_account_id);
728: END IF;
729: FND_DSQL.add_text(')');

Line 727: FND_DSQL.add_bind(p_cust_account_id);

723: FND_DSQL.add_text(' OR c.cust_account_id = ');
724: IF p_related_cust_account_id is not NULL THEN
725: FND_DSQL.add_bind(p_related_cust_account_id);
726: ELSE
727: FND_DSQL.add_bind(p_cust_account_id);
728: END IF;
729: FND_DSQL.add_text(')');
730:
731: ELSE

Line 729: FND_DSQL.add_text(')');

725: FND_DSQL.add_bind(p_related_cust_account_id);
726: ELSE
727: FND_DSQL.add_bind(p_cust_account_id);
728: END IF;
729: FND_DSQL.add_text(')');
730:
731: ELSE
732: IF p_buy_group_party_id IS NOT NULL THEN
733: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');

Line 733: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');

729: FND_DSQL.add_text(')');
730:
731: ELSE
732: IF p_buy_group_party_id IS NOT NULL THEN
733: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
734: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
735: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
736: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
737: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');

Line 734: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');

730:
731: ELSE
732: IF p_buy_group_party_id IS NOT NULL THEN
733: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
734: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
735: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
736: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
737: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
738: FND_DSQL.add_bind(p_buy_group_party_id);

Line 735: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');

731: ELSE
732: IF p_buy_group_party_id IS NOT NULL THEN
733: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
734: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
735: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
736: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
737: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
738: FND_DSQL.add_bind(p_buy_group_party_id);
739: FND_DSQL.add_text(')');

Line 736: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');

732: IF p_buy_group_party_id IS NOT NULL THEN
733: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
734: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
735: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
736: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
737: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
738: FND_DSQL.add_bind(p_buy_group_party_id);
739: FND_DSQL.add_text(')');
740: ELSE

Line 737: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');

733: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
734: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
735: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
736: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
737: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
738: FND_DSQL.add_bind(p_buy_group_party_id);
739: FND_DSQL.add_text(')');
740: ELSE
741: -- Modified for Bugfix 5346249

Line 738: FND_DSQL.add_bind(p_buy_group_party_id);

734: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
735: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
736: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
737: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
738: FND_DSQL.add_bind(p_buy_group_party_id);
739: FND_DSQL.add_text(')');
740: ELSE
741: -- Modified for Bugfix 5346249
742: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');

Line 739: FND_DSQL.add_text(')');

735: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
736: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
737: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
738: FND_DSQL.add_bind(p_buy_group_party_id);
739: FND_DSQL.add_text(')');
740: ELSE
741: -- Modified for Bugfix 5346249
742: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
743: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');

Line 742: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');

738: FND_DSQL.add_bind(p_buy_group_party_id);
739: FND_DSQL.add_text(')');
740: ELSE
741: -- Modified for Bugfix 5346249
742: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
743: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
744: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
745: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
746: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');

Line 743: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');

739: FND_DSQL.add_text(')');
740: ELSE
741: -- Modified for Bugfix 5346249
742: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
743: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
744: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
745: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
746: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
747: FND_DSQL.add_text(' and cust1.cust_account_id = ');

Line 744: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');

740: ELSE
741: -- Modified for Bugfix 5346249
742: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
743: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
744: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
745: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
746: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
747: FND_DSQL.add_text(' and cust1.cust_account_id = ');
748: IF p_related_cust_account_id IS NOT NULL THEN

Line 745: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');

741: -- Modified for Bugfix 5346249
742: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
743: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
744: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
745: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
746: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
747: FND_DSQL.add_text(' and cust1.cust_account_id = ');
748: IF p_related_cust_account_id IS NOT NULL THEN
749: FND_DSQL.add_bind(p_related_cust_account_id);

Line 746: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');

742: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
743: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
744: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
745: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
746: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
747: FND_DSQL.add_text(' and cust1.cust_account_id = ');
748: IF p_related_cust_account_id IS NOT NULL THEN
749: FND_DSQL.add_bind(p_related_cust_account_id);
750: ELSE

Line 747: FND_DSQL.add_text(' and cust1.cust_account_id = ');

743: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
744: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
745: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
746: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
747: FND_DSQL.add_text(' and cust1.cust_account_id = ');
748: IF p_related_cust_account_id IS NOT NULL THEN
749: FND_DSQL.add_bind(p_related_cust_account_id);
750: ELSE
751: FND_DSQL.add_bind(p_cust_account_id);

Line 749: FND_DSQL.add_bind(p_related_cust_account_id);

745: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
746: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
747: FND_DSQL.add_text(' and cust1.cust_account_id = ');
748: IF p_related_cust_account_id IS NOT NULL THEN
749: FND_DSQL.add_bind(p_related_cust_account_id);
750: ELSE
751: FND_DSQL.add_bind(p_cust_account_id);
752: END IF;
753: FND_DSQL.add_text(')');

Line 751: FND_DSQL.add_bind(p_cust_account_id);

747: FND_DSQL.add_text(' and cust1.cust_account_id = ');
748: IF p_related_cust_account_id IS NOT NULL THEN
749: FND_DSQL.add_bind(p_related_cust_account_id);
750: ELSE
751: FND_DSQL.add_bind(p_cust_account_id);
752: END IF;
753: FND_DSQL.add_text(')');
754: FND_DSQL.add_text(')');
755: END IF;

Line 753: FND_DSQL.add_text(')');

749: FND_DSQL.add_bind(p_related_cust_account_id);
750: ELSE
751: FND_DSQL.add_bind(p_cust_account_id);
752: END IF;
753: FND_DSQL.add_text(')');
754: FND_DSQL.add_text(')');
755: END IF;
756: END IF;
757: END IF;

Line 754: FND_DSQL.add_text(')');

750: ELSE
751: FND_DSQL.add_bind(p_cust_account_id);
752: END IF;
753: FND_DSQL.add_text(')');
754: FND_DSQL.add_text(')');
755: END IF;
756: END IF;
757: END IF;
758:

Line 1026: FND_DSQL.add_text(' AND fu.plan_curr_amount_remaining <> 0 ');

1022: IF p_summary_view IS NULL OR p_summary_view <> 'DEL_GRP_LINE_UTIL' THEN
1023:
1024: -- Added For Multi Currency - kpatro
1025: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY') THEN
1026: FND_DSQL.add_text(' AND fu.plan_curr_amount_remaining <> 0 ');
1027: ELSE
1028: FND_DSQL.add_text(' AND (DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining))<> 0 ');
1029: END IF;
1030:

Line 1028: FND_DSQL.add_text(' AND (DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining))<> 0 ');

1024: -- Added For Multi Currency - kpatro
1025: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY') THEN
1026: FND_DSQL.add_text(' AND fu.plan_curr_amount_remaining <> 0 ');
1027: ELSE
1028: FND_DSQL.add_text(' AND (DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining))<> 0 ');
1029: END IF;
1030:
1031: END IF;
1032:

Line 1034: FND_DSQL.add_text(' AND fu.plan_currency_code = '''||l_currency_rec.transaction_currency_code||''''); --kdass

1030:
1031: END IF;
1032:
1033: IF l_currency_rec.transaction_currency_code IS NOT NULL THEN --restrict for public API
1034: FND_DSQL.add_text(' AND fu.plan_currency_code = '''||l_currency_rec.transaction_currency_code||''''); --kdass
1035: END IF;
1036:
1037:
1038: -- for lead referral accruals, set utilization_type as LEAD_ACCRUAL

Line 1043: FND_DSQL.add_text(' AND fu.utilization_type IN (''LEAD_ACCRUAL'', ''LEAD_ADJUSTMENT'') ' );

1039: -- Fixed for Bug4576309
1040: -- Modified for Bug4997509 to match ClaimAssoVO.getCommonWhereClause
1041: IF l_funds_util_flt.utilization_type IS NULL OR l_funds_util_flt.utilization_type = 'ACCRUAL' THEN
1042: IF l_funds_util_flt.reference_type = 'LEAD_REFERRAL' THEN
1043: FND_DSQL.add_text(' AND fu.utilization_type IN (''LEAD_ACCRUAL'', ''LEAD_ADJUSTMENT'') ' );
1044: ELSIF l_funds_util_flt.reference_type = 'BATCH' THEN
1045: FND_DSQL.add_text(' AND fu.utilization_type = ''CHARGEBACK'' ');
1046: ELSE
1047: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );

Line 1045: FND_DSQL.add_text(' AND fu.utilization_type = ''CHARGEBACK'' ');

1041: IF l_funds_util_flt.utilization_type IS NULL OR l_funds_util_flt.utilization_type = 'ACCRUAL' THEN
1042: IF l_funds_util_flt.reference_type = 'LEAD_REFERRAL' THEN
1043: FND_DSQL.add_text(' AND fu.utilization_type IN (''LEAD_ACCRUAL'', ''LEAD_ADJUSTMENT'') ' );
1044: ELSIF l_funds_util_flt.reference_type = 'BATCH' THEN
1045: FND_DSQL.add_text(' AND fu.utilization_type = ''CHARGEBACK'' ');
1046: ELSE
1047: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );
1048: END IF;
1049: ELSE

Line 1047: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );

1043: FND_DSQL.add_text(' AND fu.utilization_type IN (''LEAD_ACCRUAL'', ''LEAD_ADJUSTMENT'') ' );
1044: ELSIF l_funds_util_flt.reference_type = 'BATCH' THEN
1045: FND_DSQL.add_text(' AND fu.utilization_type = ''CHARGEBACK'' ');
1046: ELSE
1047: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );
1048: END IF;
1049: ELSE
1050: FND_DSQL.add_text(' AND fu.utilization_type = ');
1051: FND_DSQL.add_bind(l_funds_util_flt.utilization_type);

Line 1050: FND_DSQL.add_text(' AND fu.utilization_type = ');

1046: ELSE
1047: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );
1048: END IF;
1049: ELSE
1050: FND_DSQL.add_text(' AND fu.utilization_type = ');
1051: FND_DSQL.add_bind(l_funds_util_flt.utilization_type);
1052: END IF;
1053:
1054: IF l_funds_util_flt.utilization_type = 'ADJUSTMENT' THEN

Line 1051: FND_DSQL.add_bind(l_funds_util_flt.utilization_type);

1047: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );
1048: END IF;
1049: ELSE
1050: FND_DSQL.add_text(' AND fu.utilization_type = ');
1051: FND_DSQL.add_bind(l_funds_util_flt.utilization_type);
1052: END IF;
1053:
1054: IF l_funds_util_flt.utilization_type = 'ADJUSTMENT' THEN
1055: FND_DSQL.add_text(' AND fu.cust_account_id IS NULL ');

Line 1055: FND_DSQL.add_text(' AND fu.cust_account_id IS NULL ');

1051: FND_DSQL.add_bind(l_funds_util_flt.utilization_type);
1052: END IF;
1053:
1054: IF l_funds_util_flt.utilization_type = 'ADJUSTMENT' THEN
1055: FND_DSQL.add_text(' AND fu.cust_account_id IS NULL ');
1056: ELSE
1057: -- bug fix 4338584
1058: IF l_funds_util_flt.cust_account_id IS NOT NULL AND l_scan_data_flag = 'N' AND l_funds_util_flt.run_mode is not null THEN
1059: FND_DSQL.add_text(' AND fu.cust_account_id');

Line 1059: FND_DSQL.add_text(' AND fu.cust_account_id');

1055: FND_DSQL.add_text(' AND fu.cust_account_id IS NULL ');
1056: ELSE
1057: -- bug fix 4338584
1058: IF l_funds_util_flt.cust_account_id IS NOT NULL AND l_scan_data_flag = 'N' AND l_funds_util_flt.run_mode is not null THEN
1059: FND_DSQL.add_text(' AND fu.cust_account_id');
1060:
1061: Get_Customer_For_Earnings(
1062: p_cust_account_id => l_funds_util_flt.cust_account_id
1063: ,p_relationship_type => l_funds_util_flt.relationship_type

Line 1070: FND_DSQL.add_text(' AND fu.cust_account_id = ');

1066: ,p_select_cust_children_flag => l_funds_util_flt.select_cust_children_flag
1067: );
1068:
1069: ELSIF l_cust_account_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1070: FND_DSQL.add_text(' AND fu.cust_account_id = ');
1071: FND_DSQL.add_bind(l_cust_account_id);
1072: END IF;
1073: END IF;
1074:

Line 1071: FND_DSQL.add_bind(l_cust_account_id);

1067: );
1068:
1069: ELSIF l_cust_account_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1070: FND_DSQL.add_text(' AND fu.cust_account_id = ');
1071: FND_DSQL.add_bind(l_cust_account_id);
1072: END IF;
1073: END IF;
1074:
1075: -- Add fund_id as search filter for claim autopay program.

Line 1077: FND_DSQL.add_text(' AND fu.fund_id = ');

1073: END IF;
1074:
1075: -- Add fund_id as search filter for claim autopay program.
1076: IF l_funds_util_flt.fund_id IS NOT NULL THEN
1077: FND_DSQL.add_text(' AND fu.fund_id = ');
1078: FND_DSQL.add_bind(l_funds_util_flt.fund_id);
1079: END IF;
1080:
1081: IF l_funds_util_flt.activity_id IS NOT NULL THEN

Line 1078: FND_DSQL.add_bind(l_funds_util_flt.fund_id);

1074:
1075: -- Add fund_id as search filter for claim autopay program.
1076: IF l_funds_util_flt.fund_id IS NOT NULL THEN
1077: FND_DSQL.add_text(' AND fu.fund_id = ');
1078: FND_DSQL.add_bind(l_funds_util_flt.fund_id);
1079: END IF;
1080:
1081: IF l_funds_util_flt.activity_id IS NOT NULL THEN
1082: FND_DSQL.add_text(' AND fu.plan_id = ');

Line 1082: FND_DSQL.add_text(' AND fu.plan_id = ');

1078: FND_DSQL.add_bind(l_funds_util_flt.fund_id);
1079: END IF;
1080:
1081: IF l_funds_util_flt.activity_id IS NOT NULL THEN
1082: FND_DSQL.add_text(' AND fu.plan_id = ');
1083: FND_DSQL.add_bind(l_funds_util_flt.activity_id);
1084: END IF;
1085:
1086: IF l_funds_util_flt.reference_type IS NOT NULL THEN

Line 1083: FND_DSQL.add_bind(l_funds_util_flt.activity_id);

1079: END IF;
1080:
1081: IF l_funds_util_flt.activity_id IS NOT NULL THEN
1082: FND_DSQL.add_text(' AND fu.plan_id = ');
1083: FND_DSQL.add_bind(l_funds_util_flt.activity_id);
1084: END IF;
1085:
1086: IF l_funds_util_flt.reference_type IS NOT NULL THEN
1087: FND_DSQL.add_text(' AND fu.reference_type = ');

Line 1087: FND_DSQL.add_text(' AND fu.reference_type = ');

1083: FND_DSQL.add_bind(l_funds_util_flt.activity_id);
1084: END IF;
1085:
1086: IF l_funds_util_flt.reference_type IS NOT NULL THEN
1087: FND_DSQL.add_text(' AND fu.reference_type = ');
1088: FND_DSQL.add_bind(l_funds_util_flt.reference_type);
1089: END IF;
1090:
1091: IF l_funds_util_flt.reference_id IS NOT NULL THEN

Line 1088: FND_DSQL.add_bind(l_funds_util_flt.reference_type);

1084: END IF;
1085:
1086: IF l_funds_util_flt.reference_type IS NOT NULL THEN
1087: FND_DSQL.add_text(' AND fu.reference_type = ');
1088: FND_DSQL.add_bind(l_funds_util_flt.reference_type);
1089: END IF;
1090:
1091: IF l_funds_util_flt.reference_id IS NOT NULL THEN
1092: FND_DSQL.add_text(' AND fu.reference_id = ');

Line 1092: FND_DSQL.add_text(' AND fu.reference_id = ');

1088: FND_DSQL.add_bind(l_funds_util_flt.reference_type);
1089: END IF;
1090:
1091: IF l_funds_util_flt.reference_id IS NOT NULL THEN
1092: FND_DSQL.add_text(' AND fu.reference_id = ');
1093: FND_DSQL.add_bind(l_funds_util_flt.reference_id);
1094: END IF;
1095:
1096: IF l_funds_util_flt.activity_product_id IS NOT NULL THEN

Line 1093: FND_DSQL.add_bind(l_funds_util_flt.reference_id);

1089: END IF;
1090:
1091: IF l_funds_util_flt.reference_id IS NOT NULL THEN
1092: FND_DSQL.add_text(' AND fu.reference_id = ');
1093: FND_DSQL.add_bind(l_funds_util_flt.reference_id);
1094: END IF;
1095:
1096: IF l_funds_util_flt.activity_product_id IS NOT NULL THEN
1097: FND_DSQL.add_text(' AND fu.activity_product_id = ');

Line 1097: FND_DSQL.add_text(' AND fu.activity_product_id = ');

1093: FND_DSQL.add_bind(l_funds_util_flt.reference_id);
1094: END IF;
1095:
1096: IF l_funds_util_flt.activity_product_id IS NOT NULL THEN
1097: FND_DSQL.add_text(' AND fu.activity_product_id = ');
1098: FND_DSQL.add_bind(l_funds_util_flt.activity_product_id);
1099: END IF;
1100:
1101: IF l_funds_util_flt.schedule_id IS NOT NULL AND l_scan_data_flag = 'N' THEN

Line 1098: FND_DSQL.add_bind(l_funds_util_flt.activity_product_id);

1094: END IF;
1095:
1096: IF l_funds_util_flt.activity_product_id IS NOT NULL THEN
1097: FND_DSQL.add_text(' AND fu.activity_product_id = ');
1098: FND_DSQL.add_bind(l_funds_util_flt.activity_product_id);
1099: END IF;
1100:
1101: IF l_funds_util_flt.schedule_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1102: FND_DSQL.add_text(' AND fu.component_type = ''CSCH'' ');

Line 1102: FND_DSQL.add_text(' AND fu.component_type = ''CSCH'' ');

1098: FND_DSQL.add_bind(l_funds_util_flt.activity_product_id);
1099: END IF;
1100:
1101: IF l_funds_util_flt.schedule_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1102: FND_DSQL.add_text(' AND fu.component_type = ''CSCH'' ');
1103: FND_DSQL.add_text(' AND fu.component_id = ');
1104: FND_DSQL.add_bind(l_funds_util_flt.schedule_id);
1105: END IF;
1106:

Line 1103: FND_DSQL.add_text(' AND fu.component_id = ');

1099: END IF;
1100:
1101: IF l_funds_util_flt.schedule_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1102: FND_DSQL.add_text(' AND fu.component_type = ''CSCH'' ');
1103: FND_DSQL.add_text(' AND fu.component_id = ');
1104: FND_DSQL.add_bind(l_funds_util_flt.schedule_id);
1105: END IF;
1106:
1107: -- fix for 4308165

Line 1104: FND_DSQL.add_bind(l_funds_util_flt.schedule_id);

1100:
1101: IF l_funds_util_flt.schedule_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1102: FND_DSQL.add_text(' AND fu.component_type = ''CSCH'' ');
1103: FND_DSQL.add_text(' AND fu.component_id = ');
1104: FND_DSQL.add_bind(l_funds_util_flt.schedule_id);
1105: END IF;
1106:
1107: -- fix for 4308165
1108: -- modified for bugfix 4990767

Line 1111: FND_DSQL.add_text(' AND fu.object_type = ');

1107: -- fix for 4308165
1108: -- modified for bugfix 4990767
1109: IF l_funds_util_flt.document_class IS NOT NULL AND l_scan_data_flag = 'N' THEN
1110: IF l_funds_util_flt.document_class IN ('ORDER','TP_ORDER') THEN
1111: FND_DSQL.add_text(' AND fu.object_type = ');
1112: FND_DSQL.add_bind(l_funds_util_flt.document_class);
1113: END IF;
1114: END IF;
1115:

Line 1112: FND_DSQL.add_bind(l_funds_util_flt.document_class);

1108: -- modified for bugfix 4990767
1109: IF l_funds_util_flt.document_class IS NOT NULL AND l_scan_data_flag = 'N' THEN
1110: IF l_funds_util_flt.document_class IN ('ORDER','TP_ORDER') THEN
1111: FND_DSQL.add_text(' AND fu.object_type = ');
1112: FND_DSQL.add_bind(l_funds_util_flt.document_class);
1113: END IF;
1114: END IF;
1115:
1116: IF l_funds_util_flt.document_id IS NOT NULL AND l_scan_data_flag = 'N' THEN

Line 1119: --FND_DSQL.add_text(' AND fu.object_id IN (SELECT chargeback_line_id FROM ozf_chargeback_lines WHERE chargeback_header_id = ');

1115:
1116: IF l_funds_util_flt.document_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1117: IF l_funds_util_flt.document_class = 'TP_ORDER' THEN
1118: --//Fix for Bug 14021102
1119: --FND_DSQL.add_text(' AND fu.object_id IN (SELECT chargeback_line_id FROM ozf_chargeback_lines WHERE chargeback_header_id = ');
1120: FND_DSQL.add_text(' AND fu.object_id IN (SELECT resale_line_id FROM ozf_resale_lines WHERE resale_header_id = ');
1121: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1122: FND_DSQL.add_text(') ');
1123: ELSE

Line 1120: FND_DSQL.add_text(' AND fu.object_id IN (SELECT resale_line_id FROM ozf_resale_lines WHERE resale_header_id = ');

1116: IF l_funds_util_flt.document_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1117: IF l_funds_util_flt.document_class = 'TP_ORDER' THEN
1118: --//Fix for Bug 14021102
1119: --FND_DSQL.add_text(' AND fu.object_id IN (SELECT chargeback_line_id FROM ozf_chargeback_lines WHERE chargeback_header_id = ');
1120: FND_DSQL.add_text(' AND fu.object_id IN (SELECT resale_line_id FROM ozf_resale_lines WHERE resale_header_id = ');
1121: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1122: FND_DSQL.add_text(') ');
1123: ELSE
1124: IF l_funds_util_flt.document_class = 'ORDER' THEN

Line 1121: FND_DSQL.add_bind(l_funds_util_flt.document_id);

1117: IF l_funds_util_flt.document_class = 'TP_ORDER' THEN
1118: --//Fix for Bug 14021102
1119: --FND_DSQL.add_text(' AND fu.object_id IN (SELECT chargeback_line_id FROM ozf_chargeback_lines WHERE chargeback_header_id = ');
1120: FND_DSQL.add_text(' AND fu.object_id IN (SELECT resale_line_id FROM ozf_resale_lines WHERE resale_header_id = ');
1121: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1122: FND_DSQL.add_text(') ');
1123: ELSE
1124: IF l_funds_util_flt.document_class = 'ORDER' THEN
1125: FND_DSQL.add_text(' AND fu.object_id = ');

Line 1122: FND_DSQL.add_text(') ');

1118: --//Fix for Bug 14021102
1119: --FND_DSQL.add_text(' AND fu.object_id IN (SELECT chargeback_line_id FROM ozf_chargeback_lines WHERE chargeback_header_id = ');
1120: FND_DSQL.add_text(' AND fu.object_id IN (SELECT resale_line_id FROM ozf_resale_lines WHERE resale_header_id = ');
1121: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1122: FND_DSQL.add_text(') ');
1123: ELSE
1124: IF l_funds_util_flt.document_class = 'ORDER' THEN
1125: FND_DSQL.add_text(' AND fu.object_id = ');
1126: FND_DSQL.add_bind(l_funds_util_flt.document_id);

Line 1125: FND_DSQL.add_text(' AND fu.object_id = ');

1121: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1122: FND_DSQL.add_text(') ');
1123: ELSE
1124: IF l_funds_util_flt.document_class = 'ORDER' THEN
1125: FND_DSQL.add_text(' AND fu.object_id = ');
1126: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1127: END IF;
1128: END IF;
1129: END IF;

Line 1126: FND_DSQL.add_bind(l_funds_util_flt.document_id);

1122: FND_DSQL.add_text(') ');
1123: ELSE
1124: IF l_funds_util_flt.document_class = 'ORDER' THEN
1125: FND_DSQL.add_text(' AND fu.object_id = ');
1126: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1127: END IF;
1128: END IF;
1129: END IF;
1130:

Line 1134: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');

1130:
1131: IF (l_funds_util_flt.product_level_type = 'PRODUCT' OR
1132: l_funds_util_flt.product_level_type IS NULL) AND
1133: l_funds_util_flt.product_id IS NOT NULL THEN
1134: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');
1135: FND_DSQL.add_text(' AND fu.product_id = ');
1136: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1137: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1138: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');

Line 1135: FND_DSQL.add_text(' AND fu.product_id = ');

1131: IF (l_funds_util_flt.product_level_type = 'PRODUCT' OR
1132: l_funds_util_flt.product_level_type IS NULL) AND
1133: l_funds_util_flt.product_id IS NOT NULL THEN
1134: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');
1135: FND_DSQL.add_text(' AND fu.product_id = ');
1136: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1137: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1138: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1139: FND_DSQL.add_text(' where inventory_item_id = ');

Line 1136: FND_DSQL.add_bind(l_funds_util_flt.product_id);

1132: l_funds_util_flt.product_level_type IS NULL) AND
1133: l_funds_util_flt.product_id IS NOT NULL THEN
1134: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');
1135: FND_DSQL.add_text(' AND fu.product_id = ');
1136: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1137: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1138: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1139: FND_DSQL.add_text(' where inventory_item_id = ');
1140: FND_DSQL.add_bind(l_funds_util_flt.product_id);

Line 1137: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');

1133: l_funds_util_flt.product_id IS NOT NULL THEN
1134: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');
1135: FND_DSQL.add_text(' AND fu.product_id = ');
1136: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1137: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1138: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1139: FND_DSQL.add_text(' where inventory_item_id = ');
1140: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1141: FND_DSQL.add_text(' and organization_id = ');

Line 1138: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');

1134: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');
1135: FND_DSQL.add_text(' AND fu.product_id = ');
1136: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1137: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1138: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1139: FND_DSQL.add_text(' where inventory_item_id = ');
1140: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1141: FND_DSQL.add_text(' and organization_id = ');
1142: FND_DSQL.add_bind(l_org_id);

Line 1139: FND_DSQL.add_text(' where inventory_item_id = ');

1135: FND_DSQL.add_text(' AND fu.product_id = ');
1136: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1137: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1138: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1139: FND_DSQL.add_text(' where inventory_item_id = ');
1140: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1141: FND_DSQL.add_text(' and organization_id = ');
1142: FND_DSQL.add_bind(l_org_id);
1143: FND_DSQL.add_text(' ))) ');

Line 1140: FND_DSQL.add_bind(l_funds_util_flt.product_id);

1136: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1137: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1138: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1139: FND_DSQL.add_text(' where inventory_item_id = ');
1140: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1141: FND_DSQL.add_text(' and organization_id = ');
1142: FND_DSQL.add_bind(l_org_id);
1143: FND_DSQL.add_text(' ))) ');
1144: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND

Line 1141: FND_DSQL.add_text(' and organization_id = ');

1137: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1138: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1139: FND_DSQL.add_text(' where inventory_item_id = ');
1140: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1141: FND_DSQL.add_text(' and organization_id = ');
1142: FND_DSQL.add_bind(l_org_id);
1143: FND_DSQL.add_text(' ))) ');
1144: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1145: l_funds_util_flt.product_id IS NOT NULL THEN

Line 1142: FND_DSQL.add_bind(l_org_id);

1138: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1139: FND_DSQL.add_text(' where inventory_item_id = ');
1140: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1141: FND_DSQL.add_text(' and organization_id = ');
1142: FND_DSQL.add_bind(l_org_id);
1143: FND_DSQL.add_text(' ))) ');
1144: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1145: l_funds_util_flt.product_id IS NOT NULL THEN
1146: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');

Line 1143: FND_DSQL.add_text(' ))) ');

1139: FND_DSQL.add_text(' where inventory_item_id = ');
1140: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1141: FND_DSQL.add_text(' and organization_id = ');
1142: FND_DSQL.add_bind(l_org_id);
1143: FND_DSQL.add_text(' ))) ');
1144: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1145: l_funds_util_flt.product_id IS NOT NULL THEN
1146: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1147: FND_DSQL.add_text(' AND fu.product_id = ');

Line 1146: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');

1142: FND_DSQL.add_bind(l_org_id);
1143: FND_DSQL.add_text(' ))) ');
1144: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1145: l_funds_util_flt.product_id IS NOT NULL THEN
1146: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1147: FND_DSQL.add_text(' AND fu.product_id = ');
1148: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1149: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1150: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');

Line 1147: FND_DSQL.add_text(' AND fu.product_id = ');

1143: FND_DSQL.add_text(' ))) ');
1144: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1145: l_funds_util_flt.product_id IS NOT NULL THEN
1146: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1147: FND_DSQL.add_text(' AND fu.product_id = ');
1148: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1149: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1150: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1151: FND_DSQL.add_text(' where a.parent_id = ');

Line 1148: FND_DSQL.add_bind(l_funds_util_flt.product_id);

1144: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1145: l_funds_util_flt.product_id IS NOT NULL THEN
1146: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1147: FND_DSQL.add_text(' AND fu.product_id = ');
1148: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1149: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1150: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1151: FND_DSQL.add_text(' where a.parent_id = ');
1152: FND_DSQL.add_text(l_funds_util_flt.product_id);

Line 1149: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');

1145: l_funds_util_flt.product_id IS NOT NULL THEN
1146: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1147: FND_DSQL.add_text(' AND fu.product_id = ');
1148: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1149: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1150: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1151: FND_DSQL.add_text(' where a.parent_id = ');
1152: FND_DSQL.add_text(l_funds_util_flt.product_id);
1153: FND_DSQL.add_text(' and b.organization_id = ');

Line 1150: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');

1146: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1147: FND_DSQL.add_text(' AND fu.product_id = ');
1148: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1149: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1150: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1151: FND_DSQL.add_text(' where a.parent_id = ');
1152: FND_DSQL.add_text(l_funds_util_flt.product_id);
1153: FND_DSQL.add_text(' and b.organization_id = ');
1154: FND_DSQL.add_bind(l_org_id);

Line 1151: FND_DSQL.add_text(' where a.parent_id = ');

1147: FND_DSQL.add_text(' AND fu.product_id = ');
1148: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1149: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1150: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1151: FND_DSQL.add_text(' where a.parent_id = ');
1152: FND_DSQL.add_text(l_funds_util_flt.product_id);
1153: FND_DSQL.add_text(' and b.organization_id = ');
1154: FND_DSQL.add_bind(l_org_id);
1155: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');

Line 1152: FND_DSQL.add_text(l_funds_util_flt.product_id);

1148: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1149: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1150: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1151: FND_DSQL.add_text(' where a.parent_id = ');
1152: FND_DSQL.add_text(l_funds_util_flt.product_id);
1153: FND_DSQL.add_text(' and b.organization_id = ');
1154: FND_DSQL.add_bind(l_org_id);
1155: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1156: FND_DSQL.add_text(' ))) ');

Line 1153: FND_DSQL.add_text(' and b.organization_id = ');

1149: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1150: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1151: FND_DSQL.add_text(' where a.parent_id = ');
1152: FND_DSQL.add_text(l_funds_util_flt.product_id);
1153: FND_DSQL.add_text(' and b.organization_id = ');
1154: FND_DSQL.add_bind(l_org_id);
1155: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1156: FND_DSQL.add_text(' ))) ');
1157: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN

Line 1154: FND_DSQL.add_bind(l_org_id);

1150: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1151: FND_DSQL.add_text(' where a.parent_id = ');
1152: FND_DSQL.add_text(l_funds_util_flt.product_id);
1153: FND_DSQL.add_text(' and b.organization_id = ');
1154: FND_DSQL.add_bind(l_org_id);
1155: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1156: FND_DSQL.add_text(' ))) ');
1157: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1158: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');

Line 1155: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');

1151: FND_DSQL.add_text(' where a.parent_id = ');
1152: FND_DSQL.add_text(l_funds_util_flt.product_id);
1153: FND_DSQL.add_text(' and b.organization_id = ');
1154: FND_DSQL.add_bind(l_org_id);
1155: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1156: FND_DSQL.add_text(' ))) ');
1157: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1158: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1159: IF l_funds_util_flt.product_id IS NOT NULL THEN

Line 1156: FND_DSQL.add_text(' ))) ');

1152: FND_DSQL.add_text(l_funds_util_flt.product_id);
1153: FND_DSQL.add_text(' and b.organization_id = ');
1154: FND_DSQL.add_bind(l_org_id);
1155: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1156: FND_DSQL.add_text(' ))) ');
1157: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1158: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1159: IF l_funds_util_flt.product_id IS NOT NULL THEN
1160: FND_DSQL.add_text(' AND fu.product_id = ');

Line 1158: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');

1154: FND_DSQL.add_bind(l_org_id);
1155: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1156: FND_DSQL.add_text(' ))) ');
1157: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1158: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1159: IF l_funds_util_flt.product_id IS NOT NULL THEN
1160: FND_DSQL.add_text(' AND fu.product_id = ');
1161: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1162: END IF;

Line 1160: FND_DSQL.add_text(' AND fu.product_id = ');

1156: FND_DSQL.add_text(' ))) ');
1157: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1158: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1159: IF l_funds_util_flt.product_id IS NOT NULL THEN
1160: FND_DSQL.add_text(' AND fu.product_id = ');
1161: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1162: END IF;
1163: END IF;
1164:

Line 1161: FND_DSQL.add_bind(l_funds_util_flt.product_id);

1157: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1158: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1159: IF l_funds_util_flt.product_id IS NOT NULL THEN
1160: FND_DSQL.add_text(' AND fu.product_id = ');
1161: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1162: END IF;
1163: END IF;
1164:
1165: -- Fix for Bug 11793070

Line 1167: FND_DSQL.add_text(' AND trunc(fu.gl_date) <= ');

1163: END IF;
1164:
1165: -- Fix for Bug 11793070
1166: IF l_funds_util_flt.end_date IS NOT NULL THEN
1167: FND_DSQL.add_text(' AND trunc(fu.gl_date) <= ');
1168: FND_DSQL.add_bind(l_funds_util_flt.end_date);
1169: END IF;
1170:
1171: -- Fix for Bug 11793070

Line 1168: FND_DSQL.add_bind(l_funds_util_flt.end_date);

1164:
1165: -- Fix for Bug 11793070
1166: IF l_funds_util_flt.end_date IS NOT NULL THEN
1167: FND_DSQL.add_text(' AND trunc(fu.gl_date) <= ');
1168: FND_DSQL.add_bind(l_funds_util_flt.end_date);
1169: END IF;
1170:
1171: -- Fix for Bug 11793070
1172: IF(l_funds_util_flt.period_name IS NOT NULL)THEN

Line 1173: FND_DSQL.add_text(' AND (select start_date, end_date from ozf_time_ent_qtr where period_name = ');

1169: END IF;
1170:
1171: -- Fix for Bug 11793070
1172: IF(l_funds_util_flt.period_name IS NOT NULL)THEN
1173: FND_DSQL.add_text(' AND (select start_date, end_date from ozf_time_ent_qtr where period_name = ');
1174: FND_DSQL.add_text(' AND fu.gl_date between start_date and end_date) ');
1175: FND_DSQL.add_bind(l_funds_util_flt.period_name);
1176: END IF;
1177:

Line 1174: FND_DSQL.add_text(' AND fu.gl_date between start_date and end_date) ');

1170:
1171: -- Fix for Bug 11793070
1172: IF(l_funds_util_flt.period_name IS NOT NULL)THEN
1173: FND_DSQL.add_text(' AND (select start_date, end_date from ozf_time_ent_qtr where period_name = ');
1174: FND_DSQL.add_text(' AND fu.gl_date between start_date and end_date) ');
1175: FND_DSQL.add_bind(l_funds_util_flt.period_name);
1176: END IF;
1177:
1178: IF(l_funds_util_flt.year_name IS NOT NULL) THEN

Line 1175: FND_DSQL.add_bind(l_funds_util_flt.period_name);

1171: -- Fix for Bug 11793070
1172: IF(l_funds_util_flt.period_name IS NOT NULL)THEN
1173: FND_DSQL.add_text(' AND (select start_date, end_date from ozf_time_ent_qtr where period_name = ');
1174: FND_DSQL.add_text(' AND fu.gl_date between start_date and end_date) ');
1175: FND_DSQL.add_bind(l_funds_util_flt.period_name);
1176: END IF;
1177:
1178: IF(l_funds_util_flt.year_name IS NOT NULL) THEN
1179: if (l_funds_util_flt.year_name = 'CURRENT') then

Line 1180: FND_DSQL.add_text(' AND fu.year_id = SELECT ent_year_id FROM OZF_TIME_ENT_YEAR WHERE fu.gl_date between start_date and end_date ');

1176: END IF;
1177:
1178: IF(l_funds_util_flt.year_name IS NOT NULL) THEN
1179: if (l_funds_util_flt.year_name = 'CURRENT') then
1180: FND_DSQL.add_text(' AND fu.year_id = SELECT ent_year_id FROM OZF_TIME_ENT_YEAR WHERE fu.gl_date between start_date and end_date ');
1181: elsif(l_funds_util_flt.year_name = 'PRIOR') then
1182: FND_DSQL.add_text(' AND fu.year_id = ((SELECT ent_year_id FROM OZF_TIME_ENT_YEAR WHERE fu.gl_date between start_date and end_date) - 1) ');
1183: end if;
1184: END IF;

Line 1182: FND_DSQL.add_text(' AND fu.year_id = ((SELECT ent_year_id FROM OZF_TIME_ENT_YEAR WHERE fu.gl_date between start_date and end_date) - 1) ');

1178: IF(l_funds_util_flt.year_name IS NOT NULL) THEN
1179: if (l_funds_util_flt.year_name = 'CURRENT') then
1180: FND_DSQL.add_text(' AND fu.year_id = SELECT ent_year_id FROM OZF_TIME_ENT_YEAR WHERE fu.gl_date between start_date and end_date ');
1181: elsif(l_funds_util_flt.year_name = 'PRIOR') then
1182: FND_DSQL.add_text(' AND fu.year_id = ((SELECT ent_year_id FROM OZF_TIME_ENT_YEAR WHERE fu.gl_date between start_date and end_date) - 1) ');
1183: end if;
1184: END IF;
1185: --end Fix for Bug 11793070
1186:

Line 1189: FND_DSQL.add_text(' AND fu.utilization_id = ');

1185: --end Fix for Bug 11793070
1186:
1187: -- Fix for Bug 8402328
1188: IF (l_funds_util_flt.utilization_id IS NOT NULL) THEN
1189: FND_DSQL.add_text(' AND fu.utilization_id = ');
1190: FND_DSQL.add_bind(l_funds_util_flt.utilization_id );
1191: END IF;
1192:
1193: FND_DSQL.add_text(' AND fu.gl_posted_flag = ''Y'' ');

Line 1190: FND_DSQL.add_bind(l_funds_util_flt.utilization_id );

1186:
1187: -- Fix for Bug 8402328
1188: IF (l_funds_util_flt.utilization_id IS NOT NULL) THEN
1189: FND_DSQL.add_text(' AND fu.utilization_id = ');
1190: FND_DSQL.add_bind(l_funds_util_flt.utilization_id );
1191: END IF;
1192:
1193: FND_DSQL.add_text(' AND fu.gl_posted_flag = ''Y'' ');
1194:

Line 1193: FND_DSQL.add_text(' AND fu.gl_posted_flag = ''Y'' ');

1189: FND_DSQL.add_text(' AND fu.utilization_id = ');
1190: FND_DSQL.add_bind(l_funds_util_flt.utilization_id );
1191: END IF;
1192:
1193: FND_DSQL.add_text(' AND fu.gl_posted_flag = ''Y'' ');
1194:
1195: END Get_Utiz_Sql_Stmt_Where_Clause;
1196:
1197: ---------------------------------------------------------------------

Line 1281: FND_DSQL.add_text('SELECT fu.utilization_id, fu.cust_account_id '||

1277: l_sales_rep := ozf_utility_pvt.has_sales_rep_role(l_resource_id);
1278: END IF;
1279:
1280:
1281: FND_DSQL.add_text('SELECT fu.utilization_id, fu.cust_account_id '||
1282: ', fu.plan_type, fu.plan_id, o.offer_type, o.autopay_method '||
1283: ', fu.product_level_type, fu.product_id, fu.acctd_amount_remaining, ');
1284:
1285: -- Added For Multi Currency - kpatro

Line 1287: FND_DSQL.add_text( 'fu.plan_curr_amount_remaining amount_remaining, ');

1283: ', fu.product_level_type, fu.product_id, fu.acctd_amount_remaining, ');
1284:
1285: -- Added For Multi Currency - kpatro
1286: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY') THEN
1287: FND_DSQL.add_text( 'fu.plan_curr_amount_remaining amount_remaining, ');
1288: ELSE
1289: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining) amount_remaining, ');
1290: END IF;
1291:

Line 1289: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining) amount_remaining, ');

1285: -- Added For Multi Currency - kpatro
1286: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY') THEN
1287: FND_DSQL.add_text( 'fu.plan_curr_amount_remaining amount_remaining, ');
1288: ELSE
1289: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining) amount_remaining, ');
1290: END IF;
1291:
1292: FND_DSQL.add_text('fu.scan_unit_remaining , fu.creation_date, ');
1293:

Line 1292: FND_DSQL.add_text('fu.scan_unit_remaining , fu.creation_date, ');

1288: ELSE
1289: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining) amount_remaining, ');
1290: END IF;
1291:
1292: FND_DSQL.add_text('fu.scan_unit_remaining , fu.creation_date, ');
1293:
1294: -- Added For Multi Currency - kpatro
1295: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY') THEN
1296: FND_DSQL.add_text( 'fu.PLAN_CURRENCY_CODE currency_code, ');

Line 1296: FND_DSQL.add_text( 'fu.PLAN_CURRENCY_CODE currency_code, ');

1292: FND_DSQL.add_text('fu.scan_unit_remaining , fu.creation_date, ');
1293:
1294: -- Added For Multi Currency - kpatro
1295: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY') THEN
1296: FND_DSQL.add_text( 'fu.PLAN_CURRENCY_CODE currency_code, ');
1297: ELSE
1298: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.PLAN_CURRENCY_CODE), fu.PLAN_CURRENCY_CODE, fu.PLAN_CURRENCY_CODE, '''||l_currency_rec.functional_currency_code||''') currency_code, ');
1299: END IF;
1300:

Line 1298: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.PLAN_CURRENCY_CODE), fu.PLAN_CURRENCY_CODE, fu.PLAN_CURRENCY_CODE, '''||l_currency_rec.functional_currency_code||''') currency_code, ');

1294: -- Added For Multi Currency - kpatro
1295: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY') THEN
1296: FND_DSQL.add_text( 'fu.PLAN_CURRENCY_CODE currency_code, ');
1297: ELSE
1298: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.PLAN_CURRENCY_CODE), fu.PLAN_CURRENCY_CODE, fu.PLAN_CURRENCY_CODE, '''||l_currency_rec.functional_currency_code||''') currency_code, ');
1299: END IF;
1300:
1301: FND_DSQL.add_text( 'fu.bill_to_site_use_id, fu.plan_curr_amount, fu.plan_curr_amount_remaining, fu.plan_currency_code ' || --ninarasi fix for bug 13550004
1302: 'FROM ozf_funds_utilized_all_b fu, ozf_offers o ');

Line 1301: FND_DSQL.add_text( 'fu.bill_to_site_use_id, fu.plan_curr_amount, fu.plan_curr_amount_remaining, fu.plan_currency_code ' || --ninarasi fix for bug 13550004

1297: ELSE
1298: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.PLAN_CURRENCY_CODE), fu.PLAN_CURRENCY_CODE, fu.PLAN_CURRENCY_CODE, '''||l_currency_rec.functional_currency_code||''') currency_code, ');
1299: END IF;
1300:
1301: FND_DSQL.add_text( 'fu.bill_to_site_use_id, fu.plan_curr_amount, fu.plan_curr_amount_remaining, fu.plan_currency_code ' || --ninarasi fix for bug 13550004
1302: 'FROM ozf_funds_utilized_all_b fu, ozf_offers o ');
1303: --Fix for Bug 11793070
1304: IF(l_funds_util_flt.period_name IS NOT NULL)THEN
1305: FND_DSQL.add_text( ', OZF_TIME_ENT_QTR teq ');

Line 1305: FND_DSQL.add_text( ', OZF_TIME_ENT_QTR teq ');

1301: FND_DSQL.add_text( 'fu.bill_to_site_use_id, fu.plan_curr_amount, fu.plan_curr_amount_remaining, fu.plan_currency_code ' || --ninarasi fix for bug 13550004
1302: 'FROM ozf_funds_utilized_all_b fu, ozf_offers o ');
1303: --Fix for Bug 11793070
1304: IF(l_funds_util_flt.period_name IS NOT NULL)THEN
1305: FND_DSQL.add_text( ', OZF_TIME_ENT_QTR teq ');
1306: END IF;
1307:
1308: --Modified for Bugfix 5346249
1309: FND_DSQL.add_text('WHERE fu.plan_type = ''OFFR'' '||

Line 1309: FND_DSQL.add_text('WHERE fu.plan_type = ''OFFR'' '||

1305: FND_DSQL.add_text( ', OZF_TIME_ENT_QTR teq ');
1306: END IF;
1307:
1308: --Modified for Bugfix 5346249
1309: FND_DSQL.add_text('WHERE fu.plan_type = ''OFFR'' '||
1310: 'AND fu.plan_id = o.qp_list_header_id ' ||
1311: 'AND fu.org_id = ');
1312: FND_DSQL.add_bind(l_orgId);
1313:

Line 1312: FND_DSQL.add_bind(l_orgId);

1308: --Modified for Bugfix 5346249
1309: FND_DSQL.add_text('WHERE fu.plan_type = ''OFFR'' '||
1310: 'AND fu.plan_id = o.qp_list_header_id ' ||
1311: 'AND fu.org_id = ');
1312: FND_DSQL.add_bind(l_orgId);
1313:
1314: IF l_funds_util_flt.offer_type IS NOT NULL THEN
1315: FND_DSQL.add_text(' AND o.offer_type = ');
1316: FND_DSQL.add_bind(l_funds_util_flt.offer_type);

Line 1315: FND_DSQL.add_text(' AND o.offer_type = ');

1311: 'AND fu.org_id = ');
1312: FND_DSQL.add_bind(l_orgId);
1313:
1314: IF l_funds_util_flt.offer_type IS NOT NULL THEN
1315: FND_DSQL.add_text(' AND o.offer_type = ');
1316: FND_DSQL.add_bind(l_funds_util_flt.offer_type);
1317: ELSE
1318: FND_DSQL.add_text(' AND o.offer_type <> ''SCAN_DATA'' ');
1319: END IF;

Line 1316: FND_DSQL.add_bind(l_funds_util_flt.offer_type);

1312: FND_DSQL.add_bind(l_orgId);
1313:
1314: IF l_funds_util_flt.offer_type IS NOT NULL THEN
1315: FND_DSQL.add_text(' AND o.offer_type = ');
1316: FND_DSQL.add_bind(l_funds_util_flt.offer_type);
1317: ELSE
1318: FND_DSQL.add_text(' AND o.offer_type <> ''SCAN_DATA'' ');
1319: END IF;
1320:

Line 1318: FND_DSQL.add_text(' AND o.offer_type <> ''SCAN_DATA'' ');

1314: IF l_funds_util_flt.offer_type IS NOT NULL THEN
1315: FND_DSQL.add_text(' AND o.offer_type = ');
1316: FND_DSQL.add_bind(l_funds_util_flt.offer_type);
1317: ELSE
1318: FND_DSQL.add_text(' AND o.offer_type <> ''SCAN_DATA'' ');
1319: END IF;
1320:
1321: IF l_funds_util_flt.run_mode = 'OFFER_AUTOPAY' THEN
1322: FND_DSQL.add_text(' AND o.autopay_flag = ''Y'' ');

Line 1322: FND_DSQL.add_text(' AND o.autopay_flag = ''Y'' ');

1318: FND_DSQL.add_text(' AND o.offer_type <> ''SCAN_DATA'' ');
1319: END IF;
1320:
1321: IF l_funds_util_flt.run_mode = 'OFFER_AUTOPAY' THEN
1322: FND_DSQL.add_text(' AND o.autopay_flag = ''Y'' ');
1323: ELSIF l_funds_util_flt.run_mode = 'OFFER_NO_AUTOPAY' THEN
1324: FND_DSQL.add_text(' AND (o.autopay_flag IS NULL OR o.autopay_flag = ''N'') ');
1325: END IF;
1326:

Line 1324: FND_DSQL.add_text(' AND (o.autopay_flag IS NULL OR o.autopay_flag = ''N'') ');

1320:
1321: IF l_funds_util_flt.run_mode = 'OFFER_AUTOPAY' THEN
1322: FND_DSQL.add_text(' AND o.autopay_flag = ''Y'' ');
1323: ELSIF l_funds_util_flt.run_mode = 'OFFER_NO_AUTOPAY' THEN
1324: FND_DSQL.add_text(' AND (o.autopay_flag IS NULL OR o.autopay_flag = ''N'') ');
1325: END IF;
1326:
1327: IF l_funds_util_flt.offer_payment_method IS NOT NULL THEN
1328: IF l_funds_util_flt.offer_payment_method = 'NULL' THEN

Line 1329: FND_DSQL.add_text(' AND o.autopay_method IS NULL ');

1325: END IF;
1326:
1327: IF l_funds_util_flt.offer_payment_method IS NOT NULL THEN
1328: IF l_funds_util_flt.offer_payment_method = 'NULL' THEN
1329: FND_DSQL.add_text(' AND o.autopay_method IS NULL ');
1330: ELSE
1331: FND_DSQL.add_text(' AND o.autopay_method = ');
1332: FND_DSQL.add_bind(l_funds_util_flt.offer_payment_method);
1333: END IF;

Line 1331: FND_DSQL.add_text(' AND o.autopay_method = ');

1327: IF l_funds_util_flt.offer_payment_method IS NOT NULL THEN
1328: IF l_funds_util_flt.offer_payment_method = 'NULL' THEN
1329: FND_DSQL.add_text(' AND o.autopay_method IS NULL ');
1330: ELSE
1331: FND_DSQL.add_text(' AND o.autopay_method = ');
1332: FND_DSQL.add_bind(l_funds_util_flt.offer_payment_method);
1333: END IF;
1334: END IF;
1335:

Line 1332: FND_DSQL.add_bind(l_funds_util_flt.offer_payment_method);

1328: IF l_funds_util_flt.offer_payment_method = 'NULL' THEN
1329: FND_DSQL.add_text(' AND o.autopay_method IS NULL ');
1330: ELSE
1331: FND_DSQL.add_text(' AND o.autopay_method = ');
1332: FND_DSQL.add_bind(l_funds_util_flt.offer_payment_method);
1333: END IF;
1334: END IF;
1335:
1336: IF (l_sales_rep = FND_API.g_true AND NOT l_is_admin ) THEN

Line 1337: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');

1333: END IF;
1334: END IF;
1335:
1336: IF (l_sales_rep = FND_API.g_true AND NOT l_is_admin ) THEN
1337: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');
1338: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1339: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1340: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1341: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');

Line 1338: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');

1334: END IF;
1335:
1336: IF (l_sales_rep = FND_API.g_true AND NOT l_is_admin ) THEN
1337: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');
1338: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1339: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1340: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1341: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1342: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');

Line 1339: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');

1335:
1336: IF (l_sales_rep = FND_API.g_true AND NOT l_is_admin ) THEN
1337: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');
1338: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1339: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1340: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1341: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1342: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1343: FND_DSQL.add_text(' AND act.resource_id= ');

Line 1340: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');

1336: IF (l_sales_rep = FND_API.g_true AND NOT l_is_admin ) THEN
1337: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');
1338: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1339: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1340: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1341: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1342: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1343: FND_DSQL.add_text(' AND act.resource_id= ');
1344: FND_DSQL.add_bind(l_resource_id);

Line 1341: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');

1337: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');
1338: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1339: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1340: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1341: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1342: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1343: FND_DSQL.add_text(' AND act.resource_id= ');
1344: FND_DSQL.add_bind(l_resource_id);
1345: FND_DSQL.add_text('))');

Line 1342: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');

1338: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1339: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1340: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1341: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1342: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1343: FND_DSQL.add_text(' AND act.resource_id= ');
1344: FND_DSQL.add_bind(l_resource_id);
1345: FND_DSQL.add_text('))');
1346: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');

Line 1343: FND_DSQL.add_text(' AND act.resource_id= ');

1339: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1340: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1341: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1342: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1343: FND_DSQL.add_text(' AND act.resource_id= ');
1344: FND_DSQL.add_bind(l_resource_id);
1345: FND_DSQL.add_text('))');
1346: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1347: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');

Line 1344: FND_DSQL.add_bind(l_resource_id);

1340: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1341: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1342: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1343: FND_DSQL.add_text(' AND act.resource_id= ');
1344: FND_DSQL.add_bind(l_resource_id);
1345: FND_DSQL.add_text('))');
1346: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1347: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1348: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');

Line 1345: FND_DSQL.add_text('))');

1341: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1342: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1343: FND_DSQL.add_text(' AND act.resource_id= ');
1344: FND_DSQL.add_bind(l_resource_id);
1345: FND_DSQL.add_text('))');
1346: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1347: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1348: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1349: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');

Line 1346: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');

1342: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1343: FND_DSQL.add_text(' AND act.resource_id= ');
1344: FND_DSQL.add_bind(l_resource_id);
1345: FND_DSQL.add_text('))');
1346: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1347: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1348: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1349: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1350: FND_DSQL.add_text(' AND act.resource_id= ' );

Line 1347: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');

1343: FND_DSQL.add_text(' AND act.resource_id= ');
1344: FND_DSQL.add_bind(l_resource_id);
1345: FND_DSQL.add_text('))');
1346: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1347: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1348: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1349: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1350: FND_DSQL.add_text(' AND act.resource_id= ' );
1351: FND_DSQL.add_bind(l_resource_id);

Line 1348: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');

1344: FND_DSQL.add_bind(l_resource_id);
1345: FND_DSQL.add_text('))');
1346: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1347: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1348: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1349: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1350: FND_DSQL.add_text(' AND act.resource_id= ' );
1351: FND_DSQL.add_bind(l_resource_id);
1352: FND_DSQL.add_text(')))');

Line 1349: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');

1345: FND_DSQL.add_text('))');
1346: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1347: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1348: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1349: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1350: FND_DSQL.add_text(' AND act.resource_id= ' );
1351: FND_DSQL.add_bind(l_resource_id);
1352: FND_DSQL.add_text(')))');
1353: END IF;

Line 1350: FND_DSQL.add_text(' AND act.resource_id= ' );

1346: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1347: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1348: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1349: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1350: FND_DSQL.add_text(' AND act.resource_id= ' );
1351: FND_DSQL.add_bind(l_resource_id);
1352: FND_DSQL.add_text(')))');
1353: END IF;
1354:

Line 1351: FND_DSQL.add_bind(l_resource_id);

1347: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1348: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1349: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1350: FND_DSQL.add_text(' AND act.resource_id= ' );
1351: FND_DSQL.add_bind(l_resource_id);
1352: FND_DSQL.add_text(')))');
1353: END IF;
1354:
1355: Get_Utiz_Sql_Stmt_Where_Clause (

Line 1352: FND_DSQL.add_text(')))');

1348: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1349: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1350: FND_DSQL.add_text(' AND act.resource_id= ' );
1351: FND_DSQL.add_bind(l_resource_id);
1352: FND_DSQL.add_text(')))');
1353: END IF;
1354:
1355: Get_Utiz_Sql_Stmt_Where_Clause (
1356: p_summary_view => p_summary_view

Line 1364: FND_DSQL.add_text('UNION ALL ');

1360:
1361: END IF;
1362:
1363: IF l_offer_flag = 'Y' AND l_price_list_flag = 'Y' THEN
1364: FND_DSQL.add_text('UNION ALL ');
1365: END IF;
1366:
1367: IF l_price_list_flag = 'Y' THEN
1368: FND_DSQL.add_text('SELECT fu.utilization_id, fu.cust_account_id '||

Line 1368: FND_DSQL.add_text('SELECT fu.utilization_id, fu.cust_account_id '||

1364: FND_DSQL.add_text('UNION ALL ');
1365: END IF;
1366:
1367: IF l_price_list_flag = 'Y' THEN
1368: FND_DSQL.add_text('SELECT fu.utilization_id, fu.cust_account_id '||
1369: ', fu.plan_type, fu.plan_id, null, null '||
1370: ', fu.product_level_type, fu.product_id '||
1371: ', fu.acctd_amount_remaining,' );
1372: -- Added For Multii Currency - kpatro

Line 1374: FND_DSQL.add_text( 'fu.plan_curr_amount_remaining amount_remaining, ');

1370: ', fu.product_level_type, fu.product_id '||
1371: ', fu.acctd_amount_remaining,' );
1372: -- Added For Multii Currency - kpatro
1373: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY' ) THEN
1374: FND_DSQL.add_text( 'fu.plan_curr_amount_remaining amount_remaining, ');
1375: ELSE
1376: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining) amount_remaining, ');
1377: END IF;
1378:

Line 1376: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining) amount_remaining, ');

1372: -- Added For Multii Currency - kpatro
1373: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY' ) THEN
1374: FND_DSQL.add_text( 'fu.plan_curr_amount_remaining amount_remaining, ');
1375: ELSE
1376: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining) amount_remaining, ');
1377: END IF;
1378:
1379: FND_DSQL.add_text('fu.scan_unit_remaining , fu.creation_date, ');
1380:

Line 1379: FND_DSQL.add_text('fu.scan_unit_remaining , fu.creation_date, ');

1375: ELSE
1376: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.plan_currency_code), fu.plan_currency_code, fu.plan_curr_amount_remaining, fu.acctd_amount_remaining) amount_remaining, ');
1377: END IF;
1378:
1379: FND_DSQL.add_text('fu.scan_unit_remaining , fu.creation_date, ');
1380:
1381: -- Added For Multii Currency - kpatro
1382: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY') THEN
1383: FND_DSQL.add_text( 'fu.PLAN_CURRENCY_CODE currency_code, ');

Line 1383: FND_DSQL.add_text( 'fu.PLAN_CURRENCY_CODE currency_code, ');

1379: FND_DSQL.add_text('fu.scan_unit_remaining , fu.creation_date, ');
1380:
1381: -- Added For Multii Currency - kpatro
1382: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY') THEN
1383: FND_DSQL.add_text( 'fu.PLAN_CURRENCY_CODE currency_code, ');
1384: ELSE
1385: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.PLAN_CURRENCY_CODE), fu.PLAN_CURRENCY_CODE, fu.PLAN_CURRENCY_CODE, '''||l_currency_rec.functional_currency_code||''') currency_code, ');
1386: END IF;
1387:

Line 1385: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.PLAN_CURRENCY_CODE), fu.PLAN_CURRENCY_CODE, fu.PLAN_CURRENCY_CODE, '''||l_currency_rec.functional_currency_code||''') currency_code, ');

1381: -- Added For Multii Currency - kpatro
1382: IF (p_summary_view IN ('AUTOPAY','AUTOPAY_LINE') AND l_funds_util_flt.autopay_check = 'AUTOPAY') THEN
1383: FND_DSQL.add_text( 'fu.PLAN_CURRENCY_CODE currency_code, ');
1384: ELSE
1385: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.PLAN_CURRENCY_CODE), fu.PLAN_CURRENCY_CODE, fu.PLAN_CURRENCY_CODE, '''||l_currency_rec.functional_currency_code||''') currency_code, ');
1386: END IF;
1387:
1388: FND_DSQL.add_text('fu.bill_to_site_use_id , fu.plan_curr_amount, fu.plan_curr_amount_remaining, fu.plan_currency_code' ||
1389: ' FROM ozf_funds_utilized_all_b fu ');

Line 1388: FND_DSQL.add_text('fu.bill_to_site_use_id , fu.plan_curr_amount, fu.plan_curr_amount_remaining, fu.plan_currency_code' ||

1384: ELSE
1385: FND_DSQL.add_text('DECODE(NVL('''||l_currency_rec.claim_currency_code||''',fu.PLAN_CURRENCY_CODE), fu.PLAN_CURRENCY_CODE, fu.PLAN_CURRENCY_CODE, '''||l_currency_rec.functional_currency_code||''') currency_code, ');
1386: END IF;
1387:
1388: FND_DSQL.add_text('fu.bill_to_site_use_id , fu.plan_curr_amount, fu.plan_curr_amount_remaining, fu.plan_currency_code' ||
1389: ' FROM ozf_funds_utilized_all_b fu ');
1390:
1391: --Fix for Bug 11793070
1392: IF(l_funds_util_flt.period_name IS NOT NULL)THEN

Line 1393: FND_DSQL.add_text( ', OZF_TIME_ENT_QTR teq ');

1389: ' FROM ozf_funds_utilized_all_b fu ');
1390:
1391: --Fix for Bug 11793070
1392: IF(l_funds_util_flt.period_name IS NOT NULL)THEN
1393: FND_DSQL.add_text( ', OZF_TIME_ENT_QTR teq ');
1394: END IF;
1395: FND_DSQL.add_text('WHERE fu.plan_type = ''PRIC'' ' ||
1396: 'AND fu.org_id =');
1397: FND_DSQL.add_bind(l_orgId);

Line 1395: FND_DSQL.add_text('WHERE fu.plan_type = ''PRIC'' ' ||

1391: --Fix for Bug 11793070
1392: IF(l_funds_util_flt.period_name IS NOT NULL)THEN
1393: FND_DSQL.add_text( ', OZF_TIME_ENT_QTR teq ');
1394: END IF;
1395: FND_DSQL.add_text('WHERE fu.plan_type = ''PRIC'' ' ||
1396: 'AND fu.org_id =');
1397: FND_DSQL.add_bind(l_orgId);
1398: FND_DSQL.add_text(' AND fu.cust_account_id = ');
1399: FND_DSQL.add_bind(l_funds_util_flt.cust_account_id);

Line 1397: FND_DSQL.add_bind(l_orgId);

1393: FND_DSQL.add_text( ', OZF_TIME_ENT_QTR teq ');
1394: END IF;
1395: FND_DSQL.add_text('WHERE fu.plan_type = ''PRIC'' ' ||
1396: 'AND fu.org_id =');
1397: FND_DSQL.add_bind(l_orgId);
1398: FND_DSQL.add_text(' AND fu.cust_account_id = ');
1399: FND_DSQL.add_bind(l_funds_util_flt.cust_account_id);
1400:
1401: Get_Utiz_Sql_Stmt_Where_Clause(

Line 1398: FND_DSQL.add_text(' AND fu.cust_account_id = ');

1394: END IF;
1395: FND_DSQL.add_text('WHERE fu.plan_type = ''PRIC'' ' ||
1396: 'AND fu.org_id =');
1397: FND_DSQL.add_bind(l_orgId);
1398: FND_DSQL.add_text(' AND fu.cust_account_id = ');
1399: FND_DSQL.add_bind(l_funds_util_flt.cust_account_id);
1400:
1401: Get_Utiz_Sql_Stmt_Where_Clause(
1402: p_summary_view => p_summary_view

Line 1399: FND_DSQL.add_bind(l_funds_util_flt.cust_account_id);

1395: FND_DSQL.add_text('WHERE fu.plan_type = ''PRIC'' ' ||
1396: 'AND fu.org_id =');
1397: FND_DSQL.add_bind(l_orgId);
1398: FND_DSQL.add_text(' AND fu.cust_account_id = ');
1399: FND_DSQL.add_bind(l_funds_util_flt.cust_account_id);
1400:
1401: Get_Utiz_Sql_Stmt_Where_Clause(
1402: p_summary_view => p_summary_view
1403: ,p_funds_util_flt => l_funds_util_flt

Line 1541: -- use FND_DSQL package to handle dynamic sql and bind variables

1537: OZF_Utility_PVT.debug_message('l_currency_rec.association_currency_code :' || l_currency_rec.association_currency_code);
1538: OZF_Utility_PVT.debug_message('----------------------------');
1539: END IF;
1540:
1541: -- use FND_DSQL package to handle dynamic sql and bind variables
1542: FND_DSQL.init;
1543:
1544: IF p_funds_util_flt.offer_type = 'SCAN_DATA' THEN
1545: l_scan_data_flag := 'Y';

Line 1542: FND_DSQL.init;

1538: OZF_Utility_PVT.debug_message('----------------------------');
1539: END IF;
1540:
1541: -- use FND_DSQL package to handle dynamic sql and bind variables
1542: FND_DSQL.init;
1543:
1544: IF p_funds_util_flt.offer_type = 'SCAN_DATA' THEN
1545: l_scan_data_flag := 'Y';
1546: END IF;

Line 1557: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code, bill_to_site_use_id ');

1553: -- Also need to group by currency as amount of different currencies cannot be added
1554: -- Modified for R12.1 enhancements, Need to select the value of bill_to_site_use_id.
1555:
1556: IF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1557: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code, bill_to_site_use_id ');
1558: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1559: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id ');
1560: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1561: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');

Line 1559: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id ');

1555:
1556: IF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1557: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code, bill_to_site_use_id ');
1558: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1559: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id ');
1560: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1561: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');
1562: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1563: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');

Line 1561: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');

1557: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code, bill_to_site_use_id ');
1558: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1559: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id ');
1560: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1561: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');
1562: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1563: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');
1564: END IF;
1565: FND_DSQL.add_text( 'FROM (');

Line 1563: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');

1559: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id ');
1560: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1561: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');
1562: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1563: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');
1564: END IF;
1565: FND_DSQL.add_text( 'FROM (');
1566: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1567: FND_DSQL.add_text( 'SELECT cust_account_id, plan_type, plan_id, bill_to_site_use_id '||

Line 1565: FND_DSQL.add_text( 'FROM (');

1561: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');
1562: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1563: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');
1564: END IF;
1565: FND_DSQL.add_text( 'FROM (');
1566: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1567: FND_DSQL.add_text( 'SELECT cust_account_id, plan_type, plan_id, bill_to_site_use_id '||
1568: ', product_level_type, product_id '||
1569: ', sum(amount_remaining), currency_code '||

Line 1567: FND_DSQL.add_text( 'SELECT cust_account_id, plan_type, plan_id, bill_to_site_use_id '||

1563: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');
1564: END IF;
1565: FND_DSQL.add_text( 'FROM (');
1566: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1567: FND_DSQL.add_text( 'SELECT cust_account_id, plan_type, plan_id, bill_to_site_use_id '||
1568: ', product_level_type, product_id '||
1569: ', sum(amount_remaining), currency_code '||
1570: 'FROM ('||
1571: 'SELECT cust_account_id, plan_type, plan_id '||

Line 1578: FND_DSQL.add_text( 'SELECT lu.claim_line_util_id, lu.utilization_id, lu.amount, lu.scan_unit, lu.currency_code '||

1574: ', acctd_amount_remaining , amount_remaining, currency_code, bill_to_site_use_id '||
1575: 'FROM (');
1576: ELSIF p_summary_view = 'DEL_GRP_LINE_UTIL' THEN
1577: -- Modified for FXGL ER(amount selected)
1578: FND_DSQL.add_text( 'SELECT lu.claim_line_util_id, lu.utilization_id, lu.amount, lu.scan_unit, lu.currency_code '||
1579: 'FROM (');
1580: ELSE
1581: -- Modified for FXGL ER
1582: -- R12 Multicurrency Enhancements: Amount Remaining changed from BUDGET to TRANSACTIONAL currency

Line 1583: FND_DSQL.add_text( 'SELECT utilization_id, amount_remaining, scan_unit_remaining, currency_code, plan_currency_code , plan_curr_amount_remaining , plan_curr_amount , acctd_amount_remaining '|| --ninarasi fix for bug 13550004

1579: 'FROM (');
1580: ELSE
1581: -- Modified for FXGL ER
1582: -- R12 Multicurrency Enhancements: Amount Remaining changed from BUDGET to TRANSACTIONAL currency
1583: FND_DSQL.add_text( 'SELECT utilization_id, amount_remaining, scan_unit_remaining, currency_code, plan_currency_code , plan_curr_amount_remaining , plan_curr_amount , acctd_amount_remaining '|| --ninarasi fix for bug 13550004
1584: 'FROM (');
1585: END IF;
1586:
1587: Get_Utiz_Sql_Stmt_From_Clause(

Line 1597: FND_DSQL.add_text( ') utiz ');

1593: -- R12.1 autopay enhancement, Need to select and group by bill_to_site_use_id.
1594: -- for p_summary_view = AUTOPAY and AUTOPAY_LINE.
1595:
1596: IF p_summary_view = 'AUTOPAY' THEN
1597: FND_DSQL.add_text( ') utiz ');
1598: -- R12 Enhancements: Group By Offer for Autopay.
1599: IF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1600: FND_DSQL.add_text('GROUP BY utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1601: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN

Line 1600: FND_DSQL.add_text('GROUP BY utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');

1596: IF p_summary_view = 'AUTOPAY' THEN
1597: FND_DSQL.add_text( ') utiz ');
1598: -- R12 Enhancements: Group By Offer for Autopay.
1599: IF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1600: FND_DSQL.add_text('GROUP BY utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1601: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1602: FND_DSQL.add_text('GROUP BY utiz.cust_account_id, utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1603: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1604: FND_DSQL.add_text('GROUP BY utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');

Line 1602: FND_DSQL.add_text('GROUP BY utiz.cust_account_id, utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');

1598: -- R12 Enhancements: Group By Offer for Autopay.
1599: IF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1600: FND_DSQL.add_text('GROUP BY utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1601: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1602: FND_DSQL.add_text('GROUP BY utiz.cust_account_id, utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1603: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1604: FND_DSQL.add_text('GROUP BY utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');
1605: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1606: FND_DSQL.add_text('GROUP BY utiz.cust_account_id,utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');

Line 1604: FND_DSQL.add_text('GROUP BY utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');

1600: FND_DSQL.add_text('GROUP BY utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1601: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1602: FND_DSQL.add_text('GROUP BY utiz.cust_account_id, utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1603: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1604: FND_DSQL.add_text('GROUP BY utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');
1605: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1606: FND_DSQL.add_text('GROUP BY utiz.cust_account_id,utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');
1607: END IF;
1608: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN

Line 1606: FND_DSQL.add_text('GROUP BY utiz.cust_account_id,utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');

1602: FND_DSQL.add_text('GROUP BY utiz.cust_account_id, utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1603: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1604: FND_DSQL.add_text('GROUP BY utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');
1605: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1606: FND_DSQL.add_text('GROUP BY utiz.cust_account_id,utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');
1607: END IF;
1608: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1609: FND_DSQL.add_text( ') utiz ) '||
1610: 'GROUP BY cust_account_id, plan_type, plan_id, bill_to_site_use_id, product_level_type, product_id, currency_code '||

Line 1609: FND_DSQL.add_text( ') utiz ) '||

1605: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1606: FND_DSQL.add_text('GROUP BY utiz.cust_account_id,utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');
1607: END IF;
1608: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1609: FND_DSQL.add_text( ') utiz ) '||
1610: 'GROUP BY cust_account_id, plan_type, plan_id, bill_to_site_use_id, product_level_type, product_id, currency_code '||
1611: 'ORDER BY cust_account_id, plan_type, plan_id, bill_to_site_use_id, product_level_type, product_id ');
1612: ELSIF p_summary_view = 'DEL_GRP_LINE_UTIL' THEN
1613: FND_DSQL.add_text( ') utiz, ozf_claim_lines_util lu '||

Line 1613: FND_DSQL.add_text( ') utiz, ozf_claim_lines_util lu '||

1609: FND_DSQL.add_text( ') utiz ) '||
1610: 'GROUP BY cust_account_id, plan_type, plan_id, bill_to_site_use_id, product_level_type, product_id, currency_code '||
1611: 'ORDER BY cust_account_id, plan_type, plan_id, bill_to_site_use_id, product_level_type, product_id ');
1612: ELSIF p_summary_view = 'DEL_GRP_LINE_UTIL' THEN
1613: FND_DSQL.add_text( ') utiz, ozf_claim_lines_util lu '||
1614: 'WHERE lu.utilization_id = utiz.utilization_id '||
1615: 'AND lu.claim_line_id = ');
1616: FND_DSQL.add_bind( l_funds_util_flt.claim_line_id );
1617: FND_DSQL.add_text( ' ORDER BY utiz.creation_date desc ');

Line 1616: FND_DSQL.add_bind( l_funds_util_flt.claim_line_id );

1612: ELSIF p_summary_view = 'DEL_GRP_LINE_UTIL' THEN
1613: FND_DSQL.add_text( ') utiz, ozf_claim_lines_util lu '||
1614: 'WHERE lu.utilization_id = utiz.utilization_id '||
1615: 'AND lu.claim_line_id = ');
1616: FND_DSQL.add_bind( l_funds_util_flt.claim_line_id );
1617: FND_DSQL.add_text( ' ORDER BY utiz.creation_date desc ');
1618: ELSE
1619: FND_DSQL.add_text( ') utiz ');
1620: IF l_funds_util_flt.total_amount IS NOT NULL THEN

Line 1617: FND_DSQL.add_text( ' ORDER BY utiz.creation_date desc ');

1613: FND_DSQL.add_text( ') utiz, ozf_claim_lines_util lu '||
1614: 'WHERE lu.utilization_id = utiz.utilization_id '||
1615: 'AND lu.claim_line_id = ');
1616: FND_DSQL.add_bind( l_funds_util_flt.claim_line_id );
1617: FND_DSQL.add_text( ' ORDER BY utiz.creation_date desc ');
1618: ELSE
1619: FND_DSQL.add_text( ') utiz ');
1620: IF l_funds_util_flt.total_amount IS NOT NULL THEN
1621: IF l_funds_util_flt.total_amount >= 0 THEN

Line 1619: FND_DSQL.add_text( ') utiz ');

1615: 'AND lu.claim_line_id = ');
1616: FND_DSQL.add_bind( l_funds_util_flt.claim_line_id );
1617: FND_DSQL.add_text( ' ORDER BY utiz.creation_date desc ');
1618: ELSE
1619: FND_DSQL.add_text( ') utiz ');
1620: IF l_funds_util_flt.total_amount IS NOT NULL THEN
1621: IF l_funds_util_flt.total_amount >= 0 THEN
1622: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) asc, utiz.creation_date asc');
1623: ELSE

Line 1622: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) asc, utiz.creation_date asc');

1618: ELSE
1619: FND_DSQL.add_text( ') utiz ');
1620: IF l_funds_util_flt.total_amount IS NOT NULL THEN
1621: IF l_funds_util_flt.total_amount >= 0 THEN
1622: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) asc, utiz.creation_date asc');
1623: ELSE
1624: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) desc, utiz.creation_date asc');
1625: END IF;
1626: ELSE

Line 1624: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) desc, utiz.creation_date asc');

1620: IF l_funds_util_flt.total_amount IS NOT NULL THEN
1621: IF l_funds_util_flt.total_amount >= 0 THEN
1622: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) asc, utiz.creation_date asc');
1623: ELSE
1624: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) desc, utiz.creation_date asc');
1625: END IF;
1626: ELSE
1627: FND_DSQL.add_text(' ORDER BY utiz.creation_date asc');
1628: END IF;

Line 1627: FND_DSQL.add_text(' ORDER BY utiz.creation_date asc');

1623: ELSE
1624: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) desc, utiz.creation_date asc');
1625: END IF;
1626: ELSE
1627: FND_DSQL.add_text(' ORDER BY utiz.creation_date asc');
1628: END IF;
1629: END IF;
1630:
1631: x_utiz_sql_stmt := FND_DSQL.get_text(FALSE);

Line 1631: x_utiz_sql_stmt := FND_DSQL.get_text(FALSE);

1627: FND_DSQL.add_text(' ORDER BY utiz.creation_date asc');
1628: END IF;
1629: END IF;
1630:
1631: x_utiz_sql_stmt := FND_DSQL.get_text(FALSE);
1632:
1633: IF OZF_DEBUG_HIGH_ON THEN
1634: --l_utiz_sql := FND_DSQL.get_text(TRUE);
1635: l_utiz_sql := SUBSTR(FND_DSQL.get_text(TRUE),1,4000);

Line 1634: --l_utiz_sql := FND_DSQL.get_text(TRUE);

1630:
1631: x_utiz_sql_stmt := FND_DSQL.get_text(FALSE);
1632:
1633: IF OZF_DEBUG_HIGH_ON THEN
1634: --l_utiz_sql := FND_DSQL.get_text(TRUE);
1635: l_utiz_sql := SUBSTR(FND_DSQL.get_text(TRUE),1,4000);
1636: OZF_Utility_PVT.debug_message('----- UTIZ SQL -----');
1637: OZF_Utility_PVT.debug_message(SUBSTR(l_utiz_sql, 1, 250));
1638: OZF_Utility_PVT.debug_message(SUBSTR(l_utiz_sql, 251, 250));

Line 1635: l_utiz_sql := SUBSTR(FND_DSQL.get_text(TRUE),1,4000);

1631: x_utiz_sql_stmt := FND_DSQL.get_text(FALSE);
1632:
1633: IF OZF_DEBUG_HIGH_ON THEN
1634: --l_utiz_sql := FND_DSQL.get_text(TRUE);
1635: l_utiz_sql := SUBSTR(FND_DSQL.get_text(TRUE),1,4000);
1636: OZF_Utility_PVT.debug_message('----- UTIZ SQL -----');
1637: OZF_Utility_PVT.debug_message(SUBSTR(l_utiz_sql, 1, 250));
1638: OZF_Utility_PVT.debug_message(SUBSTR(l_utiz_sql, 251, 250));
1639: OZF_Utility_PVT.debug_message(SUBSTR(l_utiz_sql, 501, 250));

Line 3577: -- use FND_DSQL package for dynamic sql and bind variables

3573: ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
3574: RAISE FND_API.g_exc_unexpected_error;
3575: END IF;
3576:
3577: -- use FND_DSQL package for dynamic sql and bind variables
3578: l_funds_util_csr := DBMS_SQL.open_cursor;
3579: FND_DSQL.set_cursor(l_funds_util_csr);
3580: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
3581: DBMS_SQL.define_column(l_funds_util_csr, 1, l_lu_line_util_id);

Line 3579: FND_DSQL.set_cursor(l_funds_util_csr);

3575: END IF;
3576:
3577: -- use FND_DSQL package for dynamic sql and bind variables
3578: l_funds_util_csr := DBMS_SQL.open_cursor;
3579: FND_DSQL.set_cursor(l_funds_util_csr);
3580: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
3581: DBMS_SQL.define_column(l_funds_util_csr, 1, l_lu_line_util_id);
3582: DBMS_SQL.define_column(l_funds_util_csr, 2, l_lu_utilization_id);
3583: DBMS_SQL.define_column(l_funds_util_csr, 3, l_lu_amt);

Line 3587: FND_DSQL.do_binds;

3583: DBMS_SQL.define_column(l_funds_util_csr, 3, l_lu_amt);
3584: DBMS_SQL.define_column(l_funds_util_csr, 4, l_lu_scan_unit);
3585: DBMS_SQL.define_column(l_funds_util_csr, 5, l_lu_currency_code, 15);
3586: -- DBMS_SQL.define_column(l_funds_util_csr, 5, l_utiz_amount);
3587: FND_DSQL.do_binds;
3588:
3589: l_ignore := DBMS_SQL.execute(l_funds_util_csr);
3590: LOOP
3591: IF DBMS_SQL.fetch_rows(l_funds_util_csr) > 0 THEN

Line 4443: FND_DSQL.init;

4439:
4440: l_performance_flag := 'T';
4441:
4442: IF p_resale_flag IS NULL OR p_resale_flag = 'F' THEN
4443: FND_DSQL.init;
4444: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4445: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4446: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4447: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');

Line 4444: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');

4440: l_performance_flag := 'T';
4441:
4442: IF p_resale_flag IS NULL OR p_resale_flag = 'F' THEN
4443: FND_DSQL.init;
4444: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4445: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4446: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4447: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4448: FND_DSQL.add_bind(p_cust_account_id);

Line 4445: FND_DSQL.add_text(' common_uom_code, common_currency_code ');

4441:
4442: IF p_resale_flag IS NULL OR p_resale_flag = 'F' THEN
4443: FND_DSQL.init;
4444: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4445: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4446: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4447: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4448: FND_DSQL.add_bind(p_cust_account_id);
4449: FND_DSQL.add_text(' AND transaction_date between ');

Line 4446: FND_DSQL.add_text(' FROM ozf_sales_transactions ');

4442: IF p_resale_flag IS NULL OR p_resale_flag = 'F' THEN
4443: FND_DSQL.init;
4444: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4445: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4446: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4447: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4448: FND_DSQL.add_bind(p_cust_account_id);
4449: FND_DSQL.add_text(' AND transaction_date between ');
4450: FND_DSQL.add_bind(l_start_date);

Line 4447: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');

4443: FND_DSQL.init;
4444: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4445: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4446: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4447: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4448: FND_DSQL.add_bind(p_cust_account_id);
4449: FND_DSQL.add_text(' AND transaction_date between ');
4450: FND_DSQL.add_bind(l_start_date);
4451: FND_DSQL.add_text(' and ');

Line 4448: FND_DSQL.add_bind(p_cust_account_id);

4444: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4445: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4446: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4447: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4448: FND_DSQL.add_bind(p_cust_account_id);
4449: FND_DSQL.add_text(' AND transaction_date between ');
4450: FND_DSQL.add_bind(l_start_date);
4451: FND_DSQL.add_text(' and ');
4452: FND_DSQL.add_bind(l_end_date);

Line 4449: FND_DSQL.add_text(' AND transaction_date between ');

4445: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4446: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4447: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4448: FND_DSQL.add_bind(p_cust_account_id);
4449: FND_DSQL.add_text(' AND transaction_date between ');
4450: FND_DSQL.add_bind(l_start_date);
4451: FND_DSQL.add_text(' and ');
4452: FND_DSQL.add_bind(l_end_date);
4453: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');

Line 4450: FND_DSQL.add_bind(l_start_date);

4446: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4447: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4448: FND_DSQL.add_bind(p_cust_account_id);
4449: FND_DSQL.add_text(' AND transaction_date between ');
4450: FND_DSQL.add_bind(l_start_date);
4451: FND_DSQL.add_text(' and ');
4452: FND_DSQL.add_bind(l_end_date);
4453: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4454: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(

Line 4451: FND_DSQL.add_text(' and ');

4447: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4448: FND_DSQL.add_bind(p_cust_account_id);
4449: FND_DSQL.add_text(' AND transaction_date between ');
4450: FND_DSQL.add_bind(l_start_date);
4451: FND_DSQL.add_text(' and ');
4452: FND_DSQL.add_bind(l_end_date);
4453: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4454: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4455: p_context => l_product_attr_context,

Line 4452: FND_DSQL.add_bind(l_end_date);

4448: FND_DSQL.add_bind(p_cust_account_id);
4449: FND_DSQL.add_text(' AND transaction_date between ');
4450: FND_DSQL.add_bind(l_start_date);
4451: FND_DSQL.add_text(' and ');
4452: FND_DSQL.add_bind(l_end_date);
4453: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4454: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4455: p_context => l_product_attr_context,
4456: p_attribute => l_product_attribute,

Line 4453: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');

4449: FND_DSQL.add_text(' AND transaction_date between ');
4450: FND_DSQL.add_bind(l_start_date);
4451: FND_DSQL.add_text(' and ');
4452: FND_DSQL.add_bind(l_end_date);
4453: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4454: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4455: p_context => l_product_attr_context,
4456: p_attribute => l_product_attribute,
4457: p_attr_value_from => l_product_attr_value,

Line 4462: FND_DSQL.add_text(') s) ');

4458: p_attr_value_to => NULL,
4459: p_comparison => NULL,
4460: p_type => 'PROD'
4461: );
4462: FND_DSQL.add_text(') s) ');
4463: FND_DSQL.add_text(' GROUP BY common_uom_code, common_currency_code ');
4464:
4465: IF OZF_DEBUG_HIGH_ON THEN
4466: l_temp_sql := FND_DSQL.get_text(TRUE);

Line 4463: FND_DSQL.add_text(' GROUP BY common_uom_code, common_currency_code ');

4459: p_comparison => NULL,
4460: p_type => 'PROD'
4461: );
4462: FND_DSQL.add_text(') s) ');
4463: FND_DSQL.add_text(' GROUP BY common_uom_code, common_currency_code ');
4464:
4465: IF OZF_DEBUG_HIGH_ON THEN
4466: l_temp_sql := FND_DSQL.get_text(TRUE);
4467: OZF_Utility_PVT.debug_message('----- Check_Offer_Performance SQL -----');

Line 4466: l_temp_sql := FND_DSQL.get_text(TRUE);

4462: FND_DSQL.add_text(') s) ');
4463: FND_DSQL.add_text(' GROUP BY common_uom_code, common_currency_code ');
4464:
4465: IF OZF_DEBUG_HIGH_ON THEN
4466: l_temp_sql := FND_DSQL.get_text(TRUE);
4467: OZF_Utility_PVT.debug_message('----- Check_Offer_Performance SQL -----');
4468: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 1, 254));
4469: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 255, 254));
4470: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 509, 254));

Line 4475: FND_DSQL.set_cursor(l_emp_csr);

4471: OZF_Utility_PVT.debug_message('---------------------------------------');
4472: END IF;
4473:
4474: l_emp_csr := DBMS_SQL.open_cursor;
4475: FND_DSQL.set_cursor(l_emp_csr);
4476: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);
4477: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4478: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4479: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);

Line 4476: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);

4472: END IF;
4473:
4474: l_emp_csr := DBMS_SQL.open_cursor;
4475: FND_DSQL.set_cursor(l_emp_csr);
4476: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);
4477: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4478: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4479: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);
4480: DBMS_SQL.define_column(l_emp_csr, 4, l_common_curr_code, 15);

Line 4481: FND_DSQL.do_binds;

4477: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4478: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4479: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);
4480: DBMS_SQL.define_column(l_emp_csr, 4, l_common_curr_code, 15);
4481: FND_DSQL.do_binds;
4482:
4483: l_ignore := DBMS_SQL.execute(l_emp_csr);
4484: IF DBMS_SQL.fetch_rows(l_emp_csr) > 0 THEN
4485: DBMS_SQL.column_value(l_emp_csr, 1, l_common_quantity);

Line 4564: FND_DSQL.init;

4560: DBMS_SQL.close_cursor(l_emp_csr);
4561:
4562: ELSE
4563: /********** Check resale data ***********/
4564: FND_DSQL.init;
4565: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4566: FND_DSQL.add_text(' uom_code ');
4567: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4568: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');

Line 4565: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');

4561:
4562: ELSE
4563: /********** Check resale data ***********/
4564: FND_DSQL.init;
4565: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4566: FND_DSQL.add_text(' uom_code ');
4567: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4568: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4569: FND_DSQL.add_bind(p_cust_account_id);

Line 4566: FND_DSQL.add_text(' uom_code ');

4562: ELSE
4563: /********** Check resale data ***********/
4564: FND_DSQL.init;
4565: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4566: FND_DSQL.add_text(' uom_code ');
4567: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4568: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4569: FND_DSQL.add_bind(p_cust_account_id);
4570: FND_DSQL.add_text(' AND date_ordered between ');

Line 4567: FND_DSQL.add_text(' FROM ozf_resale_lines ');

4563: /********** Check resale data ***********/
4564: FND_DSQL.init;
4565: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4566: FND_DSQL.add_text(' uom_code ');
4567: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4568: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4569: FND_DSQL.add_bind(p_cust_account_id);
4570: FND_DSQL.add_text(' AND date_ordered between ');
4571: FND_DSQL.add_bind(l_start_date);

Line 4568: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');

4564: FND_DSQL.init;
4565: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4566: FND_DSQL.add_text(' uom_code ');
4567: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4568: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4569: FND_DSQL.add_bind(p_cust_account_id);
4570: FND_DSQL.add_text(' AND date_ordered between ');
4571: FND_DSQL.add_bind(l_start_date);
4572: FND_DSQL.add_text(' and ');

Line 4569: FND_DSQL.add_bind(p_cust_account_id);

4565: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4566: FND_DSQL.add_text(' uom_code ');
4567: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4568: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4569: FND_DSQL.add_bind(p_cust_account_id);
4570: FND_DSQL.add_text(' AND date_ordered between ');
4571: FND_DSQL.add_bind(l_start_date);
4572: FND_DSQL.add_text(' and ');
4573: FND_DSQL.add_bind(l_end_date);

Line 4570: FND_DSQL.add_text(' AND date_ordered between ');

4566: FND_DSQL.add_text(' uom_code ');
4567: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4568: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4569: FND_DSQL.add_bind(p_cust_account_id);
4570: FND_DSQL.add_text(' AND date_ordered between ');
4571: FND_DSQL.add_bind(l_start_date);
4572: FND_DSQL.add_text(' and ');
4573: FND_DSQL.add_bind(l_end_date);
4574: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');

Line 4571: FND_DSQL.add_bind(l_start_date);

4567: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4568: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4569: FND_DSQL.add_bind(p_cust_account_id);
4570: FND_DSQL.add_text(' AND date_ordered between ');
4571: FND_DSQL.add_bind(l_start_date);
4572: FND_DSQL.add_text(' and ');
4573: FND_DSQL.add_bind(l_end_date);
4574: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4575: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(

Line 4572: FND_DSQL.add_text(' and ');

4568: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4569: FND_DSQL.add_bind(p_cust_account_id);
4570: FND_DSQL.add_text(' AND date_ordered between ');
4571: FND_DSQL.add_bind(l_start_date);
4572: FND_DSQL.add_text(' and ');
4573: FND_DSQL.add_bind(l_end_date);
4574: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4575: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4576: p_context => l_product_attr_context,

Line 4573: FND_DSQL.add_bind(l_end_date);

4569: FND_DSQL.add_bind(p_cust_account_id);
4570: FND_DSQL.add_text(' AND date_ordered between ');
4571: FND_DSQL.add_bind(l_start_date);
4572: FND_DSQL.add_text(' and ');
4573: FND_DSQL.add_bind(l_end_date);
4574: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4575: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4576: p_context => l_product_attr_context,
4577: p_attribute => l_product_attribute,

Line 4574: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');

4570: FND_DSQL.add_text(' AND date_ordered between ');
4571: FND_DSQL.add_bind(l_start_date);
4572: FND_DSQL.add_text(' and ');
4573: FND_DSQL.add_bind(l_end_date);
4574: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4575: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4576: p_context => l_product_attr_context,
4577: p_attribute => l_product_attribute,
4578: p_attr_value_from => l_product_attr_value,

Line 4583: FND_DSQL.add_text(') s) ');

4579: p_attr_value_to => NULL,
4580: p_comparison => NULL,
4581: p_type => 'PROD'
4582: );
4583: FND_DSQL.add_text(') s) ');
4584: FND_DSQL.add_text(' GROUP BY uom_code ');
4585:
4586: IF OZF_DEBUG_HIGH_ON THEN
4587: l_temp_sql := FND_DSQL.get_text(TRUE);

Line 4584: FND_DSQL.add_text(' GROUP BY uom_code ');

4580: p_comparison => NULL,
4581: p_type => 'PROD'
4582: );
4583: FND_DSQL.add_text(') s) ');
4584: FND_DSQL.add_text(' GROUP BY uom_code ');
4585:
4586: IF OZF_DEBUG_HIGH_ON THEN
4587: l_temp_sql := FND_DSQL.get_text(TRUE);
4588: OZF_Utility_PVT.debug_message('----- Check_Offer_Performance SQL -----');

Line 4587: l_temp_sql := FND_DSQL.get_text(TRUE);

4583: FND_DSQL.add_text(') s) ');
4584: FND_DSQL.add_text(' GROUP BY uom_code ');
4585:
4586: IF OZF_DEBUG_HIGH_ON THEN
4587: l_temp_sql := FND_DSQL.get_text(TRUE);
4588: OZF_Utility_PVT.debug_message('----- Check_Offer_Performance SQL -----');
4589: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 1, 254));
4590: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 255, 254));
4591: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 509, 254));

Line 4596: FND_DSQL.set_cursor(l_emp_csr);

4592: OZF_Utility_PVT.debug_message('---------------------------------------');
4593: END IF;
4594:
4595: l_emp_csr := DBMS_SQL.open_cursor;
4596: FND_DSQL.set_cursor(l_emp_csr);
4597: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);
4598: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4599: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4600: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);

Line 4597: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);

4593: END IF;
4594:
4595: l_emp_csr := DBMS_SQL.open_cursor;
4596: FND_DSQL.set_cursor(l_emp_csr);
4597: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);
4598: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4599: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4600: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);
4601: FND_DSQL.do_binds;

Line 4601: FND_DSQL.do_binds;

4597: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);
4598: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4599: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4600: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);
4601: FND_DSQL.do_binds;
4602:
4603: l_ignore := DBMS_SQL.execute(l_emp_csr);
4604: IF DBMS_SQL.fetch_rows(l_emp_csr) > 0 THEN
4605: DBMS_SQL.column_value(l_emp_csr, 1, l_common_quantity);

Line 5032: FND_DSQL.set_cursor(l_emp_csr);

5028: RAISE FND_API.g_exc_error;
5029: END IF;
5030:
5031: l_emp_csr := DBMS_SQL.open_cursor;
5032: FND_DSQL.set_cursor(l_emp_csr);
5033: DBMS_SQL.parse(l_emp_csr, l_stmt, DBMS_SQL.native);
5034: DBMS_SQL.define_column(l_emp_csr, 1, l_cust_account_id);
5035: DBMS_SQL.define_column(l_emp_csr, 2, l_plan_type, 30);
5036: DBMS_SQL.define_column(l_emp_csr, 3, l_plan_id);

Line 5042: FND_DSQL.do_binds;

5038: DBMS_SQL.define_column(l_emp_csr, 5, l_product_level_type, 30);
5039: DBMS_SQL.define_column(l_emp_csr, 6, l_product_id);
5040: DBMS_SQL.define_column(l_emp_csr, 7, l_amount);
5041: DBMS_SQL.define_column(l_emp_csr, 8, l_currency_code, 15);
5042: FND_DSQL.do_binds;
5043:
5044: l_ignore := DBMS_SQL.execute(l_emp_csr);
5045: LOOP
5046: IF DBMS_SQL.fetch_rows(l_emp_csr) > 0 THEN

Line 6390: FND_DSQL.set_cursor(l_emp_csr);

6386: RAISE FND_API.g_exc_error;
6387: END IF;
6388:
6389: l_emp_csr := DBMS_SQL.open_cursor;
6390: FND_DSQL.set_cursor(l_emp_csr);
6391: DBMS_SQL.parse(l_emp_csr, l_stmt, DBMS_SQL.native);
6392: DBMS_SQL.define_column(l_emp_csr, 1, l_cust_account_id);
6393: DBMS_SQL.define_column(l_emp_csr, 2, l_plan_type, 30);
6394: DBMS_SQL.define_column(l_emp_csr, 3, l_plan_id);

Line 6400: FND_DSQL.do_binds;

6396: DBMS_SQL.define_column(l_emp_csr, 5, l_product_level_type, 30);
6397: DBMS_SQL.define_column(l_emp_csr, 6, l_product_id);
6398: DBMS_SQL.define_column(l_emp_csr, 7, l_amount);
6399: DBMS_SQL.define_column(l_emp_csr, 8, l_currency_code, 15);
6400: FND_DSQL.do_binds;
6401:
6402: l_ignore := DBMS_SQL.execute(l_emp_csr);
6403: LOOP
6404: IF DBMS_SQL.fetch_rows(l_emp_csr) > 0 AND l_total_amount <> 0 THEN

Line 8847: FND_DSQL.set_cursor(l_funds_util_csr);

8843: END IF;
8844:
8845: j := 0;
8846: l_funds_util_csr := DBMS_SQL.open_cursor;
8847: FND_DSQL.set_cursor(l_funds_util_csr);
8848: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
8849: DBMS_SQL.define_column(l_funds_util_csr, 1, l_util_id);
8850: DBMS_SQL.define_column(l_funds_util_csr, 2, l_fu_amt_rem);
8851: DBMS_SQL.define_column(l_funds_util_csr, 3, l_fu_scan_unit_rem);

Line 8857: FND_DSQL.do_binds;

8853: DBMS_SQL.define_column(l_funds_util_csr, 5, l_plan_currency_code, 15);
8854: DBMS_SQL.define_column(l_funds_util_csr, 6, l_plan_curr_amount_remaining);
8855: DBMS_SQL.define_column(l_funds_util_csr, 7, l_plan_curr_amount);
8856: DBMS_SQL.define_column(l_funds_util_csr, 8, l_acctd_amount_remaining);
8857: FND_DSQL.do_binds;
8858:
8859: l_ignore := DBMS_SQL.execute(l_funds_util_csr);
8860: LOOP
8861: IF DBMS_SQL.fetch_rows(l_funds_util_csr) > 0 THEN

Line 10622: -- use FND_DSQL package for dynamic sql and bind variables

10618: ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
10619: RAISE FND_API.g_exc_unexpected_error;
10620: END IF;
10621:
10622: -- use FND_DSQL package for dynamic sql and bind variables
10623: l_funds_util_csr := DBMS_SQL.open_cursor;
10624: FND_DSQL.set_cursor(l_funds_util_csr);
10625: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
10626: DBMS_SQL.define_column(l_funds_util_csr, 1, l_lu_line_util_id);

Line 10624: FND_DSQL.set_cursor(l_funds_util_csr);

10620: END IF;
10621:
10622: -- use FND_DSQL package for dynamic sql and bind variables
10623: l_funds_util_csr := DBMS_SQL.open_cursor;
10624: FND_DSQL.set_cursor(l_funds_util_csr);
10625: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
10626: DBMS_SQL.define_column(l_funds_util_csr, 1, l_lu_line_util_id);
10627: DBMS_SQL.define_column(l_funds_util_csr, 2, l_lu_utilization_id);
10628: DBMS_SQL.define_column(l_funds_util_csr, 3, l_lu_amt);

Line 10632: FND_DSQL.do_binds;

10628: DBMS_SQL.define_column(l_funds_util_csr, 3, l_lu_amt);
10629: DBMS_SQL.define_column(l_funds_util_csr, 4, l_lu_scan_unit);
10630: DBMS_SQL.define_column(l_funds_util_csr, 5, l_lu_currency_code, 15);
10631: -- DBMS_SQL.define_column(l_funds_util_csr, 5, l_utiz_amount);
10632: FND_DSQL.do_binds;
10633:
10634: l_ignore := DBMS_SQL.execute(l_funds_util_csr);
10635: --OPEN l_funds_util_csr FOR l_funds_util_sql;
10636: LOOP