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

[Home] [Help]

APPS.OZF_VOLUME_CALCULATION_PUB dependencies on OZF_VOLUME_DETAIL

Line 191: FROM ozf_volume_detail

187: AND tier_type = 'PBH';
188:
189: CURSOR c_line_processed(p_offer_id NUMBER, p_source_code VARCHAR2, p_line_id NUMBER) IS
190: SELECT 'Y'
191: FROM ozf_volume_detail
192: WHERE offer_id = p_offer_id
193: AND source_code = p_source_code
194: AND order_line_id = p_line_id;
195:

Line 379: INSERT INTO ozf_volume_detail

375: END IF;
376: ozf_utility_pvt.write_conc_log('l_current_volume ' || l_current_volume);
377: IF l_include_volume = 'Y' THEN
378: -- process volume detail
379: INSERT INTO ozf_volume_detail
380: (
381: volume_detail_id
382: ,creation_date
383: ,created_by

Line 408: ozf_volume_detail_s.NEXTVAL

404: ,source_code
405: )
406: VALUES
407: (
408: ozf_volume_detail_s.NEXTVAL
409: ,SYSDATE
410: ,FND_GLOBAL.user_id
411: ,SYSDATE
412: ,FND_GLOBAL.user_id

Line 941: FROM ozf_volume_detail

937: IS
938: -- julou bug 6348078. volume before trx_date
939: CURSOR c_group_volume(p_volume_track_id NUMBER) IS
940: SELECT NVL(SUM(volume), 0)
941: FROM ozf_volume_detail
942: WHERE include_volume_flag = 'Y'
943: AND offer_id = p_offer_id
944: AND group_no = p_volume_track_id
945: AND pbh_line_id = p_pbh_line_id

Line 950: FROM ozf_volume_detail

946: AND transaction_date < p_transaction_date;
947:
948: CURSOR c_dist_volume(p_volume_track_id NUMBER) IS
949: SELECT NVL(SUM(volume), 0)
950: FROM ozf_volume_detail
951: WHERE include_volume_flag = 'Y'
952: AND offer_id = p_offer_id
953: AND distributor_acct_id = p_volume_track_id
954: AND pbh_line_id = p_pbh_line_id

Line 959: FROM ozf_volume_detail

955: AND transaction_date < p_transaction_date;
956:
957: CURSOR c_customer_volume(p_volume_track_id NUMBER) IS
958: SELECT NVL(SUM(volume), 0)
959: FROM ozf_volume_detail
960: WHERE include_volume_flag = 'Y'
961: AND offer_id = p_offer_id
962: AND cust_account_id = p_volume_track_id
963: AND pbh_line_id = p_pbh_line_id

Line 968: FROM ozf_volume_detail

964: AND transaction_date < p_transaction_date;
965:
966: CURSOR c_billto_volume(p_volume_track_id NUMBER) IS
967: SELECT NVL(SUM(volume), 0)
968: FROM ozf_volume_detail
969: WHERE include_volume_flag = 'Y'
970: AND offer_id = p_offer_id
971: AND bill_to_site_use_id = p_volume_track_id
972: AND pbh_line_id = p_pbh_line_id

Line 977: FROM ozf_volume_detail

973: AND transaction_date < p_transaction_date;
974:
975: CURSOR c_shipto_volume(p_volume_track_id NUMBER) IS
976: SELECT NVL(SUM(volume), 0)
977: FROM ozf_volume_detail
978: WHERE include_volume_flag = 'Y'
979: AND offer_id = p_offer_id
980: AND ship_to_site_use_id = p_volume_track_id
981: AND pbh_line_id = p_pbh_line_id

Line 986: FROM ozf_volume_detail

982: AND transaction_date < p_transaction_date;
983:
984: CURSOR c_combine_group_volume(p_volume_track_id NUMBER) IS
985: SELECT NVL(SUM(volume), 0)
986: FROM ozf_volume_detail
987: WHERE include_volume_flag = 'Y'
988: AND offer_id = p_offer_id
989: AND group_no = p_volume_track_id
990: AND transaction_date < p_transaction_date;

Line 994: FROM ozf_volume_detail

990: AND transaction_date < p_transaction_date;
991:
992: CURSOR c_combine_dist_volume(p_volume_track_id NUMBER) IS
993: SELECT NVL(SUM(volume), 0)
994: FROM ozf_volume_detail
995: WHERE include_volume_flag = 'Y'
996: AND offer_id = p_offer_id
997: AND distributor_acct_id = p_volume_track_id
998: AND transaction_date < p_transaction_date;

Line 1002: FROM ozf_volume_detail

998: AND transaction_date < p_transaction_date;
999:
1000: CURSOR c_combine_customer_volume(p_volume_track_id NUMBER) IS
1001: SELECT NVL(SUM(volume), 0)
1002: FROM ozf_volume_detail
1003: WHERE include_volume_flag = 'Y'
1004: AND offer_id = p_offer_id
1005: AND cust_account_id = p_volume_track_id
1006: AND transaction_date < p_transaction_date;

Line 1010: FROM ozf_volume_detail

1006: AND transaction_date < p_transaction_date;
1007:
1008: CURSOR c_combine_billto_volume(p_volume_track_id NUMBER) IS
1009: SELECT NVL(SUM(volume), 0)
1010: FROM ozf_volume_detail
1011: WHERE include_volume_flag = 'Y'
1012: AND offer_id = p_offer_id
1013: AND bill_to_site_use_id = p_volume_track_id
1014: AND transaction_date < p_transaction_date;

Line 1018: FROM ozf_volume_detail

1014: AND transaction_date < p_transaction_date;
1015:
1016: CURSOR c_combine_shipto_volume(p_volume_track_id NUMBER) IS
1017: SELECT NVL(SUM(volume), 0)
1018: FROM ozf_volume_detail
1019: WHERE include_volume_flag = 'Y'
1020: AND offer_id = p_offer_id
1021: AND ship_to_site_use_id = p_volume_track_id
1022: AND transaction_date < p_transaction_date;

Line 1026: FROM ozf_volume_detail

1022: AND transaction_date < p_transaction_date;
1023:
1024: CURSOR c_trx_date_volume_pk IS -- PK of volume rec for given order_line_id.
1025: SELECT volume_detail_id
1026: FROM ozf_volume_detail
1027: WHERE include_volume_flag = 'Y'
1028: AND offer_id = p_offer_id
1029: AND source_code = p_source_code
1030: AND order_line_id = p_order_line_id;

Line 1037: FROM ozf_volume_detail

1033:
1034: -- volume of trx_date. if multiple entries found, sum volume by primary key.
1035: CURSOR c_group_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1036: SELECT NVL(SUM(volume), 0)
1037: FROM ozf_volume_detail
1038: WHERE include_volume_flag = 'Y'
1039: AND offer_id = p_offer_id
1040: AND group_no = p_volume_track_id
1041: AND pbh_line_id = p_pbh_line_id

Line 1047: FROM ozf_volume_detail

1043: AND volume_detail_id <= p_volume_detail_id;
1044:
1045: CURSOR c_dist_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1046: SELECT NVL(SUM(volume), 0)
1047: FROM ozf_volume_detail
1048: WHERE include_volume_flag = 'Y'
1049: AND offer_id = p_offer_id
1050: AND distributor_acct_id = p_volume_track_id
1051: AND pbh_line_id = p_pbh_line_id

Line 1057: FROM ozf_volume_detail

1053: AND volume_detail_id <= p_volume_detail_id;
1054:
1055: CURSOR c_customer_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1056: SELECT NVL(SUM(volume), 0)
1057: FROM ozf_volume_detail
1058: WHERE include_volume_flag = 'Y'
1059: AND offer_id = p_offer_id
1060: AND cust_account_id = p_volume_track_id
1061: AND pbh_line_id = p_pbh_line_id

Line 1067: ozf_volume_detail is 00:00:00.

1063: AND volume_detail_id <= p_volume_detail_id;
1064:
1065: -- fix for bug 7353241
1066: /*The trunc function is needed by off-invoice volume offeras transaction_date in
1067: ozf_volume_detail is 00:00:00.
1068: The input parameter p_transaction_date has to be truncated before comparing with table value.
1069: On the other hand, trunc screws accrual incentive. As you know the calculation has two parts,
1070: one for transactions before the day, the other for transactions on the day.
1071: This is mainly for IDSM transactions as transactions may not come in the order of time.

Line 1076: FROM ozf_volume_detail

1072: So we need 2 cursors to handle two types of incentive of volume offer.*/
1073:
1074: CURSOR c_customer_volume3(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1075: SELECT NVL(SUM(volume), 0)
1076: FROM ozf_volume_detail
1077: WHERE include_volume_flag = 'Y'
1078: AND offer_id = p_offer_id
1079: AND cust_account_id = p_volume_track_id
1080: AND pbh_line_id = p_pbh_line_id

Line 1086: FROM ozf_volume_detail

1082: AND volume_detail_id <= p_volume_detail_id;
1083:
1084: CURSOR c_billto_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1085: SELECT NVL(SUM(volume), 0)
1086: FROM ozf_volume_detail
1087: WHERE include_volume_flag = 'Y'
1088: AND offer_id = p_offer_id
1089: AND bill_to_site_use_id = p_volume_track_id
1090: AND pbh_line_id = p_pbh_line_id

Line 1096: FROM ozf_volume_detail

1092: AND volume_detail_id <= p_volume_detail_id;
1093:
1094: CURSOR c_shipto_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1095: SELECT NVL(SUM(volume), 0)
1096: FROM ozf_volume_detail
1097: WHERE include_volume_flag = 'Y'
1098: AND offer_id = p_offer_id
1099: AND ship_to_site_use_id = p_volume_track_id
1100: AND pbh_line_id = p_pbh_line_id

Line 1106: FROM ozf_volume_detail

1102: AND volume_detail_id <= p_volume_detail_id;
1103:
1104: CURSOR c_combine_group_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1105: SELECT NVL(SUM(volume), 0)
1106: FROM ozf_volume_detail
1107: WHERE include_volume_flag = 'Y'
1108: AND offer_id = p_offer_id
1109: AND group_no = p_volume_track_id
1110: AND transaction_date = p_transaction_date

Line 1115: FROM ozf_volume_detail

1111: AND volume_detail_id <= p_volume_detail_id;
1112:
1113: CURSOR c_combine_dist_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1114: SELECT NVL(SUM(volume), 0)
1115: FROM ozf_volume_detail
1116: WHERE include_volume_flag = 'Y'
1117: AND offer_id = p_offer_id
1118: AND distributor_acct_id = p_volume_track_id
1119: AND transaction_date = p_transaction_date

Line 1124: FROM ozf_volume_detail

1120: AND volume_detail_id <= p_volume_detail_id;
1121:
1122: CURSOR c_combine_customer_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1123: SELECT NVL(SUM(volume), 0)
1124: FROM ozf_volume_detail
1125: WHERE include_volume_flag = 'Y'
1126: AND offer_id = p_offer_id
1127: AND cust_account_id = p_volume_track_id
1128: AND transaction_date = p_transaction_date

Line 1133: FROM ozf_volume_detail

1129: AND volume_detail_id <= p_volume_detail_id;
1130:
1131: CURSOR c_combine_billto_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1132: SELECT NVL(SUM(volume), 0)
1133: FROM ozf_volume_detail
1134: WHERE include_volume_flag = 'Y'
1135: AND offer_id = p_offer_id
1136: AND bill_to_site_use_id = p_volume_track_id
1137: AND transaction_date = p_transaction_date

Line 1142: FROM ozf_volume_detail

1138: AND volume_detail_id <= p_volume_detail_id;
1139:
1140: CURSOR c_combine_shipto_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1141: SELECT NVL(SUM(volume), 0)
1142: FROM ozf_volume_detail
1143: WHERE include_volume_flag = 'Y'
1144: AND offer_id = p_offer_id
1145: AND ship_to_site_use_id = p_volume_track_id
1146: AND transaction_date = p_transaction_date

Line 1876: FROM ozf_volume_detail

1872: RETURN NUMBER
1873: IS
1874: CURSOR c_group_no IS
1875: SELECT group_no
1876: FROM ozf_volume_detail
1877: WHERE offer_id = p_offer_id
1878: AND cust_account_id = p_cust_account_id
1879: AND transaction_date =
1880: (

Line 1882: FROM ozf_volume_detail

1878: AND cust_account_id = p_cust_account_id
1879: AND transaction_date =
1880: (
1881: SELECT MAX(transaction_date)
1882: FROM ozf_volume_detail
1883: WHERE cust_account_id = p_cust_account_id
1884: AND offer_id = p_offer_id
1885: );
1886: /*

Line 1999: from ozf_volume_detail

1995: BEGIN
1996:
1997: select sum(volume) customer_volume
1998: into l_product_volume
1999: from ozf_volume_detail
2000: where offer_id = p_offer_id
2001: and cust_account_id = p_cust_account_id
2002: and pbh_line_id = p_pbh_line_id
2003: and bill_to_site_use_id = nvl(p_bill_to_id, bill_to_site_use_id)