[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),
127: WHERE pll.line_location_id = p_line_loc_changes_rec.po_line_location_id
128: OR (pll.shipment_type = 'PLANNED'
129: AND pll.line_location_id =
130: (SELECT pll2.source_shipment_id
131: FROM po_line_locations pll2
132: WHERE pll2.shipment_type = 'SCHEDULED'
133: AND pll2.line_location_id = p_line_loc_changes_rec.po_line_location_id)
134: )
135: ;
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:
483: SELECT pll.amount,
484: pll.amount_cancelled
485: INTO x_ship_amt_ordered,
486: x_ship_amt_cancelled
487: FROM po_line_locations pll,
488: rcv_transactions rt
489: WHERE rt.po_line_location_id = pll.line_location_id
490: AND rt.transaction_id = p_receive_transaction_id;
491:
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.
731: --IN:
732: -- p_api_version
733: -- Specifies the version of the api. Value that needs to be passed in is 1.0
734: -- p_entity_type
735: -- Possible values: PO_HEADERS, PO_LINES, PO_LINE_LOCATIONS, PO_DISTRIBUTIONS
736: -- PO_LINE_LOCATIONS only supported currently.
737: -- p_entity_id_tbl
738: -- This will have one or more ids that you want to set the final match flag value.
739: -- p_final_match_flag
732: -- p_api_version
733: -- Specifies the version of the api. Value that needs to be passed in is 1.0
734: -- p_entity_type
735: -- Possible values: PO_HEADERS, PO_LINES, PO_LINE_LOCATIONS, PO_DISTRIBUTIONS
736: -- PO_LINE_LOCATIONS only supported currently.
737: -- p_entity_id_tbl
738: -- This will have one or more ids that you want to set the final match flag value.
739: -- p_final_match_flag
740: -- Possible Values: Y or N
759:
760: PROCEDURE set_final_match_flag (p_api_version IN NUMBER ,
761: p_entity_type IN VARCHAR2 ,
762: p_entity_id_tbl IN PO_TBL_NUMBER ,
763: p_final_match_flag IN PO_LINE_LOCATIONS.FINAL_MATCH_FLAG%TYPE ,
764: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
765: p_commit IN VARCHAR2 := FND_API.G_FALSE ,
766: x_ret_status OUT NOCOPY VARCHAR2 ,
767: x_msg_count OUT NOCOPY NUMBER ,
790: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
791:
792: END IF;
793:
794: IF p_entity_type <> 'PO_LINE_LOCATIONS' THEN
795:
796: RAISE FND_API.G_EXC_ERROR;
797:
798: ELSE
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;