[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_REPORT_AUTONOMOUS(
P_MESSAGE_TEXT IN VARCHAR2
, P_USER_ID IN NUMBER
, P_SEQUENCE_NUM IN OUT NOCOPY po_online_report_text.sequence%TYPE
, P_LINE_NUM IN po_online_report_text.line_num%TYPE
, p_shipment_num IN po_online_report_text.shipment_num%TYPE
, p_distribution_num IN po_online_report_text.distribution_num%TYPE
, p_transaction_id IN po_online_report_text.transaction_id%TYPE
, p_transaction_type IN po_online_report_text.transaction_type%TYPE
, p_message_type IN po_online_report_text.message_type%TYPE
, p_text_line IN po_online_report_text.text_line%TYPE
, p_segment1 IN po_online_report_text.segment1%TYPE
, p_online_report_id IN NUMBER
, x_return_status IN OUT NOCOPY VARCHAR2
);
p_dml_operation => PO_GMS_INTEGRATION_PVT.c_DML_OPERATION_DELETE
, p_dist_id => p_distribution_id
, p_project_id => x_project_id
, p_task_id => x_task_id
, p_award_number => p_award_number
, x_award_set_id => l_award_set_id
);
SELECT DISTINCT line_num into x_token FROM po_lines_all pol, po_distributions_all pod
WHERE pol.po_line_id=pod.po_line_id
AND pod.po_distribution_id= p_distibution_id;
select distinct pod.CODE_COMBINATION_ID,CHART_OF_ACCOUNTS_ID
into l_combination_id,l_structure_number
from po_distributions_all pod,gl_code_combinations gcc
where pod.po_distribution_id= p_distibution_id
and pod.code_combination_id=gcc.code_combination_id;
SELECT approved_flag
INTO l_approved_flag
FROM po_headers_all poh
WHERE poh.po_header_id = p_header_id;
Inserting the data passed to the procedure into the PO_SESSION_GT table
KEY - SESSION KEY
NUM1 - DISTRIBUTION ID
CHAR1 - ACCOUNT NUMBER
CHAR2 - LOA VALUE
CHAR3 - Value based on which we have to generate the
ACRN if LOA is not available consider Account number
CHAR4 - Change status
CHAR5 - ACRN
*/
INSERT INTO po_session_gt
(KEY,num1,num2,char1,char2,char3,char4,char5
)
WITH test_tab AS
(SELECT dist.dist_num,
dist.rn rnum,
acc.acc_nbr,
loa.loa_val,
NVL(loa.loa_val,acc.acc_nbr) gen_val,
changestat.change_stat,
acrn.acr
FROM
(SELECT column_value acr,rownum rn FROM TABLE(P_ACRN_TBL)
) acrn,
(SELECT column_value acc_nbr,rownum rn FROM TABLE(P_CODE_ID_TBL)
)acc,
(SELECT column_value loa_val,rownum rn FROM TABLE(P_LOA_TBL)
) loa,
(SELECT column_value dist_num,rownum rn FROM TABLE(P_DIST_ID_TBL)
) dist,
(SELECT column_value change_stat,rownum rn FROM TABLE(P_CHANGE_STAT_TBL)
) changestat
WHERE loa.rn = acc.rn
AND acc.rn = acrn.rn
AND loa.rn = acrn.rn
AND dist.rn = loa.rn
AND dist.rn = acc.rn
AND dist.rn = acrn.rn
AND changestat.rn = acrn.rn
)
SELECT l_key,dist_num,rnum,acc_nbr,loa_val,gen_val,change_stat,acr FROM test_tab;
update po_session_gt set char5 = null where key = l_key;
update po_session_gt set char5 = null where key = l_key and char4 not in ('NOCHANGE');
select char5 bulk collect
into l_acrn_tbl
from po_session_gt
where
key = l_key
order by num2;
DELETE from PO_SESSION_GT where key = l_key;
/*This procedure will update the po_session gt with the ACRN values pulled from
the lookup table for distinct remaining generatable values. It works on the
the values present for the session key provided as the parameter*/
PROCEDURE generate_acrn_from_lov(l_key number)
is
BEGIN
UPDATE po_session_gt gt2
SET char5 =
(SELECT acrn
FROM
(SELECT avail_acrn.acrn,
avail_dist.gen_val
FROM
(SELECT gen_val,rownum rn
from
(SELECT num1 dist_num,
char3 gen_val,
char5 acrn
FROM po_session_gt gt
WHERE KEY = l_key
AND char5 IS NULL
and not exists(
select l_key from po_session_gt gtx where gtx.char3 = gt.char3 and gtx.num1>gt.num1
and KEY=l_key
)
ORDER BY dist_num
))avail_dist,
(SELECT acrn,
rownum rn
FROM
(SELECT meaning acrn
FROM fnd_lookups
WHERE lookup_type = 'PO_ACRN'
AND enabled_flag ='Y'
AND start_date_active<=sysdate
AND (end_date_active IS NULL
OR end_date_active >=sysdate)
AND( meaning NOT IN
(SELECT DISTINCT TO_CHAR(NVL(char5,0))
FROM po_session_gt acrn_test
WHERE KEY = l_key
))
ORDER BY to_number(lookup_code)
)
)avail_acrn
WHERE avail_dist.rn = avail_acrn.rn
)
WHERE gt2.char3 = gen_val
)
WHERE gt2.char5 IS NULL;
UPDATE po_session_gt gt2
SET char5 =
(SELECT ACRN
FROM
(SELECT DISTINCT not_provided.gen_val,
provided.acrn
FROM
(SELECT char5 acrn,
char3 GEN_VAL
FROM po_session_gt
WHERE KEY = l_key
AND char5 IS NULL
) not_provided
LEFT OUTER JOIN
(SELECT DISTINCT char5 acrn,
char3 GEN_VAL
FROM po_session_gt gt1
WHERE KEY = l_key
AND CHAR5 IS NOT NULL
AND NOT EXISTS
(SELECT 1
FROM PO_SESSION_GT GT2
WHERE KEY = l_key
AND CHAR5 IS NOT NULL
AND GT1.CHAR3 = GT2.CHAR3
AND GT1.ROWID > GT2.ROWID
)
) provided
ON provided.gen_val = not_provided.gen_val
) generat
WHERE generat.gen_val = gt2.char3
)
where gt2.char5 IS NULL;
SELECT COUNT(1)
INTO l_count
FROM fnd_lookups
WHERE lookup_type = 'PO_ACRN'
AND meaning = p_acrn;
select instr(upper(p_acrn), 'I'), instr(upper(p_acrn), 'O')
into l_count1, l_count2
from dual;
SELECT pod.po_distribution_id,
pod.distribution_num,
pod.deliver_to_location_id,
pod.deliver_to_person_id,
pod.destination_type_code,
pod.destination_organization_id,
pod.encumbered_flag,
pod.WIP_ENTITY_ID,
pod.wip_line_id,
pod.wip_repetitive_schedule_id,
pod.wip_operation_seq_num,
pod.wip_resource_seq_num,
pod.gl_encumbered_date,
pod.req_distribution_id,
pod.project_id,
pod.task_id,
pod.expenditure_item_date,
pod.expenditure_type,
pod.expenditure_organization_id,
pod.bom_resource_id,
pod.DESTINATION_SUBINVENTORY,
pod.org_id,
pod.DEST_CHARGE_ACCOUNT_ID,
pod.DEST_VARIANCE_ACCOUNT_ID,
pod.CODE_COMBINATION_ID,
pod.BUDGET_ACCOUNT_ID,
pod.ACCRUAL_ACCOUNT_ID,
pod.VARIANCE_ACCOUNT_ID,
pod.distribution_type,
pod.award_id,
pod.attribute1 attribute1,
pod.attribute2 attribute2,
pod.attribute3 attribute3,
pod.attribute4 attribute4,
pod.attribute5 attribute5,
pod.attribute6 attribute6,
pod.attribute7 attribute7,
pod.attribute8 attribute8,
pod.attribute9 attribute9,
pod.attribute10 attribute10,
pod.attribute11 attribute11,
pod.attribute12 attribute12,
pod.attribute13 attribute13,
pod.attribute14 attribute14,
pod.attribute15 attribute15,
pol.line_num,
pol.item_id line_item_id,
pol.line_type_id,
pol.unit_price line_unit_price,
pol.category_id line_category_id,
pol.from_line_id line_from_line_id,
pol.attribute1 line_attribute1,
pol.attribute2 line_attribute2,
pol.attribute3 line_attribute3,
pol.attribute4 line_attribute4,
pol.attribute5 line_attribute5,
pol.attribute6 line_attribute6,
pol.attribute7 line_attribute7,
pol.attribute8 line_attribute8,
pol.attribute9 line_attribute9,
pol.attribute10 line_attribute10,
pol.attribute11 line_attribute11,
pol.attribute12 line_attribute12,
pol.attribute13 line_attribute13,
pol.attribute14 line_attribute14,
pol.attribute15 line_attribute15,
poll.shipment_num,
poll.consigned_flag,
poll.quantity_billed ship_quantity_billed,
poll.quantity_received ship_quantity_received,
poll.closed_code ship_closed_code,
poll.ship_to_organization_id,
poll.Transaction_Flow_Header_Id,
poll.payment_type ship_payment_type,
poll.attribute1 ship_attribute1,
poll.attribute2 ship_attribute2,
poll.attribute3 ship_attribute3,
poll.attribute4 ship_attribute4,
poll.attribute5 ship_attribute5,
poll.attribute6 ship_attribute6,
poll.attribute7 ship_attribute7,
poll.attribute8 ship_attribute8,
poll.attribute9 ship_attribute9,
poll.attribute10 ship_attribute10,
poll.attribute11 ship_attribute11,
poll.attribute12 ship_attribute12,
poll.attribute13 ship_attribute13,
poll.attribute14 ship_attribute14,
poll.attribute15 ship_attribute15,
poh.segment1,
poh.org_id header_org_id,
poh.agent_id header_agent_id,
poh.from_header_id header_from_header_id,
poh.type_lookup_code header_type_lookup_code,
poh.vendor_id header_vendor_id,
poh.vendor_site_id header_vendor_site_id,
poh.attribute1 header_attribute1,
poh.attribute2 header_attribute2,
poh.attribute3 header_attribute3,
poh.attribute4 header_attribute4,
poh.attribute5 header_attribute5,
poh.attribute6 header_attribute6,
poh.attribute7 header_attribute7,
poh.attribute8 header_attribute8,
poh.attribute9 header_attribute9,
poh.attribute10 header_attribute10,
poh.attribute11 header_attribute11,
poh.attribute12 header_attribute12,
poh.attribute13 header_attribute13,
poh.attribute14 header_attribute14,
poh.attribute15 header_attribute15
FROM PO_DISTRIBUTIONS_MERGE_V pod,
PO_LINE_LOCATIONS_MERGE_V poll,
PO_LINES_MERGE_V pol,
PO_HEADERS_MERGE_V poh
WHERE pod.po_line_id = poll.po_line_id
AND pod.line_location_id = poll.line_location_id
AND poll.po_line_id = pol.po_line_id
AND pol.po_header_id = poh.po_header_id
AND poh.po_header_id = p_doc_id
AND NVL(poll.cancel_flag,'N') = 'N'
AND poh.draft_id = pol.draft_id
AND pol.draft_id = poll.draft_id
AND poll.draft_id = pod.draft_id
AND pod.draft_id = p_draftid; --bug 16628805
SELECT PO_ONLINE_REPORT_TEXT_S.nextval
INTO l_report_id
FROM dual;
SELECT org_id
INTO l_current_ou_id
FROM PO_HEADERS_MERGE_V poh
WHERE poh.po_header_id = p_document_id
AND poh.draft_id = NVL(p_draft_id, -1); --bug 16628805
SELECT NVL(FSP.req_encumbrance_flag, 'N') req_encumbrance_flag,
NVL(FSP.purch_encumbrance_flag, 'N') purch_encumbrance_flag,
PSP.EXPENSE_ACCRUAL_CODE,
GLS.chart_of_accounts_id
INTO l_req_encum_on,
l_po_encum_on,
l_expense_accrual_code,
l_coa_id
FROM po_system_parameters_all PSP,
financials_system_params_all FSP,
gl_sets_of_books GLS,
fnd_id_flex_structures COAFS
WHERE FSP.org_id = PSP.org_id
AND FSP.set_of_books_id = GLS.set_of_books_id
AND COAFS.id_flex_num = GLS.chart_of_accounts_id
AND COAFS.application_id = 101 --SQLGL
AND COAFS.id_flex_code = 'GL#'
and PSP.org_id = l_current_ou_id;
SELECT NULL INTO l_dummy
FROM
PO_HEADERS_ALL POH
WHERE POH.po_header_id = p_document_id
FOR UPDATE
NOWAIT;
SELECT NULL INTO l_dummy
FROM
PO_HEADERS_DRAFT_ALL POH
WHERE POH.po_header_id = p_document_id
AND POH.draft_id = p_draft_id
FOR UPDATE
NOWAIT;
SELECT NULL INTO l_dummy
FROM
PO_DISTRIBUTIONS_ALL POD
WHERE POD.po_distribution_id = l_dists.po_distribution_id
FOR UPDATE
NOWAIT;
SELECT NULL INTO l_dummy
FROM
PO_DISTRIBUTIONS_DRAFT_ALL POD
WHERE POD.po_distribution_id = l_dists.po_distribution_id
AND POD.draft_id = p_draft_id
FOR UPDATE
NOWAIT;
SELECT nvl(pltb.outside_operation_flag, 'N')
INTO l_osp_flag
FROM po_line_types_b pltb
WHERE pltb.line_type_id = l_dists.line_type_id;
select entity_type
into l_entity_type
from wip_entities
where wip_entity_id = l_dists.wip_entity_id
and organization_id = l_dists.org_id;
update po_distributions_all
set code_combination_id = l_code_combination_id
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
where po_distribution_id = l_dists.po_distribution_id;
update po_distributions_draft_all
set code_combination_id = l_code_combination_id
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
where po_distribution_id = l_dists.po_distribution_id
and draft_id = p_draft_id;
update po_distributions_all
set budget_account_id = l_budget_account_id
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
where po_distribution_id = l_dists.po_distribution_id;
update po_distributions_draft_all
set budget_account_id = l_budget_account_id
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
where po_distribution_id = l_dists.po_distribution_id
and draft_id = p_draft_id;
update po_distributions_all
set accrual_account_id = l_accrual_account_id
, variance_account_id = l_variance_account_id
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
where po_distribution_id = l_dists.po_distribution_id;
update po_distributions_draft_all
set accrual_account_id = l_accrual_account_id
, variance_account_id = l_variance_account_id
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
where po_distribution_id = l_dists.po_distribution_id
and draft_id = p_draft_id;
update po_distributions_all
set dest_charge_account_id = l_dest_charge_account_id
, dest_variance_account_id = l_dest_variance_account_id
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
where po_distribution_id = l_dists.po_distribution_id;
update po_distributions_draft_all
set dest_charge_account_id = l_dest_charge_account_id
, dest_variance_account_id = l_dest_variance_account_id
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
where po_distribution_id = l_dists.po_distribution_id
and draft_id = p_draft_id;
INSERT_REPORT_AUTONOMOUS(
P_MESSAGE_TEXT => l_fb_error_msg
, P_USER_ID => FND_GLOBAL.user_id
, P_SEQUENCE_NUM => x_sequence
, P_LINE_NUM => l_dists.line_num
, p_shipment_num => l_dists.shipment_num
, p_distribution_num => l_dists.distribution_num
, p_transaction_id => l_dists.po_distribution_id
, p_transaction_type => 'ACCOUNT_GENERATION'
, p_message_type => 'E'
, p_text_line => NULL
, p_segment1 => l_dists.segment1
, p_online_report_id => l_report_id
, x_return_status => l_return_status
);
INSERT_REPORT_AUTONOMOUS(
P_MESSAGE_TEXT => NULL
, P_USER_ID => FND_GLOBAL.user_id
, P_SEQUENCE_NUM => x_sequence
, P_LINE_NUM => l_dists.line_num
, p_shipment_num => l_dists.shipment_num
, p_distribution_num => l_dists.distribution_num
, p_transaction_id => l_dists.po_distribution_id
, p_transaction_type => 'ACCOUNT_GENERATION'
, p_message_type => 'E'
, p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_CHARGE_FLEX')
, p_segment1 => l_dists.segment1
, p_online_report_id => l_report_id
, x_return_status => l_return_status
);
INSERT_REPORT_AUTONOMOUS(
P_MESSAGE_TEXT => NULL
, P_USER_ID => FND_GLOBAL.user_id
, P_SEQUENCE_NUM => x_sequence
, P_LINE_NUM => l_dists.line_num
, p_shipment_num => l_dists.shipment_num
, p_distribution_num => l_dists.distribution_num
, p_transaction_id => l_dists.po_distribution_id
, p_transaction_type => 'ACCOUNT_GENERATION'
, p_message_type => 'E'
, p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_ACCRUAL_FLEX')
, p_segment1 => l_dists.segment1
, p_online_report_id => l_report_id
, x_return_status => l_return_status
);
INSERT_REPORT_AUTONOMOUS(
P_MESSAGE_TEXT => NULL
, P_USER_ID => FND_GLOBAL.user_id
, P_SEQUENCE_NUM => x_sequence
, P_LINE_NUM => l_dists.line_num
, p_shipment_num => l_dists.shipment_num
, p_distribution_num => l_dists.distribution_num
, p_transaction_id => l_dists.po_distribution_id
, p_transaction_type => 'ACCOUNT_GENERATION'
, p_message_type => 'E'
, p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_BUDGET_FLEX')
, p_segment1 => l_dists.segment1
, p_online_report_id => l_report_id
, x_return_status => l_return_status
);
INSERT_REPORT_AUTONOMOUS(
P_MESSAGE_TEXT => NULL
, P_USER_ID => FND_GLOBAL.user_id
, P_SEQUENCE_NUM => x_sequence
, P_LINE_NUM => l_dists.line_num
, p_shipment_num => l_dists.shipment_num
, p_distribution_num => l_dists.distribution_num
, p_transaction_id => l_dists.po_distribution_id
, p_transaction_type => 'ACCOUNT_GENERATION'
, p_message_type => 'E'
, p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_VARIANCE_FLEX')
, p_segment1 => l_dists.segment1
, p_online_report_id => l_report_id
, x_return_status => l_return_status
);
INSERT_REPORT_AUTONOMOUS(
P_MESSAGE_TEXT => NULL
, P_USER_ID => FND_GLOBAL.user_id
, P_SEQUENCE_NUM => x_sequence
, P_LINE_NUM => l_dists.line_num
, p_shipment_num => l_dists.shipment_num
, p_distribution_num => l_dists.distribution_num
, p_transaction_id => l_dists.po_distribution_id
, p_transaction_type => 'ACCOUNT_GENERATION'
, p_message_type => 'E'
, p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_DEST_CHARGE_FLEX')
, p_segment1 => l_dists.segment1
, p_online_report_id => l_report_id
, x_return_status => l_return_status
);
INSERT_REPORT_AUTONOMOUS(
P_MESSAGE_TEXT => NULL
, P_USER_ID => FND_GLOBAL.user_id
, P_SEQUENCE_NUM => x_sequence
, P_LINE_NUM => l_dists.line_num
, p_shipment_num => l_dists.shipment_num
, p_distribution_num => l_dists.distribution_num
, p_transaction_id => l_dists.po_distribution_id
, p_transaction_type => 'ACCOUNT_GENERATION'
, p_message_type => 'E'
, p_text_line => fnd_message.get_string('PO', 'PO_ALL_NO_DEST_VARIANCE_FLEX')
, p_segment1 => l_dists.segment1
, p_online_report_id => l_report_id
, x_return_status => l_return_status
);
PROCEDURE INSERT_REPORT_AUTONOMOUS(
P_MESSAGE_TEXT IN VARCHAR2
, P_USER_ID IN NUMBER
, P_SEQUENCE_NUM IN OUT NOCOPY po_online_report_text.sequence%TYPE
, P_LINE_NUM IN po_online_report_text.line_num%TYPE
, p_shipment_num IN po_online_report_text.shipment_num%TYPE
, p_distribution_num IN po_online_report_text.distribution_num%TYPE
, p_transaction_id IN po_online_report_text.transaction_id%TYPE
, p_transaction_type IN po_online_report_text.transaction_type%TYPE
, p_message_type IN po_online_report_text.message_type%TYPE
, p_text_line IN po_online_report_text.text_line%TYPE
, p_segment1 IN po_online_report_text.segment1%TYPE
, p_online_report_id IN NUMBER
, x_return_status IN OUT NOCOPY VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
d_api_name CONSTANT VARCHAR2(30) := 'insert_report_autonomous';
INSERT INTO PO_ONLINE_REPORT_TEXT(
online_report_id
, sequence
, last_updated_by
, last_update_date
, created_by
, creation_date
, line_num
, shipment_num
, distribution_num
, transaction_id
, transaction_type
, message_type
, text_line
, segment1
)
VALUES(
p_online_report_id
, p_sequence_num
, l_user_id
, SYSDATE
, l_user_id
, SYSDATE
, p_line_num
, p_shipment_num
, p_distribution_num
, p_transaction_id
, p_transaction_type
, p_message_type
, NVL(p_text_line,l_message_text)
, p_segment1
);
END insert_report_autonomous;
Inserting the data passed to the procedure into the PO_SESSION_GT table
KEY - SESSION KEY
NUM1 - DISTRIBUTION ID
CHAR1 - ACCOUNT NUMBER
CHAR2 - LOA VALUE
CHAR3 - Value based on which we have to generate the
ACRN if LOA is not available consider Account number
CHAR4 - Change status
CHAR5 - ACRN
*/
INSERT INTO po_session_gt
(KEY,num1,char1,char2,char3,char4,char5)
WITH test_tab AS
(SELECT dist.dist_num,
dist.rn rnum,
acc.acc_nbr,
loa.loa_val,
NVL(loa.loa_val,acc.acc_nbr) gen_val,
changestat.change_stat,
acrn.acr
FROM
(SELECT column_value acr,rownum rn FROM TABLE(P_ACRN_TBL)
) acrn,
(SELECT column_value acc_nbr,rownum rn FROM TABLE(P_CODE_ID_TBL)
)acc,
(SELECT column_value loa_val,rownum rn FROM TABLE(P_LOA_TBL)
) loa,
(SELECT column_value dist_num,rownum rn FROM TABLE(P_DIST_ID_TBL)
) dist,
(SELECT column_value change_stat,rownum rn FROM TABLE(P_CHANGE_STAT_TBL)
) changestat
WHERE loa.rn = acc.rn
AND acc.rn = acrn.rn
AND loa.rn = acrn.rn
AND dist.rn = loa.rn
AND dist.rn = acc.rn
AND dist.rn = acrn.rn
AND changestat.rn = acrn.rn
)
SELECT l_key,dist_num,acc_nbr,loa_val,gen_val,change_stat,acr FROM test_tab;
INSERT INTO po_session_gt
(KEY,
num1,
char1,
char2,
char3,
char4,
char5)
SELECT DISTINCT l_key,
po_distribution_id,
code_combination_id,
clm_misc_loa,
Nvl(clm_misc_loa, code_combination_id),
change_status,
acrn
FROM po_distributions_merge_v
WHERE po_header_id = p_header_id;
SELECT DISTINCT num1 dist_id,
To_number(char1) code_combination_id,
char2,
char4,
Nvl(char5, '')
bulk collect INTO l_dist_id_tbl, l_code_id_tbl, l_loa_tbl, l_change_stat_tbl,
l_acrn_tbl
FROM po_session_gt gt1
WHERE KEY = l_key
AND NOT EXISTS (SELECT 1
FROM po_session_gt gt2
WHERE KEY = l_key
AND gt1.ROWID > gt2.ROWID
AND gt1.num1 = gt2.num1);
delete from po_session_gt where key = l_key;
INSERT INTO po_session_gt
(KEY,num1,num2,char1,char2,char3,char4,char5)
WITH test_tab AS
(SELECT dist.dist_num,
dist.rn rnum,
acc.acc_nbr,
loa.loa_val,
NVL(loa.loa_val,acc.acc_nbr) gen_val,
changestat.change_stat,
acrn.acr
FROM
(SELECT column_value acr,rownum rn FROM TABLE(N_ACRN_TBL)
) acrn,
(SELECT column_value acc_nbr,rownum rn FROM TABLE(L_CODE_ID_TBL)
)acc,
(SELECT column_value loa_val,rownum rn FROM TABLE(L_LOA_TBL)
) loa,
(SELECT column_value dist_num,rownum rn FROM TABLE(L_DIST_ID_TBL)
) dist,
(SELECT column_value change_stat,rownum rn FROM TABLE(L_CHANGE_STAT_TBL)
) changestat
WHERE loa.rn = acc.rn
AND acc.rn = acrn.rn
AND loa.rn = acrn.rn
AND dist.rn = loa.rn
AND dist.rn = acc.rn
AND dist.rn = acrn.rn
AND changestat.rn = acrn.rn
)
SELECT l_key,dist_num,rnum,acc_nbr,loa_val,gen_val,change_stat,acr FROM test_tab;
select char5
into l_acrn
from po_session_gt
where
key = l_key
and num1 = P_DIST_ID_TBL(i);
delete from po_session_gt where key = l_key;