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

APPS.OZF_CLAIM_ACCRUAL_PVT dependencies on OZF_CLAIM_LINES

Line 256: , ozf_claim_lines line

252: --csr_object_class added for Ship - Debit claims / Pranay
253: CURSOR csr_object_class (cv_request_id IN NUMBER) IS
254: SELECT cla.source_object_class
255: FROM ozf_claims cla
256: , ozf_claim_lines line
257: WHERE cla.claim_id = line.claim_id
258: AND line.activity_id = cv_request_id;
259:
260: BEGIN

Line 724: FROM ozf_claim_lines ln

720: , ln.earnings_end_date
721: , ln.claim_currency_amount
722: , cla.source_object_class
723: , cla.source_object_id
724: FROM ozf_claim_lines ln
725: , ozf_claims cla
726: WHERE ln.claim_id = cla.claim_id
727: AND ln.claim_line_id = cv_claim_line_id;
728:

Line 889: FROM ozf_claim_lines

885:
886:
887: CURSOR csr_claim_currency(cv_claim_line_id IN NUMBER) IS
888: SELECT currency_code
889: FROM ozf_claim_lines
890: where claim_line_id = cv_claim_line_id;
891:
892:
893: BEGIN

Line 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 ');

Line 1634: FROM ozf_claim_lines

1630: l_prorate_earnings_flag VARCHAR2(1);
1631:
1632: CURSOR csr_line_flag(cv_claim_line_id IN NUMBER) IS
1633: SELECT prorate_earnings_flag
1634: FROM ozf_claim_lines
1635: WHERE claim_line_id = cv_claim_line_id;
1636:
1637: -- fix for bug 5042046
1638: CURSOR csr_system_flag IS

Line 1924: , ozf_claim_lines ln

1920: , cla.exchange_rate_type
1921: , cla.exchange_rate_date
1922: , cla.exchange_rate
1923: FROM ozf_claims cla
1924: , ozf_claim_lines ln
1925: WHERE ln.claim_id = cla.claim_id
1926: AND ln.claim_line_id = cv_claim_line_id;
1927:
1928: CURSOR csr_claim_id(cv_claim_line_id IN NUMBER) IS

Line 1930: FROM ozf_claim_lines

1926: AND ln.claim_line_id = cv_claim_line_id;
1927:
1928: CURSOR csr_claim_id(cv_claim_line_id IN NUMBER) IS
1929: SELECT claim_id
1930: FROM ozf_claim_lines
1931: WHERE claim_line_id = cv_claim_line_id;
1932:
1933: -- Bug fix 7463302
1934: CURSOR csr_claim_line_util_amount(cv_claim_line_util_id IN NUMBER) IS

Line 1936: FROM ozf_claim_lines_util

1932:
1933: -- Bug fix 7463302
1934: CURSOR csr_claim_line_util_amount(cv_claim_line_util_id IN NUMBER) IS
1935: SELECT nvl(amount,0)
1936: FROM ozf_claim_lines_util
1937: WHERE claim_line_util_id = cv_claim_line_util_id;
1938:
1939: l_sum_amount NUMBER :=0;
1940: l_last_record_num NUMBER :=0;

Line 2102: UPDATE ozf_claim_lines_util_all

2098: OZF_Utility_PVT.debug_message('l_original_total_amount'||l_original_total_amount);
2099: OZF_Utility_PVT.debug_message('l_sum_amount'||l_sum_amount);
2100: END IF;
2101:
2102: UPDATE ozf_claim_lines_util_all
2103: SET amount = l_line_util_amount
2104: WHERE claim_line_util_id = l_last_record_num;
2105:
2106: END IF;

Line 2109: UPDATE ozf_claim_lines_all

2105:
2106: END IF;
2107:
2108: -- Update Claim Line: set earnings_associated_flag to TRUE
2109: UPDATE ozf_claim_lines_all
2110: SET earnings_associated_flag = 'T'
2111: WHERE claim_line_id = l_line_util_rec.claim_line_id;
2112: END IF;
2113:

Line 2211: SELECT ozf_claim_lines_util_all_s.NEXTVAL

2207: l_exchange_rate NUMBER;
2208:
2209:
2210: CURSOR csr_line_util_seq IS
2211: SELECT ozf_claim_lines_util_all_s.NEXTVAL
2212: FROM DUAL;
2213:
2214: CURSOR csr_line_util_count(cv_line_util_id IN NUMBER) IS
2215: SELECT COUNT(claim_line_util_id)

Line 2216: FROM ozf_claim_lines_util

2212: FROM DUAL;
2213:
2214: CURSOR csr_line_util_count(cv_line_util_id IN NUMBER) IS
2215: SELECT COUNT(claim_line_util_id)
2216: FROM ozf_claim_lines_util
2217: WHERE claim_line_util_id = cv_line_util_id;
2218:
2219: -- fix for bug 5042046
2220: CURSOR csr_function_currency IS

Line 2234: , ozf_claim_lines ln

2230: , cla.exchange_rate_date
2231: , cla.exchange_rate
2232: , cla.creation_date
2233: FROM ozf_claims cla
2234: , ozf_claim_lines ln
2235: WHERE ln.claim_id = cla.claim_id
2236: AND ln.claim_line_id = cv_claim_line_id;
2237:
2238:

Line 2241: FROM ozf_claim_lines

2237:
2238:
2239: CURSOR csr_claim_id(cv_claim_line_id IN NUMBER) IS
2240: SELECT claim_id, acctd_amount
2241: FROM ozf_claim_lines
2242: WHERE claim_line_id = cv_claim_line_id;
2243:
2244: CURSOR csr_total_exist_util_amt(cv_claim_line_id IN NUMBER) IS
2245: SELECT nvl(SUM(acctd_amount),0)

Line 2246: FROM ozf_claim_lines_util

2242: WHERE claim_line_id = cv_claim_line_id;
2243:
2244: CURSOR csr_total_exist_util_amt(cv_claim_line_id IN NUMBER) IS
2245: SELECT nvl(SUM(acctd_amount),0)
2246: FROM ozf_claim_lines_util
2247: WHERE claim_line_id = cv_claim_line_id;
2248:
2249: CURSOR csr_claim_org(cv_claim_id IN NUMBER) IS
2250: SELECT org_id

Line 2498: INSERT INTO ozf_claim_lines_util_all (

2494: EXIT WHEN l_line_util_count = 0;
2495: END LOOP;
2496: END IF;
2497:
2498: INSERT INTO ozf_claim_lines_util_all (
2499: claim_line_util_id,
2500: object_version_number,
2501: last_update_date,
2502: last_updated_by,

Line 2556: UPDATE ozf_claim_lines_all

2552: );
2553:
2554: ------ Update Claim Line: set earnings_associated_flag to TRUE -----
2555: IF l_line_util_rec.update_from_tbl_flag = FND_API.g_false THEN
2556: UPDATE ozf_claim_lines_all
2557: SET earnings_associated_flag = 'T'
2558: WHERE claim_line_id = l_line_util_rec.claim_line_id;
2559: END IF;
2560:

Line 2652: FROM ozf_claim_lines

2648:
2649: -- Cursor to get claim_id
2650: CURSOR csr_claim_id(cv_claim_line_id IN NUMBER) IS
2651: SELECT claim_id
2652: FROM ozf_claim_lines
2653: WHERE claim_line_id = cv_claim_line_id;
2654:
2655: -- fix for bug 5042046
2656: CURSOR csr_function_currency IS

Line 2869: FROM ozf_claim_lines

2865: AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
2866:
2867: CURSOR csr_claim_id(cv_claim_line_id IN NUMBER) IS
2868: SELECT claim_id, acctd_amount
2869: FROM ozf_claim_lines
2870: WHERE claim_line_id = cv_claim_line_id;
2871:
2872: CURSOR csr_lu_old_amt(cv_line_util_id IN NUMBER) IS
2873: SELECT amount, scan_unit , util_curr_amount

Line 2874: FROM ozf_claim_lines_util

2870: WHERE claim_line_id = cv_claim_line_id;
2871:
2872: CURSOR csr_lu_old_amt(cv_line_util_id IN NUMBER) IS
2873: SELECT amount, scan_unit , util_curr_amount
2874: FROM ozf_claim_lines_util
2875: WHERE claim_line_util_id = cv_line_util_id;
2876:
2877: CURSOR csr_total_exist_util_amt(cv_claim_line_id IN NUMBER) IS
2878: SELECT nvl(SUM(acctd_amount),0)

Line 2879: FROM ozf_claim_lines_util

2875: WHERE claim_line_util_id = cv_line_util_id;
2876:
2877: CURSOR csr_total_exist_util_amt(cv_claim_line_id IN NUMBER) IS
2878: SELECT nvl(SUM(acctd_amount),0)
2879: FROM ozf_claim_lines_util
2880: WHERE claim_line_id = cv_claim_line_id;
2881:
2882: CURSOR csr_claim_date(cv_claim_line_id IN NUMBER) IS
2883: SELECT cla.creation_date

Line 2884: FROM ozf_claims cla, ozf_claim_lines cl

2880: WHERE claim_line_id = cv_claim_line_id;
2881:
2882: CURSOR csr_claim_date(cv_claim_line_id IN NUMBER) IS
2883: SELECT cla.creation_date
2884: FROM ozf_claims cla, ozf_claim_lines cl
2885: WHERE cla.claim_id = cl.claim_id
2886: AND cl.claim_line_id = cv_claim_line_id;
2887:
2888: BEGIN

Line 3066: UPDATE ozf_claim_lines_util_all SET

3062: IF OZF_DEBUG_HIGH_ON THEN
3063: OZF_Utility_PVT.debug_message(l_full_name ||': update lines utilized');
3064: END IF;
3065:
3066: UPDATE ozf_claim_lines_util_all SET
3067: object_version_number = l_line_util_rec.object_version_number,
3068: last_update_date = SYSDATE,
3069: last_updated_by = l_line_util_rec.last_updated_by,
3070: last_update_login = l_line_util_rec.last_update_login,

Line 3193: FROM ozf_claim_lines_util_all

3189: l_lu_currency_code VARCHAR2(15);
3190:
3191: CURSOR csr_util_obj_ver(cv_line_util_id IN NUMBER) IS
3192: SELECT object_version_number
3193: FROM ozf_claim_lines_util_all
3194: WHERE claim_line_util_id = cv_line_util_id;
3195:
3196: -- fix for bug 5042046
3197: CURSOR csr_function_currency IS

Line 3388: FROM ozf_claim_lines

3384:
3385: -- Cursor to get claim_id
3386: CURSOR csr_claim_id(cv_claim_line_id IN NUMBER) IS
3387: SELECT claim_id
3388: FROM ozf_claim_lines
3389: WHERE claim_line_id = cv_claim_line_id;
3390:
3391: -- fix for bug 5042046
3392: CURSOR csr_function_currency IS

Line 3401: FROM ozf_claim_lines_util

3397: AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
3398:
3399: CURSOR csr_final_lu_acctd_amt(cv_claim_line_id IN NUMBER) IS
3400: SELECT SUM(acctd_amount)
3401: FROM ozf_claim_lines_util
3402: WHERE claim_line_id = cv_claim_line_id;
3403:
3404: BEGIN
3405: --------------------- initialize -----------------------

Line 3504: UPDATE ozf_claim_lines_all

3500: FETCH csr_final_lu_acctd_amt INTO l_final_lu_acctd_amt;
3501: CLOSE csr_final_lu_acctd_amt;
3502:
3503: IF l_final_lu_acctd_amt = 0 OR l_final_lu_acctd_amt IS NULL THEN
3504: UPDATE ozf_claim_lines_all
3505: SET earnings_associated_flag = 'F'
3506: WHERE claim_line_id = p_line_util_tbl(1).claim_line_id;
3507: END IF;
3508:

Line 3601: FROM ozf_claim_lines_util clu

3597: l_access VARCHAR2(1) := 'N';
3598:
3599: CURSOR csr_claim_line_details(cv_line_util_id IN NUMBER) IS
3600: SELECT clu.claim_line_id , clu.currency_code
3601: FROM ozf_claim_lines_util clu
3602: WHERE claim_line_util_id = cv_line_util_id;
3603:
3604: CURSOR csr_utiz_currency(cv_utilization_id IN NUMBER) IS
3605: SELECT fu.currency_code

Line 3611: FROM ozf_claim_lines

3607: where fu.utilization_id = cv_utilization_id;
3608:
3609: CURSOR csr_claim_id(cv_claim_line_id IN NUMBER) IS
3610: SELECT claim_id
3611: FROM ozf_claim_lines
3612: WHERE claim_line_id = cv_claim_line_id;
3613:
3614: CURSOR csr_old_line_util_amt(cv_line_util_id IN NUMBER) IS
3615: SELECT claim_line_id

Line 3620: FROM ozf_claim_lines_util

3616: , utilization_id
3617: , amount
3618: , scan_unit
3619: , util_curr_amount
3620: FROM ozf_claim_lines_util
3621: WHERE claim_line_util_id = cv_line_util_id;
3622:
3623: l_line_util_rec line_util_rec_type;
3624:

Line 3735: DELETE FROM ozf_claim_lines_util_all

3731: IF OZF_DEBUG_HIGH_ON THEN
3732: OZF_Utility_PVT.debug_message(l_full_name ||': delete');
3733: END IF;
3734:
3735: DELETE FROM ozf_claim_lines_util_all
3736: WHERE claim_line_util_id = p_line_util_id
3737: AND object_version_number = p_object_version;
3738:
3739: IF (SQL%NOTFOUND) THEN

Line 3842: FROM ozf_claim_lines_util

3838: quantity,
3839: org_id,
3840: fxgl_acctd_amount,
3841: utilized_acctd_amount
3842: FROM ozf_claim_lines_util
3843: WHERE claim_line_util_id = cv_line_util_id;
3844:
3845: l_line_util_rec csr_line_util%ROWTYPE;
3846:

Line 4505: FROM ozf_claim_lines

4501: , item_type
4502: , item_id
4503: , acctd_amount
4504: -- , claim_currency_amount
4505: FROM ozf_claim_lines
4506: WHERE claim_id = cv_claim_id;
4507:
4508: CURSOR csr_uom_code(cv_item_id IN NUMBER) IS
4509: SELECT primary_uom_code

Line 5201: FROM ozf_claim_lines

5197:
5198: -- Added For Bug 7611966
5199: CURSOR csr_claim_line(cv_claim_id IN NUMBER) IS
5200: SELECT claim_line_id
5201: FROM ozf_claim_lines
5202: WHERE claim_id = cv_claim_id;
5203:
5204: CURSOR csr_uom_code(cv_item_id IN NUMBER) IS
5205: SELECT primary_uom_code

Line 5223: FROM ozf_claim_lines

5219: , activity_type
5220: , activity_id
5221: , item_id
5222: , claim_currency_amount
5223: FROM ozf_claim_lines
5224: WHERE claim_line_id = cv_claim_line_id
5225: AND claim_id = cv_claim_id;
5226:
5227: BEGIN

Line 5560: from ozf_claim_lines_all

5556: , buy_group_cust_account_id
5557: , buy_group_party_id
5558: , select_cust_children_flag
5559: , prorate_earnings_flag
5560: from ozf_claim_lines_all
5561: WHERE claim_line_id = cv_claim_line_id;
5562:
5563: --used to derive old_total_amount
5564: CURSOR csr_sum_util_amounts(cv_claim_line_id IN NUMBER) IS

Line 5566: from ozf_claim_lines_util

5562:
5563: --used to derive old_total_amount
5564: CURSOR csr_sum_util_amounts(cv_claim_line_id IN NUMBER) IS
5565: select sum(amount)
5566: from ozf_claim_lines_util
5567: where claim_line_id = cv_claim_line_id;
5568:
5569: --used to derive old_total_units
5570: CURSOR csr_sum_scan_units(cv_claim_line_id IN NUMBER) IS

Line 5572: from ozf_claim_lines_util

5568:
5569: --used to derive old_total_units
5570: CURSOR csr_sum_scan_units(cv_claim_line_id IN NUMBER) IS
5571: select sum(scan_unit)
5572: from ozf_claim_lines_util
5573: where claim_line_id = cv_claim_line_id;
5574: --end of bugfix 5182452
5575:
5576: BEGIN

Line 5876: FROM ozf_claim_lines

5872: AND INVENTORY_ITEM_ID = cv_product_id;
5873:
5874: CURSOR csr_claim_line(cv_claim_id IN NUMBER) IS
5875: SELECT claim_line_id
5876: FROM ozf_claim_lines
5877: WHERE claim_id = cv_claim_id;
5878:
5879: CURSOR csr_party_name(cv_cust_account_id IN NUMBER) IS
5880: SELECT p.party_name

Line 6295: FROM ozf_claim_lines_util lu, ams_act_products ap

6291: WHERE activity_product_id = cv_activity_product_id;
6292:
6293: CURSOR csr_count_workflow(cv_claim_line_id IN NUMBER) IS
6294: SELECT count(*)
6295: FROM ozf_claim_lines_util lu, ams_act_products ap
6296: ,ozf_claim_lines cln1, ozf_claim_lines cln2
6297: WHERE cln1.claim_line_id = cv_claim_line_id
6298: AND cln1.claim_id = cln2.claim_id
6299: AND lu.claim_line_id = cln2.claim_line_id

Line 6296: ,ozf_claim_lines cln1, ozf_claim_lines cln2

6292:
6293: CURSOR csr_count_workflow(cv_claim_line_id IN NUMBER) IS
6294: SELECT count(*)
6295: FROM ozf_claim_lines_util lu, ams_act_products ap
6296: ,ozf_claim_lines cln1, ozf_claim_lines cln2
6297: WHERE cln1.claim_line_id = cv_claim_line_id
6298: AND cln1.claim_id = cln2.claim_id
6299: AND lu.claim_line_id = cln2.claim_line_id
6300: AND lu.activity_product_id = ap.activity_product_id

Line 6736: FROM ozf_claim_lines_util lu,

6732: -- Modified for Bug 4926327: Adjustment Amounts are doubled for scan data
6733: -- Modified for Bug 5101106 quantity = coupon count * offer qty
6734: CURSOR csr_funds_used_units(cv_activity_product_id IN NUMBER) IS
6735: SELECT NVL(SUM(scan_unit * amp.quantity ), 0)
6736: FROM ozf_claim_lines_util lu,
6737: ozf_claims c,
6738: ozf_claim_lines cl,
6739: ams_act_products amp
6740: WHERE lu.activity_product_id = cv_activity_product_id

Line 6738: ozf_claim_lines cl,

6734: CURSOR csr_funds_used_units(cv_activity_product_id IN NUMBER) IS
6735: SELECT NVL(SUM(scan_unit * amp.quantity ), 0)
6736: FROM ozf_claim_lines_util lu,
6737: ozf_claims c,
6738: ozf_claim_lines cl,
6739: ams_act_products amp
6740: WHERE lu.activity_product_id = cv_activity_product_id
6741: AND cl.claim_line_id = lu.claim_line_id
6742: AND cl.claim_id = c.claim_id

Line 6771: FROM ozf_claims cla, ozf_claim_lines cln

6767: ,cla.exchange_rate
6768: ,cla.source_object_id
6769: ,cln.activity_id --Bugfix 7717638
6770: ,cln.batch_line_id --Bugfix 7811671
6771: FROM ozf_claims cla, ozf_claim_lines cln
6772: WHERE cla.claim_id = cln.claim_id
6773: AND cln.claim_line_id = cv_claim_line_id;
6774:
6775: -- Introduced for Bug4348163

Line 7953: DELETE FROM ozf_claim_lines_util_all

7949:
7950: END IF; -- p_line_util_rec.utilization_id > 0
7951:
7952: ------------------------ delete ------------------------
7953: DELETE FROM ozf_claim_lines_util_all
7954: WHERE claim_line_util_id = p_line_util_rec.claim_line_util_id;
7955:
7956: IF (SQL%NOTFOUND) THEN
7957: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN

Line 8067: FROM ozf_claim_lines_util

8063: where utilization_id = cv_utilization_id ;
8064:
8065: CURSOR csr_final_lu_amt(cv_claim_line_id IN NUMBER) IS
8066: SELECT SUM(amount)
8067: FROM ozf_claim_lines_util
8068: WHERE claim_line_id = cv_claim_line_id;
8069:
8070: -- fix for bug 5042046
8071: CURSOR csr_function_currency IS

Line 8079: SELECT currency_code from ozf_claim_lines

8075: WHERE org.set_of_books_id = gs.set_of_books_id
8076: AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
8077:
8078: CURSOR csr_claim_currency(cv_claim_line_id IN NUMBER) IS
8079: SELECT currency_code from ozf_claim_lines
8080: where claim_line_id = cv_claim_line_id;
8081:
8082: CURSOR csr_offer_currency(cv_plan_id IN NUMBER) IS
8083: SELECT transaction_currency_code

Line 8089: FROM ozf_claim_lines_util

8085: WHERE qp_list_header_id = cv_plan_id;
8086:
8087: CURSOR csr_over_util(cv_claim_line_id IN NUMBER, cv_act_product_id IN NUMBER) IS
8088: SELECT claim_line_util_id, acctd_amount, scan_unit
8089: FROM ozf_claim_lines_util
8090: WHERE claim_line_id = cv_claim_line_id
8091: AND activity_product_id = cv_act_product_id
8092: AND utilization_id = -1;
8093:

Line 8096: FROM ozf_claim_lines_util util

8092: AND utilization_id = -1;
8093:
8094: CURSOR csr_acc_over_util(cv_claim_line_id IN NUMBER, cv_offer_id IN NUMBER) IS
8095: SELECT claim_line_util_id, acctd_amount
8096: FROM ozf_claim_lines_util util
8097: WHERE claim_line_id = cv_claim_line_id
8098: AND activity_product_id = cv_offer_id
8099: AND utilization_id = -2;
8100:

Line 8112: FROM ozf_claim_lines_util

8108: WHERE activity_product_id = cv_activity_product_id;
8109:
8110: CURSOR csr_utiz_amount(cv_line_util_id IN NUMBER) IS
8111: SELECT util_curr_amount
8112: FROM ozf_claim_lines_util
8113: WHERE claim_line_util_id = cv_line_util_id;
8114:
8115: BEGIN
8116: --------------------- initialize -----------------------

Line 8441: UPDATE ozf_claim_lines_all

8437: CLOSE csr_final_lu_amt;
8438:
8439: IF l_final_lu_amt = 0 OR
8440: l_final_lu_amt IS NULL THEN
8441: UPDATE ozf_claim_lines_all
8442: SET earnings_associated_flag = 'F'
8443: WHERE claim_line_id = p_funds_util_flt.claim_line_id;
8444: END IF;
8445:

Line 8546: FROM ozf_claim_lines_util

8542: ,quantity
8543: ,amount
8544: ,scan_unit
8545: ,utilization_id
8546: FROM ozf_claim_lines_util
8547: WHERE claim_line_util_id = cv_claim_line_util_id;
8548:
8549: CURSOR csr_funds_utilized(cv_activity_product_id IN NUMBER) IS
8550: SELECT utilization_id

Line 8659: DELETE FROM ozf_claim_lines_util_all

8655: */
8656: END IF;
8657:
8658: -- delete dummy utilizations
8659: DELETE FROM ozf_claim_lines_util_all
8660: WHERE claim_line_util_id = p_claim_line_util_id;
8661:
8662: ---------- Create Group Line Utils -------------------
8663: IF l_counter > 1 THEN

Line 8794: FROM ozf_claim_lines_util lu, ams_act_products ap

8790: ,lu.activity_product_id
8791: ,lu.scan_unit
8792: ,ap.act_product_used_by_id
8793: ,ap.adjustment_flag
8794: FROM ozf_claim_lines_util lu, ams_act_products ap
8795: ,ozf_claim_lines cln
8796: WHERE cln.claim_id = cv_claim_id
8797: AND lu.claim_line_id = cln.claim_line_id
8798: AND lu.activity_product_id = ap.activity_product_id

Line 8795: ,ozf_claim_lines cln

8791: ,lu.scan_unit
8792: ,ap.act_product_used_by_id
8793: ,ap.adjustment_flag
8794: FROM ozf_claim_lines_util lu, ams_act_products ap
8795: ,ozf_claim_lines cln
8796: WHERE cln.claim_id = cv_claim_id
8797: AND lu.claim_line_id = cln.claim_line_id
8798: AND lu.activity_product_id = ap.activity_product_id
8799: AND lu.utilization_id = -1

Line 8807: FROM ozf_claim_lines_util lu, ozf_claim_lines ln

8803: CURSOR csr_acc_over_utilized(cv_claim_id IN NUMBER) IS
8804: SELECT lu.claim_line_util_id
8805: ,lu.activity_product_id
8806: ,lu.acctd_amount
8807: FROM ozf_claim_lines_util lu, ozf_claim_lines ln
8808: WHERE ln.claim_id = cv_claim_id
8809: AND lu.claim_line_id = ln.claim_line_id
8810: AND lu.utilization_id = -2;
8811: */

Line 8818: FROM ozf_claim_lines_util_all lu

8814: -- the coupon count(scan_unit) is independent of the offer and claim uom code
8815: CURSOR csr_line_utils(cv_claim_id IN NUMBER) IS
8816: SELECT SUM(lu.scan_unit * amp.quantity)
8817: ,lu.utilization_id
8818: FROM ozf_claim_lines_util_all lu
8819: ,ozf_claim_lines_all cln
8820: ,ams_act_products amp
8821: WHERE cln.claim_id = cv_claim_id
8822: AND lu.claim_line_id = cln.claim_line_id

Line 8819: ,ozf_claim_lines_all cln

8815: CURSOR csr_line_utils(cv_claim_id IN NUMBER) IS
8816: SELECT SUM(lu.scan_unit * amp.quantity)
8817: ,lu.utilization_id
8818: FROM ozf_claim_lines_util_all lu
8819: ,ozf_claim_lines_all cln
8820: ,ams_act_products amp
8821: WHERE cln.claim_id = cv_claim_id
8822: AND lu.claim_line_id = cln.claim_line_id
8823: AND lu.activity_product_id = amp.activity_product_id

Line 9242: FROM ozf_claim_lines_util lu, ozf_funds_utilized_all_b fu, ozf_claim_lines ln

9238: l_cust_setup_id NUMBER;
9239:
9240: CURSOR csr_line_util(cv_claim_id IN NUMBER) IS
9241: SELECT fu.plan_id, fu.cust_account_id, ln.activity_type
9242: FROM ozf_claim_lines_util lu, ozf_funds_utilized_all_b fu, ozf_claim_lines ln
9243: WHERE lu.utilization_id = fu.utilization_id
9244: AND lu.claim_line_id = ln.claim_line_id
9245: AND ln.claim_id = cv_claim_id
9246: GROUP BY fu.plan_id, fu.cust_account_id, ln.activity_type;

Line 9377: FROM ozf_claim_lines_util lu, ozf_funds_utilized_all_b fu

9373: WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
9374:
9375: CURSOR csr_line_util(cv_claim_id IN NUMBER) IS
9376: SELECT fu.plan_id, fu.cust_account_id, sum(lu.acctd_amount)
9377: FROM ozf_claim_lines_util lu, ozf_funds_utilized_all_b fu
9378: , ozf_claim_lines ln
9379: WHERE lu.utilization_id = fu.utilization_id
9380: AND lu.claim_line_id = ln.claim_line_id
9381: AND ln.claim_id = cv_claim_id

Line 9378: , ozf_claim_lines ln

9374:
9375: CURSOR csr_line_util(cv_claim_id IN NUMBER) IS
9376: SELECT fu.plan_id, fu.cust_account_id, sum(lu.acctd_amount)
9377: FROM ozf_claim_lines_util lu, ozf_funds_utilized_all_b fu
9378: , ozf_claim_lines ln
9379: WHERE lu.utilization_id = fu.utilization_id
9380: AND lu.claim_line_id = ln.claim_line_id
9381: AND ln.claim_id = cv_claim_id
9382: GROUP BY fu.plan_id, fu.cust_account_id;

Line 9493: FROM ozf_claim_lines_util lu, ozf_funds_utilized_all_b fu, ozf_claim_lines ln

9489: l_counter PLS_INTEGER := 0;
9490:
9491: CURSOR csr_line_util(cv_claim_id IN NUMBER) IS
9492: SELECT fu.plan_id, fu.cust_account_id, ln.activity_type
9493: FROM ozf_claim_lines_util lu, ozf_funds_utilized_all_b fu, ozf_claim_lines ln
9494: WHERE lu.utilization_id = fu.utilization_id
9495: AND lu.claim_line_id = ln.claim_line_id
9496: AND ln.claim_id = cv_claim_id
9497: GROUP BY fu.plan_id, fu.cust_account_id, ln.activity_type;

Line 9563: FROM ozf_claim_lines_util lu, ozf_claim_lines ln

9559: l_counter PLS_INTEGER := 0;
9560:
9561: CURSOR csr_pay_over_earn(cv_claim_id IN NUMBER) IS
9562: SELECT lu.activity_product_id, sum(lu.acctd_amount)
9563: FROM ozf_claim_lines_util lu, ozf_claim_lines ln
9564: WHERE lu.claim_line_id = ln.claim_line_id
9565: AND lu.utilization_id = -2
9566: AND ln.claim_id = cv_claim_id
9567: GROUP BY lu.activity_product_id;

Line 9722: FROM ozf_claim_lines_all lines

9718: , item_id
9719: -- , acctd_amount
9720: , amount --7169388
9721: , fund.approved_in_currency --7231613
9722: FROM ozf_claim_lines_all lines
9723: , ozf_offers offr
9724: , ozf_act_budgets fund
9725: , ozf_sd_request_headers_all_b req
9726: WHERE claim_id = cv_claim_id

Line 9842: , ozf_claim_lines line

9838:
9839: CURSOR csr_access(cv_claim_id IN NUMBER) IS
9840: SELECT resource_id
9841: FROM ozf_sd_request_access req
9842: , ozf_claim_lines line
9843: , ozf_claims cla
9844: WHERE cla.claim_id = cv_claim_id
9845: AND cla.claim_id = line.claim_id
9846: AND line.activity_id = req.request_header_id