[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT po_header_id,
po_line_id,
po_line_location_id,
po_release_id
FROM cpg_oragems_mapping
WHERE po_id = v_po_id
AND line_id = v_line_id;
| PROCEDURE NAME update_header_status |
| |
| DESCRIPTION Procedure to update header status. |
| |
| MODIFICATION HISTORY |
| |
| 10/22/97 Kenny Jiang created |
| |
=========================================================================*/
PROCEDURE update_header_status
( v_po_header_id IN NUMBER,
v_org_id IN NUMBER,
v_last_updated_by IN NUMBER,
v_last_update_date IN DATE )
IS
CURSOR line_cur IS
SELECT closed_code
FROM po_lines_all
WHERE po_header_id = v_po_header_id
AND org_id = v_org_id;
SELECT closed_code
FROM po_headers_all
WHERE po_header_id = v_po_header_id
AND org_id = v_org_id;
UPDATE po_headers_all
SET closed_code = v_new_status,
last_update_date= v_last_update_date,
last_updated_by = v_last_updated_by
WHERE po_header_id = v_po_header_id AND
org_id = v_org_id;
END update_header_status;
| PROCEDURE NAME update_line_status |
| |
| DESCRIPTION Procedure to update_line_status. |
| |
| MODIFICATION HISTORY |
| |
| 10/22/97 Kenny Jiang created |
| |
=========================================================================*/
PROCEDURE update_line_status
( v_po_header_id IN NUMBER,
v_po_line_id IN NUMBER,
v_org_id IN NUMBER,
v_last_updated_by IN NUMBER,
v_last_update_date IN DATE )
IS
CURSOR line_location_cur IS
SELECT closed_code
FROM po_line_locations_all
WHERE po_header_id = v_po_header_id
AND po_line_id = v_po_line_id
AND org_id = v_org_id;
SELECT closed_code
FROM po_lines_all
WHERE po_header_id = v_po_header_id
AND po_line_id = v_po_line_id
AND org_id = v_org_id;
UPDATE po_lines_all
SET closed_code = v_new_status,
last_update_date= v_last_update_date,
last_updated_by = v_last_updated_by
WHERE po_header_id = v_po_header_id AND
po_line_id = v_po_line_id AND
org_id = v_org_id;
update_header_status(v_po_header_id,
v_org_id,
v_last_updated_by,
v_last_update_date );
END update_line_status;
PROCEDURE NAME update_release_status
DESCRIPTION Procedure to update the status in po_releases_all
MODIFICATION HISTORY
11/12/97 Kenny Jiang created
========================================================================*/
PROCEDURE update_release_status
( v_po_header_id IN NUMBER,
v_po_release_id IN NUMBER,
v_org_id IN NUMBER,
v_last_updated_by IN NUMBER,
v_last_update_date IN DATE )
IS
CURSOR line_location_cur IS
SELECT closed_code
FROM po_line_locations_all
WHERE po_header_id = v_po_header_id
AND po_release_id = v_po_release_id
AND org_id = v_org_id;
SELECT closed_code
FROM po_releases_all
WHERE po_header_id = v_po_header_id
AND po_release_id = v_po_release_id
AND org_id = v_org_id;
UPDATE po_releases_all
SET closed_code = v_new_status,
last_update_date= v_last_update_date,
last_updated_by = v_last_updated_by
WHERE po_header_id = v_po_header_id
AND po_release_id = v_po_release_id
AND org_id = v_org_id;
END update_release_status;
| PROCEDURE NAME update_line_locations. |
| |
| DESCRIPTION Procedure to update line locations. |
| |
| MODIFICATION HISTORY |
| |
| 10/22/97 Kenny Jiang created |
| 23-NOV-99 NC - modified all references to cpg_receiving_interface table
| which nolonger exists. |
| 16-DOC-99 NC - Added FND_GLOBAL.APPS_INITIALIZE . |
| 03-MAR-00 HW - BUG#:1222247 - changed status code |
=========================================================================*/
PROCEDURE update_line_locations
( v_po_header_id IN cpg_oragems_mapping.po_header_id%TYPE,
v_po_line_id IN cpg_oragems_mapping.po_line_id%TYPE,
v_line_location_id IN cpg_oragems_mapping.po_line_location_id%TYPE,
v_po_release_id IN cpg_oragems_mapping.po_release_id%TYPE,
v_org_id IN gl_plcy_mst.org_id%TYPE,
v_po_status IN po_ordr_dtl.po_status%TYPE,
v_received_qty IN po_recv_dtl.recv_qty1%TYPE,
v_returned_qty IN po_rtrn_dtl.return_qty1%TYPE,
v_created_by IN po_recv_dtl.created_by%TYPE,
v_timestamp IN cpg_oragems_mapping.time_stamp%TYPE)
IS
v_closed_code po_line_locations_all.closed_code%TYPE;
v_last_updated_by po_line_locations_all.last_updated_by%TYPE;
SELECT cancel_flag
FROM po_line_locations_all
WHERE line_location_id = v_line_location_id;
SELECT closed_code
FROM po_line_locations_all
WHERE line_location_id = v_line_location_id;
SELECT segment1
FROM po_headers_all
WHERE po_header_id = v_po_header_id;
SELECT line_num
FROM po_lines_all
WHERE po_line_id = v_po_line_id;
SELECT shipment_num
FROM po_line_locations_all
WHERE line_location_id = v_line_location_id;
SELECT user_id
FROM fnd_user
WHERE user_name = v_created_by;
SELECT responsibility_id
FROM fnd_user_resp_groups
WHERE user_id = v_user_id
AND responsibility_application_id
= v_resp_appl_id;
/* FETCH user_id_cur INTO v_last_updated_by;*/
v_last_updated_by := v_created_by;
/* When closed_code is updated in po_line_locations_all, a trigger on
that table tries to fire a concurrent program. For some reason
the FND_GLOBAL user_id and resp_id had wrong values becoz of which
the concurrenct request was not getting fired( returning "CONC-Unable to
get oracle name") error. Hence added the following APPS_INITIALIZE call.
-- NC 12/16/99 */
v_user_id := v_created_by;
v_closed_by := v_last_updated_by;
UPDATE po_line_locations_all
SET last_update_date = v_timestamp,
last_updated_by = v_last_updated_by,
quantity_received = v_received_qty,
quantity_rejected = v_returned_qty
WHERE po_header_id = v_po_header_id
AND po_line_id = v_po_line_id
AND line_location_id = v_line_location_id
/* Added the OR org_id is null to allow for mult org*/
AND (org_id = v_org_id OR org_id is null);
/* IF it is a Blanket or a Planned PO, Update Recd qty for the Parent Line*/
SELECT source_shipment_id
INTO v_source_shipment_id
FROM po_line_locations_all
WHERE line_location_id = v_line_location_id;
UPDATE po_line_locations_all
SET last_update_date = v_timestamp,
last_updated_by = v_last_updated_by,
quantity_received = (select sum(quantity_received)
from po_line_locations_all
where source_shipment_id = v_source_shipment_id),
quantity_rejected = (select sum(quantity_rejected)
from po_line_locations_all
where source_shipment_id = v_source_shipment_id)
WHERE po_header_id = v_po_header_id
AND po_line_id = v_po_line_id
AND line_location_id = (select source_shipment_id
from po_line_locations_all
where line_location_id = v_line_location_id)
AND org_id = v_org_id;
UPDATE po_line_locations_all
SET closed_code = v_closed_code
/* closed_reason = v_closed_reason, */
/* closed_date = v_closed_date, */
/* closed_by = v_closed_by */
WHERE po_header_id = v_po_header_id
AND po_line_id = v_po_line_id
AND line_location_id = v_line_location_id
AND org_id = v_org_id;
/* Update the PO Line for all types of PO's*/
/* BUG#:1222247 */
/* Do not call the following procedure thus the bug will prevent unnecessary */
/* updates of fields in po_lines and po_headers */
/*
update_line_status(v_po_header_id,
v_po_line_id,
v_org_id,
v_last_updated_by,
v_timestamp);
/* commented the call to update_release_status */
/* IF v_po_release_id IS NOT NULL THEN -- it's a Planned/Blanket PO shipment */
/* update_release_status(v_po_header_id, */
/* v_po_release_id, */
/* v_org_id, */
/* v_last_updated_by, */
/* v_timestamp); */
END update_line_locations;