APPS.AS_GAR_PROPOSALS_PUB dependencies on PRP_PROPOSAL_ACCESSES

Line 14: -- 2: Merge and insert records from winners into PRP_PROPOSAL_ACCESSES

10: -- This package contains procedures to accomplish each of the following
11: -- tasks:
12: -- 1: Call the JTY API to process data from JTY trans tables and
13: -- populate JTY winners.
14: -- 2: Merge and insert records from winners into PRP_PROPOSAL_ACCESSES
15: -- 3: Soft Delete unwanted records from PRP_PROPOSAL_ACCESSES
16: --
17: -- NOTE: No Owner Assignment and Cleanup steps required for PROPOSALS
18: ---------------------------------------------------------------------------

Line 15: -- 3: Soft Delete unwanted records from PRP_PROPOSAL_ACCESSES

11: -- tasks:
12: -- 1: Call the JTY API to process data from JTY trans tables and
13: -- populate JTY winners.
14: -- 2: Merge and insert records from winners into PRP_PROPOSAL_ACCESSES
15: -- 3: Soft Delete unwanted records from PRP_PROPOSAL_ACCESSES
16: --
17: -- NOTE: No Owner Assignment and Cleanup steps required for PROPOSALS
18: ---------------------------------------------------------------------------
19: /*-------------------------------------------------------------------------+

Line 717: FROM PRP_PROPOSAL_ACCESSES A,

713: SELECT A.proposal_id, -- The use nested loop hint is removed in ACCOUNTS ONLY..
714: A.resource_id,
715: A.resource_group_id,
716: decode(NVL(WIN.full_access_flag,'N'),'Y','FULL','READ') full_access_flag
717: FROM PRP_PROPOSAL_ACCESSES A,
718: JTF_TAE_1001_PROP_WINNERS WIN
719: WHERE NVL(A.ACCESS_LEVEL,'N') <> decode(NVL(WIN.full_access_flag,'N'),'Y','FULL','READ')
720: AND WIN.SOURCE_ID = -1001
721: AND WIN.worker_id = c_worker_id

Line 777: UPDATE PRP_PROPOSAL_ACCESSES ACC

773: END IF;
774: WHILE l_attempts < 3 LOOP
775: BEGIN
776: FORALL i IN l_first .. l_last
777: UPDATE PRP_PROPOSAL_ACCESSES ACC
778: SET object_version_number = NVL(object_version_number,0) + 1,
779: ACC.last_update_date = SYSDATE,
780: ACC.last_updated_by = p_terr_globals.user_id,
781: ACC.last_update_login = p_terr_globals.last_update_login,

Line 799: UPDATE PRP_PROPOSAL_ACCESSES ACC

795: IF l_attempts = 3 THEN
796: FOR i IN l_first .. l_last
797: LOOP
798: BEGIN
799: UPDATE PRP_PROPOSAL_ACCESSES ACC
800: SET object_version_number = NVL(object_version_number,0) + 1,
801: ACC.last_update_date = SYSDATE,
802: ACC.last_updated_by = p_terr_globals.user_id,
803: ACC.last_update_login = p_terr_globals.last_update_login,

Line 945: INSERT INTO PRP_PROPOSAL_ACCESSES

941: END IF;
942: BEGIN
943: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_BULK_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' to '||l_last);
944: FORALL i IN l_first .. l_last
945: INSERT INTO PRP_PROPOSAL_ACCESSES
946: (
947: proposal_access_id
948: ,object_version_number
949: ,proposal_id

Line 961: SELECT prp_proposal_accesses_s1.nextval -- Check With Sequence

957: ,last_update_date
958: ,last_update_login
959: )
960: (
961: SELECT prp_proposal_accesses_s1.nextval -- Check With Sequence
962: ,1
963: ,l_proposal_id(i)
964: ,l_resource_id(i)
965: ,l_faf(i)

Line 975: FROM PRP_PROPOSAL_ACCESSES AA

971: ,SYSDATE
972: ,p_terr_globals.last_update_login
973: FROM DUAL
974: WHERE NOT EXISTS ( SELECT 'X'
975: FROM PRP_PROPOSAL_ACCESSES AA
976: WHERE AA.proposal_id = l_proposal_id(i)
977: AND AA.resource_id = l_resource_id(i)
978: AND NVL(AA.resource_group_id,-777)= NVL(l_resource_group_id(i),-777)
979: )

Line 988: INSERT INTO PRP_PROPOSAL_ACCESSES

984: WHEN DUP_VAL_ON_INDEX THEN
985: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_INSACC || AS_GAR.G_IND_INS || AS_GAR.G_N_ROWS_PROCESSED ||l_first||' - '||l_last);
986: FOR i IN l_first .. l_last LOOP
987: BEGIN
988: INSERT INTO PRP_PROPOSAL_ACCESSES
989: (
990: proposal_access_id
991: ,object_version_number
992: ,proposal_id

Line 1004: SELECT prp_proposal_accesses_s1.nextval -- Check With Sequence

1000: ,last_update_date
1001: ,last_update_login
1002: )
1003: (
1004: SELECT prp_proposal_accesses_s1.nextval -- Check With Sequence
1005: ,1
1006: ,l_proposal_id(i)
1007: ,l_resource_id(i)
1008: ,l_faf(i)

Line 1018: FROM PRP_PROPOSAL_ACCESSES AA

1014: ,SYSDATE
1015: ,p_terr_globals.last_update_login
1016: FROM DUAL
1017: WHERE NOT EXISTS ( SELECT 'X'
1018: FROM PRP_PROPOSAL_ACCESSES AA
1019: WHERE AA.proposal_id = l_proposal_id(i)
1020: AND AA.resource_id = l_resource_id(i)
1021: AND NVL(AA.resource_group_id,-777)= NVL(l_resource_group_id(i),-777)
1022: )

Line 1180: FROM PRP_PROPOSAL_ACCESSES A

1176: p_terr_globals.request_id,
1177: SYSDATE
1178: FROM
1179: ( SELECT DISTINCT a.proposal_access_id acc_id
1180: FROM PRP_PROPOSAL_ACCESSES A
1181: WHERE A.proposal_id=l_proposal_id(i)
1182: AND NVL(A.resource_group_id,-777)=NVL(l_resource_group_id(i),-777)
1183: AND A.resource_id=l_resource_id(i)
1184: AND NOT EXISTS

Line 1231: FROM PRP_PROPOSAL_ACCESSES A

1227: p_terr_globals.request_id,
1228: SYSDATE
1229: FROM
1230: ( SELECT DISTINCT a.proposal_access_id acc_id
1231: FROM PRP_PROPOSAL_ACCESSES A
1232: WHERE A.proposal_id=l_proposal_id(i)
1233: AND NVL(A.resource_group_id,-777)=NVL(l_resource_group_id(i),-777)
1234: AND A.resource_id=l_resource_id(i)
1235: AND NOT EXISTS

Line 1389: DELETE PRP_PROPOSAL_ACCESSES ACC

1385: WHILE l_attempts < 3 LOOP --{L4
1386: BEGIN
1387: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_BULK_UPD || AS_GAR.G_START);
1388: FORALL i in l_first..l_last
1389: DELETE PRP_PROPOSAL_ACCESSES ACC
1390: WHERE ACC.PROPOSAL_ID=l_proposal_id(i)
1391: AND ACC.KEEP_FLAG = 'N'
1392: AND NOT EXISTS (SELECT 'X'
1393: FROM JTF_TAE_1001_PROP_WINNERS W

Line 1412: DELETE PRP_PROPOSAL_ACCESSES ACC

1408: IF l_attempts = 3 THEN
1409: FOR i IN l_first .. l_last LOOP --{L5
1410: BEGIN
1411: AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || AS_GAR.G_CC || AS_GAR.G_UPD_ACCESSES || AS_GAR.G_IND_UPD || AS_GAR.G_START);
1412: DELETE PRP_PROPOSAL_ACCESSES ACC
1413: WHERE ACC.PROPOSAL_ID=l_proposal_id(i)
1414: AND ACC.KEEP_FLAG = 'N'
1415: AND NOT EXISTS (SELECT 'X'
1416: FROM JTF_TAE_1001_PROP_WINNERS W