[Home] [Help]
1: PACKAGE BODY ego_ag_bulkload_pvt AS
2: /* $Header: EGOVAGBB.pls 120.16 2011/06/16 01:06:26 trudave noship $ */
3:
4: /*This Procedure is used to initialize certain column values in the interface table.
5: Used in the interface flow.
10: lv_smt NUMBER; --Statement counter
11:
12: lv_proc VARCHAR2(30) := 'Initialize';
13: BEGIN
14: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entered Initialize procedure');
15:
16: x_return_status := G_RET_STS_SUCCESS;
17:
18: lv_smt := 1;
16: x_return_status := G_RET_STS_SUCCESS;
17:
18: lv_smt := 1;
19:
20: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
21:
22: /*Sets the EGO application ID*/
23: SELECT application_id
24: INTO g_ego_application_id
26: WHERE application_short_name = 'EGO';
27:
28: lv_smt := 2;
29:
30: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
31:
32: /*Sets the Transaction_id and upper case the transaction_type*/
33: UPDATE ego_attr_groups_interface
34: SET transaction_id = mtl_system_items_interface_s.nextval,
55: OR (set_process_id = G_SET_PROCESS_ID));
56:
57: lv_smt := 3;
58:
59: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
60:
61: UPDATE ego_attr_groups_dl_interface
62: SET transaction_id = mtl_system_items_interface_s.nextval,
63: transaction_type = Upper(transaction_type),
83: OR (set_process_id = G_SET_PROCESS_ID));
84:
85: lv_smt := 4;
86:
87: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
88:
89: UPDATE ego_attr_group_cols_intf
90: SET transaction_id = mtl_system_items_interface_s.nextval,
91: transaction_type = Upper(transaction_type),
109: WHERE process_status = G_PROCESS_RECORD
110: AND ((G_SET_PROCESS_ID IS NULL)
111: OR (set_process_id = G_SET_PROCESS_ID));
112:
113: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Initialize procedure');
114: EXCEPTION
115: WHEN OTHERS THEN
116: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Initialize - Exception when others'||SQLERRM);
117:
112:
113: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Initialize procedure');
114: EXCEPTION
115: WHEN OTHERS THEN
116: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Initialize - Exception when others'||SQLERRM);
117:
118: x_return_status := G_RET_STS_UNEXP_ERROR;
119:
120: x_return_msg := 'ego_ag_bulkload_pvt.initialize - '||SQLERRM;
116: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Initialize - Exception when others'||SQLERRM);
117:
118: x_return_status := G_RET_STS_UNEXP_ERROR;
119:
120: x_return_msg := 'ego_ag_bulkload_pvt.initialize - '||SQLERRM;
121:
122: RETURN;
123: END initialize;
124:
131: lv_smt NUMBER; --Statement counter
132:
133: lv_proc VARCHAR2(30) := 'Validate_transaction_type';
134: BEGIN
135: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entered Validate_transaction_type');
136:
137: x_return_status := G_RET_STS_SUCCESS;
138:
139: /* Update the interface records with process_status 3 and insert into
145: G_MESSAGE_TEXT := fnd_message.get;
146:
147: lv_smt := 1;
148:
149: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
150:
151: INSERT INTO mtl_interface_errors
152: (transaction_id,
153: unique_id,
206: OR (set_process_id = G_SET_PROCESS_ID));
207:
208: lv_smt := 2;
209:
210: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
211:
212: INSERT INTO mtl_interface_errors
213: (transaction_id,
214: unique_id,
267: OR (set_process_id = G_SET_PROCESS_ID));
268:
269: lv_smt := 3;
270:
271: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
272:
273: INSERT INTO mtl_interface_errors
274: (transaction_id,
275: unique_id,
326: AND process_status = G_PROCESS_RECORD
327: AND ((G_SET_PROCESS_ID IS NULL)
328: OR (set_process_id = G_SET_PROCESS_ID));
329:
330: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Validatetransactiontype');
331: EXCEPTION
332: WHEN OTHERS THEN
333: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Validate_transaction_type Exception when others'||SQLERRM);
334:
329:
330: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Validatetransactiontype');
331: EXCEPTION
332: WHEN OTHERS THEN
333: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Validate_transaction_type Exception when others'||SQLERRM);
334:
335: x_return_status := G_RET_STS_UNEXP_ERROR;
336:
337: x_return_msg := 'ego_ag_bulkload_pvt.Validate_transaction_type - '||SQLERRM;
333: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Validate_transaction_type Exception when others'||SQLERRM);
334:
335: x_return_status := G_RET_STS_UNEXP_ERROR;
336:
337: x_return_msg := 'ego_ag_bulkload_pvt.Validate_transaction_type - '||SQLERRM;
338:
339: RETURN;
340: END Validate_transaction_type;
341:
348: lv_smt NUMBER; --Statement counter
349:
350: lv_proc VARCHAR2(30) := 'bulk_validate_attr_groups';
351: BEGIN
352: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entered bulk_validate_attr_groups');
353:
354: x_return_status := G_RET_STS_SUCCESS;
355:
356: Value_to_id_ag(x_return_status, x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
362: construct_ag_data_level(x_return_status, x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
363:
364: lv_smt := 1;
365:
366: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
367:
368: /* Update the interface records with process_status 3 and insert into
369: MTL_INTERFACE_ERRORS if internal name or the display name for AG is not
370: provided in the CREATE flow*/
434: OR (set_process_id = G_SET_PROCESS_ID));
435:
436: lv_smt := 9;
437:
438: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 9');
439:
440: /* Update the interface records with process_status 3 and insert into
441: MTL_INTERFACE_ERRORS if Attribute Group Type in the AG interface table is other than EGO_ITEMMGMT_GROUP*/
442: G_MESSAGE_NAME := 'EGO_AG_TYPE_INVALID';
501: OR (set_process_id = G_SET_PROCESS_ID));
502:
503: lv_smt := 10;
504:
505: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 10');
506:
507: /* Update the interface records with process_status 3 and insert into
508: MTL_INTERFACE_ERRORS if Attribute Group Type in the DL interface is other than EGO_ITEMMGMT_GROUP*/
509: G_MESSAGE_NAME := 'EGO_AG_TYPE_INVALID';
568: OR (set_process_id = G_SET_PROCESS_ID));
569:
570: lv_smt := 2; --Added for 9625957
571:
572: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
573:
574: /* Update the interface records with process_status 3 and insert into
575: MTL_INTERFACE_ERRORS if Attribute Group Type in the DL interface is other than EGO_ITEMMGMT_GROUP*/
576: G_MESSAGE_NAME := 'EGO_DL_MANDATORY';
640:
641:
642: lv_smt := 2.1;
643:
644: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2.1');
645:
646: /* Update the interface records with process_status 3 and insert into
647: MTL_INTERFACE_ERRORS if internal name is not existing in the UPDATE flow.
648: Updates the AG interface table*/
712: OR (set_process_id = G_SET_PROCESS_ID));
713:
714: lv_smt := 3;
715:
716: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
717:
718: /* Update the interface records with process_status 3 and insert into
719: MTL_INTERFACE_ERRORS if internal name is not existing. Updates the DL
720: interface table*/
792: OR (set_process_id = G_SET_PROCESS_ID));
793:
794: lv_smt := 4;
795:
796: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
797:
798: /* Update the interface records with process_status 3 and insert into
799: MTL_INTERFACE_ERRORS if for the CREATE flow if there already exists an AG with
800: the same internal attribute group name*/
872:
873:
874: lv_smt := 4.1;
875:
876: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4.1');
877:
878: /* Update the interface records with process_status 3 and insert into
879: MTL_INTERFACE_ERRORS if for the CREATE flow if there doesn't exist any DL
880: in the interface table*/
954: OR (set_process_id = G_SET_PROCESS_ID));
955:
956: lv_smt := 4.2;
957:
958: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4.2');
959:
960: /* Update the interface records with process_status 3 and insert into
961: MTL_INTERFACE_ERRORS if Data Level passed is already enabled in the system*/
962: G_MESSAGE_NAME := 'EGO_EF_DL_AG_EXISTS';
1037: OR (set_process_id = G_SET_PROCESS_ID));
1038:
1039: lv_smt := 5;
1040:
1041: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
1042:
1043: /* Update the interface records with process_status 3 and insert into
1044: MTL_INTERFACE_ERRORS if Data Level passed is incorrect*/
1045: G_MESSAGE_NAME := 'EGO_DL_NOT_EXIST';
1106: OR (set_process_id = G_SET_PROCESS_ID));
1107:
1108: lv_smt := 6;
1109:
1110: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 6');
1111:
1112: /* Update the interface records with process_status 3 and insert into
1113: MTL_INTERFACE_ERRORS if the AG is of type Variant and Business entity is not ITEM or the Style to SKU is not NULL*/
1114: G_MESSAGE_NAME := 'EGO_VAR_DL_SKU';
1205: OR (set_process_id = G_SET_PROCESS_ID));
1206:
1207: lv_smt := 7;
1208:
1209: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 7');
1210:
1211: /* Update the interface records with process_status 3 and insert into
1212: MTL_INTERFACE_ERRORS if AG is of Single Row or MultiRow Business and the business entity is not any of the below
1213: ITEM_LEVEL, ITEM_REVISION_LEVEL, ITEM_ORG, ITEM_SUP, ITEM_SUP_SITE, ITEM_SUP_SITE_ORG*/
1283: OR (set_process_id = G_SET_PROCESS_ID));
1284:
1285: lv_smt := 8;
1286:
1287: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 8');
1288:
1289: /* Update the interface records with process_status 3 and insert into
1290: MTL_INTERFACE_ERRORS if AG is of Single Row or MultiRow Business flag an error if the business entity and the
1291: Style to SKU combination is invalid*/
1367: OR (set_process_id = G_SET_PROCESS_ID));
1368:
1369: lv_smt := 11;
1370:
1371: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 11');
1372:
1373: /* Update the interface records with process_status 3 and insert into
1374: MTL_INTERFACE_ERRORS if View Privilege is incorrect*/
1375: G_MESSAGE_NAME := 'EGO_VIEW_PRIV_NOT_EXIST';
1444: OR (set_process_id = G_SET_PROCESS_ID));
1445:
1446: lv_smt := 12;
1447:
1448: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 12');
1449:
1450: --
1451: -- Bug 12394675. Issue #1.
1452: -- Attribute group import was erroing our with invalid number exception.
1525: OR (set_process_id = G_SET_PROCESS_ID));
1526:
1527: lv_smt := 13;
1528:
1529: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 13');
1530:
1531: /* Update the interface records with process_status 3 and insert into
1532: MTL_INTERFACE_ERRORS if Defaulting passed is incorrect*/
1533: G_MESSAGE_NAME := 'EGO_DEFAULTING_INVALID';
1624: AND variant = 'Y');
1625:
1626: lv_smt := 14;
1627:
1628: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 14');
1629:
1630: /* Update the interface records with process_status 3 and insert into
1631: MTL_INTERFACE_ERRORS if AG has associations and user is trying to delete a DL*/
1632: G_MESSAGE_NAME := 'EGO_EF_ASSOCS_EXIST3';
1699: OR (set_process_id = G_SET_PROCESS_ID));
1700:
1701: lv_smt := 15;
1702:
1703: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 15');
1704:
1705: /* Update the interface records with process_status 3 and insert into
1706: MTL_INTERFACE_ERRORS if AG has associations and user is trying to delete the AG*/
1707: G_MESSAGE_NAME := 'EGO_EF_ASSOCS_EXIST1';
1772: AND process_status = G_PROCESS_RECORD
1773: AND ((G_SET_PROCESS_ID IS NULL)
1774: OR (set_process_id = G_SET_PROCESS_ID));
1775:
1776: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit bulk_validate_attr_groups');
1777: EXCEPTION
1778: WHEN OTHERS THEN
1779: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'bulk_validate_attr_groups Exception when others'||SQLERRM);
1780:
1775:
1776: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit bulk_validate_attr_groups');
1777: EXCEPTION
1778: WHEN OTHERS THEN
1779: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'bulk_validate_attr_groups Exception when others'||SQLERRM);
1780:
1781: x_return_status := G_RET_STS_UNEXP_ERROR;
1782:
1783: x_return_msg := 'ego_ag_bulkload_pvt.bulk_validate_attr_groups - '||SQLERRM;
1779: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'bulk_validate_attr_groups Exception when others'||SQLERRM);
1780:
1781: x_return_status := G_RET_STS_UNEXP_ERROR;
1782:
1783: x_return_msg := 'ego_ag_bulkload_pvt.bulk_validate_attr_groups - '||SQLERRM;
1784:
1785: RETURN;
1786: END bulk_validate_attr_groups;
1787:
1794: lv_smt NUMBER; --Statement counter
1795:
1796: lv_proc VARCHAR2(30) := 'bulk_validate_attribute';
1797: BEGIN
1798: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entered bulk_validate_attribute');
1799:
1800: x_return_status := G_RET_STS_SUCCESS;
1801:
1802: lv_smt := 1;
1800: x_return_status := G_RET_STS_SUCCESS;
1801:
1802: lv_smt := 1;
1803:
1804: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
1805:
1806: /* Update the interface records with process_status 3 and insert into
1807: MTL_INTERFACE_ERRORS if Attribute Group Type in the Attribute interface table is other than EGO_ITEMMGMT_GROUP*/
1808: G_MESSAGE_NAME := 'EGO_AG_TYPE_INVALID';
1867: OR (set_process_id = G_SET_PROCESS_ID));
1868:
1869: lv_smt := 2;
1870:
1871: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
1872:
1873: /* Update the interface records with process_status 3 and insert into
1874: MTL_INTERFACE_ERRORS if the attribute with the internal name is already present in the system*/
1875: G_MESSAGE_NAME := 'EGO_EF_INTERNAL_NAME_UNIQUE';
1952: construct_attribute(x_return_status, x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
1953:
1954: lv_smt := 2;
1955:
1956: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
1957:
1958: /* Update the interface records with process_status 3 and insert into
1959: MTL_INTERFACE_ERRORS if Attribute group does exist in the system or
1960: as a create flow in the AG interface table. Updates the DL interface table*/
2032: OR (set_process_id = G_SET_PROCESS_ID));
2033:
2034: lv_smt := 3;
2035:
2036: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
2037:
2038: /*Sets the error EGO_ATTR_NOT_EXISTS if the attr_id or internal_name is null in the update flow*/
2039: G_MESSAGE_NAME := 'EGO_ATTR_NOT_EXISTS';
2040:
2106: OR (set_process_id = G_SET_PROCESS_ID));
2107:
2108: lv_smt := 4;
2109:
2110: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
2111:
2112: /*Sets the error EGO_EF_BC_SEL_EXI_VALUE if the value set is defined in the system*/
2113: G_MESSAGE_NAME := 'EGO_EF_BC_SEL_EXI_VALUE';
2114:
2182: OR (set_process_id = G_SET_PROCESS_ID));
2183:
2184: lv_smt := 5;
2185:
2186: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
2187:
2188: /* Update the interface records with process_status 3 and insert into
2189: MTL_INTERFACE_ERRORS if AG has associations and user is trying to delete the attributes*/
2190: G_MESSAGE_NAME := 'EGO_EF_ASSOCS_EXIST2';
2256: AND ((G_SET_PROCESS_ID IS NULL)
2257: OR (set_process_id = G_SET_PROCESS_ID));
2258:
2259:
2260: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit bulk_validate_attribute');
2261: EXCEPTION
2262: WHEN OTHERS THEN
2263: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'bulk_validate_attribute Exception when others'||SQLERRM);
2264:
2259:
2260: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit bulk_validate_attribute');
2261: EXCEPTION
2262: WHEN OTHERS THEN
2263: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'bulk_validate_attribute Exception when others'||SQLERRM);
2264:
2265: x_return_status := G_RET_STS_UNEXP_ERROR;
2266:
2267: x_return_msg := 'ego_ag_bulkload_pvt.bulk_validate_attribute - '||SQLERRM;
2263: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'bulk_validate_attribute Exception when others'||SQLERRM);
2264:
2265: x_return_status := G_RET_STS_UNEXP_ERROR;
2266:
2267: x_return_msg := 'ego_ag_bulkload_pvt.bulk_validate_attribute - '||SQLERRM;
2268:
2269: RETURN;
2270: END bulk_validate_attribute;
2271:
2278: lv_smt NUMBER; --Statement counter
2279:
2280: lv_proc VARCHAR2(30) := 'Value_to_id_ag';
2281: BEGIN
2282: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entered Value_to_id_ag');
2283:
2284: x_return_status := G_RET_STS_SUCCESS;
2285:
2286: lv_smt := 1;
2284: x_return_status := G_RET_STS_SUCCESS;
2285:
2286: lv_smt := 1;
2287:
2288: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
2289:
2290: /*Sets the Attribute group id when the Internal Attribute group name is given*/
2291: UPDATE ego_attr_groups_interface eagi
2292: SET eagi.attr_group_id = (SELECT attr_group_id
2306: OR (set_process_id = G_SET_PROCESS_ID));
2307:
2308: lv_smt := 2;
2309:
2310: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
2311:
2312: /*Sets the SYNC transaction type to CREATE*/
2313: UPDATE ego_attr_groups_interface eagi
2314: SET eagi.transaction_type = decode(attr_group_id,null,G_OPR_CREATE,G_OPR_UPDATE),
2324:
2325:
2326: /*As of now we dont support Owning party. When we support do value to id
2327: converstion for that here*/
2328: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_ag');
2329: EXCEPTION
2330: WHEN OTHERS THEN
2331: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_ag Exception when others'||SQLERRM);
2332:
2327: converstion for that here*/
2328: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_ag');
2329: EXCEPTION
2330: WHEN OTHERS THEN
2331: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_ag Exception when others'||SQLERRM);
2332:
2333: x_return_status := G_RET_STS_UNEXP_ERROR;
2334:
2335: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_ag - '||SQLERRM;
2331: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_ag Exception when others'||SQLERRM);
2332:
2333: x_return_status := G_RET_STS_UNEXP_ERROR;
2334:
2335: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_ag - '||SQLERRM;
2336:
2337: RETURN;
2338: END Value_to_id_ag;
2339:
2346: lv_smt NUMBER;
2347:
2348: lv_proc VARCHAR2(30) := 'Value_to_id_dl';
2349: BEGIN
2350: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entered Value_to_id_dl');
2351:
2352: x_return_status := G_RET_STS_SUCCESS;
2353:
2354: lv_smt := 1;
2352: x_return_status := G_RET_STS_SUCCESS;
2353:
2354: lv_smt := 1;
2355:
2356: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
2357:
2358: /*Sets the Attribute group id when the Internal Attribute group name is given*/
2359: UPDATE ego_attr_groups_dl_interface eagdi
2360: SET eagdi.attr_group_id = (SELECT attr_group_id
2373:
2374:
2375: lv_smt := 3;
2376:
2377: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
2378:
2379: /*Set the View Privilege ID when the View Privilege Name or User View privilege name is given*/
2380: UPDATE ego_attr_groups_dl_interface eagdl
2381: SET eagdl.view_privilege_id = (SELECT function_id
2404: OR (set_process_id = G_SET_PROCESS_ID));
2405:
2406: lv_smt := 4;
2407:
2408: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
2409:
2410: /*Set the Edit Privilege ID when the Edit Privilege Name or user edit priviledge name is given*/
2411: UPDATE ego_attr_groups_dl_interface eagdl
2412: SET edit_privilege_id = (SELECT function_id
2435: OR (set_process_id = G_SET_PROCESS_ID));
2436:
2437: lv_smt := 5;
2438:
2439: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
2440:
2441: /*Set the Data Level ID when the data level is given*/
2442: UPDATE ego_attr_groups_dl_interface eagdi
2443: SET eagdi.data_level_id = (SELECT data_level_id
2455: OR (set_process_id = G_SET_PROCESS_ID));
2456:
2457: lv_smt := 6;
2458:
2459: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 6');
2460:
2461: /*Sets the SYNC transaction type to UPDATE*/
2462: UPDATE ego_attr_groups_dl_interface eagdi
2463: SET eagdi.transaction_type = G_OPR_UPDATE,
2476: OR (set_process_id = G_SET_PROCESS_ID));
2477:
2478: lv_smt := 7;
2479:
2480: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 7');
2481:
2482: /*Sets the SYNC transaction type to CREATE*/
2483: UPDATE ego_attr_groups_dl_interface eagdi
2484: SET eagdi.transaction_type = G_OPR_CREATE,
2497: OR (set_process_id = G_SET_PROCESS_ID));
2498:
2499: lv_smt := 7.1;
2500:
2501: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 7.1');
2502:
2503: /*Sets the SYNC transaction type to CREATE*/
2504: UPDATE ego_attr_groups_dl_interface eagdi
2505: SET eagdi.transaction_type = G_OPR_CREATE,
2511: AND eagdi.process_status = G_PROCESS_RECORD
2512: AND ((G_SET_PROCESS_ID IS NULL)
2513: OR (set_process_id = G_SET_PROCESS_ID));
2514:
2515: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_dl');
2516: EXCEPTION
2517: WHEN OTHERS THEN
2518: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_dl Exception when others'||SQLERRM);
2519:
2514:
2515: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_dl');
2516: EXCEPTION
2517: WHEN OTHERS THEN
2518: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_dl Exception when others'||SQLERRM);
2519:
2520: x_return_status := G_RET_STS_UNEXP_ERROR;
2521:
2522: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_dl - '||SQLERRM;
2518: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_dl Exception when others'||SQLERRM);
2519:
2520: x_return_status := G_RET_STS_UNEXP_ERROR;
2521:
2522: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_dl - '||SQLERRM;
2523:
2524: RETURN;
2525: END Value_to_id_dl;
2526:
2533: lv_smt NUMBER; --Statement counter;
2534:
2535: lv_proc VARCHAR2(30) := 'Value_to_id_attr';
2536: BEGIN
2537: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Value_to_id_attr');
2538:
2539: x_return_status := G_RET_STS_SUCCESS;
2540:
2541: lv_smt := 1;
2539: x_return_status := G_RET_STS_SUCCESS;
2540:
2541: lv_smt := 1;
2542:
2543: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
2544:
2545: /*Sets the Attribute group id when the Internal Attribute group name is given*/
2546: UPDATE ego_attr_group_cols_intf eagci
2547: SET eagci.attr_group_id = (SELECT attr_group_id
2559: OR (set_process_id = G_SET_PROCESS_ID));
2560:
2561: lv_smt := 2;
2562:
2563: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
2564:
2565: /*Set the application id*/
2566: UPDATE ego_attr_group_cols_intf
2567: SET application_id = g_ego_application_id,
2574: OR (set_process_id = G_SET_PROCESS_ID));
2575:
2576: lv_smt := 3;
2577:
2578: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
2579:
2580: /*Sets the Attr_id for the update and sync flow*/
2581: UPDATE ego_attr_group_cols_intf eagci
2582: SET eagci.attr_id = (SELECT efdcue.attr_id
2598: OR (set_process_id = G_SET_PROCESS_ID));
2599:
2600: lv_smt := 4;
2601:
2602: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
2603:
2604: /*Sets the value set id when the value set name is given*/
2605: UPDATE ego_attr_group_cols_intf eagci
2606: SET eagci.flex_value_set_id = (SELECT ffvs.flex_value_set_id
2613: OR (set_process_id = G_SET_PROCESS_ID));
2614:
2615: lv_smt := 5;
2616:
2617: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
2618:
2619: /*Sets the Sync type to UPDATE*/
2620: UPDATE ego_attr_group_cols_intf
2621: SET transaction_type = G_OPR_UPDATE
2626: OR (set_process_id = G_SET_PROCESS_ID));
2627:
2628: lv_smt := 6;
2629:
2630: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 6');
2631:
2632: /*Sets the Sync type to CREATE*/
2633: UPDATE ego_attr_group_cols_intf
2634: SET transaction_type = G_OPR_CREATE
2637: AND process_status = G_PROCESS_RECORD
2638: AND ((G_SET_PROCESS_ID IS NULL)
2639: OR (set_process_id = G_SET_PROCESS_ID));
2640:
2641: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_attr');
2642: EXCEPTION
2643: WHEN OTHERS THEN
2644: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_attr Exception when others'||SQLERRM);
2645:
2640:
2641: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_attr');
2642: EXCEPTION
2643: WHEN OTHERS THEN
2644: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_attr Exception when others'||SQLERRM);
2645:
2646: x_return_status := G_RET_STS_UNEXP_ERROR;
2647:
2648: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_attr - '||SQLERRM;
2644: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_attr Exception when others'||SQLERRM);
2645:
2646: x_return_status := G_RET_STS_UNEXP_ERROR;
2647:
2648: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_attr - '||SQLERRM;
2649:
2650: RETURN;
2651: END value_to_id_attr;
2652:
2659: lv_smt NUMBER; --Statement counter
2660:
2661: lv_proc VARCHAR2(30) := 'construct_attr_groups';
2662: BEGIN
2663: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering construct_attr_groups');
2664:
2665: x_return_status := G_RET_STS_SUCCESS;
2666:
2667: lv_smt := 1;
2665: x_return_status := G_RET_STS_SUCCESS;
2666:
2667: lv_smt := 1;
2668:
2669: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
2670:
2671: /*Sets the Internal Attribute group name when the attribute group id is given*/
2672: UPDATE ego_attr_groups_interface eagi
2673: SET eagi.attr_group_name = (SELECT attr_group_name
2685: OR (set_process_id = G_SET_PROCESS_ID));
2686:
2687: lv_smt := 2;
2688:
2689: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
2690:
2691: /*Set default for MULTIROW as N*/
2692: UPDATE ego_attr_groups_interface
2693: SET multi_row = 'N',
2699: AND process_status = G_PROCESS_RECORD
2700: AND ((G_SET_PROCESS_ID IS NULL)
2701: OR (set_process_id = G_SET_PROCESS_ID));
2702:
2703: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit construct_attr_groups');
2704: EXCEPTION
2705: WHEN OTHERS THEN
2706: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'construct_attr_groups Exception when others'||SQLERRM);
2707:
2702:
2703: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit construct_attr_groups');
2704: EXCEPTION
2705: WHEN OTHERS THEN
2706: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'construct_attr_groups Exception when others'||SQLERRM);
2707:
2708: x_return_status := G_RET_STS_UNEXP_ERROR;
2709:
2710: x_return_msg := 'ego_ag_bulkload_pvt.construct_attr_groups - '||SQLERRM;
2706: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'construct_attr_groups Exception when others'||SQLERRM);
2707:
2708: x_return_status := G_RET_STS_UNEXP_ERROR;
2709:
2710: x_return_msg := 'ego_ag_bulkload_pvt.construct_attr_groups - '||SQLERRM;
2711:
2712: RETURN;
2713: END construct_attr_groups;
2714:
2721: lv_smt NUMBER;
2722:
2723: lv_proc VARCHAR2(30) := 'construct_ag_data_level';
2724: BEGIN
2725: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering construct_ag_data_level');
2726:
2727: x_return_status := G_RET_STS_SUCCESS;
2728:
2729: lv_smt := 1;
2727: x_return_status := G_RET_STS_SUCCESS;
2728:
2729: lv_smt := 1;
2730:
2731: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
2732:
2733: /*Sets the Internal Attribute group name when the attribute group id is given*/
2734: UPDATE ego_attr_groups_dl_interface eagdi
2735: SET eagdi.attr_group_name = (SELECT attr_group_name
2746: OR (set_process_id = G_SET_PROCESS_ID));
2747:
2748: lv_smt := 2;
2749:
2750: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
2751:
2752: /*Set the View Privilege when the id is given*/
2753: UPDATE ego_attr_groups_dl_interface eagdl
2754: SET (view_privilege_name,user_view_priv_name) = (SELECT function_name, user_function_name
2763: OR (set_process_id = G_SET_PROCESS_ID));
2764:
2765: lv_smt := 3;
2766:
2767: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
2768:
2769: /*Set the Edit Privilege when the id is given*/
2770: UPDATE ego_attr_groups_dl_interface eagdl
2771: SET (edit_privilege_name,user_edit_priv_name) = (SELECT function_name, user_function_name
2780: OR (set_process_id = G_SET_PROCESS_ID));
2781:
2782: lv_smt := 4;
2783:
2784: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
2785:
2786: /*Set the defaulting when defaulting name is given*/
2787:
2788: UPDATE ego_attr_groups_dl_interface eagdl
2802:
2803:
2804: lv_smt := 5;
2805:
2806: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
2807:
2808: /*Set the defaulting name when defaulting is given*/
2809: UPDATE ego_attr_groups_dl_interface eagdl
2810: SET eagdl.defaulting_name = (SELECT meaning
2821: OR (set_process_id = G_SET_PROCESS_ID));
2822:
2823: lv_smt := 6;
2824:
2825: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 6');
2826:
2827: /*Set the Data Level when the data level id is given*/
2828: UPDATE ego_attr_groups_dl_interface eagdi
2829: SET eagdi.data_level_name = (SELECT data_level_name
2838: AND eagdi.process_status = G_PROCESS_RECORD
2839: AND ((G_SET_PROCESS_ID IS NULL)
2840: OR (set_process_id = G_SET_PROCESS_ID));
2841:
2842: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit construct_ag_data_level');
2843: EXCEPTION
2844: WHEN OTHERS THEN
2845: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'construct_ag_data_level Exception when others'||SQLERRM);
2846:
2841:
2842: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit construct_ag_data_level');
2843: EXCEPTION
2844: WHEN OTHERS THEN
2845: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'construct_ag_data_level Exception when others'||SQLERRM);
2846:
2847: x_return_status := G_RET_STS_UNEXP_ERROR;
2848:
2849: x_return_msg := 'ego_ag_bulkload_pvt.construct_ag_data_level - '||SQLERRM;
2845: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'construct_ag_data_level Exception when others'||SQLERRM);
2846:
2847: x_return_status := G_RET_STS_UNEXP_ERROR;
2848:
2849: x_return_msg := 'ego_ag_bulkload_pvt.construct_ag_data_level - '||SQLERRM;
2850:
2851: RETURN;
2852: END construct_ag_data_level;
2853:
2860: lv_smt NUMBER; --Statement counter
2861:
2862: lv_proc VARCHAR2(30) := 'construct_attribute';
2863: BEGIN
2864: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering construct_attribute');
2865:
2866: x_return_status := G_RET_STS_SUCCESS;
2867:
2868: lv_smt := 1;
2866: x_return_status := G_RET_STS_SUCCESS;
2867:
2868: lv_smt := 1;
2869:
2870: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
2871:
2872: /*Sets the Internal Attribute group name when the Attribute group id is given*/
2873: UPDATE ego_attr_group_cols_intf eagci
2874: SET eagci.attr_group_name = (SELECT descriptive_flex_context_code
2885: OR (set_process_id = G_SET_PROCESS_ID));
2886:
2887: lv_smt := 2;
2888:
2889: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
2890:
2891: /*Sets the Internal Attribute name when then Attr id is given*/
2892: UPDATE ego_attr_group_cols_intf eagci
2893: SET eagci.internal_name = (SELECT fdfcu.end_user_column_name
2908: OR (set_process_id = G_SET_PROCESS_ID));
2909:
2910: lv_smt := 3;
2911:
2912: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
2913:
2914: /*Sets the value set name when the value set id is given*/
2915: UPDATE ego_attr_group_cols_intf eagci
2916: SET eagci.flex_value_set_name = (SELECT ffvs.flex_value_set_name
2920: AND eagci.process_status = G_PROCESS_RECORD
2921: AND ((G_SET_PROCESS_ID IS NULL)
2922: OR (set_process_id = G_SET_PROCESS_ID));
2923:
2924: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit construct_attribute');
2925: EXCEPTION
2926: WHEN OTHERS THEN
2927: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'construct_attribute Exception when others'||SQLERRM);
2928:
2923:
2924: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit construct_attribute');
2925: EXCEPTION
2926: WHEN OTHERS THEN
2927: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'construct_attribute Exception when others'||SQLERRM);
2928:
2929: x_return_status := G_RET_STS_UNEXP_ERROR;
2930:
2931: x_return_msg := 'ego_ag_bulkload_pvt.construct_attribute - '||SQLERRM;
2927: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'construct_attribute Exception when others'||SQLERRM);
2928:
2929: x_return_status := G_RET_STS_UNEXP_ERROR;
2930:
2931: x_return_msg := 'ego_ag_bulkload_pvt.construct_attribute - '||SQLERRM;
2932:
2933: RETURN;
2934: END construct_attribute;
2935:
2947: IS
2948:
2949: lv_proc VARCHAR2(30) := 'process_attr_groups';
2950: BEGIN
2951: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering process_attr_groups');
2952:
2953: x_return_status := G_RET_STS_SUCCESS;
2954:
2955: error_handler.Set_bo_identifier(G_BO_IDENTIFIER_AG);
2980: END IF;
2981:
2982: delete_ag_none_dl(x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
2983:
2984: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit process_attr_groups');
2985: EXCEPTION
2986: WHEN OTHERS THEN
2987: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'process_attr_groups Exception when others'||SQLERRM);
2988:
2983:
2984: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit process_attr_groups');
2985: EXCEPTION
2986: WHEN OTHERS THEN
2987: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'process_attr_groups Exception when others'||SQLERRM);
2988:
2989: x_return_status := G_RET_STS_UNEXP_ERROR;
2990:
2991: x_return_msg := 'ego_ag_bulkload_pvt.process_attr_groups - '||SQLERRM;
2987: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'process_attr_groups Exception when others'||SQLERRM);
2988:
2989: x_return_status := G_RET_STS_UNEXP_ERROR;
2990:
2991: x_return_msg := 'ego_ag_bulkload_pvt.process_attr_groups - '||SQLERRM;
2992:
2993: RETURN;
2994: END process_attr_groups;
2995:
3007: x_msg_data VARCHAR2(2000);
3008:
3009: lv_proc VARCHAR2(30) := 'Process_ag';
3010: BEGIN
3011: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Process_ag');
3012:
3013: x_return_status := G_RET_STS_SUCCESS;
3014:
3015: Common_ag_validations(p_ag_tbl => p_ag_tbl,x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
3018:
3019: FOR i IN p_ag_tbl.FIRST.. p_ag_tbl.LAST LOOP
3020: IF (P_ag_tbl(i).process_status = G_PROCESS_RECORD
3021: AND P_ag_tbl(i).transaction_type = 'CREATE') THEN
3022: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Create AG');
3023:
3024: ego_ext_fwk_pub.Create_attribute_group(p_api_version => 1.0
3025: ,p_application_id => g_ego_application_id
3026: ,p_attr_group_type => P_ag_tbl(i).attr_group_type
3055: ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3056: x_return_msg := x_msg_data;
3057: RETURN;
3058: ELSE
3059: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_ag_tbl(i).transaction_id||' (AG) = ('||P_ag_tbl(i).attr_group_name||'). ' ||'Error in creating AG');
3060:
3061: P_ag_tbl(i).process_status := G_ERROR_RECORD;
3062:
3063: G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
3083: G_TOKEN_TABLE.DELETE;
3084: END IF;
3085: ELSIF (P_ag_tbl(i).process_status = G_PROCESS_RECORD
3086: AND P_ag_tbl(i).transaction_type = 'UPDATE') THEN
3087: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Update AG');
3088:
3089: ego_ext_fwk_pub.Update_attribute_group(p_api_version => 1.0
3090: ,p_application_id => g_ego_application_id
3091: ,p_attr_group_type => P_ag_tbl(i).attr_group_type
3112: ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3113: x_return_msg := x_msg_data;
3114: RETURN;
3115: ELSE
3116: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_ag_tbl(i).transaction_id||' (AG) = ('||P_ag_tbl(i).attr_group_name||'). ' ||'Error in updating AG');
3117:
3118: P_ag_tbl(i).process_status := G_ERROR_RECORD;
3119:
3120: G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
3139: G_TOKEN_TABLE.DELETE;
3140: END IF;
3141: ELSIF (P_ag_tbl(i).process_status = G_PROCESS_RECORD
3142: AND P_ag_tbl(i).transaction_type = 'DELETE') THEN
3143: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Delete AG');
3144:
3145: ego_ext_fwk_pub.Delete_attribute_group(p_api_version => 1.0
3146: ,p_application_id => g_ego_application_id
3147: ,p_attr_group_type => P_ag_tbl(i).attr_group_type
3157: ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3158: x_return_msg := x_msg_data;
3159: RETURN;
3160: ELSE
3161: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_ag_tbl(i).transaction_id||' (AG) = ('||P_ag_tbl(i).attr_group_name||'). ' ||'Error in deleting AG');
3162:
3163: P_ag_tbl(i).process_status := G_ERROR_RECORD;
3164:
3165: G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
3186: END IF;
3187: END LOOP;
3188:
3189: IF (G_COMMIT = true AND G_FLOW_TYPE = G_EGO_MD_API) THEN
3190: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'COMMIT issed after AG Processing');
3191:
3192: COMMIT;
3193: END IF;
3194:
3191:
3192: COMMIT;
3193: END IF;
3194:
3195: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Process_ag');
3196: EXCEPTION
3197: WHEN OTHERS THEN
3198: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Process_ag Exception when others'||SQLERRM);
3199:
3194:
3195: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Process_ag');
3196: EXCEPTION
3197: WHEN OTHERS THEN
3198: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Process_ag Exception when others'||SQLERRM);
3199:
3200: x_return_status := G_RET_STS_UNEXP_ERROR;
3201:
3202: x_return_msg := 'ego_ag_bulkload_pvt.Process_ag - '||SQLERRM;
3198: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Process_ag Exception when others'||SQLERRM);
3199:
3200: x_return_status := G_RET_STS_UNEXP_ERROR;
3201:
3202: x_return_msg := 'ego_ag_bulkload_pvt.Process_ag - '||SQLERRM;
3203:
3204: RETURN;
3205: END Process_ag;
3206:
3216: x_msg_data VARCHAR2(2000);
3217: lv_count NUMBER := 0;
3218: lv_proc VARCHAR2(30) := 'Process_dl';
3219: BEGIN
3220: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Process_dl');
3221:
3222: x_return_status := G_RET_STS_SUCCESS;
3223:
3224: common_dl_validations(p_agdl_tbl => p_agdl_tbl,x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
3246: p_agdl_tbl(i).process_status := G_ERROR_RECORD;
3247:
3248: x_return_status := G_RET_STS_ERROR;
3249:
3250: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id
3251: ||' (AG,DL) = ('||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). '
3252: ||'The combination of Business Entities choosen is Invalid for Item Level.');
3253:
3254: error_handler.Add_error_message(p_message_name => 'EGO_EF_DL_INVALID_COMB',p_application_id => 'EGO',
3274: p_agdl_tbl(i).process_status := G_ERROR_RECORD;
3275:
3276: x_return_status := G_RET_STS_ERROR;
3277:
3278: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id
3279: ||' (AG,DL) = ('||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). '
3280: ||'The combination of Business Entities choosen is Invalid');
3281:
3282: error_handler.Add_error_message(p_message_name => 'EGO_EF_DL_INVALID_COMB',p_application_id => 'EGO',
3302: p_agdl_tbl(i).process_status := G_ERROR_RECORD;
3303:
3304: x_return_status := G_RET_STS_ERROR;
3305:
3306: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id
3307: ||' (AG,DL) = ('||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). '
3308: ||'The combination of Business Entities choosen is Invalid.');
3309:
3310: error_handler.Add_error_message(p_message_name => 'EGO_EF_DL_INVALID_COMB',p_application_id => 'EGO',
3324:
3325: IF (lv_count > 0 AND P_agdl_tbl(i).transaction_type = G_OPR_CREATE) THEN
3326: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
3327:
3328: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id||' (AG,DL) = ('
3329: ||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). '
3330: ||'Business entity is already enabled for the attribute group.');
3331:
3332: error_handler.Add_error_message(p_message_name => 'EGO_EF_DL_AG_EXISTS',p_application_id => 'EGO',
3366: x_return_msg := x_msg_data;
3367: RETURN;
3368: ELSE
3369:
3370: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id||' (AG,DL) = ('||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). ' ||'Error in sync of DL - ' ||x_msg_data);
3371:
3372: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
3373:
3374: G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
3400: AND data_level_id = P_agdl_tbl(i).data_level_id;
3401:
3402: EXCEPTION
3403: WHEN OTHERS THEN
3404: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exception when others');
3405:
3406: x_return_status := G_RET_STS_UNEXP_ERROR;
3407:
3408: x_return_msg := 'ego_ag_bulkload_pvt.Process_dl - '||SQLERRM;
3404: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exception when others');
3405:
3406: x_return_status := G_RET_STS_UNEXP_ERROR;
3407:
3408: x_return_msg := 'ego_ag_bulkload_pvt.Process_dl - '||SQLERRM;
3409: END;
3410: END IF;
3411:
3412: lv_count := 0;
3412: lv_count := 0;
3413: END LOOP;
3414:
3415: IF (G_COMMIT = true AND G_FLOW_TYPE = G_EGO_MD_API) THEN
3416: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'COMMIT issued for DL');
3417:
3418: COMMIT;
3419: END IF;
3420:
3417:
3418: COMMIT;
3419: END IF;
3420:
3421: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Process_dl');
3422: EXCEPTION
3423: WHEN OTHERS THEN
3424: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Process_dl Exception when others'||SQLERRM);
3425:
3420:
3421: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Process_dl');
3422: EXCEPTION
3423: WHEN OTHERS THEN
3424: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Process_dl Exception when others'||SQLERRM);
3425:
3426: x_return_status := G_RET_STS_UNEXP_ERROR;
3427:
3428: x_return_msg := 'ego_ag_bulkload_pvt.Process_dl - '||SQLERRM;
3424: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Process_dl Exception when others'||SQLERRM);
3425:
3426: x_return_status := G_RET_STS_UNEXP_ERROR;
3427:
3428: x_return_msg := 'ego_ag_bulkload_pvt.Process_dl - '||SQLERRM;
3429:
3430: RETURN;
3431: END process_dl;
3432:
3442: IS
3443:
3444: lv_proc VARCHAR2(30) := 'process_attribute';
3445: BEGIN
3446: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering process_attribute');
3447:
3448: x_return_status := G_RET_STS_SUCCESS;
3449:
3450: error_handler.Set_bo_identifier(G_BO_IDENTIFIER_AG);
3466:
3467: /*Send the AG table for processing*/
3468: Process_attr(p_attr_tbl,x_return_status, x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
3469: END IF;
3470: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit process_attribute');
3471: EXCEPTION
3472: WHEN OTHERS THEN
3473: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'process_attribute Exception when others'||SQLERRM);
3474:
3469: END IF;
3470: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit process_attribute');
3471: EXCEPTION
3472: WHEN OTHERS THEN
3473: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'process_attribute Exception when others'||SQLERRM);
3474:
3475: x_return_status := G_RET_STS_UNEXP_ERROR;
3476:
3477: x_return_msg := 'ego_ag_bulkload_pvt.process_attribute - '||SQLERRM;
3473: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'process_attribute Exception when others'||SQLERRM);
3474:
3475: x_return_status := G_RET_STS_UNEXP_ERROR;
3476:
3477: x_return_msg := 'ego_ag_bulkload_pvt.process_attribute - '||SQLERRM;
3478:
3479: RETURN;
3480: END process_attribute;
3481:
3492: x_msg_data VARCHAR2(2000);
3493:
3494: lv_proc VARCHAR2(30) := 'Process_attr';
3495: BEGIN
3496: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Process_attr');
3497:
3498: x_return_status := G_RET_STS_SUCCESS;
3499:
3500: common_attr_validations(p_attr_tbl => p_attr_tbl,x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
3502:
3503: FOR i IN p_attr_tbl.FIRST.. p_attr_tbl.LAST LOOP
3504: IF (P_attr_tbl(i).process_status = G_PROCESS_RECORD
3505: AND P_attr_tbl(i).transaction_type = 'CREATE') THEN
3506: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Create Attribute');
3507:
3508: ego_ext_fwk_pub.Create_attribute(p_api_version => 1.0
3509: ,p_application_id => P_attr_tbl(i).application_id
3510: ,p_attr_group_type => P_attr_tbl(i).attr_group_type
3539: ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3540: x_return_msg := x_msg_data;
3541: RETURN;
3542: ELSE
3543: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_attr_tbl(i).transaction_id||' (AG,ATTR) = ('||P_attr_tbl(i).attr_group_name||','||P_attr_tbl(i).internal_name||'). ' ||'Error in creating attribute');
3544:
3545: P_attr_tbl(i).process_status := G_ERROR_RECORD;
3546:
3547: G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
3566: G_TOKEN_TABLE.DELETE;
3567: END IF;
3568: ELSIF (P_attr_tbl(i).process_status = G_PROCESS_RECORD
3569: AND P_attr_tbl(i).transaction_type = 'UPDATE') THEN
3570: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Update Attribute');
3571:
3572: ego_ext_fwk_pub.Update_attribute(p_api_version => 1.0
3573: ,p_application_id => P_attr_tbl(i).application_id
3574: ,p_attr_group_type => P_attr_tbl(i).attr_group_type
3602: ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3603: x_return_msg := x_msg_data;
3604: RETURN;
3605: ELSE
3606: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_attr_tbl(i).transaction_id||' (AG,ATTR) = ('||P_attr_tbl(i).attr_group_name||','||P_attr_tbl(i).internal_name||'). ' ||'Error in updating attribute');
3607:
3608: P_attr_tbl(i).process_status := G_ERROR_RECORD;
3609:
3610: G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
3630:
3631: END IF;
3632: ELSIF (P_attr_tbl(i).process_status = G_PROCESS_RECORD
3633: AND P_attr_tbl(i).transaction_type = 'DELETE') THEN
3634: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Delete Attribute');
3635:
3636: ego_ext_fwk_pub.Delete_attribute(p_api_version => 1.0
3637: ,p_application_id => P_attr_tbl(i).application_id
3638: ,p_attr_group_type => P_attr_tbl(i).attr_group_type
3650: ELSIF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3651: x_return_msg := x_msg_data;
3652: RETURN;
3653: ELSE
3654: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_attr_tbl(i).transaction_id||' (AG,ATTR) = ('||P_attr_tbl(i).attr_group_name||','||P_attr_tbl(i).internal_name||'). ' ||'Error is deleting attribute');
3655:
3656: P_attr_tbl(i).process_status := G_ERROR_RECORD;
3657:
3658: G_TOKEN_TABLE(1).Token_Name := 'Entity_Name';
3679: END IF;
3680: END LOOP;
3681:
3682: IF (G_COMMIT = true AND G_FLOW_TYPE = G_EGO_MD_API) THEN
3683: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'COMMIT issed after Attribute Processing');
3684:
3685: COMMIT;
3686: END IF;
3687:
3684:
3685: COMMIT;
3686: END IF;
3687:
3688: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Process_attr');
3689: EXCEPTION
3690: WHEN OTHERS THEN
3691: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Process_attr Exception when others'||SQLERRM);
3692:
3687:
3688: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Process_attr');
3689: EXCEPTION
3690: WHEN OTHERS THEN
3691: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Process_attr Exception when others'||SQLERRM);
3692:
3693: x_return_status := G_RET_STS_UNEXP_ERROR;
3694:
3695: x_return_msg := 'ego_ag_bulkload_pvt.Process_attr - '||SQLERRM;
3691: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Process_attr Exception when others'||SQLERRM);
3692:
3693: x_return_status := G_RET_STS_UNEXP_ERROR;
3694:
3695: x_return_msg := 'ego_ag_bulkload_pvt.Process_attr - '||SQLERRM;
3696:
3697: RETURN;
3698: END Process_attr;
3699:
3730: l_ego_attr_tbl ego_metadata_pub.ego_attr_group_cols_tbl;
3731:
3732: lv_proc VARCHAR2(30) := 'import_ag_intf';
3733: BEGIN
3734: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entered import_ag_intf.');
3735:
3736: x_return_status := G_RET_STS_SUCCESS;
3737:
3738: error_handler.Set_bo_identifier(G_BO_IDENTIFIER_AG);
3747: Validate_transaction_type(x_return_status, x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
3748: IF (G_AG_COUNT <> 0 OR G_DL_COUNT <> 0) THEN
3749: bulk_validate_attr_groups(x_return_status, x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
3750:
3751: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Load and process AG');
3752:
3753: /*Load PL/SQL tables for AG and then call processing method*/
3754: OPEN c_ag;
3755:
3768: END LOOP;
3769:
3770: CLOSE c_ag;
3771:
3772: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'AG processed');
3773:
3774: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Load and process DL');
3775:
3776: /*Load PL/SQL tables for DL and then call processing method*/
3770: CLOSE c_ag;
3771:
3772: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'AG processed');
3773:
3774: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Load and process DL');
3775:
3776: /*Load PL/SQL tables for DL and then call processing method*/
3777: OPEN c_agdl;
3778:
3790: END LOOP;
3791:
3792: CLOSE c_agdl;
3793:
3794: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'DL Processed');
3795:
3796: delete_ag_none_dl(x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
3797: END IF;
3798:
3798:
3799: IF (G_ATTR_COUNT <> 0) THEN
3800: bulk_validate_attribute(x_return_status, x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
3801:
3802: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Load and process Attributes');
3803:
3804: /*Load PL/SQL tables for Attributes and then call processing method*/
3805: OPEN c_attr;
3806:
3818: END LOOP;
3819:
3820: CLOSE c_attr;
3821:
3822: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Processed Attributes');
3823: END IF;
3824: END IF;
3825: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit import_ag_intf');
3826: EXCEPTION
3821:
3822: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Processed Attributes');
3823: END IF;
3824: END IF;
3825: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit import_ag_intf');
3826: EXCEPTION
3827: WHEN OTHERS THEN
3828: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'import_ag_intf Exception when others'||SQLERRM);
3829:
3824: END IF;
3825: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit import_ag_intf');
3826: EXCEPTION
3827: WHEN OTHERS THEN
3828: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'import_ag_intf Exception when others'||SQLERRM);
3829:
3830: x_return_status := G_RET_STS_UNEXP_ERROR;
3831:
3832: x_return_msg := 'ego_ag_bulkload_pvt.import_ag_intf - '||SQLERRM;
3828: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'import_ag_intf Exception when others'||SQLERRM);
3829:
3830: x_return_status := G_RET_STS_UNEXP_ERROR;
3831:
3832: x_return_msg := 'ego_ag_bulkload_pvt.import_ag_intf - '||SQLERRM;
3833:
3834: RETURN;
3835: END import_ag_intf;
3836:
3845: lv_smt NUMBER;
3846:
3847: lv_proc VARCHAR2(30) := 'Value_to_id_ag_tbl';
3848: BEGIN
3849: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Value_to_id_ag_tbl');
3850:
3851: x_return_status := G_RET_STS_SUCCESS;
3852:
3853: FOR i IN p_ag_tbl.FIRST.. p_ag_tbl.LAST LOOP
3853: FOR i IN p_ag_tbl.FIRST.. p_ag_tbl.LAST LOOP
3854: IF (P_ag_tbl(i).process_status = G_PROCESS_RECORD) THEN
3855: lv_smt := 1;
3856:
3857: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
3858:
3859: /*Sets the transaction_id*/
3860: SELECT mtl_system_items_interface_s.nextval
3861: INTO P_ag_tbl(i).transaction_id
3862: FROM dual;
3863:
3864: lv_smt := 2;
3865:
3866: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
3867:
3868: /*Sets the Attribute ID if present for Update and Sync flow. If not present then flags an error EGO_AG_INVALID for UPDATE flow
3869: and for sync flow updates the transaction type to CREATE*/
3870: IF ((P_ag_tbl(i).transaction_type = G_OPR_UPDATE
3883: WHEN no_data_found THEN
3884: IF (P_ag_tbl(i).transaction_type = G_OPR_SYNC) THEN
3885: P_ag_tbl(i).transaction_type := G_OPR_CREATE;
3886: ELSE
3887: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_ag_tbl(i).transaction_id||' (AG) = ('||P_ag_tbl(i).attr_group_name||'). ' ||'Attribute Group does not exist in the system');
3888:
3889: P_ag_tbl(i).process_status := G_ERROR_RECORD;
3890:
3891: x_return_status := G_RET_STS_ERROR;
3895: p_row_identifier => P_ag_tbl(i).transaction_id,
3896: p_entity_code => G_ENTITY_AG,p_table_name => G_ENTITY_AG_TAB);
3897: END IF;
3898: WHEN OTHERS THEN
3899: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_ag_tbl Exception when others smt 2');
3900:
3901: x_return_status := G_RET_STS_UNEXP_ERROR;
3902:
3903: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_ag_tbl smt 2- '||SQLERRM;
3899: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_ag_tbl Exception when others smt 2');
3900:
3901: x_return_status := G_RET_STS_UNEXP_ERROR;
3902:
3903: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_ag_tbl smt 2- '||SQLERRM;
3904:
3905: RETURN;
3906: END;
3907: END IF;
3907: END IF;
3908: END IF;
3909: END LOOP;
3910:
3911: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_ag_tbl');
3912: EXCEPTION
3913: WHEN OTHERS THEN
3914: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_ag_tbl Exception when others'||SQLERRM);
3915:
3910:
3911: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_ag_tbl');
3912: EXCEPTION
3913: WHEN OTHERS THEN
3914: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_ag_tbl Exception when others'||SQLERRM);
3915:
3916: x_return_status := G_RET_STS_UNEXP_ERROR;
3917:
3918: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_ag_tbl - '||SQLERRM;
3914: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_ag_tbl Exception when others'||SQLERRM);
3915:
3916: x_return_status := G_RET_STS_UNEXP_ERROR;
3917:
3918: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_ag_tbl - '||SQLERRM;
3919:
3920: RETURN;
3921: END Value_to_id_ag_tbl;
3922:
3931: lv_smt NUMBER;
3932: lv_flag VARCHAR2(1);
3933: lv_proc VARCHAR2(30) := 'Value_to_id_dl_tbl';
3934: BEGIN
3935: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Value_to_id_dl_tbl');
3936:
3937: x_return_status := G_RET_STS_SUCCESS;
3938:
3939: FOR i IN p_agdl_tbl.FIRST.. p_agdl_tbl.LAST LOOP
3939: FOR i IN p_agdl_tbl.FIRST.. p_agdl_tbl.LAST LOOP
3940: IF (P_agdl_tbl(i).process_status = G_PROCESS_RECORD) THEN
3941: lv_smt := 1;
3942:
3943: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
3944:
3945: /*Sets the transaction_id*/
3946: SELECT mtl_system_items_interface_s.nextval
3947: INTO P_agdl_tbl(i).transaction_id
3948: FROM dual;
3949:
3950: lv_smt := 2;
3951:
3952: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
3953:
3954: /*Sets the View Privilege ID and returns an error is it doesnt existing in the system*/
3955: IF (P_agdl_tbl(i).view_privilege_id IS NULL
3956: AND P_agdl_tbl(i).view_privilege_name IS NOT NULL
3961: FROM fnd_form_functions
3962: WHERE function_name = P_agdl_tbl(i).view_privilege_name;
3963: EXCEPTION
3964: WHEN no_data_found THEN
3965: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_agdl_tbl(i).transaction_id||' (AG,DL,VS) = ('
3966: ||P_agdl_tbl(i).attr_group_name||','||P_agdl_tbl(i).data_level_name||','||P_agdl_tbl(i).view_privilege_name
3967: ||'). ' ||'View privilege does not exist in the system');
3968:
3969: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
3974: p_application_id => 'EGO',p_token_tbl => g_token_table,
3975: p_message_type => G_RET_STS_ERROR,p_row_identifier => P_agdl_tbl(i).transaction_id,
3976: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
3977: WHEN OTHERS THEN
3978: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_agdl_tbl(i).transaction_id||' (AG,DL,VS) = ('
3979: ||P_agdl_tbl(i).attr_group_name||','||P_agdl_tbl(i).data_level_name||','||P_agdl_tbl(i).view_privilege_name||'). '
3980: ||'Value_to_id_dl_tbl Exception when others smt 2');
3981:
3982: x_return_status := G_RET_STS_UNEXP_ERROR;
3985: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
3986: p_row_identifier => P_agdl_tbl(i).transaction_id,
3987: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
3988:
3989: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_dl_tbl smt 2- '||SQLERRM;
3990:
3991: RETURN;
3992: END;
3993: END IF;
3993: END IF;
3994:
3995: lv_smt := 3;
3996:
3997: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
3998:
3999: /*Sets the Edit Privilege ID and returns an error is it doesnt existing in the system*/
4000: IF (P_agdl_tbl(i).edit_privilege_id IS NULL
4001: AND P_agdl_tbl(i).edit_privilege_name IS NOT NULL
4006: FROM fnd_form_functions
4007: WHERE function_name = P_agdl_tbl(i).edit_privilege_name;
4008: EXCEPTION
4009: WHEN no_data_found THEN
4010: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_agdl_tbl(i).transaction_id||' (AG,DL,VS) = ('
4011: ||P_agdl_tbl(i).attr_group_name||','||P_agdl_tbl(i).data_level_name||','||P_agdl_tbl(i).edit_privilege_name||'). '
4012: ||'Edit privilege does not exist in the system');
4013:
4014: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
4019: p_application_id => 'EGO',p_token_tbl => g_token_table,
4020: p_message_type => G_RET_STS_ERROR,p_row_identifier => P_agdl_tbl(i).transaction_id,
4021: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
4022: WHEN OTHERS THEN
4023: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_agdl_tbl(i).transaction_id||' (AG,DL,VS) = ('
4024: ||P_agdl_tbl(i).attr_group_name||','||P_agdl_tbl(i).data_level_name||','||P_agdl_tbl(i).edit_privilege_name||'). '
4025: ||'Exception: '||SQLERRM);
4026:
4027: x_return_status := G_RET_STS_UNEXP_ERROR;
4030: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4031: p_row_identifier => P_agdl_tbl(i).transaction_id,
4032: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
4033:
4034: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_dl_tbl smt 3- '||SQLERRM;
4035:
4036: RETURN;
4037: END;
4038: END IF;
4038: END IF;
4039:
4040: lv_smt := 4;
4041:
4042: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
4043:
4044: /*Sets the DATA LEVEL ID and returns an error is it doesnt existing in the system*/
4045: IF (P_agdl_tbl(i).data_level_id IS NULL
4046: AND P_agdl_tbl(i).data_level_name IS NOT NULL) THEN
4052: AND application_id = g_ego_application_id
4053: AND data_level_name = P_agdl_tbl(i).data_level_name;
4054: EXCEPTION
4055: WHEN no_data_found THEN
4056: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_agdl_tbl(i).transaction_id||' (AG,DL) = ('||P_agdl_tbl(i).attr_group_name||','||P_agdl_tbl(i).data_level_name||'). ' ||'Data Level does not exist in the system');
4057:
4058: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
4059:
4060: x_return_status := G_RET_STS_ERROR;
4063: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4064: p_row_identifier => P_agdl_tbl(i).transaction_id,
4065: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
4066: WHEN OTHERS THEN
4067: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_agdl_tbl(i).transaction_id||' (AG,DL) = ('||P_agdl_tbl(i).attr_group_name||','||P_agdl_tbl(i).data_level_name||'). ' ||'Exception: '||SQLERRM);
4068:
4069: x_return_status := G_RET_STS_UNEXP_ERROR;
4070:
4071: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
4072: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4073: p_row_identifier => P_agdl_tbl(i).transaction_id,
4074: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
4075:
4076: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_dl_tbl smt 4 - '||SQLERRM;
4077:
4078: RETURN;
4079: END;
4080: END IF;
4080: END IF;
4081:
4082: lv_smt := 5;
4083:
4084: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
4085:
4086: /*Sets the SYNC Transaction Type to CREATE or UPDATE*/
4087: IF (P_agdl_tbl(i).data_level_id IS NOT NULL
4088: AND P_agdl_tbl(i).attr_group_id IS NOT NULL
4100: EXCEPTION
4101: WHEN no_data_found THEN
4102: P_agdl_tbl(i).transaction_type := G_OPR_CREATE;
4103: WHEN OTHERS THEN
4104: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_dl_tbl Exception when others smt 5');
4105:
4106: x_return_status := G_RET_STS_UNEXP_ERROR;
4107:
4108: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
4109: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4110: p_row_identifier => P_agdl_tbl(i).transaction_id,
4111: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
4112:
4113: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_dl_tbl smt 5 - '||SQLERRM;
4114:
4115: RETURN;
4116: END;
4117: END IF;
4118: END IF;
4119: END LOOP;
4120: EXCEPTION
4121: WHEN OTHERS THEN
4122: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_dl_tbl Exception when others'||SQLERRM);
4123:
4124: x_return_status := G_RET_STS_UNEXP_ERROR;
4125:
4126: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_dl_tbl - '||SQLERRM;
4122: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value_to_id_dl_tbl Exception when others'||SQLERRM);
4123:
4124: x_return_status := G_RET_STS_UNEXP_ERROR;
4125:
4126: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_dl_tbl - '||SQLERRM;
4127:
4128: RETURN;
4129: END Value_to_id_dl_tbl;
4130:
4138: IS
4139: lv_smt NUMBER; --Statement counter
4140: lv_proc VARCHAR2(30) := 'Value_to_id_attr_tbl';
4141: BEGIN
4142: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Value_to_id_attr_tbl');
4143:
4144: x_return_status := G_RET_STS_SUCCESS;
4145:
4146: FOR i IN p_attr_tbl.FIRST.. p_attr_tbl.LAST LOOP
4146: FOR i IN p_attr_tbl.FIRST.. p_attr_tbl.LAST LOOP
4147: IF (P_attr_tbl(i).process_status = G_PROCESS_RECORD) THEN
4148: lv_smt := 1;
4149:
4150: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
4151:
4152: /*Sets the Attribute group id when the Internal Attribute group name is given*/
4153: IF (P_attr_tbl(i).attr_group_id IS NULL
4154: AND P_attr_tbl(i).attr_group_name IS NOT NULL) THEN
4162: EXCEPTION
4163: WHEN no_data_found THEN
4164: x_return_status := G_RET_STS_ERROR;
4165:
4166: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_attr_tbl(i).transaction_id||' (AG,ATTR) = ('||P_attr_tbl(i).attr_group_name||','||P_attr_tbl(i).internal_name||'). ' ||'Attribute Group does not exist in the system');
4167:
4168: error_handler.Add_error_message(p_message_name => 'EGO_AG_INVALID',p_application_id => 'EGO',
4169: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4170: p_row_identifier => P_attr_tbl(i).transaction_id,
4171: p_entity_code => G_ENTITY_ATTR,p_table_name => G_ENTITY_ATTR_TAB);
4172: WHEN OTHERS THEN
4173: x_return_status := G_RET_STS_UNEXP_ERROR;
4174:
4175: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_attr_tbl(i).transaction_id||' (AG,ATTR) = ('||P_attr_tbl(i).attr_group_name||','||P_attr_tbl(i).internal_name||'). ' ||'Exception: '||SQLERRM);
4176:
4177: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
4178: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4179: p_row_identifier => P_attr_tbl(i).transaction_id,
4178: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4179: p_row_identifier => P_attr_tbl(i).transaction_id,
4180: p_entity_code => G_ENTITY_ATTR,p_table_name => G_ENTITY_ATTR_TAB);
4181:
4182: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_attr_tbl smt 1 - '||SQLERRM;
4183:
4184: RETURN;
4185: END;
4186: END IF;
4186: END IF;
4187:
4188: lv_smt := 2;
4189:
4190: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
4191:
4192: /*Sets the application ID*/
4193: P_attr_tbl(i).application_id := g_ego_application_id;
4194:
4193: P_attr_tbl(i).application_id := g_ego_application_id;
4194:
4195: lv_smt := 3;
4196:
4197: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
4198:
4199: /*Sets the Attr_id for the update and sync flow. Also handles convert SYNC to CREATE/UPDATE flow*/
4200: IF (P_attr_tbl(i).attr_id IS NULL
4201: AND P_attr_tbl(i).internal_name IS NOT NULL
4224: P_attr_tbl(i).transaction_type := G_OPR_CREATE;
4225: ELSE
4226: x_return_status := G_RET_STS_ERROR;
4227:
4228: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_attr_tbl(i).transaction_id||' (AG,ATTR) = ('||P_attr_tbl(i).attr_group_name||','||P_attr_tbl(i).internal_name||'). ' ||'Attribute does not exist in the system');
4229:
4230: error_handler.Add_error_message(p_message_name => 'EGO_ATTR_NOT_EXISTS',p_application_id => 'EGO',
4231: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4232: p_row_identifier => P_attr_tbl(i).transaction_id,
4234: END IF;
4235: WHEN OTHERS THEN
4236: x_return_status := G_RET_STS_UNEXP_ERROR;
4237:
4238: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4239: ||P_attr_tbl(i).attr_group_name||','||P_attr_tbl(i).internal_name||'). ' ||'Exception: '||SQLERRM);
4240:
4241: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
4242: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4242: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4243: p_row_identifier => P_attr_tbl(i).transaction_id,
4244: p_entity_code => G_ENTITY_ATTR,p_table_name => G_ENTITY_ATTR_TAB);
4245:
4246: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_attr_tbl smt 3 - '||SQLERRM;
4247:
4248: RETURN;
4249: END;
4250: END IF;
4250: END IF;
4251:
4252: lv_smt := 4;
4253:
4254: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
4255:
4256: /*Sets value set id when the value set name is given*/
4257: IF (P_attr_tbl(i).flex_value_set_id IS NULL
4258: AND P_attr_tbl(i).flex_value_set_name IS NOT NULL
4265: EXCEPTION
4266: WHEN no_data_found THEN
4267: x_return_status := G_RET_STS_ERROR;
4268:
4269: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_attr_tbl(i).transaction_id||' (AG,ATTR,VS) = ('
4270: ||P_attr_tbl(i).attr_group_name||','||P_attr_tbl(i).internal_name||','||P_attr_tbl(i).flex_value_set_name||'). '
4271: ||'Value Set does not exist');
4272:
4273: error_handler.Add_error_message(p_message_name => 'EGO_EF_BC_SEL_EXI_VALUE',p_application_id => 'EGO',
4276: p_entity_code => G_ENTITY_ATTR,p_table_name => G_ENTITY_ATTR_TAB);
4277: WHEN OTHERS THEN
4278: x_return_status := G_RET_STS_UNEXP_ERROR;
4279:
4280: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_attr_tbl(i).transaction_id||' (AG,ATTR,VS) = ('
4281: ||P_attr_tbl(i).attr_group_name||','||P_attr_tbl(i).internal_name||','||P_attr_tbl(i).flex_value_set_name||'). '
4282: ||'Exception : '||SQLERRM);
4283:
4284: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
4285: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4286: p_row_identifier => P_attr_tbl(i).transaction_id,
4287: p_entity_code => G_ENTITY_ATTR,p_table_name => G_ENTITY_ATTR_TAB);
4288:
4289: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_attr_tbl smt 4 - '||SQLERRM;
4290:
4291: RETURN;
4292: END;
4293: END IF;
4293: END IF;
4294: END IF;
4295: END LOOP;
4296:
4297: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_attr_tbl');
4298: EXCEPTION
4299: WHEN OTHERS THEN
4300: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||' Value_to_id_attr_tbl Exception when others'||SQLERRM);
4301:
4296:
4297: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Value_to_id_attr_tbl');
4298: EXCEPTION
4299: WHEN OTHERS THEN
4300: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||' Value_to_id_attr_tbl Exception when others'||SQLERRM);
4301:
4302: x_return_status := G_RET_STS_UNEXP_ERROR;
4303:
4304: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_attr_tbl - '||SQLERRM;
4300: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||' Value_to_id_attr_tbl Exception when others'||SQLERRM);
4301:
4302: x_return_status := G_RET_STS_UNEXP_ERROR;
4303:
4304: x_return_msg := 'ego_ag_bulkload_pvt.Value_to_id_attr_tbl - '||SQLERRM;
4305:
4306: RETURN;
4307:
4308: END Value_to_id_attr_tbl;
4344: lv_smt NUMBER; --Statement counter
4345: lv_proc VARCHAR2(30) := 'Common_ag_validations';
4346: l_old_ag_record ego_fnd_dsc_flx_ctx_ext%ROWTYPE; -- Bug 12394675.
4347: BEGIN
4348: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Common_ag_validations');
4349:
4350: x_return_status := G_RET_STS_SUCCESS;
4351:
4352: FOR i IN p_ag_tbl.FIRST.. p_ag_tbl.LAST LOOP
4353: IF (p_ag_tbl(i).process_status = G_PROCESS_RECORD) THEN
4354: /*Start of common validations*/
4355: lv_smt := 1;
4356:
4357: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
4358:
4359: IF P_ag_tbl(i).multi_row = 'N'
4360: AND Nvl(P_ag_tbl(i).variant,'N') = 'N'
4361: AND P_ag_tbl(i).num_of_cols = 0 THEN
4362: P_ag_tbl(i).process_status := G_ERROR_RECORD;
4363:
4364: x_return_status := G_RET_STS_ERROR;
4365:
4366: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_ag_tbl(i).transaction_id||' (AG) = ('||P_ag_tbl(i).attr_group_name||'). ' ||'Number of columns is not within acceptable range for Single Row Attribute group');
4367:
4368: error_handler.Add_error_message(p_message_name => 'EGO_EF_SIN_COL_VAL',p_application_id => 'EGO',
4369: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4370: p_row_identifier => P_ag_tbl(i).transaction_id,
4374: END IF;
4375:
4376: lv_smt := 2;
4377:
4378: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
4379:
4380: IF P_ag_tbl(i).multi_row = 'Y'
4381: AND P_ag_tbl(i).num_of_cols = 0 THEN
4382: P_ag_tbl(i).process_status := G_ERROR_RECORD;
4382: P_ag_tbl(i).process_status := G_ERROR_RECORD;
4383:
4384: x_return_status := G_RET_STS_ERROR;
4385:
4386: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_ag_tbl(i).transaction_id||' (AG) = ('||P_ag_tbl(i).attr_group_name||'). ' ||'Number of columns is not within acceptable range for Multi Row Attribute group');
4387:
4388: error_handler.Add_error_message(p_message_name => 'EGO_EF_MUL_COL_VAL',p_application_id => 'EGO',
4389: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4390: p_row_identifier => P_ag_tbl(i).transaction_id,
4392: END IF;
4393:
4394: lv_smt := 3;
4395:
4396: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
4397:
4398: IF P_ag_tbl(i).multi_row = 'Y'
4399: AND P_ag_tbl(i).num_of_rows = 0 THEN
4400: P_ag_tbl(i).process_status := G_ERROR_RECORD;
4400: P_ag_tbl(i).process_status := G_ERROR_RECORD;
4401:
4402: x_return_status := G_RET_STS_ERROR;
4403:
4404: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_ag_tbl(i).transaction_id||' (AG) = ('||P_ag_tbl(i).attr_group_name||'). ' ||'Number of columns is not within acceptable range for Multi Row Attribute group');
4405:
4406: error_handler.Add_error_message(p_message_name => 'EGO_EF_ROW_VAL',p_application_id => 'EGO',
4407: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4408: p_row_identifier => P_ag_tbl(i).transaction_id,
4410: END IF;
4411:
4412: lv_smt := 4;
4413:
4414: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
4415:
4416: IF P_ag_tbl(i).multi_row = 'Y'
4417: AND P_ag_tbl(i).variant = 'Y' THEN
4418: P_ag_tbl(i).process_status := G_ERROR_RECORD;
4418: P_ag_tbl(i).process_status := G_ERROR_RECORD;
4419:
4420: x_return_status := G_RET_STS_ERROR;
4421:
4422: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||P_ag_tbl(i).transaction_id||' (AG) = ('||P_ag_tbl(i).attr_group_name||'). ' ||'Invalid MultiRow Variant combination');
4423:
4424: error_handler.Add_error_message(p_message_name => 'EGO_MULROW_VAR_COMB',p_application_id => 'EGO',
4425: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4426: p_row_identifier => P_ag_tbl(i).transaction_id,
4428: END IF;
4429:
4430: lv_smt := 5;
4431:
4432: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
4433: --
4434: -- Bug 12412639. Issue#1 Update of AG is in sync
4435: -- was erroring out. We must check whether multirow or variant
4436: -- value changed before we throw the error.
4446: P_ag_tbl(i).process_status := G_ERROR_RECORD;
4447:
4448: x_return_status := G_RET_STS_ERROR;
4449:
4450: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '|| P_ag_tbl(i).transaction_id ||
4451: ' (AG) = ('||P_ag_tbl(i).attr_group_name||'). ' ||
4452: ' Updating the behaviour of attribute group is not allowed');
4453:
4454: error_handler.Add_error_message(p_message_name => 'EGO_UPD_BEHAVIOUR',p_application_id => 'EGO',
4460:
4461: END IF;
4462: END LOOP;
4463:
4464: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Common_ag_validations');
4465: EXCEPTION
4466: WHEN OTHERS THEN
4467: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Common_ag_validations Exception when others'||SQLERRM);
4468:
4463:
4464: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Common_ag_validations');
4465: EXCEPTION
4466: WHEN OTHERS THEN
4467: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Common_ag_validations Exception when others'||SQLERRM);
4468:
4469: x_return_status := G_RET_STS_UNEXP_ERROR;
4470:
4471: x_return_msg := 'ego_ag_bulkload_pvt.Common_ag_validations - '||SQLERRM;
4467: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Common_ag_validations Exception when others'||SQLERRM);
4468:
4469: x_return_status := G_RET_STS_UNEXP_ERROR;
4470:
4471: x_return_msg := 'ego_ag_bulkload_pvt.Common_ag_validations - '||SQLERRM;
4472:
4473: RETURN;
4474: END Common_ag_validations;
4475:
4483: IS
4484: lv_smt NUMBER; --Statement counter
4485: lv_proc VARCHAR2(30) := 'Common_dl_validations';
4486: BEGIN
4487: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Common_dl_validations');
4488:
4489: x_return_status := G_RET_STS_SUCCESS;
4490:
4491: FOR i IN p_agdl_tbl.FIRST.. p_agdl_tbl.LAST LOOP
4497: p_agdl_tbl(i).process_status := G_ERROR_RECORD;
4498:
4499: x_return_status := G_RET_STS_ERROR;
4500:
4501: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id||' (AG,DL) = ('||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). ' ||'Invalid Pre event flag.');
4502:
4503: error_handler.Add_error_message(p_message_name => 'EGO_INVALID_PRE_EVENT',p_application_id => 'EGO',
4504: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4505: p_row_identifier => p_agdl_tbl(i).transaction_id,
4513: p_agdl_tbl(i).process_status := G_ERROR_RECORD;
4514:
4515: x_return_status := G_RET_STS_ERROR;
4516:
4517: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id||' (AG,DL) = ('
4518: ||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). ' ||'Invalid post event flag.');
4519:
4520: error_handler.Add_error_message(p_message_name => 'EGO_INVALID_POST_EVENT',p_application_id => 'EGO',
4521: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4544: p_agdl_tbl(i).process_status := G_ERROR_RECORD;
4545:
4546: x_return_status := G_RET_STS_ERROR;
4547:
4548: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id||' (AG,DL) = ('
4549: ||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). '
4550: ||'Cannot delete the business entity since there is only one business entity');
4551:
4552: error_handler.Add_error_message(p_message_name => 'EGO_EF_DL_REQD_AG',p_application_id => 'EGO',
4560: p_agdl_tbl(i).process_status := G_ERROR_RECORD;
4561:
4562: x_return_status := G_RET_STS_ERROR;
4563:
4564: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id||' (AG,DL) = ('
4565: ||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). '
4566: ||'Cannot delete the business entity since its not associated to the AG');
4567:
4568: error_handler.Add_error_message(p_message_name => 'EGO_DL_NOT_ASSOC',p_application_id => 'EGO',
4571: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_TAB);
4572: WHEN OTHERS THEN
4573: x_return_status := G_RET_STS_UNEXP_ERROR;
4574:
4575: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id||' (AG,DL) = ('
4576: ||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). ' ||'Exception -'||SQLERRM);
4577:
4578: error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => 'EGO',
4579: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4595: p_agdl_tbl(i).process_status := G_ERROR_RECORD;
4596:
4597: x_return_status := G_RET_STS_ERROR;
4598:
4599: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id||' (AG,DL) = ('
4600: ||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). ' ||'Attribute group does not exist');
4601:
4602: error_handler.Add_error_message(p_message_name => 'EGO_AG_NOT_EXIST',p_application_id => 'EGO',
4603: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4605: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_TAB);
4606: WHEN OTHERS THEN
4607: x_return_status := G_RET_STS_UNEXP_ERROR;
4608:
4609: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_agdl_tbl(i).transaction_id||' (AG,DL) = ('
4610: ||p_agdl_tbl(i).attr_group_name||','||p_agdl_tbl(i).data_level_name||'). ' ||'Exception -'||SQLERRM);
4611:
4612: error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => 'EGO',
4613: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4618:
4619: END IF;
4620: END LOOP;
4621:
4622: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Common_dl_validations');
4623: EXCEPTION
4624: WHEN OTHERS THEN
4625: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Common_dl_validations Exception when others'||SQLERRM);
4626:
4621:
4622: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Common_dl_validations');
4623: EXCEPTION
4624: WHEN OTHERS THEN
4625: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Common_dl_validations Exception when others'||SQLERRM);
4626:
4627: x_return_status := G_RET_STS_UNEXP_ERROR;
4628:
4629: x_return_msg := 'ego_ag_bulkload_pvt.Common_dl_validations - '||SQLERRM;
4625: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Common_dl_validations Exception when others'||SQLERRM);
4626:
4627: x_return_status := G_RET_STS_UNEXP_ERROR;
4628:
4629: x_return_msg := 'ego_ag_bulkload_pvt.Common_dl_validations - '||SQLERRM;
4630:
4631: RETURN;
4632: END Common_dl_validations;
4633:
4642: lv_smt NUMBER; --Statement counter
4643: lv_proc VARCHAR2(30) := 'Common_attr_validations';
4644: lv_flag VARCHAR2(1):= 'N';
4645: BEGIN
4646: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Common_attr_validations');
4647:
4648: x_return_status := G_RET_STS_SUCCESS;
4649:
4650: FOR i IN p_attr_tbl.FIRST.. p_attr_tbl.LAST LOOP
4662: p_attr_tbl(i).process_status := G_ERROR_RECORD;
4663:
4664: x_return_status := G_RET_STS_ERROR;
4665:
4666: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4667: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Mandatory columns for the attribute are not populated');
4668:
4669: error_handler.Add_error_message(p_message_name => 'EGO_ATTR_MANDATORY',p_application_id => 'EGO',
4670: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4685: p_attr_tbl(i).process_status := G_ERROR_RECORD;
4686:
4687: x_return_status := G_RET_STS_ERROR;
4688:
4689: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4690: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Data type populated is invalid');
4691:
4692: error_handler.Add_error_message(p_message_name => 'EGO_DATA_TYPE_INVALID',p_application_id => 'EGO',
4693: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4717: p_attr_tbl(i).process_status := G_ERROR_RECORD;
4718:
4719: x_return_status := G_RET_STS_ERROR;
4720:
4721: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4722: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Data type and display as combination is invalid');
4723:
4724: error_handler.Add_error_message(p_message_name => 'EGO_DT_DISP_COMB_INVALID',p_application_id => 'EGO',
4725: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4735: p_attr_tbl(i).process_status := G_ERROR_RECORD;
4736:
4737: x_return_status := G_RET_STS_ERROR;
4738:
4739: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4740: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Value Set is mandatory for particular display');
4741:
4742: error_handler.Add_error_message(p_message_name => 'EGO_VALUE_SET_REQUIRED',p_application_id => 'EGO',
4743: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4752: p_attr_tbl(i).process_status := G_ERROR_RECORD;
4753:
4754: x_return_status := G_RET_STS_ERROR;
4755:
4756: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4757: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Value Set is mandatory for particular display');
4758:
4759: error_handler.Add_error_message(p_message_name => 'EGO_DYN_URL_MANDATORY',p_application_id => 'EGO',
4760: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4776: p_attr_tbl(i).process_status := G_ERROR_RECORD;
4777:
4778: x_return_status := G_RET_STS_ERROR;
4779:
4780: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4781: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'SEQUENCE already exists in the system - CREATE');
4782:
4783: error_handler.Add_error_message(p_message_name => 'EGO_EF_CR_ATTR_DUP_SEQ_ERR',p_application_id => 'EGO',
4784: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4793: NULL;
4794: WHEN OTHERS THEN
4795: x_return_status := G_RET_STS_UNEXP_ERROR;
4796:
4797: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4798: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Exception - '||SQLERRM);
4799:
4800: error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => 'EGO',
4801: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4834: p_attr_tbl(i).process_status := G_ERROR_RECORD;
4835:
4836: x_return_status := G_RET_STS_ERROR;
4837:
4838: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4839: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'SEQUENCE already exists in the system - UPDATE');
4840:
4841: error_handler.Add_error_message(p_message_name => 'EGO_EF_UP_ATTR_DUP_SEQ_ERR',p_application_id => 'EGO',
4842: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4848: EXCEPTION
4849: WHEN OTHERS THEN
4850: x_return_status := G_RET_STS_UNEXP_ERROR;
4851:
4852: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4853: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Exception - '||SQLERRM);
4854:
4855: error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => 'EGO',
4856: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4873: p_attr_tbl(i).process_status := G_ERROR_RECORD;
4874:
4875: x_return_status := G_RET_STS_ERROR;
4876:
4877: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4878: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'SEQUENCE already exists in the system - CREATE');
4879:
4880: error_handler.Add_error_message(p_message_name => 'EGO_EF_CR_ATTR_DUP_COL_ERR',p_application_id => 'EGO',
4881: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4890: NULL;
4891: WHEN OTHERS THEN
4892: x_return_status := G_RET_STS_UNEXP_ERROR;
4893:
4894: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4895: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Exception - '||SQLERRM);
4896:
4897: error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => 'EGO',
4898: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
4932: p_attr_tbl(i).process_status := G_ERROR_RECORD;
4933:
4934: x_return_status := G_RET_STS_ERROR;
4935:
4936: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4937: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'SEQUENCE already exists in the system - UPDATE');
4938:
4939: error_handler.Add_error_message(p_message_name => 'EGO_EF_UP_ATTR_DUP_SEQ_ERR',p_application_id => 'EGO',
4940: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
4947: EXCEPTION
4948: WHEN OTHERS THEN
4949: x_return_status := G_RET_STS_UNEXP_ERROR;
4950:
4951: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
4952: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Exception - '||SQLERRM);
4953:
4954: error_handler.Add_error_message(p_message_text => SQLERRM,p_application_id => 'EGO',
4955: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5010: p_attr_tbl(i).process_status := G_ERROR_RECORD;
5011:
5012: x_return_status := G_RET_STS_ERROR;
5013:
5014: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
5015: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Column name and data type dont match');
5016:
5017: error_handler.Add_error_message(p_message_name => 'EGO_EF_CR_ATTR_COL_DT_ERR',p_application_id => 'EGO',
5018: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5029: p_attr_tbl(i).process_status := G_ERROR_RECORD;
5030:
5031: x_return_status := G_RET_STS_ERROR;
5032:
5033: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
5034: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Default value exceeds the acceptable length');
5035:
5036: error_handler.Add_error_message(p_message_name => 'EGO_EF_DEFAULT_VAL_LEN_ERR',p_application_id => 'EGO',
5037: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5048: p_attr_tbl(i).process_status := G_ERROR_RECORD;
5049:
5050: x_return_status := G_RET_STS_ERROR;
5051:
5052: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
5053: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Column name and data type dont match');
5054:
5055: error_handler.Add_error_message(p_message_name => 'EGO_EF_CR_ATTR_COL_DT_ERR',p_application_id => 'EGO',
5056: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5066: p_attr_tbl(i).process_status := G_ERROR_RECORD;
5067:
5068: x_return_status := G_RET_STS_ERROR;
5069:
5070: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
5071: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Invalid required flag.');
5072:
5073: G_TOKEN_TABLE(1).Token_Name := 'COL_NAME';
5074: G_TOKEN_TABLE(1).Token_Value := 'REQUIRED_FLAG';
5089: p_attr_tbl(i).process_status := G_ERROR_RECORD;
5090:
5091: x_return_status := G_RET_STS_ERROR;
5092:
5093: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
5094: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Invalid enabled flag.');
5095:
5096: G_TOKEN_TABLE(1).Token_Name := 'COL_NAME';
5097: G_TOKEN_TABLE(1).Token_Value := 'ENABLED_FLAG';
5112: p_attr_tbl(i).process_status := G_ERROR_RECORD;
5113:
5114: x_return_status := G_RET_STS_ERROR;
5115:
5116: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
5117: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Invalid security enabled flag.');
5118:
5119: G_TOKEN_TABLE(1).Token_Name := 'COL_NAME';
5120: G_TOKEN_TABLE(1).Token_Value := 'SECURITY_ENABLED_FLAG';
5135: p_attr_tbl(i).process_status := G_ERROR_RECORD;
5136:
5137: x_return_status := G_RET_STS_ERROR;
5138:
5139: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
5140: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Invalid searchable flag.');
5141:
5142: G_TOKEN_TABLE(1).Token_Name := 'COL_NAME';
5143: G_TOKEN_TABLE(1).Token_Value := 'SEARCH_FLAG';
5158: p_attr_tbl(i).process_status := G_ERROR_RECORD;
5159:
5160: x_return_status := G_RET_STS_ERROR;
5161:
5162: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_attr_tbl(i).transaction_id||' (AG,ATTR) = ('
5163: ||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'Invalid unique key flag.');
5164:
5165: G_TOKEN_TABLE(1).Token_Name := 'COL_NAME';
5166: G_TOKEN_TABLE(1).Token_Value := 'UNIQUE_KEY_FLAG';
5174:
5175: END IF;
5176: END IF;
5177: END LOOP;
5178: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Common_attr_validations');
5179: EXCEPTION
5180: WHEN OTHERS THEN
5181: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Common_attr_validations Exception when others'||SQLERRM);
5182:
5177: END LOOP;
5178: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Common_attr_validations');
5179: EXCEPTION
5180: WHEN OTHERS THEN
5181: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Common_attr_validations Exception when others'||SQLERRM);
5182:
5183: x_return_status := G_RET_STS_UNEXP_ERROR;
5184:
5185: x_return_msg := 'ego_ag_bulkload_pvt.Common_attr_validations - '||SQLERRM;
5181: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Common_attr_validations Exception when others'||SQLERRM);
5182:
5183: x_return_status := G_RET_STS_UNEXP_ERROR;
5184:
5185: x_return_msg := 'ego_ag_bulkload_pvt.Common_attr_validations - '||SQLERRM;
5186:
5187: RETURN;
5188: END Common_attr_validations;
5189:
5199: lv_smt NUMBER; --Statement counter
5200: lv_flag VARCHAR2(1);
5201: lv_proc VARCHAR2(30) := 'Additional_agdl_validations';
5202: BEGIN
5203: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Additional_agdl_validations');
5204:
5205: x_return_status := G_RET_STS_SUCCESS;
5206:
5207: lv_smt := 1;
5205: x_return_status := G_RET_STS_SUCCESS;
5206:
5207: lv_smt := 1;
5208:
5209: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
5210:
5211: /*Check for invalid transaction type in the AG table*/
5212: FOR i IN p_ag_tbl.FIRST.. p_ag_tbl.LAST LOOP
5213: /*Convert transaction type to upper case*/
5220: OR P_ag_tbl(i).transaction_type = G_OPR_DELETE
5221: OR P_ag_tbl(i).transaction_type = G_OPR_SYNC) THEN
5222: NULL;
5223: ELSE
5224: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Transaction type Invalid for AG');
5225:
5226: P_ag_tbl(i).process_status := G_ERROR_RECORD;
5227:
5228: x_return_status := G_RET_STS_ERROR;
5238: P_ag_tbl(i).process_status := G_ERROR_RECORD;
5239:
5240: x_return_status := G_RET_STS_ERROR;
5241:
5242: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute group Type passed is incorrect');
5243:
5244: error_handler.Add_error_message(p_message_name => 'EGO_AG_TYPE_INVALID',p_application_id => 'EGO',
5245: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5246: p_row_identifier => P_ag_tbl(i).transaction_id,
5249: END LOOP;
5250:
5251: lv_smt := 2;
5252:
5253: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
5254:
5255: /*Check for invalid transaction type in the DL table*/
5256: FOR i IN p_agdl_tbl.FIRST.. p_agdl_tbl.LAST LOOP
5257: /*Convert transaction type to upper case*/
5263: OR P_agdl_tbl(i).transaction_type = G_OPR_UPDATE
5264: OR P_agdl_tbl(i).transaction_type = G_OPR_SYNC) THEN
5265: NULL;
5266: ELSE
5267: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Transaction type is invalid for DL');
5268:
5269: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5270:
5271: x_return_status := G_RET_STS_ERROR;
5269: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5270:
5271: x_return_status := G_RET_STS_ERROR;
5272:
5273: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Invalid transaction type for DL');
5274:
5275: error_handler.Add_error_message(p_message_name => 'EGO_TRANS_TYPE_INVALID',p_application_id => 'EGO',
5276: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5277: p_row_identifier => P_agdl_tbl(i).transaction_id,
5284: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5285:
5286: x_return_status := G_RET_STS_ERROR;
5287:
5288: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute group Type passed is incorrect');
5289:
5290: error_handler.Add_error_message(p_message_name => 'EGO_AG_TYPE_INVALID',p_application_id => 'EGO',
5291: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5292: p_row_identifier => P_agdl_tbl(i).transaction_id,
5299: Value_to_id_ag_tbl(p_ag_tbl,x_return_status, x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
5300:
5301: lv_smt := 3;
5302:
5303: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
5304:
5305: /*If the attribute group in the DL plsql table doesn't exist in the system as well in the AG plsql table then error OUT NOCOPY with message EGO_AG_INVALID*/
5306: FOR i IN p_agdl_tbl.FIRST.. p_ag_tbl.LAST LOOP
5307: IF (P_agdl_tbl(i).process_status = G_PROCESS_RECORD) THEN
5320: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5321:
5322: x_return_status := G_RET_STS_ERROR;
5323:
5324: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute Group does not exist');
5325:
5326: error_handler.Add_error_message(p_message_name => 'EGO_AG_INVALID',p_application_id => 'EGO',
5327: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5328: p_row_identifier => P_agdl_tbl(i).transaction_id,
5346: END IF;
5347: END LOOP;
5348:
5349: IF (lv_flag = 'N') THEN
5350: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute Group in the DL plsql table does not exist in the system or in the AG plsql table');
5351:
5352: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5353:
5354: x_return_status := G_RET_STS_ERROR;
5359: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5360: END IF;
5361: END IF;
5362: WHEN OTHERS THEN
5363: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 3');
5364:
5365: x_return_status := G_RET_STS_UNEXP_ERROR;
5366:
5367: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5368: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5369: p_row_identifier => P_agdl_tbl(i).transaction_id,
5370: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5371:
5372: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 3 - '||SQLERRM;
5373:
5374: RETURN;
5375: END;
5376: END IF;
5381: Value_to_id_dl_tbl(p_agdl_tbl,x_return_status, x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
5382:
5383: lv_smt := 4;
5384:
5385: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 4');
5386:
5387: /*For a AG record of transaction Type CREATE there should be atleast one record in the DL record. Else error OUT NOCOPY EGO_EF_DL_REQD_AG*/
5388: FOR i IN p_ag_tbl.FIRST.. p_ag_tbl.LAST LOOP
5389: lv_flag := 'N';
5395: END IF;
5396: END LOOP;
5397:
5398: IF (lv_flag = 'N') THEN
5399: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Please insert at least one business entity when creating an Attribute Group');
5400:
5401: P_ag_tbl(i).process_status := G_ERROR_RECORD;
5402:
5403: x_return_status := G_RET_STS_ERROR;
5411: END LOOP;
5412:
5413: lv_smt := 5;
5414:
5415: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 5');
5416:
5417: /*Additional Validations for AG*/
5418: FOR i IN p_ag_tbl.FIRST.. p_ag_tbl.LAST LOOP
5419: IF (P_ag_tbl(i).process_status = G_PROCESS_RECORD) THEN
5429: AND descriptive_flexfield_name = P_ag_tbl(i).attr_group_type
5430: AND descriptive_flex_context_code = P_ag_tbl(i).attr_group_id;
5431: EXCEPTION
5432: WHEN no_data_found THEN
5433: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute group does not exist in the system');
5434:
5435: P_ag_tbl(i).process_status := G_ERROR_RECORD;
5436:
5437: x_return_status := G_RET_STS_ERROR;
5440: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5441: p_row_identifier => P_ag_tbl(i).transaction_id,
5442: p_entity_code => G_ENTITY_AG,p_table_name => G_ENTITY_AG_TAB);
5443: WHEN OTHERS THEN
5444: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 5');
5445:
5446: x_return_status := G_RET_STS_UNEXP_ERROR;
5447:
5448: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5449: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5450: p_row_identifier => P_ag_tbl(i).transaction_id,
5451: p_entity_code => G_ENTITY_AG,p_table_name => G_ENTITY_AG_TAB);
5452:
5453: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 5 - '||SQLERRM;
5454:
5455: RETURN;
5456: END;
5457: END IF;
5465: /*If the attribute group internal name or the display name is null for the CREATE flow then flag an error EGO_AG_MANDATORY*/
5466: IF ((P_ag_tbl(i).attr_group_name IS NULL
5467: OR P_ag_tbl(i).attr_group_disp_name IS NULL)
5468: AND P_ag_tbl(i).transaction_type = G_OPR_CREATE) THEN
5469: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute group internal name and display name are not provided in the CREATE flow');
5470:
5471: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5472:
5473: x_return_status := G_RET_STS_ERROR;
5481: END LOOP;
5482:
5483: lv_smt := 6;
5484:
5485: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 6');
5486:
5487: /*Additional Validation for DL*/
5488: FOR i IN p_agdl_tbl.FIRST.. p_agdl_tbl.LAST LOOP
5489: IF (P_agdl_tbl(i).process_status = G_PROCESS_RECORD) THEN
5499: AND descriptive_flexfield_name = P_agdl_tbl(i).attr_group_type
5500: AND descriptive_flex_context_code = P_agdl_tbl(i).attr_group_id;
5501: EXCEPTION
5502: WHEN no_data_found THEN
5503: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute group does not exist in the system');
5504:
5505: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5506:
5507: x_return_status := G_RET_STS_ERROR;
5510: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5511: p_row_identifier => P_agdl_tbl(i).transaction_id,
5512: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5513: WHEN OTHERS THEN
5514: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 6');
5515:
5516: x_return_status := G_RET_STS_UNEXP_ERROR;
5517:
5518: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5519: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5520: p_row_identifier => P_agdl_tbl(i).transaction_id,
5521: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5522:
5523: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 6 - '||SQLERRM;
5524:
5525: RETURN;
5526: END;
5527: END IF;
5537: FROM fnd_form_functions
5538: WHERE function_id = P_agdl_tbl(i).view_privilege_id;
5539: EXCEPTION
5540: WHEN no_data_found THEN
5541: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'View Privilege provided is invalid');
5542:
5543: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5544:
5545: x_return_status := G_RET_STS_ERROR;
5548: p_application_id => 'EGO',p_token_tbl => g_token_table,
5549: p_message_type => G_RET_STS_ERROR,p_row_identifier => P_agdl_tbl(i).transaction_id,
5550: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5551: WHEN OTHERS THEN
5552: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 7');
5553:
5554: x_return_status := G_RET_STS_UNEXP_ERROR;
5555:
5556: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5557: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5558: p_row_identifier => P_agdl_tbl(i).transaction_id,
5559: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5560:
5561: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 7 - '||SQLERRM;
5562:
5563: RETURN;
5564: END;
5565: END IF;
5574: FROM fnd_form_functions
5575: WHERE function_id = P_agdl_tbl(i).edit_privilege_id;
5576: EXCEPTION
5577: WHEN no_data_found THEN
5578: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Edit Privilege provided is invalid');
5579:
5580: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5581:
5582: x_return_status := G_RET_STS_ERROR;
5585: p_application_id => 'EGO',p_token_tbl => g_token_table,
5586: p_message_type => G_RET_STS_ERROR,p_row_identifier => P_agdl_tbl(i).transaction_id,
5587: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5588: WHEN OTHERS THEN
5589: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 8');
5590:
5591: x_return_status := G_RET_STS_UNEXP_ERROR;
5592:
5593: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5594: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5595: p_row_identifier => P_agdl_tbl(i).transaction_id,
5596: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5597:
5598: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 8 - '||SQLERRM;
5599:
5600: RETURN;
5601: END;
5602: END IF;
5614: AND language = Userenv('LANG');
5615: EXCEPTION
5616: WHEN no_data_found THEN
5617: IF (P_agdl_tbl(i).data_level_name <> 'ITEM_REVISION_LEVEL') THEN
5618: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'The defaulting provided is invalid');
5619:
5620: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5621:
5622: x_return_status := G_RET_STS_ERROR;
5626: p_row_identifier => P_agdl_tbl(i).transaction_id,
5627: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5628: END IF;
5629: WHEN OTHERS THEN
5630: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 9');
5631:
5632: x_return_status := G_RET_STS_UNEXP_ERROR;
5633:
5634: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5635: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5636: p_row_identifier => P_agdl_tbl(i).transaction_id,
5637: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5638:
5639: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 9 - '||SQLERRM;
5640:
5641: RETURN;
5642: END;
5643: END IF;
5655: AND language = Userenv('LANG');
5656: EXCEPTION
5657: WHEN no_data_found THEN
5658: IF (P_agdl_tbl(i).data_level_name <> 'ITEM_REVISION_LEVEL') THEN
5659: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'The defaulting name provided is invalid');
5660:
5661: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5662:
5663: x_return_status := G_RET_STS_ERROR;
5667: p_row_identifier => P_agdl_tbl(i).transaction_id,
5668: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5669: END IF;
5670: WHEN OTHERS THEN
5671: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 10');
5672:
5673: x_return_status := G_RET_STS_UNEXP_ERROR;
5674:
5675: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5676: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5677: p_row_identifier => P_agdl_tbl(i).transaction_id,
5678: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5679:
5680: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 10 - '||SQLERRM;
5681:
5682: RETURN;
5683: END;
5684: END IF;
5694: AND application_id = g_ego_application_id
5695: AND data_level_id = P_agdl_tbl(i).data_level_id;
5696: EXCEPTION
5697: WHEN no_data_found THEN
5698: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Data Level provided is invalid');
5699:
5700: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5701:
5702: x_return_status := G_RET_STS_ERROR;
5705: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5706: p_row_identifier => P_agdl_tbl(i).transaction_id,
5707: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5708: WHEN OTHERS THEN
5709: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 11');
5710:
5711: x_return_status := G_RET_STS_UNEXP_ERROR;
5712:
5713: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5714: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5715: p_row_identifier => P_agdl_tbl(i).transaction_id,
5716: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5717:
5718: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 11 - '||SQLERRM;
5719:
5720: RETURN;
5721: END;
5722: END IF;
5731: FROM ego_fnd_dsc_flx_ctx_ext
5732: WHERE attr_group_id = P_agdl_tbl(i).attr_group_id;
5733:
5734: IF (lv_flag = 'Y') THEN
5735: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute group of type Variant can only set ITEM as the business entity and with SKU as NULL');
5736:
5737: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5738:
5739: x_return_status := G_RET_STS_ERROR;
5744: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5745: END IF;
5746: EXCEPTION
5747: WHEN no_data_found THEN
5748: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute Group is invalid');
5749:
5750: x_return_status := G_RET_STS_ERROR;
5751:
5752: error_handler.Add_error_message(p_message_name => 'EGO_AG_INVALID',p_application_id => 'EGO',
5753: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5754: p_row_identifier => P_agdl_tbl(i).transaction_id,
5755: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5756: WHEN OTHERS THEN
5757: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 12');
5758:
5759: x_return_status := G_RET_STS_UNEXP_ERROR;
5760:
5761: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5762: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5763: p_row_identifier => P_agdl_tbl(i).transaction_id,
5764: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5765:
5766: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 12 - '||SQLERRM;
5767:
5768: RETURN;
5769: END;
5770: END IF;
5786: FROM ego_fnd_dsc_flx_ctx_ext
5787: WHERE attr_group_id = P_agdl_tbl(i).attr_group_id;
5788:
5789: IF (lv_flag = 'N') THEN
5790: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'The Behaviour and Business Entity combination is incorrect');
5791:
5792: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5793:
5794: x_return_status := G_RET_STS_ERROR;
5799: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5800: END IF;
5801: EXCEPTION
5802: WHEN no_data_found THEN
5803: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute group is invalid');
5804:
5805: x_return_status := G_RET_STS_ERROR;
5806:
5807: error_handler.Add_error_message(p_message_name => 'EGO_AG_INVALID',p_application_id => 'EGO',
5808: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5809: p_row_identifier => P_agdl_tbl(i).transaction_id,
5810: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5811: WHEN OTHERS THEN
5812: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 13');
5813:
5814: x_return_status := G_RET_STS_UNEXP_ERROR;
5815:
5816: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5817: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5818: p_row_identifier => P_agdl_tbl(i).transaction_id,
5819: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5820:
5821: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 13 - '||SQLERRM;
5822:
5823: RETURN;
5824: END;
5825: END IF;
5843: FROM ego_fnd_dsc_flx_ctx_ext
5844: WHERE attr_group_id = P_agdl_tbl(i).attr_group_id;
5845:
5846: IF (lv_flag = 'N') THEN
5847: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute Group behaviour, business entity and the style to SKU combination in invalid');
5848:
5849: P_agdl_tbl(i).process_status := G_ERROR_RECORD;
5850:
5851: x_return_status := G_RET_STS_ERROR;
5856: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5857: END IF;
5858: EXCEPTION
5859: WHEN no_data_found THEN
5860: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute group is invalid');
5861:
5862: x_return_status := G_RET_STS_ERROR;
5863:
5864: error_handler.Add_error_message(p_message_name => 'EGO_AG_INVALID',p_application_id => 'EGO',
5865: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5866: p_row_identifier => P_agdl_tbl(i).transaction_id,
5867: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5868: WHEN OTHERS THEN
5869: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others smt 14');
5870:
5871: x_return_status := G_RET_STS_UNEXP_ERROR;
5872:
5873: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5874: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5875: p_row_identifier => P_agdl_tbl(i).transaction_id,
5876: p_entity_code => G_ENTITY_DL,p_table_name => G_ENTITY_DL_tab);
5877:
5878: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations smt 14 - '||SQLERRM;
5879:
5880: RETURN;
5881: END;
5882: END IF;
5883: END IF;
5884: END LOOP;
5885: EXCEPTION
5886: WHEN OTHERS THEN
5887: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others'||SQLERRM);
5888:
5889: x_return_status := G_RET_STS_UNEXP_ERROR;
5890:
5891: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations - '||SQLERRM;
5887: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_agdl_validations Exception when others'||SQLERRM);
5888:
5889: x_return_status := G_RET_STS_UNEXP_ERROR;
5890:
5891: x_return_msg := 'ego_ag_bulkload_pvt.Additional_agdl_validations - '||SQLERRM;
5892:
5893: RETURN;
5894: END Additional_agdl_validations;
5895:
5903: IS
5904: lv_smt NUMBER; --Statement counter
5905: lv_proc VARCHAR2(30) := 'Additional_attr_validations';
5906: BEGIN
5907: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering Additional_attr_validations');
5908:
5909: x_return_status := G_RET_STS_SUCCESS;
5910:
5911: lv_smt := 1;
5909: x_return_status := G_RET_STS_SUCCESS;
5910:
5911: lv_smt := 1;
5912:
5913: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
5914:
5915: FOR i IN p_attr_tbl.FIRST.. p_attr_tbl.LAST LOOP
5916: /*Convert transaction type to upper case*/
5917: SELECT Upper(P_attr_tbl(i).transaction_type)
5924: OR P_attr_tbl(i).transaction_type = G_OPR_DELETE
5925: OR P_attr_tbl(i).transaction_type = G_OPR_SYNC) THEN
5926: NULL;
5927: ELSE
5928: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Transaction type Invalid for Attributes');
5929:
5930: P_attr_tbl(i).process_status := G_ERROR_RECORD;
5931:
5932: x_return_status := G_RET_STS_ERROR;
5930: P_attr_tbl(i).process_status := G_ERROR_RECORD;
5931:
5932: x_return_status := G_RET_STS_ERROR;
5933:
5934: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Transaction Type passed is incorrect');
5935:
5936: error_handler.Add_error_message(p_message_name => 'EGO_TRANS_TYPE_INVALID',p_application_id => 'EGO',
5937: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5938: p_row_identifier => P_attr_tbl(i).transaction_id,
5944: P_attr_tbl(i).process_status := G_ERROR_RECORD;
5945:
5946: x_return_status := G_RET_STS_ERROR;
5947:
5948: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute group Type passed is incorrect');
5949:
5950: error_handler.Add_error_message(p_message_name => 'EGO_AG_TYPE_INVALID',p_application_id => 'EGO',
5951: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5952: p_row_identifier => P_attr_tbl(i).transaction_id,
5960: FOR i IN p_attr_tbl.FIRST.. p_attr_tbl.LAST LOOP
5961: IF (P_attr_tbl(i).process_status = G_PROCESS_RECORD) THEN
5962: lv_smt := 1;
5963:
5964: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 1');
5965:
5966: /*Sets the attribute group name when the attribute group id is given*/
5967: IF (P_attr_tbl(i).attr_group_id IS NOT NULL
5968: AND P_attr_tbl(i).attr_group_name IS NULL) THEN
5976: EXCEPTION
5977: WHEN no_data_found THEN
5978: x_return_status := G_RET_STS_ERROR;
5979:
5980: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute Group does not exist');
5981:
5982: error_handler.Add_error_message(p_message_name => 'EGO_AG_INVALID',p_application_id => 'EGO',
5983: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
5984: p_row_identifier => P_attr_tbl(i).transaction_id,
5985: p_entity_code => G_ENTITY_ATTR,p_table_name => G_ENTITY_ATTR_TAB);
5986: WHEN OTHERS THEN
5987: x_return_status := G_RET_STS_UNEXP_ERROR;
5988:
5989: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_attr_validations when others smt 1');
5990:
5991: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
5992: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5993: p_row_identifier => P_attr_tbl(i).transaction_id,
5992: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
5993: p_row_identifier => P_attr_tbl(i).transaction_id,
5994: p_entity_code => G_ENTITY_ATTR,p_table_name => G_ENTITY_ATTR_TAB);
5995:
5996: x_return_msg := 'ego_ag_bulkload_pvt.Additional_attr_validations smt 1- '||SQLERRM;
5997:
5998: RETURN;
5999: END;
6000: END IF;
6000: END IF;
6001:
6002: lv_smt := 2;
6003:
6004: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 2');
6005:
6006: /*Sets the internal attribute name when the attribute id is given*/
6007: IF (P_attr_tbl(i).attr_id IS NOT NULL
6008: AND P_attr_tbl(i).internal_name IS NULL) THEN
6029: P_attr_tbl(i).transaction_type := G_OPR_CREATE;
6030: ELSE
6031: x_return_status := G_RET_STS_ERROR;
6032:
6033: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Attribute does not exist');
6034:
6035: error_handler.Add_error_message(p_message_name => 'EGO_ATTR_NOT_EXISTS',p_application_id => 'EGO',
6036: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
6037: p_row_identifier => P_attr_tbl(i).transaction_id,
6039: END IF;
6040: WHEN OTHERS THEN
6041: x_return_status := G_RET_STS_UNEXP_ERROR;
6042:
6043: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_attr_validations Exception when others smt 2');
6044:
6045: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
6046: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
6047: p_row_identifier => P_attr_tbl(i).transaction_id,
6046: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
6047: p_row_identifier => P_attr_tbl(i).transaction_id,
6048: p_entity_code => G_ENTITY_ATTR,p_table_name => G_ENTITY_ATTR_TAB);
6049:
6050: x_return_msg := 'ego_ag_bulkload_pvt.Additional_attr_validations smt 2- '||SQLERRM;
6051:
6052: RETURN;
6053: END;
6054: END IF;
6054: END IF;
6055:
6056: lv_smt := 3;
6057:
6058: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Statement 3');
6059:
6060: /*Sets the value set name when the value set id is given*/
6061: IF (P_attr_tbl(i).flex_value_set_id IS NOT NULL
6062: AND P_attr_tbl(i).flex_value_set_id <> G_NULL_NUM
6069: EXCEPTION
6070: WHEN no_data_found THEN
6071: x_return_status := G_RET_STS_ERROR;
6072:
6073: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Value Set does not exist in the system');
6074:
6075: error_handler.Add_error_message(p_message_name => 'EGO_EF_BC_SEL_EXI_VALUE',p_application_id => 'EGO',
6076: p_token_tbl => g_token_table,p_message_type => G_RET_STS_ERROR,
6077: p_row_identifier => P_attr_tbl(i).transaction_id,
6078: p_entity_code => G_ENTITY_ATTR,p_table_name => G_ENTITY_ATTR_TAB);
6079: WHEN OTHERS THEN
6080: x_return_status := G_RET_STS_UNEXP_ERROR;
6081:
6082: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_attr_validations Exception when others smt 3');
6083:
6084:
6085: error_handler.Add_error_message(p_message_text => sqlerrm,p_application_id => 'EGO',
6086: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
6086: p_token_tbl => g_token_table,p_message_type => G_RET_STS_UNEXP_ERROR,
6087: p_row_identifier => P_attr_tbl(i).transaction_id,
6088: p_entity_code => G_ENTITY_ATTR,p_table_name => G_ENTITY_ATTR_TAB);
6089:
6090: x_return_msg := 'ego_ag_bulkload_pvt.Additional_attr_validations smt 3 - '||SQLERRM;
6091:
6092: RETURN;
6093: END;
6094: END IF;
6094: END IF;
6095: END IF;
6096: END LOOP;
6097:
6098: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit Additional_attr_validations');
6099: EXCEPTION
6100: WHEN OTHERS THEN
6101: x_return_status := G_RET_STS_UNEXP_ERROR;
6102:
6099: EXCEPTION
6100: WHEN OTHERS THEN
6101: x_return_status := G_RET_STS_UNEXP_ERROR;
6102:
6103: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_attr_validations Exception when others'||SQLERRM);
6104:
6105: x_return_msg := 'ego_ag_bulkload_pvt.Additional_attr_validations - '||SQLERRM;
6106:
6107: RETURN;
6101: x_return_status := G_RET_STS_UNEXP_ERROR;
6102:
6103: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Additional_attr_validations Exception when others'||SQLERRM);
6104:
6105: x_return_msg := 'ego_ag_bulkload_pvt.Additional_attr_validations - '||SQLERRM;
6106:
6107: RETURN;
6108: END Additional_attr_validations;
6109:
6117: IS
6118: lv_proc VARCHAR2(30) := 'update_intf_attr_groups';
6119: trans_id dbms_sql.number_table;
6120: BEGIN
6121: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering update_intf_attr_groups');
6122:
6123: x_return_status := G_RET_STS_SUCCESS;
6124:
6125: FOR i IN p_ag_tbl.FIRST..p_ag_tbl.LAST LOOP
6130: UPDATE ego_attr_groups_interface
6131: SET ROW = p_ag_tbl(i)
6132: WHERE transaction_id = trans_id(i);
6133:
6134: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit update_intf_attr_groups');
6135: EXCEPTION
6136: WHEN OTHERS THEN
6137: x_return_status := G_RET_STS_UNEXP_ERROR;
6138:
6135: EXCEPTION
6136: WHEN OTHERS THEN
6137: x_return_status := G_RET_STS_UNEXP_ERROR;
6138:
6139: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'update_intf_attr_groups Exception when others'||SQLERRM);
6140:
6141: x_return_msg := 'ego_ag_bulkload_pvt.update_intf_attr_groups - '||SQLERRM;
6142:
6143: RETURN;
6137: x_return_status := G_RET_STS_UNEXP_ERROR;
6138:
6139: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'update_intf_attr_groups Exception when others'||SQLERRM);
6140:
6141: x_return_msg := 'ego_ag_bulkload_pvt.update_intf_attr_groups - '||SQLERRM;
6142:
6143: RETURN;
6144: END update_intf_attr_groups;
6145:
6153: IS
6154: lv_proc VARCHAR2(30) := 'update_intf_data_level';
6155: trans_id dbms_sql.number_table;
6156: BEGIN
6157: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering update_intf_data_level');
6158:
6159: x_return_status := G_RET_STS_SUCCESS;
6160:
6161: FOR i IN p_agdl_tbl.FIRST..p_agdl_tbl.LAST LOOP
6167: SET ROW = p_agdl_tbl(i)
6168: WHERE transaction_id = trans_id(i);
6169:
6170:
6171: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit update_intf_data_level');
6172: EXCEPTION
6173: WHEN OTHERS THEN
6174: x_return_status := G_RET_STS_UNEXP_ERROR;
6175:
6172: EXCEPTION
6173: WHEN OTHERS THEN
6174: x_return_status := G_RET_STS_UNEXP_ERROR;
6175:
6176: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'update_intf_data_level Exception when others'||SQLERRM);
6177:
6178: x_return_msg := 'ego_ag_bulkload_pvt.update_intf_data_level - '||SQLERRM;
6179:
6180: RETURN;
6174: x_return_status := G_RET_STS_UNEXP_ERROR;
6175:
6176: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'update_intf_data_level Exception when others'||SQLERRM);
6177:
6178: x_return_msg := 'ego_ag_bulkload_pvt.update_intf_data_level - '||SQLERRM;
6179:
6180: RETURN;
6181: END update_intf_data_level;
6182:
6190: IS
6191: lv_proc VARCHAR2(30) := 'update_intf_attribute';
6192: trans_id dbms_sql.number_table;
6193: BEGIN
6194: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering update_intf_attribute');
6195:
6196: x_return_status := G_RET_STS_SUCCESS;
6197:
6198: FOR i IN p_attr_tbl.FIRST..p_attr_tbl.LAST LOOP
6203: UPDATE ego_attr_group_cols_intf
6204: SET ROW = P_attr_tbl(i)
6205: WHERE transaction_id = trans_id(i);
6206:
6207: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit update_intf_attribute');
6208: EXCEPTION
6209: WHEN OTHERS THEN
6210: x_return_status := G_RET_STS_UNEXP_ERROR;
6211:
6208: EXCEPTION
6209: WHEN OTHERS THEN
6210: x_return_status := G_RET_STS_UNEXP_ERROR;
6211:
6212: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'update_intf_attribute Exception when others'||SQLERRM);
6213:
6214: x_return_msg := 'ego_ag_bulkload_pvt.update_intf_attribute - '||SQLERRM;
6215:
6216: RETURN;
6210: x_return_status := G_RET_STS_UNEXP_ERROR;
6211:
6212: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'update_intf_attribute Exception when others'||SQLERRM);
6213:
6214: x_return_msg := 'ego_ag_bulkload_pvt.update_intf_attribute - '||SQLERRM;
6215:
6216: RETURN;
6217: END update_intf_attribute;
6218:
6225: x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
6226: IS
6227: lv_proc VARCHAR2(30) := 'delete_processed_attr_groups';
6228: BEGIN
6229: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering delete_processed_attr_groups');
6230:
6231: x_return_status := G_RET_STS_SUCCESS;
6232:
6233: DELETE FROM ego_attr_groups_interface
6234: WHERE process_status = G_SUCCESS_RECORD
6235: AND (x_set_process_id IS NULL
6236: OR set_process_id = x_set_process_id);
6237:
6238: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit delete_processed_attr_groups');
6239: EXCEPTION
6240: WHEN OTHERS THEN
6241: x_return_status := G_RET_STS_UNEXP_ERROR;
6242:
6239: EXCEPTION
6240: WHEN OTHERS THEN
6241: x_return_status := G_RET_STS_UNEXP_ERROR;
6242:
6243: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_processed_attr_groups Exception when others'||SQLERRM);
6244:
6245: x_return_msg := 'ego_ag_bulkload_pvt.delete_processed_attr_groups - '||SQLERRM;
6246:
6247: RETURN;
6241: x_return_status := G_RET_STS_UNEXP_ERROR;
6242:
6243: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_processed_attr_groups Exception when others'||SQLERRM);
6244:
6245: x_return_msg := 'ego_ag_bulkload_pvt.delete_processed_attr_groups - '||SQLERRM;
6246:
6247: RETURN;
6248:
6249: END delete_processed_attr_groups;
6257: x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
6258: IS
6259: lv_proc VARCHAR2(30) := 'delete_processed_data_level';
6260: BEGIN
6261: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering delete_processed_data_level');
6262:
6263: x_return_status := G_RET_STS_SUCCESS;
6264:
6265: DELETE FROM ego_attr_groups_dl_interface
6266: WHERE process_status = G_SUCCESS_RECORD
6267: AND (x_set_process_id IS NULL
6268: OR set_process_id = x_set_process_id);
6269:
6270: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit delete_processed_data_level');
6271: EXCEPTION
6272: WHEN OTHERS THEN
6273: x_return_status := G_RET_STS_UNEXP_ERROR;
6274:
6271: EXCEPTION
6272: WHEN OTHERS THEN
6273: x_return_status := G_RET_STS_UNEXP_ERROR;
6274:
6275: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_processed_data_level Exception when others'||SQLERRM);
6276:
6277: x_return_msg := 'ego_ag_bulkload_pvt.delete_processed_data_level - '||SQLERRM;
6278:
6279: RETURN;
6273: x_return_status := G_RET_STS_UNEXP_ERROR;
6274:
6275: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_processed_data_level Exception when others'||SQLERRM);
6276:
6277: x_return_msg := 'ego_ag_bulkload_pvt.delete_processed_data_level - '||SQLERRM;
6278:
6279: RETURN;
6280: END delete_processed_data_level;
6281:
6288: x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
6289: IS
6290: lv_proc VARCHAR2(30) := 'delete_processed_attributes';
6291: BEGIN
6292: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering delete_processed_attributes');
6293:
6294: x_return_status := G_RET_STS_SUCCESS;
6295:
6296: DELETE FROM ego_attr_group_cols_intf
6297: WHERE process_status = G_SUCCESS_RECORD
6298: AND (x_set_process_id IS NULL
6299: OR set_process_id = x_set_process_id);
6300:
6301: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit delete_processed_attributes');
6302: EXCEPTION
6303: WHEN OTHERS THEN
6304: x_return_status := G_RET_STS_UNEXP_ERROR;
6305:
6302: EXCEPTION
6303: WHEN OTHERS THEN
6304: x_return_status := G_RET_STS_UNEXP_ERROR;
6305:
6306: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_processed_attributes Exception when others'||SQLERRM);
6307:
6308: x_return_msg := 'ego_ag_bulkload_pvt.delete_processed_attributes - '||SQLERRM;
6309:
6310: RETURN;
6304: x_return_status := G_RET_STS_UNEXP_ERROR;
6305:
6306: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_processed_attributes Exception when others'||SQLERRM);
6307:
6308: x_return_msg := 'ego_ag_bulkload_pvt.delete_processed_attributes - '||SQLERRM;
6309:
6310: RETURN;
6311: END delete_processed_attributes;
6312:
6326: lv_variant VARCHAR2(1);
6327: lv_description VARCHAR2(240); /*Added for bug 9738246*/
6328: lv_proc VARCHAR2(30) := 'handle_null_ag';
6329: BEGIN
6330: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering handle_null_ag');
6331:
6332: x_return_status := G_RET_STS_SUCCESS;
6333:
6334: FOR i IN p_ag_tbl.FIRST..p_ag_tbl.LAST LOOP
6334: FOR i IN p_ag_tbl.FIRST..p_ag_tbl.LAST LOOP
6335: IF (p_ag_tbl(i).process_status = G_PROCESS_RECORD
6336: AND p_ag_tbl(i).transaction_type = G_OPR_UPDATE) THEN
6337: BEGIN
6338: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Smt 1');
6339:
6340: SELECT NUM_OF_COLS, NUM_OF_ROWS, MULTI_ROW, VARIANT INTO lv_num_of_cols, lv_num_of_rows, lv_multi_row, lv_variant
6341: FROM EGO_FND_DSC_FLX_CTX_EXT
6342: WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_tbl(i).attr_group_type
6342: WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_tbl(i).attr_group_type
6343: AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_ag_tbl(i).attr_group_name
6344: AND APPLICATION_ID = G_EGO_APPLICATION_ID;
6345:
6346: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'smt 2');
6347:
6348: SELECT DESCRIPTIVE_FLEX_CONTEXT_NAME, DESCRIPTION INTO lv_display_name, lv_description
6349: FROM FND_DESCR_FLEX_CONTEXTS_TL
6350: WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
6351: AND DESCRIPTIVE_FLEXFIELD_NAME = p_ag_tbl(i).attr_group_type
6352: AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_ag_tbl(i).attr_group_name
6353: AND USERENV('LANG') = LANGUAGE;
6354:
6355: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'smt 3');
6356:
6357: IF (p_ag_tbl(i).attr_group_disp_name IS NULL
6358: OR p_ag_tbl(i).attr_group_disp_name = G_NULL_CHAR) THEN
6359: p_ag_tbl(i).attr_group_disp_name := lv_display_name;
6358: OR p_ag_tbl(i).attr_group_disp_name = G_NULL_CHAR) THEN
6359: p_ag_tbl(i).attr_group_disp_name := lv_display_name;
6360: END IF;
6361:
6362: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'smt 4');
6363:
6364: IF p_ag_tbl(i).description IS NULL THEN
6365: p_ag_tbl(i).description := lv_description;
6366: ELSIF p_ag_tbl(i).description = G_NULL_CHAR THEN
6366: ELSIF p_ag_tbl(i).description = G_NULL_CHAR THEN
6367: p_ag_tbl(i).description := NULL;
6368: END IF;
6369:
6370: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'smt 5');
6371:
6372: IF ((p_ag_tbl(i).num_of_rows IS NULL
6373: OR p_ag_tbl(i).num_of_rows = G_NULL_NUM) /*Changed for bug 9719202*/
6374: AND lv_num_of_rows IS NOT NULL) THEN
6374: AND lv_num_of_rows IS NOT NULL) THEN
6375: p_ag_tbl(i).num_of_rows := lv_num_of_rows;
6376: END IF;
6377:
6378: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'smt 6');
6379:
6380: IF ((p_ag_tbl(i).num_of_cols IS NULL
6381: OR p_ag_tbl(i).num_of_cols = G_NULL_NUM) /*Changed for bug 9719202*/
6382: AND lv_num_of_cols IS NOT NULL) THEN
6383: p_ag_tbl(i).num_of_cols := lv_num_of_cols;
6384: END IF;
6385:
6386: /*Added for bug 9719202*/
6387: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'smt 7');
6388:
6389: IF (NVL(lv_variant,'N') = 'N' AND lv_multi_row = 'N') THEN
6390: p_ag_tbl(i).num_of_rows := null;
6391: END IF;
6389: IF (NVL(lv_variant,'N') = 'N' AND lv_multi_row = 'N') THEN
6390: p_ag_tbl(i).num_of_rows := null;
6391: END IF;
6392:
6393: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'smt 8');
6394:
6395: IF (NVL(lv_variant,'N') = 'Y' AND lv_multi_row = 'N') THEN
6396: p_ag_tbl(i).num_of_rows := null;
6397: p_ag_tbl(i).num_of_cols := null;
6400: EXCEPTION
6401: WHEN OTHERS THEN
6402: x_return_status := G_RET_STS_UNEXP_ERROR;
6403:
6404: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_ag exception when others smt 1');
6405:
6406: x_return_msg := 'ego_ag_bulkload_pvt.handle_null_ag - '||SQLERRM;
6407:
6408: RETURN;
6402: x_return_status := G_RET_STS_UNEXP_ERROR;
6403:
6404: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_ag exception when others smt 1');
6405:
6406: x_return_msg := 'ego_ag_bulkload_pvt.handle_null_ag - '||SQLERRM;
6407:
6408: RETURN;
6409: END;
6410: END IF;
6408: RETURN;
6409: END;
6410: END IF;
6411: END LOOP;
6412: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit handle_null_ag');
6413: EXCEPTION
6414: WHEN OTHERS THEN
6415: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_ag Exception when others'||SQLERRM);
6416: END handle_null_ag;
6411: END LOOP;
6412: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit handle_null_ag');
6413: EXCEPTION
6414: WHEN OTHERS THEN
6415: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_ag Exception when others'||SQLERRM);
6416: END handle_null_ag;
6417:
6418: /*This procedure is used to do final processing of the attribute group data level.
6419: Used in the interface and API flow.
6430: lv_raise_pre_event VARCHAR2(1);
6431: lv_raise_post_event VARCHAR2(1);
6432: lv_proc VARCHAR2(30) := 'handle_null_dl';
6433: BEGIN
6434: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering handle_null_dl');
6435:
6436: x_return_status := G_RET_STS_SUCCESS;
6437:
6438: FOR i IN p_agdl_tbl.FIRST..p_agdl_tbl.LAST LOOP
6438: FOR i IN p_agdl_tbl.FIRST..p_agdl_tbl.LAST LOOP
6439: IF (p_agdl_tbl(i).process_status = G_PROCESS_RECORD
6440: AND p_agdl_tbl(i).transaction_type = G_OPR_UPDATE) THEN
6441: BEGIN
6442: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Smt 1');
6443:
6444: SELECT defaulting ,view_privilege_id,edit_privilege_id,raise_pre_event,raise_post_event
6445: INTO lv_defaulting, lv_view_privilege_id ,lv_edit_privilege_id,lv_raise_pre_event, lv_raise_post_event
6446: FROM EGO_ATTR_GROUP_DL
6483: EXCEPTION
6484: WHEN OTHERS THEN
6485: x_return_status := G_RET_STS_UNEXP_ERROR;
6486:
6487: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_dl exception when others smt 1');
6488:
6489: x_return_msg := 'ego_ag_bulkload_pvt.handle_null_dl - '||SQLERRM;
6490:
6491: RETURN;
6485: x_return_status := G_RET_STS_UNEXP_ERROR;
6486:
6487: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_dl exception when others smt 1');
6488:
6489: x_return_msg := 'ego_ag_bulkload_pvt.handle_null_dl - '||SQLERRM;
6490:
6491: RETURN;
6492: END;
6493: END IF;
6491: RETURN;
6492: END;
6493: END IF;
6494: END LOOP;
6495: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit handle_null_dl');
6496:
6497: EXCEPTION
6498: WHEN OTHERS THEN
6499: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_dl Exception when others'||SQLERRM);
6495: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit handle_null_dl');
6496:
6497: EXCEPTION
6498: WHEN OTHERS THEN
6499: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_dl Exception when others'||SQLERRM);
6500: END handle_null_dl;
6501:
6502: /*This procedure is used to do final processing of the attributes.
6503: Used in the interface and API flow.
6513: lv_info_1 VARCHAR2(2048); /*Added for bug 9738246 */
6514: lv_uom_class VARCHAR2(40);
6515: lv_proc VARCHAR2(30) := 'handle_null_attr';
6516: BEGIN
6517: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering handle_null_attr');
6518:
6519: x_return_status := G_RET_STS_SUCCESS;
6520:
6521: FOR i IN p_attr_tbl.FIRST..p_attr_tbl.LAST LOOP
6562: EXCEPTION
6563: WHEN OTHERS THEN
6564: x_return_status := G_RET_STS_UNEXP_ERROR;
6565:
6566: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_attr exception when others smt 1');
6567:
6568: x_return_msg := 'ego_ag_bulkload_pvt.handle_null_attr - '||SQLERRM;
6569: END;
6570: END IF;
6564: x_return_status := G_RET_STS_UNEXP_ERROR;
6565:
6566: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_attr exception when others smt 1');
6567:
6568: x_return_msg := 'ego_ag_bulkload_pvt.handle_null_attr - '||SQLERRM;
6569: END;
6570: END IF;
6571: END LOOP;
6572: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit handle_null_attr');
6568: x_return_msg := 'ego_ag_bulkload_pvt.handle_null_attr - '||SQLERRM;
6569: END;
6570: END IF;
6571: END LOOP;
6572: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit handle_null_attr');
6573: EXCEPTION
6574: WHEN OTHERS THEN
6575: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_attr Exception when others'||SQLERRM);
6576: END handle_null_attr;
6571: END LOOP;
6572: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit handle_null_attr');
6573: EXCEPTION
6574: WHEN OTHERS THEN
6575: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'handle_null_attr Exception when others'||SQLERRM);
6576: END handle_null_attr;
6577:
6578: /*This procedure is used to delete AG existing in the production table without a single DL associated
6579: Used in the Interface flow and API flow.
6595: x_msg_count NUMBER;
6596: x_msg_data VARCHAR2(2000);
6597: lv_proc VARCHAR2(30) := 'delete_ag_none_dl';
6598: BEGIN
6599: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Enters delete_ag_none_dl');
6600:
6601: x_return_status := G_RET_STS_SUCCESS;
6602:
6603: FOR rec IN cur_ag_none_dl LOOP
6612: ,x_msg_data => x_msg_data
6613: );
6614: END LOOP;
6615: COMMIT;
6616: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exits delete_ag_none_dl');
6617: EXCEPTION
6618: WHEN OTHERS THEN
6619: x_return_status := G_RET_STS_UNEXP_ERROR;
6620:
6617: EXCEPTION
6618: WHEN OTHERS THEN
6619: x_return_status := G_RET_STS_UNEXP_ERROR;
6620:
6621: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_ag_none_dl Exception when others'||SQLERRM);
6622:
6623: x_return_msg := 'ego_ag_bulkload_pvt.delete_ag_none_dl - '||SQLERRM;
6624: END delete_ag_none_dl;
6625:
6619: x_return_status := G_RET_STS_UNEXP_ERROR;
6620:
6621: write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_ag_none_dl Exception when others'||SQLERRM);
6622:
6623: x_return_msg := 'ego_ag_bulkload_pvt.delete_ag_none_dl - '||SQLERRM;
6624: END delete_ag_none_dl;
6625:
6626: /*This local procedure will return the TL table name based on the attribute group Type
6627: p_application_id IN EGO Application ID
6762: EXCEPTION
6763: WHEN OTHERS THEN
6764: NULL;
6765: END write_debug;
6766: END ego_ag_bulkload_pvt;