[Home] [Help]
83: WHERE multi_mod_request_id = p_multi_mod_request_id)))
84: OR (change_type = 'VENDOR_REMIT_ADDRESS'
85: AND (org_id,old_remit_to_addr) NOT IN
86: (SELECT DISTINCT rmt_ofc.n_ext_attr3 org_id, rmt_ofc.n_ext_attr2 remit_to_office
87: FROM po_headers_all_ext_b rmt_ofc,
88: ego_attr_groups_v v_ag
89: WHERE v_ag.attr_group_type = 'PO_HEADER_EXT_ATTRS'
90: AND v_ag.attr_group_name = 'SUPPLIER_DTLS'
91: AND v_ag.attr_group_id = rmt_ofc.attr_group_id
478:
479: BEGIN
480: SELECT distinct ags.ATTR_GROUP_NAME
481: INTO l_doc_num_ag_name
482: FROM po_headers_all_ext_b phaeb,
483: po_uda_ag_template_usages usages,
484: ego_attr_groups_v ags
485: WHERE phaeb.po_header_id = p_po_header_id
486: AND phaeb.draft_id = -1
548: should be PO_UDA_ADDRESS_TYPES. When modification is created from BWC, award related address fields
549: have value PO_UDA_ADDRESS_TYPES.
550: */
551:
552: UPDATE po_headers_all_ext_b
553: SET c_ext_attr40 = 'PO_UDA_ADDRESS_TYPES'
554: WHERE po_header_id = p_po_header_id
555: AND draft_id = p_draft_id
556: AND c_ext_attr39 NOT IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE');
777:
778: -- Cursor for fetching the extension id's against 'MOD_ISSUING_OFFICE','MOD_ADMIN_OFFICE'
779: CURSOR po_headers_ext_cursor(p_addr_attr_group_id NUMBER) IS
780: SELECT extension_id, EGO_EXTFWK_S.NEXTVAL
781: FROM po_headers_all_ext_b
782: WHERE po_header_id = p_uda_key_po_header_id
783: AND draft_id = p_uda_key_draft_id
784: AND attr_group_id = p_addr_attr_group_id
785: AND c_ext_attr39 IN ('MOD_ISSUING_OFFICE','MOD_ADMIN_OFFICE');
814: OPEN po_headers_ext_cursor(l_addr_attr_group_id);
815: FETCH po_headers_ext_cursor BULK COLLECT INTO l_old_hdr_ext_id_tbl, l_new_hdr_ext_id_tbl;
816: CLOSE po_headers_ext_cursor;
817:
818: -- Start Insert into PO_HEADERS_ALL_EXT_B
819: d_position := 30;
820: debug(d_module, d_position, 'Insert into Address Ext B');
821:
822: FORALL i IN 1..l_old_hdr_ext_id_tbl.Count
819: d_position := 30;
820: debug(d_module, d_position, 'Insert into Address Ext B');
821:
822: FORALL i IN 1..l_old_hdr_ext_id_tbl.Count
823: INSERT INTO PO_HEADERS_ALL_EXT_B
824: (EXTENSION_ID
825: ,ATTR_GROUP_ID
826: ,PO_HEADER_ID
827: ,DATA_LEVEL_ID
1034: ,D_EXT_ATTR9
1035: ,D_EXT_ATTR10
1036: ,UDA_TEMPLATE_ID
1037: ,p_draft_id
1038: FROM PO_HEADERS_ALL_EXT_B
1039: WHERE extension_id = l_old_hdr_ext_id_tbl(i);
1040: -- End PO_HEADERS_ALL_EXT_B
1041:
1042: -- Start PO_HEADERS_ALL_EXT_TL
1036: ,UDA_TEMPLATE_ID
1037: ,p_draft_id
1038: FROM PO_HEADERS_ALL_EXT_B
1039: WHERE extension_id = l_old_hdr_ext_id_tbl(i);
1040: -- End PO_HEADERS_ALL_EXT_B
1041:
1042: -- Start PO_HEADERS_ALL_EXT_TL
1043: d_position := 40;
1044: debug(d_module, d_position, 'Insert into Address Ext TL');
1177: d_position := 60;
1178: debug(d_module, d_position, 'Merge records in Address B Table');
1179: -- Update Headers Ext Table with addresses from the Multi-Mod (stored against
1180: -- uda_key_po_header_id, uda_key_draft_id) if not null
1181: UPDATE po_headers_all_ext_b PHA1
1182: SET(
1183: C_EXT_ATTR1
1184: ,C_EXT_ATTR2
1185: ,C_EXT_ATTR3
1361: ,Nvl(PHA2.D_EXT_ATTR7, PHA1.D_EXT_ATTR7)
1362: ,Nvl(PHA2.D_EXT_ATTR8, PHA1.D_EXT_ATTR8)
1363: ,Nvl(PHA2.D_EXT_ATTR9, PHA1.D_EXT_ATTR9)
1364: ,Nvl(PHA2.D_EXT_ATTR10, PHA1.D_EXT_ATTR10)
1365: FROM po_headers_all_ext_b PHA2
1366: WHERE PHA2.po_header_id = p_uda_key_po_header_id
1367: AND PHA2.draft_id = p_uda_key_draft_id
1368: AND PHA2.attr_group_id = l_addr_attr_group_id
1369: AND PHA1.attr_group_id = PHA2.attr_group_id
2740: NVL(n_ext_attr13,0) + NVL(n_ext_attr14,0) + NVL(n_ext_attr15,0) +
2741: NVL(n_ext_attr16,0) + NVL(n_ext_attr17,0) + NVL(n_ext_attr18,0) +
2742: NVL(n_ext_attr19,0) + NVL(n_ext_attr20,0)),0) attr_sum
2743: BULK COLLECT INTO l_entity_pk2, l_sum_check_tbl
2744: FROM po_headers_all_ext_b ext,
2745: po_multi_mod_requests multimod
2746: WHERE ext.po_header_id = multimod.uda_key_po_header_id
2747: AND ext.draft_id = multimod.uda_key_draft_id
2748: AND ext.attr_group_id = l_addr_attr_group_id