[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(multi_org_flag, 'N')
INTO l_multi_org
FROM fnd_product_groups;
/* SELECT substr(hou.name,1,20) */
/* Bug 943602: Display 30 chars to be consistent with client side code */
/*SELECT substr(hou.name,1,64)*/
/* Bug 1040332, zxzhang, substr is dependent on character set */
/*SELECT substr(hou.name,1,30)*/
SELECT substrb(hou.name,1,30)
INTO x_org_sob_name
FROM hr_organization_units hou
WHERE hou.organization_id = x_org_sob_id;
SELECT org_id
INTO x_org_sob_id
FROM po_system_parameters;
/* SELECT substr(hou.name,1,20) */
/* Bug 943602: Display 30 chars to be consistent with client side code */
/*SELECT substr(hou.name,1,64)*/
/* Bug 1040332, zxzhang, substr is dependent on character set */
/*SELECT substr(hou.name,1,30)*/
SELECT substrb(hou.name,1,30)
INTO x_org_sob_name
FROM hr_organization_units hou
WHERE hou.organization_id = x_org_sob_id;
SELECT fsp.set_of_books_id,
gsb.short_name
INTO x_org_sob_id,
x_org_sob_name
FROM financials_system_parameters fsp,
gl_sets_of_books gsb
WHERE fsp.set_of_books_id = gsb.set_of_books_id;
SELECT umbrella_program_name
INTO l_umb_prog_name
FROM PON_UMBRELLA_PROGRAMS pup
WHERE pup.umbrella_program_id = p_umb_prog_id;
SELECT document_number
INTO l_fon_name
FROM pon_auction_headers_all paha
WHERE paha.auction_header_id = p_fon_ref_id;
SELECT umbrella_program_id
INTO l_umb_prog_id
FROM po_headers_all pha
WHERE pha.po_header_id = p_doc_header_id;
SELECT Decode(p_period_uom, 'DAYS', p_old_date + p_period,
'WEEKS', p_old_date + (p_period * 7),
'MONTHS', Add_months(p_old_date, p_period),
NULL)
INTO p_new_date
FROM dual;
PROCEDURE generate_update_payment_seq (p_po_header_id IN PO_HEADERS_ALL.PO_HEADER_ID%TYPE,
p_line_id IN NUMBER DEFAULT NULL,
p_payment_instr_code IN PO_HEADERS_ALL.CLM_PAYMENT_INSTR_CODE%TYPE)
IS
l_key number;
INSERT INTO po_session_gt
(key,-- l_key
num1,-- po_distribution_id
num2)-- payment_sequence_num
SELECT l_key,
pd.po_distribution_id,
rec.seq_num
FROM po_distributions_all pd,
(SELECT acrn,
rownum AS seq_num
FROM (SELECT DISTINCT acrn
FROM po_distributions_all
WHERE po_header_id = p_po_header_id
ORDER BY acrn)) rec
WHERE pd.acrn = rec.acrn
AND pd.po_header_id = p_po_header_id;
INSERT INTO po_session_gt
(key,-- l_key
num1,-- po_distribution_id
num2)-- payment_sequence_num
SELECT l_key,
pd.po_distribution_id,
1
FROM po_distributions_all pd
WHERE pd.po_header_id = p_po_header_id;
INSERT INTO po_session_gt
(key,-- l_key
num1,-- po_distribution_id
num2)-- payment_sequence_num
SELECT l_key,
pd.po_distribution_id,
rec.seq_num
FROM po_distributions_all pd,
(SELECT fiscal_year,
rownum AS seq_num
FROM (SELECT DISTINCT PO_CORE_S3.get_fiscal_year_from_ccid(set_of_books_id, code_combination_id) as fiscal_year
FROM po_distributions_all
WHERE po_header_id = p_po_header_id
ORDER BY fiscal_year)) rec
WHERE PO_CORE_S3.get_fiscal_year_from_ccid(pd.set_of_books_id, pd.code_combination_id) = rec.fiscal_year
AND pd.po_header_id = p_po_header_id;
INSERT INTO po_session_gt
(key,-- l_key
num1,-- po_distribution_id
num2)-- payment_sequence_num
SELECT l_key,
pd.po_distribution_id,
rec.seq_num
FROM po_distributions_all pd,
(SELECT cancellation_date,
rownum AS seq_num
FROM (SELECT DISTINCT PO_CORE_S3.get_ccid_cancellation_date(set_of_books_id, code_combination_id) as cancellation_date
FROM po_distributions_all
WHERE po_header_id = p_po_header_id
ORDER BY cancellation_date)) rec
WHERE PO_CORE_S3.get_ccid_cancellation_date(pd.set_of_books_id, pd.code_combination_id)= rec.cancellation_date
AND pd.po_header_id = p_po_header_id;
INSERT INTO po_session_gt
(key,-- l_key
num1,-- po_distribution_id
num2)-- payment_sequence_num
SELECT l_key,
pd.po_distribution_id,
rec.seq_num
FROM po_distributions_all pd,
(SELECT acrn,
rownum AS seq_num
FROM (SELECT DISTINCT acrn
FROM po_distributions_all
WHERE po_header_id = p_po_header_id
AND po_line_id = p_line_id
ORDER BY acrn)) rec
WHERE pd.acrn = rec.acrn
AND pd.po_line_id = p_line_id
AND pd.po_header_id = p_po_header_id;
INSERT INTO po_session_gt
(key,-- l_key
num1,-- po_distribution_id
num2)-- payment_sequence_num
SELECT l_key,
pd.po_distribution_id,
1
FROM po_distributions_all pd
WHERE pd.po_header_id = p_po_header_id
AND pd.po_line_id = p_line_id;
INSERT INTO po_session_gt
(key,-- l_key
num1,-- po_distribution_id
num2)-- payment_sequence_num
SELECT l_key,
pd.po_distribution_id,
rec.seq_num
FROM po_distributions_all pd,
(SELECT fiscal_year,
rownum AS seq_num
FROM (SELECT DISTINCT PO_CORE_S3.get_fiscal_year_from_ccid(set_of_books_id, code_combination_id) as fiscal_year
FROM po_distributions_all
WHERE po_header_id = p_po_header_id
AND po_line_id = p_line_id
ORDER BY fiscal_year )) rec
WHERE PO_CORE_S3.get_fiscal_year_from_ccid(pd.set_of_books_id, pd.code_combination_id) = rec.fiscal_year
AND pd.po_line_id = p_line_id
AND pd.po_header_id = p_po_header_id;
INSERT INTO po_session_gt
(key,-- l_key
num1,-- po_distribution_id
num2)-- payment_sequence_num
SELECT l_key,
pd.po_distribution_id,
rec.seq_num
FROM po_distributions_all pd,
(SELECT cancellation_date,
rownum AS seq_num
FROM (SELECT DISTINCT PO_CORE_S3.get_ccid_cancellation_date(set_of_books_id, code_combination_id) as cancellation_date
FROM po_distributions_all
WHERE po_header_id = p_po_header_id
AND po_line_id = p_line_id
ORDER BY cancellation_date)) rec
WHERE PO_CORE_S3.get_ccid_cancellation_date(pd.set_of_books_id, pd.code_combination_id) = rec.cancellation_date
AND pd.po_line_id = p_line_id
AND pd.po_header_id = p_po_header_id;
UPDATE po_distributions_all pda
SET pda.clm_payment_sequence_num = (SELECT gt.num2
FROM po_session_gt gt
WHERE gt.KEY = l_key
AND gt.num1 = pda.po_distribution_id)
WHERE pda.po_header_id= p_po_header_id
AND pda.po_line_id = Nvl(p_line_id, pda.po_line_id);
END generate_update_payment_seq;
SELECT clm_payment_instr_code, org_id
INTO l_payment_instr_code, l_org_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT clm_payment_instr_code,
po_line_id
BULK COLLECT INTO l_payment_instr_code_tbl,
l_po_line_id_tbl
FROM po_lines_all
WHERE po_header_id = p_po_header_id;
UPDATE po_lines_all pol
SET pol.clm_payment_instr_code = NULL
WHERE pol.po_header_id = p_po_header_id;
generate_update_payment_seq (p_po_header_id, NULL,l_payment_instr_code );
UPDATE po_distributions_all pod
SET pod.clm_payment_sequence_num = NULL
WHERE pod.po_header_id = p_po_header_id
AND pod.po_line_id = l_po_line_id_tbl(i)
AND pod.clm_payment_sequence_num IS NOT NULL;
generate_update_payment_seq (p_po_header_id, l_po_line_id_tbl(i),l_payment_instr_code_tbl(i) );
SELECT DISTINCT Decode (draft_line_status,
NULL, 'COMPLETED',
draft_line_status)
INTO line_status_code
FROM (SELECT draft_line_status,
Rank () over ( ORDER BY Decode (draft_line_status,
'ASSIGNED', 1,
'MOD_CREATED', 2,
'PO_CREATED', 3,
'RETURNED', 4,
'WITHDRAWN', 5,
'COMPLETED', 6) ASC) priority
FROM po_lines_draft_all
WHERE draft_id = p_draft_id
AND po_header_id = p_po_header_id)
WHERE priority = 1;
SELECT DECODE(pd.status,
'COMPLETED', DECODE(pohd.mod_draft_id,
NULL, ( SELECT DECODE(prl.assignment_number,
NULL, 'COMPLETED',
'ASSIGNED')
FROM po_requisition_lines_all prl
WHERE prl.par_draft_id = pd.draft_id),
(SELECT DECODE(pd1.status,
'COMPLETED', 'PO_CREATED',
'MOD_CREATED')
FROM po_drafts pd1
WHERE pd1.draft_id = pohd.mod_draft_id)),
pd.status)
INTO line_status_code
FROM po_headers_draft_all pohd,
po_drafts pd
WHERE pd.draft_id = p_draft_id
AND pd.document_id = p_po_header_id
AND pohd.po_header_id = pd.document_id
AND pohd.draft_id = pd.draft_id;
SELECT pd.status
INTO line_status_code
FROM po_headers_draft_all pohd,
po_drafts pd
WHERE pd.draft_id = p_draft_id
AND pd.document_id = p_po_header_id
AND pohd.po_header_id = pd.document_id
AND pohd.draft_id = pd.draft_id;
SELECT displayed_field
INTO x_status_disp
FROm po_lookup_codes
WHERE lookup_type = 'PO_PAR_LINE_STATUS'
AND lookup_code = line_status_code;