[Home] [Help]
152: AND flv.flex_value_id = l_child_id;
153:
154: CURSOR get_existing_objects (l_object_type VARCHAR2) IS
155: SELECT distinct object_id
156: FROM AMW_ENTITY_HIERARCHIES
157: WHERE entity_type = p_entity_type
158: AND entity_id = p_entity_id
159: AND delete_flag = 'Y'
160: AND object_type = l_object_type;
184: x_return_status := FND_API.G_RET_STS_SUCCESS;
185:
186: -- Whenever there is a change to hierarchy, the system will delete the existing hierarchy and rebuild it.
187: -- To begin with mark all records in the table as DELETED
188: UPDATE AMW_ENTITY_HIERARCHIES
189: SET DELETE_FLAG = 'Y'
190: WHERE ENTITY_ID = p_entity_id
191: AND ENTITY_TYPE = p_entity_type;
192:
257: FETCH find_parent_subsidiary INTO l_temp_sub_parent_id;
258: CLOSE find_parent_subsidiary;
259:
260: --Step 3(Condition A): Populate table with object_type = ORGANIZATION and parent_object_type = SUBSIDIARY
261: INSERT INTO AMW_ENTITY_HIERARCHIES
262: (ENTITY_HIERARCHY_ID,
263: ENTITY_TYPE,
264: ENTITY_ID,
265: CREATED_BY,
271: OBJECT_ID,
272: PARENT_OBJECT_TYPE,
273: PARENT_OBJECT_ID,
274: LEVEL_ID)
275: SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
276: p_entity_type,
277: p_entity_id,
278: FND_GLOBAL.USER_ID,
279: SYSDATE,
301: FETCH find_parent_lob INTO l_temp_parent_id;
302: CLOSE find_parent_lob;
303:
304: --Step 5(Condition A) : Populate table with object_type = ORGANIZATION and parent_object_type = LINEOFBUSINESS
305: INSERT INTO AMW_ENTITY_HIERARCHIES
306: (ENTITY_HIERARCHY_ID,
307: ENTITY_TYPE,
308: ENTITY_ID,
309: CREATED_BY,
315: OBJECT_ID,
316: PARENT_OBJECT_TYPE,
317: PARENT_OBJECT_ID,
318: LEVEL_ID )
319: SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
320: p_entity_type,
321: p_entity_id,
322: FND_GLOBAL.USER_ID,
323: SYSDATE,
333:
334: ELSE
335:
336: --Step 5(Condition B) : Populate table with object_type = ORGANIZATION and parent_object_type = DUMMYLOB
337: INSERT INTO AMW_ENTITY_HIERARCHIES
338: (ENTITY_HIERARCHY_ID,
339: ENTITY_TYPE,
340: ENTITY_ID,
341: CREATED_BY,
347: OBJECT_ID,
348: PARENT_OBJECT_TYPE,
349: PARENT_OBJECT_ID,
350: LEVEL_ID )
351: SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
352: p_entity_type,
353: p_entity_id,
354: FND_GLOBAL.USER_ID,
355: SYSDATE,
410: IF found_sub_parent = true
411: THEN
412:
413: --Step 7 : Populate table with Subsidiary hierarchy
414: INSERT INTO AMW_ENTITY_HIERARCHIES
415: (ENTITY_HIERARCHY_ID,
416: ENTITY_TYPE,
417: ENTITY_ID,
418: CREATED_BY,
424: OBJECT_ID,
425: PARENT_OBJECT_TYPE,
426: PARENT_OBJECT_ID,
427: LEVEL_ID )
428: SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
429: p_entity_type,
430: p_entity_id,
431: FND_GLOBAL.USER_ID,
432: SYSDATE,
441: FROM dual;
442: ELSE
443:
444: --Step 7 : Populate table with Subsidiary hierarchy
445: INSERT INTO AMW_ENTITY_HIERARCHIES
446: (ENTITY_HIERARCHY_ID,
447: ENTITY_TYPE,
448: ENTITY_ID,
449: CREATED_BY,
455: OBJECT_ID,
456: PARENT_OBJECT_TYPE,
457: PARENT_OBJECT_ID,
458: LEVEL_ID )
459: SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
460: p_entity_type,
461: p_entity_id,
462: FND_GLOBAL.USER_ID,
463: SYSDATE,
519: IF found_lob_parent = true
520: THEN
521:
522: --Step 9 : Populate table with LOB hierarchy
523: INSERT INTO AMW_ENTITY_HIERARCHIES
524: (ENTITY_HIERARCHY_ID,
525: ENTITY_TYPE,
526: ENTITY_ID,
527: CREATED_BY,
533: OBJECT_ID,
534: PARENT_OBJECT_TYPE,
535: PARENT_OBJECT_ID,
536: LEVEL_ID )
537: SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
538: p_entity_type,
539: p_entity_id,
540: FND_GLOBAL.USER_ID,
541: SYSDATE,
550: FROM dual;
551: ELSE
552:
553: --Step 9 : Populate table with LOB hierarchy
554: INSERT INTO AMW_ENTITY_HIERARCHIES
555: (ENTITY_HIERARCHY_ID,
556: ENTITY_TYPE,
557: ENTITY_ID,
558: CREATED_BY,
564: OBJECT_ID,
565: PARENT_OBJECT_TYPE,
566: PARENT_OBJECT_ID,
567: LEVEL_ID )
568: SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
569: p_entity_type,
570: p_entity_id,
571: FND_GLOBAL.USER_ID,
572: SYSDATE,
647:
648: END IF;
649:
650: --Step 14: Finally remove all the old entries from the table
651: DELETE FROM AMW_ENTITY_HIERARCHIES
652: WHERE DELETE_FLAG = 'Y'
653: AND ENTITY_ID = p_entity_id
654: AND ENTITY_TYPE = p_entity_type;
655:
750: IF found_parent = TRUE
751: THEN
752:
753: --Step 11: Populate selected CustomORGS into table and build hierarchy
754: INSERT INTO AMW_ENTITY_HIERARCHIES
755: (ENTITY_HIERARCHY_ID,
756: ENTITY_TYPE,
757: ENTITY_ID,
758: CREATED_BY,
764: OBJECT_ID,
765: PARENT_OBJECT_TYPE,
766: PARENT_OBJECT_ID,
767: LEVEL_ID )
768: SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
769: p_entity_type,
770: p_entity_id,
771: FND_GLOBAL.USER_ID,
772: SYSDATE,
781: FROM dual;
782:
783: ELSE
784: --Step 11: Populate selected CustomORGS into table and build hierarchy
785: INSERT INTO AMW_ENTITY_HIERARCHIES
786: (ENTITY_HIERARCHY_ID,
787: ENTITY_TYPE,
788: ENTITY_ID,
789: CREATED_BY,
795: OBJECT_ID,
796: PARENT_OBJECT_TYPE,
797: PARENT_OBJECT_ID,
798: LEVEL_ID )
799: SELECT AMW_ENTITY_HIERARCHIES_S.nextval,
800: p_entity_type,
801: p_entity_id,
802: FND_GLOBAL.USER_ID,
803: SYSDATE,
826: IS
827:
828: CURSOR get_existing_objects (l_object_type VARCHAR2) IS
829: SELECT distinct object_id
830: FROM AMW_ENTITY_HIERARCHIES
831: WHERE entity_type = p_entity_type
832: AND entity_id = p_entity_id
833: AND delete_flag = 'Y'
834: AND object_type = l_object_type;
3477: IS
3478: CURSOR get_relevant_rows
3479: IS
3480: SELECT 'Y'
3481: FROM AMW_ENTITY_HIERARCHIES
3482: WHERE entity_id = p_entity_id
3483: FOR UPDATE NOWAIT;
3484:
3485: CURSOR get_object_type(l_object_id NUMBER, l_entity_id NUMBER)
3484:
3485: CURSOR get_object_type(l_object_id NUMBER, l_entity_id NUMBER)
3486: IS
3487: SELECT object_type
3488: FROM AMW_ENTITY_HIERARCHIES
3489: WHERE object_id = l_object_id
3490: AND entity_id = l_entity_id
3491: AND entity_type = p_entity_type;
3492:
3541: FETCH get_relevant_rows INTO l_exists;
3542: CLOSE get_relevant_rows;
3543:
3544: --Step 1: Delete specified row
3545: DELETE FROM AMW_ENTITY_HIERARCHIES
3546: WHERE object_type = p_object_type
3547: AND object_id = p_object_id
3548: AND entity_type = p_entity_type
3549: AND entity_id = p_entity_id;
3558: --Step 3: Delete parent and child rows as found from stack
3559: FOR each_rec IN 1..l_object_tbl.count
3560: LOOP
3561:
3562: DELETE FROM AMW_ENTITY_HIERARCHIES
3563: WHERE object_id = l_object_tbl(each_rec).org_id
3564: AND object_type = p_object_type
3565: AND entity_id = p_entity_id
3566: AND entity_type = p_entity_type;
3564: AND object_type = p_object_type
3565: AND entity_id = p_entity_id
3566: AND entity_type = p_entity_type;
3567:
3568: DELETE FROM AMW_ENTITY_HIERARCHIES
3569: WHERE parent_object_id = l_object_tbl(each_rec).org_id
3570: AND parent_object_type = p_object_type
3571: AND entity_id = p_entity_id
3572: AND entity_type = p_entity_type;
3716: -- Initialize API return status to SUCCESS
3717: x_return_status := FND_API.G_RET_STS_SUCCESS;
3718:
3719: --Step 1: Delete the node 'ORG' AND 'ORGANIZATION' from the hierarchy table
3720: DELETE FROM AMW_ENTITY_HIERARCHIES
3721: WHERE object_id = p_object_id
3722: AND entity_id = p_entity_id
3723: AND entity_type = p_entity_type;
3724:
3749: --Step 4: Delete parent and child rows from hierarchy and denormalized tables as found from stack
3750: FOR each_rec IN 1..l_object_tbl.count
3751: LOOP
3752:
3753: DELETE FROM AMW_ENTITY_HIERARCHIES
3754: WHERE object_id = l_object_tbl(each_rec).org_id
3755: AND object_type = 'ORG'
3756: AND parent_object_type = 'ORG'
3757: AND entity_id = p_entity_id
3756: AND parent_object_type = 'ORG'
3757: AND entity_id = p_entity_id
3758: AND entity_type = p_entity_type;
3759:
3760: DELETE FROM AMW_ENTITY_HIERARCHIES
3761: WHERE parent_object_id = l_object_tbl(each_rec).org_id
3762: AND parent_object_type = 'ORG'
3763: AND object_type = 'ORG'
3764: AND entity_id = p_entity_id
3801:
3802: CURSOR get_child_orgs(l_entity_id NUMBER, l_object_id NUMBER)
3803: IS
3804: SELECT object_id
3805: FROM AMW_ENTITY_HIERARCHIES
3806: WHERE parent_object_id = l_object_id
3807: AND entity_id = l_entity_id
3808: AND entity_type = p_entity_type
3809: AND object_type = 'ORG'
3838:
3839: CURSOR get_child_objects(l_entity_id NUMBER, l_object_id NUMBER)
3840: IS
3841: SELECT object_id
3842: FROM AMW_ENTITY_HIERARCHIES
3843: WHERE parent_object_id = l_object_id
3844: AND entity_id = l_entity_id
3845: AND entity_type = p_entity_type
3846: AND parent_object_type = p_object_type;
3982: px_org_ids IN OUT NOCOPY VARCHAR2)
3983: IS
3984: CURSOR c_sub_orgs IS
3985: SELECT object_id
3986: FROM amw_entity_Hierarchies
3987: WHERE entity_id = p_entity_id
3988: AND entity_type = p_entity_type
3989: AND parent_object_type = 'ORG'
3990: AND parent_object_id = p_org_id;
4017: x_org_ids OUT NOCOPY VARCHAR2)
4018: IS
4019: CURSOR c_root_orgs IS
4020: SELECT object_id
4021: FROM amw_entity_Hierarchies
4022: WHERE entity_id = p_entity_id
4023: AND entity_type = p_entity_type
4024: AND parent_object_type = 'ROOTNODE'
4025: AND object_type = 'ORG';