[Home] [Help]
23: --Start of Comments
24: --Name: POPULATE_STRUCTURES
25: --Procedure:
26: -- This procedure populates the structures EGO_COL_NAME_VALUE_PAIR_ARRAY, EGO_USER_ATTR_DATA_TABLE,
27: -- EGO_USER_ATTR_ROW_TABLE, EGO_COL_NAME_VALUE_PAIR_ARRAY from the data in the interface table PO_UDA_INTERFACE
28: --Parameters:
29: --IN:
30: -- 1.p_transaction_id
31: -- Unique id of the transaction
163: ,CREATED_BY_NAME
164: ,LAST_UPDATED_BY_NAME
165: ,INTERFACE_SOURCE_CODE
166: ,INTERFACE_SOURCE_LINE_ID
167: FROM PO_UDA_INTERFACE
168: WHERE PROCESS_STATUS = PO_UDA_IMPORT_PKG.IN_PROGRESS
169: AND NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
170: AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
171: AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
201: BEGIN
202: d_progress := 30;
203: SELECT INTERFACE_REFERENCE_ID
204: INTO g_interface_reference_id
205: FROM PO_UDA_INTERFACE
206: WHERE NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
207: AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
208: AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
209: AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
495: EXIT WHEN cur_uda_attributes%NOTFOUND;
496:
497: d_progress := 110;
498: BEGIN
499: SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
500: INTO l_user_row_identifier
501: FROM DUAL;
502: d_progress := 110;
503: PO_UDA_DEFAULTING_PKG.DEBUG('l_user_row_identifier : ' || l_user_row_identifier, d_progress);
868: PO_UDA_DEFAULTING_PKG.DEBUG('Usage type is ADDRESS', d_progress);
869: FOR cur_address_rec IN cur_address(p_address_lookup_type, l_use_mod_addr_type) LOOP
870: BEGIN
871: d_progress := 400;
872: SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
873: INTO l_row_identifier
874: FROM DUAL;
875:
876: d_progress := 410;
911: EXIT WHEN cur_uda_attributes%NOTFOUND;
912:
913: BEGIN
914: d_progress := 460;
915: SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
916: INTO l_user_row_identifier
917: FROM DUAL;
918:
919: d_progress := 470;
960: ELSE -- FOR NON ADDRESS AGS
961: d_progress := 570;
962: BEGIN
963: d_progress := 580;
964: SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
965: INTO l_row_identifier
966: FROM DUAL;
967: d_progress := 590;
968: PO_UDA_DEFAULTING_PKG.DEBUG('l_row_identifier : ' || l_row_identifier, d_progress);
1003:
1004: d_progress := 640;
1005: BEGIN
1006: d_progress := 650;
1007: SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
1008: INTO l_user_row_identifier
1009: FROM DUAL;
1010: d_progress := 660;
1011: PO_UDA_DEFAULTING_PKG.DEBUG('l_user_row_identifier : ' || l_user_row_identifier, d_progress);
2161: )
2162: IS
2163: BEGIN
2164: IF p_process_status IN (PO_UDA_IMPORT_PKG.PENDING, PO_UDA_IMPORT_PKG.IN_PROGRESS, PO_UDA_IMPORT_PKG.COMPLETED) THEN
2165: UPDATE PO_UDA_INTERFACE
2166: SET PROCESS_STATUS = p_process_status
2167: ,PROCESS_PHASE = p_process_phase
2168: WHERE NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
2169: AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
2180: --------------------------------------------------------------------------------------------------------------------
2181: --Start of Comments
2182: --Name: TRANSFER_UDA_DATA
2183: --Procedure:
2184: -- This procedure imports data from po_uda_interface table to ext table
2185: -- This method used in MOD API flow
2186: --Parameters:
2187: --IN:
2188: -- 1. p_header_id
2231: l_quant NUMBER;
2232: Cursor c_uda_intf(p_transaction_id number) IS
2233:
2234: SELECT pk1_value, pk2_value, attr_group_type
2235: FROM po_uda_interface pui
2236: WHERE transaction_id = p_transaction_id
2237: AND attr_group_type IN ('PO_HEADER_EXT_ATTRS', 'PO_LINE_EXT_ATTRS','PO_SHIPMENTS_EXT_ATTRS' ) ;
2238:
2239: BEGIN
2278: IF (PO_LOG.d_stmt) THEN
2279: PO_LOG.stmt(d_module, d_position, 'inside loop');
2280: END IF;
2281:
2282: --Call import function to import data into po_uda_interface table
2283: IMPORT_UDA_ATTRS( p_attr_grp_type => l_attr_group_type_tbl(i), --ATTR_GROUP_TYPE
2284: p_pk1_value => l_uda_pk1_tbl(i), -- po_header_id in case of header level attr, po_line_id in case of line level attr,
2285: p_pk2_value => l_uda_pk2_tbl(i), -- draft_id
2286: x_po_uda_usage_object_array => x_po_uda_usage_object_array,
2337:
2338: --Fetch complex pricing related data
2339: SELECT DISTINCT attr_group_id, pk1_value , pk2_value , uda_template_id
2340: BULK collect INTO l_attr_group_id_tbl , l_po_lines_tbl, l_draft_id_tbl, l_template_id_tbl
2341: FROM po_uda_interface
2342: WHERE ATTR_GROUP_TYPE = 'PO_LINE_EXT_ATTRS'
2343: AND transaction_id = p_transaction_id;
2344:
2345: FOR i IN 1..l_attr_group_id_tbl.Count()
3368: l_attr_group_typ_tbl(3):= 'PO_SHIPMENT_EXT_ATTRS';
3369:
3370: SELECT DISTINCT pk1_value
3371: BULK COLLECT INTO l_pk1_value_tbl
3372: FROM po_uda_interface
3373: WHERE transaction_id = p_transaction_id;
3374:
3375: FOR i IN 1..l_pk1_value_tbl.Count
3376: LOOP
3379:
3380: SELECT ATTR_GROUP_NAME
3381: BULK COLLECT INTO l_attr_grp_name_tbl
3382: FROM (SELECT DISTINCT ATTR_GROUP_NAME
3383: FROM po_uda_interface
3384: WHERE ATTR_GROUP_TYPE = l_attr_group_typ_tbl(j)
3385: AND transaction_id = p_transaction_id
3386: AND pk1_value = l_pk1_value_tbl(i));
3387:
3400:
3401: MINUS
3402:
3403: SELECT attr_name
3404: FROM po_uda_interface
3405: WHERE transaction_id = p_transaction_id
3406: AND ATTR_GROUP_TYPE = l_attr_group_typ_tbl(j)
3407: AND ATTR_GROUP_NAME = l_attr_grp_name_tbl(k)
3408: AND pk1_value = l_pk1_value_tbl(i)