[Home] [Help]
12: G_PARTY_ID hz_parties.party_id%TYPE;
13: G_PARTY_NAME hz_parties.party_name%TYPE;
14: G_LOGIN_ID fnd_user.last_update_login%TYPE;
15: G_REQUEST_ID NUMBER;
16: G_PROG_APPID ego_aml_intf.program_application_id%TYPE;
17: G_PROG_ID ego_aml_intf.program_id%TYPE;
18: G_SYSDATE fnd_user.creation_date%TYPE;
19: G_SESSION_LANG VARCHAR2(99);
20: G_FND_OBJECT_NAME fnd_objects.obj_name%TYPE;
13: G_PARTY_NAME hz_parties.party_name%TYPE;
14: G_LOGIN_ID fnd_user.last_update_login%TYPE;
15: G_REQUEST_ID NUMBER;
16: G_PROG_APPID ego_aml_intf.program_application_id%TYPE;
17: G_PROG_ID ego_aml_intf.program_id%TYPE;
18: G_SYSDATE fnd_user.creation_date%TYPE;
19: G_SESSION_LANG VARCHAR2(99);
20: G_FND_OBJECT_NAME fnd_objects.obj_name%TYPE;
21: G_FND_OBJECT_ID fnd_objects.object_id%TYPE;
90: -- ==========================================================================
91: PROCEDURE write_aml_rec (p_data_set_id IN NUMBER
92: ,p_watch_data IN VARCHAR2
93: ) IS
94: l_aml_rec ego_aml_intf%ROWTYPE;
95: BEGIN
96: SELECT *
97: INTO l_aml_rec
98: from ego_aml_intf
94: l_aml_rec ego_aml_intf%ROWTYPE;
95: BEGIN
96: SELECT *
97: INTO l_aml_rec
98: from ego_aml_intf
99: where data_set_id = p_data_set_id;
100: -- sri_debug( p_watch_data ||': flag '||l_aml_rec.process_flag||
101: -- ' item id '||l_aml_rec.inventory_item_id||
102: -- ' item number '||l_aml_rec.item_number||
172: END;
173: --
174: -- error handler parameters
175: --
176: G_ERROR_TABLE_NAME := NVL(G_ERROR_TABLE_NAME,'EGO_AML_INTF');
177: G_ERROR_ENTITY_CODE := NVL(G_ERROR_ENTITY_CODE,'EGO_AML');
178: G_ERROR_FILE_NAME := NULL;
179: G_BO_IDENTIFIER := NVL(G_BO_IDENTIFIER,'EGO_AML');
180: --
275: --
276: -- records will be processed with process_flag = G_PS_IN_PROCESS
277: -- records will be ended with process_flag = G_PS_VAL_TO_ID_COMPLETE
278: --
279: UPDATE ego_aml_intf
280: SET process_flag = G_PS_INVALID_TRANS_TYPE
281: WHERE data_set_id = p_data_set_id
282: AND process_flag = G_PS_IN_PROCESS
283: AND transaction_type NOT IN
290: ,p_module => l_api_name
291: ,p_message => ' Transaction type validation complete'
292: );
293:
294: UPDATE ego_aml_intf aml_intf
295: SET process_flag = G_PS_SD_GT_ED_ERROR
296: WHERE data_set_id = p_data_set_id
297: AND process_flag = G_PS_IN_PROCESS
298: AND NVL(start_date,EGO_ITEM_PUB.G_INTF_NULL_DATE)
301: ,p_module => l_api_name
302: ,p_message => ' Start Date - End Date validation complete'
303: );
304:
305: UPDATE ego_aml_intf aml_intf
306: SET process_flag = G_PS_FA_STATUS_ERR
307: WHERE data_set_id = p_data_set_id
308: AND process_flag = G_PS_IN_PROCESS
309: AND transaction_type <> EGO_ITEM_PUB.G_TTYPE_DELETE
329: AND fa_lookup.language = G_SESSION_LANG)
330: )
331: );
332:
333: UPDATE ego_aml_intf aml_intf
334: SET first_article_status =
335: DECODE (first_article_status_meaning, EGO_ITEM_PUB.G_INTF_NULL_CHAR,
336: EGO_ITEM_PUB.G_INTF_NULL_CHAR,
337: (Select lookup_code
349: ,p_module => l_api_name
350: ,p_message => 'VtoID Conversion Complete for First Article Status'
351: );
352:
353: UPDATE ego_aml_intf aml_intf
354: SET process_flag = G_PS_APPROVAL_STATUS_ERR
355: WHERE data_set_id = p_data_set_id
356: AND process_flag = G_PS_IN_PROCESS
357: AND transaction_type <> EGO_ITEM_PUB.G_TTYPE_DELETE
377: AND fa_lookup.language = G_SESSION_LANG)
378: )
379: );
380:
381: UPDATE ego_aml_intf aml_intf
382: SET approval_status =
383: DECODE (approval_status_meaning, EGO_ITEM_PUB.G_INTF_NULL_CHAR,
384: EGO_ITEM_PUB.G_INTF_NULL_CHAR,
385: (Select lookup_code
397: ,p_module => l_api_name
398: ,p_message => 'VtoID Conversion Complete for Approval Status'
399: );
400:
401: UPDATE ego_aml_intf aml_intf
402: SET process_flag = G_PS_MANUFACTURER_ERR
403: WHERE data_set_id = p_data_set_id
404: AND process_flag = G_PS_IN_PROCESS
405: AND ( (manufacturer_id IS NOT NULL
416: WHERE manu.manufacturer_name = aml_intf.manufacturer_name)
417: )
418: );
419:
420: UPDATE ego_aml_intf aml_intf
421: SET manufacturer_id =
422: (Select manufacturer_id
423: from mtl_manufacturers manu
424: where manu.manufacturer_name = aml_intf.manufacturer_name)
429: ,p_module => l_api_name
430: ,p_message => 'VtoID Conversion Complete for Manufacturers'
431: );
432:
433: UPDATE ego_aml_intf aml_intf
434: SET process_flag = G_PS_ORGANIZATION_ERR
435: WHERE data_set_id = p_data_set_id
436: AND process_flag = G_PS_IN_PROCESS
437: AND ( (organization_id IS NOT NULL
446: WHERE mp.organization_code = aml_intf.organization_code)
447: )
448: );
449:
450: UPDATE ego_aml_intf aml_intf
451: SET process_flag = G_PS_NOT_MASTER_ORG_ERR
452: WHERE data_set_id = p_data_set_id
453: AND process_flag = G_PS_IN_PROCESS
454: AND ( (organization_id IS NOT NULL
465: AND mp.organization_id = mp.master_organization_id)
466: )
467: );
468:
469: UPDATE ego_aml_intf aml_intf
470: SET organization_id =
471: (Select organization_id
472: from mtl_parameters mp
473: where mp.organization_code = aml_intf.organization_code)
478: ,p_module => l_api_name
479: ,p_message => 'VtoID Conversion Complete for Organization'
480: );
481:
482: UPDATE ego_aml_intf aml_intf
483: SET process_flag = G_PS_ITEM_ERR
484: WHERE data_set_id = p_data_set_id
485: AND process_flag = G_PS_IN_PROCESS
486: AND ( (inventory_item_id IS NOT NULL
499: AND item.concatenated_segments = aml_intf.item_number)
500: )
501: );
502:
503: UPDATE ego_aml_intf aml_intf
504: SET (item_number, prog_int_num1, prog_int_num2,
505: prog_int_num3, prog_int_char1) =
506: (Select concatenated_segments, item_catalog_group_id, lifecycle_id,
507: current_phase_id, NVL(approval_status,'A')
511: WHERE data_set_id = p_data_set_id
512: AND process_flag = G_PS_IN_PROCESS
513: AND inventory_item_id IS NOT NULL;
514:
515: UPDATE ego_aml_intf aml_intf
516: SET (inventory_item_id, prog_int_num1, prog_int_num2,
517: prog_int_num3, prog_int_char1) =
518: (Select inventory_item_id, item_catalog_group_id, lifecycle_id,
519: current_phase_id, NVL(APPROVAL_STATUS,'A')
523: WHERE data_set_id = p_data_set_id
524: AND process_flag = G_PS_IN_PROCESS
525: AND inventory_item_id IS NULL;
526:
527: UPDATE ego_aml_intf aml_intf
528: SET process_flag = G_PS_VAL_TO_ID_COMPLETE
529: WHERE data_set_id = p_data_set_id
530: AND process_flag = G_PS_IN_PROCESS;
531:
560: --
561: -- records will be processed with process_flag = G_PS_VAL_TO_ID_COMPLETE
562: -- records will be ended with process_flag = G_PS_VAL_TO_ID_COMPLETE
563: --
564: UPDATE ego_aml_intf aml_intf
565: SET process_flag = G_PS_CREATE_REC_EXISTS
566: WHERE data_set_id = p_data_set_id
567: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
568: AND transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE
578: ,p_module => l_api_name
579: ,p_message => 'Transaction check complete for CREATE'
580: );
581:
582: UPDATE ego_aml_intf aml_intf
583: SET process_flag = G_PS_REC_NOT_EXISTS
584: WHERE data_set_id = p_data_set_id
585: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
586: AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_UPDATE
598: ,p_module => l_api_name
599: ,p_message => 'Transaction check complete for UPDATE'
600: );
601:
602: UPDATE ego_aml_intf aml_intf
603: SET transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
604: WHERE data_set_id = p_data_set_id
605: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
606: AND transaction_type = EGO_ITEM_PUB.G_TTYPE_SYNC
612: and part_num.manufacturer_id = aml_intf.manufacturer_id
613: and part_num.mfg_part_num = aml_intf.mfg_part_num
614: );
615:
616: UPDATE ego_aml_intf aml_intf
617: SET transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE
618: WHERE data_set_id = p_data_set_id
619: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
620: AND transaction_type = EGO_ITEM_PUB.G_TTYPE_SYNC;
623: ,p_message => 'Transaction check complete for SYNC'
624: );
625:
626: -- I think we do not need this
627: -- UPDATE ego_aml_intf aml_intf
628: -- SET process_flag = G_PS_SD_NOT_NULL
629: -- WHERE data_set_id = p_data_set_id
630: -- AND process_flag = G_PS_VAL_TO_ID_COMPLETE
631: -- AND transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
631: -- AND transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
632: -- AND start_date = EGO_ITEM_PUB.G_INTF_NULL_DATE;
633:
634: IF p_mode <> MODE_HISTORICAL THEN
635: UPDATE ego_aml_intf aml_intf
636: SET process_flag = G_PS_ED_LT_SYSDATE
637: WHERE data_set_id = p_data_set_id
638: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
639: AND transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
668: --
669: -- records will be processed with process_flag = G_PS_VAL_TO_ID_COMPLETE
670: -- records will be ended with process_flag = G_PS_VAL_TO_ID_COMPLETE
671: --
672: UPDATE ego_aml_intf orig
673: SET process_flag = G_PS_DUP_INTF_RECORDS
674: WHERE data_set_id = p_data_set_id
675: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
676: AND EXISTS
674: WHERE data_set_id = p_data_set_id
675: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
676: AND EXISTS
677: (SELECT 'X'
678: FROM ego_aml_intf
679: WHERE data_set_id = p_data_set_id
680: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
681: AND transaction_id <> orig.transaction_id
682: AND inventory_item_id = orig.inventory_item_id
708: ,x_msg_data OUT NOCOPY VARCHAR2) IS
709:
710: CURSOR c_item_records (cp_data_set_id IN NUMBER) IS
711: SELECT *
712: FROM ego_aml_intf
713: WHERE data_set_id = cp_data_set_id
714: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
715: AND NVL(prog_int_char2,'N') <> 'Y'
716: AND prog_int_num4 IS NOT NULL
739: ,p_module => l_api_name
740: ,p_message => 'Value of Add all to CM '||l_add_all_to_cm
741: );
742:
743: UPDATE ego_aml_intf aml_intf
744: SET prog_int_char2 = 'Y'
745: WHERE data_set_id = p_data_set_id
746: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
747: AND EXISTS (Select 1
752: and transaction_type = 'CREATE'
753: and process_flag = 7
754: );
755:
756: UPDATE ego_aml_intf aml_intf
757: SET prog_int_num4 =
758: (SELECT ic.item_catalog_group_id
759: FROM mtl_item_catalog_groups_b ic
760: WHERE EXISTS
777: ,p_message => 'CC at which policy is associated obtained'
778: );
779:
780: IF l_add_all_to_cm = 'Y' THEN
781: UPDATE ego_aml_intf aml_intf
782: SET process_flag = G_PS_TRANSFER_TO_CM
783: WHERE data_set_id = p_data_set_id
784: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
785: AND NVL(prog_int_char2,'N') <> 'Y'
831: ' under transaction '|| cr.transaction_id ||
832: ' policy is '||l_policy_value
833: );
834: IF l_policy_value = G_CP_NOT_ALLOWED THEN
835: UPDATE ego_aml_intf aml_intf
836: SET process_flag = G_PS_CHANGE_NOT_ALLOWED
837: WHERE CURRENT OF c_item_records;
838: ELSIF l_policy_value = G_CP_CO_REQUIRED OR l_add_all_to_cm = 'Y' THEN
839: UPDATE ego_aml_intf aml_intf
835: UPDATE ego_aml_intf aml_intf
836: SET process_flag = G_PS_CHANGE_NOT_ALLOWED
837: WHERE CURRENT OF c_item_records;
838: ELSIF l_policy_value = G_CP_CO_REQUIRED OR l_add_all_to_cm = 'Y' THEN
839: UPDATE ego_aml_intf aml_intf
840: SET process_flag = G_PS_TRANSFER_TO_CM
841: WHERE CURRENT OF c_item_records;
842: END IF;
843: END LOOP;
875: l_api_name VARCHAR2(30);
876:
877: CURSOR c_item_records (cp_data_set_id IN NUMBER) IS
878: SELECT *
879: FROM ego_aml_intf
880: WHERE data_set_id = cp_data_set_id
881: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
882: AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_CREATE
883: ,EGO_ITEM_PUB.G_TTYPE_UPDATE
944: ,p_message => SUBSTR(l_sec_predicate,(i-1)*100,100)
945: );
946: END LOOP;
947: l_dynamic_sql :=
948: ' UPDATE EGO_AML_INTF aml_intf ' ||
949: ' SET process_flag = '||G_PS_NO_AML_PRIV ||
950: ' WHERE data_set_id = :1'||
951: ' AND process_flag = '||G_PS_VAL_TO_ID_COMPLETE||
952: ' AND NVL(prog_int_char2,''N'') <> ''Y'''||
971: ,p_message => 'No need to perform Security check'
972: );
973: END IF;
974:
975: UPDATE ego_aml_intf
976: SET mrp_planning_code =
977: DECODE(mrp_planning_code,EGO_ITEM_PUB.G_INTF_NULL_NUM,NULL,
978: mrp_planning_code),
979: description =
1112: ,p_module => l_api_name
1113: ,p_message => 'Changing intf table with NULL during CREATE'
1114: );
1115:
1116: UPDATE ego_aml_intf intf
1117: SET (mrp_planning_code
1118: ,description
1119: ,attribute_category
1120: ,attribute1
1324: NULL;
1325: END resetDFFFieldUsage;
1326:
1327: FUNCTION getAttrValue (
1328: p_part_num_rec IN ego_aml_intf%ROWTYPE
1329: ,p_column_name IN VARCHAR2
1330: ,p_dff_usage_record IN OUT NOCOPY G_MTL_DFF_ATTRIBUTES_REC
1331: ) RETURN VARCHAR2 IS
1332: BEGIN
1495:
1496: l_std_ctx_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
1497: l_dff_ctx_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
1498:
1499: l_last_attribute_category ego_aml_intf.attribute_category%TYPE;
1500: l_global_data_elements ego_aml_intf.attribute_category%TYPE;
1501: l_global_ctx_index NUMBER;
1502:
1503: l_dff_fields_used G_MTL_DFF_ATTRIBUTES_REC;
1496: l_std_ctx_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
1497: l_dff_ctx_code fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE;
1498:
1499: l_last_attribute_category ego_aml_intf.attribute_category%TYPE;
1500: l_global_data_elements ego_aml_intf.attribute_category%TYPE;
1501: l_global_ctx_index NUMBER;
1502:
1503: l_dff_fields_used G_MTL_DFF_ATTRIBUTES_REC;
1504:
1506: l_count NUMBER;
1507:
1508: CURSOR c_item_records (cp_data_set_id IN NUMBER) IS
1509: SELECT *
1510: FROM ego_aml_intf
1511: WHERE data_set_id = cp_data_set_id
1512: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
1513: AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_CREATE
1514: ,EGO_ITEM_PUB.G_TTYPE_UPDATE
1531: -- check if there are any records for which DFF is input
1532: --
1533: SELECT COUNT(*)
1534: INTO l_count
1535: FROM ego_aml_intf
1536: WHERE data_set_id = p_data_set_id
1537: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
1538: AND transaction_type IN (EGO_ITEM_PUB.G_TTYPE_CREATE
1539: ,EGO_ITEM_PUB.G_TTYPE_UPDATE
1674: -- ,enabled_activation IN BOOLEAN DEFAULT TRUE
1675: -- ,resp_appl_id IN NUMBER DEFAULT NULL
1676: -- ,resp_id IN NUMBER DEFAULT NULL
1677: ) THEN
1678: UPDATE ego_aml_intf
1679: SET process_flag = G_PS_DFF_INVALID,
1680: prog_int_char2 = fnd_flex_descval.error_message
1681: WHERE CURRENT OF c_item_records;
1682: ELSE
1680: prog_int_char2 = fnd_flex_descval.error_message
1681: WHERE CURRENT OF c_item_records;
1682: ELSE
1683: -- set to NULL all other DFF fields which are not defined
1684: UPDATE ego_aml_intf intf
1685: SET attribute1 =
1686: (SELECT CASE WHEN l_dff_fields_used.attribute1 = FND_API.G_TRUE
1687: THEN intf.attribute1 ELSE NULL END
1688: FROM DUAL)
1746: END IF;
1747: END LOOP;
1748: ELSE
1749: -- no context fields defined.
1750: UPDATE ego_aml_intf
1751: SET process_flag = G_PS_DFF_INVALID
1752: WHERE data_set_id = p_data_set_id
1753: AND process_flag = G_PS_VAL_TO_ID_COMPLETE
1754: AND (attribute1 IS NOT NULL
1791: ,p_module => l_api_name
1792: ,p_message => 'No DFF fields are present for validation'
1793: );
1794: END IF; -- there exists some records with DFF fields
1795: UPDATE ego_aml_intf
1796: SET process_flag = G_PS_DFF_VAL_COMPLETE
1797: WHERE data_set_id = p_data_set_id
1798: AND process_flag = G_PS_VAL_TO_ID_COMPLETE;
1799:
1888: ,G_USER_ID
1889: ,G_SYSDATE
1890: ,G_USER_ID
1891: ,G_LOGIN_ID
1892: FROM ego_aml_intf
1893: WHERE data_set_id = p_data_set_id
1894: AND process_flag = G_PS_DFF_VAL_COMPLETE
1895: AND transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE;
1896: log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
1950: ,intf.request_id
1951: ,G_SYSDATE
1952: ,G_USER_ID
1953: ,G_LOGIN_ID
1954: FROM ego_aml_intf intf
1955: WHERE intf.data_set_id = p_data_set_id
1956: AND intf.process_flag = G_PS_DFF_VAL_COMPLETE
1957: AND intf.transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
1958: AND intf.inventory_item_id = prod.inventory_item_id
1960: AND intf.manufacturer_id = prod.manufacturer_id
1961: AND intf.mfg_part_num = prod.mfg_part_num
1962: )
1963: WHERE EXISTS (select 1
1964: from ego_aml_intf intf1
1965: where intf1.data_set_id = p_data_set_id
1966: and intf1.process_flag = G_PS_DFF_VAL_COMPLETE
1967: and intf1.transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
1968: and intf1.inventory_item_id = prod.inventory_item_id
1977:
1978: DELETE mtl_mfg_part_numbers prod
1979: WHERE EXISTS
1980: (Select 1
1981: From ego_aml_intf intf
1982: Where intf.data_set_id = p_data_set_id
1983: and intf.process_flag = G_PS_DFF_VAL_COMPLETE
1984: and intf.transaction_type = EGO_ITEM_PUB.G_TTYPE_DELETE
1985: and intf.inventory_item_id = prod.inventory_item_id
1991: ,p_module => l_api_name
1992: ,p_message => ' Deletion of items from production table done'
1993: );
1994:
1995: UPDATE ego_aml_intf
1996: SET process_flag = G_PS_SUCCESS
1997: WHERE data_set_id = p_data_set_id
1998: AND process_flag = G_PS_DFF_VAL_COMPLETE;
1999:
2021: RETURN BOOLEAN IS
2022:
2023: CURSOR c_err_records (cp_data_set_id IN NUMBER) IS
2024: SELECT *
2025: FROM ego_aml_intf
2026: WHERE data_set_id = cp_data_set_id
2027: AND process_flag > G_PS_SUCCESS;
2028:
2029: l_dummy_message fnd_new_messages.message_text%TYPE;
2046: l_application_context := 'EGO';
2047: l_message_type := FND_API.G_RET_STS_ERROR;
2048: -- this takes precedence over entity id
2049: l_entity_index := 0;
2050: l_intf_table_name := 'EGO_AML_INTF';
2051: l_message_has_token := FALSE;
2052: log_now (p_log_level => G_DEBUG_LEVEL_STATEMENT
2053: ,p_module => l_api_name
2054: ,p_message => 'Started processing errors'
2054: ,p_message => 'Started processing errors'
2055: );
2056:
2057: -- populate the interface table which uses tokens
2058: UPDATE ego_aml_intf aml_intf
2059: SET manufacturer_name =
2060: (Select manufacturer_name
2061: from mtl_manufacturers manu
2062: where manu.manufacturer_id = aml_intf.manufacturer_id)
2066: ,G_PS_REC_NOT_EXISTS
2067: ,G_PS_DUP_INTF_RECORDS
2068: );
2069:
2070: UPDATE ego_aml_intf aml_intf
2071: SET organization_code =
2072: (Select organization_code
2073: from mtl_parameters mp
2074: where mp.organization_id = aml_intf.organization_id)
2282: ,p_module => l_api_name
2283: ,p_message => 'Completed Logging errors to table'
2284: );
2285:
2286: UPDATE ego_aml_intf
2287: SET process_flag = G_PS_GENERIC_ERROR
2288: WHERE data_set_id = p_data_set_id
2289: AND process_flag > G_PS_SUCCESS;
2290:
2390: IF FND_API.To_Boolean(p_commit) THEN
2391: SAVEPOINT DELETE_AML_INTF_LINES_SP;
2392: END IF;
2393:
2394: l_table_name := 'EGO_AML_INTF';
2395: IF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_ALL THEN
2396: --
2397: -- delete all lines
2398: --
2399: DELETE MTL_INTERFACE_ERRORS
2400: WHERE table_name = l_table_name
2401: AND transaction_id IN
2402: (SELECT transaction_id
2403: FROM EGO_AML_INTF
2404: WHERE data_set_id = p_data_set_id
2405: );
2406:
2407: DELETE EGO_AML_INTF
2403: FROM EGO_AML_INTF
2404: WHERE data_set_id = p_data_set_id
2405: );
2406:
2407: DELETE EGO_AML_INTF
2408: WHERE data_set_id = p_data_set_id;
2409:
2410: ELSIF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_ERROR THEN
2411: --
2414: DELETE MTL_INTERFACE_ERRORS
2415: WHERE table_name = l_table_name
2416: AND transaction_id IN
2417: (SELECT transaction_id
2418: FROM EGO_AML_INTF
2419: WHERE data_set_id = p_data_set_id
2420: AND process_flag = G_PS_GENERIC_ERROR
2421: );
2422:
2419: WHERE data_set_id = p_data_set_id
2420: AND process_flag = G_PS_GENERIC_ERROR
2421: );
2422:
2423: DELETE EGO_AML_INTF
2424: WHERE data_set_id = p_data_set_id
2425: AND process_flag = G_PS_GENERIC_ERROR;
2426:
2427: ELSIF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS THEN
2427: ELSIF p_delete_line_type = EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS THEN
2428: --
2429: -- delete all success lines
2430: --
2431: DELETE EGO_AML_INTF
2432: WHERE data_set_id = p_data_set_id
2433: AND process_flag = G_PS_SUCCESS;
2434: END IF;
2435:
2558: );
2559:
2560: BEGIN
2561: SELECT 'S' INTO l_return_status
2562: FROM EGO_AML_INTF
2563: WHERE DATA_SET_ID = p_data_set_id
2564: AND PROCESS_FLAG = G_PS_TO_BE_PROCESSED
2565: AND ROWNUM = 1;
2566: EXCEPTION
2600: FND_API.to_boolean(NVL(p_perform_security_check,FND_API.G_TRUE));
2601: --
2602: -- initialize default values on interface table
2603: --
2604: UPDATE ego_aml_intf
2605: SET process_flag = G_PS_IN_PROCESS,
2606: transaction_type = UPPER(transaction_type),
2607: transaction_id = NVL(transaction_id, EGO_IPI_TRANSACTION_ID_S.nextval),
2608: first_article_status =
2641: ,p_module => l_api_name
2642: ,p_message => 'Defalting values complete'
2643: );
2644:
2645: UPDATE ego_aml_intf
2646: SET process_flag = G_PS_MAND_PARAM_MISSING
2647: WHERE data_set_id = p_data_set_id
2648: AND process_flag = G_PS_IN_PROCESS
2649: AND ( mfg_part_num IS NULL
2840: l_err_msg_sql := 'SELECT INTF.ITEM_NUMBER as ITEM_NUMBER, '||
2841: ' INTF.ORGANIZATION_CODE as ORGANIZATINO_CODE, '||
2842: -- ' NULL AS REVISION_CODE, '||
2843: ' MIERR.ERROR_MESSAGE as ERROR_MESSAGE '||
2844: ' FROM EGO_AML_INTF INTF, MTL_INTERFACE_ERRORS MIERR '||
2845: ' WHERE MIERR.TRANSACTION_ID = INTF.TRANSACTION_ID '||
2846: ' AND MIERR.REQUEST_ID = INTF.REQUEST_ID '||
2847: ' AND MIERR.request_id = :1';
2848: EGO_ITEM_OPEN_INTERFACE_PVT.Write_Error_into_ConcurrentLog
2846: ' AND MIERR.REQUEST_ID = INTF.REQUEST_ID '||
2847: ' AND MIERR.request_id = :1';
2848: EGO_ITEM_OPEN_INTERFACE_PVT.Write_Error_into_ConcurrentLog
2849: (p_entity_name => 'EGO_AML'
2850: ,p_table_name => 'EGO_AML_INTF'
2851: ,p_selectQuery => l_err_msg_sql
2852: ,p_request_id => G_REQUEST_ID
2853: ,x_return_status => l_return_status
2854: ,x_msg_count => l_msg_count