[Home] [Help]
322: FND_MESSAGE.get_string('OZF','OZF_RESALE_SELL_PRICE_NULL'),
323: 'SELLING_PRICE',
324: NULL,
325: org_id
326: FROM ozf_resale_lines_int_all b
327: WHERE b.status_code = 'OPEN'
328: AND b.direct_customer_flag = 'T'
329: AND b.selling_price IS NULL
330: AND b.resale_batch_id = p_resale_batch_id
341: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
342: END;
343:
344: BEGIN
345: UPDATE ozf_resale_lines_int_all
346: SET status_code = 'DISPUTED',
347: dispute_code = 'OZF_RESALE_SELL_PRICE_NULL'
348: WHERE status_code = 'OPEN'
349: AND direct_customer_flag = 'T'
374: FND_MESSAGE.get_string('OZF','OZF_RESALE_SELL_PRICE_NULL'),
375: 'SELLING_PRICE',
376: NULL,
377: b.org_id
378: FROM ozf_resale_lines_all b
379: , ozf_resale_batch_line_maps_all c
380: WHERE b.direct_customer_flag = 'T'
381: AND b.selling_price IS NULL
382: AND b.resale_line_id = c.resale_line_id
441: -- It creates resale lines and accruals based on the discount information.
442: --
443: -- PARAMETERS
444: -- p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype,
445: -- p_resale_line_rec IN OZF_RESALE_LINES_ALL%rowtype,
446: -- p_line_result_rec IN OZF_ORDER_PRICE_PVT.LINE_REC_TYPE,
447: -- p_header_id IN NUMBER,
448: -- p_resale_batch_id IN NUMBER,
449: -- p_inventory_tracking IN BOOLEAN,
457: --
458: ---------------------------------------------------------------------
459: PROCEDURE Process_One_Line(
460: p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype,
461: p_resale_line_rec IN OZF_RESALE_LINES%rowtype,
462: p_line_result_rec IN OZF_ORDER_PRICE_PVT.LINE_REC_TYPE,
463: p_header_id IN NUMBER,
464: p_resale_batch_id IN NUMBER,
465: p_inventory_tracking IN BOOLEAN,
504:
505: l_is_valid_offer BOOLEAN;
506: l_price_diff_util BOOLEAN;
507: l_object_type VARCHAR2(30);
508: l_line_int_rec OZF_RESALE_LINES_INT%rowtype;
509:
510: l_rate NUMBER;
511: l_exchange_type VARCHAR2(30);
512: l_to_create_utilization BOOLEAN;
1249: CURSOR order_identifiers_csr (p_id IN NUMBER) IS
1250: SELECT order_number
1251: , bill_to_cust_account_id
1252: , date_ordered
1253: FROM ozf_resale_lines_int_all
1254: WHERE resale_line_int_id = p_id;
1255:
1256: CURSOR resale_info_csr (p_id IN NUMBER) IS
1257: SELECT resale_header_id
1254: WHERE resale_line_int_id = p_id;
1255:
1256: CURSOR resale_info_csr (p_id IN NUMBER) IS
1257: SELECT resale_header_id
1258: FROM ozf_resale_lines
1259: WHERE resale_line_id = p_id;
1260:
1261: l_order_number VARCHAR2(30);
1262: l_cust_account_id NUMBER;
1266: l_log_id NUMBER;
1267:
1268: CURSOR resale_rec_csr (p_id IN NUMBER) IS
1269: SELECT *
1270: FROM ozf_resale_lines
1271: WHERE resale_line_id = p_id;
1272:
1273: l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype;
1274: l_resale_rec OZF_RESALE_LINES%rowtype;
1270: FROM ozf_resale_lines
1271: WHERE resale_line_id = p_id;
1272:
1273: l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype;
1274: l_resale_rec OZF_RESALE_LINES%rowtype;
1275:
1276: l_header_id NUMBER;
1277: l_line_id NUMBER;
1278:
1302: , p_cust_id IN NUMBER
1303: , p_date IN DATE
1304: ) IS
1305: SELECT 1
1306: FROM ozf_resale_lines_int_all
1307: WHERE resale_batch_id = p_id
1308: AND order_number = p_order_number
1309: AND bill_to_cust_account_id = p_cust_id
1310: AND date_ordered = p_date
1321: , p_date IN DATE
1322: ) IS
1323: SELECT a.resale_header_id
1324: FROM ozf_resale_headers_all a
1325: , ozf_resale_lines_int_all b
1326: , ozf_resale_lines_all c
1327: WHERE b.resale_batch_id = p_id
1328: AND b.order_number = p_order_number
1329: AND b.bill_to_cust_account_id = p_cust_id
1322: ) IS
1323: SELECT a.resale_header_id
1324: FROM ozf_resale_headers_all a
1325: , ozf_resale_lines_int_all b
1326: , ozf_resale_lines_all c
1327: WHERE b.resale_batch_id = p_id
1328: AND b.order_number = p_order_number
1329: AND b.bill_to_cust_account_id = p_cust_id
1330: AND b.date_ordered = p_date
1407: -- IF there is an error for a line or lines, we need to UPDATE the whole order as error;
1408: -- nothing to UPDATE if it's FROM line.
1409: IF p_caller_type = 'IFACE' THEN
1410: BEGIN
1411: UPDATE ozf_resale_lines_int_all
1412: SET status_code = 'DISPUTED'
1413: WHERE status_code = 'OPEN'
1414: AND order_NUMBER = l_order_number
1415: AND bill_to_cust_account_id = l_cust_account_id
1585: RAISE FND_API.g_exc_unexpected_error;
1586: END IF;
1587:
1588: -- Delink resale interface line from batch
1589: UPDATE ozf_resale_lines_int_all
1590: SET resale_batch_id = null,
1591: request_id = null,
1592: status_code = 'DISPUTED',
1593: dispute_code = 'OZF_LT_INVT'
1730: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1731: -- drop this line from batch if it is from interface
1732: IF p_caller_type = 'IFACE' THEN
1733: BEGIN
1734: UPDATE ozf_resale_lines_int_all
1735: SET resale_batch_id = NULL,
1736: request_id = NULL,
1737: status_code = 'DISPUTED',
1738: dispute_code = 'OZF_PRIC_RESULT_ERR'
1746: ELSE
1747: -- CLOSE THIS LINE If it is from interface
1748: IF p_caller_type = 'IFACE' THEN
1749: BEGIN
1750: UPDATE ozf_resale_lines_int_all
1751: SET status_code= 'CLOSED'
1752: WHERE resale_line_int_id = p_line_tbl(i).chargeback_int_id;
1753: EXCEPTION
1754: WHEN OTHERS THEN
1829: , a.header_attribute13
1830: , a.header_attribute14
1831: , a.header_attribute15
1832: FROM ozf_resale_headers_all a
1833: , ozf_resale_lines_all b
1834: , ozf_resale_batch_line_maps_all c
1835: WHERE a.resale_header_id = b.resale_header_id
1836: AND b.resale_line_id = c.resale_line_id
1837: AND c.resale_batch_id = p_resale_batch_id;
1847: l_header_rec order_header_csr%rowtype;
1848:
1849: CURSOR order_set_csr(p_header_id in NUMBER) IS
1850: SELECT *
1851: FROM ozf_resale_lines
1852: WHERE resale_header_id = p_header_id;
1853:
1854: TYPE resale_lines_tbl_type is TABLE OF order_set_csr%rowtype INDEX BY binary_integer;
1855: l_order_set_tbl resale_lines_tbl_type;
2603: CURSOR order_num_csr is
2604: SELECT distinct order_NUMBER,
2605: bill_to_cust_account_id,
2606: date_ordered
2607: FROM ozf_resale_lines_int
2608: WHERE status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
2609: AND direct_customer_flag = 'F'
2610: AND resale_batch_id = p_resale_batch_id
2611: ORDER BY date_ordered;
2617: CURSOR interface_rec_csr(p_num in VARCHAR2,
2618: p_name in VARCHAR2,
2619: p_date in date) IS
2620: SELECT *
2621: FROM ozf_resale_lines_int
2622: WHERE order_NUMBER = p_num
2623: AND bill_to_cust_account_id = p_name
2624: AND date_ordered = p_date
2625: AND direct_customer_flag ='F'
2714: DELETE FROM ozf_resale_logs_all a
2715: WHERE a.resale_id_type = 'IFACE'
2716: AND a.resale_id IN (
2717: SELECT resale_line_int_id
2718: FROM ozf_resale_lines_int_all b
2719: WHERE b.direct_customer_flag = 'F'
2720: AND b.status_code = 'OPEN'
2721: AND b.order_number = l_order_num_tbl(i).order_number
2722: AND b.bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
2767: END IF;
2768: END LOOP;
2769:
2770: BEGIN
2771: UPDATE ozf_resale_lines_int
2772: SET status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
2773: WHERE direct_customer_flag ='F'
2774: AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
2775: AND resale_batch_id = p_resale_batch_id;
2854: CURSOR order_num_csr IS
2855: SELECT DISTINCT order_NUMBER,
2856: bill_to_cust_account_id,
2857: date_ordered
2858: FROM ozf_resale_lines_int_all
2859: WHERE status_code IN ('OPEN', 'DUPLICATED')
2860: AND duplicated_adjustment_id IS NULL
2861: AND resale_batch_id = p_resale_batch_id
2862: ORDER BY date_ordered;
2870: p_id IN NUMBER,
2871: p_date IN DATE
2872: ) IS
2873: SELECT *
2874: FROM ozf_resale_lines_int_all
2875: WHERE order_number = p_order_number
2876: AND bill_to_cust_account_id= p_id
2877: AND date_ordered = p_date
2878: AND status_code IN ('OPEN', 'DUPLICATED')
2907: p_id IN NUMBER,
2908: p_date IN DATE
2909: ) IS
2910: SELECT *
2911: FROM ozf_resale_lines_int_all
2912: WHERE order_number = p_order_number
2913: AND bill_to_cust_account_id = p_id
2914: AND date_ordered = p_date
2915: AND status_code = 'DUPLICATED'
2921: p_id IN NUMBER,
2922: p_date IN DATE
2923: ) IS
2924: SELECT *
2925: FROM ozf_resale_lines_int_all
2926: WHERE order_number = p_order_number -- ?? need this
2927: AND bill_to_cust_account_id = p_id -- ?? need this
2928: AND date_ordered = p_date
2929: AND status_code IN ('OPEN', 'DUPLICATED')
2932:
2933: -- [BEGIN OF BUG 4233341 FIXING]
2934: CURSOR csr_valid_line_count(cv_batch_id IN NUMBER) IS
2935: SELECT COUNT(1)
2936: FROM ozf_resale_lines_int_all
2937: WHERE status_code IN ('PROCESSED', 'CLOSED', 'DUPLICATED')
2938: AND resale_batch_id = cv_batch_id;
2939:
2940: CURSOR csr_get_batch_number(cv_batch_id IN NUMBER) IS
2950: , lk.meaning dispute_code
2951: , lg.error_message
2952: , lg.column_name
2953: , lg.column_value
2954: FROM ozf_resale_lines_int_all i
2955: , ozf_resale_logs_all lg
2956: , ozf_lookups lk
2957: WHERE i.dispute_code = lk.lookup_code(+)
2958: AND lk.lookup_type(+) = 'OZF_RESALE_DISPUTE_CODE'
2968: i_output_idx NUMBER;
2969:
2970: /*
2971: TYPE output_dispute_line_id_tbl IS
2972: TABLE OF ozf_resale_lines_int_all.resale_line_int_id%TYPE
2973: INDEX BY BINARY_INTEGER;
2974:
2975: TYPE output_dispute_code_tbl IS
2976: TABLE OF ozf_lookups.meaning%TYPE
2976: TABLE OF ozf_lookups.meaning%TYPE
2977: INDEX BY BINARY_INTEGER;
2978:
2979: TYPE output_dispute_bill_to_tbl IS
2980: TABLE OF ozf_resale_lines_int_all.bill_to_party_name%TYPE
2981: INDEX BY BINARY_INTEGER;
2982:
2983: TYPE output_dispute_order_tbl IS
2984: TABLE OF ozf_resale_lines_int_all.order_number%ROWTYPE
2980: TABLE OF ozf_resale_lines_int_all.bill_to_party_name%TYPE
2981: INDEX BY BINARY_INTEGER;
2982:
2983: TYPE output_dispute_order_tbl IS
2984: TABLE OF ozf_resale_lines_int_all.order_number%ROWTYPE
2985: INDEX BY BINARY_INTEGER;
2986:
2987: TYPE output_dispute_order_date_tbl IS
2988: TABLE OF ozf_resale_lines_int_all.date_ordered%ROWTYPE
2984: TABLE OF ozf_resale_lines_int_all.order_number%ROWTYPE
2985: INDEX BY BINARY_INTEGER;
2986:
2987: TYPE output_dispute_order_date_tbl IS
2988: TABLE OF ozf_resale_lines_int_all.date_ordered%ROWTYPE
2989: INDEX BY BINARY_INTEGER;
2990:
2991: TYPE output_dispute_item_tbl IS
2992: TABLE OF ozf_resale_lines_int_all.item_number%ROWTYPE
2988: TABLE OF ozf_resale_lines_int_all.date_ordered%ROWTYPE
2989: INDEX BY BINARY_INTEGER;
2990:
2991: TYPE output_dispute_item_tbl IS
2992: TABLE OF ozf_resale_lines_int_all.item_number%ROWTYPE
2993: INDEX BY BINARY_INTEGER;
2994:
2995: l_output_dispute_code_tbl output_dispute_code_tbl;
2996: l_output_dispute_bill_to_tbl output_dispute_bill_to_tbl;
3097: END LOOP;
3098: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3099:
3100: -- remove all the disputed lines
3101: UPDATE ozf_resale_lines_int
3102: SET resale_batch_id = null
3103: , request_id = null -- [BUG 4233341 FIXING]
3104: Where resale_batch_id = p_resale_batch_id
3105: and status_code = 'DISPUTED';
3198: l_order_num_tbl(i).bill_to_cust_account_id IS NOT NULL AND
3199: l_order_num_tbl(i).date_ordered IS NOT NULL THEN
3200:
3201: -- UPDATE tracing order lines to processed for this order to be processed
3202: UPDATE ozf_resale_lines_int_all
3203: SET status_code= 'PROCESSED'
3204: WHERE status_code = 'OPEN'
3205: AND order_number = l_order_num_tbl(i).order_number
3206: AND bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
3491: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3492: ozf_utility_pvt.error_message('OZF_GET_ORDER_PRIC_ERR');
3493:
3494: BEGIN
3495: UPDATE ozf_resale_lines_int
3496: SET status_code = 'DISPUTED'
3497: WHERE status_code = 'OPEN'
3498: AND order_number = l_order_num_tbl(i).order_number
3499: AND bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
3590: END;
3591: END LOOP;
3592:
3593: BEGIN
3594: UPDATE ozf_resale_lines_int_all
3595: SET status_code = 'DISPUTED'
3596: WHERE status_code = 'OPEN'
3597: AND order_number = l_order_num_tbl(i).order_number
3598: AND bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
3695: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3696:
3697: -- [BEGIN OF BUG 4233341 FIXING]
3698: -- delink all the new/processed/disputed lines
3699: UPDATE ozf_resale_lines_int_all
3700: SET resale_batch_id = null
3701: , request_id = null
3702: WHERE resale_batch_id = p_resale_batch_id
3703: AND status_code IN ('NEW', 'OPEN', 'DISPUTED'); -- 'PROCESSED'
3824: i NUMBER:=1;
3825:
3826: CURSOR account_id_csr (p_id NUMBER)IS
3827: SELECT distinct sold_from_cust_account_id, org_id, currency_code
3828: FROM ozf_resale_lines_int
3829: WHERE resale_batch_id IS NULL
3830: AND request_id = p_id;
3831:
3832: l_sold_from_cust_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
3878: , orli.sold_from_email
3879: , orli.sold_from_phone
3880: , orli.sold_from_fax
3881: ,orli.currency_code
3882: FROM ozf_resale_lines_int orli
3883: WHERE orli.resale_batch_id IS NULL
3884: AND orli.sold_from_cust_account_id = p_id
3885: AND orli.request_id = G_CONC_REQUEST_ID
3886: AND orli.org_id = p_org_id
3901: CURSOR start_end_date_csr( p_account_id NUMBER,
3902: p_org_id NUMBER,
3903: p_currency_code VARCHAR2) IS
3904: SELECT MIN(date_ordered), MAX(date_ordered)
3905: FROM ozf_resale_lines_int_all
3906: WHERE sold_from_cust_account_id = p_account_id
3907: AND request_id = FND_GLOBAL.CONC_REQUEST_ID
3908: AND org_id = p_org_id
3909: AND currency_code = p_currency_code;
3956: IF OZF_DEBUG_LOW_ON THEN
3957: ozf_utility_PVT.debug_message('source code is '|| p_data_source_code||', id:' || FND_GLOBAL.CONC_REQUEST_ID );
3958: END IF;
3959: END IF;
3960: UPDATE ozf_resale_lines_int_all
3961: SET request_id = G_CONC_REQUEST_ID
3962: , dispute_code = null
3963: , program_application_id = FND_GLOBAL.PROG_APPL_ID
3964: , program_update_date = SYSDATE
3970: ELSE
3971: IF OZF_DEBUG_LOW_ON THEN
3972: ozf_utility_PVT.debug_message('source code is '|| p_data_source_code||', id:' || FND_GLOBAL.CONC_REQUEST_ID );
3973: END IF;
3974: UPDATE ozf_resale_lines_int_all
3975: SET request_id = G_CONC_REQUEST_ID
3976: , dispute_code = null
3977: , program_application_id = FND_GLOBAL.PROG_APPL_ID
3978: , program_update_date = SYSDATE
3984: END IF;
3985:
3986: DELETE FROM ozf_resale_logs_all
3987: WHERE resale_id IN (SELECT resale_line_int_id
3988: FROM ozf_resale_lines_int
3989: WHERE request_id = G_CONC_REQUEST_ID)
3990: AND resale_id_type = 'IFACE';
3991:
3992: -- [BUG 4233341 FIXING: Add program output file]
4118: l_batch_tbl(i) := l_resale_batch_id;
4119: l_sold_from_cust_name_tbl(i) := l_party_name;
4120: i := i +1;
4121:
4122: UPDATE ozf_resale_lines_int_all orli
4123: SET resale_batch_id = l_resale_batch_id
4124: WHERE orli.sold_from_cust_account_id = l_sold_from_cust_id_tbl(j)
4125: AND orli.org_id = l_org_id_tbl(j)
4126: AND orli.currency_code = l_currency_code_tbl(j)