[Home] [Help]
86: -- SQL What: Update the AP-related fields on the PO line location
87: -- SQL Where: For the line location row represented in p_line_loc_changes_rec
88: -- Also, for Scheduled Releases, update the information on the
89: -- backing Planned PO line location row as well
90: UPDATE po_line_locations_all pll
91: SET quantity_billed =
92: DECODE(p_line_loc_changes_rec.quantity_billed
93: , NULL, quantity_billed
94: , nvl(quantity_billed, 0) + p_line_loc_changes_rec.quantity_billed),
313: p_line_loc_match_amt_tbl(i),
314: pl.retainage_rate,
315: pl.max_retainage_amount
316: FROM po_lines_all pl,
317: po_line_locations_all pll
318: where pl.po_line_id = pll.po_line_id
319: and pll.line_location_id = p_line_location_id_tbl(i);
320:
321: -- SumAmtInvoiced_Line_Session (SIALS)
327:
328: -- Sum of Retained Amount at Line(SRAL)
329: UPDATE PO_SESSION_GT GTO
330: SET GTO.NUM5 = (SELECT SUM(Nvl(pll.retainage_withheld_amount,0))
331: FROM po_line_locations_all PLL
332: WHERE PLL.po_line_id = GTO.num2);
333:
334: -- Calculated Retainable Amount for the Current Session (CRAS)
335: UPDATE PO_SESSION_GT GTO
455: -------------------------------------------------------------------------------------------------
456:
457: PROCEDURE get_po_ship_amounts (p_api_version IN NUMBER,
458: p_receive_transaction_id IN RCV_TRANSACTIONS.transaction_id%TYPE,
459: x_ship_amt_ordered OUT NOCOPY PO_LINE_LOCATIONS_ALL.amount%TYPE,
460: x_ship_amt_cancelled OUT NOCOPY PO_LINE_LOCATIONS_ALL.amount_cancelled%TYPE,
461: x_ret_status OUT NOCOPY VARCHAR2,
462: x_msg_count OUT NOCOPY NUMBER,
463: x_msg_data OUT NOCOPY VARCHAR2) IS
456:
457: PROCEDURE get_po_ship_amounts (p_api_version IN NUMBER,
458: p_receive_transaction_id IN RCV_TRANSACTIONS.transaction_id%TYPE,
459: x_ship_amt_ordered OUT NOCOPY PO_LINE_LOCATIONS_ALL.amount%TYPE,
460: x_ship_amt_cancelled OUT NOCOPY PO_LINE_LOCATIONS_ALL.amount_cancelled%TYPE,
461: x_ret_status OUT NOCOPY VARCHAR2,
462: x_msg_count OUT NOCOPY NUMBER,
463: x_msg_data OUT NOCOPY VARCHAR2) IS
464:
578: --Name: update_po_ship_amounts
579: --
580: --Pre-reqs: None
581: --
582: --Modifies: PO_LINE_LOCATIONS_ALL
583: --
584: --Locks: None
585: --
586: --Function: This procedure updates the amount billed on po shipments during amount matching process
609: --End of Comments
610: ------------------------------------------------------------------------------------------------
611:
612: PROCEDURE update_po_ship_amounts (p_api_version IN NUMBER,
613: p_po_line_location_id IN PO_LINE_LOCATIONS_ALL.line_location_id%TYPE,
614: p_ship_amt_billed IN PO_LINE_LOCATIONS_ALL.amount_billed%TYPE,
615: x_ret_status OUT NOCOPY VARCHAR2,
616: x_msg_count OUT NOCOPY NUMBER,
617: x_msg_data OUT NOCOPY VARCHAR2) IS
610: ------------------------------------------------------------------------------------------------
611:
612: PROCEDURE update_po_ship_amounts (p_api_version IN NUMBER,
613: p_po_line_location_id IN PO_LINE_LOCATIONS_ALL.line_location_id%TYPE,
614: p_ship_amt_billed IN PO_LINE_LOCATIONS_ALL.amount_billed%TYPE,
615: x_ret_status OUT NOCOPY VARCHAR2,
616: x_msg_count OUT NOCOPY NUMBER,
617: x_msg_data OUT NOCOPY VARCHAR2) IS
618:
632:
633: -- SQL What : Updates the amount billed on the po shipments
634: -- SQL Why : This is the amount that was billed against this shipment
635:
636: UPDATE po_line_locations_all
637: SET amount_billed = nvl(amount_billed,0) + nvl(p_ship_amt_billed,0)
638: WHERE line_location_id = p_po_line_location_id;
639:
640:
719: --Name: set_final_match_flag
720: --
721: --Pre-reqs: None
722: --
723: --Modifies: PO_LINE_LOCATIONS_ALL
724: --
725: --Locks: None
726: --
727: --Function: This procedure updates the final_match_flag on po shipments when an invoice is finally matched.
800: -- SQL Why : This is to indicate the shipment has been finally matched by an invoice.
801:
802: FORALL i IN 1 .. p_entity_id_tbl.COUNT
803:
804: UPDATE po_line_locations_all
805: SET final_match_flag = p_final_match_flag
806: WHERE line_location_id = p_entity_id_tbl(i);
807:
808: END IF;