[Home] [Help]
1031: CURSOR l_rma IS
1032: SELECT
1033: count(transaction_id)
1034: FROM
1035: rcv_transactions rt
1036: , oe_order_lines_all oola
1037: WHERE rt.oe_order_line_id = oola.line_id
1038: AND oola.reference_line_id = to_char(p_movement_transaction.order_line_id)
1039: AND rt.mvt_stat_status = 'PROCESSED'
1049: RA_CUSTOMER_TRX_ALL rat
1050: , RA_CUSTOMER_TRX_LINES_ALL ratl
1051: , RA_CUST_TRX_TYPES_ALL ratt
1052: , OE_ORDER_LINES_ALL oola
1053: , RCV_TRANSACTIONS rt
1054: WHERE rat.customer_trx_id = ratl.customer_trx_id
1055: AND rat.cust_trx_type_id = ratt.cust_trx_type_id
1056: AND rat.org_id = ratt.org_id
1057: AND ratt.type IN ('CM','DM')
1079: CURSOR l_rtv IS
1080: SELECT
1081: COUNT(transaction_id)
1082: FROM
1083: rcv_transactions
1084: WHERE transaction_type = 'RETURN TO VENDOR'
1085: AND parent_transaction_id = p_movement_transaction.rcv_transaction_id
1086: AND mvt_stat_status = 'PROCESSED';
1087:
1090: CURSOR l_total_rtv_quantity IS
1091: SELECT
1092: SUM(quantity)
1093: FROM
1094: rcv_transactions
1095: WHERE po_header_id = p_movement_transaction.po_header_id
1096: AND transaction_type = 'RETURN TO VENDOR';
1097:
1098: --Cursor to get processed rtv transaction quantity exclude
1101: CURSOR l_netted_rtv_quantity IS
1102: SELECT
1103: SUM(quantity)
1104: FROM
1105: rcv_transactions rt
1106: WHERE po_header_id = p_movement_transaction.po_header_id
1107: AND transaction_type = 'RETURN TO VENDOR'
1108: AND mvt_stat_status = 'PROCESSED'
1109: AND transaction_id NOT IN (SELECT rcv_transaction_id
2446: AP_INVOICES_ALL aia
2447: , AP_INVOICE_LINES_ALL aila
2448: , RA_CUSTOMER_TRX_LINES_ALL rctla
2449: , ra_customer_trx_all rcta
2450: , rcv_transactions rcv
2451: , rcv_shipment_lines rsl
2452: , mtl_material_transactions mmt
2453: WHERE aia.invoice_id = aila.invoice_id
2454: AND aia.cancelled_date IS NULL
2475: AND rsl.shipment_line_id = rcv.shipment_line_id
2476: AND rcv.transaction_id = l_movement_transaction.rcv_transaction_id;
2477:
2478: --Bugfix 12949295: AP Invoice cursor for SO Arrival transactions.
2479: --SO arrival details are not present in rcv_transactions. So l_ap_intercompany_io_arrival
2480: --cannot be used for SO arrivals.
2481: CURSOR l_ap_intercompany_so_arrival IS
2482: SELECT
2483: aia.invoice_id
2616: ,NVL(rctla.quantity_invoiced,0) InvoicedQuantity
2617: FROM
2618: AP_INVOICES_ALL aia
2619: , RA_CUSTOMER_TRX_LINES_ALL rctla
2620: , rcv_transactions rcv
2621: , rcv_shipment_lines rsl
2622: , mtl_material_transactions mmt
2623: WHERE aia.invoice_id = x_movement_transaction.invoice_id
2624: AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2691:
2692: --No changes are needed in this cursor. Consider the following scenario:
2693: --RMA is created for 30 quantities. Whille receiving, the receipt is created for
2694: --15,10 and 5 quantities. This would split the original RMA line into 3 with
2695: --quantities of 15,10 and 5. 3 RCV transactions would get created which would create
2696: --3 movement records with proper RMA line_id stamped. The SUM function would still
2697: --return the correct data.
2698: /* 7165989 - Intercompany AP invoice for RMA */
2699: CURSOR l_ap_intercompany_rma_inv_qty IS
3080: CURSOR l_total_rtv_quantity IS
3081: SELECT
3082: SUM(quantity)
3083: FROM
3084: rcv_transactions
3085: WHERE po_header_id = l_movement_transaction.po_header_id
3086: AND transaction_type = 'RETURN TO VENDOR';
3087:
3088: BEGIN
4282: IF l_movement_transaction.rcv_transaction_id IS NOT NULL
4283: THEN
4284: SELECT parent_transaction_id
4285: INTO l_parent_transaction_id
4286: FROM rcv_transactions
4287: WHERE transaction_id = l_movement_transaction.rcv_transaction_id;
4288: END IF;
4289:
4290: EXCEPTION
4760: WHERE MMT.transaction_id = x_movement_transaction.mtl_transaction_id;
4761: ELSE
4762: SELECT transaction_date
4763: INTO l_transaction_date
4764: FROM rcv_transactions
4765: WHERE transaction_id = x_movement_transaction.rcv_transaction_id;
4766: END IF;
4767: END IF;
4768: