[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE
FROM po_multi_mod_changes
WHERE multi_mod_request_id = p_multi_mod_request_id
AND ((change_type = 'VENDOR_SITE_CONTACT'
AND (org_id, old_vendor_site_id) NOT IN
(SELECT DISTINCT org_id, vendor_site_id
FROM po_headers_all
WHERE po_header_id IN
(SELECT document_id
FROM po_multi_mod_docs
WHERE multi_mod_request_id = p_multi_mod_request_id)
UNION
SELECT DISTINCT purchasing_org_id org_id, vendor_site_id
FROM po_ga_org_assignments
WHERE po_header_id IN
(SELECT document_id
FROM po_multi_mod_docs
WHERE multi_mod_request_id = p_multi_mod_request_id)))
OR (change_type = 'VENDOR_REMIT_ADDRESS'
AND (org_id,old_remit_to_addr) NOT IN
(SELECT DISTINCT rmt_ofc.n_ext_attr3 org_id, rmt_ofc.n_ext_attr2 remit_to_office
FROM po_headers_all_ext_b rmt_ofc,
ego_attr_groups_v v_ag
WHERE v_ag.attr_group_type = 'PO_HEADER_EXT_ATTRS'
AND v_ag.attr_group_name = 'SUPPLIER_DTLS'
AND v_ag.attr_group_id = rmt_ofc.attr_group_id
AND rmt_ofc.draft_id = -1
AND rmt_ofc.po_header_id IN
(SELECT document_id
FROM po_multi_mod_docs
WHERE multi_mod_request_id = p_multi_mod_request_id)
AND rmt_ofc.n_ext_attr2 IS NOT NULL
AND rmt_ofc.n_ext_attr3 IS NOT NULL)));
DELETE
FROM po_multi_mod_val_results pmmvr
WHERE multi_mod_request_id = p_multi_mod_request_id
AND multi_mod_doc_id IS NOT NULL -- Remove only document-level exceptions
AND NOT EXISTS(
SELECT 1
FROM po_multi_mod_docs
WHERE multi_mod_doc_id = pmmvr.multi_mod_doc_id);
DELETE FROM po_multi_mod_val_results
WHERE multi_mod_request_id = p_multi_mod_request_id;
DELETE FROM po_multi_mod_docs
WHERE multi_mod_request_id = p_multi_mod_request_id;
DELETE FROM po_multi_mod_changes
WHERE multi_mod_request_id = p_multi_mod_request_id;
DELETE FROM po_multi_mod_requests
WHERE multi_mod_request_id = p_multi_mod_request_id;
DELETE FROM po_multi_mod_clause_changes
WHERE multi_mod_request_id = p_multi_mod_request_id;
SELECT 'PURCHASING', TYPE_LOOKUP_CODE, STYLE_ID
INTO l_functional_area_code, l_document_type_code, l_document_style_id
FROM po_headers_all
WHERE PO_HEADER_ID = p_po_header_id;
INSERT INTO po_drafts(
draft_id,
document_id,
owner_user_id,
owner_role,
status,
draft_type,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
VALUES(
l_draft_id,
p_po_header_id,
FND_GLOBAL.user_id,
'BUYER',
'DRAFT',
'MOD',
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
SYSDATE,
FND_GLOBAL.user_id);
p_delete_flag => 'N',
x_record_already_exist => l_record_already_exists
);
SELECT phda.clm_source_document_id
INTO l_clm_source_document_id
FROM po_headers_draft_all phda,
po_headers_all pha
WHERE phda.po_header_id = p_po_header_id
AND phda.draft_id = p_draft_id
AND pha.po_header_id = phda.clm_source_document_id
AND pha.clm_award_type IS NOT NULL;
SELECT distinct ags.ATTR_GROUP_NAME
INTO l_doc_num_ag_name
FROM po_headers_all_ext_b phaeb,
po_uda_ag_template_usages usages,
ego_attr_groups_v ags
WHERE phaeb.po_header_id = p_po_header_id
AND phaeb.draft_id = -1
AND phaeb.attr_group_id = ags.attr_group_id
AND phaeb.attr_group_id = usages.attribute_group_id
AND usages.attribute_category in ('DOCUMENT_NUMBERING', 'CLOSEOUT')
AND usages.template_id = p_template_id;
UPDATE po_headers_all_ext_b
SET c_ext_attr40 = 'PO_UDA_ADDRESS_TYPES'
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id
AND c_ext_attr39 NOT IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE');
SELECT standard_form,
document_format
INTO l_standard_form,
l_document_format
FROM po_print_form_formats
WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
AND default_flag = 'Y'
AND style_id = p_style_id
AND document_type = l_print_doc_type;
SELECT standard_form,
document_format
INTO l_standard_form,
l_document_format
FROM po_print_form_formats
WHERE NVL(inactive_date, SYSDATE+1) > SYSDATE
AND style_id = p_style_id
AND document_type = l_print_doc_type
AND rownum =1;
SELECT po_ame_approvals_s.nextval
INTO l_ame_approval_id
FROM dual;
SELECT mod_ame_transaction_type
INTO l_ame_transaction_type
FROM po_doc_style_headers
WHERE style_id = p_style_id;
debug(d_module, d_position, 'Update PO_DRAFTS with the Mod-specific defaults and Multi-mod fields', FND_API.G_TRUE);
UPDATE po_drafts
SET modification_number = l_doc_number,
clm_standard_form = l_standard_form,
clm_document_format = l_document_format,
mod_effective_date = p_multi_mod_request_record.mod_effective_date,
clm_noofcopies = p_multi_mod_request_record.clm_noofcopies,
clm_contract_officer = p_multi_mod_request_record.clm_contract_officer,
agent_id = p_multi_mod_request_record.agent_id --added as part of conc mod impacts
WHERE draft_id = p_draft_id;
debug(d_module, d_position, 'Update PO_DRAFT_HEADERS_ALL with the Mod-specific defaults and Multi-mod fields', FND_API.G_TRUE);
UPDATE po_headers_draft_all
SET ame_approval_id = l_ame_approval_id,
ame_transaction_type = l_ame_transaction_type,
acceptance_required_flag = p_multi_mod_request_record.acceptance_required_flag,
acceptance_due_date = p_multi_mod_request_record.acceptance_due_date,
clm_contract_officer = p_multi_mod_request_record.clm_contract_officer,
clm_default_dist_flag = l_clm_default_dist,
authorization_status = NULL,
document_creation_method = 'MULTI_MOD',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id;
SELECT extension_id, EGO_EXTFWK_S.NEXTVAL
FROM po_headers_all_ext_b
WHERE po_header_id = p_uda_key_po_header_id
AND draft_id = p_uda_key_draft_id
AND attr_group_id = p_addr_attr_group_id
AND c_ext_attr39 IN ('MOD_ISSUING_OFFICE','MOD_ADMIN_OFFICE');
SELECT attr_group_id
INTO l_addr_attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_type = l_addr_attr_grp_type
AND attr_group_name = l_addr_attr_grp_name;
debug(d_module, d_position, 'Insert into Address Ext B');
INSERT INTO PO_HEADERS_ALL_EXT_B
(EXTENSION_ID
,ATTR_GROUP_ID
,PO_HEADER_ID
,DATA_LEVEL_ID
,PK1_VALUE
,PK2_VALUE
,PK3_VALUE
,PK4_VALUE
,PK5_VALUE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,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
,UDA_TEMPLATE_ID
,DRAFT_ID
)
SELECT
l_new_hdr_ext_id_tbl(i)
,ATTR_GROUP_ID
,p_po_header_id
,DATA_LEVEL_ID
,PK1_VALUE
,PK2_VALUE
,PK3_VALUE
,PK4_VALUE
,PK5_VALUE
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,SYSDATE
,FND_GLOBAL.user_id
,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
,UDA_TEMPLATE_ID
,p_draft_id
FROM PO_HEADERS_ALL_EXT_B
WHERE extension_id = l_old_hdr_ext_id_tbl(i);
debug(d_module, d_position, 'Insert into Address Ext TL');
INSERT INTO PO_HEADERS_ALL_EXT_TL
(EXTENSION_ID
,ATTR_GROUP_ID
,PO_HEADER_ID
,DATA_LEVEL_ID
,PK1_VALUE
,PK2_VALUE
,PK3_VALUE
,PK4_VALUE
,PK5_VALUE
,SOURCE_LANG
,LANGUAGE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
,TL_EXT_ATTR1
,TL_EXT_ATTR2
,TL_EXT_ATTR3
,TL_EXT_ATTR4
,TL_EXT_ATTR5
,TL_EXT_ATTR6
,TL_EXT_ATTR7
,TL_EXT_ATTR8
,TL_EXT_ATTR9
,TL_EXT_ATTR10
,TL_EXT_ATTR11
,TL_EXT_ATTR12
,TL_EXT_ATTR13
,TL_EXT_ATTR14
,TL_EXT_ATTR15
,TL_EXT_ATTR16
,TL_EXT_ATTR17
,TL_EXT_ATTR18
,TL_EXT_ATTR19
,TL_EXT_ATTR20
,TL_EXT_ATTR21
,TL_EXT_ATTR22
,TL_EXT_ATTR23
,TL_EXT_ATTR24
,TL_EXT_ATTR25
,TL_EXT_ATTR26
,TL_EXT_ATTR27
,TL_EXT_ATTR28
,TL_EXT_ATTR29
,TL_EXT_ATTR30
,TL_EXT_ATTR31
,TL_EXT_ATTR32
,TL_EXT_ATTR33
,TL_EXT_ATTR34
,TL_EXT_ATTR35
,TL_EXT_ATTR36
,TL_EXT_ATTR37
,TL_EXT_ATTR38
,TL_EXT_ATTR39
,TL_EXT_ATTR40
,UDA_TEMPLATE_ID
,DRAFT_ID
)
SELECT
l_new_hdr_ext_id_tbl(i)
,ATTR_GROUP_ID
,p_po_header_id
,DATA_LEVEL_ID
,PK1_VALUE
,PK2_VALUE
,PK3_VALUE
,PK4_VALUE
,PK5_VALUE
,SOURCE_LANG
,LANGUAGE
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,SYSDATE
,FND_GLOBAL.user_id
,TL_EXT_ATTR1
,TL_EXT_ATTR2
,TL_EXT_ATTR3
,TL_EXT_ATTR4
,TL_EXT_ATTR5
,TL_EXT_ATTR6
,TL_EXT_ATTR7
,TL_EXT_ATTR8
,TL_EXT_ATTR9
,TL_EXT_ATTR10
,TL_EXT_ATTR11
,TL_EXT_ATTR12
,TL_EXT_ATTR13
,TL_EXT_ATTR14
,TL_EXT_ATTR15
,TL_EXT_ATTR16
,TL_EXT_ATTR17
,TL_EXT_ATTR18
,TL_EXT_ATTR19
,TL_EXT_ATTR20
,TL_EXT_ATTR21
,TL_EXT_ATTR22
,TL_EXT_ATTR23
,TL_EXT_ATTR24
,TL_EXT_ATTR25
,TL_EXT_ATTR26
,TL_EXT_ATTR27
,TL_EXT_ATTR28
,TL_EXT_ATTR29
,TL_EXT_ATTR30
,TL_EXT_ATTR31
,TL_EXT_ATTR32
,TL_EXT_ATTR33
,TL_EXT_ATTR34
,TL_EXT_ATTR35
,TL_EXT_ATTR36
,TL_EXT_ATTR37
,TL_EXT_ATTR38
,TL_EXT_ATTR39
,TL_EXT_ATTR40
,UDA_TEMPLATE_ID
,p_draft_id
FROM PO_HEADERS_ALL_EXT_TL
WHERE extension_id = l_old_hdr_ext_id_tbl(i);
UPDATE po_headers_all_ext_b PHA1
SET(
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) =
(SELECT
Nvl(PHA2.C_EXT_ATTR1, PHA1.C_EXT_ATTR1)
,Nvl(PHA2.C_EXT_ATTR2, PHA1.C_EXT_ATTR2)
,Nvl(PHA2.C_EXT_ATTR3, PHA1.C_EXT_ATTR3)
,Nvl(PHA2.C_EXT_ATTR4, PHA1.C_EXT_ATTR4)
,Nvl(PHA2.C_EXT_ATTR5, PHA1.C_EXT_ATTR5)
,Nvl(PHA2.C_EXT_ATTR6, PHA1.C_EXT_ATTR6)
,Nvl(PHA2.C_EXT_ATTR7, PHA1.C_EXT_ATTR7)
,Nvl(PHA2.C_EXT_ATTR8, PHA1.C_EXT_ATTR8)
,Nvl(PHA2.C_EXT_ATTR9, PHA1.C_EXT_ATTR9)
,Nvl(PHA2.C_EXT_ATTR10, PHA1.C_EXT_ATTR10)
,Nvl(PHA2.C_EXT_ATTR11, PHA1.C_EXT_ATTR11)
,Nvl(PHA2.C_EXT_ATTR12, PHA1.C_EXT_ATTR12)
,Nvl(PHA2.C_EXT_ATTR13, PHA1.C_EXT_ATTR13)
,Nvl(PHA2.C_EXT_ATTR14, PHA1.C_EXT_ATTR14)
,Nvl(PHA2.C_EXT_ATTR15, PHA1.C_EXT_ATTR15)
,Nvl(PHA2.C_EXT_ATTR16, PHA1.C_EXT_ATTR16)
,Nvl(PHA2.C_EXT_ATTR17, PHA1.C_EXT_ATTR17)
,Nvl(PHA2.C_EXT_ATTR18, PHA1.C_EXT_ATTR18)
,Nvl(PHA2.C_EXT_ATTR19, PHA1.C_EXT_ATTR19)
,Nvl(PHA2.C_EXT_ATTR20, PHA1.C_EXT_ATTR20)
,Nvl(PHA2.C_EXT_ATTR21, PHA1.C_EXT_ATTR21)
,Nvl(PHA2.C_EXT_ATTR22, PHA1.C_EXT_ATTR22)
,Nvl(PHA2.C_EXT_ATTR23, PHA1.C_EXT_ATTR23)
,Nvl(PHA2.C_EXT_ATTR24, PHA1.C_EXT_ATTR24)
,Nvl(PHA2.C_EXT_ATTR25, PHA1.C_EXT_ATTR25)
,Nvl(PHA2.C_EXT_ATTR26, PHA1.C_EXT_ATTR26)
,Nvl(PHA2.C_EXT_ATTR27, PHA1.C_EXT_ATTR27)
,Nvl(PHA2.C_EXT_ATTR28, PHA1.C_EXT_ATTR28)
,Nvl(PHA2.C_EXT_ATTR29, PHA1.C_EXT_ATTR29)
,Nvl(PHA2.C_EXT_ATTR30, PHA1.C_EXT_ATTR30)
,Nvl(PHA2.C_EXT_ATTR31, PHA1.C_EXT_ATTR31)
,Nvl(PHA2.C_EXT_ATTR32, PHA1.C_EXT_ATTR32)
,Nvl(PHA2.C_EXT_ATTR33, PHA1.C_EXT_ATTR33)
,Nvl(PHA2.C_EXT_ATTR34, PHA1.C_EXT_ATTR34)
,Nvl(PHA2.C_EXT_ATTR35, PHA1.C_EXT_ATTR35)
,Nvl(PHA2.C_EXT_ATTR36, PHA1.C_EXT_ATTR36)
,Nvl(PHA2.C_EXT_ATTR37, PHA1.C_EXT_ATTR37)
,Nvl(PHA2.C_EXT_ATTR38, PHA1.C_EXT_ATTR38)
,Nvl(PHA2.C_EXT_ATTR39, PHA1.C_EXT_ATTR39)
,PHA1.C_EXT_ATTR40 -- keeping value which is on base document.
--,l_mod_uda_address_types -- The Address Type for the Mod is always PO_MOD_UDA_ADDRESS_TYPES
,Nvl(PHA2.N_EXT_ATTR1, PHA1.N_EXT_ATTR1)
,Nvl(PHA2.N_EXT_ATTR2, PHA1.N_EXT_ATTR2)
,Nvl(PHA2.N_EXT_ATTR3, PHA1.N_EXT_ATTR3)
,Nvl(PHA2.N_EXT_ATTR4, PHA1.N_EXT_ATTR4)
,Nvl(PHA2.N_EXT_ATTR5, PHA1.N_EXT_ATTR5)
,Nvl(PHA2.N_EXT_ATTR6, PHA1.N_EXT_ATTR6)
,Nvl(PHA2.N_EXT_ATTR7, PHA1.N_EXT_ATTR7)
,Nvl(PHA2.N_EXT_ATTR8, PHA1.N_EXT_ATTR8)
,Nvl(PHA2.N_EXT_ATTR9, PHA1.N_EXT_ATTR9)
,Nvl(PHA2.N_EXT_ATTR10, PHA1.N_EXT_ATTR10)
,Nvl(PHA2.N_EXT_ATTR11, PHA1.N_EXT_ATTR11)
,Nvl(PHA2.N_EXT_ATTR12, PHA1.N_EXT_ATTR12)
,Nvl(PHA2.N_EXT_ATTR13, PHA1.N_EXT_ATTR13)
,Nvl(PHA2.N_EXT_ATTR14, PHA1.N_EXT_ATTR14)
,Nvl(PHA2.N_EXT_ATTR15, PHA1.N_EXT_ATTR15)
,Nvl(PHA2.N_EXT_ATTR16, PHA1.N_EXT_ATTR16)
,Nvl(PHA2.N_EXT_ATTR17, PHA1.N_EXT_ATTR17)
,Nvl(PHA2.N_EXT_ATTR18, PHA1.N_EXT_ATTR18)
,Nvl(PHA2.N_EXT_ATTR19, PHA1.N_EXT_ATTR19)
,Nvl(PHA2.N_EXT_ATTR20, PHA1.N_EXT_ATTR20)
,Nvl(PHA2.UOM_EXT_ATTR1, PHA1.UOM_EXT_ATTR1)
,Nvl(PHA2.UOM_EXT_ATTR2, PHA1.UOM_EXT_ATTR2)
,Nvl(PHA2.UOM_EXT_ATTR3, PHA1.UOM_EXT_ATTR3)
,Nvl(PHA2.UOM_EXT_ATTR4, PHA1.UOM_EXT_ATTR4)
,Nvl(PHA2.UOM_EXT_ATTR5, PHA1.UOM_EXT_ATTR5)
,Nvl(PHA2.UOM_EXT_ATTR6, PHA1.UOM_EXT_ATTR6)
,Nvl(PHA2.UOM_EXT_ATTR7, PHA1.UOM_EXT_ATTR7)
,Nvl(PHA2.UOM_EXT_ATTR8, PHA1.UOM_EXT_ATTR8)
,Nvl(PHA2.UOM_EXT_ATTR9, PHA1.UOM_EXT_ATTR9)
,Nvl(PHA2.UOM_EXT_ATTR10, PHA1.UOM_EXT_ATTR10)
,Nvl(PHA2.UOM_EXT_ATTR11, PHA1.UOM_EXT_ATTR11)
,Nvl(PHA2.UOM_EXT_ATTR12, PHA1.UOM_EXT_ATTR12)
,Nvl(PHA2.UOM_EXT_ATTR13, PHA1.UOM_EXT_ATTR13)
,Nvl(PHA2.UOM_EXT_ATTR14, PHA1.UOM_EXT_ATTR14)
,Nvl(PHA2.UOM_EXT_ATTR15, PHA1.UOM_EXT_ATTR15)
,Nvl(PHA2.UOM_EXT_ATTR16, PHA1.UOM_EXT_ATTR16)
,Nvl(PHA2.UOM_EXT_ATTR17, PHA1.UOM_EXT_ATTR17)
,Nvl(PHA2.UOM_EXT_ATTR18, PHA1.UOM_EXT_ATTR18)
,Nvl(PHA2.UOM_EXT_ATTR19, PHA1.UOM_EXT_ATTR19)
,Nvl(PHA2.UOM_EXT_ATTR20, PHA1.UOM_EXT_ATTR20)
,Nvl(PHA2.D_EXT_ATTR1, PHA1.D_EXT_ATTR1)
,Nvl(PHA2.D_EXT_ATTR2, PHA1.D_EXT_ATTR2)
,Nvl(PHA2.D_EXT_ATTR3, PHA1.D_EXT_ATTR3)
,Nvl(PHA2.D_EXT_ATTR4, PHA1.D_EXT_ATTR4)
,Nvl(PHA2.D_EXT_ATTR5, PHA1.D_EXT_ATTR5)
,Nvl(PHA2.D_EXT_ATTR6, PHA1.D_EXT_ATTR6)
,Nvl(PHA2.D_EXT_ATTR7, PHA1.D_EXT_ATTR7)
,Nvl(PHA2.D_EXT_ATTR8, PHA1.D_EXT_ATTR8)
,Nvl(PHA2.D_EXT_ATTR9, PHA1.D_EXT_ATTR9)
,Nvl(PHA2.D_EXT_ATTR10, PHA1.D_EXT_ATTR10)
FROM po_headers_all_ext_b PHA2
WHERE PHA2.po_header_id = p_uda_key_po_header_id
AND PHA2.draft_id = p_uda_key_draft_id
AND PHA2.attr_group_id = l_addr_attr_group_id
AND PHA1.attr_group_id = PHA2.attr_group_id
AND PHA1.c_ext_attr39 = PHA2.c_ext_attr39)
WHERE PHA1.po_header_id = p_po_header_id
AND PHA1.draft_id = p_draft_id
AND PHA1.attr_group_id = l_addr_attr_group_id;
UPDATE po_headers_all_ext_tl PHAT1
SET(
TL_EXT_ATTR1
,TL_EXT_ATTR2
,TL_EXT_ATTR3
,TL_EXT_ATTR4
,TL_EXT_ATTR5
,TL_EXT_ATTR6
,TL_EXT_ATTR7
,TL_EXT_ATTR8
,TL_EXT_ATTR9
,TL_EXT_ATTR10
,TL_EXT_ATTR11
,TL_EXT_ATTR12
,TL_EXT_ATTR13
,TL_EXT_ATTR14
,TL_EXT_ATTR15
,TL_EXT_ATTR16
,TL_EXT_ATTR17
,TL_EXT_ATTR18
,TL_EXT_ATTR19
,TL_EXT_ATTR20
,TL_EXT_ATTR21
,TL_EXT_ATTR22
,TL_EXT_ATTR23
,TL_EXT_ATTR24
,TL_EXT_ATTR25
,TL_EXT_ATTR26
,TL_EXT_ATTR27
,TL_EXT_ATTR28
,TL_EXT_ATTR29
,TL_EXT_ATTR30
,TL_EXT_ATTR31
,TL_EXT_ATTR32
,TL_EXT_ATTR33
,TL_EXT_ATTR34
,TL_EXT_ATTR35
,TL_EXT_ATTR36
,TL_EXT_ATTR37
,TL_EXT_ATTR38
,TL_EXT_ATTR39
,TL_EXT_ATTR40
) =
(SELECT
Nvl(PHAT2.TL_EXT_ATTR1, PHAT1.TL_EXT_ATTR1)
,Nvl(PHAT2.TL_EXT_ATTR2, PHAT1.TL_EXT_ATTR2)
,Nvl(PHAT2.TL_EXT_ATTR3, PHAT1.TL_EXT_ATTR3)
,Nvl(PHAT2.TL_EXT_ATTR4, PHAT1.TL_EXT_ATTR4)
,Nvl(PHAT2.TL_EXT_ATTR5, PHAT1.TL_EXT_ATTR5)
,Nvl(PHAT2.TL_EXT_ATTR6, PHAT1.TL_EXT_ATTR6)
,Nvl(PHAT2.TL_EXT_ATTR7, PHAT1.TL_EXT_ATTR7)
,Nvl(PHAT2.TL_EXT_ATTR8, PHAT1.TL_EXT_ATTR8)
,Nvl(PHAT2.TL_EXT_ATTR9, PHAT1.TL_EXT_ATTR9)
,Nvl(PHAT2.TL_EXT_ATTR10, PHAT1.TL_EXT_ATTR10)
,Nvl(PHAT2.TL_EXT_ATTR11, PHAT1.TL_EXT_ATTR11)
,Nvl(PHAT2.TL_EXT_ATTR12, PHAT1.TL_EXT_ATTR12)
,Nvl(PHAT2.TL_EXT_ATTR13, PHAT1.TL_EXT_ATTR13)
,Nvl(PHAT2.TL_EXT_ATTR14, PHAT1.TL_EXT_ATTR14)
,Nvl(PHAT2.TL_EXT_ATTR15, PHAT1.TL_EXT_ATTR15)
,Nvl(PHAT2.TL_EXT_ATTR16, PHAT1.TL_EXT_ATTR16)
,Nvl(PHAT2.TL_EXT_ATTR17, PHAT1.TL_EXT_ATTR17)
,Nvl(PHAT2.TL_EXT_ATTR18, PHAT1.TL_EXT_ATTR18)
,Nvl(PHAT2.TL_EXT_ATTR19, PHAT1.TL_EXT_ATTR19)
,Nvl(PHAT2.TL_EXT_ATTR20, PHAT1.TL_EXT_ATTR20)
,Nvl(PHAT2.TL_EXT_ATTR21, PHAT1.TL_EXT_ATTR21)
,Nvl(PHAT2.TL_EXT_ATTR22, PHAT1.TL_EXT_ATTR22)
,Nvl(PHAT2.TL_EXT_ATTR23, PHAT1.TL_EXT_ATTR23)
,Nvl(PHAT2.TL_EXT_ATTR24, PHAT1.TL_EXT_ATTR24)
,Nvl(PHAT2.TL_EXT_ATTR25, PHAT1.TL_EXT_ATTR25)
,Nvl(PHAT2.TL_EXT_ATTR26, PHAT1.TL_EXT_ATTR26)
,Nvl(PHAT2.TL_EXT_ATTR27, PHAT1.TL_EXT_ATTR27)
,Nvl(PHAT2.TL_EXT_ATTR28, PHAT1.TL_EXT_ATTR28)
,Nvl(PHAT2.TL_EXT_ATTR29, PHAT1.TL_EXT_ATTR29)
,Nvl(PHAT2.TL_EXT_ATTR30, PHAT1.TL_EXT_ATTR30)
,Nvl(PHAT2.TL_EXT_ATTR31, PHAT1.TL_EXT_ATTR31)
,Nvl(PHAT2.TL_EXT_ATTR32, PHAT1.TL_EXT_ATTR32)
,Nvl(PHAT2.TL_EXT_ATTR33, PHAT1.TL_EXT_ATTR33)
,Nvl(PHAT2.TL_EXT_ATTR34, PHAT1.TL_EXT_ATTR34)
,Nvl(PHAT2.TL_EXT_ATTR35, PHAT1.TL_EXT_ATTR35)
,Nvl(PHAT2.TL_EXT_ATTR36, PHAT1.TL_EXT_ATTR36)
,Nvl(PHAT2.TL_EXT_ATTR37, PHAT1.TL_EXT_ATTR37)
,Nvl(PHAT2.TL_EXT_ATTR38, PHAT1.TL_EXT_ATTR38)
,Nvl(PHAT2.TL_EXT_ATTR39, PHAT1.TL_EXT_ATTR39)
,Nvl(PHAT2.TL_EXT_ATTR40, PHAT1.TL_EXT_ATTR40)
FROM po_headers_all_ext_tl PHAT2
WHERE PHAT2.po_header_id = p_uda_key_po_header_id
AND PHAT2.draft_id = p_uda_key_draft_id
AND PHAT2.attr_group_id = l_addr_attr_group_id
AND PHAT1.attr_group_id = PHAT2.attr_group_id
AND PHAT1.tl_ext_attr39 = PHAT2.tl_ext_attr39
AND PHAT1.language = PHAT2.language)
WHERE PHAT1.po_header_id = p_po_header_id
AND PHAT1.draft_id = p_draft_id
AND PHAT1.attr_group_id = l_addr_attr_group_id;
SELECT etd.edi_flag
INTO x_edi_flag
FROM ece_tp_details etd,
po_vendor_sites_all pvs
WHERE pvs.tp_header_id = etd.tp_header_id
AND etd.document_id = 'POO'
AND pvs.vendor_site_id = p_vendor_site_id
AND etd.document_type = p_doc_sub_type;
SELECT supplier_notif_method,
email_address,
fax_area_code,
fax
INTO x_default_method,
x_email_address,
l_fax_area,
l_faxnum
FROM po_vendor_sites_all
WHERE vendor_site_id = p_vendor_site_id;
SELECT DECODE(l_po_comm_email_default,
'SITE', x_email_address,
NVL(email_address, x_email_address)),
Nvl(fax_area_code, l_fax_area),
Nvl(fax, l_faxnum)
INTO x_email_address,
l_fax_area,
l_faxnum
FROM po_vendor_contacts
WHERE vendor_contact_id = p_vendor_contact_id
AND vendor_site_id = p_vendor_site_id;
SELECT end_user_column_name
FROM fnd_descr_flex_column_usages
WHERE descriptive_flexfield_name = l_entity_code
AND descriptive_flex_context_code = l_supplier_attr_grp;
SELECT attr_group_id
INTO l_sup_attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_type = l_entity_code
AND attr_group_name = l_supplier_attr_grp;
debug(d_module, d_position, 'Calling ' || d_called_api || ' to populate the updated Supplier Attrs', FND_API.G_TRUE);
SELECT org_id,
type_lookup_code,
vendor_id,
vendor_site_id,
vendor_contact_id
INTO l_org_id,
l_doc_subtype,
l_old_vendor_id,
l_old_vendor_site_id,
l_old_vendor_contact_id
FROM po_headers_draft_all
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id;
SELECT new_vendor_id, new_vendor_name
INTO l_new_vendor_id, l_new_vendor_name
FROM po_multi_mod_changes
WHERE multi_mod_request_id = p_multi_mod_request_id
AND change_type = 'VENDOR'
AND old_vendor_id = l_old_vendor_id;
SELECT new_vendor_site_id, new_vendor_contact_id
INTO l_new_vendor_site_id, l_new_vendor_contact_id
FROM po_multi_mod_changes
WHERE multi_mod_request_id = p_multi_mod_request_id
AND change_type = 'VENDOR_SITE_CONTACT'
AND org_id = l_org_id
AND old_vendor_site_id = l_old_vendor_site_id;
SELECT new_remit_to_addr
INTO l_new_remit_to_addr
FROM po_multi_mod_changes
WHERE multi_mod_request_id = p_multi_mod_request_id
AND change_type = 'VENDOR_REMIT_ADDRESS'
AND old_remit_to_addr = l_old_remit_to_addr
AND org_id = l_org_id;
SELECT vendor_site_code
INTO l_new_vendor_site_name
FROM po_vendor_sites_all
WHERE vendor_site_id = l_new_vendor_site_id;
UPDATE po_headers_draft_all
SET vendor_id = l_new_vendor_id,
vendor_site_id = l_new_vendor_site_id,
vendor_contact_id = l_new_vendor_contact_id,
--clm_supplier_name = l_new_vendor_name,
--clm_supplier_site_name = l_new_vendor_site_name,
fax = l_fax_number,
email_address = l_email_address,
supplier_notif_method = l_default_method,
xml_flag = l_xml_flag,
edi_processed_flag = Decode(l_xml_flag, 'Y', 'N', NULL)
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id;
INSERT INTO po_ga_org_assign_draft(
draft_id,
delete_flag,
change_accepted_flag,
change_status,
po_header_id,
organization_id,
enabled_flag,
vendor_site_id,
purchasing_org_id,
org_assignment_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
SELECT
p_draft_id,
NULL, -- delete_flag,
NULL, -- change_accepted_flag,
'UPDATE',
PGOA.po_header_id,
PGOA.organization_id,
PGOA.enabled_flag,
PMMC.new_vendor_site_id,
PGOA.purchasing_org_id,
PGOA.org_assignment_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
SYSDATE,
FND_GLOBAL.user_id
FROM po_ga_org_assignments PGOA,
po_multi_mod_changes PMMC
WHERE PGOA.po_header_id = p_po_header_id
AND PMMC.org_id = PGOA.purchasing_org_id
AND PMMC.old_vendor_site_id = PGOA.vendor_site_id
AND PMMC.change_type = 'VENDOR_SITE_CONTACT'
AND PMMC.multi_mod_request_id = p_multi_mod_request_id;
SELECT *
INTO l_headers_draft_rec
FROM po_headers_draft_all
WHERE po_header_id = p_po_header_id
AND draft_id = p_draft_id;
SELECT Decode(multi_mod_request_type, l_change_type_vendor, 'Y', 'N')
INTO l_org_assign_change_flag
FROM po_multi_mod_requests
WHERE multi_mod_request_id = p_multi_mod_request_id;
l_communicate_price_change := l_headers_draft_rec.retro_price_apply_updates_flag;
UpdateSourcingRule => l_headers_draft_rec.update_sourcing_rules_flag,
MassUpdateReleases => l_headers_draft_rec.retro_price_apply_updates_flag,
RetroactivePriceChange => 'N',
OrgAssignChange => l_org_assign_change_flag,
CommunicatePriceChange => l_communicate_price_change,
p_Background_Flag => 'N',
p_Initiator => NULL,
p_xml_flag => l_headers_draft_rec.xml_flag,
FpdsngFlag => 'N',
p_source_type_code => NULL,
DraftID => p_draft_id,
p_bypass_checks_flag => 'N' --hard-coded in case of Multi-Mod
);
l_clause_updated_yn VARCHAR2(1);
SELECT *
INTO l_multi_mod_request_record
FROM po_multi_mod_requests
WHERE multi_mod_request_id = p_multi_mod_request_id;
SELECT uda_template_id,
style_id,
type_lookup_code,
org_id
INTO l_template_id,
l_style_id,
l_doc_subtype,
l_org_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT conc_mods_enabled_flag
INTO l_conc_mods_enabled_flag
FROM po_doc_style_headers
WHERE style_id = l_style_id;
SELECT 'Y'
INTO l_mod_exists
FROM dual
WHERE EXISTS (SELECT 'Mod Exists'
FROM po_drafts
WHERE document_id = p_po_header_id
AND status IN ('DRAFT', 'IN PROCESS', 'REJECTED', 'SUPPLIER SIGN', 'PRE-APPROVED'));
debug(d_module, d_position, 'Update Multi-Mod documents in po_multi_mod_docs with the new draft_id: ' || l_draft_id);
UPDATE po_multi_mod_docs
SET draft_id = l_draft_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE multi_mod_request_id = p_multi_mod_request_id
AND document_id = p_po_header_id;
SELECT attr_group_id
INTO l_addr_attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_type = 'PO_HEADER_EXT_ATTRS'
AND attr_group_name = 'addresses';
SELECT ext.c_ext_attr39,
NVL(SUM(NVL(n_ext_attr1,0) + NVL(n_ext_attr2,0) + NVL(n_ext_attr3,0) +
NVL(n_ext_attr4,0) + NVL(n_ext_attr5,0) + NVL(n_ext_attr6,0) +
NVL(n_ext_attr7,0) + NVL(n_ext_attr8,0) + NVL(n_ext_attr9,0) +
NVL(n_ext_attr10,0) + NVL(n_ext_attr11,0) + NVL(n_ext_attr12,0) +
NVL(n_ext_attr13,0) + NVL(n_ext_attr14,0) + NVL(n_ext_attr15,0) +
NVL(n_ext_attr16,0) + NVL(n_ext_attr17,0) + NVL(n_ext_attr18,0) +
NVL(n_ext_attr19,0) + NVL(n_ext_attr20,0)),0) attr_sum
BULK COLLECT INTO l_entity_pk2, l_sum_check_tbl
FROM po_headers_all_ext_b ext,
po_multi_mod_requests multimod
WHERE ext.po_header_id = multimod.uda_key_po_header_id
AND ext.draft_id = multimod.uda_key_draft_id
AND ext.attr_group_id = l_addr_attr_group_id
AND multimod.multi_mod_request_id = p_multi_mod_request_id
AND ext.c_ext_attr39 NOT IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE')
GROUP BY ext.c_ext_attr39;
select conc_mods_enabled_flag into l_conc_mod_enabled from po_doc_style_headers where style_id = l_style_id;
x_updated_yn => l_clause_updated_yn,
x_return_status => x_return_status,
p_locking_enabled_yn => l_conc_mod_enabled);
AND (p_clause_change_flag = 'N' OR l_clause_updated_yn = 'N')) THEN
d_position := 80;
debug(d_module, d_position, 'FPDS-NG Reporting is Enabled. Inserting defaut Car Records', FND_API.G_TRUE);
PO_CLM_CAR_UTIL.insert_mod_default_car(
p_po_header_id => p_po_header_id,
p_draft_id => l_draft_id,
p_invoked_from => 'MULTI_MOD',
p_rel_without_rpt_reason => l_multi_mod_request_record.exemption_reason
);
SELECT MULTI_MOD_REQUEST_ID
INTO l_multi_mod_req_id
FROM PO_MULTI_MOD_DOCS
WHERE DRAFT_ID = l_draft_id;
SELECT MULTI_MOD_REQUEST_TYPE
INTO l_multi_mod_req_type
FROM PO_MULTI_MOD_REQUESTS
WHERE MULTI_MOD_REQUEST_ID = l_multi_mod_req_id;
DELETE FROM po_asl_documents
WHERE DOCUMENT_HEADER_ID = l_document_id;