[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Insert */
IF (p_mode = 'I') THEN
SELECT po_headers_s.NEXTVAL
INTO l_po_headers_rec.po_header_id
FROM dual;
/* Update */
l_po_headers_rec := p_po_headers_rec;
SELECT employee_id
INTO l_employee_id
FROM fnd_user
WHERE user_id = p_cc_headers_rec.cc_preparer_user_id;
l_po_headers_rec.last_update_login := p_cc_headers_rec.last_update_login;
l_po_headers_rec.last_update_date := p_cc_headers_rec.last_update_date;
l_po_headers_rec.last_updated_by := p_cc_headers_rec.last_updated_by;
l_po_headers_rec.program_update_date := NULL;
l_po_headers_rec.price_update_tolerance := NULL;
/* Insert */
IF (p_mode = 'I') THEN
SELECT po_lines_s.NEXTVAL
INTO l_po_lines_rec.po_line_id
FROM dual;
/* Update */
l_po_lines_rec := p_po_lines_rec;
l_po_lines_rec.last_update_date := p_cc_acct_lines_rec.last_update_date;
l_po_lines_rec.last_updated_by := p_cc_acct_lines_rec.last_updated_by;
SELECT line_type_id
INTO l_line_type_id
FROM po_line_types_tl
WHERE line_type = 'IGC CONTRACT COMMITMENT'
AND LANGUAGE = DECODE(l_icx_lang,'AMERICAN','US',
'DUTCH','NL','US');
SELECT order_type_lookup_code,
purchase_basis,
matching_basis
INTO l_order_type_lookup_code,
l_purchase_basis,
l_matching_basis
FROM po_line_types_b
WHERE line_type_id = l_line_type_id;
l_po_lines_rec.last_update_login := p_cc_acct_lines_rec.last_update_login;
SELECT apt.name,ccal.tax_id
INTO l_po_lines_rec.tax_name, l_po_lines_rec.tax_code_id
FROM igc_cc_acct_lines ccal,
ap_tax_codes apt
WHERE ccal.cc_acct_line_id = p_cc_acct_lines_rec.cc_acct_line_id
AND apt.tax_id = ccal.tax_id;
SELECT SUM(cc_det_pf_entered_amt)
INTO l_po_lines_rec.quantity
FROM igc_cc_det_pf
WHERE cc_acct_line_id = p_cc_acct_lines_rec.cc_acct_line_id AND
/* Commented this part of code to fix bug 1576123 (cc_det_pf_date >= p_yr_start_date AND */
( cc_det_pf_date <= p_yr_end_date) ;
l_po_lines_rec.program_update_date := NULL;
/* Insert */
IF (p_mode = 'I') THEN
SELECT po_line_locations_s.nextval
INTO l_po_line_locs_rec.line_location_id
FROM DUAL;
l_po_line_locs_rec.last_update_date := p_po_lines_rec.last_update_date;
l_po_line_locs_rec.last_updated_by := p_po_lines_rec.last_updated_by;
l_po_line_locs_rec.last_update_login := p_po_lines_rec.last_update_login;
l_po_line_locs_rec.program_update_date := NULL;
SELECT po_distributions_s.nextval
INTO l_po_dist_rec.po_distribution_id
FROM DUAL;
SELECT period_name
INTO l_po_dist_rec.gl_encumbered_period_name
FROM gl_periods gp, gl_sets_of_books gb
WHERE gb.set_of_books_id = p_cc_headers_rec.set_of_books_id AND
gp.period_set_name = gb.period_set_name AND
/* Begin Fix for bug 1569257 */
gp.adjustment_period_flag = 'N' AND
/* End Fix for bug 1569257 */
gb.accounted_period_type = gp.period_type AND
( (gp.start_date <= TRUNC(l_po_dist_rec.gl_encumbered_date) ) AND
(gp.end_date >= TRUNC(l_po_dist_rec.gl_encumbered_date) ) );
l_po_dist_rec.last_update_login := p_cc_pmt_fcst_rec.last_update_login;
l_po_dist_rec.last_update_date := p_cc_pmt_fcst_rec.last_update_date;
l_po_dist_rec.last_updated_by := p_cc_pmt_fcst_rec.last_updated_by;
l_po_dist_rec.program_update_date := NULL;
| PROCEDURE Update_PO_Approved_Flag |
+=======================================================================*/
PROCEDURE Update_PO_Approved_Flag
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cc_header_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_PO_Approved_Flag';
SELECT MIN(start_date) start_date, MAX(end_date) end_date
FROM GL_PERIODS GP,
GL_SETS_OF_BOOKS GB
WHERE
GP.period_set_name = GB.period_set_name AND
GP.period_type = GB.accounted_period_type AND
GB.set_of_books_id = p_sob_id AND
TO_CHAR(start_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
TO_CHAR(end_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
GP.adjustment_period_flag = 'N';
SELECT *
FROM igc_cc_acct_lines
WHERE cc_header_id = t_cc_header_id;
SAVEPOINT Update_PO_Approved_Flag;
SELECT * INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
SELECT count(cc_det_pf_line_id)
INTO l_curr_year_pf_lines
FROM igc_cc_det_pf a, igc_cc_acct_lines b, igc_cc_headers c
WHERE
NVL(a.cc_det_pf_date,l_end_date + 1) <= l_end_date AND
a.cc_acct_line_id = b.cc_acct_line_id AND
b.cc_header_id = c.cc_header_id AND
c.cc_header_id = p_cc_header_id;
SELECT * INTO l_po_headers_rec
FROM PO_HEADERS_ALL
WHERE segment1 = l_cc_headers_rec.cc_num AND
type_lookup_code = 'STANDARD' AND
org_id = l_cc_headers_rec.org_id;
IGC_CC_PO_HEADERS_ALL_PVT.Update_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_headers_rec);
SELECT * INTO l_po_lines_rec
FROM po_lines_all pol
WHERE
pol.po_header_id = l_po_headers_rec.po_header_id AND
pol.line_num = l_cc_acct_lines_rec.cc_acct_line_num;
SELECT * INTO l_po_line_locs_rec
FROM po_line_locations_all pll
WHERE
pll.po_header_id = l_po_headers_rec.po_header_id AND
pll.po_line_id = l_po_lines_rec.po_line_id;
IGC_CC_PO_LINE_LOCS_ALL_PVT.Update_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_line_locs_rec);
ROLLBACK TO Update_PO_Approved_Flag;
ROLLBACK TO Update_PO_Approved_Flag;
ROLLBACK TO Update_PO_Approved_Flag;
ROLLBACK TO Update_PO_Approved_Flag;
END Update_PO_Approved_Flag;
SELECT MIN(start_date) start_date, MAX(end_date) end_date
FROM GL_PERIODS GP,
GL_SETS_OF_BOOKS GB
WHERE
GP.period_set_name = GB.period_set_name AND
GP.period_type = GB.accounted_period_type AND
GB.set_of_books_id = p_sob_id AND
TO_CHAR(start_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
TO_CHAR(end_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
GP.adjustment_period_flag = 'N';
SELECT *
FROM igc_cc_det_pf
WHERE cc_acct_line_id = t_cc_acct_line_id;
SELECT *
FROM igc_cc_acct_lines ccac
WHERE ccac.cc_header_id = t_cc_header_id;
SELECT * INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;
SELECT * INTO l_po_headers_rec
FROM PO_HEADERS_ALL
WHERE segment1 = l_cc_headers_rec.cc_num AND
type_lookup_code = 'STANDARD' AND
org_id = l_cc_headers_rec.org_id;
SELECT count(cc_det_pf_line_id)
INTO l_curr_year_pf_lines
FROM igc_cc_det_pf a, igc_cc_acct_lines b, igc_cc_headers c
WHERE
NVL(a.cc_det_pf_date,l_end_date + 1) <= l_end_date AND
a.cc_acct_line_id = b.cc_acct_line_id AND
b.cc_header_id = c.cc_header_id AND
c.cc_header_id = p_cc_header_id;
IGC_CC_PO_HEADERS_ALL_PVT.Update_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_headers_rec);
SELECT * INTO l_po_lines_rec
FROM po_lines_all pol
WHERE
pol.po_header_id = l_po_headers_rec.po_header_id AND
pol.line_num = l_cc_acct_lines_rec.cc_acct_line_num;
SELECT *
INTO l_po_line_locs_rec
FROM po_line_locations_all pll
WHERE pll.po_header_id = l_po_headers_rec.po_header_id
AND pll.po_line_id = l_po_lines_rec.po_line_id;
SELECT count(*)
INTO l_curr_year_pf_lines
FROM igc_cc_det_pf b
WHERE
/* commented this part of code to fix bug 1576123 ( cc_det_pf_date >= l_start_date
AND */
(cc_det_pf_date <= l_end_date ) AND
b.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
IGC_CC_PO_LINES_ALL_PVT.Insert_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_lines_rec);
IGC_CC_PO_LINE_LOCS_ALL_PVT.Insert_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_line_locs_rec);
IGC_CC_PO_DIST_ALL_PVT.Insert_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_dist_rec);
IGC_CC_PO_LINES_ALL_PVT.Update_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_lines_rec);
IGC_CC_PO_LINE_LOCS_ALL_PVT.Update_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_line_locs_rec);
SELECT *
INTO l_po_dist_rec
FROM
po_distributions_all pod
WHERE
pod.po_header_id = l_po_headers_rec.po_header_id AND
pod.po_line_id = l_po_lines_rec.po_line_id AND
pod.line_location_id = l_po_line_locs_rec.line_location_id AND
pod.distribution_num = l_cc_pmt_fcst_rec.cc_det_pf_line_num;
IGC_CC_PO_DIST_ALL_PVT.Insert_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_dist_rec);
IGC_CC_PO_DIST_ALL_PVT.Update_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_dist_rec);
/* Insert row into PO_HEADERS_ALL */
Initialize_Header_Row('I',l_encumbrance_on, l_cc_headers_rec, l_po_headers_rec);
IGC_CC_PO_HEADERS_ALL_PVT.Insert_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_headers_rec);
SELECT count(*)
INTO l_curr_year_pf_lines
FROM igc_cc_det_pf b
WHERE
/* commented this part of code to fix bug 1576123 ( cc_det_pf_date >= l_start_date AND */
(cc_det_pf_date <= l_end_date ) AND
b.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
/* Insert row into PO_LINES_ALL */
Initialize_Lines_Row('I',
l_po_headers_rec.po_header_id,
l_cc_headers_rec.org_id,
l_cc_acct_lines_rec,
l_po_lines_rec,
l_start_date,
l_end_date);
IGC_CC_PO_LINES_ALL_PVT.Insert_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_lines_rec);
/* Insert row into PO_LINE_LOCATIONS_ALL */
Initialize_Line_Locs_Row('I',
l_encumbrance_on,
l_po_headers_rec,
l_po_lines_rec,
l_po_line_locs_rec);
IGC_CC_PO_LINE_LOCS_ALL_PVT.Insert_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_line_locs_rec);
IGC_CC_PO_DIST_ALL_PVT.Insert_Row(1.0,
FND_API.G_FALSE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_NONE,
l_return_status,
l_msg_count,
l_msg_data,
l_po_dist_rec);
SELECT po_header_id
FROM po_headers_all
WHERE segment1 = p_cc_num AND
type_lookup_code = 'STANDARD' AND
org_id = p_org_id
FOR UPDATE NOWAIT;
SELECT * INTO l_cc_headers_rec
FROM igc_cc_headers
WHERE cc_header_id = p_cc_header_id;