[Home] [Help]
5939: SELECT count(*)
5940: INTO l_count_lines_matched
5941: FROM ap_invoice_lines ail,
5942: ap_invoices ai,
5943: po_line_locations pll
5944: WHERE ai.invoice_num = p_invoice_rec.prepay_num
5945: AND ai.org_id = p_invoice_rec.org_id
5946: AND ail.invoice_id = ai.invoice_id
5947: AND ail.po_line_location_id = pll.line_location_id
8359: l_correction_dist_amount NUMBER;
8360: l_shipment_finally_closed VARCHAR2(1);
8361: l_corrupt_po_distributions NUMBER;
8362: l_calc_line_amount NUMBER;
8363: l_accrue_on_receipt_flag po_line_locations.accrue_on_receipt_flag%TYPE;
8364: l_temp_match_option VARCHAR2(25); --Bug5225547
8365:
8366: BEGIN
8367: -- Update the calling sequence
8842: END IF;
8843: --
8844: SELECT 'Y'
8845: INTO l_po_rel_is_consistent_flag
8846: FROM po_line_locations
8847: WHERE po_line_id = l_po_line_id
8848: AND po_release_id = l_po_release_id
8849: /*Bug 2787396 we need to validate the shipment level for matching */
8850: AND nvl(approved_flag, 'N' ) = 'Y'
8886: END IF;
8887: --
8888: SELECT DISTINCT po_line_id
8889: INTO l_po_line_id
8890: FROM po_line_locations
8891: WHERE po_release_id = l_po_release_id
8892: /* For bug 4038403
8893: we should check at line location level approved flag
8894: as we can do invoicing for the line/shipment for which
8978:
8979: IF l_po_header_id IS NOT NULL THEN -- Fix for 2809177
8980: SELECT 'Y'
8981: INTO l_po_rel_is_consistent_flag
8982: FROM po_line_locations
8983: WHERE po_line_id = (
8984: select po_line_id
8985: from po_lines pol, po_headers poh
8986: where poh.po_header_id = pol.po_header_id
9007: AND rownum <=1;
9008: ELSIF p_invoice_lines_rec.po_number IS NOT NULL THEN
9009: SELECT 'Y'
9010: INTO l_po_rel_is_consistent_flag
9011: FROM po_line_locations
9012: WHERE po_line_id = (
9013: select po_line_id
9014: from po_lines pol, po_headers poh
9015: where poh.po_header_id = pol.po_header_id
9632: END IF;
9633: --
9634: SELECT 'Y'
9635: INTO l_po_shipment_is_valid_flag
9636: FROM po_line_locations
9637: WHERE line_location_id = l_po_line_location_id
9638: /* For bug 4038403
9639: Need to check the validation for
9640: line location approved_flag */
9687: --
9688:
9689: SELECT 'Y', line_location_id
9690: INTO l_po_shipment_is_valid_flag, l_po_line_location_id
9691: FROM po_line_locations
9692: WHERE shipment_num = p_invoice_lines_rec.po_shipment_num
9693: AND po_header_id = l_po_header_id
9694: AND po_line_id = l_po_line_id;
9695: EXCEPTION
9764: --
9765:
9766: SELECT 'Y', line_location_id
9767: INTO l_po_shipment_is_valid_flag, l_po_line_location_id
9768: FROM po_line_locations pll,
9769: hr_locations hl
9770: WHERE hl.location_code = p_invoice_lines_rec.ship_to_location_code
9771: AND hl.location_id = pll.ship_to_location_id
9772: AND pll.po_header_id = l_po_header_id
9838: SELECT 'Y', line_location_id,
9839: po_line_id
9840: INTO l_po_shipment_is_valid_flag, l_po_line_location_id,
9841: l_po_line_id
9842: FROM po_line_locations
9843: WHERE shipment_num = p_invoice_lines_rec.po_shipment_num
9844: AND po_header_id = l_po_header_id
9845: AND po_release_id = l_po_release_id;
9846: EXCEPTION
9891: SELECT 'Y', line_location_id,
9892: po_line_id
9893: INTO l_po_shipment_is_valid_flag, l_po_line_location_id,
9894: l_po_line_id
9895: FROM po_line_locations pll, hr_locations hl
9896: WHERE hl.location_code = p_invoice_lines_rec.ship_to_location_code
9897: AND hl.location_id = pll.ship_to_location_id
9898: AND pll.po_header_id = l_po_header_id
9899: AND pll.po_release_id = l_po_release_id;
9941: END IF;
9942: --
9943: SELECT 'Y'
9944: INTO l_po_shipment_is_consis_flag
9945: FROM po_line_locations
9946: WHERE shipment_num = p_invoice_lines_rec.po_shipment_num
9947: AND line_location_id = l_po_line_location_id;
9948: EXCEPTION
9949: WHEN NO_DATA_FOUND THEN
9984: --
9985:
9986: SELECT 'Y'
9987: INTO l_po_shipment_is_consis_flag
9988: FROM po_line_locations pll,
9989: hr_locations hl
9990: WHERE hl.location_code = p_invoice_lines_rec.ship_to_location_code
9991: AND hl.location_id = pll.ship_to_location_id
9992: AND line_location_id = l_po_line_location_id;
10032: END IF;
10033: --
10034: SELECT 'Y'
10035: INTO l_po_shipment_is_consis_flag
10036: FROM po_line_locations pll,
10037: hr_locations hl
10038: WHERE hl.location_code = p_invoice_lines_rec.ship_to_location_code
10039: AND hl.location_id = pll.ship_to_location_id
10040: AND po_line_id = l_po_line_id
10098: --
10099:
10100: SELECT NVL(accrue_on_receipt_flag, 'N')
10101: INTO l_accrue_on_receipt_flag
10102: FROM po_line_locations
10103: WHERE ((shipment_num = p_invoice_lines_rec.po_shipment_num
10104: AND p_invoice_lines_rec.po_shipment_num IS NOT NULL
10105: AND p_invoice_lines_rec.po_line_location_id IS NULL)
10106: OR (line_location_id = p_invoice_lines_rec.po_line_location_id
10170: END IF;
10171: --
10172: SELECT 'Y'
10173: INTO l_po_shipment_is_consis_flag
10174: FROM po_line_locations pll,
10175: hr_locations hl
10176: WHERE hl.location_code = p_invoice_lines_rec.ship_to_location_code
10177: AND hl.location_id = pll.ship_to_location_id
10178: AND po_release_id = l_po_release_id
10511: ph.segment1
10512: INTO l_po_header_id,
10513: l_po_number
10514: FROM po_headers ph,
10515: po_line_locations pll
10516: WHERE pll.line_location_id = l_po_line_location_id
10517: AND pll.po_header_id = ph.po_header_id;
10518: EXCEPTION
10519: WHEN NO_DATA_FOUND THEN
10615: END IF;
10616:
10617: SELECT po_line_id
10618: INTO l_po_line_id
10619: FROM po_line_locations
10620: WHERE line_location_id = l_po_line_location_id;
10621: EXCEPTION
10622: WHEN NO_DATA_FOUND THEN
10623: NULL;
10790: +---------------------------------------------------------------------*/
10791:
10792: SELECT line_location_id
10793: INTO l_po_line_location_id
10794: FROM po_line_locations pll
10795: WHERE po_header_id = l_po_header_id
10796: AND po_line_id = l_po_line_id
10797: AND
10798: (
10859: END IF;
10860:
10861: SELECT pll.matching_basis
10862: INTO l_match_basis
10863: FROM po_line_locations pll
10864: WHERE pll.line_location_id = l_po_line_location_id;
10865:
10866: END IF;
10867:
10909:
10910: SELECT COUNT(*)
10911: INTO l_corrupt_po_distributions
10912: FROM po_distributions pod,
10913: po_line_locations pll
10914: WHERE pod.po_header_id = l_po_header_id
10915: AND pod.line_location_id = pll.line_location_id
10916: AND ((pll.matching_basis = 'QUANTITY'
10917: AND pod.quantity_ordered IS NULL)
11107: END IF;
11108:
11109: SELECT 'X'
11110: INTO l_po_is_not_blanket
11111: FROM po_line_locations pll,
11112: po_releases pr
11113: WHERE pr.po_header_id = l_po_header_id
11114: AND pr.po_release_id = l_po_release_id
11115: AND pll.po_release_id = pr.po_release_id
11529: END IF;
11530:
11531: SELECT 'Y'
11532: INTO l_shipment_finally_closed
11533: FROM ap_invoice_lines ail, po_line_locations pll
11534: WHERE ail.invoice_id = l_price_correct_inv_id
11535: AND ail.line_number = p_invoice_lines_rec.price_correct_inv_line_num
11536: AND pll.line_location_id = ail.po_line_location_id
11537: AND pll.closed_code = 'FINALLY CLOSED';
12153:
12154: BEGIN
12155: SELECT line_location_id
12156: INTO l_po_line_location_id
12157: FROM po_line_locations
12158: WHERE po_header_id = l_po_header_id
12159: AND po_line_id = l_po_line_id
12160: AND shipment_num = p_invoice_lines_rec.po_shipment_num ;
12161: EXCEPTION
12191: END IF;
12192:
12193: Select nvl(match_option,'P')
12194: Into l_temp_match_option
12195: From po_line_locations
12196: Where line_location_id = l_po_line_location_id;
12197: If (l_temp_match_option is not null) then
12198: If ( p_invoice_lines_rec.match_option is not null and p_invoice_lines_rec.match_option <> l_temp_match_option) then
12199:
12512: l_temp_match_option VARCHAR2(25);
12513: current_calling_sequence VARCHAR2(2000);
12514: debug_info VARCHAR2(500);
12515: l_line_amount NUMBER; /* Amount Based Matching */
12516: l_temp_shipment_type PO_LINE_LOCATIONS_ALL.SHIPMENT_TYPE%TYPE;
12517:
12518: BEGIN
12519:
12520: -- Update the calling sequence
12550: --Standard/Blanket/Scheduled shipments are valid for Standard/Credit invoices.
12551: SELECT 'X'
12552: INTO l_invalid_shipment_type
12553: FROM po_distributions pd,
12554: po_line_locations pll
12555: WHERE pd.line_location_id = pll.line_location_id
12556: AND pd.po_distribution_id = l_po_distribution_id
12557: AND
12558: (
12604: --Standard/Blanket/Scheduled shipments are valid for Standard/Credit invoices.
12605: BEGIN
12606: SELECT 'X'
12607: INTO l_invalid_shipment_type
12608: FROM po_line_locations pll
12609: WHERE line_location_id = l_po_line_location_id
12610: AND(
12611: (p_invoice_rec.invoice_type_lookup_code <> 'PREPAYMENT' and
12612: pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
12653: END IF;
12654:
12655: SELECT nvl(match_option,'P')
12656: INTO l_temp_match_option
12657: FROM po_line_locations
12658: WHERE line_location_id = l_po_line_location_id;
12659:
12660: IF (p_invoice_lines_rec.match_option IS NOT NULL AND
12661: p_invoice_lines_rec.match_option <> l_temp_match_option) THEN
12696:
12697: BEGIN
12698: SELECT count(*)
12699: INTO l_invalid_shipment_count
12700: FROM po_line_locations pll
12701: WHERE pll.po_line_id = l_po_line_id
12702: AND(
12703: (
12704: (p_invoice_rec.invoice_type_lookup_code <> 'PREPAYMENT' and
12815: IF l_po_line_location_id IS NOT NULL THEN
12816:
12817: Select shipment_type
12818: Into l_temp_shipment_type
12819: From po_line_locations
12820: Where line_location_id = l_po_line_location_id;
12821:
12822: END IF;
12823:
12931: --bug2878889.Commented the following code and added the code below.
12932: /* IF (l_po_line_location_id IS NOT NULL) THEN
12933: SELECT pll.price_override, pll.unit_meas_lookup_code
12934: INTO l_po_unit_price,l_unit_of_measure
12935: FROM po_line_locations pll
12936: WHERE pll.line_location_id = l_po_line_location_id;
12937: ELSE
12938: SELECT unit_price,unit_meas_lookup_code
12939: INTO l_po_unit_price,l_unit_of_measure
12946: AND (p_invoice_lines_rec.po_release_id IS NOT NULL) ) THEN
12947:
12948: SELECT NVL(price_override,unit_price),unit_meas_lookup_code
12949: INTO l_po_unit_price,l_unit_of_measure
12950: FROM po_line_locations_release_v
12951: WHERE po_line_id = l_po_line_id
12952: -- bug7328060, added the below condition
12953: AND line_location_id = nvl(l_po_line_location_id, line_location_id)
12954: AND po_release_id = p_invoice_lines_rec.po_release_id;
12959: AND (p_invoice_lines_rec.po_release_id IS NULL) ) THEN
12960:
12961: SELECT pll.price_override, pll.unit_meas_lookup_code
12962: INTO l_po_unit_price,l_unit_of_measure
12963: FROM po_line_locations pll
12964: WHERE pll.line_location_id = l_po_line_location_id;
12965:
12966: ELSIF ( (l_po_line_id IS NOT NULL)
12967: AND (p_invoice_lines_rec.quantity_invoiced IS NULL)
13490: NVL(p_invoice_lines_rec.amount, l_line_amount)
13491: INTO l_total_qty_billed,
13492: l_total_match_amount /* Amount Based Matching */
13493: FROM ap_invoice_lines L,
13494: po_line_locations PLL
13495: WHERE L.po_line_location_id = PLL.line_location_id
13496: AND PLL.po_line_id = l_po_line_id;
13497:
13498: -- If total qty billed is below zero
14023: AND nvl(accrue_on_receipt_flag,'N') <> 'Y' --Bug 2667171 added this Condition
14024: ORDER BY distribution_num;
14025:
14026: --Contract Payments: Modified the where clause
14027: CURSOR po_line_locations_cur IS
14028: SELECT pd.code_combination_id
14029: FROM po_distributions pd,
14030: po_line_locations pll
14031: WHERE pd.line_location_id = pll.line_location_id
14026: --Contract Payments: Modified the where clause
14027: CURSOR po_line_locations_cur IS
14028: SELECT pd.code_combination_id
14029: FROM po_distributions pd,
14030: po_line_locations pll
14031: WHERE pd.line_location_id = pll.line_location_id
14032: AND pll.po_line_id = l_po_line_id
14033: AND(
14034: (p_invoice_rec.invoice_type_lookup_code <> 'PREPAYMENT' and
14252: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
14253: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14254: '(v_check_po_overlay 1) Get l_code_combination_id FROM l_po_line_id ');
14255: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14256: '(v_check_po_overlay 1) Open po_line_locations ');
14257: END IF;
14258:
14259: OPEN po_line_locations_cur;
14260:
14255: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14256: '(v_check_po_overlay 1) Open po_line_locations ');
14257: END IF;
14258:
14259: OPEN po_line_locations_cur;
14260:
14261: LOOP
14262: --
14263: --
14262: --
14263: --
14264: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
14265: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14266: '(v_check_po_overlay 2) Fetch po_line_locations_cur ');
14267: END IF;
14268:
14269: FETCH po_line_locations_cur INTO l_code_combination_id;
14270: --
14265: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14266: '(v_check_po_overlay 2) Fetch po_line_locations_cur ');
14267: END IF;
14268:
14269: FETCH po_line_locations_cur INTO l_code_combination_id;
14270: --
14271: EXIT WHEN po_line_locations_cur%NOTFOUND OR
14272: po_line_locations_cur%NOTFOUND IS NULL;
14273:
14267: END IF;
14268:
14269: FETCH po_line_locations_cur INTO l_code_combination_id;
14270: --
14271: EXIT WHEN po_line_locations_cur%NOTFOUND OR
14272: po_line_locations_cur%NOTFOUND IS NULL;
14273:
14274: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
14275: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14268:
14269: FETCH po_line_locations_cur INTO l_code_combination_id;
14270: --
14271: EXIT WHEN po_line_locations_cur%NOTFOUND OR
14272: po_line_locations_cur%NOTFOUND IS NULL;
14273:
14274: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
14275: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14276: '------------------> l_code_combination_id = '||
14302: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14303: '(v_check_po_overlay 3) Overlay_Segments<-'
14304: ||current_calling_sequence);
14305: END IF;
14306: CLOSE po_line_locations_cur;
14307: Raise check_po_failure;
14308: ELSE
14309: -- show output values (only IF debug_switch = 'Y')
14310: --
14331: AP_IMPORT_UTILITIES_PKG.Print(
14332: AP_IMPORT_INVOICES_PKG.g_debug_switch,
14333: 'insert_rejections<-'||current_calling_sequence);
14334: END IF;
14335: CLOSE po_line_locations_cur;
14336: RAISE check_po_failure;
14337: --
14338: END IF; -- insert rejections
14339: l_current_invoice_status := 'N';
14342:
14343: END LOOP;
14344: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
14345: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14346: '(v_check_po_overlay 5) Close po_line_locations ');
14347: END IF;
14348: CLOSE po_line_locations_cur;
14349: END IF; -- po distribution id is not NULL
14350: ELSE -- invoice status <> 'N'
14344: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
14345: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14346: '(v_check_po_overlay 5) Close po_line_locations ');
14347: END IF;
14348: CLOSE po_line_locations_cur;
14349: END IF; -- po distribution id is not NULL
14350: ELSE -- invoice status <> 'N'
14351: IF (AP_IMPORT_INVOICES_PKG.g_debug_switch = 'Y') THEN
14352: AP_IMPORT_UTILITIES_PKG.Print(AP_IMPORT_INVOICES_PKG.g_debug_switch,
14400: l_cascade_receipts_flag VARCHAR2(1);
14401: l_price_correct_inv_id AP_INVOICES.INVOICE_ID%TYPE;
14402:
14403: --Contract Payments
14404: l_shipment_type PO_LINE_LOCATIONS_ALL.SHIPMENT_TYPE%TYPE;
14405:
14406: BEGIN
14407:
14408: -- Update the calling sequence
14682: INTO l_qty_billed_sum
14683: FROM rcv_transactions RT ,
14684: rcv_shipment_headers SH ,
14685: po_headers_all PH ,
14686: po_line_locations_all PS ,
14687: po_releases_all PR ,
14688: per_all_people_f BU
14689: WHERE RT.po_line_location_id = p_invoice_lines_rec.po_line_location_id
14690: AND SH.receipt_num = nvl(p_invoice_lines_rec.receipt_number,sh.receipt_num)
20560:
20561: ELSE
20562: SELECT pl.category_id
20563: INTO l_purchasing_category_id
20564: FROM po_lines_all pl, po_line_locations_all pll
20565: WHERE pll.line_location_id = p_invoice_lines_rec.po_line_location_id
20566: AND pl.po_line_id = pll.po_line_id;
20567:
20568: END IF;