[Home] [Help]
10: -- PACKAGE NAME: JTF_TTY_EXCEL_NAORG_PVT
11: -- ---------------------------------------------------
12: -- PURPOSE
13: --
14: -- This package is used to populate the interface table jtf_tty_webadi_interface
15: -- for the admin export download
16: --
17: --
18: -- Procedures:
118: L_WEBSITE_STR := NULL;
119: L_EMAIL_STR := NULL;
120:
121:
122: /* form the pl/sql block to insert all the ORGs in the interface table jtf_tty_webadi_interface */
123: l_na_query :=
124: 'DECLARE ' ||
125: ' P_USERID INTEGER := :P_USERID; '||
126: ' P_SICCODE VARCHAR2(100) := :P_SICCODE; '||
149: ' P_NUM_EMP_TO VARCHAR2(10) := :P_NUM_EMP_TO; ' ||
150: ' P_CUST_CATEGORY VARCHAR2(60) := :P_CUST_CATEGORY; ' ||
151: ' P_IDENT_ADDR_FLAG VARCHAR2(1) := :P_IDADDR_FLAG; ' ||
152: ' BEGIN '||
153: ' INSERT into JTF_TTY_WEBADI_INTERFACE ( ' ||
154: ' USER_SEQUENCE' ||
155: ' ,USER_ID' ||
156: ' ,TERR_GRP_ACCT_ID' ||
157: ' ,JTF_TTY_WEBADI_INT_ID' ||
582: WHERE rol.role_code = narsc.rsc_role_code
583: AND narsc.terr_group_account_id = ga.terr_group_account_id
584: AND ga.terr_group_account_id IN
585: ( SELECT terr_grp_acct_id
586: FROM jtf_tty_webadi_interface
587: WHERE user_id = userid
588: )
589: GROUP BY ga.terr_group_account_id, rol.role_code
590: ORDER BY MAX(rol.role_name)
593:
594:
595: CURSOR getNAFromInterface(userid IN NUMBER) IS
596: SELECT jtf_tty_webadi_int_id, terr_grp_acct_id
597: FROM jtf_tty_webadi_interface
598: WHERE user_id = userid;
599:
600: CURSOR getSalesperson( P_TG_ACCT_ID IN NUMBER) IS
601: SELECT rsc.resource_name resource_name
641: P_GU_NAME_STR := NULL;
642:
643: -- dbms_output.put_line('Start POPULATE_INTERFACE_FOR_NA');
644:
645: /* form the pl/sql block to insert all the NAs in the interface table jtf_tty_webadi_interface */
646: l_na_query :=
647: 'DECLARE ' ||
648: ' P_USERID INTEGER := :P_USERID; '||
649: ' P_GRPID NUMBER := :P_GRPID ; '|| --vbghosh Added
681: ' P_CUST_CATEGORY VARCHAR2(60) := :P_CUST_CATEGORY; ' ||
682: ' P_IDENT_ADDR_FLAG VARCHAR2(1) := :P_IDADDR_FLAG; ' || -- end added
683: ' BEGIN '||
684:
685: ' INSERT into JTF_TTY_WEBADI_INTERFACE ( ' ||
686: ' USER_SEQUENCE ' ||
687: ' ,USER_ID ' ||
688: ' ,TERR_GRP_ACCT_ID ' ||
689: ' ,JTF_TTY_WEBADI_INT_ID ' ||
1396: END LOOP; -- of slotting
1397: END LOOP; -- of SALES
1398:
1399:
1400: UPDATE JTF_TTY_WEBADI_INTERFACE -- /*+ INDEX JTF_TTY_WEBADI_INTF_N2 */
1401: SET RESOURCE1_NAME=RESOURCE_NAME(1),GROUP1_NAME=GROUP_NAME(1),ROLE1_NAME=ROLE_NAME(1),
1402: RESOURCE1_ATTRIBUTE1=RESOURCE_ATTRIBUTE1(1), RESOURCE1_ATTRIBUTE2=RESOURCE_ATTRIBUTE2(1),
1403: RESOURCE1_ATTRIBUTE3=RESOURCE_ATTRIBUTE3(1), RESOURCE1_ATTRIBUTE4=RESOURCE_ATTRIBUTE4(1),
1404: RESOURCE1_ATTRIBUTE5=RESOURCE_ATTRIBUTE5(1),RESOURCE1_START_DATE=RESOURCE_START_DATE(1),RESOURCE1_END_DATE=RESOURCE_END_DATE(1),
1610: X_RETCODE := 0;
1611: X_ERRBUF := NULL;
1612: l_rows_inserted := 0;
1613:
1614: /* Get the next sequence number of the table jtf_tty_webadi_interface */
1615: BEGIN
1616: SELECT jtf_tty_interface_s.NEXTVAL
1617: INTO g_seq
1618: FROM DUAL;
1637:
1638: /* Delete the existing data for the user from the interface table */
1639: BEGIN
1640: DELETE /*+ index(tty jtf_tty_webadi_intf_n1) */
1641: FROM JTF_TTY_WEBADI_INTERFACE tty
1642: WHERE tty.user_id = TO_NUMBER(p_userid);
1643:
1644: COMMIT;
1645: