APPS.ASO_SALES_TEAM_PVT dependencies on ASO_STEAM_TEMP

Line 293: DELETE FROM ASO_STEAM_TEMP;

289: aso_debug_pub.add('Operation_Code: '||P_Operation,1,'Y');
290: aso_debug_pub.add('Before Truncating Temp Table',1,'Y');
291: END IF;
292:
293: DELETE FROM ASO_STEAM_TEMP;
294:
295: IF P_Operation <> 'CREATE' THEN
296:
297: OPEN C_Get_Header_Info (p_qte_header_rec.Quote_header_id);

Line 762: INSERT INTO ASO_STEAM_TEMP ( Access_Id,

758: aso_debug_pub.add('Before Temp_Insert'||NVL(lx_gen_return_Rec.resource_id.COUNT,0),1,'Y');
759: END IF;
760:
761: FORALL i IN lx_gen_return_Rec.resource_id.FIRST..lx_gen_return_Rec.resource_id.LAST
762: INSERT INTO ASO_STEAM_TEMP ( Access_Id,
763: Quote_Number,
764: Resource_Id,
765: Resource_Grp_Id,
766: Created_By,

Line 801: select count(*) into i from aso_steam_temp;

797:
798: IF l_Reassign_Flag = 'Y' THEN
799:
800: IF aso_debug_pub.g_debug_flag = 'Y' THEN
801: select count(*) into i from aso_steam_temp;
802: aso_debug_pub.add('count: '||i,1,'Y');
803:
804: aso_debug_pub.add('Before Delete Not Kept Existing Res ',1,'Y');
805: END IF;

Line 809: FROM ASO_QUOTE_ACCESSES A , ASO_STEAM_TEMP B

805: END IF;
806: DELETE FROM ASO_QUOTE_ACCESSES C
807: WHERE C.resource_id NOT IN
808: (SELECT A.resource_id
809: FROM ASO_QUOTE_ACCESSES A , ASO_STEAM_TEMP B
810: WHERE ((A.resource_id = B.resource_id
811: AND NVL(A.resource_grp_id, -999) = NVL(B.resource_grp_id, -999)
812: AND NVL(A.role_id, -999) = NVL(B.role_id, -999)
813: AND NVL(A.keep_flag,'N') = 'N')

Line 819: select count(*) into i from aso_steam_temp;

815: AND A.Quote_Number = l_qte_header_rec.quote_number)
816: AND C.Quote_Number = l_qte_header_rec.quote_number;
817:
818: IF aso_debug_pub.g_debug_flag = 'Y' THEN
819: select count(*) into i from aso_steam_temp;
820: aso_debug_pub.add('count: '||i,1,'Y');
821:
822: aso_debug_pub.add('Before Delete Kept Res ',1,'Y');
823: END IF;

Line 833: DELETE FROM ASO_STEAM_TEMP

829: aso_debug_pub.add('Keep_Res_Id.COUNT: '||NVL(Keep_Res_Id.COUNT,0),1,'Y');
830: END IF;
831: IF NVL(Keep_Res_Id.COUNT,0) > 0 THEN
832: FORALL i IN Keep_Res_Id.FIRST..Keep_Res_Id.LAST
833: DELETE FROM ASO_STEAM_TEMP
834: WHERE Resource_Id = Keep_Res_Id(i);
835: END IF;
836: END IF;
837:

Line 839: select count(*) into i from aso_steam_temp;

835: END IF;
836: END IF;
837:
838: IF aso_debug_pub.g_debug_flag = 'Y' THEN
839: select count(*) into i from aso_steam_temp;
840: aso_debug_pub.add('count: '||i,1,'Y');
841:
842: aso_debug_pub.add('Before Delete Invalid Roles ',1,'Y');
843: END IF;

Line 844: DELETE FROM ASO_STEAM_TEMP

840: aso_debug_pub.add('count: '||i,1,'Y');
841:
842: aso_debug_pub.add('Before Delete Invalid Roles ',1,'Y');
843: END IF;
844: DELETE FROM ASO_STEAM_TEMP
845: WHERE Role_Id IS NOT NULL
846: AND Role_Id NOT IN ( SELECT Role_Id
847: FROM JTF_RS_ROLES_B
848: WHERE Role_Type_Code IN ('TELESALES', 'SALES','FIELDSALES','PRM'));

Line 851: select count(*) into i from aso_steam_temp;

847: FROM JTF_RS_ROLES_B
848: WHERE Role_Type_Code IN ('TELESALES', 'SALES','FIELDSALES','PRM'));
849:
850: IF aso_debug_pub.g_debug_flag = 'Y' THEN
851: select count(*) into i from aso_steam_temp;
852: aso_debug_pub.add('count: '||i,1,'Y');
853:
854: aso_debug_pub.add('Before Delete Duplicate Res/Roles/Grp combos ',1,'Y');
855: END IF;

Line 856: l_dynamic := 'DELETE FROM ASO_STEAM_TEMP '||

852: aso_debug_pub.add('count: '||i,1,'Y');
853:
854: aso_debug_pub.add('Before Delete Duplicate Res/Roles/Grp combos ',1,'Y');
855: END IF;
856: l_dynamic := 'DELETE FROM ASO_STEAM_TEMP '||
857: 'WHERE rowid NOT IN ( SELECT rowid '||
858: 'FROM ( SELECT rowid, dense_rank() OVER '||
859: '( PARTITION BY Resource_Id '||
860: 'ORDER BY Role_Id DESC nulls last, Resource_Grp_Id DESC nulls last) AS Rank_Val '||

Line 861: 'FROM ASO_STEAM_TEMP '||

857: 'WHERE rowid NOT IN ( SELECT rowid '||
858: 'FROM ( SELECT rowid, dense_rank() OVER '||
859: '( PARTITION BY Resource_Id '||
860: 'ORDER BY Role_Id DESC nulls last, Resource_Grp_Id DESC nulls last) AS Rank_Val '||
861: 'FROM ASO_STEAM_TEMP '||
862: 'ORDER BY Role_Id DESC nulls last, Resource_Grp_Id DESC nulls last ) '||
863: 'WHERE Rank_Val = 1 )';
864:
865: EXECUTE IMMEDIATE l_dynamic;

Line 868: select count(*) into i from aso_steam_temp;

864:
865: EXECUTE IMMEDIATE l_dynamic;
866:
867: IF aso_debug_pub.g_debug_flag = 'Y' THEN
868: select count(*) into i from aso_steam_temp;
869: aso_debug_pub.add('count: '||i,1,'Y');
870:
871: aso_debug_pub.add('Before Delete Duplicate Resources ',1,'Y');
872: END IF;

Line 873: DELETE FROM ASO_STEAM_TEMP

869: aso_debug_pub.add('count: '||i,1,'Y');
870:
871: aso_debug_pub.add('Before Delete Duplicate Resources ',1,'Y');
872: END IF;
873: DELETE FROM ASO_STEAM_TEMP
874: WHERE rowid IN (
875: SELECT rowid FROM ASO_STEAM_TEMP
876: GROUP BY rowid, Resource_Id
877: MINUS

Line 875: SELECT rowid FROM ASO_STEAM_TEMP

871: aso_debug_pub.add('Before Delete Duplicate Resources ',1,'Y');
872: END IF;
873: DELETE FROM ASO_STEAM_TEMP
874: WHERE rowid IN (
875: SELECT rowid FROM ASO_STEAM_TEMP
876: GROUP BY rowid, Resource_Id
877: MINUS
878: SELECT min(rowid) FROM ASO_STEAM_TEMP
879: GROUP BY Resource_Id);

Line 878: SELECT min(rowid) FROM ASO_STEAM_TEMP

874: WHERE rowid IN (
875: SELECT rowid FROM ASO_STEAM_TEMP
876: GROUP BY rowid, Resource_Id
877: MINUS
878: SELECT min(rowid) FROM ASO_STEAM_TEMP
879: GROUP BY Resource_Id);
880:
881: IF aso_debug_pub.g_debug_flag = 'Y' THEN
882: select count(*) into i from aso_steam_temp;

Line 882: select count(*) into i from aso_steam_temp;

878: SELECT min(rowid) FROM ASO_STEAM_TEMP
879: GROUP BY Resource_Id);
880:
881: IF aso_debug_pub.g_debug_flag = 'Y' THEN
882: select count(*) into i from aso_steam_temp;
883: aso_debug_pub.add('count: '||i,1,'Y');
884:
885: aso_debug_pub.add('Before Insert into Quote_Accesses ',1,'Y');
886: END IF;

Line 913: FROM ASO_STEAM_TEMP

909: Full_Access_Flag,
910: Territory_Id,
911: Territory_Source_Flag,
912: Role_Id
913: FROM ASO_STEAM_TEMP
914: WHERE Quote_Number = l_Qte_Header_Rec.Quote_Number;
915:
916: IF aso_debug_pub.g_debug_flag = 'Y' THEN
917: aso_debug_pub.add('After Insert into Quote_Accesses ',1,'Y');