[Home] [Help]
188: AND indirect_flag = p_indirect_flag;
189:
190: CURSOR c_group_volume_exists(p_offer_id NUMBER, p_group_no NUMBER, p_pbh_lind_id NUMBER) IS
191: SELECT 'Y'
192: FROM ozf_volume_summary
193: WHERE offer_id = p_offer_id
194: AND group_no = p_group_no
195: AND pbh_line_id = p_pbh_lind_id;
196:
195: AND pbh_line_id = p_pbh_lind_id;
196:
197: CURSOR c_individual_volume_exists(p_offer_id NUMBER, p_individual_type VARCHAR2, p_individual_id NUMBER, p_pbh_line_id NUMBER) IS
198: SELECT 'Y'
199: FROM ozf_volume_summary
200: WHERE offer_id = p_offer_id
201: AND individual_type = p_individual_type
202: AND individual_id = p_individual_id
203: AND pbh_line_id = p_pbh_line_id;
204:
205:
206: CURSOR c_ind_vol_woindvid_exists(p_offer_id NUMBER, p_individual_type VARCHAR2, p_individual_id NUMBER, p_pbh_line_id NUMBER) IS
207: SELECT 'Y'
208: FROM ozf_volume_summary
209: WHERE offer_id = p_offer_id
210: AND individual_type = p_individual_type
211: AND individual_id IS NULL
212: AND pbh_line_id = p_pbh_line_id;
476: -- process volume summary
477: -- 1. group's volume
478: IF l_volume_track_type = 'GROUP' THEN
479: IF l_combine_schedule_yn = 'Y' THEN
480: --INSERT IN TO OZF_VOLUME_SUMMARY_INT table all the pbh lines for the offer
481: FOR l_pbh_line IN c_pbh_lines(p_offer_id) LOOP
482: INSERT INTO ozf_volume_summary_int
483: (
484: resale_batch_id
478: IF l_volume_track_type = 'GROUP' THEN
479: IF l_combine_schedule_yn = 'Y' THEN
480: --INSERT IN TO OZF_VOLUME_SUMMARY_INT table all the pbh lines for the offer
481: FOR l_pbh_line IN c_pbh_lines(p_offer_id) LOOP
482: INSERT INTO ozf_volume_summary_int
483: (
484: resale_batch_id
485: ,offer_id
486: ,group_no
511: ,FND_GLOBAL.conc_request_id
512: );
513: END LOOP;
514:
515: ELSE -- insert one pbh line only in to ozf_volume_summary_int table
516: INSERT INTO ozf_volume_summary_int
517: (
518: resale_batch_id
519: ,offer_id
512: );
513: END LOOP;
514:
515: ELSE -- insert one pbh line only in to ozf_volume_summary_int table
516: INSERT INTO ozf_volume_summary_int
517: (
518: resale_batch_id
519: ,offer_id
520: ,group_no
552: --IF p_volume_detail_rec.sold_from_cust_account_id IS NOT NULL AND p_volume_detail_rec.sold_from_cust_account_id <> fnd_api.g_miss_num THEN
553: IF p_indirect_flag = 'R' THEN -- indirect sales
554: ozf_utility_pvt.write_conc_log('distributor ' || l_volume_exists);
555: --insert distributor's volume in to volume summary int table
556: INSERT INTO ozf_volume_summary_int
557: (
558: resale_batch_id
559: ,offer_id
560: ,individual_type
589:
590: END IF; -- end distributor's volume
591:
592: -- 3. customer's volume
593: -- insert customer's volume into ozf_volume_summary_int table
594: INSERT INTO ozf_volume_summary_int
595: (
596: resale_batch_id
597: ,offer_id
590: END IF; -- end distributor's volume
591:
592: -- 3. customer's volume
593: -- insert customer's volume into ozf_volume_summary_int table
594: INSERT INTO ozf_volume_summary_int
595: (
596: resale_batch_id
597: ,offer_id
598: ,individual_type
627:
628:
629:
630: -- 4. bill_to's volume
631: -- insert bill_to's volume into ozf_volume_summary_int table
632: INSERT INTO ozf_volume_summary_int
633: (
634: resale_batch_id
635: ,offer_id
628:
629:
630: -- 4. bill_to's volume
631: -- insert bill_to's volume into ozf_volume_summary_int table
632: INSERT INTO ozf_volume_summary_int
633: (
634: resale_batch_id
635: ,offer_id
636: ,individual_type
664: );
665:
666:
667: -- 5. ship_to's volume
668: -- insert ship_to's volume into ozf_volume_summary_int table
669: INSERT INTO ozf_volume_summary_int
670: (
671: resale_batch_id
672: ,offer_id
665:
666:
667: -- 5. ship_to's volume
668: -- insert ship_to's volume into ozf_volume_summary_int table
669: INSERT INTO ozf_volume_summary_int
670: (
671: resale_batch_id
672: ,offer_id
673: ,individual_type
709: CLOSE c_group_volume_exists;
710: ozf_utility_pvt.write_conc_log('group ' || l_volume_exists);
711: IF l_volume_exists = 'Y' THEN -- update group's volume
712: IF l_combine_schedule_yn = 'Y' THEN -- update all pbh lines
713: UPDATE ozf_volume_summary
714: SET group_volume = group_volume + l_current_volume,
715: last_update_date = SYSDATE,
716: last_updated_by = FND_GLOBAL.user_id,
717: last_update_login = FND_GLOBAL.conc_login_id
717: last_update_login = FND_GLOBAL.conc_login_id
718: WHERE offer_id = p_offer_id
719: AND group_no = l_group_no;
720: ELSE -- update one pbh line only
721: UPDATE ozf_volume_summary
722: SET group_volume = group_volume + l_current_volume,
723: last_update_date = SYSDATE,
724: last_updated_by = FND_GLOBAL.user_id,
725: last_update_login = FND_GLOBAL.conc_login_id
729: END IF;
730: ELSE -- insert group's volume
731: IF l_combine_schedule_yn = 'Y' THEN -- insert all pbh lines
732: FOR l_pbh_line IN c_pbh_lines(p_offer_id) LOOP
733: INSERT INTO ozf_volume_summary
734: (
735: volume_summary_id
736: ,creation_date
737: ,created_by
745: ,pbh_line_id
746: )
747: VALUES
748: (
749: ozf_volume_summary_s.NEXTVAL
750: ,SYSDATE
751: ,FND_GLOBAL.user_id
752: ,SYSDATE
753: ,FND_GLOBAL.user_id
759: ,l_pbh_line.offer_discount_line_id
760: );
761: END LOOP;
762: ELSE -- insert one pbh line
763: INSERT INTO ozf_volume_summary
764: (
765: volume_summary_id
766: ,creation_date
767: ,created_by
775: ,pbh_line_id
776: )
777: VALUES
778: (
779: ozf_volume_summary_s.NEXTVAL
780: ,SYSDATE
781: ,FND_GLOBAL.user_id
782: ,SYSDATE
783: ,FND_GLOBAL.user_id
806: CLOSE c_ind_vol_woindvid_exists;
807: END IF;
808: ozf_utility_pvt.write_conc_log('distributor ' || l_volume_exists);
809: IF l_volume_exists = 'Y' THEN -- update distributor's volume
810: UPDATE ozf_volume_summary
811: SET individual_volume = individual_volume + l_current_volume,
812: last_update_date = SYSDATE,
813: last_updated_by = FND_GLOBAL.user_id,
814: last_update_login = FND_GLOBAL.conc_login_id
816: AND individual_type = 'DISTRIBUTOR'
817: AND individual_id = p_volume_detail_rec.sold_from_cust_account_id
818: AND pbh_line_id = l_pbh_line_id;
819: ELSE -- insert distributor's volume
820: INSERT INTO ozf_volume_summary
821: (
822: volume_summary_id
823: ,creation_date
824: ,created_by
833: ,pbh_line_id
834: )
835: VALUES
836: (
837: ozf_volume_summary_s.NEXTVAL
838: ,SYSDATE
839: ,FND_GLOBAL.user_id
840: ,SYSDATE
841: ,FND_GLOBAL.user_id
863:
864: END IF;
865: ozf_utility_pvt.write_conc_log('account ' || l_volume_exists);
866: IF l_volume_exists = 'Y' THEN -- update customer's volume
867: UPDATE ozf_volume_summary
868: SET individual_volume = individual_volume + l_current_volume,
869: last_update_date = SYSDATE,
870: last_updated_by = FND_GLOBAL.user_id,
871: last_update_login = FND_GLOBAL.conc_login_id
873: AND individual_type = 'ACCOUNT'
874: AND individual_id = p_volume_detail_rec.sold_to_cust_account_id
875: AND pbh_line_id = l_pbh_line_id;
876: ELSE -- insert customer's volume
877: INSERT INTO ozf_volume_summary
878: (
879: volume_summary_id
880: ,creation_date
881: ,created_by
890: ,pbh_line_id
891: )
892: VALUES
893: (
894: ozf_volume_summary_s.NEXTVAL
895: ,SYSDATE
896: ,FND_GLOBAL.user_id
897: ,SYSDATE
898: ,FND_GLOBAL.user_id
918: CLOSE c_ind_vol_woindvid_exists;
919: END IF;
920: ozf_utility_pvt.write_conc_log('bill_to ' || l_volume_exists);
921: IF l_volume_exists = 'Y' THEN -- update bill_to's volume
922: UPDATE ozf_volume_summary
923: SET individual_volume = individual_volume + l_current_volume,
924: last_update_date = SYSDATE,
925: last_updated_by = FND_GLOBAL.user_id,
926: last_update_login = FND_GLOBAL.conc_login_id
928: AND individual_type = 'BILL_TO'
929: AND individual_id = p_volume_detail_rec.bill_to_site_use_id
930: AND pbh_line_id = l_pbh_line_id;
931: ELSE -- insert bill_to's volume
932: INSERT INTO ozf_volume_summary
933: (
934: volume_summary_id
935: ,creation_date
936: ,created_by
945: ,pbh_line_id
946: )
947: VALUES
948: (
949: ozf_volume_summary_s.NEXTVAL
950: ,SYSDATE
951: ,FND_GLOBAL.user_id
952: ,SYSDATE
953: ,FND_GLOBAL.user_id
973: CLOSE c_ind_vol_woindvid_exists;
974: END IF;
975: ozf_utility_pvt.write_conc_log('ship to ' || l_volume_exists);
976: IF l_volume_exists = 'Y' THEN -- update ship_to's volume
977: UPDATE ozf_volume_summary
978: SET individual_volume = individual_volume + l_current_volume,
979: last_update_date = SYSDATE,
980: last_updated_by = FND_GLOBAL.user_id,
981: last_update_login = FND_GLOBAL.conc_login_id
983: AND individual_type = 'SHIP_TO'
984: AND individual_id = p_volume_detail_rec.ship_to_site_use_id
985: AND pbh_line_id = l_pbh_line_id;
986: ELSE -- insert ship_to's volume
987: INSERT INTO ozf_volume_summary
988: (
989: volume_summary_id
990: ,creation_date
991: ,created_by
1000: ,pbh_line_id
1001: )
1002: VALUES
1003: (
1004: ozf_volume_summary_s.NEXTVAL
1005: ,SYSDATE
1006: ,FND_GLOBAL.user_id
1007: ,SYSDATE
1008: ,FND_GLOBAL.user_id
1715: )
1716: IS
1717: CURSOR c_group_volume IS
1718: SELECT group_volume
1719: FROM ozf_volume_summary
1720: WHERE offer_id = p_offer_id
1721: AND group_no = p_group_no
1722: AND pbh_line_id = p_pbh_line_id;
1723:
1722: AND pbh_line_id = p_pbh_line_id;
1723:
1724: CURSOR c_individual_volume(p_volume_track_type VARCHAR2, p_volume_track_id NUMBER) IS
1725: SELECT individual_volume
1726: FROM ozf_volume_summary
1727: WHERE offer_id = p_offer_id
1728: AND individual_type = p_vol_track_type
1729: AND individual_id = p_volume_track_id
1730: AND pbh_line_id = p_pbh_line_id;
1730: AND pbh_line_id = p_pbh_line_id;
1731:
1732: CURSOR c_combine_individual_volume(p_volume_track_type VARCHAR2, p_volume_track_id NUMBER) IS
1733: SELECT SUM(individual_volume)
1734: FROM ozf_volume_summary
1735: WHERE offer_id = p_offer_id
1736: AND individual_type = p_vol_track_type
1737: AND individual_id = p_volume_track_id;
1738:
2425: IS
2426: l_group_volume NUMBER;
2427: BEGIN
2428:
2429: select nvl(group_volume,0) into l_group_volume from ozf_volume_summary
2430: where offer_id = p_offer_id
2431: and group_no = p_group_number
2432: and pbh_line_id = p_pbh_line_id;
2433: