[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_delete_draft IN VARCHAR2
);
PROCEDURE update_acceptance_status
( p_draft_id IN NUMBER,
p_acceptance_action IN VARCHAR2
);
PROCEDURE create_update_locks
( p_draft_id IN NUMBER,
p_return_status IN OUT NOCOPY VARCHAR2,
x_results OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
);
PROCEDURE update_revnum_headers_ext
(p_draft_id IN NUMBER,
p_po_header_id IN NUMBER
)
IS
l_header_revision NUMBER;
d_api_name CONSTANT VARCHAR2(30) := 'update_revnum_headers_ext';
SELECT revision_num
INTO l_header_revision
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT 'Y'
INTO l_is_mod_document
FROM po_drafts
WHERE draft_id = p_draft_id
AND draft_type = 'MOD';
UPDATE po_headers_all_ext_b phaeb
SET revision_num = l_header_revision
WHERE EXISTS ( SELECT 'Lock exists for the address'
FROM po_entity_locks poel
WHERE poel.entity_pk1 = phaeb.po_header_id
AND poel.entity_pk2 = phaeb.c_ext_attr39 -- address type
AND poel.lock_by_draft_id = p_draft_id
AND poel.entity_name = G_LOCK_ADDRESS_ENTITY
)
AND phaeb.po_header_id = p_po_header_id
AND phaeb.draft_id = -1
AND phaeb.c_ext_attr40 = 'PO_UDA_ADDRESS_TYPES';
PO_LOG.proc_end(d_module, 'number of rows updated', SQL%ROWCOUNT);
END update_revnum_headers_ext;
SELECT PO_DRAFTS_S.nextval
INTO l_draft_id
FROM DUAL;
p_delete_processed_draft IN VARCHAR2,
p_acceptance_action IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_caller IN VARCHAR2 DEFAULT NULL
) IS
d_api_name CONSTANT VARCHAR2(30) := 'transfer_draft_to_txn';
PO_LOG.proc_begin(d_module, 'p_delete_processed_draft',
p_delete_processed_draft);
PO_LOG.stmt(d_module, d_position, 'update acceptance action');
update_acceptance_status
( p_draft_id => p_draft_id,
p_acceptance_action => p_acceptance_action
);
PO_DRAFT_APPR_STATUS_PVT.update_approval_status
( p_draft_info => l_draft_info
, x_rebuild_attribs => l_rebuild_attribs -- Bug#4902870
);
SELECT 'Y'
INTO l_is_mod_document
FROM po_drafts
WHERE draft_id = p_draft_id
AND draft_type = 'MOD';
SELECT revision_num
INTO l_header_revision
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
UPDATE po_headers_all
SET revision_num = l_header_revision,
revised_date = SYSDATE
WHERE po_header_id = p_po_header_id;
UPDATE po_lines_all pla
SET revision_num = l_header_revision
WHERE EXISTS ( SELECT 'Lock exists for the line'
FROM po_entity_locks poel
WHERE poel.entity_pk1 = pla.po_line_id
AND poel.lock_by_draft_id = p_draft_id
AND poel.entity_name = G_LOCK_LINE_ENTITY
)
-- : Stamping revision_num for newly added lines on mod.
OR EXISTS ( SELECT 'New Line'
FROM po_lines_draft_all pld
WHERE pld.po_line_id = pla.po_line_id
AND pld.draft_id = p_draft_id
AND pld.change_status = 'NEW'
)
AND pla.po_header_id = p_po_header_id;
UPDATE po_line_locations_all plla
SET revision_num = l_header_revision
WHERE EXISTS ( SELECT 'Lock exists for the shipment'
FROM po_entity_locks poel
WHERE poel.entity_pk1 = plla.line_location_id
AND poel.lock_by_draft_id = p_draft_id
AND poel.entity_name = G_LOCK_SHIPMENT_ENTITY
)
-- : Stamping revision_num for newly added shipment on mod.
OR EXISTS ( SELECT 'New Shipment'
FROM po_line_locations_draft_all plld
WHERE plld.line_location_id = plla.line_location_id
AND plld.draft_id = p_draft_id
AND plld.change_status = 'NEW'
)
AND plla.po_header_id=p_po_header_id;
UPDATE po_distributions_all pda
SET revision_num = l_header_revision
WHERE EXISTS ( SELECT 'Lock exists for the distribution'
FROM po_entity_locks poel
WHERE poel.entity_pk1 = pda.po_distribution_id
AND poel.lock_by_draft_id = p_draft_id
AND poel.entity_name = G_LOCK_DIST_ENTITY
)
-- : Stamping revision_num for newly added shipment on mod.
OR EXISTS ( SELECT 'New Distribution'
FROM po_distributions_draft_all pdd
WHERE pdd.po_distribution_id = pda.po_distribution_id
AND pdd.draft_id = p_draft_id
AND pdd.change_status = 'NEW'
)
AND pda.po_header_id=p_po_header_id;
UPDATE po_exhibit_details pex
SET revision_num = l_header_revision
WHERE EXISTS ( SELECT 'Lock exists for the exhibit'
FROM po_entity_locks poel
WHERE poel.entity_pk1 = pex.po_exhibit_details_id
AND poel.lock_by_draft_id = p_draft_id
--AND poel.entity_name = G_LOCK_EXHIBIT_ENTITY
)
-- : Stamping revision_num for newly added shipment on mod.
OR EXISTS ( SELECT 'New Exhibit'
FROM po_exhibit_details_draft pexd
WHERE pexd.po_exhibit_details_id = pex.po_exhibit_details_id
AND pexd.draft_id = p_draft_id
AND pexd.change_status = 'NEW'
)
AND pex.po_header_id=p_po_header_id;
PO_LOG.stmt(d_module,d_position,'Uda update handled in PO_DRAFT_MERGE_PKG.MergeReserve_Autonomous');
p_delete_draft => p_delete_processed_draft
);
SELECT authorization_status
INTO l_new_approval_status
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
PO_HEADERS_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_po_header_id => NULL
);
Select po_line_id
BULK Collect into po_line_ids_tbl
from po_lines_draft_all pold
where pold.draft_id = p_draft_id;
delete_line_ucas(p_po_draft_id => p_draft_id,
po_line_ids_tbl => po_line_ids_tbl);
PO_LINES_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_po_line_id => NULL
);
PO_LINE_LOCATIONS_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_line_location_id => NULL
);
PO_DISTRIBUTIONS_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_po_distribution_id => NULL
);
PO_GA_ORG_ASSIGN_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_org_assignment_id => NULL
);
PO_PRICE_DIFF_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_price_differential_id => NULL
);
PO_NOTIFICATION_CTRL_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_notification_id => NULL
);
PO_ATTR_VALUES_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_attribute_values_id => NULL
);
PO_ATTR_VALUES_TLP_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_attribute_values_tlp_id => NULL
);
PO_PRICE_ADJ_DRAFT_PKG.delete_rows
( p_draft_id => p_draft_id,
p_price_adjustment_id => NULL
);
PO_EXHIBITS_PVT.delete_rows
( p_draft_id => p_draft_id,
p_po_exhibit_details_id => NULL
);
DELETE FROM po_drafts
WHERE draft_id = p_draft_id;
SELECT NVL(MAX(FND_API.G_FALSE), FND_API.G_TRUE)
INTO x_draft_info.new_document
FROM po_headers_all POH
WHERE POH.po_header_id = p_po_header_id;
SELECT PHD.type_lookup_code,
PHD.quote_type_lookup_code,
NVL(PHD.global_agreement_flag, 'N')
INTO l_type_lookup_code,
l_quote_type_lookup_code,
x_draft_info.ga_flag
FROM PO_HEADERS_DRAFT_ALL PHD
WHERE PHD.po_header_id = p_po_header_id
AND PHD.draft_id = p_draft_id;
SELECT PHA.type_lookup_code,
PHA.quote_type_lookup_code,
NVL(PHA.global_agreement_flag, 'N')
INTO l_type_lookup_code,
l_quote_type_lookup_code,
x_draft_info.ga_flag
FROM PO_HEADERS_ALL PHA
WHERE PHA.po_header_id = p_po_header_id;
SELECT draft_type
INTO x_draft_info.draft_type
FROM po_drafts pod
WHERE pod.draft_id = p_draft_id;
SELECT DFT.draft_id,
DFT.status,
DFT.owner_role
INTO x_draft_id,
x_draft_status,
x_draft_owner_role
FROM po_drafts DFT
WHERE DFT.document_id = p_po_header_id
AND DFT.status <> g_status_COMPLETED
AND DFT.status <> g_status_WITHDRAWN --Mod Project
AND Nvl(dft.draft_type,'NULL') <> 'PAR'; --
SELECT request_id
INTO x_request_id
FROM po_drafts
WHERE draft_id = p_draft_id;
SELECT lock_owner_role,
lock_owner_user_id
INTO x_lock_owner_role,
x_lock_owner_user_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
UPDATE po_headers_all
SET lock_owner_role = p_role,
lock_owner_user_id = p_role_user_id
WHERE po_header_id = p_po_header_id;
PROCEDURE update_permission_check
( p_calling_module IN VARCHAR2,
p_po_header_id IN NUMBER,
p_role IN VARCHAR2,
p_skip_cat_upload_chk IN VARCHAR2,
x_update_allowed OUT NOCOPY VARCHAR2,
x_locking_applicable OUT NOCOPY VARCHAR2,
x_unlock_required OUT NOCOPY VARCHAR2,
x_message OUT NOCOPY VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_permission_check';
x_update_allowed := FND_API.G_TRUE;
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
x_message := 'PO_UPDATE_NOT_ALLOWED';
x_update_allowed := FND_API.G_FALSE;
x_message := 'PO_UPDATE_NOT_ALLOWED';
x_update_allowed := FND_API.G_FALSE;
SELECT NVL(authorization_status, 'INCOMPLETE')
INTO l_authorization_status
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
l_authorization_status || '. This role cannot update the document ' ||
'in this status');
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
x_update_allowed := FND_API.G_FALSE;
END update_permission_check;
PROCEDURE update_permission_check
( p_calling_module IN VARCHAR2,
p_po_header_id IN NUMBER,
p_role IN VARCHAR2,
p_skip_cat_upload_chk IN VARCHAR2,
x_update_allowed OUT NOCOPY VARCHAR2,
x_locking_applicable OUT NOCOPY VARCHAR2,
x_unlock_required OUT NOCOPY VARCHAR2,
x_message OUT NOCOPY VARCHAR2,
x_token_name_tbl OUT NOCOPY PO_TBL_VARCHAR30,
x_token_value_tbl OUT NOCOPY PO_TBL_VARCHAR2000
) IS
l_num_records NUMBER;
update_permission_check
( p_calling_module => p_calling_module,
p_po_header_id => p_po_header_id,
p_role => p_role,
p_skip_cat_upload_chk => p_skip_cat_upload_chk,
x_update_allowed => x_update_allowed,
x_locking_applicable => x_locking_applicable,
x_unlock_required => x_unlock_required,
x_message => x_message
);
IF (x_update_allowed = FND_API.G_TRUE) THEN
RETURN;
IF (x_message IN ('PO_DOC_LOCKED', 'PO_UPDATE_NOT_ALLOWED',
'PO_AUTH_STATUS_ERROR', 'PO_DOC_LOCKED_BY_OTHER_ROLE',
'PO_UPLOAD_PENDING_RUNNING', 'PO_UPLOAD_ERROR',
'PO_BUYER_ACCEPTANCE_PENDING',
'PO_LOCKED_BY_PDOI_ERR')) THEN
x_token_name_tbl := PO_TBL_VARCHAR30 ('STYLE_NAME');
END update_permission_check;
PROCEDURE update_permission_check
( p_calling_module IN VARCHAR2,
p_po_header_id IN NUMBER,
p_role IN VARCHAR2,
p_skip_cat_upload_chk IN VARCHAR2,
x_update_allowed OUT NOCOPY VARCHAR2,
x_locking_applicable OUT NOCOPY VARCHAR2,
x_unlock_required OUT NOCOPY VARCHAR2,
x_message OUT NOCOPY VARCHAR2,
x_message_text OUT NOCOPY VARCHAR2
) IS
l_token_name_tbl PO_TBL_VARCHAR30;
update_permission_check
( p_calling_module => p_calling_module,
p_po_header_id => p_po_header_id,
p_role => p_role,
p_skip_cat_upload_chk => p_skip_cat_upload_chk,
x_update_allowed => x_update_allowed,
x_locking_applicable => x_locking_applicable,
x_unlock_required => x_unlock_required,
x_message => x_message,
x_token_name_tbl => l_token_name_tbl,
x_token_value_tbl => l_token_value_tbl
);
IF (x_update_allowed = FND_API.G_TRUE) THEN
RETURN;
END update_permission_check;
SELECT type_lookup_code,
NVL(global_agreement_flag, 'N'),
approved_date,
lock_owner_role
INTO l_type_lookup_code,
l_ga_flag,
l_approved_date,
l_current_lock_owner_role
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT type_lookup_code,
NVL(global_agreement_flag, 'N')
INTO l_type_lookup_code,
l_ga_flag
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
update_permission_check
( p_calling_module => p_calling_module,
p_po_header_id => p_po_header_id,
p_role => p_role,
x_update_allowed => x_locking_allowed,
x_locking_applicable => l_locking_applicable,
x_unlock_required => l_unlock_required,
x_message => x_message,
x_message_text => x_message_text
);
PROCEDURE update_draft_status
( p_draft_id IN NUMBER,
p_new_status IN VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_draft_status';
UPDATE po_drafts
SET status = p_new_status,
request_id = DECODE (p_new_status,
g_status_PDOI_PROCESSING, l_cur_conc_request_id,
NULL)
WHERE draft_id = p_draft_id;
END update_draft_status;
SELECT FND_API.G_TRUE
INTO l_pending_changes_exist
FROM dual
WHERE EXISTS (SELECT 'Draft Exists'
FROM po_drafts
WHERE document_id = p_po_header_id
AND status <> g_status_COMPLETED
AND NVL(draft_type,g_draft_type_DOC_CHANGE) = g_draft_type_DOC_CHANGE);
SELECT supplier_auth_enabled_flag
INTO l_supplier_auth_enabled_flag
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
UPDATE po_headers_all
SET supplier_auth_enabled_flag
= p_supplier_auth_enabled_flag
WHERE po_header_id = p_po_header_id;
SELECT cat_admin_auth_enabled_flag
INTO l_cat_admin_auth_enable_flag
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
UPDATE po_headers_all
SET cat_admin_auth_enabled_flag
= p_cat_admin_auth_enable_flag
WHERE po_header_id = p_po_header_id;
SELECT job_status,
-- role_user_id,
job_number,
job_status_display,
is_error
INTO x_upload_status_code,
--x_upload_requestor_role_id,
x_upload_job_number,
x_upload_status_display,
x_upload_is_error
FROM icx_cat_latest_batch_jobs_v
WHERE po_header_Id = p_po_header_id
AND role = p_role;
SELECT job_status,
-- role_user_id,
ROLE,
job_number,
job_status_display
INTO x_upload_status_code,
--x_upload_requestor_role_id,
x_upload_requestor_role,
x_upload_job_number,
x_upload_status_display
FROM icx_cat_latest_batch_jobs_v
WHERE po_header_id = p_po_header_id
AND job_status IN (g_upload_status_PENDING,
g_upload_status_RUNNING,
g_upload_status_ERROR)
AND ROWNUM = 1;
SELECT org_id,
segment1,
revision_num
INTO l_org_id,
l_segment1,
l_revision_num
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
UPDATE icx_cat_batch_jobs
SET JOB_STATUS ='COMPLETED'
WHERE po_header_id=p_po_header_id;
SELECT agent_id
INTO l_agent_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT 1
INTO l_dummy
FROM po_headers_all POH
WHERE POH.po_header_id = p_po_header_id
AND NVL(cancel_flag, 'N') = 'N'
AND NVL(closed_code, 'OPEN') NOT IN ('CLOSED', 'FINALLY CLOSED')
AND NVL(frozen_flag, 'N') <> 'Y'
AND NVL(user_hold_flag, 'N') <> 'Y';
PO_REQ_LINES_SV.update_reqs_in_pool_flag;
SELECT PH.revision_num
INTO l_orig_revision_num
FROM po_headers_all PH
WHERE PH.po_header_id = p_draft_info.po_header_id;
SELECT 'Y',
podsh.ame_transaction_type
INTO l_new_ame_appr_id_req,
l_ame_transaction_type
FROM po_headers_all poh,
po_doc_style_headers podsh
WHERE poh.style_id = podsh.style_id
AND NVL(podsh.change_process_type,'CHANGE_ORDER') <> 'MODIFICATION'
AND podsh.ame_transaction_type IS NOT NULL
AND poh.po_header_id = p_draft_info.po_header_id;
UPDATE po_headers_all
SET revision_num = l_new_revision_num,
revised_date = SYSDATE,
ame_approval_id = DECODE(l_new_ame_appr_id_req,
'Y', po_ame_approvals_s.NEXTVAL,
ame_approval_id),
ame_transaction_type = DECODE(l_new_ame_appr_id_req,
'Y', l_ame_transaction_type,
ame_transaction_type)
WHERE po_header_id = p_draft_info.po_header_id;
p_delete_draft IN VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'complete_transfer';
IF (p_delete_draft = FND_API.G_FALSE OR
p_delete_draft = 'X') THEN
d_position := 10;
update_draft_status
( p_draft_id => p_draft_info.draft_id,
p_new_status => g_status_COMPLETED
);
IF (p_delete_draft = FND_API.G_TRUE OR
p_delete_draft = 'X') THEN
IF (p_delete_draft = FND_API.G_TRUE) THEN
l_exclude_ctrl_tbl := FND_API.G_FALSE;
ELSIF (p_delete_draft = 'X') THEN
l_exclude_ctrl_tbl := FND_API.G_TRUE;
PROCEDURE update_acceptance_status
( p_draft_id IN NUMBER,
p_acceptance_action IN VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'update_acceptance_status';
UPDATE po_headers_draft_all
SET change_accepted_flag = l_change_accepted_flag
WHERE draft_id = p_draft_id
AND change_accepted_flag IS NULL;
UPDATE po_lines_draft_all
SET change_accepted_flag = l_change_accepted_flag
WHERE draft_id = p_draft_id
AND change_accepted_flag IS NULL;
UPDATE po_line_locations_draft_all
SET change_accepted_flag = l_change_accepted_flag
WHERE draft_id = p_draft_id
AND change_accepted_flag IS NULL;
UPDATE po_distributions_draft_all
SET change_accepted_flag = l_change_accepted_flag
WHERE draft_id = p_draft_id
AND change_accepted_flag IS NULL;
UPDATE po_ga_org_assign_draft
SET change_accepted_flag = l_change_accepted_flag
WHERE draft_id = p_draft_id
AND change_accepted_flag IS NULL;
UPDATE po_price_diff_draft
SET change_accepted_flag = l_change_accepted_flag
WHERE draft_id = p_draft_id
AND change_accepted_flag IS NULL;
UPDATE po_notification_ctrl_draft
SET change_accepted_flag = l_change_accepted_flag
WHERE draft_id = p_draft_id
AND change_accepted_flag IS NULL;
UPDATE po_attribute_values_draft
SET change_accepted_flag = l_change_accepted_flag
WHERE draft_id = p_draft_id
AND change_accepted_flag IS NULL;
UPDATE po_attribute_values_tlp_draft
SET change_accepted_flag = l_change_accepted_flag
WHERE draft_id = p_draft_id
AND change_accepted_flag IS NULL;
UPDATE po_price_adjustments_draft
SET change_accepted_flag = l_change_accepted_flag
WHERE draft_id = p_draft_id
AND change_accepted_flag IS NULL;
END update_acceptance_status;
SELECT Count(*)
INTO l_draft_count
FROM po_drafts
WHERE document_id=p_po_header_id
AND draft_type='MOD';
SELECT 'Y'
INTO l_mod_flag
FROM dual
WHERE EXISTS(SELECT 'mod enabled po'
FROM po_doc_style_headers pdsh ,
po_headers_all poh
WHERE pdsh.style_id=poh.style_id
AND poh.po_header_id=p_po_header_id
AND pdsh.change_process_type='MODIFICATION');
SELECT 'Y'
INTO l_open_mod_exist_flag
FROM dual
WHERE EXISTS (SELECT 'Open Mod Exists'
FROM po_drafts pod,
po_headers_all poh
WHERE pod.document_id = poh.po_header_id
AND poh.style_id = p_style_id
AND pod.draft_type= 'MOD'
AND pod.status IN ('DRAFT','REJECTED','IN PROCESS',
'PRE-APPROVED','SUPPLIER SIGN'));
DELETE FROM po_session_gt
WHERE index_char1 IN (G_CHANGED_ENTTIES_ID, G_LOCKS_REQUIRED_ID);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity name
char1, -- entity_pk1
char2, -- entity_pk2
char3, -- entity_pk3
char4 -- entity_pk4
)
VALUES(
po_session_gt_s.NEXTVAL,
G_CHANGED_ENTTIES_ID,
p_entity_name_tbl(i),
p_pk1_tbl(i),
p_pk2_tbl(i),
p_pk3_tbl(i),
p_pk4_tbl(i)
);
PO_LOG.stmt(d_module,d_position,'Nmber Of rows Inserted'||SQL%ROWCOUNT);
SELECT pha.TYPE_LOOKUP_CODE, pha.STYLE_ID,phd.po_header_id
INTO l_document_type_code, l_document_style_id, l_po_header_id
FROM po_headers_all pha, po_headers_draft_all phd
WHERE pha.PO_HEADER_ID = phd.PO_HEADER_ID
AND phd.draft_id=p_draft_id ;
create_update_locks (p_draft_id =>p_draft_id,
p_return_status=> x_return_status,
x_results =>x_results);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_HEADER_ENTITY,
draft.po_header_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
WHEN base.s_lock_attrs <> draft.s_lock_attrs THEN 'S'
ELSE NULL
END lock_type
FROM (SELECT poh.po_header_id,
G_DRAFT_ID_MINUS_1 draft_id,
po_header_mod_s_lock_attrs(
NVL(poh.amount_limit, G_NULL_NUM)
) s_lock_attrs,
po_header_mod_p_lock_attrs(
NVL(poh.user_document_status, G_NULL_CHAR),
NVL(poh.vendor_site_id, G_NULL_NUM),
NVL(poh.vendor_contact_id, G_NULL_NUM),
NVL(poh.supplier_notif_method, G_NULL_CHAR),
NVL(poh.fax, G_NULL_CHAR),
NVL(poh.email_address, G_NULL_CHAR),
NVL(poh.clm_external_idv, G_NULL_CHAR),
NVL(poh.clm_vendor_offer_number, G_NULL_CHAR),
NVL(poh.clm_effective_date, G_NULL_DATE),
NVL(poh.agent_id, G_NULL_NUM),
NVL(poh.clm_award_administrator, G_NULL_CHAR),
NVL(poh.comments, G_NULL_CHAR),
NVL(poh.confirming_order_flag, G_NULL_CHAR),
NVL(poh.terms_id, G_NULL_NUM),
NVL(poh.ship_to_location_id, G_NULL_NUM),
NVL(poh.bill_to_location_id, G_NULL_NUM),
NVL(poh.ship_via_lookup_code, G_NULL_CHAR),
NVL(poh.freight_terms_lookup_code, G_NULL_CHAR),
NVL(poh.fob_lookup_code, G_NULL_CHAR),
NVL(poh.shipping_control, G_NULL_CHAR),
NVL(poh.note_to_vendor, G_NULL_CHAR),
NVL(poh.note_to_receiver, G_NULL_CHAR),
NVL(poh.blanket_total_amount, G_NULL_NUM),
NVL(poh.price_update_tolerance, G_NULL_NUM),
NVL(poh.start_date, G_NULL_DATE),
NVL(poh.end_date, G_NULL_DATE),
NVL(poh.clm_max_order_amount, G_NULL_NUM),
NVL(poh.clm_min_order_amount, G_NULL_NUM),
NVL(poh.clm_min_guarantee_award_amt, G_NULL_NUM),
NVL(poh.clm_min_guar_award_amt_percent, G_NULL_NUM),
NVL(poh.auto_sourcing_flag, G_NULL_CHAR),
NVL(poh.update_sourcing_rules_flag, G_NULL_CHAR),
NVL(poh.retro_price_apply_updates_flag, G_NULL_CHAR),
NVL(poh.retro_price_comm_updates_flag, g_null_char),
NVL(poh.attribute_category ,G_NULL_CHAR),
NVL(poh.attribute1 ,G_NULL_CHAR),
NVL(poh.attribute2 ,G_NULL_CHAR),
NVL(poh.attribute3 ,G_NULL_CHAR),
NVL(poh.attribute4 ,G_NULL_CHAR),
NVL(poh.attribute5 ,G_NULL_CHAR),
NVL(poh.attribute6 ,G_NULL_CHAR),
NVL(poh.attribute7 ,G_NULL_CHAR),
NVL(poh.attribute8 ,G_NULL_CHAR),
NVL(poh.attribute9 ,G_NULL_CHAR),
NVL(poh.attribute10 ,G_NULL_CHAR),
NVL(poh.attribute11 ,G_NULL_CHAR),
NVL(poh.attribute12 ,G_NULL_CHAR),
NVL(poh.attribute13 ,G_NULL_CHAR),
NVL(poh.attribute14 ,g_null_char),
NVL(poh.attribute15 ,G_NULL_CHAR),
NVL(poh.umbrella_program_id ,G_NULL_NUM),
NVL(poh.clm_contract_finance_code, G_NULL_CHAR),
NVL(poh.clm_payment_instr_code, G_NULL_CHAR)
) p_lock_attrs
FROM po_headers_all poh,
po_session_gt pogt
WHERE poh.po_header_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_HEADER_ENTITY
) base,
(SELECT pohd.po_header_id,
pohd.draft_id,
po_header_mod_s_lock_attrs(
NVL(pohd.amount_limit, G_NULL_NUM)
) s_lock_attrs,
po_header_mod_p_lock_attrs(
NVL(pohd.user_document_status, G_NULL_CHAR),
NVL(pohd.vendor_site_id, G_NULL_NUM),
NVL(pohd.vendor_contact_id, G_NULL_NUM),
NVL(pohd.supplier_notif_method, G_NULL_CHAR),
NVL(pohd.fax, G_NULL_CHAR),
NVL(pohd.email_address, G_NULL_CHAR),
NVL(pohd.clm_external_idv, G_NULL_CHAR),
NVL(pohd.clm_vendor_offer_number, G_NULL_CHAR),
NVL(pohd.clm_effective_date, G_NULL_DATE),
NVL(pohd.agent_id, G_NULL_NUM),
NVL(pohd.clm_award_administrator, G_NULL_CHAR),
NVL(pohd.comments, G_NULL_CHAR),
NVL(pohd.confirming_order_flag, G_NULL_CHAR),
NVL(pohd.terms_id, G_NULL_NUM),
NVL(pohd.ship_to_location_id, G_NULL_NUM),
NVL(pohd.bill_to_location_id, G_NULL_NUM),
NVL(pohd.ship_via_lookup_code, G_NULL_CHAR),
NVL(pohd.freight_terms_lookup_code, G_NULL_CHAR),
NVL(pohd.fob_lookup_code, G_NULL_CHAR),
NVL(pohd.shipping_control, G_NULL_CHAR),
NVL(pohd.note_to_vendor, G_NULL_CHAR),
NVL(pohd.note_to_receiver, G_NULL_CHAR),
NVL(pohd.blanket_total_amount, G_NULL_NUM),
NVL(pohd.price_update_tolerance, G_NULL_NUM),
NVL(pohd.start_date, G_NULL_DATE),
NVL(pohd.end_date, G_NULL_DATE),
NVL(pohd.clm_max_order_amount, G_NULL_NUM),
NVL(pohd.clm_min_order_amount, G_NULL_NUM),
NVL(pohd.clm_min_guarantee_award_amt, G_NULL_NUM),
NVL(pohd.clm_min_guar_award_amt_percent, G_NULL_NUM),
NVL(pohd.auto_sourcing_flag, G_NULL_CHAR),
NVL(pohd.update_sourcing_rules_flag, G_NULL_CHAR),
NVL(pohd.retro_price_apply_updates_flag, g_null_char),
NVL(pohd.retro_price_comm_updates_flag, G_NULL_CHAR),
NVL(pohd.attribute_category ,G_NULL_CHAR),
NVL(pohd.attribute1 ,G_NULL_CHAR),
NVL(pohd.attribute2 ,G_NULL_CHAR),
NVL(pohd.attribute3 ,G_NULL_CHAR),
NVL(pohd.attribute4 ,G_NULL_CHAR),
NVL(pohd.attribute5 ,G_NULL_CHAR),
NVL(pohd.attribute6 ,G_NULL_CHAR),
NVL(pohd.attribute7 ,G_NULL_CHAR),
NVL(pohd.attribute8 ,G_NULL_CHAR),
NVL(pohd.attribute9 ,G_NULL_CHAR),
NVL(pohd.attribute10 ,G_NULL_CHAR),
NVL(pohd.attribute11 ,G_NULL_CHAR),
NVL(pohd.attribute12 ,G_NULL_CHAR),
NVL(pohd.attribute13 ,G_NULL_CHAR),
nvl(pohd.attribute14 ,g_null_char),
NVL(pohd.attribute15 ,G_NULL_CHAR),
NVL(pohd.umbrella_program_id ,G_NULL_NUM),
NVL(pohd.clm_contract_finance_code, G_NULL_CHAR),
NVL(pohd.clm_payment_instr_code, G_NULL_CHAR)
) p_lock_attrs
FROM po_headers_draft_all pohd,
po_session_gt pogt
WHERE pohd.draft_id = p_draft_id
AND pohd.po_header_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_HEADER_ENTITY
) draft
WHERE base.p_lock_attrs <> draft.p_lock_attrs
OR base.s_lock_attrs <> draft.s_lock_attrs;
PO_LOG.stmt(d_module, d_position, 'Number of Records Inserted',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_HEADER_ENTITY,
draft.po_header_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT pgoa.po_header_id,
G_DRAFT_ID_MINUS_1 draft_id,
po_ga_org_asg_p_lock_attrs(
NVL(pgoa.purchasing_org_id ,g_null_num),
NVL(pgoa.vendor_site_id ,G_NULL_NUM)
) p_lock_attrs,
pgoa.org_assignment_id
FROM po_ga_org_assignments pgoa,
po_session_gt pogt
WHERE pgoa.po_header_id = pogt.char1 -- po_header_id
and pogt.index_char1 = G_CHANGED_ENTTIES_ID
and pogt.index_char2 = G_LOCK_HEADER_ENTITY
) base,--done
(SELECT pgoad.po_header_id,
pgoad.draft_id,
po_ga_org_asg_p_lock_attrs(
NVL(pgoad.purchasing_org_id ,G_NULL_NUM),
NVL(pgoad.vendor_site_id ,G_NULL_NUM)
) P_LOCK_ATTRS,
pgoad.org_assignment_id
FROM po_ga_org_assign_draft pgoad,
po_session_gt pogt
WHERE pgoad.draft_id = p_draft_id
AND pgoad.po_header_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_HEADER_ENTITY
) draft
WHERE base.org_assignment_id = draft.org_assignment_id
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_HEADER_ENTITY,
draft.po_header_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT pnc.po_header_id,
G_DRAFT_ID_MINUS_1 draft_id,
po_notif_ctrl_p_lock_attrs(
NVL(pnc.notification_condition_code ,G_NULL_CHAR),
NVL(pnc.notification_qty_percentage ,G_NULL_NUM),
NVL(pnc.notification_amount ,G_NULL_NUM),
NVL(pnc.start_date_active ,G_NULL_DATE),
NVL(pnc.end_date_active ,G_NULL_DATE)
) p_lock_attrs,
pnc.notification_id
FROM po_notification_controls pnc,
po_session_gt pogt
WHERE pnc.po_header_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_HEADER_ENTITY
) base,
(SELECT pncd.po_header_id,
pncd.draft_id,
po_notif_ctrl_p_lock_attrs(
NVL(pncd.notification_condition_code ,G_NULL_CHAR),
NVL(pncd.notification_qty_percentage ,G_NULL_NUM),
NVL(pncd.notification_amount ,G_NULL_NUM),
NVL(pncd.start_date_active ,G_NULL_DATE),
NVL(pncd.end_date_active ,G_NULL_DATE)
) p_lock_attrs,
pncd.notification_id
FROM po_notification_ctrl_draft pncd,
po_session_gt pogt
WHERE pncd.draft_id = p_draft_id
AND pncd.po_header_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_HEADER_ENTITY
) draft
WHERE base.notification_id = draft.notification_id
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
draft.po_line_id,
draft.draft_id,
CASE
WHEN base.f_lock_attrs <> draft.f_lock_attrs THEN 'F'
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
WHEN base.s_lock_attrs <> draft.s_lock_attrs THEN 'S'
ELSE NULL
END lock_type
FROM (SELECT pol.po_line_id,
G_DRAFT_ID_MINUS_1 draft_id,
po_line_mod_s_lock_attrs(
NVL(pol.quantity, G_NULL_NUM),
NVL(pol.secondary_qty, G_NULL_NUM),
NVL(pol.amount, G_NULL_NUM)
) s_lock_attrs,
po_line_mod_p_lock_attrs(
NVL(pol.line_type_id ,G_NULL_NUM),
NVL(pol.item_revision ,G_NULL_CHAR),
NVL(pol.item_description ,G_NULL_CHAR),
NVL(pol.vendor_product_num ,G_NULL_CHAR),
NVL(pol.transaction_reason_code ,G_NULL_CHAR),
NVL(pol.capital_expense_flag ,G_NULL_CHAR),
NVL(pol.user_document_status ,G_NULL_CHAR),
NVL(pol.category_id ,G_NULL_NUM),
NVL(pol.preferred_grade ,G_NULL_CHAR),
NVL(pol.negotiated_by_preparer_flag ,G_NULL_CHAR),
NVL(pol.clm_option_from_date ,G_NULL_DATE),
NVL(pol.clm_option_to_date ,G_NULL_DATE),
NVL(pol.recoupment_rate ,G_NULL_NUM),
NVL(pol.progress_payment_rate ,G_NULL_NUM),
NVL(pol.max_retainage_amount ,G_NULL_NUM),
NVL(pol.retainage_rate ,G_NULL_NUM),
NVL(pol.note_to_vendor,G_NULL_CHAR),
NVL(pol.supplier_part_auxid ,G_NULL_CHAR),
NVL(pol.un_number_id ,G_NULL_NUM),
NVL(pol.hazard_class_id ,G_NULL_NUM),
NVL(pol.ip_category_id ,G_NULL_NUM),
NVL(pol.allow_price_override_flag ,G_NULL_CHAR),
NVL(pol.not_to_exceed_price ,G_NULL_NUM),
NVL(pol.expiration_date ,G_NULL_DATE),
NVL(pol.min_release_amount ,G_NULL_NUM),
NVL(pol.committed_amount ,G_NULL_NUM),
NVL(pol.quantity_committed ,G_NULL_NUM),
NVL(pol.clm_order_start_date ,G_NULL_DATE),
NVL(pol.clm_max_total_amount ,G_NULL_NUM),
NVL(pol.clm_min_total_amount ,G_NULL_NUM),
NVL(pol.clm_max_total_quantity ,G_NULL_NUM),
NVL(pol.clm_min_total_quantity ,G_NULL_NUM),
NVL(pol.clm_max_order_amount ,G_NULL_NUM),
NVL(pol.clm_min_order_amount ,G_NULL_NUM),
NVL(pol.clm_order_end_date ,G_NULL_DATE),
NVL(pol.clm_max_order_quantity ,G_NULL_NUM),
NVL(pol.clm_min_order_quantity ,G_NULL_NUM),
NVL(pol.clm_total_amount_ordered ,G_NULL_NUM),
NVL(pol.clm_total_quantity_ordered ,G_NULL_NUM),
NVL(pol.attribute_category ,G_NULL_CHAR),
NVL(pol.attribute1 ,G_NULL_CHAR),
NVL(pol.attribute2 ,G_NULL_CHAR),
NVL(pol.attribute3 ,G_NULL_CHAR),
NVL(pol.attribute4 ,G_NULL_CHAR),
NVL(pol.attribute5 ,G_NULL_CHAR),
NVL(pol.attribute6 ,G_NULL_CHAR),
NVL(pol.attribute7 ,G_NULL_CHAR),
NVL(pol.attribute8 ,G_NULL_CHAR),
NVL(pol.attribute9 ,G_NULL_CHAR),
NVL(pol.attribute10 ,G_NULL_CHAR),
NVL(pol.attribute11 ,G_NULL_CHAR),
NVL(pol.attribute12 ,G_NULL_CHAR),
NVL(pol.attribute13 ,G_NULL_CHAR),
NVL(pol.attribute14 ,G_NULL_CHAR),
NVL(pol.attribute15 ,G_NULL_CHAR),
NVL(pol.oke_contract_header_id, G_NULL_NUM),
NVL(pol.oke_contract_version_id, G_NULL_NUM),
NVL(pol.clm_payment_instr_code, G_NULL_CHAR),
NVL(pol.clm_pop_exception_reason, G_NULL_CHAR)
) p_lock_attrs,
po_line_mod_f_lock_attrs(
NVL(pol.clm_exercised_flag, G_NULL_CHAR),
NVL(pol.contract_type, G_NULL_CHAR),
NVL(pol.cost_constraint, G_NULL_CHAR),
NVL(pol.unit_meas_lookup_code, G_NULL_CHAR),
NVL(pol.unit_price, G_NULL_NUM),
NVL(pol.clm_exercised_date, G_NULL_DATE),
NVL(pol.from_header_id, G_NULL_NUM),
NVL(pol.from_line_id, G_NULL_NUM),
NVL(pol.clm_idc_type, G_NULL_CHAR)
) f_lock_attrs
FROM po_lines_all pol,
po_session_gt pogt
WHERE pol.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
) base,
(SELECT pold.po_line_id,
pold.draft_id,
po_line_mod_s_lock_attrs(
NVL(pold.quantity, G_NULL_NUM),
NVL(pold.secondary_qty, G_NULL_NUM),
NVL(pold.amount, G_NULL_NUM)
) s_lock_attrs,
po_line_mod_p_lock_attrs(
NVL(pold.line_type_id ,G_NULL_NUM),
NVL(pold.item_revision ,G_NULL_CHAR),
NVL(pold.item_description ,G_NULL_CHAR),
NVL(pold.vendor_product_num ,G_NULL_CHAR),
NVL(pold.transaction_reason_code ,G_NULL_CHAR),
NVL(pold.capital_expense_flag ,G_NULL_CHAR),
NVL(pold.user_document_status ,G_NULL_CHAR),
NVL(pold.category_id ,G_NULL_NUM),
NVL(pold.preferred_grade ,G_NULL_CHAR),
NVL(pold.negotiated_by_preparer_flag ,G_NULL_CHAR),
NVL(pold.clm_option_from_date ,G_NULL_DATE),
NVL(pold.clm_option_to_date ,G_NULL_DATE),
NVL(pold.recoupment_rate ,G_NULL_NUM),
NVL(pold.progress_payment_rate ,G_NULL_NUM),
NVL(pold.max_retainage_amount ,G_NULL_NUM),
NVL(pold.retainage_rate ,G_NULL_NUM),
NVL(pold.note_to_vendor ,G_NULL_CHAR),
NVL(pold.supplier_part_auxid ,G_NULL_CHAR),
NVL(pold.un_number_id ,G_NULL_NUM),
NVL(pold.hazard_class_id ,G_NULL_NUM),
NVL(pold.ip_category_id ,G_NULL_NUM),
NVL(pold.allow_price_override_flag ,G_NULL_CHAR),
NVL(pold.not_to_exceed_price ,G_NULL_NUM),
NVL(pold.expiration_date ,G_NULL_DATE),
NVL(pold.min_release_amount ,G_NULL_NUM),
NVL(pold.committed_amount ,G_NULL_NUM),
NVL(pold.quantity_committed ,G_NULL_NUM),
NVL(pold.clm_order_start_date ,G_NULL_DATE),
NVL(pold.clm_max_total_amount ,G_NULL_NUM),
NVL(pold.clm_min_total_amount ,G_NULL_NUM),
NVL(pold.clm_max_total_quantity ,G_NULL_NUM),
NVL(pold.clm_min_total_quantity ,G_NULL_NUM),
NVL(pold.clm_max_order_amount ,G_NULL_NUM),
NVL(pold.clm_min_order_amount ,G_NULL_NUM),
NVL(pold.clm_order_end_date ,G_NULL_DATE),
NVL(pold.clm_max_order_quantity ,G_NULL_NUM),
NVL(pold.clm_min_order_quantity ,G_NULL_NUM),
NVL(pold.clm_total_amount_ordered ,G_NULL_NUM),
NVL(pold.clm_total_quantity_ordered ,G_NULL_NUM),
NVL(pold.attribute_category ,G_NULL_CHAR),
NVL(pold.attribute1 ,G_NULL_CHAR),
NVL(pold.attribute2 ,G_NULL_CHAR),
NVL(pold.attribute3 ,G_NULL_CHAR),
NVL(pold.attribute4 ,G_NULL_CHAR),
NVL(pold.attribute5 ,G_NULL_CHAR),
NVL(pold.attribute6 ,G_NULL_CHAR),
NVL(pold.attribute7 ,G_NULL_CHAR),
NVL(pold.attribute8 ,G_NULL_CHAR),
NVL(pold.attribute9 ,G_NULL_CHAR),
NVL(pold.attribute10 ,G_NULL_CHAR),
NVL(pold.attribute11 ,G_NULL_CHAR),
NVL(pold.attribute12 ,G_NULL_CHAR),
NVL(pold.attribute13 ,G_NULL_CHAR),
NVL(pold.attribute14 ,G_NULL_CHAR),
NVL(pold.attribute15 ,G_NULL_CHAR),
NVL(pold.oke_contract_header_id, G_NULL_NUM),
NVL(pold.oke_contract_version_id, G_NULL_NUM),
NVL(pold.clm_payment_instr_code, G_NULL_CHAR),
NVL(pold.clm_pop_exception_reason, G_NULL_CHAR)
) p_lock_attrs,
po_line_mod_f_lock_attrs(
NVL(pold.clm_exercised_flag, G_NULL_CHAR),
NVL(pold.contract_type, G_NULL_CHAR),
NVL(pold.cost_constraint, G_NULL_CHAR),
NVL(pold.unit_meas_lookup_code, G_NULL_CHAR),
NVL(pold.unit_price, G_NULL_NUM),
NVL(pold.clm_exercised_date, G_NULL_DATE),
NVL(pold.from_header_id, G_NULL_NUM),
NVL(pold.from_line_id, G_NULL_NUM),
NVL(pold.clm_idc_type, G_NULL_CHAR)
) f_lock_attrs
FROM po_lines_draft_all pold,
po_session_gt pogt
WHERE pold.draft_id = p_draft_id
AND pold.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
) draft
WHERE base.po_line_id = draft.po_line_id
AND (base.f_lock_attrs <> draft.f_lock_attrs
OR base.p_lock_attrs <> draft.p_lock_attrs
OR base.s_lock_attrs <> draft.s_lock_attrs);
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
draft.po_line_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT poav.po_line_id,
G_DRAFT_ID_MINUS_1 draft_id,
po_attr_val_p_lock_attrs(
NVL(poav.supplier_url ,G_NULL_CHAR),
NVL(poav.manufacturer_url ,G_NULL_CHAR),
NVL(poav.manufacturer_part_num ,G_NULL_CHAR),
NVL(poav.attachment_url ,G_NULL_CHAR),
NVL(poav.lead_time ,G_NULL_NUM),
NVL(poav.unspsc ,G_NULL_CHAR),
NVL(poav.picture ,G_NULL_CHAR),
nvl(poav.thumbnail_image ,G_NULL_CHAR),
NVL(poav.availability ,G_NULL_CHAR)
) p_lock_attrs,
poav.attribute_values_id
FROM po_attribute_values poav,
po_session_gt pogt
WHERE poav.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
) base,
(SELECT poavd.po_line_id,
poavd.draft_id,
po_attr_val_p_lock_attrs(
NVL(poavd.supplier_url ,G_NULL_CHAR),
NVL(poavd.manufacturer_url ,G_NULL_CHAR),
NVL(poavd.manufacturer_part_num ,G_NULL_CHAR),
NVL(poavd.attachment_url ,G_NULL_CHAR),
NVL(poavd.lead_time ,G_NULL_NUM),
NVL(poavd.unspsc ,G_NULL_CHAR),
NVL(poavd.picture ,G_NULL_CHAR),
nvl(poavd.thumbnail_image ,G_NULL_CHAR),
NVL(poavd.availability ,G_NULL_CHAR)
) p_lock_attrs,
poavd.attribute_values_id
FROM po_attribute_values_draft poavd,
po_session_gt pogt
WHERE poavd.draft_id = p_draft_id
AND poavd.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
) draft
WHERE base.attribute_values_id = draft.attribute_values_id
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
draft.po_line_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT poavt.po_line_id,
G_DRAFT_ID_MINUS_1 draft_id,
po_attr_val_tlp_p_lock_attrs(
NVL(poavt.long_description ,G_NULL_CHAR),
NVL(poavt.manufacturer ,G_NULL_CHAR),
NVL(poavt.ALIAS ,G_NULL_CHAR),
NVL(poavt.comments ,G_NULL_CHAR)
) p_lock_attrs,
poavt.attribute_values_tlp_id
FROM po_attribute_values_tlp poavt,
po_session_gt pogt
WHERE poavt.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
) base,
(SELECT poavtd.po_line_id,
poavtd.draft_id,
po_attr_val_tlp_p_lock_attrs(
NVL(poavtd.long_description ,G_NULL_CHAR),
NVL(poavtd.manufacturer ,G_NULL_CHAR),
NVL(poavtd.ALIAS ,G_NULL_CHAR),
NVL(poavtd.comments ,G_NULL_CHAR)
) p_lock_attrs,
poavtd.attribute_values_tlp_id
FROM po_attribute_values_tlp_draft poavtd,
po_session_gt pogt
WHERE poavtd.draft_id = p_draft_id
AND poavtd.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
) draft
WHERE base.attribute_values_tlp_id = draft.attribute_values_tlp_id
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
draft.po_line_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT ppbr.po_line_id,
G_DRAFT_ID_MINUS_1 draft_id,
po_price_brk_mod_p_lock_attrs(
NVL(ppbr.shipment_num ,G_NULL_NUM),
NVL(ppbr.ship_to_organization_id ,G_NULL_NUM),
NVL(ppbr.ship_to_location_id ,G_NULL_NUM),
NVL(ppbr.quantity ,G_NULL_NUM),
NVL(ppbr.price_override ,G_NULL_NUM),
NVL(ppbr.price_discount ,G_NULL_NUM),
NVL(ppbr.start_date ,G_NULL_DATE),
NVL(ppbr.end_date ,G_NULL_DATE)
) p_lock_attrs ,
ppbr.line_location_id
FROM po_line_locations_all ppbr,
po_session_gt pogt
WHERE ppbr.po_line_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
AND ppbr.shipment_type = 'PRICE BREAK'
) base,
(SELECT ppbrd.po_line_id,
ppbrd.draft_id,
po_price_brk_mod_p_lock_attrs(
NVL(ppbrd.shipment_num ,G_NULL_NUM),
NVL(ppbrd.ship_to_organization_id ,G_NULL_NUM),
NVL(ppbrd.ship_to_location_id ,G_NULL_NUM),
NVL(ppbrd.quantity ,G_NULL_NUM),
NVL(ppbrd.price_override ,G_NULL_NUM),
NVL(ppbrd.price_discount ,G_NULL_NUM),
NVL(ppbrd.start_date ,G_NULL_DATE),
NVL(ppbrd.end_date ,G_NULL_DATE)
) p_lock_attrs,
ppbrd.line_location_id
FROM po_line_locations_draft_all ppbrd,
po_session_gt pogt
WHERE ppbrd.draft_id = p_draft_id
AND ppbrd.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
AND ppbrd.shipment_type = 'PRICE BREAK'
) draft
WHERE base.line_location_id = draft.line_location_id
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
draft.entity_id, -- po_lin
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT ppd.entity_id,
g_draft_id_minus_1 draft_id,
po_price_diff_p_lock_attrs(
NVL(ppd.price_differential_num ,G_NULL_NUM),
NVL(ppd.multiplier ,G_NULL_NUM),
NVL(ppd.enabled_flag ,G_NULL_CHAR),
NVL(ppd.min_multiplier ,G_NULL_NUM),
NVL(ppd.max_multiplier ,G_NULL_NUM)
) p_lock_attrs,
ppd.price_differential_id
FROM po_price_differentials ppd,
po_session_gt pogt
WHERE ppd.entity_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
) base,
(SELECT ppdr.entity_id,
ppdr.draft_id,
po_price_diff_p_lock_attrs(
NVL(ppdr.price_differential_num ,G_NULL_NUM),
NVL(ppdr.multiplier ,G_NULL_NUM),
NVL(ppdr.enabled_flag ,G_NULL_CHAR),
NVL(ppdr.min_multiplier ,G_NULL_NUM),
NVL(ppdr.max_multiplier ,G_NULL_NUM)
) p_lock_attrs,
ppdr.price_differential_id
FROM po_price_diff_draft ppdr,
po_session_gt pogt
WHERE ppdr.draft_id = p_draft_id
AND ppdr.entity_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
) draft
WHERE base.price_differential_id = draft.price_differential_id
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
pol.po_line_id,
p_draft_id,
'S'
FROM po_lines_all pol,
po_drafts dft
WHERE EXISTS ( SELECT line_location_id -- new shipment added
FROM po_line_locations_draft_all poll_draft
WHERE poll_draft.po_line_id = pol.po_line_id
AND poll_draft.draft_id = p_draft_id
MINUS
SELECT line_location_id
FROM po_line_locations_all poll_base
WHERE poll_base.po_line_id = pol.po_line_id
)
AND pol.po_header_id = dft.document_id
AND dft.draft_id = p_draft_id;
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
pol.po_line_id,
p_draft_id,
'S'
FROM po_lines_all pol,
po_drafts dft
WHERE EXISTS ( SELECT po_distribution_id -- new distribution added
FROM po_distributions_draft_all pod_draft
WHERE pod_draft.po_line_id = pol.po_line_id
AND pod_draft.draft_id = p_draft_id
MINUS
SELECT po_distribution_id
FROM po_distributions_all pod_base
WHERE pod_base.po_line_id = pol.po_line_id
)
AND pol.po_header_id = dft.document_id
AND dft.draft_id = p_draft_id;
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_SHIPMENT_ENTITY,
draft.line_location_id,
draft.draft_id,
CASE
WHEN base.f_lock_attrs <> draft.f_lock_attrs THEN 'F'
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
WHEN base.s_lock_attrs <> draft.s_lock_attrs THEN 'S'
ELSE NULL
END lock_type
FROM (SELECT poll.line_location_id,
G_DRAFT_ID_MINUS_1 draft_id,
po_line_loc_mod_s_lock_attrs(
NVL(poll.quantity, G_NULL_NUM),
NVL(poll.secondary_quantity, G_NULL_NUM),
NVL(poll.amount, G_NULL_NUM)
) s_lock_attrs,
po_line_loc_mod_p_lock_attrs(
NVL(poll.need_by_date ,G_NULL_DATE),
NVL(poll.promised_date ,G_NULL_DATE),
NVL(poll.clm_period_perf_start_date ,G_NULL_DATE),
NVL(poll.clm_period_perf_end_date ,G_NULL_DATE),
NVL(poll.days_early_receipt_allowed ,G_NULL_NUM),
NVL(poll.days_late_receipt_allowed ,G_NULL_NUM),
NVL(poll.receipt_days_exception_code ,G_NULL_CHAR),
NVL(poll.allow_substitute_receipts_flag ,G_NULL_CHAR),
NVL(poll.receive_close_tolerance ,G_NULL_NUM),
NVL(poll.qty_rcv_tolerance ,G_NULL_NUM),
NVL(poll.qty_rcv_exception_code ,G_NULL_CHAR),
NVL(poll.receiving_routing_id ,G_NULL_NUM),
NVL(poll.enforce_ship_to_location_code ,G_NULL_CHAR),
NVL(poll.note_to_receiver ,G_NULL_CHAR),
NVL(poll.vmi_flag ,G_NULL_CHAR),
NVL(poll.consigned_flag ,G_NULL_CHAR),
NVL(poll.preferred_grade ,G_NULL_CHAR),
NVL(poll.country_of_origin_code ,G_NULL_CHAR),
NVL(poll.encumbered_flag ,G_NULL_CHAR),
NVL(poll.firm_status_lookup_code ,G_NULL_CHAR),
NVL(poll.attribute_category ,G_NULL_CHAR),
NVL(poll.attribute1 ,G_NULL_CHAR),
NVL(poll.attribute2 ,G_NULL_CHAR),
NVL(poll.attribute3 ,G_NULL_CHAR),
NVL(poll.attribute4 ,G_NULL_CHAR),
NVL(poll.attribute5 ,G_NULL_CHAR),
NVL(poll.attribute6 ,G_NULL_CHAR),
NVL(poll.attribute7 ,G_NULL_CHAR),
NVL(poll.attribute8 ,G_NULL_CHAR),
NVL(poll.attribute9 ,G_NULL_CHAR),
NVL(poll.attribute10 ,G_NULL_CHAR),
NVL(poll.attribute11 ,G_NULL_CHAR),
NVL(poll.attribute12 ,G_NULL_CHAR),
NVL(poll.attribute13 ,G_NULL_CHAR),
NVL(poll.attribute14 ,G_NULL_CHAR),
NVL(poll.attribute15 ,G_NULL_CHAR),
NVL(poll.accrue_on_receipt_flag ,G_NULL_CHAR),
NVL(poll.match_option ,G_NULL_CHAR),
NVL(poll.invoice_close_tolerance ,G_NULL_NUM),
NVL(poll.payment_type ,G_NULL_CHAR),
NVL(poll.price_override ,G_NULL_NUM),
NVL(poll.description ,G_NULL_CHAR),
NVL(poll.inspection_required_flag ,G_NULL_CHAR ),--bug-13571320
NVL(poll.receipt_required_flag ,G_NULL_CHAR )--bug-13571320
) p_lock_attrs,
po_line_loc_mod_f_lock_attrs(
NVL(poll.ship_to_location_id ,G_NULL_NUM),
NVL(poll.ship_to_organization_id ,G_NULL_NUM),
NVL(poll.unit_meas_lookup_code, G_NULL_CHAR),
NVL(poll.transaction_flow_header_id ,G_NULL_NUM)
) f_lock_attrs
FROM po_line_locations_all poll,
po_session_gt pogt
WHERE poll.line_location_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_SHIPMENT_ENTITY
AND poll.shipment_type <> 'PRICE BREAK'
) base,
(SELECT polld.line_location_id,
polld.draft_id,
po_line_loc_mod_s_lock_attrs(
NVL(polld.quantity, G_NULL_NUM),
NVL(polld.secondary_quantity, G_NULL_NUM),
NVL(polld.amount, G_NULL_NUM)
) s_lock_attrs,
po_line_loc_mod_p_lock_attrs(
NVL(polld.need_by_date ,G_NULL_DATE),
NVL(polld.promised_date ,G_NULL_DATE),
NVL(polld.clm_period_perf_start_date ,G_NULL_DATE),
NVL(polld.clm_period_perf_end_date ,G_NULL_DATE),
NVL(polld.days_early_receipt_allowed ,G_NULL_NUM),
NVL(polld.days_late_receipt_allowed ,G_NULL_NUM),
NVL(polld.receipt_days_exception_code ,G_NULL_CHAR),
NVL(polld.allow_substitute_receipts_flag ,G_NULL_CHAR),
NVL(polld.receive_close_tolerance ,G_NULL_NUM),
NVL(polld.qty_rcv_tolerance ,G_NULL_NUM),
NVL(polld.qty_rcv_exception_code ,G_NULL_CHAR),
NVL(polld.receiving_routing_id ,G_NULL_NUM),
NVL(polld.enforce_ship_to_location_code ,G_NULL_CHAR),
NVL(polld.note_to_receiver ,G_NULL_CHAR),
NVL(polld.vmi_flag ,G_NULL_CHAR),
NVL(polld.consigned_flag ,G_NULL_CHAR),
NVL(polld.preferred_grade ,G_NULL_CHAR),
NVL(polld.country_of_origin_code ,G_NULL_CHAR),
NVL(polld.encumbered_flag ,G_NULL_CHAR),
NVL(polld.firm_status_lookup_code ,G_NULL_CHAR),
NVL(polld.attribute_category ,G_NULL_CHAR),
NVL(polld.attribute1 ,G_NULL_CHAR),
NVL(polld.attribute2 ,G_NULL_CHAR),
NVL(polld.attribute3 ,G_NULL_CHAR),
NVL(polld.attribute4 ,G_NULL_CHAR),
NVL(polld.attribute5 ,G_NULL_CHAR),
NVL(polld.attribute6 ,G_NULL_CHAR),
NVL(polld.attribute7 ,G_NULL_CHAR),
NVL(polld.attribute8 ,G_NULL_CHAR),
NVL(polld.attribute9 ,G_NULL_CHAR),
NVL(polld.attribute10 ,G_NULL_CHAR),
NVL(polld.attribute11 ,G_NULL_CHAR),
NVL(polld.attribute12 ,G_NULL_CHAR),
NVL(polld.attribute13 ,G_NULL_CHAR),
NVL(polld.attribute14 ,G_NULL_CHAR),
NVL(polld.attribute15 ,G_NULL_CHAR),
NVL(polld.accrue_on_receipt_flag ,G_NULL_CHAR),
NVL(polld.match_option ,G_NULL_CHAR),
NVL(polld.invoice_close_tolerance ,G_NULL_NUM),
NVL(polld.payment_type ,G_NULL_CHAR),
NVL(polld.price_override ,G_NULL_NUM),
NVL(polld.description ,G_NULL_CHAR),
NVL(polld.inspection_required_flag ,G_NULL_CHAR ),-- bug13571320
NVL(polld.receipt_required_flag ,G_NULL_CHAR )-- bug13571320
) p_lock_attrs,
po_line_loc_mod_f_lock_attrs(
NVL(polld.ship_to_location_id ,G_NULL_NUM),
NVL(polld.ship_to_organization_id ,G_NULL_NUM),
NVL(polld.unit_meas_lookup_code, G_NULL_CHAR),
NVL(polld.transaction_flow_header_id ,G_NULL_NUM)
) f_lock_attrs
FROM po_line_locations_draft_all polld,
po_session_gt pogt
WHERE polld.draft_id = p_draft_id
AND polld.line_location_id = pogt.char1 -- line_location_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_SHIPMENT_ENTITY
AND polld.shipment_type <> 'PRICE BREAK'
) draft
WHERE base.line_location_id = draft.line_location_id
AND (base.f_lock_attrs <> draft.f_lock_attrs
OR base.p_lock_attrs <> draft.p_lock_attrs
OR base.s_lock_attrs <> draft.s_lock_attrs);
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_SHIPMENT_ENTITY,
poll.line_location_id,
p_draft_id,
'F'
FROM po_line_locations_all poll,
po_drafts dft
WHERE poll.po_header_id = dft.document_id
AND dft.draft_id = p_draft_id
AND (EXISTS (SELECT 'Line is modified in current txn and requires F lock'
FROM po_session_gt pol_lcks
WHERE pol_lcks.index_char1 = G_LOCKS_REQUIRED_ID
AND pol_lcks.index_char2 = G_LOCK_LINE_ENTITY
AND pol_lcks.char5 = 'F' -- lock_type
AND poll.po_line_id = pol_lcks.char1) -- entity_pk1
OR EXISTS (SELECT 'Line is already F locked and not modified in current txn'
FROM po_entity_locks poel,
po_session_gt poll_ch
WHERE poel.entity_name = G_LOCK_LINE_ENTITY
AND poel.lock_type = 'F'
AND poel.entity_pk1 = poll.po_line_id
AND poll_ch.index_char1 = G_CHANGED_ENTTIES_ID
AND poll_ch.index_char2 = G_LOCK_SHIPMENT_ENTITY -- entity_name
AND poll_ch.char1 = poll.line_location_id -- entity_pk1
AND NOT EXISTS (SELECT 'Line modified in current txn'
FROM po_session_gt pol_ch
WHERE pol_ch.index_char1 = G_CHANGED_ENTTIES_ID
AND pol_ch.index_char2 = G_LOCK_LINE_ENTITY -- entity_name
AND pol_ch.char1 = poel.entity_pk1) -- entity_pk1
)
);
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_DIST_ENTITY,
draft.po_distribution_id,
draft.draft_id,
CASE
WHEN base.f_lock_attrs <> draft.f_lock_attrs THEN 'F'
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
WHEN base.s_lock_attrs <> draft.s_lock_attrs THEN 'S'
ELSE NULL
END lock_type
FROM (SELECT pod.po_distribution_id,
G_DRAFT_ID_MINUS_1 draft_id,
po_dist_mod_s_lock_attrs(
NVL(pod.funded_value ,G_NULL_NUM),
NVL(pod.quantity_ordered ,G_NULL_NUM),
NVL(pod.amount_ordered ,G_NULL_NUM)
) s_lock_attrs,
po_dist_mod_p_lock_attrs(
NVL(pod.clm_defence_funding ,G_NULL_CHAR),
NVL(pod.gl_encumbered_date ,G_NULL_DATE),
NVL(pod.deliver_to_person_id ,G_NULL_NUM),
NVL(pod.deliver_to_location_id ,G_NULL_NUM),
NVL(pod.req_header_reference_num ,G_NULL_NUM),
NVL(pod.req_line_reference_num ,G_NULL_NUM),
NVL(pod.wip_operation_seq_num ,G_NULL_NUM),
NVL(pod.wip_resource_seq_num ,G_NULL_NUM),
NVL(pod.clm_misc_loa ,G_NULL_CHAR),
NVL(pod.clm_fms_case_number ,G_NULL_CHAR),
NVL(pod.clm_agency_acct_identifier ,G_NULL_CHAR),
NVL(pod.dest_charge_account_id ,G_NULL_NUM),
NVL(pod.code_combination_id ,G_NULL_NUM),
NVL(pod.recovery_rate ,G_NULL_NUM),
NVL(pod.project_id ,G_NULL_NUM),
NVL(pod.task_id ,G_NULL_NUM),
NVL(pod.award_id ,G_NULL_NUM),
NVL(pod.expenditure_type ,G_NULL_CHAR),
NVL(pod.expenditure_organization_id ,G_NULL_NUM),
NVL(pod.expenditure_item_date ,G_NULL_DATE),
NVL(pod.end_item_unit_number ,G_NULL_CHAR),
NVL(pod.oke_contract_line_id ,G_NULL_NUM),
NVL(pod.oke_contract_deliverable_id ,G_NULL_NUM),
NVL(pod.attribute_category ,G_NULL_CHAR),
NVL(pod.attribute1 ,G_NULL_CHAR),
NVL(pod.attribute2 ,G_NULL_CHAR),
NVL(pod.attribute3 ,G_NULL_CHAR),
NVL(pod.attribute4 ,G_NULL_CHAR),
NVL(pod.attribute5 ,G_NULL_CHAR),
NVL(pod.attribute6 ,G_NULL_CHAR),
NVL(pod.attribute7 ,G_NULL_CHAR),
NVL(pod.attribute8 ,G_NULL_CHAR),
NVL(pod.attribute9 ,G_NULL_CHAR),
NVL(pod.attribute10 ,G_NULL_CHAR),
NVL(pod.attribute11 ,G_NULL_CHAR),
NVL(pod.attribute12 ,G_NULL_CHAR),
NVL(pod.attribute13 ,G_NULL_CHAR),
NVL(pod.attribute14 ,G_NULL_CHAR),
NVL(pod.attribute15 ,G_NULL_CHAR),
NVL(pod.clm_payment_sequence_num ,G_NULL_NUM)
) p_lock_attrs,
po_dist_mod_f_lock_attrs(
NVL(pod.partial_funded_flag ,G_NULL_CHAR),
NVL(pod.destination_type_code ,G_NULL_CHAR),
NVL(pod.destination_subinventory ,G_NULL_CHAR)
) f_lock_attrs
FROM po_distributions_all pod,
po_session_gt pogt
WHERE pod.po_distribution_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_DIST_ENTITY
) base,
(SELECT podd.po_distribution_id,
podd.draft_id,
po_dist_mod_s_lock_attrs(
NVL(podd.funded_value ,G_NULL_NUM),
NVL(podd.quantity_ordered,G_NULL_NUM),
NVL(podd.amount_ordered ,G_NULL_NUM)
) s_lock_attrs,
po_dist_mod_p_lock_attrs(
NVL(podd.clm_defence_funding ,G_NULL_CHAR),
NVL(podd.gl_encumbered_date ,G_NULL_DATE),
NVL(podd.deliver_to_person_id ,G_NULL_NUM),
NVL(podd.deliver_to_location_id ,G_NULL_NUM),
NVL(podd.req_header_reference_num ,G_NULL_NUM),
NVL(podd.req_line_reference_num ,G_NULL_NUM),
NVL(podd.wip_operation_seq_num ,G_NULL_NUM),
NVL(podd.wip_resource_seq_num ,G_NULL_NUM),
NVL(podd.clm_misc_loa ,G_NULL_CHAR),
NVL(podd.clm_fms_case_number ,G_NULL_CHAR),
NVL(podd.clm_agency_acct_identifier ,G_NULL_CHAR),
NVL(podd.dest_charge_account_id ,G_NULL_NUM),
NVL(podd.code_combination_id ,G_NULL_NUM),
NVL(podd.recovery_rate ,G_NULL_NUM),
NVL(podd.project_id ,G_NULL_NUM),
NVL(podd.task_id ,G_NULL_NUM),
NVL(podd.award_id ,G_NULL_NUM),
NVL(podd.expenditure_type ,G_NULL_CHAR),
NVL(podd.expenditure_organization_id ,G_NULL_NUM),
NVL(podd.expenditure_item_date ,G_NULL_DATE),
NVL(podd.end_item_unit_number ,G_NULL_CHAR),
NVL(podd.oke_contract_line_id ,G_NULL_NUM),
NVL(podd.oke_contract_deliverable_id ,G_NULL_NUM),
NVL(podd.attribute_category ,G_NULL_CHAR),
NVL(podd.attribute1 ,G_NULL_CHAR),
NVL(podd.attribute2 ,G_NULL_CHAR),
NVL(podd.attribute3 ,G_NULL_CHAR),
NVL(podd.attribute4 ,G_NULL_CHAR),
NVL(podd.attribute5 ,G_NULL_CHAR),
NVL(podd.attribute6 ,G_NULL_CHAR),
NVL(podd.attribute7 ,G_NULL_CHAR),
NVL(podd.attribute8 ,G_NULL_CHAR),
NVL(podd.attribute9 ,G_NULL_CHAR),
NVL(podd.attribute10 ,G_NULL_CHAR),
NVL(podd.attribute11 ,G_NULL_CHAR),
NVL(podd.attribute12 ,G_NULL_CHAR),
NVL(podd.attribute13 ,G_NULL_CHAR),
NVL(podd.attribute14 ,G_NULL_CHAR),
NVL(podd.attribute15 ,G_NULL_CHAR),
NVL(podd.clm_payment_sequence_num ,G_NULL_NUM)
) p_lock_attrs,
po_dist_mod_f_lock_attrs(
NVL(podd.partial_funded_flag ,G_NULL_CHAR),
NVL(podd.destination_type_code ,G_NULL_CHAR),
NVL(podd.destination_subinventory ,G_NULL_CHAR)
) f_lock_attrs
FROM po_distributions_draft_all podd,
po_session_gt pogt
WHERE podd.draft_id = p_draft_id
AND podd.po_distribution_id = pogt.char1 -- po_distribution_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_DIST_ENTITY
) draft
WHERE base.po_distribution_id = draft.po_distribution_id
AND (base.f_lock_attrs <> draft.f_lock_attrs
OR base.p_lock_attrs <> draft.p_lock_attrs
OR base.s_lock_attrs <> draft.s_lock_attrs);
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_DIST_ENTITY,
pod.po_distribution_id,
p_draft_id,
'F'
FROM po_distributions_all pod,
po_drafts dft
WHERE pod.po_header_id = dft.document_id
AND dft.draft_id = p_draft_id
AND (EXISTS (SELECT 'Line is modified in current txn and requires F lock'
FROM po_session_gt pol_lcks
WHERE pol_lcks.index_char1 = G_LOCKS_REQUIRED_ID
AND pol_lcks.index_char2 = G_LOCK_LINE_ENTITY
AND pol_lcks.char5 = 'F' -- lock_type
AND pod.po_line_id = pol_lcks.char1) -- entity_pk1
OR EXISTS (SELECT 'Line is already F locked and not modified in current txn'
FROM po_entity_locks poel,
po_session_gt pod_ch
WHERE poel.entity_name = G_LOCK_LINE_ENTITY
AND poel.lock_type = 'F'
AND poel.entity_pk1 = pod.po_line_id
AND pod_ch.index_char1 = G_CHANGED_ENTTIES_ID
AND pod_ch.index_char2 = G_LOCK_DIST_ENTITY -- entity_name
AND pod_ch.char1 = pod.po_distribution_id -- entity_pk1
AND NOT EXISTS (SELECT 'Line modified in current txn'
FROM po_session_gt pol_ch
WHERE pol_ch.index_char1 = G_CHANGED_ENTTIES_ID
AND pol_ch.index_char2 = G_LOCK_LINE_ENTITY -- entity_name
AND pol_ch.char1 = poel.entity_pk1) -- entity_pk1
)
OR EXISTS (SELECT 'shipment is modified in current txn and requires F lock'
FROM po_session_gt poll_lcks
WHERE poll_lcks.index_char1 = G_LOCKS_REQUIRED_ID
AND poll_lcks.index_char2 = G_LOCK_SHIPMENT_ENTITY
AND poll_lcks.char5 = 'F' -- lock_type
AND pod.line_location_id = poll_lcks.char1) -- entity_pk1
OR EXISTS (SELECT 'Shipment is already F locked and not modified in current txn'
FROM po_entity_locks poel,
po_session_gt pod_ch
WHERE poel.entity_name = G_LOCK_SHIPMENT_ENTITY
AND poel.lock_type = 'F'
AND poel.entity_pk1 = pod.line_location_id
AND pod_ch.index_char1 = G_CHANGED_ENTTIES_ID
AND pod_ch.index_char2 = G_LOCK_DIST_ENTITY -- entity_name
AND pod_ch.char1 = pod.po_distribution_id -- entity_pk1
AND NOT EXISTS (SELECT 'Shipment modified in current txn'
FROM po_session_gt poll_ch
WHERE poll_ch.index_char1 = G_CHANGED_ENTTIES_ID
AND poll_ch.index_char2 = G_LOCK_SHIPMENT_ENTITY -- entity_name
AND poll_ch.char1 = poel.entity_pk1) -- entity_pk1
)
);
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_HEADER_ENTITY,
draft.po_header_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT phb.po_header_id,
phb.draft_id,
po_uda_mod_p_lock_attrs(
NVL(phb.c_ext_attr1 ,G_NULL_CHAR),
NVL(phb.c_ext_attr2 ,G_NULL_CHAR),
NVL(phb.c_ext_attr3 ,G_NULL_CHAR),
NVL(phb.c_ext_attr4 ,G_NULL_CHAR),
NVL(phb.c_ext_attr5 ,G_NULL_CHAR),
NVL(phb.c_ext_attr6 ,G_NULL_CHAR),
NVL(phb.c_ext_attr7 ,G_NULL_CHAR),
NVL(phb.c_ext_attr8 ,G_NULL_CHAR),
NVL(phb.c_ext_attr9 ,G_NULL_CHAR),
NVL(phb.c_ext_attr10 ,G_NULL_CHAR),
NVL(phb.c_ext_attr11 ,G_NULL_CHAR),
NVL(phb.c_ext_attr12 ,G_NULL_CHAR),
NVL(phb.c_ext_attr13 ,G_NULL_CHAR),
NVL(phb.c_ext_attr14 ,G_NULL_CHAR),
NVL(phb.c_ext_attr15 ,G_NULL_CHAR),
NVL(phb.c_ext_attr16 ,G_NULL_CHAR),
NVL(phb.c_ext_attr17 ,G_NULL_CHAR),
NVL(phb.c_ext_attr18 ,G_NULL_CHAR),
NVL(phb.c_ext_attr19 ,G_NULL_CHAR),
NVL(phb.c_ext_attr20 ,G_NULL_CHAR),
NVL(phb.c_ext_attr21 ,G_NULL_CHAR),
NVL(phb.c_ext_attr22 ,G_NULL_CHAR),
NVL(phb.c_ext_attr23 ,G_NULL_CHAR),
NVL(phb.c_ext_attr24 ,G_NULL_CHAR),
NVL(phb.c_ext_attr25 ,G_NULL_CHAR),
NVL(phb.c_ext_attr26 ,G_NULL_CHAR),
NVL(phb.c_ext_attr27 ,G_NULL_CHAR),
NVL(phb.c_ext_attr28 ,G_NULL_CHAR),
NVL(phb.c_ext_attr29 ,G_NULL_CHAR),
NVL(phb.c_ext_attr30 ,G_NULL_CHAR),
NVL(phb.c_ext_attr31 ,G_NULL_CHAR),
NVL(phb.c_ext_attr32 ,G_NULL_CHAR),
NVL(phb.c_ext_attr33 ,G_NULL_CHAR),
NVL(phb.c_ext_attr34 ,G_NULL_CHAR),
NVL(phb.c_ext_attr35 ,G_NULL_CHAR),
NVL(phb.c_ext_attr36 ,G_NULL_CHAR),
NVL(phb.c_ext_attr37 ,G_NULL_CHAR),
NVL(phb.c_ext_attr38 ,G_NULL_CHAR),
NVL(phb.c_ext_attr39 ,G_NULL_CHAR),
NVL(phb.c_ext_attr40 ,G_NULL_CHAR),
NVL(phb.n_ext_attr1 ,G_NULL_NUM),
NVL(phb.n_ext_attr2 ,G_NULL_NUM),
NVL(phb.n_ext_attr3 ,G_NULL_NUM),
NVL(phb.n_ext_attr4 ,G_NULL_NUM),
NVL(phb.n_ext_attr5 ,G_NULL_NUM),
NVL(phb.n_ext_attr6 ,G_NULL_NUM),
NVL(phb.n_ext_attr7 ,G_NULL_NUM),
NVL(phb.n_ext_attr8 ,G_NULL_NUM),
NVL(phb.n_ext_attr9 ,G_NULL_NUM),
NVL(phb.n_ext_attr10 ,G_NULL_NUM),
NVL(phb.n_ext_attr11 ,G_NULL_NUM),
NVL(phb.n_ext_attr12 ,G_NULL_NUM),
NVL(phb.n_ext_attr13 ,G_NULL_NUM),
NVL(phb.n_ext_attr14 ,G_NULL_NUM),
NVL(phb.n_ext_attr15 ,G_NULL_NUM),
NVL(phb.n_ext_attr16 ,G_NULL_NUM),
NVL(phb.n_ext_attr17 ,G_NULL_NUM),
NVL(phb.n_ext_attr18 ,G_NULL_NUM),
NVL(phb.n_ext_attr19 ,G_NULL_NUM),
NVL(phb.n_ext_attr20 ,G_NULL_NUM),
NVL(phb.uom_ext_attr1 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr2 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr3 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr4 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr5 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr6 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr7 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr8 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr9 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr10 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr11 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr12 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr13 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr14 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr15 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr16 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr17 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr18 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr19 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr20 ,G_NULL_CHAR),
NVL(phb.d_ext_attr1 ,G_NULL_DATE),
NVL(phb.d_ext_attr2 ,G_NULL_DATE),
NVL(phb.d_ext_attr3 ,G_NULL_DATE),
NVL(phb.d_ext_attr4 ,G_NULL_DATE),
NVL(phb.d_ext_attr5 ,G_NULL_DATE),
NVL(phb.d_ext_attr6 ,G_NULL_DATE),
NVL(phb.d_ext_attr7 ,G_NULL_DATE),
NVL(phb.d_ext_attr8 ,G_NULL_DATE),
NVL(phb.d_ext_attr9 ,G_NULL_DATE),
NVL(phb.d_ext_attr10 ,G_NULL_DATE)
) p_lock_attrs,
phb.attr_group_id
FROM po_headers_all_ext_b phb,
po_session_gt pogt,
ego_fnd_dsc_flx_ctx_ext ag
WHERE phb.po_header_id = pogt.char1 -- po_header_id
AND phb.draft_id = G_DRAFT_ID_MINUS_1
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_HEADER_ENTITY
AND phb.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'N'
) base,
(SELECT phbd.po_header_id,
phbd.draft_id,
po_uda_mod_p_lock_attrs(
NVL(phbd.c_ext_attr1 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr2 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr3 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr4 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr5 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr6 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr7 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr8 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr9 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr10 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr11 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr12 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr13 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr14 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr15 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr16 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr17 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr18 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr19 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr20 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr21 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr22 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr23 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr24 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr25 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr26 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr27 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr28 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr29 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr30 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr31 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr32 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr33 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr34 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr35 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr36 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr37 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr38 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr39 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr40 ,G_NULL_CHAR),
NVL(phbd.n_ext_attr1 ,G_NULL_NUM),
NVL(phbd.n_ext_attr2 ,G_NULL_NUM),
NVL(phbd.n_ext_attr3 ,G_NULL_NUM),
NVL(phbd.n_ext_attr4 ,G_NULL_NUM),
NVL(phbd.n_ext_attr5 ,G_NULL_NUM),
NVL(phbd.n_ext_attr6 ,G_NULL_NUM),
NVL(phbd.n_ext_attr7 ,G_NULL_NUM),
NVL(phbd.n_ext_attr8 ,G_NULL_NUM),
NVL(phbd.n_ext_attr9 ,G_NULL_NUM),
NVL(phbd.n_ext_attr10 ,G_NULL_NUM),
NVL(phbd.n_ext_attr11 ,G_NULL_NUM),
NVL(phbd.n_ext_attr12 ,G_NULL_NUM),
NVL(phbd.n_ext_attr13 ,G_NULL_NUM),
NVL(phbd.n_ext_attr14 ,G_NULL_NUM),
NVL(phbd.n_ext_attr15 ,G_NULL_NUM),
NVL(phbd.n_ext_attr16 ,G_NULL_NUM),
NVL(phbd.n_ext_attr17 ,G_NULL_NUM),
NVL(phbd.n_ext_attr18 ,G_NULL_NUM),
NVL(phbd.n_ext_attr19 ,G_NULL_NUM),
NVL(phbd.n_ext_attr20 ,G_NULL_NUM),
NVL(phbd.uom_ext_attr1 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr2 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr3 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr4 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr5 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr6 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr7 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr8 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr9 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr10 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr11 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr12 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr13 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr14 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr15 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr16 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr17 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr18 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr19 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr20 ,G_NULL_CHAR),
NVL(phbd.d_ext_attr1 ,G_NULL_DATE),
NVL(phbd.d_ext_attr2 ,G_NULL_DATE),
NVL(phbd.d_ext_attr3 ,G_NULL_DATE),
NVL(phbd.d_ext_attr4 ,G_NULL_DATE),
NVL(phbd.d_ext_attr5 ,G_NULL_DATE),
NVL(phbd.d_ext_attr6 ,G_NULL_DATE),
NVL(phbd.d_ext_attr7 ,G_NULL_DATE),
NVL(phbd.d_ext_attr8 ,G_NULL_DATE),
nvl(phbd.d_ext_attr9 ,g_null_date),
NVL(phbd.d_ext_attr10 ,G_NULL_DATE)
) p_lock_attrs,
phbd.attr_group_id
FROM po_headers_all_ext_b phbd,
po_session_gt pogt,
ego_fnd_dsc_flx_ctx_ext ag
WHERE phbd.draft_id = p_draft_id
AND phbd.po_header_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_HEADER_ENTITY
AND phbd.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'N'
) draft
WHERE base.attr_group_id = draft.attr_group_id
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
SELECT document_id
INTO l_po_header_id
FROM po_drafts
WHERE draft_id = p_draft_id;
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_HEADER_ENTITY,
phda.po_header_id,
p_draft_id,
'P'
FROM po_headers_draft_all phda,
po_session_gt pogt
WHERE phda.draft_id= p_draft_id
AND phda.po_header_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_HEADER_ENTITY
AND EXISTS (SELECT draft.attr_group_id
FROM po_headers_all_ext_b draft,
po_uda_ag_template_usages uda_usg,
ego_fnd_dsc_flx_ctx_ext ag
WHERE draft.po_header_id = l_po_header_id
AND draft.draft_id = p_draft_id
AND draft.uda_template_id = uda_usg.template_id
AND draft.attr_group_id = uda_usg.attribute_group_id
AND uda_usg.attribute_category NOT IN ('DOCUMENT_NUMBERING', 'MODIFICATIONS')
AND draft.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'N'
MINUS
SELECT base.attr_group_id
FROM po_headers_all_ext_b base,
po_uda_ag_template_usages uda_usg,
ego_fnd_dsc_flx_ctx_ext ag
WHERE base.po_header_id = l_po_header_id
AND base.draft_id = -1
AND base.uda_template_id = uda_usg.template_id
AND base.attr_group_id = uda_usg.attribute_group_id
AND uda_usg.attribute_category NOT IN ('DOCUMENT_NUMBERING', 'MODIFICATIONS')
AND base.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'N'
);
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_HEADER_ENTITY,
pohd.po_header_id,
pohd.draft_id,
'P'
FROM po_headers_draft_all pohd,
po_session_gt pogt
WHERE pohd.draft_id = p_draft_id
AND pohd.po_header_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_HEADER_ENTITY
AND EXISTS (-- Consider the added/updated records as a change
SELECT c_ext_attr1,
c_ext_attr2,
c_ext_attr3,
c_ext_attr4,
c_ext_attr5,
c_ext_attr6,
c_ext_attr7,
c_ext_attr8,
c_ext_attr9,
c_ext_attr10,
c_ext_attr11,
c_ext_attr12,
c_ext_attr13,
c_ext_attr14,
c_ext_attr15,
c_ext_attr16,
c_ext_attr17,
c_ext_attr18,
c_ext_attr19,
c_ext_attr20,
c_ext_attr21,
c_ext_attr22,
c_ext_attr23,
c_ext_attr24,
c_ext_attr25,
c_ext_attr26,
c_ext_attr27,
c_ext_attr28,
c_ext_attr29,
c_ext_attr30,
c_ext_attr31,
c_ext_attr32,
c_ext_attr33,
c_ext_attr34,
c_ext_attr35,
c_ext_attr36,
c_ext_attr37,
c_ext_attr38,
c_ext_attr39,
c_ext_attr40,
n_ext_attr1,
n_ext_attr2,
n_ext_attr3,
n_ext_attr4,
n_ext_attr5,
n_ext_attr6,
n_ext_attr7,
n_ext_attr8,
n_ext_attr9,
n_ext_attr10,
n_ext_attr11,
n_ext_attr12,
n_ext_attr13,
n_ext_attr14,
n_ext_attr15,
n_ext_attr16,
n_ext_attr17,
n_ext_attr18,
n_ext_attr19,
n_ext_attr20,
uom_ext_attr1,
uom_ext_attr2,
uom_ext_attr3,
uom_ext_attr4,
uom_ext_attr5,
uom_ext_attr6,
uom_ext_attr7,
uom_ext_attr8,
uom_ext_attr9,
uom_ext_attr10,
uom_ext_attr11,
uom_ext_attr12,
uom_ext_attr13,
uom_ext_attr14,
uom_ext_attr15,
uom_ext_attr16,
uom_ext_attr17,
uom_ext_attr18,
uom_ext_attr19,
uom_ext_attr20,
d_ext_attr1,
d_ext_attr2,
d_ext_attr3,
d_ext_attr4,
d_ext_attr5,
d_ext_attr6,
d_ext_attr7,
d_ext_attr8,
d_ext_attr9,
d_ext_attr10
FROM po_headers_all_ext_b pohextb,
ego_fnd_dsc_flx_ctx_ext ag
WHERE pohextb.po_header_id = pohd.po_header_id
AND pohextb.draft_id = p_draft_id
AND pohextb.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'Y'
AND (pohextb.c_ext_attr40 <> 'PO_UDA_ADDRESS_TYPES'
OR pohextb.c_ext_attr40 IS NULL)
MINUS
SELECT c_ext_attr1,
c_ext_attr2,
c_ext_attr3,
c_ext_attr4,
c_ext_attr5,
c_ext_attr6,
c_ext_attr7,
c_ext_attr8,
c_ext_attr9,
c_ext_attr10,
c_ext_attr11,
c_ext_attr12,
c_ext_attr13,
c_ext_attr14,
c_ext_attr15,
c_ext_attr16,
c_ext_attr17,
c_ext_attr18,
c_ext_attr19,
c_ext_attr20,
c_ext_attr21,
c_ext_attr22,
c_ext_attr23,
c_ext_attr24,
c_ext_attr25,
c_ext_attr26,
c_ext_attr27,
c_ext_attr28,
c_ext_attr29,
c_ext_attr30,
c_ext_attr31,
c_ext_attr32,
c_ext_attr33,
c_ext_attr34,
c_ext_attr35,
c_ext_attr36,
c_ext_attr37,
c_ext_attr38,
c_ext_attr39,
c_ext_attr40,
n_ext_attr1,
n_ext_attr2,
n_ext_attr3,
n_ext_attr4,
n_ext_attr5,
n_ext_attr6,
n_ext_attr7,
n_ext_attr8,
n_ext_attr9,
n_ext_attr10,
n_ext_attr11,
n_ext_attr12,
n_ext_attr13,
n_ext_attr14,
n_ext_attr15,
n_ext_attr16,
n_ext_attr17,
n_ext_attr18,
n_ext_attr19,
n_ext_attr20,
uom_ext_attr1,
uom_ext_attr2,
uom_ext_attr3,
uom_ext_attr4,
uom_ext_attr5,
uom_ext_attr6,
uom_ext_attr7,
uom_ext_attr8,
uom_ext_attr9,
uom_ext_attr10,
uom_ext_attr11,
uom_ext_attr12,
uom_ext_attr13,
uom_ext_attr14,
uom_ext_attr15,
uom_ext_attr16,
uom_ext_attr17,
uom_ext_attr18,
uom_ext_attr19,
uom_ext_attr20,
d_ext_attr1,
d_ext_attr2,
d_ext_attr3,
d_ext_attr4,
d_ext_attr5,
d_ext_attr6,
d_ext_attr7,
d_ext_attr8,
d_ext_attr9,
d_ext_attr10
FROM po_headers_all_ext_b pohextb,
ego_fnd_dsc_flx_ctx_ext ag
WHERE pohextb.po_header_id = pohd.po_header_id
AND pohextb.draft_id = G_DRAFT_ID_MINUS_1
AND pohextb.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'Y'
AND (pohextb.c_ext_attr40 <> 'PO_UDA_ADDRESS_TYPES'
OR pohextb.c_ext_attr40 IS NULL)
UNION ALL
-- Consider the deleted records as a change
SELECT c_ext_attr1,
c_ext_attr2,
c_ext_attr3,
c_ext_attr4,
c_ext_attr5,
c_ext_attr6,
c_ext_attr7,
c_ext_attr8,
c_ext_attr9,
c_ext_attr10,
c_ext_attr11,
c_ext_attr12,
c_ext_attr13,
c_ext_attr14,
c_ext_attr15,
c_ext_attr16,
c_ext_attr17,
c_ext_attr18,
c_ext_attr19,
c_ext_attr20,
c_ext_attr21,
c_ext_attr22,
c_ext_attr23,
c_ext_attr24,
c_ext_attr25,
c_ext_attr26,
c_ext_attr27,
c_ext_attr28,
c_ext_attr29,
c_ext_attr30,
c_ext_attr31,
c_ext_attr32,
c_ext_attr33,
c_ext_attr34,
c_ext_attr35,
c_ext_attr36,
c_ext_attr37,
c_ext_attr38,
c_ext_attr39,
c_ext_attr40,
n_ext_attr1,
n_ext_attr2,
n_ext_attr3,
n_ext_attr4,
n_ext_attr5,
n_ext_attr6,
n_ext_attr7,
n_ext_attr8,
n_ext_attr9,
n_ext_attr10,
n_ext_attr11,
n_ext_attr12,
n_ext_attr13,
n_ext_attr14,
n_ext_attr15,
n_ext_attr16,
n_ext_attr17,
n_ext_attr18,
n_ext_attr19,
n_ext_attr20,
uom_ext_attr1,
uom_ext_attr2,
uom_ext_attr3,
uom_ext_attr4,
uom_ext_attr5,
uom_ext_attr6,
uom_ext_attr7,
uom_ext_attr8,
uom_ext_attr9,
uom_ext_attr10,
uom_ext_attr11,
uom_ext_attr12,
uom_ext_attr13,
uom_ext_attr14,
uom_ext_attr15,
uom_ext_attr16,
uom_ext_attr17,
uom_ext_attr18,
uom_ext_attr19,
uom_ext_attr20,
d_ext_attr1,
d_ext_attr2,
d_ext_attr3,
d_ext_attr4,
d_ext_attr5,
d_ext_attr6,
d_ext_attr7,
d_ext_attr8,
d_ext_attr9,
d_ext_attr10
FROM po_headers_all_ext_b pohextb,
ego_fnd_dsc_flx_ctx_ext ag
WHERE pohextb.po_header_id = pohd.po_header_id
AND pohextb.draft_id = G_DRAFT_ID_MINUS_1
AND pohextb.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'Y'
AND (pohextb.c_ext_attr40 <> 'PO_UDA_ADDRESS_TYPES'
OR pohextb.c_ext_attr40 IS NULL)
MINUS
SELECT c_ext_attr1,
c_ext_attr2,
c_ext_attr3,
c_ext_attr4,
c_ext_attr5,
c_ext_attr6,
c_ext_attr7,
c_ext_attr8,
c_ext_attr9,
c_ext_attr10,
c_ext_attr11,
c_ext_attr12,
c_ext_attr13,
c_ext_attr14,
c_ext_attr15,
c_ext_attr16,
c_ext_attr17,
c_ext_attr18,
c_ext_attr19,
c_ext_attr20,
c_ext_attr21,
c_ext_attr22,
c_ext_attr23,
c_ext_attr24,
c_ext_attr25,
c_ext_attr26,
c_ext_attr27,
c_ext_attr28,
c_ext_attr29,
c_ext_attr30,
c_ext_attr31,
c_ext_attr32,
c_ext_attr33,
c_ext_attr34,
c_ext_attr35,
c_ext_attr36,
c_ext_attr37,
c_ext_attr38,
c_ext_attr39,
c_ext_attr40,
n_ext_attr1,
n_ext_attr2,
n_ext_attr3,
n_ext_attr4,
n_ext_attr5,
n_ext_attr6,
n_ext_attr7,
n_ext_attr8,
n_ext_attr9,
n_ext_attr10,
n_ext_attr11,
n_ext_attr12,
n_ext_attr13,
n_ext_attr14,
n_ext_attr15,
n_ext_attr16,
n_ext_attr17,
n_ext_attr18,
n_ext_attr19,
n_ext_attr20,
uom_ext_attr1,
uom_ext_attr2,
uom_ext_attr3,
uom_ext_attr4,
uom_ext_attr5,
uom_ext_attr6,
uom_ext_attr7,
uom_ext_attr8,
uom_ext_attr9,
uom_ext_attr10,
uom_ext_attr11,
uom_ext_attr12,
uom_ext_attr13,
uom_ext_attr14,
uom_ext_attr15,
uom_ext_attr16,
uom_ext_attr17,
uom_ext_attr18,
uom_ext_attr19,
uom_ext_attr20,
d_ext_attr1,
d_ext_attr2,
d_ext_attr3,
d_ext_attr4,
d_ext_attr5,
d_ext_attr6,
d_ext_attr7,
d_ext_attr8,
d_ext_attr9,
d_ext_attr10
FROM po_headers_all_ext_b pohextb,
ego_fnd_dsc_flx_ctx_ext ag
WHERE pohextb.po_header_id = pohd.po_header_id
AND pohextb.draft_id = p_draft_id
AND pohextb.attr_group_id = ag.attr_group_id
AND ag.multi_row = 'Y'
AND (pohextb.c_ext_attr40 <> 'PO_UDA_ADDRESS_TYPES'
OR pohextb.c_ext_attr40 IS NULL)
);
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_HEADER_ENTITY,
draft.po_header_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
end lock_type
FROM (SELECT put.po_header_id,
put.draft_id,
po_uda_tl_mod_p_lock_attrs(
NVL(put.tl_ext_attr1 ,G_NULL_CHAR),
NVL(put.tl_ext_attr2 ,G_NULL_CHAR),
NVL(put.tl_ext_attr3 ,G_NULL_CHAR),
NVL(put.tl_ext_attr4 ,G_NULL_CHAR),
NVL(put.tl_ext_attr5 ,G_NULL_CHAR),
NVL(put.tl_ext_attr6 ,G_NULL_CHAR),
NVL(put.tl_ext_attr7 ,G_NULL_CHAR),
NVL(put.tl_ext_attr8 ,G_NULL_CHAR),
NVL(put.tl_ext_attr9 ,G_NULL_CHAR),
NVL(put.tl_ext_attr10 ,G_NULL_CHAR),
NVL(put.tl_ext_attr11 ,G_NULL_CHAR),
NVL(put.tl_ext_attr12 ,G_NULL_CHAR),
NVL(put.tl_ext_attr13 ,G_NULL_CHAR),
NVL(put.tl_ext_attr14 ,G_NULL_CHAR),
NVL(put.tl_ext_attr15 ,G_NULL_CHAR),
NVL(put.tl_ext_attr16 ,G_NULL_CHAR),
NVL(put.tl_ext_attr17 ,G_NULL_CHAR),
NVL(put.tl_ext_attr18 ,G_NULL_CHAR),
NVL(put.tl_ext_attr19 ,G_NULL_CHAR),
NVL(put.tl_ext_attr20 ,G_NULL_CHAR),
NVL(put.tl_ext_attr21 ,G_NULL_CHAR),
NVL(put.tl_ext_attr22 ,G_NULL_CHAR),
NVL(put.tl_ext_attr23 ,G_NULL_CHAR),
NVL(put.tl_ext_attr24 ,G_NULL_CHAR),
NVL(put.tl_ext_attr25 ,G_NULL_CHAR),
NVL(put.tl_ext_attr26 ,G_NULL_CHAR),
NVL(put.tl_ext_attr27 ,G_NULL_CHAR),
NVL(put.tl_ext_attr28 ,G_NULL_CHAR),
NVL(put.tl_ext_attr29 ,G_NULL_CHAR),
NVL(put.tl_ext_attr30 ,G_NULL_CHAR),
NVL(put.tl_ext_attr31 ,G_NULL_CHAR),
NVL(put.tl_ext_attr32 ,G_NULL_CHAR),
NVL(put.tl_ext_attr33 ,G_NULL_CHAR),
NVL(put.tl_ext_attr34 ,G_NULL_CHAR),
NVL(put.tl_ext_attr35 ,G_NULL_CHAR),
NVL(put.tl_ext_attr36 ,G_NULL_CHAR),
NVL(put.tl_ext_attr37 ,G_NULL_CHAR),
NVL(put.tl_ext_attr38 ,G_NULL_CHAR),
nvl(put.tl_ext_attr39 ,G_NULL_CHAR),
NVL(put.tl_ext_attr40 ,G_NULL_CHAR)
) p_lock_attrs,
put.attr_group_id,
put.language
FROM po_headers_all_ext_tl put,
po_session_gt pogt
where put.po_header_id = pogt.char1 -- po_header_id
AND put.draft_id =G_DRAFT_ID_MINUS_1
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
and pogt.index_char2 = g_lock_header_entity
) base,
(select putd.po_header_id,
putd.draft_id,
po_uda_tl_mod_p_lock_attrs(
NVL(putd.tl_ext_attr1 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr2 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr3 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr4 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr5 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr6 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr7 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr8 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr9 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr10 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr11 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr12 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr13 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr14 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr15 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr16 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr17 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr18 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr19 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr20 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr21 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr22 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr23 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr24 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr25 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr26 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr27 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr28 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr29 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr30 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr31 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr32 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr33 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr34 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr35 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr36 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr37 ,G_NULL_CHAR),
NVL(putd.tl_ext_attr38 ,G_NULL_CHAR),
nvl(putd.tl_ext_attr39 ,g_null_char),
NVL(putd.tl_ext_attr40 ,G_NULL_CHAR)
) p_lock_attrs,
putd.attr_group_id,
putd.language
FROM po_headers_all_ext_tl putd,
po_session_gt pogt
WHERE putd.draft_id = p_draft_id
AND putd.po_header_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
and pogt.index_char2 = g_lock_header_entity
) draft
where base.attr_group_id = draft.attr_group_id
AND base.language = draft.language
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
char2, -- entity_pk2
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_ADDRESS_ENTITY,
draft.po_header_id,
draft.c_ext_attr39,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT phb.po_header_id,
phb.draft_id,
po_uda_mod_p_lock_attrs(
NVL(phb.c_ext_attr1 ,G_NULL_CHAR),
NVL(phb.c_ext_attr2 ,G_NULL_CHAR),
NVL(phb.c_ext_attr3 ,G_NULL_CHAR),
NVL(phb.c_ext_attr4 ,G_NULL_CHAR),
NVL(phb.c_ext_attr5 ,G_NULL_CHAR),
NVL(phb.c_ext_attr6 ,G_NULL_CHAR),
NVL(phb.c_ext_attr7 ,G_NULL_CHAR),
NVL(phb.c_ext_attr8 ,G_NULL_CHAR),
NVL(phb.c_ext_attr9 ,G_NULL_CHAR),
NVL(phb.c_ext_attr10 ,G_NULL_CHAR),
NVL(phb.c_ext_attr11 ,G_NULL_CHAR),
NVL(phb.c_ext_attr12 ,G_NULL_CHAR),
NVL(phb.c_ext_attr13 ,G_NULL_CHAR),
NVL(phb.c_ext_attr14 ,G_NULL_CHAR),
NVL(phb.c_ext_attr15 ,G_NULL_CHAR),
NVL(phb.c_ext_attr16 ,G_NULL_CHAR),
NVL(phb.c_ext_attr17 ,G_NULL_CHAR),
NVL(phb.c_ext_attr18 ,G_NULL_CHAR),
NVL(phb.c_ext_attr19 ,G_NULL_CHAR),
NVL(phb.c_ext_attr20 ,G_NULL_CHAR),
NVL(phb.c_ext_attr21 ,G_NULL_CHAR),
NVL(phb.c_ext_attr22 ,G_NULL_CHAR),
NVL(phb.c_ext_attr23 ,G_NULL_CHAR),
NVL(phb.c_ext_attr24 ,G_NULL_CHAR),
NVL(phb.c_ext_attr25 ,G_NULL_CHAR),
NVL(phb.c_ext_attr26 ,G_NULL_CHAR),
NVL(phb.c_ext_attr27 ,G_NULL_CHAR),
NVL(phb.c_ext_attr28 ,G_NULL_CHAR),
NVL(phb.c_ext_attr29 ,G_NULL_CHAR),
NVL(phb.c_ext_attr30 ,G_NULL_CHAR),
NVL(phb.c_ext_attr31 ,G_NULL_CHAR),
NVL(phb.c_ext_attr32 ,G_NULL_CHAR),
NVL(phb.c_ext_attr33 ,G_NULL_CHAR),
NVL(phb.c_ext_attr34 ,G_NULL_CHAR),
NVL(phb.c_ext_attr35 ,G_NULL_CHAR),
NVL(phb.c_ext_attr36 ,G_NULL_CHAR),
NVL(phb.c_ext_attr37 ,G_NULL_CHAR),
NVL(phb.c_ext_attr38 ,G_NULL_CHAR),
NVL(phb.c_ext_attr39 ,G_NULL_CHAR),
NVL(phb.c_ext_attr40 ,G_NULL_CHAR),
NVL(phb.n_ext_attr1 ,G_NULL_NUM),
NVL(phb.n_ext_attr2 ,G_NULL_NUM),
NVL(phb.n_ext_attr3 ,G_NULL_NUM),
NVL(phb.n_ext_attr4 ,G_NULL_NUM),
NVL(phb.n_ext_attr5 ,G_NULL_NUM),
NVL(phb.n_ext_attr6 ,G_NULL_NUM),
NVL(phb.n_ext_attr7 ,G_NULL_NUM),
NVL(phb.n_ext_attr8 ,G_NULL_NUM),
NVL(phb.n_ext_attr9 ,G_NULL_NUM),
NVL(phb.n_ext_attr10 ,G_NULL_NUM),
NVL(phb.n_ext_attr11 ,G_NULL_NUM),
NVL(phb.n_ext_attr12 ,G_NULL_NUM),
NVL(phb.n_ext_attr13 ,G_NULL_NUM),
NVL(phb.n_ext_attr14 ,G_NULL_NUM),
NVL(phb.n_ext_attr15 ,G_NULL_NUM),
NVL(phb.n_ext_attr16 ,G_NULL_NUM),
NVL(phb.n_ext_attr17 ,G_NULL_NUM),
NVL(phb.n_ext_attr18 ,G_NULL_NUM),
NVL(phb.n_ext_attr19 ,G_NULL_NUM),
NVL(phb.n_ext_attr20 ,G_NULL_NUM),
NVL(phb.uom_ext_attr1 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr2 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr3 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr4 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr5 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr6 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr7 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr8 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr9 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr10 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr11 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr12 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr13 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr14 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr15 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr16 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr17 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr18 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr19 ,G_NULL_CHAR),
NVL(phb.uom_ext_attr20 ,G_NULL_CHAR),
NVL(phb.d_ext_attr1 ,G_NULL_DATE),
NVL(phb.d_ext_attr2 ,G_NULL_DATE),
NVL(phb.d_ext_attr3 ,G_NULL_DATE),
NVL(phb.d_ext_attr4 ,G_NULL_DATE),
NVL(phb.d_ext_attr5 ,G_NULL_DATE),
NVL(phb.d_ext_attr6 ,G_NULL_DATE),
NVL(phb.d_ext_attr7 ,G_NULL_DATE),
NVL(phb.d_ext_attr8 ,G_NULL_DATE),
NVL(phb.d_ext_attr9 ,G_NULL_DATE),
NVL(phb.d_ext_attr10 ,G_NULL_DATE)
) P_LOCK_ATTRS,
phb.attr_group_id,
phb.c_ext_attr39
FROM po_headers_all_ext_b phb,
po_session_gt pogt
WHERE phb.po_header_id = pogt.char1 -- po_header_id
AND phb.draft_id = G_DRAFT_ID_MINUS_1
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_ADDRESS_ENTITY
AND phb.c_ext_attr40='PO_UDA_ADDRESS_TYPES'
) base,
(SELECT phbd.po_header_id,
phbd.draft_id,
po_uda_mod_p_lock_attrs(
NVL(phbd.c_ext_attr1 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr2 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr3 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr4 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr5 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr6 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr7 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr8 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr9 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr10 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr11 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr12 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr13 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr14 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr15 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr16 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr17 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr18 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr19 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr20 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr21 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr22 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr23 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr24 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr25 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr26 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr27 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr28 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr29 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr30 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr31 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr32 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr33 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr34 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr35 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr36 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr37 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr38 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr39 ,G_NULL_CHAR),
NVL(phbd.c_ext_attr40 ,G_NULL_CHAR),
NVL(phbd.n_ext_attr1 ,G_NULL_NUM),
NVL(phbd.n_ext_attr2 ,G_NULL_NUM),
NVL(phbd.n_ext_attr3 ,G_NULL_NUM),
NVL(phbd.n_ext_attr4 ,G_NULL_NUM),
NVL(phbd.n_ext_attr5 ,G_NULL_NUM),
NVL(phbd.n_ext_attr6 ,G_NULL_NUM),
NVL(phbd.n_ext_attr7 ,G_NULL_NUM),
NVL(phbd.n_ext_attr8 ,G_NULL_NUM),
NVL(phbd.n_ext_attr9 ,G_NULL_NUM),
NVL(phbd.n_ext_attr10 ,G_NULL_NUM),
NVL(phbd.n_ext_attr11 ,G_NULL_NUM),
NVL(phbd.n_ext_attr12 ,G_NULL_NUM),
NVL(phbd.n_ext_attr13 ,G_NULL_NUM),
NVL(phbd.n_ext_attr14 ,G_NULL_NUM),
NVL(phbd.n_ext_attr15 ,G_NULL_NUM),
NVL(phbd.n_ext_attr16 ,G_NULL_NUM),
NVL(phbd.n_ext_attr17 ,G_NULL_NUM),
NVL(phbd.n_ext_attr18 ,G_NULL_NUM),
NVL(phbd.n_ext_attr19 ,G_NULL_NUM),
NVL(phbd.n_ext_attr20 ,G_NULL_NUM),
NVL(phbd.uom_ext_attr1 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr2 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr3 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr4 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr5 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr6 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr7 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr8 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr9 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr10 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr11 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr12 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr13 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr14 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr15 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr16 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr17 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr18 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr19 ,G_NULL_CHAR),
NVL(phbd.uom_ext_attr20 ,G_NULL_CHAR),
NVL(phbd.d_ext_attr1 ,G_NULL_DATE),
NVL(phbd.d_ext_attr2 ,G_NULL_DATE),
NVL(phbd.d_ext_attr3 ,G_NULL_DATE),
NVL(phbd.d_ext_attr4 ,G_NULL_DATE),
NVL(phbd.d_ext_attr5 ,G_NULL_DATE),
NVL(phbd.d_ext_attr6 ,G_NULL_DATE),
NVL(phbd.d_ext_attr7 ,G_NULL_DATE),
NVL(phbd.d_ext_attr8 ,G_NULL_DATE),
nvl(phbd.d_ext_attr9 ,g_null_date),
NVL(phbd.d_ext_attr10 ,G_NULL_DATE)
) p_lock_attrs,
phbd.attr_group_id,
phbd.c_ext_attr39
FROM po_headers_all_ext_b phbd,
po_session_gt pogt
WHERE phbd.draft_id = p_draft_id
AND phbd.po_header_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_ADDRESS_ENTITY
AND phbd.c_ext_attr40 = 'PO_UDA_ADDRESS_TYPES'
) draft
WHERE base.attr_group_id = draft.attr_group_id
AND BASE.c_ext_attr39 = DRAFT.c_ext_attr39
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Updated ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
draft.po_line_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT plb.po_line_id,
plb.draft_id,
po_uda_mod_p_lock_attrs(
NVL(plb.c_ext_attr1 ,G_NULL_CHAR),
NVL(plb.c_ext_attr2 ,G_NULL_CHAR),
NVL(plb.c_ext_attr3 ,G_NULL_CHAR),
NVL(plb.c_ext_attr4 ,G_NULL_CHAR),
NVL(plb.c_ext_attr5 ,G_NULL_CHAR),
NVL(plb.c_ext_attr6 ,G_NULL_CHAR),
NVL(plb.c_ext_attr7 ,G_NULL_CHAR),
NVL(plb.c_ext_attr8 ,G_NULL_CHAR),
NVL(plb.c_ext_attr9 ,G_NULL_CHAR),
NVL(plb.c_ext_attr10 ,G_NULL_CHAR),
NVL(plb.c_ext_attr11 ,G_NULL_CHAR),
NVL(plb.c_ext_attr12 ,G_NULL_CHAR),
NVL(plb.c_ext_attr13 ,G_NULL_CHAR),
NVL(plb.c_ext_attr14 ,G_NULL_CHAR),
NVL(plb.c_ext_attr15 ,G_NULL_CHAR),
NVL(plb.c_ext_attr16 ,G_NULL_CHAR),
NVL(plb.c_ext_attr17 ,G_NULL_CHAR),
NVL(plb.c_ext_attr18 ,G_NULL_CHAR),
NVL(plb.c_ext_attr19 ,G_NULL_CHAR),
NVL(plb.c_ext_attr20 ,G_NULL_CHAR),
NVL(plb.c_ext_attr21 ,G_NULL_CHAR),
NVL(plb.c_ext_attr22 ,G_NULL_CHAR),
NVL(plb.c_ext_attr23 ,G_NULL_CHAR),
NVL(plb.c_ext_attr24 ,G_NULL_CHAR),
NVL(plb.c_ext_attr25 ,G_NULL_CHAR),
NVL(plb.c_ext_attr26 ,G_NULL_CHAR),
NVL(plb.c_ext_attr27 ,G_NULL_CHAR),
NVL(plb.c_ext_attr28 ,G_NULL_CHAR),
NVL(plb.c_ext_attr29 ,G_NULL_CHAR),
NVL(plb.c_ext_attr30 ,G_NULL_CHAR),
NVL(plb.c_ext_attr31 ,G_NULL_CHAR),
NVL(plb.c_ext_attr32 ,G_NULL_CHAR),
NVL(plb.c_ext_attr33 ,G_NULL_CHAR),
NVL(plb.c_ext_attr34 ,G_NULL_CHAR),
NVL(plb.c_ext_attr35 ,G_NULL_CHAR),
NVL(plb.c_ext_attr36 ,G_NULL_CHAR),
NVL(plb.c_ext_attr37 ,G_NULL_CHAR),
NVL(plb.c_ext_attr38 ,G_NULL_CHAR),
NVL(plb.c_ext_attr39 ,G_NULL_CHAR),
NVL(plb.c_ext_attr40 ,G_NULL_CHAR),
NVL(plb.n_ext_attr1 ,G_NULL_NUM),
NVL(plb.n_ext_attr2 ,G_NULL_NUM),
NVL(plb.n_ext_attr3 ,G_NULL_NUM),
NVL(plb.n_ext_attr4 ,G_NULL_NUM),
NVL(plb.n_ext_attr5 ,G_NULL_NUM),
NVL(plb.n_ext_attr6 ,G_NULL_NUM),
NVL(plb.n_ext_attr7 ,G_NULL_NUM),
NVL(plb.n_ext_attr8 ,G_NULL_NUM),
NVL(plb.n_ext_attr9 ,G_NULL_NUM),
NVL(plb.n_ext_attr10 ,G_NULL_NUM),
NVL(plb.n_ext_attr11 ,G_NULL_NUM),
NVL(plb.n_ext_attr12 ,G_NULL_NUM),
NVL(plb.n_ext_attr13 ,G_NULL_NUM),
NVL(plb.n_ext_attr14 ,G_NULL_NUM),
NVL(plb.n_ext_attr15 ,G_NULL_NUM),
NVL(plb.n_ext_attr16 ,G_NULL_NUM),
NVL(plb.n_ext_attr17 ,G_NULL_NUM),
NVL(plb.n_ext_attr18 ,G_NULL_NUM),
NVL(plb.n_ext_attr19 ,G_NULL_NUM),
NVL(plb.n_ext_attr20 ,G_NULL_NUM),
NVL(plb.uom_ext_attr1 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr2 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr3 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr4 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr5 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr6 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr7 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr8 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr9 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr10 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr11 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr12 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr13 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr14 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr15 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr16 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr17 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr18 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr19 ,G_NULL_CHAR),
NVL(plb.uom_ext_attr20 ,G_NULL_CHAR),
NVL(plb.d_ext_attr1 ,G_NULL_DATE),
NVL(plb.d_ext_attr2 ,G_NULL_DATE),
NVL(plb.d_ext_attr3 ,G_NULL_DATE),
NVL(plb.d_ext_attr4 ,G_NULL_DATE),
NVL(plb.d_ext_attr5 ,G_NULL_DATE),
NVL(plb.d_ext_attr6 ,G_NULL_DATE),
NVL(plb.d_ext_attr7 ,G_NULL_DATE),
NVL(plb.d_ext_attr8 ,G_NULL_DATE),
NVL(plb.d_ext_attr9 ,G_NULL_DATE),
NVL(plb.d_ext_attr10 ,G_NULL_DATE)
) p_lock_attrs,
plb.attr_group_id
FROM po_lines_all_ext_b plb,
po_session_gt pogt
WHERE plb.po_line_id = pogt.char1 -- po_line_id
AND plb.draft_id = G_DRAFT_ID_MINUS_1
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
and pogt.index_char2 = g_lock_line_entity
AND NOT EXISTS (SELECT 'exclude PRICING attribute category'
FROM po_uda_ag_template_usages puatu,
po_lines_all pla
WHERE puatu.template_id = pla.uda_template_id
AND puatu.attribute_category = 'PRICING'
AND pla.po_line_id = plb.po_line_id
AND puatu.attribute_group_id = plb.attr_group_id
)
) base,
(SELECT plbd.po_line_id,
plbd.draft_id,
po_uda_mod_p_lock_attrs(
NVL(plbd.c_ext_attr1 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr2 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr3 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr4 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr5 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr6 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr7 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr8 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr9 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr10 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr11 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr12 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr13 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr14 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr15 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr16 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr17 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr18 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr19 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr20 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr21 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr22 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr23 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr24 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr25 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr26 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr27 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr28 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr29 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr30 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr31 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr32 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr33 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr34 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr35 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr36 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr37 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr38 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr39 ,G_NULL_CHAR),
NVL(plbd.c_ext_attr40 ,G_NULL_CHAR),
NVL(plbd.n_ext_attr1 ,G_NULL_NUM),
NVL(plbd.n_ext_attr2 ,G_NULL_NUM),
NVL(plbd.n_ext_attr3 ,G_NULL_NUM),
NVL(plbd.n_ext_attr4 ,G_NULL_NUM),
NVL(plbd.n_ext_attr5 ,G_NULL_NUM),
NVL(plbd.n_ext_attr6 ,G_NULL_NUM),
NVL(plbd.n_ext_attr7 ,G_NULL_NUM),
NVL(plbd.n_ext_attr8 ,G_NULL_NUM),
NVL(plbd.n_ext_attr9 ,G_NULL_NUM),
NVL(plbd.n_ext_attr10 ,G_NULL_NUM),
NVL(plbd.n_ext_attr11 ,G_NULL_NUM),
NVL(plbd.n_ext_attr12 ,G_NULL_NUM),
NVL(plbd.n_ext_attr13 ,G_NULL_NUM),
NVL(plbd.n_ext_attr14 ,G_NULL_NUM),
NVL(plbd.n_ext_attr15 ,G_NULL_NUM),
NVL(plbd.n_ext_attr16 ,G_NULL_NUM),
NVL(plbd.n_ext_attr17 ,G_NULL_NUM),
NVL(plbd.n_ext_attr18 ,G_NULL_NUM),
NVL(plbd.n_ext_attr19 ,G_NULL_NUM),
NVL(plbd.n_ext_attr20 ,G_NULL_NUM),
NVL(plbd.uom_ext_attr1 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr2 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr3 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr4 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr5 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr6 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr7 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr8 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr9 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr10 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr11 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr12 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr13 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr14 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr15 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr16 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr17 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr18 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr19 ,G_NULL_CHAR),
NVL(plbd.uom_ext_attr20 ,G_NULL_CHAR),
NVL(plbd.d_ext_attr1 ,G_NULL_DATE),
NVL(plbd.d_ext_attr2 ,G_NULL_DATE),
NVL(plbd.d_ext_attr3 ,G_NULL_DATE),
NVL(plbd.d_ext_attr4 ,G_NULL_DATE),
NVL(plbd.d_ext_attr5 ,G_NULL_DATE),
NVL(plbd.d_ext_attr6 ,G_NULL_DATE),
NVL(plbd.d_ext_attr7 ,G_NULL_DATE),
NVL(plbd.d_ext_attr8 ,G_NULL_DATE),
NVL(plbd.d_ext_attr9 ,G_NULL_DATE),
NVL(plbd.d_ext_attr10 ,G_NULL_DATE)
) p_lock_attrs,
plbd.attr_group_id
FROM po_lines_all_ext_b plbd,
po_session_gt pogt
WHERE plbd.draft_id = p_draft_id
AND plbd.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
AND NOT EXISTS (SELECT 'exclude PRICING attribute category'
FROM po_uda_ag_template_usages puatu,
po_lines_all pla
where puatu.template_id = pla.uda_template_id
AND puatu.attribute_category = 'PRICING'
AND pla.po_line_id = plbd.po_line_id
AND puatu.attribute_group_id = plbd.attr_group_id
)
) draft
WHERE base.attr_group_id = draft.attr_group_id
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows INserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
plbd.po_line_id,
p_draft_id,
'P'
FROM po_lines_draft_all plbd,
po_session_gt pogt
WHERE plbd.draft_id = p_draft_id
AND plbd.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_LINE_ENTITY
AND EXISTS (SELECT draft.attr_group_id
FROM po_lines_all_ext_b draft,
po_uda_ag_template_usages uda_usg
WHERE draft.po_line_id = plbd.po_line_id
AND draft.draft_id = p_draft_id
AND draft.uda_template_id = uda_usg.template_id
AND draft.attr_group_id = uda_usg.attribute_group_id
AND uda_usg.attribute_category <> 'PRICING'
MINUS
SELECT base.attr_group_id
FROM po_lines_all_ext_b base,
po_uda_ag_template_usages uda_usg
WHERE base.po_line_id = plbd.po_line_id
AND base.draft_id = -1
AND base.uda_template_id = uda_usg.template_id
AND base.attr_group_id = uda_usg.attribute_group_id
AND uda_usg.attribute_category <> 'PRICING'
);
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
draft.po_line_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
end lock_type
FROM (SELECT plt.po_line_id,
plt.draft_id,
po_uda_tl_mod_p_lock_attrs(
NVL(plt.tl_ext_attr1 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr2 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr3 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr4 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr5 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr6 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr7 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr8 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr9 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr10 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr11 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr12 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr13 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr14 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr15 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr16 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr17 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr18 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr19 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr20 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr21 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr22 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr23 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr24 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr25 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr26 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr27 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr28 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr29 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr30 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr31 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr32 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr33 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr34 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr35 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr36 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr37 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr38 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr39 ,G_NULL_CHAR),
NVL(plt.tl_ext_attr40 ,G_NULL_CHAR)
) p_lock_attrs,
plt.attr_group_id,
plt.language
FROM po_lines_all_ext_tl plt,
po_session_gt pogt
where plt.po_line_id = pogt.char1 -- po_line_id
AND plt.draft_id = G_DRAFT_ID_MINUS_1
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
and pogt.index_char2 = g_lock_header_entity
) base,
(select pltd.po_line_id,
pltd.draft_id,
po_uda_tl_mod_p_lock_attrs(
NVL(pltd.tl_ext_attr1 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr2 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr3 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr4 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr5 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr6 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr7 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr8 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr9 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr10 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr11 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr12 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr13 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr14 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr15 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr16 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr17 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr18 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr19 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr20 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr21 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr22 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr23 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr24 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr25 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr26 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr27 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr28 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr29 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr30 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr31 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr32 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr33 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr34 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr35 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr36 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr37 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr38 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr39 ,G_NULL_CHAR),
NVL(pltd.tl_ext_attr40 ,G_NULL_CHAR)
) p_lock_attrs,
pltd.attr_group_id,
pltd.language
FROM po_lines_all_ext_tl pltd,
po_session_gt pogt
WHERE pltd.draft_id = p_draft_id
AND pltd.po_line_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
and pogt.index_char2 = g_lock_header_entity
) draft
where base.attr_group_id = draft.attr_group_id
AND base.language = draft.language
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
polext_base.po_line_id,
polext_draft.draft_id,
'S'
FROM po_lines_all_ext_b polext_base,
po_lines_all_ext_b polext_draft,
po_session_gt pogt
WHERE EXISTS (SELECT 'Is Complex Pricig Attribute'
FROM po_uda_ag_template_usages agtu,
po_lines_all pol
WHERE polext_base.po_line_id = pol.po_line_id
AND pol.uda_template_id = agtu.template_id
AND polext_base.attr_group_id = agtu.attribute_group_id
AND agtu.attribute_category = 'PRICING')
AND polext_draft.draft_id = p_draft_id
AND polext_base.draft_id = -1
AND polext_base.po_line_id = polext_draft.po_line_id
AND polext_base.attr_group_id = polext_draft.attr_group_id
-- ignore delta and old values records
AND polext_base.data_level_id = polext_draft.data_level_id
AND polext_base.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID -- records identifier
AND pogt.index_char2 = G_LOCK_LINE_ENTITY -- entity_name
AND (NVL(polext_base.n_ext_attr1, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr1, G_NULL_NUM)
OR NVL(polext_base.n_ext_attr3, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr3, G_NULL_NUM)
OR NVL(polext_base.n_ext_attr4, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr4, G_NULL_NUM)
OR NVL(polext_base.n_ext_attr5, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr5, G_NULL_NUM)
OR NVL(polext_base.n_ext_attr7, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr7, G_NULL_NUM)
OR NVL(polext_base.n_ext_attr8, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr8, G_NULL_NUM)
OR NVL(polext_base.n_ext_attr10, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr10, G_NULL_NUM)
OR NVL(polext_base.n_ext_attr11, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr11, G_NULL_NUM)
OR NVL(polext_base.n_ext_attr20, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr20, G_NULL_NUM)
-- include n_ext_attr2 even if it is used as UNIT_PRICE
-- if UNIT_PRICE is changed F lock will be taken in the next SQL
OR NVL(polext_base.n_ext_attr2, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr2, G_NULL_NUM)
);
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_LINE_ENTITY,
polext_base.po_line_id,
polext_draft.draft_id,
'F'
FROM po_lines_all_ext_b polext_base,
po_lines_all_ext_b polext_draft,
po_session_gt pogt
WHERE EXISTS (SELECT 'Is Complex Pricig Attribute'
FROM po_uda_ag_template_usages agtu,
po_lines_all pol
WHERE polext_base.po_line_id = pol.po_line_id
AND pol.uda_template_id = agtu.template_id
AND polext_base.attr_group_id = agtu.attribute_group_id
AND agtu.attribute_category = 'PRICING')
AND polext_draft.draft_id = p_draft_id
AND polext_base.draft_id = -1
AND polext_base.po_line_id = polext_draft.po_line_id
AND polext_base.attr_group_id = polext_draft.attr_group_id
-- ignore delta and old values records
AND polext_base.data_level_id = polext_draft.data_level_id
AND polext_base.po_line_id = pogt.char1 -- po_line_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID -- records identifier
AND pogt.index_char2 = G_LOCK_LINE_ENTITY -- entity_name
AND (NVL(polext_base.c_ext_attr1, G_NULL_CHAR) <> NVL(polext_draft.c_ext_attr1, G_NULL_CHAR)
-- compare n_ext_attr2 only if it is used as UNIT_PRICE
OR NVL(polext_base.n_ext_attr2, G_NULL_NUM) <> NVL(polext_draft.n_ext_attr2, G_NULL_NUM)
AND EXISTS (SELECT 'n_ext_attr2 used as UNIT_PRICE'
FROM ego_attrs_v attrs,
ego_obj_attr_grp_assocs_v att_assoc,
po_uda_ag_template_usages agtu,
po_lines_all pol
WHERE attrs.attr_group_type = att_assoc.attr_group_type
AND attrs.attr_group_name = att_assoc.attr_group_name
--AND attrs.attr_group_type = 'PO_LINE_EXT_ATTRS'
--AND attrs.attr_group_name LIKE 'AMT%'
AND attrs.attr_name = 'UNIT_PRICE'
AND polext_base.attr_group_id = att_assoc.attr_group_id
AND polext_base.po_line_id = pol.po_line_id
AND pol.uda_template_id = agtu.template_id
AND polext_base.attr_group_id = agtu.attribute_group_id
AND agtu.attribute_category = 'PRICING')
);
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_SHIPMENT_ENTITY,
draft.line_location_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT pllb.line_location_id,
pllb.draft_id,
po_uda_mod_p_lock_attrs(
NVL(pllb.c_ext_attr1 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr2 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr3 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr4 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr5 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr6 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr7 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr8 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr9 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr10 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr11 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr12 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr13 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr14 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr15 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr16 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr17 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr18 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr19 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr20 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr21 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr22 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr23 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr24 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr25 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr26 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr27 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr28 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr29 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr30 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr31 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr32 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr33 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr34 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr35 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr36 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr37 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr38 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr39 ,G_NULL_CHAR),
NVL(pllb.c_ext_attr40 ,G_NULL_CHAR),
NVL(pllb.n_ext_attr1 ,G_NULL_NUM),
NVL(pllb.n_ext_attr2 ,G_NULL_NUM),
NVL(pllb.n_ext_attr3 ,G_NULL_NUM),
NVL(pllb.n_ext_attr4 ,G_NULL_NUM),
NVL(pllb.n_ext_attr5 ,G_NULL_NUM),
NVL(pllb.n_ext_attr6 ,G_NULL_NUM),
NVL(pllb.n_ext_attr7 ,G_NULL_NUM),
NVL(pllb.n_ext_attr8 ,G_NULL_NUM),
NVL(pllb.n_ext_attr9 ,G_NULL_NUM),
NVL(pllb.n_ext_attr10 ,G_NULL_NUM),
NVL(pllb.n_ext_attr11 ,G_NULL_NUM),
NVL(pllb.n_ext_attr12 ,G_NULL_NUM),
NVL(pllb.n_ext_attr13 ,G_NULL_NUM),
NVL(pllb.n_ext_attr14 ,G_NULL_NUM),
NVL(pllb.n_ext_attr15 ,G_NULL_NUM),
NVL(pllb.n_ext_attr16 ,G_NULL_NUM),
NVL(pllb.n_ext_attr17 ,G_NULL_NUM),
NVL(pllb.n_ext_attr18 ,G_NULL_NUM),
NVL(pllb.n_ext_attr19 ,G_NULL_NUM),
NVL(pllb.n_ext_attr20 ,G_NULL_NUM),
NVL(pllb.uom_ext_attr1 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr2 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr3 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr4 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr5 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr6 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr7 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr8 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr9 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr10 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr11 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr12 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr13 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr14 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr15 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr16 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr17 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr18 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr19 ,G_NULL_CHAR),
NVL(pllb.uom_ext_attr20 ,G_NULL_CHAR),
NVL(pllb.d_ext_attr1 ,G_NULL_DATE),
NVL(pllb.d_ext_attr2 ,G_NULL_DATE),
NVL(pllb.d_ext_attr3 ,G_NULL_DATE),
NVL(pllb.d_ext_attr4 ,G_NULL_DATE),
NVL(pllb.d_ext_attr5 ,G_NULL_DATE),
NVL(pllb.d_ext_attr6 ,G_NULL_DATE),
NVL(pllb.d_ext_attr7 ,G_NULL_DATE),
NVL(pllb.d_ext_attr8 ,G_NULL_DATE),
NVL(pllb.d_ext_attr9 ,G_NULL_DATE),
NVL(pllb.d_ext_attr10 ,G_NULL_DATE)
) p_lock_attrs,
pllb.attr_group_id
FROM po_line_locations_all_ext_b pllb,
po_session_gt pogt
WHERE pllb.line_location_id = pogt.char1 -- po_header_id
AND pllb.draft_id = G_DRAFT_ID_MINUS_1
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_SHIPMENT_ENTITY
) base,
(SELECT pllbd.line_location_id,
pllbd.draft_id,
po_uda_mod_p_lock_attrs(
NVL(pllbd.c_ext_attr1 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr2 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr3 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr4 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr5 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr6 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr7 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr8 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr9 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr10 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr11 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr12 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr13 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr14 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr15 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr16 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr17 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr18 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr19 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr20 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr21 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr22 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr23 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr24 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr25 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr26 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr27 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr28 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr29 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr30 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr31 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr32 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr33 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr34 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr35 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr36 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr37 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr38 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr39 ,G_NULL_CHAR),
NVL(pllbd.c_ext_attr40 ,G_NULL_CHAR),
NVL(pllbd.n_ext_attr1 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr2 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr3 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr4 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr5 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr6 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr7 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr8 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr9 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr10 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr11 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr12 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr13 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr14 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr15 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr16 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr17 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr18 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr19 ,G_NULL_NUM),
NVL(pllbd.n_ext_attr20 ,G_NULL_NUM),
NVL(pllbd.uom_ext_attr1 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr2 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr3 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr4 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr5 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr6 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr7 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr8 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr9 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr10 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr11 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr12 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr13 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr14 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr15 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr16 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr17 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr18 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr19 ,G_NULL_CHAR),
NVL(pllbd.uom_ext_attr20 ,G_NULL_CHAR),
NVL(pllbd.d_ext_attr1 ,G_NULL_DATE),
NVL(pllbd.d_ext_attr2 ,G_NULL_DATE),
NVL(pllbd.d_ext_attr3 ,G_NULL_DATE),
NVL(pllbd.d_ext_attr4 ,G_NULL_DATE),
NVL(pllbd.d_ext_attr5 ,G_NULL_DATE),
NVL(pllbd.d_ext_attr6 ,G_NULL_DATE),
NVL(pllbd.d_ext_attr7 ,G_NULL_DATE),
NVL(pllbd.d_ext_attr8 ,G_NULL_DATE),
NVL(pllbd.d_ext_attr9 ,G_NULL_DATE),
NVL(pllbd.d_ext_attr10 ,G_NULL_DATE)
) p_lock_attrs,
pllbd.attr_group_id
FROM po_line_locations_all_ext_b pllbd,
po_session_gt pogt
WHERE pllbd.draft_id = p_draft_id
AND pllbd.line_location_id = pogt.char1 -- line_location_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_SHIPMENT_ENTITY
) draft
WHERE base.attr_group_id = draft.attr_group_id
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_SHIPMENT_ENTITY,
pllbd.line_location_id,
p_draft_id,
'P'
FROM po_line_locations_draft_all pllbd,
po_session_gt pogt
WHERE pllbd.draft_id = p_draft_id
AND pllbd.line_location_id = pogt.char1 -- line_location_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_SHIPMENT_ENTITY
AND EXISTS (SELECT draft.attr_group_id
FROM po_line_locations_all_ext_b draft
WHERE draft.line_location_id = pllbd.line_location_id
AND draft.draft_id = p_draft_id
MINUS
SELECT base.attr_group_id
FROM po_line_locations_all_ext_b base
WHERE base.line_location_id = pllbd.line_location_id
AND base.draft_id = -1
);
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_SHIPMENT_ENTITY,
draft.line_location_id,
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END LOCK_TYPE
FROM (SELECT pllt.line_location_id,
pllt.draft_id,
po_uda_tl_mod_p_lock_attrs(
NVL(pllt.tl_ext_attr1 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr2 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr3 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr4 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr5 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr6 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr7 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr8 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr9 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr10 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr11 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr12 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr13 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr14 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr15 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr16 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr17 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr18 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr19 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr20 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr21 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr22 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr23 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr24 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr25 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr26 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr27 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr28 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr29 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr30 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr31 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr32 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr33 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr34 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr35 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr36 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr37 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr38 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr39 ,G_NULL_CHAR),
NVL(pllt.tl_ext_attr40 ,G_NULL_CHAR)
) p_lock_attrs,
pllt.attr_group_id,
pllt.language
FROM po_line_locations_all_ext_tl pllt,
po_session_gt pogt
where pllt.line_location_id = pogt.char1 -- line_location_id
AND pllt.draft_id = G_DRAFT_ID_MINUS_1
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
and pogt.index_char2 = G_LOCK_SHIPMENT_ENTITY
) BASE,
(select plltd.line_location_id,
plltd.draft_id,
po_uda_tl_mod_p_lock_attrs(
NVL(plltd.tl_ext_attr1 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr2 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr3 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr4 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr5 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr6 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr7 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr8 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr9 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr10 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr11 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr12 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr13 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr14 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr15 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr16 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr17 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr18 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr19 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr20 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr21 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr22 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr23 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr24 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr25 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr26 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr27 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr28 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr29 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr30 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr31 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr32 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr33 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr34 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr35 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr36 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr37 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr38 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr39 ,G_NULL_CHAR),
NVL(plltd.tl_ext_attr40 ,G_NULL_CHAR)
) p_lock_attrs,
plltd.attr_group_id,
plltd.language
FROM po_line_locations_all_ext_tl plltd,
po_session_gt pogt
WHERE plltd.draft_id = p_draft_id
AND plltd.line_location_id = pogt.char1 -- po_header_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
and pogt.index_char2 = G_LOCK_SHIPMENT_ENTITY
) draft
where base.attr_group_id = draft.attr_group_id
AND base.language = draft.language
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module,d_position ,'number of rows Inserted ',SQL%ROWCOUNT);
PROCEDURE create_update_locks(
p_draft_id IN NUMBER,
p_return_status IN OUT NOCOPY VARCHAR2,
x_results OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
)
IS
d_api_name CONSTANT VARCHAR2(30) := 'create_update_locks';
DELETE
FROM po_entity_locks poel
WHERE poel.lock_by_draft_id = p_draft_id
AND NOT EXISTS
(SELECT 'Lock required'
FROM po_session_gt pogt
WHERE pogt.index_char1 = G_LOCKS_REQUIRED_ID
AND pogt.index_char2 = poel.entity_name
AND pogt.num1 = poel.lock_by_draft_id
AND pogt.char1 = poel.entity_pk1
AND NVL(pogt.char2, G_NULL_CHAR) = NVL(poel.entity_pk2, G_NULL_CHAR)
AND NVL(pogt.char3, G_NULL_CHAR) = NVL(poel.entity_pk3, G_NULL_CHAR)
AND NVL(pogt.char4, G_NULL_CHAR) = NVL(poel.entity_pk4, G_NULL_CHAR)
)
AND EXISTS
(SELECT 'Entity changed in current transaction'
FROM po_session_gt pogt
WHERE pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = poel.entity_name
AND pogt.char1 = poel.entity_pk1
AND NVL(pogt.char2, G_NULL_CHAR) = NVL(poel.entity_pk2, G_NULL_CHAR)
AND NVL(pogt.char3, G_NULL_CHAR) = NVL(poel.entity_pk3, G_NULL_CHAR)
AND NVL(pogt.char4, G_NULL_CHAR) = NVL(poel.entity_pk4, G_NULL_CHAR)
);
PO_LOG.stmt(d_module, d_position, 'Number of Rows Deleted',SQL%ROWCOUNT);
SELECT pogt.index_char2 entity_name,
pogt.char1 entity_pk1,
pogt.char2 entity_pk2,
pogt.char3 entity_pk3,
pogt.char4 entity_pk4,
pogt.num1 lock_by_draft_id,
SUBSTR(MAX(CASE pogt.char5
WHEN 'F' THEN '5-F'
WHEN 'P' THEN '3-P'
WHEN 'S' THEN '1-S'
ELSE NULL
END),
3) lock_type
FROM po_session_gt pogt
WHERE pogt.index_char1 = G_LOCKS_REQUIRED_ID
GROUP BY pogt.index_char2, -- entity_name
pogt.char1, -- entity_pk1
pogt.char2, -- entity_pk2
pogt.char3, -- entity_pk3
pogt.char4, -- entity_pk4
pogt.num1 -- lock_by_draft_id
) lck_dat
ON (lck_tbl.entity_name = lck_dat.entity_name
AND lck_tbl.entity_pk1 = lck_dat.entity_pk1
AND NVL(lck_tbl.entity_pk2, G_NULL_CHAR) = NVL(lck_dat.entity_pk2, G_NULL_CHAR)
AND NVL(lck_tbl.entity_pk3, G_NULL_CHAR) = NVL(lck_dat.entity_pk3, G_NULL_CHAR)
AND NVL(lck_tbl.entity_pk4, G_NULL_CHAR) = NVL(lck_dat.entity_pk4, G_NULL_CHAR)
AND lck_tbl.lock_by_draft_id = lck_dat.lock_by_draft_id)
WHEN MATCHED THEN
UPDATE
SET
lck_tbl.lock_type = lck_dat.lock_type,
lck_tbl.last_update_date = SYSDATE,
lck_tbl.last_updated_by = fnd_global.user_id,
lck_tbl.last_update_login = fnd_global.login_id
WHEN NOT MATCHED THEN
INSERT(
lck_tbl.po_entity_lock_id,
lck_tbl.entity_name,
lck_tbl.entity_pk1,
lck_tbl.entity_pk2,
lck_tbl.entity_pk3,
lck_tbl.entity_pk4,
lck_tbl.lock_by_draft_id,
lck_tbl.lock_type,
lck_tbl.last_update_date,
lck_tbl.last_updated_by,
lck_tbl.last_update_login,
lck_tbl.creation_date,
lck_tbl.created_by)
VALUES(
po_entity_locks_s.NEXTVAL,
lck_dat.entity_name,
lck_dat.entity_pk1,
lck_dat.entity_pk2,
lck_dat.entity_pk3,
lck_dat.entity_pk4,
lck_dat.lock_by_draft_id,
lck_dat.lock_type,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
SYSDATE,
fnd_global.user_id);
END create_update_locks;
SELECT poh.po_header_id
BULK COLLECT
INTO l_entity_id_tbl
FROM po_headers_all poh
WHERE EXISTS (SELECT 'lock exists'
FROM po_entity_locks poel
WHERE poel.entity_name = G_LOCK_HEADER_ENTITY
AND poel.lock_by_draft_id = p_draft_id
AND poel.entity_pk1 = poh.po_header_id)
FOR UPDATE;
SELECT pol.po_line_id
BULK COLLECT
INTO l_entity_id_tbl
FROM po_lines_all pol
WHERE EXISTS (SELECT 'lock exists'
FROM po_entity_locks poel
WHERE poel.entity_name = G_LOCK_LINE_ENTITY
AND poel.lock_by_draft_id = p_draft_id
AND poel.entity_pk1 = pol.po_line_id)
FOR UPDATE;
SELECT poll.po_line_id
BULK COLLECT
INTO l_entity_id_tbl
FROM po_line_locations_all poll
WHERE EXISTS (SELECT 'lock exists'
FROM po_entity_locks poel
WHERE poel.entity_name = G_LOCK_SHIPMENT_ENTITY
AND poel.lock_by_draft_id = p_draft_id
AND poel.entity_pk1 = poll.line_location_id)
FOR UPDATE;
SELECT pod.po_line_id
BULK COLLECT
INTO l_entity_id_tbl
FROM po_distributions_all pod
WHERE EXISTS (SELECT 'lock exists'
FROM po_entity_locks poel
WHERE poel.entity_name = G_LOCK_DIST_ENTITY
AND poel.lock_by_draft_id = p_draft_id
AND poel.entity_pk1 = pod.po_distribution_id)
FOR UPDATE;
SELECT poel_curr.entity_name,
poel_curr.entity_pk1,
pod.modification_number
BULK COLLECT
INTO l_err_entity_tbl,
l_err_pk1_tbl,
l_lock_by_mod_num_tbl
FROM po_entity_locks poel_curr,
po_entity_locks poel_oth,
TABLE(g_entity_lock_comp_tbl) comp,
po_drafts pod
WHERE poel_curr.lock_by_draft_id = p_draft_id
AND poel_oth.lock_by_draft_id <> p_draft_id
AND poel_curr.entity_name = poel_oth.entity_name
AND poel_curr.entity_pk1 = poel_oth.entity_pk1
AND NVL(poel_curr.entity_pk2, -1) = NVL(poel_oth.entity_pk2, -1)
AND NVL(poel_curr.entity_pk3, -1) = NVL(poel_oth.entity_pk3, -1)
AND NVL(poel_curr.entity_pk4, -1) = NVL(poel_oth.entity_pk4, -1)
AND comp.lock_type1 = poel_curr.lock_type
AND comp.lock_type2 = poel_oth.lock_type
AND comp.is_compatible = 'I'
AND poel_oth.lock_by_draft_id = pod.draft_id;
cursor get_to_be_deleted_attachments(p_header_id NUMBER,p_draft_id NUMBER, p_entity_name VARCHAR2) is
select fad.attached_document_id , fd.datatype_id
from fnd_attached_documents fad, fnd_documents fd
where fad.entity_name = p_entity_name
and fad.pk1_value = to_char(p_header_id) || '-' || to_char(p_draft_id)
AND fd.document_id = fad.document_id
AND fad.category_id <> (SELECT category_id
FROM fnd_document_categories
WHERE application_id = 201 AND name = 'CUSTOM5340'
);
select type_lookup_code, style_id
into l_document_type_code, l_document_style_id
from po_headers_all
where po_header_id = p_po_header_id;
DELETE
FROM po_entity_locks pel
WHERE pel.entity_pk1 = p_po_header_id
AND pel.lock_by_draft_id = p_draft_id
AND pel.entity_name IN (PO_DRAFTS_PVT.G_LOCK_ADDRESS_ENTITY, PO_DRAFTS_PVT.G_LOCK_HEADER_ENTITY,G_LOCK_HEADER_ATTACH_ENTITY);
PO_LOG.stmt(d_module,d_position ,'Deleted po_entity_locks');
PO_LOG.stmt(d_module,d_position ,'number of rows deleted ',SQL%ROWCOUNT);
FOR l_rec in get_to_be_deleted_attachments(p_po_header_id,p_draft_id,'PO_HEADERS')
LOOP
fnd_attached_documents3_pkg.delete_row
( X_attached_document_id => l_rec.attached_document_id,
X_datatype_id => l_rec.datatype_id,
delete_document_flag => 'Y'
);
FOR l_rec in get_to_be_deleted_attachments(p_po_header_id,p_draft_id,'PO_HEADERS_DEL')
LOOP
fnd_attached_documents3_pkg.delete_row
( X_attached_document_id => l_rec.attached_document_id,
X_datatype_id => l_rec.datatype_id,
delete_document_flag => 'Y'
);
PO_MOD_CONTROL_PVT.update_header_dummy_req(p_draft_id);
PO_REQ_LINES_SV.update_req_for_linked_po_count (po_dest_line_ids_tbl,
'DISTRIBUTION MOD');
PO_REQ_LINES_SV.update_reqs_in_pool_flag;
DELETE
FROM po_distributions_draft_all
WHERE po_distribution_id = po_dest_line_ids_tbl(itr)
AND draft_id = p_po_draft_id;
DELETE
FROM po_entity_locks pel
WHERE pel.entity_pk1 = po_dest_line_ids_tbl(itr)
AND pel.lock_by_draft_id = p_po_draft_id
AND pel.entity_name = PO_DRAFTS_PVT.G_LOCK_DIST_ENTITY;
PO_LOG.stmt(d_module,d_position ,'Deleted po_entity_locks');
PO_LOG.stmt(d_module,d_position ,'number of rows deleted ',SQL%ROWCOUNT);
cursor get_to_be_deleted_attachments(p_line_location_id NUMBER,p_draft_id NUMBER, p_entity_name VARCHAR2) is
select fad.attached_document_id , fd.datatype_id
from fnd_attached_documents fad, fnd_documents fd
where fad.entity_name = p_entity_name
and fad.pk1_value = to_char(p_line_location_id) || '-' || to_char(p_draft_id)
AND fd.document_id = fad.document_id
AND fad.category_id <> (SELECT category_id
FROM fnd_document_categories
WHERE application_id = 201 AND name = 'CUSTOM5340'
);
SELECT po_distribution_id
BULK COLLECT INTO po_distribution_id_tbl
FROM po_distributions_all pda
WHERE pda.line_location_id IN (SELECT * FROM TABLE(po_ship_ids_tbl));
DELETE
FROM po_line_locations_draft_all
WHERE line_location_id = po_ship_ids_tbl(itr)
AND draft_id = p_po_draft_id;
DELETE
FROM po_entity_locks pel
WHERE pel.entity_pk1 = po_ship_ids_tbl(itr)
AND pel.lock_by_draft_id = p_po_draft_id
AND pel.entity_name IN (PO_DRAFTS_PVT.G_LOCK_SHIPMENT_ENTITY,PO_DRAFTS_PVT.G_LOCK_SHIPMENT_ATTACH_ENTITY);
PO_LOG.stmt(d_module,d_position ,'Deleted po_entity_locks');
PO_LOG.stmt(d_module,d_position ,'number of rows deleted ',SQL%ROWCOUNT);
DELETE
FROM po_line_locations_all_ext_b
WHERE line_location_id = po_ship_ids_tbl(itr) AND draft_id = p_po_draft_id;
DELETE
FROM po_line_locations_all_ext_tl
WHERE line_location_id = po_ship_ids_tbl(itr) AND draft_id = p_po_draft_id;
FOR l_rec in get_to_be_deleted_attachments(po_ship_ids_tbl(itr),p_po_draft_id,'PO_SHIPMENTS') LOOP
fnd_attached_documents3_pkg.delete_row
( X_attached_document_id => l_rec.attached_document_id,
X_datatype_id => l_rec.datatype_id,
delete_document_flag => 'Y'
);
FOR l_rec in get_to_be_deleted_attachments(po_ship_ids_tbl(itr),p_po_draft_id,'PO_SHIPMENTS_DEL') LOOP
fnd_attached_documents3_pkg.delete_row
( X_attached_document_id => l_rec.attached_document_id,
X_datatype_id => l_rec.datatype_id,
delete_document_flag => 'Y'
);
cursor get_to_be_deleted_attachments(p_line_id NUMBER,p_draft_id NUMBER,p_entity_name VARCHAR2) is
select fad.attached_document_id , fd.datatype_id
from fnd_attached_documents fad, fnd_documents fd
where fad.entity_name = p_entity_name
and fad.pk1_value = to_char(p_line_id) || '-' || to_char(p_draft_id)
AND fd.document_id = fad.document_id
AND fad.category_id <> (SELECT category_id
FROM fnd_document_categories
WHERE application_id = 201 AND name = 'CUSTOM5340'
);
SELECT line_location_id
BULK COLLECT INTO po_line_location_id_tbl
FROM po_line_locations_all poll
WHERE poll.po_line_id IN (SELECT * FROM TABLE(po_line_ids_tbl));
delete_line_ucas(p_po_draft_id => p_po_draft_id,
po_line_ids_tbl => po_line_ids_tbl);
PO_REQ_LINES_SV.update_req_for_linked_po_count (po_line_ids_tbl,
'LINE');
PO_REQ_LINES_SV.update_reqs_in_pool_flag;
DELETE
FROM po_lines_draft_all
WHERE po_line_id = po_line_ids_tbl(i)
AND draft_id = p_po_draft_id;
DELETE
FROM po_entity_locks pel
WHERE pel.entity_pk1 = po_line_ids_tbl(i)
AND pel.lock_by_draft_id = p_po_draft_id
AND pel.entity_name IN (PO_DRAFTS_PVT.G_LOCK_LINE_ENTITY,PO_DRAFTS_PVT.G_LOCK_LINE_ATTACH_ENTITY);
PO_LOG.stmt(d_module,d_position ,'Deleted po_entity_locks');
PO_LOG.stmt(d_module,d_position ,'number of rows deleted ',SQL%ROWCOUNT);
DELETE
FROM po_lines_all_ext_b
WHERE (po_line_id = po_line_ids_tbl(i) -- main record for Mod
AND draft_id = p_po_draft_id)
OR -- old record for Mod complex pricing attribute
(po_line_id = po_line_ids_tbl(i)
AND draft_id = -1
AND pk1_value = p_po_draft_id)
OR -- delta record for Mod complex pricing attribute
(po_line_id = -po_line_ids_tbl(i)
AND draft_id = -p_po_draft_id
AND pk1_value = -p_po_draft_id);
DELETE
FROM po_lines_all_ext_tl
WHERE (po_line_id = po_line_ids_tbl(i) -- main record for Mod
AND draft_id = p_po_draft_id)
OR -- old record for Mod complex pricing attribute
(po_line_id = po_line_ids_tbl(i)
AND draft_id = -1
AND pk1_value = p_po_draft_id)
OR -- delta record for Mod complex pricing attribute
(po_line_id = -po_line_ids_tbl(i)
AND draft_id = -p_po_draft_id
AND pk1_value = -p_po_draft_id);
FOR l_rec in get_to_be_deleted_attachments(po_line_ids_tbl(itr),p_po_draft_id,'PO_LINES') LOOP
fnd_attached_documents3_pkg.delete_row
( X_attached_document_id => l_rec.attached_document_id,
X_datatype_id => l_rec.datatype_id,
delete_document_flag => 'Y'
);
FOR l_rec in get_to_be_deleted_attachments(po_line_ids_tbl(itr),p_po_draft_id,'PO_LINES_DEL') LOOP
fnd_attached_documents3_pkg.delete_row
( X_attached_document_id => l_rec.attached_document_id,
X_datatype_id => l_rec.datatype_id,
delete_document_flag => 'Y'
);
Deletes draft data and uda data for line entity which is deleted
from UI. i.e. delete_flag is 'Y'.
*/
PROCEDURE delete_marked_lines(
p_draft_id IN NUMBER)
IS
l_line_id_tbl PO_TBL_NUMBER;
d_api_name CONSTANT VARCHAR2(30) := 'delete_marked_lines';
SELECT po_line_id
BULK COLLECT
INTO l_line_id_tbl
FROM po_lines_draft_all
WHERE delete_flag = 'Y'
AND draft_id = p_draft_id
AND NVL(change_accepted_flag, 'Y') = 'Y';
pon_clm_clo_util_pkg.SEND_LINE_DELETED_NOTIF( p_caller=>'PO_MOD'
,p_document_id => p_draft_id
,p_document_line_id => null
,x_return_status=>l_return_status
,x_msg_count=>l_msg_count
,x_msg_data =>l_msg_data);
PO_LOG.stmt(d_module, d_position, 'Deleted Lines Found');
PO_REQ_LINES_SV.update_req_for_linked_po_count (l_line_id_tbl, 'LINE');
delete_line_ucas(p_po_draft_id => p_draft_id,
po_line_ids_tbl => l_line_id_tbl);
DELETE FROM po_lines_draft_all
WHERE po_line_id = l_line_id_tbl(i)
AND draft_id = p_draft_id;
DELETE FROM po_lines_all_ext_b
WHERE po_line_id = l_line_id_tbl(i)
AND draft_id = p_draft_id;
DELETE FROM po_lines_all_ext_tl
WHERE po_line_id = l_line_id_tbl(i)
AND draft_id = p_draft_id;
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
( 'PO_LINES',
l_line_id_tbl(i)||'-'||p_draft_id,
'', '', '', '', '');
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
( 'PO_LINES_DEL',
l_line_id_tbl(i)||'-'||p_draft_id,
'', '', '', '', '');
PO_LOG.proc_end(d_module, 'Deleted line id(s): ', l_line_id_tbl);
END delete_marked_lines;
Deletes draft data and uda data for shipment entity which is deleted
from UI. i.e. delete_flag is 'Y'.
*/
PROCEDURE delete_marked_shipments(
p_draft_id IN NUMBER)
IS
l_line_loc_id_tbl PO_TBL_NUMBER;
d_api_name CONSTANT VARCHAR2(30) := 'delete_marked_shipments';
SELECT line_location_id
BULK COLLECT
INTO l_line_loc_id_tbl
FROM po_line_locations_draft_all
WHERE delete_flag = 'Y'
AND draft_id = p_draft_id
AND NVL(change_accepted_flag, 'Y') = 'Y';
DELETE FROM po_line_locations_draft_all
WHERE line_location_id = l_line_loc_id_tbl(i)
AND draft_id = p_draft_id;
DELETE FROM po_line_locations_all_ext_b
WHERE line_location_id = l_line_loc_id_tbl(i)
AND draft_id = p_draft_id;
DELETE FROM po_line_locations_all_ext_tl
WHERE line_location_id = l_line_loc_id_tbl(i)
AND draft_id = p_draft_id;
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
( 'PO_SHIPMENTS',
l_line_loc_id_tbl(i)||'-'||p_draft_id,
'', '', '', '', '');
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
( 'PO_SHIPMENTS_DEL',
l_line_loc_id_tbl(i)||'-'||p_draft_id,
'', '', '', '', '');
PO_LOG.proc_end(d_module, 'Deleted shipment id(s): ', l_line_loc_id_tbl);
END delete_marked_shipments;
Deletes draft data for distribution entity which is deleted
from UI. i.e. delete_flag is 'Y'.
*/
PROCEDURE delete_marked_distributions(
p_draft_id IN NUMBER)
IS
l_distribution_id_tbl PO_TBL_NUMBER;
d_api_name CONSTANT VARCHAR2(30) := 'delete_marked_distributions';
SELECT po_distribution_id
BULK COLLECT
INTO l_distribution_id_tbl
FROM po_distributions_draft_all
WHERE delete_flag = 'Y'
AND draft_id = p_draft_id
AND NVL(change_accepted_flag, 'Y') = 'Y';
PO_REQ_LINES_SV.update_req_for_linked_po_count (l_distribution_id_tbl, 'DISTRIBUTION MOD');
DELETE FROM po_distributions_draft_all
WHERE po_distribution_id = l_distribution_id_tbl(i)
AND draft_id = p_draft_id;
PO_LOG.proc_end(d_module, 'Deleted distribution id(s): ', l_distribution_id_tbl);
END delete_marked_distributions;
PROCEDURE delete_marked_entities(
p_draft_id IN NUMBER
)
IS
BEGIN
/**
Calling delete_marked_lines, shipments and distributions procedures.
These procedures remove entities marked for removal i.e. delete_flag
is set to 'Y'. This is for entities in modification.
*/
delete_marked_lines(p_draft_id);
delete_marked_shipments(p_draft_id);
delete_marked_distributions(p_draft_id);
PO_REQ_LINES_SV.update_reqs_in_pool_flag;
END delete_marked_entities;
DELETE FROM po_lines_all_ext_b ple
where ( Abs(ple.po_line_id), Abs(ple.draft_id) ) IN ( SELECT po_line_id, Decode(draft_id,-1,1,draft_id)
FROM po_lines_merge_v
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id
)
AND EXISTS ( SELECT 1
FROM po_lines_merge_v plm , po_uda_ag_template_usages ptu
WHERE plm.po_line_id = Abs(ple.po_line_id)
AND plm.draft_id = Decode(p_draft_id,
-1,-1,
Abs(ple.draft_id))
AND ptu.attribute_category = 'PRICING'
AND ptu.template_id = plm.uda_template_id
AND ( NVL(ptu.attribute1, '*') <> NVL(plm.clm_idc_type, '*')
OR
Nvl(ptu.attribute2, '*') <> Nvl(plm.contract_type, '*')
)
AND ple.attr_group_id = ptu.attribute_group_id
);
PO_LOG.stmt(d_module,d_position ,'Deleted extra prcing uda records from po_lines_all_ext_b');
PO_LOG.stmt(d_module,d_position ,'number of rows deleted ',SQL%ROWCOUNT);
DELETE FROM po_lines_all_ext_tl ple
where ( Abs(ple.po_line_id), Abs(ple.draft_id) ) IN ( SELECT po_line_id, Decode(draft_id,-1,1,draft_id)
FROM po_lines_merge_v
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id
)
AND EXISTS ( SELECT 1
FROM po_lines_merge_v plm , po_uda_ag_template_usages ptu
WHERE plm.po_line_id = Abs(ple.po_line_id)
AND plm.draft_id = Decode(p_draft_id,
-1,-1,
Abs(ple.draft_id))
AND ptu.attribute_category = 'PRICING'
AND ptu.template_id = plm.uda_template_id
AND ( NVL(ptu.attribute1, '*') <> NVL(plm.clm_idc_type, '*')
OR
Nvl(ptu.attribute2, '*') <> Nvl(plm.contract_type, '*')
)
AND ple.attr_group_id = ptu.attribute_group_id
);
PO_LOG.stmt(d_module,d_position ,'Deleted extra prcing uda records from po_lines_all_ext_tl');
PO_LOG.stmt(d_module,d_position ,'number of rows deleted ',SQL%ROWCOUNT);
DELETE FROM po_headers_all_ext_b phe
where phe.po_header_id = p_po_header_id
AND Nvl(phe.draft_id,-1) = p_draft_id
AND EXISTS ( SELECT 1
FROM po_headers_merge_v phm , po_uda_ag_template_usages ptu
WHERE phm.po_header_id = phe.po_header_id
AND phm.draft_id = Nvl(phe.draft_id,-1)
AND ptu.attribute_category = 'FORMS'
AND ptu.template_id = phm.uda_template_id
AND Nvl(ptu.attribute1, '*') <> Nvl(phm.clm_standard_form, '*')
AND phe.attr_group_id = ptu.attribute_group_id
);
PO_LOG.stmt(d_module,d_position ,'Deleted extra forms uda records from po_headers_all_ext_b');
PO_LOG.stmt(d_module,d_position ,'number of rows deleted ',SQL%ROWCOUNT);
DELETE FROM po_headers_all_ext_tl phe
where phe.po_header_id = p_po_header_id
AND Nvl(phe.draft_id,-1) = p_draft_id
AND EXISTS ( SELECT 1
FROM po_headers_merge_v phm , po_uda_ag_template_usages ptu
WHERE phm.po_header_id = phe.po_header_id
AND phm.draft_id = Nvl(phe.draft_id,-1)
AND ptu.attribute_category = 'FORMS'
AND ptu.template_id = phm.uda_template_id
AND Nvl(ptu.attribute1, '*') <> Nvl(phm.clm_standard_form, '*')
AND phe.attr_group_id = ptu.attribute_group_id
);
PO_LOG.stmt(d_module,d_position ,'Deleted extra forms uda records from po_headers_all_ext_tl');
PO_LOG.stmt(d_module,d_position ,'number of rows deleted ',SQL%ROWCOUNT);
SELECT Count(*) INTO l_count
FROM FND_ATTACHED_DOCUMENTS
WHERE entity_name = p_entity_name
AND pk1_value = p_po_header_id||'-'|| p_po_draft_id
AND category_id <> (SELECT category_id
FROM fnd_document_categories_vl
WHERE application_id = 201 AND name = 'CUSTOM5340'
);
INSERT INTO po_entity_locks(
po_entity_lock_id,
entity_name,
entity_pk1,
entity_pk2,
entity_pk3,
entity_pk4,
lock_by_draft_id,
lock_type,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by
)
VALUES(
po_entity_locks_s.NEXTVAL,
p_lock_entity_name,
p_lock_entity_pk1,
TO_CHAR(p_orig_attach_doc_id),
p_lock_entity_pk3,
p_lock_entity_pk4,
p_lock_by_draft_id,
p_lock_type ,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
SYSDATE,
fnd_global.user_id
);
PO_LOG.proc_end(d_module, 'number of rows updated', SQL%ROWCOUNT);
SELECT DISTINCT
clm_exhibit_name,
pl.po_header_id,
pl.draft_id
BULK COLLECT INTO
clm_exhibit_name_tbl,
po_header_id_tbl,
po_draft_id_tbl
FROM
po_lines_merge_v pl
WHERE pl.clm_exhibit_name IS NOT NULL
AND pl.po_header_id = p_po_header_id
AND pl.draft_id = p_draft_id
AND Nvl(pl.delete_flag,'N') <> 'Y'
AND NOT EXISTS (SELECT 1 FROM po_exhibit_details_merge_v ex
WHERE ex.exhibit_name = pl.clm_exhibit_name
AND ex.po_header_id = pl.po_header_id
AND ex.draft_id = pl.draft_id
AND Nvl(ex.delete_flag,'N') <> 'Y');
PO_LOG.stmt(d_module,d_position ,'number of rows selected ',SQL%ROWCOUNT);
INSERT INTO po_exhibit_details_draft
(
po_exhibit_details_id,
exhibit_name,
exhibit_description,
is_cdrl,
po_header_id,
draft_id,
change_status,
revision_num,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT
po_exhibit_details_s.nextval,
clm_exhibit_name_tbl(idx),
null, -- Description
'N', -- IsCdrl (Cdrl is inserted by contracts team)
po_header_id_tbl(idx),
po_draft_id_tbl(idx),
'NEW',--ChangeStatus
Nvl(p_revision_num,0),
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id
FROM
dual
) ;
PO_LOG.stmt(d_module,d_position ,'number of rows selected ',SQL%ROWCOUNT);
SELECT DISTINCT
po_exhibit_details_id,
exhibit_name,
po_header_id,
draft_id
BULK COLLECT INTO
po_exhibit_details_id_tbl,
clm_exhibit_name_tbl,
po_header_id_tbl,
po_draft_id_tbl
FROM po_exhibit_details_merge_v ex
WHERE ex.po_header_id = p_po_header_id
AND ex.draft_id = p_draft_id
AND ex.is_cdrl = 'N'
AND Nvl(ex.delete_flag,'N') <> 'Y'
AND NOT EXISTS (SELECT 1 FROM po_lines_draft_all PLD
WHERE PLD.draft_id = ex.draft_id
AND ex.exhibit_name = PLD.clm_exhibit_name
AND NVL(PLD.delete_flag, 'N') <> 'Y')
AND NOT EXISTS (SELECT 1 FROM po_lines_all pl
WHERE pl.po_header_id = p_po_header_id
AND ex.exhibit_name = pl.clm_exhibit_name
AND NOT EXISTS (SELECT 1 FROM po_lines_draft_all pd
WHERE pl.po_line_id = pd.po_line_id
AND NVL(pd.delete_flag, 'N') = 'Y')
);
PO_LOG.stmt(d_module,d_position ,'number of rows selected ',SQL%ROWCOUNT);
INSERT INTO po_exhibit_details_draft
(
po_exhibit_details_id,
exhibit_name,
exhibit_description,
is_cdrl,
po_header_id,
draft_id,
delete_flag,
revision_num,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
(
SELECT po_exhibit_details_id_tbl(idx),
clm_exhibit_name_tbl(idx),
null,
'N',--DummyValue
po_header_id_tbl(idx),
po_draft_id_tbl(idx),
'Y',-- Delete Flag
Nvl(p_revision_num,0),
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM po_exhibit_details_draft exd
WHERE exd.po_exhibit_details_id = po_exhibit_details_id_tbl(idx)
)
);
PO_LOG.stmt(d_module,d_position ,'number of rows inserted for deletion ',SQL%ROWCOUNT);
UPDATE po_exhibit_details_draft ex
SET ex.delete_flag = 'Y'
WHERE ex.po_exhibit_details_id IN (SELECT column_value FROM TABLE(po_exhibit_details_id_tbl));
SELECT 'Y'
INTO l_par_exist_flag
FROM dual
WHERE EXISTS (SELECT 'PAR Exists'
FROM po_drafts pod,
po_headers_all poh
WHERE pod.document_id = poh.po_header_id
AND poh.style_id = p_style_id
AND pod.draft_type= 'PAR');
PROCEDURE delete_line_ucas(p_po_draft_id IN NUMBER,
po_line_ids_tbl IN PO_TBL_NUMBER)
IS
d_api_name CONSTANT VARCHAR2(30) := 'delete_line_ucas';
SELECT
Nvl(po_core_s.Retrieveoptionvalue(org_id, po_core_s.g_undef_cont_act_col),
'N'
)
INTO l_isucaenabled
FROM po_lines_merge_v
WHERE po_line_id = po_line_ids_tbl(1)
AND draft_id = p_po_draft_id;
DELETE FROM po_line_ucas
WHERE po_line_id = Po_line_ids_tbl(ucaindex)
AND undef_draft_id = p_po_draft_id;
UPDATE po_line_ucas
SET def_draft_id = NULL,
def_description = NULL,
def_par_draft_id = NULL
WHERE po_line_id = Po_line_ids_tbl(ucaindex)
AND ( def_draft_id = p_po_draft_id
OR def_par_draft_id = p_po_draft_id );
END delete_line_ucas;
INSERT INTO po_session_gt(
key,
index_char1, -- records identifier
index_char2, -- entity_name
char1, -- entity_pk1
num1, -- lock_by_draft_id
char5 -- lock_type
)
SELECT po_session_gt_s.NEXTVAL,
G_LOCKS_REQUIRED_ID,
G_LOCK_EXHIBIT_ENTITY,
draft.po_exhibit_details_id, -- po_exhibit_details_id
draft.draft_id,
CASE
WHEN base.p_lock_attrs <> draft.p_lock_attrs THEN 'P'
ELSE NULL
END lock_type
FROM (SELECT pex.po_exhibit_details_id,
G_DRAFT_ID_MINUS_1 draft_id,
po_exhibit_mod_p_lock_attrs(
NVL(pex.exhibit_description ,G_NULL_CHAR),
NVL(pex.reference_line_id ,G_NULL_NUM)
) p_lock_attrs
FROM po_exhibit_details pex,
po_session_gt pogt
WHERE pex.po_exhibit_details_id = pogt.char1 -- po_exhibit_details_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_EXHIBIT_ENTITY
) base,
(SELECT pexd.po_exhibit_details_id,
pexd.draft_id,
po_exhibit_mod_p_lock_attrs(
NVL(pexd.exhibit_description ,G_NULL_CHAR),
NVL(pexd.reference_line_id ,G_NULL_NUM)
) p_lock_attrs
FROM po_exhibit_details_draft pexd,
po_session_gt pogt
WHERE pexd.draft_id = p_draft_id
AND pexd.po_exhibit_details_id = pogt.char1 -- po_exhibit_details_id
AND pogt.index_char1 = G_CHANGED_ENTTIES_ID
AND pogt.index_char2 = G_LOCK_EXHIBIT_ENTITY
) draft
WHERE base.po_exhibit_details_id = draft.po_exhibit_details_id
AND base.p_lock_attrs <> draft.p_lock_attrs;
PO_LOG.stmt(d_module, d_position, 'Number of Records Inserted',SQL%ROWCOUNT);