[Home] [Help]
567: -- p_select_cust_children_flag : Include all member earnings
568: --
569: -- HISTORY
570: -- 14-FEB-2003 yizhang Create.
571: -- 05-MAY-2003 yizhang Use FND_DSQL for dynamic sql and bind vars
572: -- 28-feb-06 azahmed modified for bugfix 4958714
573: ---------------------------------------------------------------------
574: PROCEDURE Get_Customer_For_Earnings(
575: p_cust_account_id IN NUMBER
599: p_select_cust_children_flag = 'F' OR
600: p_select_cust_children_flag = 'N' THEN
601: -- not to include member earnings
602: IF p_buy_group_party_id IS NOT NULL THEN
603: FND_DSQL.add_text(' IN (SELECT c.cust_account_id FROM hz_cust_accounts c');
604: FND_DSQL.add_text(' WHERE c.party_id = ');
605: FND_DSQL.add_bind(p_buy_group_party_id);
606: FND_DSQL.add_text(')');
607: ELSIF p_relationship_type IS NOT NULL AND
600: p_select_cust_children_flag = 'N' THEN
601: -- not to include member earnings
602: IF p_buy_group_party_id IS NOT NULL THEN
603: FND_DSQL.add_text(' IN (SELECT c.cust_account_id FROM hz_cust_accounts c');
604: FND_DSQL.add_text(' WHERE c.party_id = ');
605: FND_DSQL.add_bind(p_buy_group_party_id);
606: FND_DSQL.add_text(')');
607: ELSIF p_relationship_type IS NOT NULL AND
608: p_related_cust_account_id IS NULL
601: -- not to include member earnings
602: IF p_buy_group_party_id IS NOT NULL THEN
603: FND_DSQL.add_text(' IN (SELECT c.cust_account_id FROM hz_cust_accounts c');
604: FND_DSQL.add_text(' WHERE c.party_id = ');
605: FND_DSQL.add_bind(p_buy_group_party_id);
606: FND_DSQL.add_text(')');
607: ELSIF p_relationship_type IS NOT NULL AND
608: p_related_cust_account_id IS NULL
609: THEN
602: IF p_buy_group_party_id IS NOT NULL THEN
603: FND_DSQL.add_text(' IN (SELECT c.cust_account_id FROM hz_cust_accounts c');
604: FND_DSQL.add_text(' WHERE c.party_id = ');
605: FND_DSQL.add_bind(p_buy_group_party_id);
606: FND_DSQL.add_text(')');
607: ELSIF p_relationship_type IS NOT NULL AND
608: p_related_cust_account_id IS NULL
609: THEN
610: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
606: FND_DSQL.add_text(')');
607: ELSIF p_relationship_type IS NOT NULL AND
608: p_related_cust_account_id IS NULL
609: THEN
610: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
611: FND_DSQL.add_text(' WHERE cust_account_id = ');
612: FND_DSQL.add_bind(p_cust_account_id);
613: FND_DSQL.add_text(' AND relationship_type = ');
614: FND_DSQL.add_bind(p_relationship_type);
607: ELSIF p_relationship_type IS NOT NULL AND
608: p_related_cust_account_id IS NULL
609: THEN
610: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
611: FND_DSQL.add_text(' WHERE cust_account_id = ');
612: FND_DSQL.add_bind(p_cust_account_id);
613: FND_DSQL.add_text(' AND relationship_type = ');
614: FND_DSQL.add_bind(p_relationship_type);
615: FND_DSQL.add_text(')');
608: p_related_cust_account_id IS NULL
609: THEN
610: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
611: FND_DSQL.add_text(' WHERE cust_account_id = ');
612: FND_DSQL.add_bind(p_cust_account_id);
613: FND_DSQL.add_text(' AND relationship_type = ');
614: FND_DSQL.add_bind(p_relationship_type);
615: FND_DSQL.add_text(')');
616: ELSE
609: THEN
610: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
611: FND_DSQL.add_text(' WHERE cust_account_id = ');
612: FND_DSQL.add_bind(p_cust_account_id);
613: FND_DSQL.add_text(' AND relationship_type = ');
614: FND_DSQL.add_bind(p_relationship_type);
615: FND_DSQL.add_text(')');
616: ELSE
617: FND_DSQL.add_text(' = ');
610: FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
611: FND_DSQL.add_text(' WHERE cust_account_id = ');
612: FND_DSQL.add_bind(p_cust_account_id);
613: FND_DSQL.add_text(' AND relationship_type = ');
614: FND_DSQL.add_bind(p_relationship_type);
615: FND_DSQL.add_text(')');
616: ELSE
617: FND_DSQL.add_text(' = ');
618: IF p_related_cust_account_id IS NOT NULL THEN
611: FND_DSQL.add_text(' WHERE cust_account_id = ');
612: FND_DSQL.add_bind(p_cust_account_id);
613: FND_DSQL.add_text(' AND relationship_type = ');
614: FND_DSQL.add_bind(p_relationship_type);
615: FND_DSQL.add_text(')');
616: ELSE
617: FND_DSQL.add_text(' = ');
618: IF p_related_cust_account_id IS NOT NULL THEN
619: FND_DSQL.add_bind(p_related_cust_account_id);
613: FND_DSQL.add_text(' AND relationship_type = ');
614: FND_DSQL.add_bind(p_relationship_type);
615: FND_DSQL.add_text(')');
616: ELSE
617: FND_DSQL.add_text(' = ');
618: IF p_related_cust_account_id IS NOT NULL THEN
619: FND_DSQL.add_bind(p_related_cust_account_id);
620: ELSE
621: FND_DSQL.add_bind(p_cust_account_id);
615: FND_DSQL.add_text(')');
616: ELSE
617: FND_DSQL.add_text(' = ');
618: IF p_related_cust_account_id IS NOT NULL THEN
619: FND_DSQL.add_bind(p_related_cust_account_id);
620: ELSE
621: FND_DSQL.add_bind(p_cust_account_id);
622: END IF;
623: END IF;
617: FND_DSQL.add_text(' = ');
618: IF p_related_cust_account_id IS NOT NULL THEN
619: FND_DSQL.add_bind(p_related_cust_account_id);
620: ELSE
621: FND_DSQL.add_bind(p_cust_account_id);
622: END IF;
623: END IF;
624:
625: ELSIF p_select_cust_children_flag = 'T' OR p_select_cust_children_flag = 'Y' THEN
640: END IF;
641: END IF;
642:
643: IF l_bg_is_parent_of_cust = 1 OR l_bg_is_parent_of_relcust = 1 THEN
644: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
645: FND_DSQL.add_text(' FROM hz_cust_accounts c ');
646: FND_DSQL.add_text(' WHERE c.party_id = ');
647: FND_DSQL.add_bind(p_buy_group_party_id);
648: FND_DSQL.add_text(' OR c.cust_account_id = ');
641: END IF;
642:
643: IF l_bg_is_parent_of_cust = 1 OR l_bg_is_parent_of_relcust = 1 THEN
644: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
645: FND_DSQL.add_text(' FROM hz_cust_accounts c ');
646: FND_DSQL.add_text(' WHERE c.party_id = ');
647: FND_DSQL.add_bind(p_buy_group_party_id);
648: FND_DSQL.add_text(' OR c.cust_account_id = ');
649: IF p_related_cust_account_id is not NULL THEN
642:
643: IF l_bg_is_parent_of_cust = 1 OR l_bg_is_parent_of_relcust = 1 THEN
644: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
645: FND_DSQL.add_text(' FROM hz_cust_accounts c ');
646: FND_DSQL.add_text(' WHERE c.party_id = ');
647: FND_DSQL.add_bind(p_buy_group_party_id);
648: FND_DSQL.add_text(' OR c.cust_account_id = ');
649: IF p_related_cust_account_id is not NULL THEN
650: FND_DSQL.add_bind(p_related_cust_account_id);
643: IF l_bg_is_parent_of_cust = 1 OR l_bg_is_parent_of_relcust = 1 THEN
644: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
645: FND_DSQL.add_text(' FROM hz_cust_accounts c ');
646: FND_DSQL.add_text(' WHERE c.party_id = ');
647: FND_DSQL.add_bind(p_buy_group_party_id);
648: FND_DSQL.add_text(' OR c.cust_account_id = ');
649: IF p_related_cust_account_id is not NULL THEN
650: FND_DSQL.add_bind(p_related_cust_account_id);
651: ELSE
644: FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
645: FND_DSQL.add_text(' FROM hz_cust_accounts c ');
646: FND_DSQL.add_text(' WHERE c.party_id = ');
647: FND_DSQL.add_bind(p_buy_group_party_id);
648: FND_DSQL.add_text(' OR c.cust_account_id = ');
649: IF p_related_cust_account_id is not NULL THEN
650: FND_DSQL.add_bind(p_related_cust_account_id);
651: ELSE
652: FND_DSQL.add_bind(p_cust_account_id);
646: FND_DSQL.add_text(' WHERE c.party_id = ');
647: FND_DSQL.add_bind(p_buy_group_party_id);
648: FND_DSQL.add_text(' OR c.cust_account_id = ');
649: IF p_related_cust_account_id is not NULL THEN
650: FND_DSQL.add_bind(p_related_cust_account_id);
651: ELSE
652: FND_DSQL.add_bind(p_cust_account_id);
653: END IF;
654: FND_DSQL.add_text(')');
648: FND_DSQL.add_text(' OR c.cust_account_id = ');
649: IF p_related_cust_account_id is not NULL THEN
650: FND_DSQL.add_bind(p_related_cust_account_id);
651: ELSE
652: FND_DSQL.add_bind(p_cust_account_id);
653: END IF;
654: FND_DSQL.add_text(')');
655:
656: ELSE
650: FND_DSQL.add_bind(p_related_cust_account_id);
651: ELSE
652: FND_DSQL.add_bind(p_cust_account_id);
653: END IF;
654: FND_DSQL.add_text(')');
655:
656: ELSE
657: IF p_buy_group_party_id IS NOT NULL THEN
658: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
654: FND_DSQL.add_text(')');
655:
656: ELSE
657: IF p_buy_group_party_id IS NOT NULL THEN
658: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
659: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
660: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
661: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
662: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
655:
656: ELSE
657: IF p_buy_group_party_id IS NOT NULL THEN
658: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
659: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
660: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
661: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
662: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
663: FND_DSQL.add_bind(p_buy_group_party_id);
656: ELSE
657: IF p_buy_group_party_id IS NOT NULL THEN
658: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
659: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
660: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
661: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
662: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
663: FND_DSQL.add_bind(p_buy_group_party_id);
664: FND_DSQL.add_text(')');
657: IF p_buy_group_party_id IS NOT NULL THEN
658: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
659: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
660: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
661: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
662: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
663: FND_DSQL.add_bind(p_buy_group_party_id);
664: FND_DSQL.add_text(')');
665: ELSE
658: FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
659: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
660: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
661: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
662: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
663: FND_DSQL.add_bind(p_buy_group_party_id);
664: FND_DSQL.add_text(')');
665: ELSE
666: -- Modified for Bugfix 5346249
659: FND_DSQL.add_text(' FROM ams_party_market_segments sg, hz_cust_accounts c2 ');
660: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
661: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
662: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
663: FND_DSQL.add_bind(p_buy_group_party_id);
664: FND_DSQL.add_text(')');
665: ELSE
666: -- Modified for Bugfix 5346249
667: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
660: FND_DSQL.add_text(' WHERE sg.market_qualifier_type = ''BG'' ');
661: FND_DSQL.add_text(' AND sg.party_id = c2.party_id ');
662: FND_DSQL.add_text(' AND sg.market_qualifier_reference = ');
663: FND_DSQL.add_bind(p_buy_group_party_id);
664: FND_DSQL.add_text(')');
665: ELSE
666: -- Modified for Bugfix 5346249
667: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
668: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
663: FND_DSQL.add_bind(p_buy_group_party_id);
664: FND_DSQL.add_text(')');
665: ELSE
666: -- Modified for Bugfix 5346249
667: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
668: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
669: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
670: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
671: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
664: FND_DSQL.add_text(')');
665: ELSE
666: -- Modified for Bugfix 5346249
667: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
668: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
669: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
670: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
671: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
672: FND_DSQL.add_text(' and cust1.cust_account_id = ');
665: ELSE
666: -- Modified for Bugfix 5346249
667: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
668: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
669: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
670: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
671: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
672: FND_DSQL.add_text(' and cust1.cust_account_id = ');
673: IF p_related_cust_account_id IS NOT NULL THEN
666: -- Modified for Bugfix 5346249
667: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
668: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
669: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
670: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
671: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
672: FND_DSQL.add_text(' and cust1.cust_account_id = ');
673: IF p_related_cust_account_id IS NOT NULL THEN
674: FND_DSQL.add_bind(p_related_cust_account_id);
667: FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
668: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
669: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
670: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
671: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
672: FND_DSQL.add_text(' and cust1.cust_account_id = ');
673: IF p_related_cust_account_id IS NOT NULL THEN
674: FND_DSQL.add_bind(p_related_cust_account_id);
675: ELSE
668: FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
669: FND_DSQL.add_text(' ams_party_market_segments seg ,hz_cust_accounts cust1 ');
670: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
671: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
672: FND_DSQL.add_text(' and cust1.cust_account_id = ');
673: IF p_related_cust_account_id IS NOT NULL THEN
674: FND_DSQL.add_bind(p_related_cust_account_id);
675: ELSE
676: FND_DSQL.add_bind(p_cust_account_id);
670: FND_DSQL.add_text(' where seg.market_qualifier_type = ''BG'' ');
671: FND_DSQL.add_text(' and seg.market_qualifier_reference = cust1.party_id ');
672: FND_DSQL.add_text(' and cust1.cust_account_id = ');
673: IF p_related_cust_account_id IS NOT NULL THEN
674: FND_DSQL.add_bind(p_related_cust_account_id);
675: ELSE
676: FND_DSQL.add_bind(p_cust_account_id);
677: END IF;
678: FND_DSQL.add_text(')');
672: FND_DSQL.add_text(' and cust1.cust_account_id = ');
673: IF p_related_cust_account_id IS NOT NULL THEN
674: FND_DSQL.add_bind(p_related_cust_account_id);
675: ELSE
676: FND_DSQL.add_bind(p_cust_account_id);
677: END IF;
678: FND_DSQL.add_text(')');
679: FND_DSQL.add_text(')');
680: END IF;
674: FND_DSQL.add_bind(p_related_cust_account_id);
675: ELSE
676: FND_DSQL.add_bind(p_cust_account_id);
677: END IF;
678: FND_DSQL.add_text(')');
679: FND_DSQL.add_text(')');
680: END IF;
681: END IF;
682: END IF;
675: ELSE
676: FND_DSQL.add_bind(p_cust_account_id);
677: END IF;
678: FND_DSQL.add_text(')');
679: FND_DSQL.add_text(')');
680: END IF;
681: END IF;
682: END IF;
683:
912: --Bug 5154157 : Reverted change made for bug 4927201 as this will not be called if
913: -- total amount is to be overpaid
914: -- Modified for FXGL ER: only accruals in claim currency must be returned
915: IF p_summary_view IS NULL OR p_summary_view <> 'DEL_GRP_LINE_UTIL' THEN
916: FND_DSQL.add_text(' AND fu.amount_remaining <> 0 ');
917: IF l_funds_util_flt.utiz_currency_code is not null THEN
918: FND_DSQL.add_text(' AND fu.currency_code = ');
919: FND_DSQL.add_bind(l_funds_util_flt.utiz_currency_code);
920: END IF;
914: -- Modified for FXGL ER: only accruals in claim currency must be returned
915: IF p_summary_view IS NULL OR p_summary_view <> 'DEL_GRP_LINE_UTIL' THEN
916: FND_DSQL.add_text(' AND fu.amount_remaining <> 0 ');
917: IF l_funds_util_flt.utiz_currency_code is not null THEN
918: FND_DSQL.add_text(' AND fu.currency_code = ');
919: FND_DSQL.add_bind(l_funds_util_flt.utiz_currency_code);
920: END IF;
921: END IF;
922:
915: IF p_summary_view IS NULL OR p_summary_view <> 'DEL_GRP_LINE_UTIL' THEN
916: FND_DSQL.add_text(' AND fu.amount_remaining <> 0 ');
917: IF l_funds_util_flt.utiz_currency_code is not null THEN
918: FND_DSQL.add_text(' AND fu.currency_code = ');
919: FND_DSQL.add_bind(l_funds_util_flt.utiz_currency_code);
920: END IF;
921: END IF;
922:
923: -- for lead referral accruals, set utilization_type as LEAD_ACCRUAL
924: -- Fixed for Bug4576309
925: -- Modified for Bug4997509 to match ClaimAssoVO.getCommonWhereClause
926: IF l_funds_util_flt.utilization_type IS NULL OR l_funds_util_flt.utilization_type = 'ACCRUAL' THEN
927: IF l_funds_util_flt.reference_type = 'LEAD_REFERRAL' THEN
928: FND_DSQL.add_text(' AND fu.utilization_type IN (''LEAD_ACCRUAL'', ''LEAD_ADJUSTMENT'') ' );
929: ELSIF l_funds_util_flt.reference_type = 'BATCH' THEN
930: FND_DSQL.add_text(' AND fu.utilization_type = ''CHARGEBACK'' ');
931: ELSE
932: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );
926: IF l_funds_util_flt.utilization_type IS NULL OR l_funds_util_flt.utilization_type = 'ACCRUAL' THEN
927: IF l_funds_util_flt.reference_type = 'LEAD_REFERRAL' THEN
928: FND_DSQL.add_text(' AND fu.utilization_type IN (''LEAD_ACCRUAL'', ''LEAD_ADJUSTMENT'') ' );
929: ELSIF l_funds_util_flt.reference_type = 'BATCH' THEN
930: FND_DSQL.add_text(' AND fu.utilization_type = ''CHARGEBACK'' ');
931: ELSE
932: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );
933: END IF;
934: ELSE
928: FND_DSQL.add_text(' AND fu.utilization_type IN (''LEAD_ACCRUAL'', ''LEAD_ADJUSTMENT'') ' );
929: ELSIF l_funds_util_flt.reference_type = 'BATCH' THEN
930: FND_DSQL.add_text(' AND fu.utilization_type = ''CHARGEBACK'' ');
931: ELSE
932: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );
933: END IF;
934: ELSE
935: FND_DSQL.add_text(' AND fu.utilization_type = ');
936: FND_DSQL.add_bind(l_funds_util_flt.utilization_type);
931: ELSE
932: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );
933: END IF;
934: ELSE
935: FND_DSQL.add_text(' AND fu.utilization_type = ');
936: FND_DSQL.add_bind(l_funds_util_flt.utilization_type);
937: END IF;
938:
939: IF l_funds_util_flt.utilization_type = 'ADJUSTMENT' THEN
932: FND_DSQL.add_text(' AND fu.utilization_type IN (''ACCRUAL'', ''ADJUSTMENT'') ' );
933: END IF;
934: ELSE
935: FND_DSQL.add_text(' AND fu.utilization_type = ');
936: FND_DSQL.add_bind(l_funds_util_flt.utilization_type);
937: END IF;
938:
939: IF l_funds_util_flt.utilization_type = 'ADJUSTMENT' THEN
940: FND_DSQL.add_text(' AND fu.cust_account_id IS NULL ');
936: FND_DSQL.add_bind(l_funds_util_flt.utilization_type);
937: END IF;
938:
939: IF l_funds_util_flt.utilization_type = 'ADJUSTMENT' THEN
940: FND_DSQL.add_text(' AND fu.cust_account_id IS NULL ');
941: ELSE
942: -- bug fix 4338584
943: 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
944: FND_DSQL.add_text(' AND fu.cust_account_id');
940: FND_DSQL.add_text(' AND fu.cust_account_id IS NULL ');
941: ELSE
942: -- bug fix 4338584
943: 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
944: FND_DSQL.add_text(' AND fu.cust_account_id');
945:
946: Get_Customer_For_Earnings(
947: p_cust_account_id => l_funds_util_flt.cust_account_id
948: ,p_relationship_type => l_funds_util_flt.relationship_type
951: ,p_select_cust_children_flag => l_funds_util_flt.select_cust_children_flag
952: );
953:
954: ELSIF l_cust_account_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
955: FND_DSQL.add_text(' AND fu.cust_account_id = ');
956: FND_DSQL.add_bind(l_cust_account_id);
957: END IF;
958: END IF;
959:
952: );
953:
954: ELSIF l_cust_account_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
955: FND_DSQL.add_text(' AND fu.cust_account_id = ');
956: FND_DSQL.add_bind(l_cust_account_id);
957: END IF;
958: END IF;
959:
960: -- Add fund_id as search filter for claim autopay program.
958: END IF;
959:
960: -- Add fund_id as search filter for claim autopay program.
961: IF l_funds_util_flt.fund_id IS NOT NULL THEN
962: FND_DSQL.add_text(' AND fu.fund_id = ');
963: FND_DSQL.add_bind(l_funds_util_flt.fund_id);
964: END IF;
965:
966: IF l_funds_util_flt.activity_id IS NOT NULL THEN
959:
960: -- Add fund_id as search filter for claim autopay program.
961: IF l_funds_util_flt.fund_id IS NOT NULL THEN
962: FND_DSQL.add_text(' AND fu.fund_id = ');
963: FND_DSQL.add_bind(l_funds_util_flt.fund_id);
964: END IF;
965:
966: IF l_funds_util_flt.activity_id IS NOT NULL THEN
967: FND_DSQL.add_text(' AND fu.plan_id = ');
963: FND_DSQL.add_bind(l_funds_util_flt.fund_id);
964: END IF;
965:
966: IF l_funds_util_flt.activity_id IS NOT NULL THEN
967: FND_DSQL.add_text(' AND fu.plan_id = ');
968: FND_DSQL.add_bind(l_funds_util_flt.activity_id);
969: END IF;
970:
971: IF l_funds_util_flt.reference_type IS NOT NULL THEN
964: END IF;
965:
966: IF l_funds_util_flt.activity_id IS NOT NULL THEN
967: FND_DSQL.add_text(' AND fu.plan_id = ');
968: FND_DSQL.add_bind(l_funds_util_flt.activity_id);
969: END IF;
970:
971: IF l_funds_util_flt.reference_type IS NOT NULL THEN
972: FND_DSQL.add_text(' AND fu.reference_type = ');
968: FND_DSQL.add_bind(l_funds_util_flt.activity_id);
969: END IF;
970:
971: IF l_funds_util_flt.reference_type IS NOT NULL THEN
972: FND_DSQL.add_text(' AND fu.reference_type = ');
973: FND_DSQL.add_bind(l_funds_util_flt.reference_type);
974: END IF;
975:
976: IF l_funds_util_flt.reference_id IS NOT NULL THEN
969: END IF;
970:
971: IF l_funds_util_flt.reference_type IS NOT NULL THEN
972: FND_DSQL.add_text(' AND fu.reference_type = ');
973: FND_DSQL.add_bind(l_funds_util_flt.reference_type);
974: END IF;
975:
976: IF l_funds_util_flt.reference_id IS NOT NULL THEN
977: FND_DSQL.add_text(' AND fu.reference_id = ');
973: FND_DSQL.add_bind(l_funds_util_flt.reference_type);
974: END IF;
975:
976: IF l_funds_util_flt.reference_id IS NOT NULL THEN
977: FND_DSQL.add_text(' AND fu.reference_id = ');
978: FND_DSQL.add_bind(l_funds_util_flt.reference_id);
979: END IF;
980:
981: IF l_funds_util_flt.activity_product_id IS NOT NULL THEN
974: END IF;
975:
976: IF l_funds_util_flt.reference_id IS NOT NULL THEN
977: FND_DSQL.add_text(' AND fu.reference_id = ');
978: FND_DSQL.add_bind(l_funds_util_flt.reference_id);
979: END IF;
980:
981: IF l_funds_util_flt.activity_product_id IS NOT NULL THEN
982: FND_DSQL.add_text(' AND fu.activity_product_id = ');
978: FND_DSQL.add_bind(l_funds_util_flt.reference_id);
979: END IF;
980:
981: IF l_funds_util_flt.activity_product_id IS NOT NULL THEN
982: FND_DSQL.add_text(' AND fu.activity_product_id = ');
983: FND_DSQL.add_bind(l_funds_util_flt.activity_product_id);
984: END IF;
985:
986: IF l_funds_util_flt.schedule_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
979: END IF;
980:
981: IF l_funds_util_flt.activity_product_id IS NOT NULL THEN
982: FND_DSQL.add_text(' AND fu.activity_product_id = ');
983: FND_DSQL.add_bind(l_funds_util_flt.activity_product_id);
984: END IF;
985:
986: IF l_funds_util_flt.schedule_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
987: FND_DSQL.add_text(' AND fu.component_type = ''CSCH'' ');
983: FND_DSQL.add_bind(l_funds_util_flt.activity_product_id);
984: END IF;
985:
986: IF l_funds_util_flt.schedule_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
987: FND_DSQL.add_text(' AND fu.component_type = ''CSCH'' ');
988: FND_DSQL.add_text(' AND fu.component_id = ');
989: FND_DSQL.add_bind(l_funds_util_flt.schedule_id);
990: END IF;
991:
984: END IF;
985:
986: IF l_funds_util_flt.schedule_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
987: FND_DSQL.add_text(' AND fu.component_type = ''CSCH'' ');
988: FND_DSQL.add_text(' AND fu.component_id = ');
989: FND_DSQL.add_bind(l_funds_util_flt.schedule_id);
990: END IF;
991:
992: -- fix for 4308165
985:
986: IF l_funds_util_flt.schedule_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
987: FND_DSQL.add_text(' AND fu.component_type = ''CSCH'' ');
988: FND_DSQL.add_text(' AND fu.component_id = ');
989: FND_DSQL.add_bind(l_funds_util_flt.schedule_id);
990: END IF;
991:
992: -- fix for 4308165
993: -- modified for bugfix 4990767
992: -- fix for 4308165
993: -- modified for bugfix 4990767
994: IF l_funds_util_flt.document_class IS NOT NULL AND l_scan_data_flag = 'N' THEN
995: IF l_funds_util_flt.document_class IN ('ORDER','TP_ORDER') THEN
996: FND_DSQL.add_text(' AND fu.object_type = ');
997: FND_DSQL.add_bind(l_funds_util_flt.document_class);
998: END IF;
999: END IF;
1000:
993: -- modified for bugfix 4990767
994: IF l_funds_util_flt.document_class IS NOT NULL AND l_scan_data_flag = 'N' THEN
995: IF l_funds_util_flt.document_class IN ('ORDER','TP_ORDER') THEN
996: FND_DSQL.add_text(' AND fu.object_type = ');
997: FND_DSQL.add_bind(l_funds_util_flt.document_class);
998: END IF;
999: END IF;
1000:
1001: IF l_funds_util_flt.document_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
999: END IF;
1000:
1001: IF l_funds_util_flt.document_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1002: IF l_funds_util_flt.document_class = 'TP_ORDER' THEN
1003: FND_DSQL.add_text(' AND fu.object_id IN (SELECT chargeback_line_id FROM ozf_chargeback_lines WHERE chargeback_header_id = ');
1004: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1005: FND_DSQL.add_text(') ');
1006: ELSE
1007: IF l_funds_util_flt.document_class = 'ORDER' THEN
1000:
1001: IF l_funds_util_flt.document_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1002: IF l_funds_util_flt.document_class = 'TP_ORDER' THEN
1003: FND_DSQL.add_text(' AND fu.object_id IN (SELECT chargeback_line_id FROM ozf_chargeback_lines WHERE chargeback_header_id = ');
1004: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1005: FND_DSQL.add_text(') ');
1006: ELSE
1007: IF l_funds_util_flt.document_class = 'ORDER' THEN
1008: FND_DSQL.add_text(' AND fu.object_id = ');
1001: IF l_funds_util_flt.document_id IS NOT NULL AND l_scan_data_flag = 'N' THEN
1002: IF l_funds_util_flt.document_class = 'TP_ORDER' THEN
1003: FND_DSQL.add_text(' AND fu.object_id IN (SELECT chargeback_line_id FROM ozf_chargeback_lines WHERE chargeback_header_id = ');
1004: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1005: FND_DSQL.add_text(') ');
1006: ELSE
1007: IF l_funds_util_flt.document_class = 'ORDER' THEN
1008: FND_DSQL.add_text(' AND fu.object_id = ');
1009: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1004: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1005: FND_DSQL.add_text(') ');
1006: ELSE
1007: IF l_funds_util_flt.document_class = 'ORDER' THEN
1008: FND_DSQL.add_text(' AND fu.object_id = ');
1009: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1010: END IF;
1011: END IF;
1012: END IF;
1005: FND_DSQL.add_text(') ');
1006: ELSE
1007: IF l_funds_util_flt.document_class = 'ORDER' THEN
1008: FND_DSQL.add_text(' AND fu.object_id = ');
1009: FND_DSQL.add_bind(l_funds_util_flt.document_id);
1010: END IF;
1011: END IF;
1012: END IF;
1013:
1013:
1014: IF (l_funds_util_flt.product_level_type = 'PRODUCT' OR
1015: l_funds_util_flt.product_level_type IS NULL) AND
1016: l_funds_util_flt.product_id IS NOT NULL THEN
1017: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');
1018: FND_DSQL.add_text(' AND fu.product_id = ');
1019: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1020: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1021: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1014: IF (l_funds_util_flt.product_level_type = 'PRODUCT' OR
1015: l_funds_util_flt.product_level_type IS NULL) AND
1016: l_funds_util_flt.product_id IS NOT NULL THEN
1017: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');
1018: FND_DSQL.add_text(' AND fu.product_id = ');
1019: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1020: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1021: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1022: FND_DSQL.add_text(' where inventory_item_id = ');
1015: l_funds_util_flt.product_level_type IS NULL) AND
1016: l_funds_util_flt.product_id IS NOT NULL THEN
1017: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');
1018: FND_DSQL.add_text(' AND fu.product_id = ');
1019: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1020: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1021: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1022: FND_DSQL.add_text(' where inventory_item_id = ');
1023: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1016: l_funds_util_flt.product_id IS NOT NULL THEN
1017: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');
1018: FND_DSQL.add_text(' AND fu.product_id = ');
1019: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1020: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1021: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1022: FND_DSQL.add_text(' where inventory_item_id = ');
1023: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1024: FND_DSQL.add_text(' and organization_id = ');
1017: FND_DSQL.add_text(' AND ((fu.product_level_type = ''PRODUCT'' ');
1018: FND_DSQL.add_text(' AND fu.product_id = ');
1019: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1020: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1021: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1022: FND_DSQL.add_text(' where inventory_item_id = ');
1023: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1024: FND_DSQL.add_text(' and organization_id = ');
1025: FND_DSQL.add_bind(l_org_id);
1018: FND_DSQL.add_text(' AND fu.product_id = ');
1019: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1020: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1021: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1022: FND_DSQL.add_text(' where inventory_item_id = ');
1023: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1024: FND_DSQL.add_text(' and organization_id = ');
1025: FND_DSQL.add_bind(l_org_id);
1026: FND_DSQL.add_text(' ))) ');
1019: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1020: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1021: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1022: FND_DSQL.add_text(' where inventory_item_id = ');
1023: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1024: FND_DSQL.add_text(' and organization_id = ');
1025: FND_DSQL.add_bind(l_org_id);
1026: FND_DSQL.add_text(' ))) ');
1027: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1020: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''FAMILY'' ');
1021: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1022: FND_DSQL.add_text(' where inventory_item_id = ');
1023: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1024: FND_DSQL.add_text(' and organization_id = ');
1025: FND_DSQL.add_bind(l_org_id);
1026: FND_DSQL.add_text(' ))) ');
1027: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1028: l_funds_util_flt.product_id IS NOT NULL THEN
1021: FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
1022: FND_DSQL.add_text(' where inventory_item_id = ');
1023: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1024: FND_DSQL.add_text(' and organization_id = ');
1025: FND_DSQL.add_bind(l_org_id);
1026: FND_DSQL.add_text(' ))) ');
1027: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1028: l_funds_util_flt.product_id IS NOT NULL THEN
1029: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1022: FND_DSQL.add_text(' where inventory_item_id = ');
1023: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1024: FND_DSQL.add_text(' and organization_id = ');
1025: FND_DSQL.add_bind(l_org_id);
1026: FND_DSQL.add_text(' ))) ');
1027: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1028: l_funds_util_flt.product_id IS NOT NULL THEN
1029: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1030: FND_DSQL.add_text(' AND fu.product_id = ');
1025: FND_DSQL.add_bind(l_org_id);
1026: FND_DSQL.add_text(' ))) ');
1027: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1028: l_funds_util_flt.product_id IS NOT NULL THEN
1029: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1030: FND_DSQL.add_text(' AND fu.product_id = ');
1031: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1032: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1033: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1026: FND_DSQL.add_text(' ))) ');
1027: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1028: l_funds_util_flt.product_id IS NOT NULL THEN
1029: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1030: FND_DSQL.add_text(' AND fu.product_id = ');
1031: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1032: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1033: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1034: FND_DSQL.add_text(' where a.parent_id = ');
1027: ELSIF l_funds_util_flt.product_level_type = 'FAMILY' AND
1028: l_funds_util_flt.product_id IS NOT NULL THEN
1029: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1030: FND_DSQL.add_text(' AND fu.product_id = ');
1031: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1032: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1033: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1034: FND_DSQL.add_text(' where a.parent_id = ');
1035: FND_DSQL.add_text(l_funds_util_flt.product_id);
1028: l_funds_util_flt.product_id IS NOT NULL THEN
1029: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1030: FND_DSQL.add_text(' AND fu.product_id = ');
1031: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1032: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1033: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1034: FND_DSQL.add_text(' where a.parent_id = ');
1035: FND_DSQL.add_text(l_funds_util_flt.product_id);
1036: FND_DSQL.add_text(' and b.organization_id = ');
1029: FND_DSQL.add_text(' AND ((fu.product_level_type = ''FAMILY'' ');
1030: FND_DSQL.add_text(' AND fu.product_id = ');
1031: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1032: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1033: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1034: FND_DSQL.add_text(' where a.parent_id = ');
1035: FND_DSQL.add_text(l_funds_util_flt.product_id);
1036: FND_DSQL.add_text(' and b.organization_id = ');
1037: FND_DSQL.add_bind(l_org_id);
1030: FND_DSQL.add_text(' AND fu.product_id = ');
1031: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1032: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1033: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1034: FND_DSQL.add_text(' where a.parent_id = ');
1035: FND_DSQL.add_text(l_funds_util_flt.product_id);
1036: FND_DSQL.add_text(' and b.organization_id = ');
1037: FND_DSQL.add_bind(l_org_id);
1038: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1031: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1032: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1033: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1034: FND_DSQL.add_text(' where a.parent_id = ');
1035: FND_DSQL.add_text(l_funds_util_flt.product_id);
1036: FND_DSQL.add_text(' and b.organization_id = ');
1037: FND_DSQL.add_bind(l_org_id);
1038: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1039: FND_DSQL.add_text(' ))) ');
1032: FND_DSQL.add_text(' ) OR (fu.product_level_type = ''PRODUCT'' ');
1033: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1034: FND_DSQL.add_text(' where a.parent_id = ');
1035: FND_DSQL.add_text(l_funds_util_flt.product_id);
1036: FND_DSQL.add_text(' and b.organization_id = ');
1037: FND_DSQL.add_bind(l_org_id);
1038: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1039: FND_DSQL.add_text(' ))) ');
1040: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1033: FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
1034: FND_DSQL.add_text(' where a.parent_id = ');
1035: FND_DSQL.add_text(l_funds_util_flt.product_id);
1036: FND_DSQL.add_text(' and b.organization_id = ');
1037: FND_DSQL.add_bind(l_org_id);
1038: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1039: FND_DSQL.add_text(' ))) ');
1040: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1041: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1034: FND_DSQL.add_text(' where a.parent_id = ');
1035: FND_DSQL.add_text(l_funds_util_flt.product_id);
1036: FND_DSQL.add_text(' and b.organization_id = ');
1037: FND_DSQL.add_bind(l_org_id);
1038: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1039: FND_DSQL.add_text(' ))) ');
1040: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1041: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1042: IF l_funds_util_flt.product_id IS NOT NULL THEN
1035: FND_DSQL.add_text(l_funds_util_flt.product_id);
1036: FND_DSQL.add_text(' and b.organization_id = ');
1037: FND_DSQL.add_bind(l_org_id);
1038: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1039: FND_DSQL.add_text(' ))) ');
1040: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1041: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1042: IF l_funds_util_flt.product_id IS NOT NULL THEN
1043: FND_DSQL.add_text(' AND fu.product_id = ');
1037: FND_DSQL.add_bind(l_org_id);
1038: FND_DSQL.add_text(' and a.object_type = ''CATEGORY_SET'' and b.category_id = a.child_id ');
1039: FND_DSQL.add_text(' ))) ');
1040: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1041: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1042: IF l_funds_util_flt.product_id IS NOT NULL THEN
1043: FND_DSQL.add_text(' AND fu.product_id = ');
1044: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1045: END IF;
1039: FND_DSQL.add_text(' ))) ');
1040: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1041: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1042: IF l_funds_util_flt.product_id IS NOT NULL THEN
1043: FND_DSQL.add_text(' AND fu.product_id = ');
1044: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1045: END IF;
1046: END IF;
1047:
1040: ELSIF l_funds_util_flt.product_level_type = 'MEDIA' THEN
1041: FND_DSQL.add_text(' AND fu.product_level_type = ''MEDIA'' ');
1042: IF l_funds_util_flt.product_id IS NOT NULL THEN
1043: FND_DSQL.add_text(' AND fu.product_id = ');
1044: FND_DSQL.add_bind(l_funds_util_flt.product_id);
1045: END IF;
1046: END IF;
1047:
1048: IF l_funds_util_flt.end_date IS NOT NULL THEN
1045: END IF;
1046: END IF;
1047:
1048: IF l_funds_util_flt.end_date IS NOT NULL THEN
1049: FND_DSQL.add_text(' AND trunc(fu.creation_date) <= ');
1050: FND_DSQL.add_bind(l_funds_util_flt.end_date);
1051: END IF;
1052:
1053: FND_DSQL.add_text(' AND fu.gl_posted_flag = ''Y'' ');
1046: END IF;
1047:
1048: IF l_funds_util_flt.end_date IS NOT NULL THEN
1049: FND_DSQL.add_text(' AND trunc(fu.creation_date) <= ');
1050: FND_DSQL.add_bind(l_funds_util_flt.end_date);
1051: END IF;
1052:
1053: FND_DSQL.add_text(' AND fu.gl_posted_flag = ''Y'' ');
1054:
1049: FND_DSQL.add_text(' AND trunc(fu.creation_date) <= ');
1050: FND_DSQL.add_bind(l_funds_util_flt.end_date);
1051: END IF;
1052:
1053: FND_DSQL.add_text(' AND fu.gl_posted_flag = ''Y'' ');
1054:
1055: END Get_Utiz_Sql_Stmt_Where_Clause;
1056:
1057: ---------------------------------------------------------------------
1124: l_sales_rep := ozf_utility_pvt.has_sales_rep_role(l_resource_id);
1125: END IF;
1126:
1127:
1128: FND_DSQL.add_text('SELECT fu.utilization_id, fu.cust_account_id '||
1129: ', fu.plan_type, fu.plan_id, o.offer_type, o.autopay_method '||
1130: ', fu.product_level_type, fu.product_id '||
1131: ', fu.acctd_amount_remaining, fu.amount_remaining, fu.scan_unit_remaining '||
1132: ', fu.creation_date , fu.currency_code , fu.bill_to_site_use_id '||
1132: ', fu.creation_date , fu.currency_code , fu.bill_to_site_use_id '||
1133: 'FROM ozf_funds_utilized_all_b fu, ozf_offers o ');
1134:
1135: --Modified for Bugfix 5346249
1136: FND_DSQL.add_text('WHERE fu.plan_type = ''OFFR'' '||
1137: 'AND fu.plan_id = o.qp_list_header_id ' ||
1138: 'AND fu.org_id = ');
1139: FND_DSQL.add_bind(l_orgId);
1140:
1135: --Modified for Bugfix 5346249
1136: FND_DSQL.add_text('WHERE fu.plan_type = ''OFFR'' '||
1137: 'AND fu.plan_id = o.qp_list_header_id ' ||
1138: 'AND fu.org_id = ');
1139: FND_DSQL.add_bind(l_orgId);
1140:
1141: IF l_funds_util_flt.offer_type IS NOT NULL THEN
1142: FND_DSQL.add_text(' AND o.offer_type = ');
1143: FND_DSQL.add_bind(l_funds_util_flt.offer_type);
1138: 'AND fu.org_id = ');
1139: FND_DSQL.add_bind(l_orgId);
1140:
1141: IF l_funds_util_flt.offer_type IS NOT NULL THEN
1142: FND_DSQL.add_text(' AND o.offer_type = ');
1143: FND_DSQL.add_bind(l_funds_util_flt.offer_type);
1144: ELSE
1145: FND_DSQL.add_text(' AND o.offer_type <> ''SCAN_DATA'' ');
1146: END IF;
1139: FND_DSQL.add_bind(l_orgId);
1140:
1141: IF l_funds_util_flt.offer_type IS NOT NULL THEN
1142: FND_DSQL.add_text(' AND o.offer_type = ');
1143: FND_DSQL.add_bind(l_funds_util_flt.offer_type);
1144: ELSE
1145: FND_DSQL.add_text(' AND o.offer_type <> ''SCAN_DATA'' ');
1146: END IF;
1147:
1141: IF l_funds_util_flt.offer_type IS NOT NULL THEN
1142: FND_DSQL.add_text(' AND o.offer_type = ');
1143: FND_DSQL.add_bind(l_funds_util_flt.offer_type);
1144: ELSE
1145: FND_DSQL.add_text(' AND o.offer_type <> ''SCAN_DATA'' ');
1146: END IF;
1147:
1148: IF l_funds_util_flt.run_mode = 'OFFER_AUTOPAY' THEN
1149: FND_DSQL.add_text(' AND o.autopay_flag = ''Y'' ');
1145: FND_DSQL.add_text(' AND o.offer_type <> ''SCAN_DATA'' ');
1146: END IF;
1147:
1148: IF l_funds_util_flt.run_mode = 'OFFER_AUTOPAY' THEN
1149: FND_DSQL.add_text(' AND o.autopay_flag = ''Y'' ');
1150: ELSIF l_funds_util_flt.run_mode = 'OFFER_NO_AUTOPAY' THEN
1151: FND_DSQL.add_text(' AND (o.autopay_flag IS NULL OR o.autopay_flag = ''N'') ');
1152: END IF;
1153:
1147:
1148: IF l_funds_util_flt.run_mode = 'OFFER_AUTOPAY' THEN
1149: FND_DSQL.add_text(' AND o.autopay_flag = ''Y'' ');
1150: ELSIF l_funds_util_flt.run_mode = 'OFFER_NO_AUTOPAY' THEN
1151: FND_DSQL.add_text(' AND (o.autopay_flag IS NULL OR o.autopay_flag = ''N'') ');
1152: END IF;
1153:
1154: IF l_funds_util_flt.offer_payment_method IS NOT NULL THEN
1155: IF l_funds_util_flt.offer_payment_method = 'NULL' THEN
1152: END IF;
1153:
1154: IF l_funds_util_flt.offer_payment_method IS NOT NULL THEN
1155: IF l_funds_util_flt.offer_payment_method = 'NULL' THEN
1156: FND_DSQL.add_text(' AND o.autopay_method IS NULL ');
1157: ELSE
1158: FND_DSQL.add_text(' AND o.autopay_method = ');
1159: FND_DSQL.add_bind(l_funds_util_flt.offer_payment_method);
1160: END IF;
1154: IF l_funds_util_flt.offer_payment_method IS NOT NULL THEN
1155: IF l_funds_util_flt.offer_payment_method = 'NULL' THEN
1156: FND_DSQL.add_text(' AND o.autopay_method IS NULL ');
1157: ELSE
1158: FND_DSQL.add_text(' AND o.autopay_method = ');
1159: FND_DSQL.add_bind(l_funds_util_flt.offer_payment_method);
1160: END IF;
1161: END IF;
1162:
1155: IF l_funds_util_flt.offer_payment_method = 'NULL' THEN
1156: FND_DSQL.add_text(' AND o.autopay_method IS NULL ');
1157: ELSE
1158: FND_DSQL.add_text(' AND o.autopay_method = ');
1159: FND_DSQL.add_bind(l_funds_util_flt.offer_payment_method);
1160: END IF;
1161: END IF;
1162:
1163: IF (l_sales_rep = FND_API.g_true AND NOT l_is_admin ) THEN
1160: END IF;
1161: END IF;
1162:
1163: IF (l_sales_rep = FND_API.g_true AND NOT l_is_admin ) THEN
1164: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');
1165: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1166: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1167: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1168: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1161: END IF;
1162:
1163: IF (l_sales_rep = FND_API.g_true AND NOT l_is_admin ) THEN
1164: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');
1165: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1166: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1167: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1168: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1169: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1162:
1163: IF (l_sales_rep = FND_API.g_true AND NOT l_is_admin ) THEN
1164: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');
1165: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1166: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1167: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1168: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1169: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1170: FND_DSQL.add_text(' AND act.resource_id= ');
1163: IF (l_sales_rep = FND_API.g_true AND NOT l_is_admin ) THEN
1164: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');
1165: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1166: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1167: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1168: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1169: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1170: FND_DSQL.add_text(' AND act.resource_id= ');
1171: FND_DSQL.add_bind(l_resource_id);
1164: FND_DSQL.add_text(' AND (o.confidential_flag =''N'' OR ');
1165: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1166: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1167: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1168: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1169: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1170: FND_DSQL.add_text(' AND act.resource_id= ');
1171: FND_DSQL.add_bind(l_resource_id);
1172: FND_DSQL.add_text('))');
1165: FND_DSQL.add_text(' o.confidential_flag IS NULL OR ');
1166: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1167: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1168: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1169: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1170: FND_DSQL.add_text(' AND act.resource_id= ');
1171: FND_DSQL.add_bind(l_resource_id);
1172: FND_DSQL.add_text('))');
1173: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1166: FND_DSQL.add_text(' ( NVL(o.budget_offer_yn, ''N'') = ''N'' AND ');
1167: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1168: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1169: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1170: FND_DSQL.add_text(' AND act.resource_id= ');
1171: FND_DSQL.add_bind(l_resource_id);
1172: FND_DSQL.add_text('))');
1173: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1174: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1167: FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1168: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1169: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1170: FND_DSQL.add_text(' AND act.resource_id= ');
1171: FND_DSQL.add_bind(l_resource_id);
1172: FND_DSQL.add_text('))');
1173: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1174: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1175: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1168: FND_DSQL.add_text(' WHERE act.object_id = o.qp_list_header_id ');
1169: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1170: FND_DSQL.add_text(' AND act.resource_id= ');
1171: FND_DSQL.add_bind(l_resource_id);
1172: FND_DSQL.add_text('))');
1173: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1174: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1175: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1176: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1169: FND_DSQL.add_text(' AND act.object_type = ''OFFR'' ');
1170: FND_DSQL.add_text(' AND act.resource_id= ');
1171: FND_DSQL.add_bind(l_resource_id);
1172: FND_DSQL.add_text('))');
1173: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1174: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1175: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1176: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1177: FND_DSQL.add_text(' AND act.resource_id= ' );
1170: FND_DSQL.add_text(' AND act.resource_id= ');
1171: FND_DSQL.add_bind(l_resource_id);
1172: FND_DSQL.add_text('))');
1173: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1174: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1175: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1176: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1177: FND_DSQL.add_text(' AND act.resource_id= ' );
1178: FND_DSQL.add_bind(l_resource_id);
1171: FND_DSQL.add_bind(l_resource_id);
1172: FND_DSQL.add_text('))');
1173: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1174: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1175: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1176: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1177: FND_DSQL.add_text(' AND act.resource_id= ' );
1178: FND_DSQL.add_bind(l_resource_id);
1179: FND_DSQL.add_text(')))');
1172: FND_DSQL.add_text('))');
1173: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1174: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1175: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1176: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1177: FND_DSQL.add_text(' AND act.resource_id= ' );
1178: FND_DSQL.add_bind(l_resource_id);
1179: FND_DSQL.add_text(')))');
1180: END IF;
1173: FND_DSQL.add_text(' OR ( NVL(o.budget_offer_yn, ''N'') = ''Y'' ');
1174: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1175: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1176: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1177: FND_DSQL.add_text(' AND act.resource_id= ' );
1178: FND_DSQL.add_bind(l_resource_id);
1179: FND_DSQL.add_text(')))');
1180: END IF;
1181:
1174: FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
1175: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1176: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1177: FND_DSQL.add_text(' AND act.resource_id= ' );
1178: FND_DSQL.add_bind(l_resource_id);
1179: FND_DSQL.add_text(')))');
1180: END IF;
1181:
1182: Get_Utiz_Sql_Stmt_Where_Clause (
1175: FND_DSQL.add_text(' WHERE act.object_id = fu.fund_id ');
1176: FND_DSQL.add_text(' AND act.object_type = ''FUND'' ');
1177: FND_DSQL.add_text(' AND act.resource_id= ' );
1178: FND_DSQL.add_bind(l_resource_id);
1179: FND_DSQL.add_text(')))');
1180: END IF;
1181:
1182: Get_Utiz_Sql_Stmt_Where_Clause (
1183: p_summary_view => p_summary_view
1186:
1187: END IF;
1188:
1189: IF l_offer_flag = 'Y' AND l_price_list_flag = 'Y' THEN
1190: FND_DSQL.add_text('UNION ALL ');
1191: END IF;
1192:
1193: IF l_price_list_flag = 'Y' THEN
1194: FND_DSQL.add_text('SELECT fu.utilization_id, fu.cust_account_id '||
1190: FND_DSQL.add_text('UNION ALL ');
1191: END IF;
1192:
1193: IF l_price_list_flag = 'Y' THEN
1194: FND_DSQL.add_text('SELECT fu.utilization_id, fu.cust_account_id '||
1195: ', fu.plan_type, fu.plan_id, null, null '||
1196: ', fu.product_level_type, fu.product_id '||
1197: ', fu.acctd_amount_remaining, fu.amount_remaining, fu.scan_unit_remaining '||
1198: ', fu.creation_date , fu.currency_code , fu.bill_to_site_use_id '||
1198: ', fu.creation_date , fu.currency_code , fu.bill_to_site_use_id '||
1199: 'FROM ozf_funds_utilized_all_b fu '||
1200: 'WHERE fu.plan_type = ''PRIC'' ' ||
1201: 'AND fu.org_id =');
1202: FND_DSQL.add_bind(l_orgId);
1203: FND_DSQL.add_text(' AND fu.cust_account_id = ');
1204: FND_DSQL.add_bind(l_funds_util_flt.cust_account_id);
1205:
1206:
1199: 'FROM ozf_funds_utilized_all_b fu '||
1200: 'WHERE fu.plan_type = ''PRIC'' ' ||
1201: 'AND fu.org_id =');
1202: FND_DSQL.add_bind(l_orgId);
1203: FND_DSQL.add_text(' AND fu.cust_account_id = ');
1204: FND_DSQL.add_bind(l_funds_util_flt.cust_account_id);
1205:
1206:
1207: Get_Utiz_Sql_Stmt_Where_Clause(
1200: 'WHERE fu.plan_type = ''PRIC'' ' ||
1201: 'AND fu.org_id =');
1202: FND_DSQL.add_bind(l_orgId);
1203: FND_DSQL.add_text(' AND fu.cust_account_id = ');
1204: FND_DSQL.add_bind(l_funds_util_flt.cust_account_id);
1205:
1206:
1207: Get_Utiz_Sql_Stmt_Where_Clause(
1208: p_summary_view => p_summary_view
1323: OZF_Utility_PVT.debug_message('product_id : ' || l_funds_util_flt.product_id);
1324: OZF_Utility_PVT.debug_message('----------------------------');
1325: END IF;
1326:
1327: -- use FND_DSQL package to handle dynamic sql and bind variables
1328: FND_DSQL.init;
1329:
1330: IF p_funds_util_flt.offer_type = 'SCAN_DATA' THEN
1331: l_scan_data_flag := 'Y';
1324: OZF_Utility_PVT.debug_message('----------------------------');
1325: END IF;
1326:
1327: -- use FND_DSQL package to handle dynamic sql and bind variables
1328: FND_DSQL.init;
1329:
1330: IF p_funds_util_flt.offer_type = 'SCAN_DATA' THEN
1331: l_scan_data_flag := 'Y';
1332: END IF;
1339: -- Also need to group by currency as amount of different currencies cannot be added
1340: -- Modified for R12.1 enhancements, Need to select the value of bill_to_site_use_id.
1341:
1342: IF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1343: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code, bill_to_site_use_id ');
1344: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1345: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id ');
1346: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1347: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');
1341:
1342: IF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1343: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code, bill_to_site_use_id ');
1344: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1345: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id ');
1346: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1347: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');
1348: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1349: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');
1343: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code, bill_to_site_use_id ');
1344: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1345: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id ');
1346: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1347: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');
1348: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1349: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');
1350: END IF;
1351: FND_DSQL.add_text( 'FROM (');
1345: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id ');
1346: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1347: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');
1348: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1349: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');
1350: END IF;
1351: FND_DSQL.add_text( 'FROM (');
1352: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1353: FND_DSQL.add_text( 'SELECT cust_account_id, plan_type, plan_id, bill_to_site_use_id '||
1347: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');
1348: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1349: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');
1350: END IF;
1351: FND_DSQL.add_text( 'FROM (');
1352: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1353: FND_DSQL.add_text( 'SELECT cust_account_id, plan_type, plan_id, bill_to_site_use_id '||
1354: ', product_level_type, product_id '||
1355: ', sum(amount_remaining), currency_code '||
1349: FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');
1350: END IF;
1351: FND_DSQL.add_text( 'FROM (');
1352: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1353: FND_DSQL.add_text( 'SELECT cust_account_id, plan_type, plan_id, bill_to_site_use_id '||
1354: ', product_level_type, product_id '||
1355: ', sum(amount_remaining), currency_code '||
1356: 'FROM ('||
1357: 'SELECT cust_account_id, plan_type, plan_id '||
1360: ', acctd_amount_remaining , amount_remaining, currency_code, bill_to_site_use_id '||
1361: 'FROM (');
1362: ELSIF p_summary_view = 'DEL_GRP_LINE_UTIL' THEN
1363: -- Modified for FXGL ER(amount selected)
1364: FND_DSQL.add_text( 'SELECT lu.claim_line_util_id, lu.utilization_id, lu.amount, lu.scan_unit, lu.currency_code '||
1365: 'FROM (');
1366: ELSE
1367: -- Modified for FXGL ER
1368: FND_DSQL.add_text( 'SELECT utilization_id, amount_remaining, scan_unit_remaining, currency_code '||
1364: FND_DSQL.add_text( 'SELECT lu.claim_line_util_id, lu.utilization_id, lu.amount, lu.scan_unit, lu.currency_code '||
1365: 'FROM (');
1366: ELSE
1367: -- Modified for FXGL ER
1368: FND_DSQL.add_text( 'SELECT utilization_id, amount_remaining, scan_unit_remaining, currency_code '||
1369: 'FROM (');
1370: END IF;
1371:
1372: Get_Utiz_Sql_Stmt_From_Clause(
1377: -- R12.1 autopay enhancement, Need to select and group by bill_to_site_use_id.
1378: -- for p_summary_view = AUTOPAY and AUTOPAY_LINE.
1379:
1380: IF p_summary_view = 'AUTOPAY' THEN
1381: FND_DSQL.add_text( ') utiz ');
1382: -- R12 Enhancements: Group By Offer for Autopay.
1383: IF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1384: FND_DSQL.add_text('GROUP BY utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1385: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1380: IF p_summary_view = 'AUTOPAY' THEN
1381: FND_DSQL.add_text( ') utiz ');
1382: -- R12 Enhancements: Group By Offer for Autopay.
1383: IF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1384: FND_DSQL.add_text('GROUP BY utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1385: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1386: FND_DSQL.add_text('GROUP BY utiz.cust_account_id, utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1387: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1388: FND_DSQL.add_text('GROUP BY utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');
1382: -- R12 Enhancements: Group By Offer for Autopay.
1383: IF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1384: FND_DSQL.add_text('GROUP BY utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1385: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1386: FND_DSQL.add_text('GROUP BY utiz.cust_account_id, utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1387: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1388: FND_DSQL.add_text('GROUP BY utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');
1389: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1390: 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 ');
1384: FND_DSQL.add_text('GROUP BY utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1385: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'N' THEN
1386: FND_DSQL.add_text('GROUP BY utiz.cust_account_id, utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1387: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1388: FND_DSQL.add_text('GROUP BY utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');
1389: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1390: 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 ');
1391: END IF;
1392: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1386: FND_DSQL.add_text('GROUP BY utiz.cust_account_id, utiz.autopay_method, utiz.currency_code, utiz.bill_to_site_use_id ');
1387: ELSIF p_cust_account_id IS NOT NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1388: FND_DSQL.add_text('GROUP BY utiz.plan_id, utiz.autopay_method , utiz.currency_code, utiz.bill_to_site_use_id ');
1389: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1390: 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 ');
1391: END IF;
1392: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1393: FND_DSQL.add_text( ') utiz ) '||
1394: 'GROUP BY cust_account_id, plan_type, plan_id, bill_to_site_use_id, product_level_type, product_id, currency_code '||
1389: ELSIF p_cust_account_id IS NULL AND l_funds_util_flt.group_by_offer = 'Y' THEN
1390: 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 ');
1391: END IF;
1392: ELSIF p_summary_view = 'AUTOPAY_LINE' THEN
1393: FND_DSQL.add_text( ') utiz ) '||
1394: 'GROUP BY cust_account_id, plan_type, plan_id, bill_to_site_use_id, product_level_type, product_id, currency_code '||
1395: 'ORDER BY cust_account_id, plan_type, plan_id, bill_to_site_use_id, product_level_type, product_id ');
1396: ELSIF p_summary_view = 'DEL_GRP_LINE_UTIL' THEN
1397: FND_DSQL.add_text( ') utiz, ozf_claim_lines_util lu '||
1393: FND_DSQL.add_text( ') utiz ) '||
1394: 'GROUP BY cust_account_id, plan_type, plan_id, bill_to_site_use_id, product_level_type, product_id, currency_code '||
1395: 'ORDER BY cust_account_id, plan_type, plan_id, bill_to_site_use_id, product_level_type, product_id ');
1396: ELSIF p_summary_view = 'DEL_GRP_LINE_UTIL' THEN
1397: FND_DSQL.add_text( ') utiz, ozf_claim_lines_util lu '||
1398: 'WHERE lu.utilization_id = utiz.utilization_id '||
1399: 'AND lu.claim_line_id = ');
1400: FND_DSQL.add_bind( l_funds_util_flt.claim_line_id );
1401: FND_DSQL.add_text( ' ORDER BY utiz.creation_date desc ');
1396: ELSIF p_summary_view = 'DEL_GRP_LINE_UTIL' THEN
1397: FND_DSQL.add_text( ') utiz, ozf_claim_lines_util lu '||
1398: 'WHERE lu.utilization_id = utiz.utilization_id '||
1399: 'AND lu.claim_line_id = ');
1400: FND_DSQL.add_bind( l_funds_util_flt.claim_line_id );
1401: FND_DSQL.add_text( ' ORDER BY utiz.creation_date desc ');
1402: ELSE
1403: FND_DSQL.add_text( ') utiz ');
1404: IF l_funds_util_flt.total_amount IS NOT NULL THEN
1397: FND_DSQL.add_text( ') utiz, ozf_claim_lines_util lu '||
1398: 'WHERE lu.utilization_id = utiz.utilization_id '||
1399: 'AND lu.claim_line_id = ');
1400: FND_DSQL.add_bind( l_funds_util_flt.claim_line_id );
1401: FND_DSQL.add_text( ' ORDER BY utiz.creation_date desc ');
1402: ELSE
1403: FND_DSQL.add_text( ') utiz ');
1404: IF l_funds_util_flt.total_amount IS NOT NULL THEN
1405: IF l_funds_util_flt.total_amount >= 0 THEN
1399: 'AND lu.claim_line_id = ');
1400: FND_DSQL.add_bind( l_funds_util_flt.claim_line_id );
1401: FND_DSQL.add_text( ' ORDER BY utiz.creation_date desc ');
1402: ELSE
1403: FND_DSQL.add_text( ') utiz ');
1404: IF l_funds_util_flt.total_amount IS NOT NULL THEN
1405: IF l_funds_util_flt.total_amount >= 0 THEN
1406: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) asc, utiz.creation_date asc');
1407: ELSE
1402: ELSE
1403: FND_DSQL.add_text( ') utiz ');
1404: IF l_funds_util_flt.total_amount IS NOT NULL THEN
1405: IF l_funds_util_flt.total_amount >= 0 THEN
1406: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) asc, utiz.creation_date asc');
1407: ELSE
1408: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) desc, utiz.creation_date asc');
1409: END IF;
1410: ELSE
1404: IF l_funds_util_flt.total_amount IS NOT NULL THEN
1405: IF l_funds_util_flt.total_amount >= 0 THEN
1406: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) asc, utiz.creation_date asc');
1407: ELSE
1408: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) desc, utiz.creation_date asc');
1409: END IF;
1410: ELSE
1411: FND_DSQL.add_text(' ORDER BY utiz.creation_date asc');
1412: END IF;
1407: ELSE
1408: FND_DSQL.add_text(' ORDER BY sign(utiz.amount_remaining) desc, utiz.creation_date asc');
1409: END IF;
1410: ELSE
1411: FND_DSQL.add_text(' ORDER BY utiz.creation_date asc');
1412: END IF;
1413: END IF;
1414:
1415: x_utiz_sql_stmt := FND_DSQL.get_text(FALSE);
1411: FND_DSQL.add_text(' ORDER BY utiz.creation_date asc');
1412: END IF;
1413: END IF;
1414:
1415: x_utiz_sql_stmt := FND_DSQL.get_text(FALSE);
1416:
1417: IF OZF_DEBUG_HIGH_ON THEN
1418: l_utiz_sql := FND_DSQL.get_text(TRUE);
1419: OZF_Utility_PVT.debug_message('----- UTIZ SQL -----');
1414:
1415: x_utiz_sql_stmt := FND_DSQL.get_text(FALSE);
1416:
1417: IF OZF_DEBUG_HIGH_ON THEN
1418: l_utiz_sql := FND_DSQL.get_text(TRUE);
1419: OZF_Utility_PVT.debug_message('----- UTIZ SQL -----');
1420: OZF_Utility_PVT.debug_message(SUBSTR(l_utiz_sql, 1, 250));
1421: OZF_Utility_PVT.debug_message(SUBSTR(l_utiz_sql, 251, 250));
1422: OZF_Utility_PVT.debug_message(SUBSTR(l_utiz_sql, 501, 250));
3250: FETCH csr_function_currency INTO G_FUNCTIONAL_CURRENCY;
3251: CLOSE csr_function_currency;
3252:
3253:
3254: -- use FND_DSQL package for dynamic sql and bind variables
3255: l_funds_util_csr := DBMS_SQL.open_cursor;
3256: FND_DSQL.set_cursor(l_funds_util_csr);
3257: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
3258: DBMS_SQL.define_column(l_funds_util_csr, 1, l_lu_line_util_id);
3252:
3253:
3254: -- use FND_DSQL package for dynamic sql and bind variables
3255: l_funds_util_csr := DBMS_SQL.open_cursor;
3256: FND_DSQL.set_cursor(l_funds_util_csr);
3257: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
3258: DBMS_SQL.define_column(l_funds_util_csr, 1, l_lu_line_util_id);
3259: DBMS_SQL.define_column(l_funds_util_csr, 2, l_lu_utilization_id);
3260: DBMS_SQL.define_column(l_funds_util_csr, 3, l_lu_amt);
3260: DBMS_SQL.define_column(l_funds_util_csr, 3, l_lu_amt);
3261: DBMS_SQL.define_column(l_funds_util_csr, 4, l_lu_scan_unit);
3262: DBMS_SQL.define_column(l_funds_util_csr, 5, l_lu_currency_code, 15);
3263: -- DBMS_SQL.define_column(l_funds_util_csr, 5, l_utiz_amount);
3264: FND_DSQL.do_binds;
3265:
3266: l_ignore := DBMS_SQL.execute(l_funds_util_csr);
3267: LOOP
3268: IF DBMS_SQL.fetch_rows(l_funds_util_csr) > 0 THEN
4059:
4060: l_performance_flag := 'T';
4061:
4062: IF p_resale_flag IS NULL OR p_resale_flag = 'F' THEN
4063: FND_DSQL.init;
4064: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4065: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4066: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4067: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4060: l_performance_flag := 'T';
4061:
4062: IF p_resale_flag IS NULL OR p_resale_flag = 'F' THEN
4063: FND_DSQL.init;
4064: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4065: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4066: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4067: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4068: FND_DSQL.add_bind(p_cust_account_id);
4061:
4062: IF p_resale_flag IS NULL OR p_resale_flag = 'F' THEN
4063: FND_DSQL.init;
4064: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4065: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4066: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4067: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4068: FND_DSQL.add_bind(p_cust_account_id);
4069: FND_DSQL.add_text(' AND transaction_date between ');
4062: IF p_resale_flag IS NULL OR p_resale_flag = 'F' THEN
4063: FND_DSQL.init;
4064: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4065: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4066: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4067: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4068: FND_DSQL.add_bind(p_cust_account_id);
4069: FND_DSQL.add_text(' AND transaction_date between ');
4070: FND_DSQL.add_bind(l_start_date);
4063: FND_DSQL.init;
4064: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4065: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4066: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4067: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4068: FND_DSQL.add_bind(p_cust_account_id);
4069: FND_DSQL.add_text(' AND transaction_date between ');
4070: FND_DSQL.add_bind(l_start_date);
4071: FND_DSQL.add_text(' and ');
4064: FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
4065: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4066: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4067: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4068: FND_DSQL.add_bind(p_cust_account_id);
4069: FND_DSQL.add_text(' AND transaction_date between ');
4070: FND_DSQL.add_bind(l_start_date);
4071: FND_DSQL.add_text(' and ');
4072: FND_DSQL.add_bind(l_end_date);
4065: FND_DSQL.add_text(' common_uom_code, common_currency_code ');
4066: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4067: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4068: FND_DSQL.add_bind(p_cust_account_id);
4069: FND_DSQL.add_text(' AND transaction_date between ');
4070: FND_DSQL.add_bind(l_start_date);
4071: FND_DSQL.add_text(' and ');
4072: FND_DSQL.add_bind(l_end_date);
4073: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4066: FND_DSQL.add_text(' FROM ozf_sales_transactions ');
4067: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4068: FND_DSQL.add_bind(p_cust_account_id);
4069: FND_DSQL.add_text(' AND transaction_date between ');
4070: FND_DSQL.add_bind(l_start_date);
4071: FND_DSQL.add_text(' and ');
4072: FND_DSQL.add_bind(l_end_date);
4073: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4074: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4067: FND_DSQL.add_text(' WHERE sold_to_cust_account_id = ');
4068: FND_DSQL.add_bind(p_cust_account_id);
4069: FND_DSQL.add_text(' AND transaction_date between ');
4070: FND_DSQL.add_bind(l_start_date);
4071: FND_DSQL.add_text(' and ');
4072: FND_DSQL.add_bind(l_end_date);
4073: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4074: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4075: p_context => l_product_attr_context,
4068: FND_DSQL.add_bind(p_cust_account_id);
4069: FND_DSQL.add_text(' AND transaction_date between ');
4070: FND_DSQL.add_bind(l_start_date);
4071: FND_DSQL.add_text(' and ');
4072: FND_DSQL.add_bind(l_end_date);
4073: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4074: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4075: p_context => l_product_attr_context,
4076: p_attribute => l_product_attribute,
4069: FND_DSQL.add_text(' AND transaction_date between ');
4070: FND_DSQL.add_bind(l_start_date);
4071: FND_DSQL.add_text(' and ');
4072: FND_DSQL.add_bind(l_end_date);
4073: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4074: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4075: p_context => l_product_attr_context,
4076: p_attribute => l_product_attribute,
4077: p_attr_value_from => l_product_attr_value,
4078: p_attr_value_to => NULL,
4079: p_comparison => NULL,
4080: p_type => 'PROD'
4081: );
4082: FND_DSQL.add_text(') s) ');
4083: FND_DSQL.add_text(' GROUP BY common_uom_code, common_currency_code ');
4084:
4085: IF OZF_DEBUG_HIGH_ON THEN
4086: l_temp_sql := FND_DSQL.get_text(TRUE);
4079: p_comparison => NULL,
4080: p_type => 'PROD'
4081: );
4082: FND_DSQL.add_text(') s) ');
4083: FND_DSQL.add_text(' GROUP BY common_uom_code, common_currency_code ');
4084:
4085: IF OZF_DEBUG_HIGH_ON THEN
4086: l_temp_sql := FND_DSQL.get_text(TRUE);
4087: OZF_Utility_PVT.debug_message('----- Check_Offer_Performance SQL -----');
4082: FND_DSQL.add_text(') s) ');
4083: FND_DSQL.add_text(' GROUP BY common_uom_code, common_currency_code ');
4084:
4085: IF OZF_DEBUG_HIGH_ON THEN
4086: l_temp_sql := FND_DSQL.get_text(TRUE);
4087: OZF_Utility_PVT.debug_message('----- Check_Offer_Performance SQL -----');
4088: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 1, 254));
4089: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 255, 254));
4090: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 509, 254));
4091: OZF_Utility_PVT.debug_message('---------------------------------------');
4092: END IF;
4093:
4094: l_emp_csr := DBMS_SQL.open_cursor;
4095: FND_DSQL.set_cursor(l_emp_csr);
4096: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);
4097: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4098: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4099: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);
4092: END IF;
4093:
4094: l_emp_csr := DBMS_SQL.open_cursor;
4095: FND_DSQL.set_cursor(l_emp_csr);
4096: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);
4097: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4098: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4099: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);
4100: DBMS_SQL.define_column(l_emp_csr, 4, l_common_curr_code, 15);
4097: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4098: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4099: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);
4100: DBMS_SQL.define_column(l_emp_csr, 4, l_common_curr_code, 15);
4101: FND_DSQL.do_binds;
4102:
4103: l_ignore := DBMS_SQL.execute(l_emp_csr);
4104: IF DBMS_SQL.fetch_rows(l_emp_csr) > 0 THEN
4105: DBMS_SQL.column_value(l_emp_csr, 1, l_common_quantity);
4180: DBMS_SQL.close_cursor(l_emp_csr);
4181:
4182: ELSE
4183: /********** Check resale data ***********/
4184: FND_DSQL.init;
4185: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4186: FND_DSQL.add_text(' uom_code ');
4187: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4188: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4181:
4182: ELSE
4183: /********** Check resale data ***********/
4184: FND_DSQL.init;
4185: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4186: FND_DSQL.add_text(' uom_code ');
4187: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4188: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4189: FND_DSQL.add_bind(p_cust_account_id);
4182: ELSE
4183: /********** Check resale data ***********/
4184: FND_DSQL.init;
4185: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4186: FND_DSQL.add_text(' uom_code ');
4187: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4188: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4189: FND_DSQL.add_bind(p_cust_account_id);
4190: FND_DSQL.add_text(' AND date_ordered between ');
4183: /********** Check resale data ***********/
4184: FND_DSQL.init;
4185: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4186: FND_DSQL.add_text(' uom_code ');
4187: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4188: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4189: FND_DSQL.add_bind(p_cust_account_id);
4190: FND_DSQL.add_text(' AND date_ordered between ');
4191: FND_DSQL.add_bind(l_start_date);
4184: FND_DSQL.init;
4185: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4186: FND_DSQL.add_text(' uom_code ');
4187: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4188: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4189: FND_DSQL.add_bind(p_cust_account_id);
4190: FND_DSQL.add_text(' AND date_ordered between ');
4191: FND_DSQL.add_bind(l_start_date);
4192: FND_DSQL.add_text(' and ');
4185: FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
4186: FND_DSQL.add_text(' uom_code ');
4187: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4188: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4189: FND_DSQL.add_bind(p_cust_account_id);
4190: FND_DSQL.add_text(' AND date_ordered between ');
4191: FND_DSQL.add_bind(l_start_date);
4192: FND_DSQL.add_text(' and ');
4193: FND_DSQL.add_bind(l_end_date);
4186: FND_DSQL.add_text(' uom_code ');
4187: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4188: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4189: FND_DSQL.add_bind(p_cust_account_id);
4190: FND_DSQL.add_text(' AND date_ordered between ');
4191: FND_DSQL.add_bind(l_start_date);
4192: FND_DSQL.add_text(' and ');
4193: FND_DSQL.add_bind(l_end_date);
4194: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4187: FND_DSQL.add_text(' FROM ozf_resale_lines ');
4188: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4189: FND_DSQL.add_bind(p_cust_account_id);
4190: FND_DSQL.add_text(' AND date_ordered between ');
4191: FND_DSQL.add_bind(l_start_date);
4192: FND_DSQL.add_text(' and ');
4193: FND_DSQL.add_bind(l_end_date);
4194: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4195: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4188: FND_DSQL.add_text(' WHERE sold_from_cust_account_id = ');
4189: FND_DSQL.add_bind(p_cust_account_id);
4190: FND_DSQL.add_text(' AND date_ordered between ');
4191: FND_DSQL.add_bind(l_start_date);
4192: FND_DSQL.add_text(' and ');
4193: FND_DSQL.add_bind(l_end_date);
4194: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4195: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4196: p_context => l_product_attr_context,
4189: FND_DSQL.add_bind(p_cust_account_id);
4190: FND_DSQL.add_text(' AND date_ordered between ');
4191: FND_DSQL.add_bind(l_start_date);
4192: FND_DSQL.add_text(' and ');
4193: FND_DSQL.add_bind(l_end_date);
4194: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4195: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4196: p_context => l_product_attr_context,
4197: p_attribute => l_product_attribute,
4190: FND_DSQL.add_text(' AND date_ordered between ');
4191: FND_DSQL.add_bind(l_start_date);
4192: FND_DSQL.add_text(' and ');
4193: FND_DSQL.add_bind(l_end_date);
4194: FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
4195: l_temp_sql := OZF_OFFR_ELIG_PROD_DENORM_PVT.get_sql(
4196: p_context => l_product_attr_context,
4197: p_attribute => l_product_attribute,
4198: p_attr_value_from => l_product_attr_value,
4199: p_attr_value_to => NULL,
4200: p_comparison => NULL,
4201: p_type => 'PROD'
4202: );
4203: FND_DSQL.add_text(') s) ');
4204: FND_DSQL.add_text(' GROUP BY uom_code ');
4205:
4206: IF OZF_DEBUG_HIGH_ON THEN
4207: l_temp_sql := FND_DSQL.get_text(TRUE);
4200: p_comparison => NULL,
4201: p_type => 'PROD'
4202: );
4203: FND_DSQL.add_text(') s) ');
4204: FND_DSQL.add_text(' GROUP BY uom_code ');
4205:
4206: IF OZF_DEBUG_HIGH_ON THEN
4207: l_temp_sql := FND_DSQL.get_text(TRUE);
4208: OZF_Utility_PVT.debug_message('----- Check_Offer_Performance SQL -----');
4203: FND_DSQL.add_text(') s) ');
4204: FND_DSQL.add_text(' GROUP BY uom_code ');
4205:
4206: IF OZF_DEBUG_HIGH_ON THEN
4207: l_temp_sql := FND_DSQL.get_text(TRUE);
4208: OZF_Utility_PVT.debug_message('----- Check_Offer_Performance SQL -----');
4209: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 1, 254));
4210: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 255, 254));
4211: OZF_Utility_PVT.debug_message(SUBSTR(l_temp_sql, 509, 254));
4212: OZF_Utility_PVT.debug_message('---------------------------------------');
4213: END IF;
4214:
4215: l_emp_csr := DBMS_SQL.open_cursor;
4216: FND_DSQL.set_cursor(l_emp_csr);
4217: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);
4218: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4219: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4220: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);
4213: END IF;
4214:
4215: l_emp_csr := DBMS_SQL.open_cursor;
4216: FND_DSQL.set_cursor(l_emp_csr);
4217: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);
4218: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4219: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4220: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);
4221: FND_DSQL.do_binds;
4217: DBMS_SQL.parse(l_emp_csr, FND_DSQL.get_text(FALSE), DBMS_SQL.native);
4218: DBMS_SQL.define_column(l_emp_csr, 1, l_common_quantity);
4219: DBMS_SQL.define_column(l_emp_csr, 2, l_common_amount);
4220: DBMS_SQL.define_column(l_emp_csr, 3, l_common_uom_code, 3);
4221: FND_DSQL.do_binds;
4222:
4223: l_ignore := DBMS_SQL.execute(l_emp_csr);
4224: IF DBMS_SQL.fetch_rows(l_emp_csr) > 0 THEN
4225: DBMS_SQL.column_value(l_emp_csr, 1, l_common_quantity);
4573: RAISE FND_API.g_exc_error;
4574: END IF;
4575:
4576: l_emp_csr := DBMS_SQL.open_cursor;
4577: FND_DSQL.set_cursor(l_emp_csr);
4578: DBMS_SQL.parse(l_emp_csr, l_stmt, DBMS_SQL.native);
4579: DBMS_SQL.define_column(l_emp_csr, 1, l_cust_account_id);
4580: DBMS_SQL.define_column(l_emp_csr, 2, l_plan_type, 30);
4581: DBMS_SQL.define_column(l_emp_csr, 3, l_plan_id);
4583: DBMS_SQL.define_column(l_emp_csr, 5, l_product_level_type, 30);
4584: DBMS_SQL.define_column(l_emp_csr, 6, l_product_id);
4585: DBMS_SQL.define_column(l_emp_csr, 7, l_amount);
4586: DBMS_SQL.define_column(l_emp_csr, 8, l_currency_code, 15);
4587: FND_DSQL.do_binds;
4588:
4589: l_ignore := DBMS_SQL.execute(l_emp_csr);
4590: LOOP
4591: IF DBMS_SQL.fetch_rows(l_emp_csr) > 0 THEN
5296: RAISE FND_API.g_exc_error;
5297: END IF;
5298:
5299: l_emp_csr := DBMS_SQL.open_cursor;
5300: FND_DSQL.set_cursor(l_emp_csr);
5301: DBMS_SQL.parse(l_emp_csr, l_stmt, DBMS_SQL.native);
5302: DBMS_SQL.define_column(l_emp_csr, 1, l_cust_account_id);
5303: DBMS_SQL.define_column(l_emp_csr, 2, l_plan_type, 30);
5304: DBMS_SQL.define_column(l_emp_csr, 3, l_plan_id);
5306: DBMS_SQL.define_column(l_emp_csr, 5, l_product_level_type, 30);
5307: DBMS_SQL.define_column(l_emp_csr, 6, l_product_id);
5308: DBMS_SQL.define_column(l_emp_csr, 7, l_amount);
5309: DBMS_SQL.define_column(l_emp_csr, 8, l_currency_code, 15);
5310: FND_DSQL.do_binds;
5311:
5312: l_ignore := DBMS_SQL.execute(l_emp_csr);
5313: LOOP
5314: IF DBMS_SQL.fetch_rows(l_emp_csr) > 0 AND l_total_amount <> 0 THEN
7328: ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
7329: RAISE FND_API.g_exc_error;
7330: END IF;
7331:
7332: -- use FND_DSQL package for dynamic sql and bind variables
7333: l_funds_util_csr := DBMS_SQL.open_cursor;
7334: FND_DSQL.set_cursor(l_funds_util_csr);
7335: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
7336: DBMS_SQL.define_column(l_funds_util_csr, 1, l_util_id);
7330: END IF;
7331:
7332: -- use FND_DSQL package for dynamic sql and bind variables
7333: l_funds_util_csr := DBMS_SQL.open_cursor;
7334: FND_DSQL.set_cursor(l_funds_util_csr);
7335: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
7336: DBMS_SQL.define_column(l_funds_util_csr, 1, l_util_id);
7337: DBMS_SQL.define_column(l_funds_util_csr, 2, l_fu_amt_rem);
7338: DBMS_SQL.define_column(l_funds_util_csr, 3, l_fu_scan_unit_rem);
7336: DBMS_SQL.define_column(l_funds_util_csr, 1, l_util_id);
7337: DBMS_SQL.define_column(l_funds_util_csr, 2, l_fu_amt_rem);
7338: DBMS_SQL.define_column(l_funds_util_csr, 3, l_fu_scan_unit_rem);
7339: DBMS_SQL.define_column(l_funds_util_csr, 4, l_fu_currency_code, 15);
7340: FND_DSQL.do_binds;
7341:
7342: l_ignore := DBMS_SQL.execute(l_funds_util_csr);
7343: --OPEN l_funds_util_csr FOR l_funds_util_sql;
7344: LOOP
8257: ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
8258: RAISE FND_API.g_exc_unexpected_error;
8259: END IF;
8260:
8261: -- use FND_DSQL package for dynamic sql and bind variables
8262: l_funds_util_csr := DBMS_SQL.open_cursor;
8263: FND_DSQL.set_cursor(l_funds_util_csr);
8264: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
8265: DBMS_SQL.define_column(l_funds_util_csr, 1, l_lu_line_util_id);
8259: END IF;
8260:
8261: -- use FND_DSQL package for dynamic sql and bind variables
8262: l_funds_util_csr := DBMS_SQL.open_cursor;
8263: FND_DSQL.set_cursor(l_funds_util_csr);
8264: DBMS_SQL.parse(l_funds_util_csr, l_funds_util_sql, DBMS_SQL.native);
8265: DBMS_SQL.define_column(l_funds_util_csr, 1, l_lu_line_util_id);
8266: DBMS_SQL.define_column(l_funds_util_csr, 2, l_lu_utilization_id);
8267: DBMS_SQL.define_column(l_funds_util_csr, 3, l_lu_amt);
8267: DBMS_SQL.define_column(l_funds_util_csr, 3, l_lu_amt);
8268: DBMS_SQL.define_column(l_funds_util_csr, 4, l_lu_scan_unit);
8269: DBMS_SQL.define_column(l_funds_util_csr, 5, l_lu_currency_code, 15);
8270: -- DBMS_SQL.define_column(l_funds_util_csr, 5, l_utiz_amount);
8271: FND_DSQL.do_binds;
8272:
8273: l_ignore := DBMS_SQL.execute(l_funds_util_csr);
8274: --OPEN l_funds_util_csr FOR l_funds_util_sql;
8275: LOOP