[Home] [Help]
3705:
3706: -- Bug 11710754
3707: -- x_1ary_unit_price := (l_txn_qty * l_txn_unit_price) / x_1ary_qty;
3708: IF p_interface_source_code = 'RCV' THEN
3709: l_debug_info := 'source table is RCV_TRANSACTIONS_INTERFACE';
3710: INL_LOGGING_PVT.Log_Statement(
3711: p_module_name => g_module_name,
3712: p_procedure_name => l_program_name,
3713: p_debug_info => l_debug_info);
3713: p_debug_info => l_debug_info);
3714:
3715: SELECT rti.uom_code, muom.uom_code, pl.unit_price
3716: INTO l_rcv_uom_code, l_po_uom_code, l_po_unit_price
3717: FROM rcv_transactions_interface rti,
3718: po_lines_all pl,
3719: mtl_units_of_measure muom
3720: WHERE muom.unit_of_measure = pl.unit_meas_lookup_code
3721: AND pl.po_line_id = rti.po_line_id
3761: p_procedure_name => l_program_name,
3762: p_debug_info => l_debug_info);
3763:
3764: ELSE
3765: l_debug_info := 'Source is different from RCV_TRANSACTIONS_INTERFACE';
3766: INL_LOGGING_PVT.Log_Statement(
3767: p_module_name => g_module_name,
3768: p_procedure_name => l_program_name,
3769: p_debug_info => l_debug_info);
9726: p_module_name => g_module_name,
9727: p_procedure_name => l_program_name,
9728: p_debug_info => l_debug_info
9729: ) ;
9730: IF x_new_to_parent_table_name = 'RCV_TRANSACTIONS' THEN
9731: x_new_to_parent_table_name := 'INL_SHIP_LINES';
9732: SELECT lcm_shipment_line_id
9733: INTO x_new_to_parent_table_id
9734: FROM rcv_transactions rt
9730: IF x_new_to_parent_table_name = 'RCV_TRANSACTIONS' THEN
9731: x_new_to_parent_table_name := 'INL_SHIP_LINES';
9732: SELECT lcm_shipment_line_id
9733: INTO x_new_to_parent_table_id
9734: FROM rcv_transactions rt
9735: WHERE rt.transaction_id = x_new_to_parent_table_id
9736: ;
9737: END IF;
9738: Set_existingMatchInfoFlag(
10319: SELECT
10320: MIN(DECODE(mp.process_enabled_flag,'Y',NVL(mi.adj_group_date,p_initial_sysdate),p_initial_sysdate)) adj_group_date, -- OPM Integration
10321: mi.group_id
10322: FROM inl_matches_int mi,
10323: rcv_transactions rt, -- OPM Integration
10324: mtl_parameters mp
10325: WHERE mi.processing_status_code = 'RUNNING' /* Point 1: If any change occur here the other 4 points might be affected */
10326: AND (p_group_id IS NULL
10327: OR p_group_id = mi.group_id)
10324: mtl_parameters mp
10325: WHERE mi.processing_status_code = 'RUNNING' /* Point 1: If any change occur here the other 4 points might be affected */
10326: AND (p_group_id IS NULL
10327: OR p_group_id = mi.group_id)
10328: AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
10329: AND mi.to_parent_table_id = rt.transaction_id
10330: AND rt.organization_id = mp.organization_id
10331: AND mi.request_id = l_fnd_conc_request_id_int --Bug#11794442
10332: group by mi.group_id
10361: mi.group_id, --BUG#8264388
10362: mi.charge_line_type_id, --BUG#8264388
10363: mi.tax_code --BUG#8264388
10364: FROM inl_matches_int mi,
10365: rcv_transactions rt, -- OPM Integration
10366: mtl_parameters mp -- OPM Integration
10367: WHERE mi.processing_status_code = 'RUNNING'
10368: AND ((pc_group_id IS NULL AND mi.group_id IS NULL)
10369: OR mi.group_id = pc_group_id) --Bug#11794442
10367: WHERE mi.processing_status_code = 'RUNNING'
10368: AND ((pc_group_id IS NULL AND mi.group_id IS NULL)
10369: OR mi.group_id = pc_group_id) --Bug#11794442
10370: AND mi.match_amounts_flag = 'Y'
10371: AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
10372: AND mi.to_parent_table_id = rt.transaction_id
10373: AND rt.organization_id = mp.organization_id
10374: AND mi.request_id = l_fnd_conc_request_id_int --Bug#11794442
10375: GROUP BY
10417: NULL org_id , --Bug#10381495
10418: mi.match_amounts_flag , --BUG#8264388
10419: mi.group_id --BUG#8264388
10420: FROM inl_matches_int mi,
10421: rcv_transactions rt, -- OPM Integration
10422: mtl_parameters mp
10423: WHERE mi.processing_status_code = 'RUNNING' /* Point 1: If any change occur here the other 4 points might be affected */
10424: AND ((pc_group_id IS NULL AND mi.group_id IS NULL)
10425: OR mi.group_id = pc_group_id) --Bug#11794442
10422: mtl_parameters mp
10423: WHERE mi.processing_status_code = 'RUNNING' /* Point 1: If any change occur here the other 4 points might be affected */
10424: AND ((pc_group_id IS NULL AND mi.group_id IS NULL)
10425: OR mi.group_id = pc_group_id) --Bug#11794442
10426: AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
10427: AND mi.to_parent_table_id = rt.transaction_id
10428: AND rt.organization_id = mp.organization_id
10429: AND mi.request_id = l_fnd_conc_request_id_int --Bug#11794442
10430: order by DECODE(mp.process_enabled_flag,'Y',NVL(mi.adj_group_date,pc_initial_sysdate),pc_initial_sysdate), -- OPM Integration
10439: SELECT sh.ship_num
10440: FROM inl_ship_lines sl,
10441: inl_ship_headers sh,
10442: inl_matches_int mi,
10443: rcv_transactions rt
10444: WHERE sh.ship_header_id = sl.ship_header_id
10445: AND sl.ship_line_id = rt.lcm_shipment_line_id
10446: AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
10447: AND mi.to_parent_table_id = rt.transaction_id
10442: inl_matches_int mi,
10443: rcv_transactions rt
10444: WHERE sh.ship_header_id = sl.ship_header_id
10445: AND sl.ship_line_id = rt.lcm_shipment_line_id
10446: AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
10447: AND mi.to_parent_table_id = rt.transaction_id
10448: AND mi.group_id = pc_group_id
10449: FOR UPDATE OF sh.ship_header_id NOWAIT;
10450:
12378: p_module_name => g_module_name,
12379: p_procedure_name => l_program_name,
12380: p_debug_info => 'Verify if any related Ship Line has an ELC update');
12381:
12382: IF p_to_parent_table_name = 'RCV_TRANSACTIONS' THEN
12383:
12384: INL_LOGGING_PVT.Log_Statement(
12385: p_module_name => g_module_name,
12386: p_procedure_name => l_program_name,
12383:
12384: INL_LOGGING_PVT.Log_Statement(
12385: p_module_name => g_module_name,
12386: p_procedure_name => l_program_name,
12387: p_debug_info => 'Verify if Ship Line has an ELC update to parent RCV_TRANSACTIONS with FOR UPDATE of inl_ship_headers');
12388:
12389: SELECT
12390: sl.new_currency_conversion_rate,
12391: sl.new_currency_conversion_date,
12398: l_new_currency_conversion_type,
12399: l_new_txn_unit_price,
12400: l_ship_num -- SCM-051
12401: FROM
12402: rcv_transactions rt,
12403: inl_adj_ship_lines_v sl,
12404: inl_ship_headers_all sh -- SCM-051
12405: WHERE
12406: rt.transaction_id = p_to_parent_table_id
12493: p_module_name => g_module_name,
12494: p_procedure_name => l_program_name,
12495: p_debug_info => 'Verify if any related charge Line has an ELC update');
12496:
12497: IF p_to_parent_table_name = 'RCV_TRANSACTIONS' THEN
12498:
12499: INL_LOGGING_PVT.Log_Statement(
12500: p_module_name => g_module_name,
12501: p_procedure_name => l_program_name,
12505: SELECT sh.ship_num
12506: INTO l_ship_num
12507: FROM inl_ship_headers_all sh,
12508: inl_ship_lines_all sl,
12509: rcv_transactions rt
12510: WHERE sh.ship_header_id = sl.ship_header_id
12511: AND rt.transaction_id = p_to_parent_table_id
12512: AND NVL(sl.parent_ship_line_id,sl.ship_line_id) = rt.lcm_shipment_line_id
12513: AND ROWNUM = 1
12516:
12517: INL_LOGGING_PVT.Log_Statement(
12518: p_module_name => g_module_name,
12519: p_procedure_name => l_program_name,
12520: p_debug_info => 'Verify if Charge Line has an ELC update to parent RCV_TRANSACTIONS');
12521:
12522: SELECT
12523: NVL(COUNT(*),0)
12524: INTO
12526: FROM
12527: inl_adj_charge_lines_v cl,
12528: inl_associations a,
12529: inl_ship_lines_all sl,
12530: rcv_transactions rt
12531: WHERE
12532: rt.transaction_id = p_to_parent_table_id
12533: AND NVL(sl.parent_ship_line_id,sl.ship_line_id) = rt.lcm_shipment_line_id
12534: AND a.from_parent_table_name = 'INL_CHARGE_LINES'
13177: 'INL_SHIP_LINES',
13178: 'INL_SHIP_LINE_GROUPS',
13179: 'INL_CHARGE_LINES',
13180: 'INL_TAX_LINES',
13181: 'RCV_TRANSACTIONS'
13182: )
13183: OR (p_transaction_type = 'CREATE'
13184: AND p_to_parent_table_name IN('INL_SHIP_LINES_INT',
13185: 'INL_SHIP_HEADERS_INT')
13316: WHERE sh.ship_header_int_id = p_to_parent_table_id;
13317: l_INT_importedOk := 'Y';
13318: */--Bug#9660043
13319:
13320: ELSIF p_to_parent_table_name = 'RCV_TRANSACTIONS' THEN
13321: --Bug#9660043
13322: l_INT_importedOk := 'Y';
13323:
13324: IF p_match_type_code IN('ITEM', 'CHARGE', 'TAX') THEN
13331: INTO
13332: l_ship_line_id,
13333: l_ship_header_id
13334: FROM
13335: rcv_transactions rt,
13336: inl_ship_lines_all sl --Bug#10381495
13337: WHERE
13338: rt.transaction_id = p_to_parent_table_id
13339: AND sl.ship_line_id = rt.lcm_shipment_line_id
13341: --Bug#9660043
13342: /*--Bug#9660043
13343: SELECT lcm_shipment_line_id
13344: INTO l_ship_line_id
13345: FROM rcv_transactions rt
13346: WHERE rt.transaction_id = p_to_parent_table_id;
13347:
13348: SELECT sl.ship_header_id
13349: INTO l_ship_header_id
14601: AND EXISTS
14602: (
14603: SELECT 1
14604: FROM
14605: rcv_transactions rt,
14606: org_organization_definitions ood
14607: WHERE
14608: rt.transaction_id = m.to_parent_table_id
14609: AND ood.organization_id = rt.organization_id
14639: SET mi.request_id = l_fnd_conc_request_id_int
14640: WHERE (nvl(mi.request_id,1) > 0
14641: OR mi.request_id = -1)
14642: AND mi.processing_status_code = 'PENDING'
14643: AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
14644: AND EXISTS (
14645: SELECT 1
14646: FROM inl_matches_int mi2
14647: WHERE mi2.processing_status_code = 'PENDING'
14646: FROM inl_matches_int mi2
14647: WHERE mi2.processing_status_code = 'PENDING'
14648: AND mi2.request_id = l_fnd_conc_request_id_int
14649: AND mi2.match_int_id <> mi.match_int_id
14650: AND mi2.to_parent_table_name = 'RCV_TRANSACTIONS'
14651: AND mi2.to_parent_table_id = mi.to_parent_table_id
14652: );
14653: l_return_value := l_return_value + NVL(SQL%ROWCOUNT,0);
14654:
14686: AND NOT EXISTS
14687: (
14688: SELECT 1
14689: FROM
14690: rcv_transactions rt,
14691: org_organization_definitions ood
14692: WHERE
14693: rt.transaction_id = mi.to_parent_table_id
14694: AND ood.organization_id = rt.organization_id
17593:
17594: CURSOR c_matches_int_error IS
17595: SELECT DISTINCT m.group_id
17596: FROM inl_matches_int m,
17597: rcv_transactions rt,
17598: inl_ship_lines_all sl
17599: WHERE sl.ship_line_id = rt.lcm_shipment_line_id
17600: AND rt.transaction_id = m.to_parent_table_id
17601: AND m.to_parent_table_name = 'RCV_TRANSACTIONS'
17597: rcv_transactions rt,
17598: inl_ship_lines_all sl
17599: WHERE sl.ship_line_id = rt.lcm_shipment_line_id
17600: AND rt.transaction_id = m.to_parent_table_id
17601: AND m.to_parent_table_name = 'RCV_TRANSACTIONS'
17602: AND m.processing_status_code = 'ERROR'
17603: AND sl.ship_header_id = p_ship_header_id;
17604:
17605: TYPE matches_int_error IS TABLE OF c_matches_int_error%ROWTYPE;
17637: p_var_value => l_matches_int_error(i).group_id);
17638: SELECT COUNT(1)
17639: INTO l_count_elc_pend_shipment
17640: FROM inl_matches_int m,
17641: rcv_transactions rt,
17642: inl_ship_lines_all sl,
17643: inl_ship_headers_all sh
17644: WHERE sh.ship_header_id = sl.ship_header_id
17645: AND sl.ship_line_id = rt.lcm_shipment_line_id
17644: WHERE sh.ship_header_id = sl.ship_header_id
17645: AND sl.ship_line_id = rt.lcm_shipment_line_id
17646: AND rt.transaction_id = m.to_parent_table_id
17647: AND m.group_id = l_matches_int_error(i).group_id
17648: AND m.to_parent_table_name = 'RCV_TRANSACTIONS'
17649: AND sh.pending_update_flag = 'Y';
17650:
17651: INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
17652: p_procedure_name => l_program_name,