[Home] [Help]
161: */
162:
163: /* Bug#6649580
164: * As part of bug#5443196 fix to improve the performance of 'Pay on Receipt'
165: * concurrent request, index RCV_TRANSACTIONS_N16 was changed to function based
166: * index (SOURCE_DOCUMENT_CODE, NVL(INVOICE_STATUS_CODE,'NA'),TRANSACTION_TYPE).
167: * But in the following cursor C_receipt_txns, there is no nvl on
168: * rts.invoice_status_code and this index is not effectively used and results in
169: * performance issue.
171: * in 1159 bug#4732594 fix.
172: */
173:
174: CURSOR C_receipt_txns IS
175: SELECT /*+ INDEX (rts RCV_TRANSACTIONS_N16) */
176: rts.rowid rcv_txn_rowid,
177: rts.transaction_id,
178: rts.po_header_id,
179: rts.po_release_id,
232: po_line_locations_all plls,
233: rcv_shipment_headers rsh,
234: rcv_shipment_lines rsl,
235: inl_ship_lines_all isl, -- PoR with LCM project
236: rcv_transactions rts
237: WHERE rts.shipment_header_id = rsh.shipment_header_id
238: AND rts.po_header_id = phs.po_header_id
239: --AND rts.po_release_id = prs.po_release_id(+) /*Bug 5443196*/
240: AND rts.po_line_location_id = plls.line_location_id
266: /* PoR with LCM project
267: AND nvl(plls.lcm_flag, 'N') = 'N' -- Bug 7758359: Added this condition so that LCM Receipts are not picked up.
268: */
269: UNION
270: SELECT /*+ INDEX (rts RCV_TRANSACTIONS_N16) */
271: rts.rowid rcv_txn_rowid,
272: rts.transaction_id,
273: rts.po_header_id,
274: rts.po_release_id,
327: po_line_locations_all plls,
328: rcv_shipment_headers rsh,
329: rcv_shipment_lines rsl,
330: inl_ship_lines_all isl, -- PoR with LCM project
331: rcv_transactions rts
332: WHERE rts.shipment_header_id = rsh.shipment_header_id
333: AND rts.po_header_id = phs.po_header_id
334: AND rts.po_release_id = prs.po_release_id
335: AND rts.po_line_location_id = plls.line_location_id
441: po_line_locations plls,
442: rcv_shipment_headers rsh,
443: rcv_shipment_lines rsl,
444: inl_ship_lines_all isl, -- PoR with LCM project
445: rcv_transactions rts
446: WHERE rts.shipment_header_id = rsh.shipment_header_id
447: AND rts.po_header_id = phs.po_header_id
448: AND rts.po_release_id = prs.po_release_id(+)
449: AND rts.po_line_location_id = plls.line_location_id
509: X_curr_invoice_num ap_invoices.invoice_num%TYPE;
510:
511: X_curr_vendor_id NUMBER := NULL;
512: X_curr_pay_site_id NUMBER := NULL;
513: X_curr_currency_code rcv_transactions.currency_code%TYPE := NULL;
514: X_curr_payment_terms_id NUMBER := NULL;
515: X_curr_transaction_date DATE := NULL;
516: X_curr_le_transaction_date DATE := NULL; --LE time zone date (Bug: 5205516)
517: X_curr_packing_slip rcv_shipment_headers.receipt_num%TYPE := NULL;
517: X_curr_packing_slip rcv_shipment_headers.receipt_num%TYPE := NULL;
518: X_curr_shipment_header_id NUMBER := NULL;
519: X_curr_osa_flag VARCHAR2(1) := NULL; --Shikyu project
520:
521: X_curr_conversion_rate_type rcv_transactions.currency_conversion_type%TYPE;
522: X_curr_conversion_rate_date DATE;
523: X_curr_conversion_rate NUMBER;
524:
525: /** Bug# 1176326 **/
1209:
1210:
1211: SELECT MIN(NVL(transaction_type, X_receipt_event))
1212: INTO X_inv_event
1213: FROM rcv_transactions
1214: WHERE shipment_line_id = X_rcv_txns.shipment_line_id
1215: AND po_distribution_id = NVL(X_rcv_txns.po_distribution_id,-1)
1216: AND parent_transaction_id = X_rcv_txns.transaction_id
1217: AND transaction_type = 'DELIVER';
1312:
1313: -- update invoice_status_code of 'RECEIVE', 'CORRECT' and
1314: -- 'RETURN TO VENDOR' transactions
1315:
1316: UPDATE rcv_transactions
1317: SET invoice_status_code = DECODE(X_curr_inv_process_flag,'Y','INVOICED','REJECTED'), -- bug 3640106
1318: last_updated_by = FND_GLOBAL.user_id,
1319: last_update_date = sysdate,
1320: last_update_login = FND_GLOBAL.login_id
1321: WHERE transaction_id IN (
1322: SELECT
1323: transaction_id
1324: FROM
1325: rcv_transactions
1326: WHERE
1327: invoice_status_code <> 'INVOICED' AND
1328: transaction_type IN ('RECEIVE','CORRECT','RETURN TO VENDOR')
1329: START WITH transaction_id = X_rcv_txns.transaction_id
1908: transaction_type,
1909: parent_transaction_id,
1910: unit_of_measure -- Added for bug 6822594 : To get the transaction uom
1911: from
1912: rcv_transactions
1913: where
1914: invoice_status_code <> 'INVOICED'
1915: start with transaction_id = c_transaction_id
1916: connect by parent_transaction_id = prior transaction_id;
1952: transaction_type
1953: into
1954: v_parent_type
1955: from
1956: rcv_transactions
1957: where
1958: transaction_id = v_parent_id;
1959:
1960: IF v_parent_type = 'RECEIVE' THEN
2049: amount,
2050: transaction_type,
2051: parent_transaction_id
2052: from
2053: rcv_transactions
2054: where
2055: invoice_status_code <> 'INVOICED'
2056: start with transaction_id = c_transaction_id
2057: connect by parent_transaction_id = prior transaction_id;
2078: transaction_type
2079: into
2080: v_parent_type
2081: from
2082: rcv_transactions
2083: where
2084: transaction_id = v_parent_id;
2085:
2086: IF v_parent_type = 'RECEIVE' THEN