[Home] [Help]
1296: and jwot.TERR_QUAL_ID70 IS NULL and jwot.TERR_QUAL_ID71 IS NULL
1297: and jwot.TERR_QUAL_ID72 IS NULL and jwot.TERR_QUAL_ID73 IS NULL
1298: and jwot.TERR_QUAL_ID74 IS NULL and jwot.TERR_QUAL_ID75 IS NULL
1299: )
1300: OR EXISTS (select 1 from jty_webadi_resources jwr
1301: where jwot.lay_seq_num = jwr.lay_seq_num
1302: and jwr.terr_rsc_id is null
1303: and jwr.header = l_header)
1304: )
1354: update JTY_WEBADI_OTH_TERR_INTF jwot
1355: set jwot.status = x_return_status,
1356: jwot.error_msg = X_Msg_Data
1357: where exists
1358: (select 1 from JTY_WEBADI_RESOURCES jwr
1359: where jwot.lay_seq_num = jwr.lay_seq_num
1360: AND jwr.TRANS_ACCESS_CODE1 is null
1361: AND jwr.TRANS_ACCESS_CODE2 is null
1362: AND jwr.TRANS_ACCESS_CODE3 is null
1379: -- Fix for bug #5583243 START
1380: -- if any access is left blank while uploading the data from excel,
1381: -- then the upload should populate the value 'NONE' in the database.
1382: debugmsg('VALIDATE_TERRITORY_RECORDS: Validate Update None if access is left blank : ' );
1383: UPDATE JTY_WEBADI_RESOURCES jut
1384: SET jut.TRANS_ACCESS_CODE1 = NVL(jut.TRANS_ACCESS_CODE1,'NONE')
1385: , jut.TRANS_ACCESS_CODE2 = NVL(jut.TRANS_ACCESS_CODE2,'NONE')
1386: , jut.TRANS_ACCESS_CODE3 = NVL(jut.TRANS_ACCESS_CODE3,'NONE')
1387: , jut.TRANS_ACCESS_CODE4 = NVL(jut.TRANS_ACCESS_CODE4,'NONE')
1408: update JTY_WEBADI_OTH_TERR_INTF jwot
1409: set jwot.status = x_return_status,
1410: jwot.error_msg = X_Msg_Data
1411: where exists
1412: (select 1 from JTY_WEBADI_RESOURCES jwr
1413: where jwot.lay_seq_num = jwr.lay_seq_num
1414: AND jwr.TRANS_ACCESS_CODE1 = 'NONE'
1415: AND jwr.TRANS_ACCESS_CODE2 = 'NONE'
1416: AND jwr.TRANS_ACCESS_CODE3 = 'NONE'
1446: and jwot.status is null
1447: and jwot.action_flag = 'C'
1448: and jwot.header = 'RSC'
1449: and exists( select 1
1450: from JTF_TERR_RSC_ALL jtr,JTY_WEBADI_RESOURCES jwr
1451: where jwr.USER_SEQUENCE = jwot.USER_SEQUENCE
1452: and jwr.header = jwot.header
1453: and jwr.INTERFACE_TYPE = jwot.INTERFACE_TYPE
1454: and jwot.lay_seq_num = jwr.lay_seq_num
1471: and jwot.status is null
1472: and jwot.action_flag = 'C'
1473: and jwot.header = 'RSC'
1474: and exists( select 1
1475: from JTY_WEBADI_RESOURCES jwr1,JTY_WEBADI_RESOURCES jwr2
1476: where jwr1.USER_SEQUENCE = jwot.USER_SEQUENCE
1477: and jwr1.header = jwot.header
1478: and jwr1.INTERFACE_TYPE = jwot.INTERFACE_TYPE
1479: and jwot.lay_seq_num = jwr1.lay_seq_num
1496: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
1497: set jwot.status = x_return_status,
1498: jwot.error_msg = X_Msg_Data
1499: where exists
1500: (select 1 from JTY_WEBADI_RESOURCES jwr
1501: where jwot.lay_seq_num = jwr.lay_seq_num
1502: and jwr.USER_SEQUENCE = jwot.USER_SEQUENCE
1503: and jwr.interface_type = jwot.interface_type
1504: and jwr.header = jwot.header
1518: UPDATE JTY_WEBADI_OTH_TERR_INTF jwot
1519: set jwot.status = x_return_status,
1520: jwot.error_msg = X_Msg_Data
1521: where exists
1522: (select 1 from JTY_WEBADI_RESOURCES jwr
1523: where jwot.lay_seq_num = jwr.lay_seq_num
1524: and jwr.USER_SEQUENCE = jwot.USER_SEQUENCE
1525: and jwr.interface_type = jwot.interface_type
1526: and jwr.header = jwot.header
1855: and jwot.user_sequence = jwot2.user_sequence
1856: and jwot.interface_type = jwot2.interface_type
1857: and jwot2.header = 'TERR');
1858:
1859: UPDATE JTY_WEBADI_RESOURCES jwr
1860: SET TERR_ID = (select jwot.terr_id
1861: from JTY_WEBADI_OTH_TERR_INTF jwot
1862: where jwr.lay_seq_num = jwot.lay_seq_num
1863: and rownum = 1) ,
4100: v_header varchar2
4101: ) IS
4102: SELECT jwr.TERR_RSC_ID, jwot.lay_seq_num
4103: FROM JTY_WEBADI_OTH_TERR_INTF jwot,
4104: JTY_WEBADI_RESOURCES jwr
4105: WHERE jwot.lay_seq_num = jwr.lay_seq_num
4106: and jwot.header = jwr.header
4107: and jwot.user_sequence = jwr.user_sequence
4108: AND jwot.interface_type = jwr.interface_type
10646: jwr.ATTRIBUTE12, jwr.ATTRIBUTE13, jwr.ATTRIBUTE14,
10647: jwr.ATTRIBUTE15
10648: from
10649: JTY_WEBADI_OTH_TERR_INTF jwot,
10650: JTY_WEBADI_RESOURCES jwr,
10651: jtf_rs_resource_extns res
10652: where jwr.RESOURCE_ID = res.resource_id(+)
10653: and jwr.user_sequence = jwot.user_sequence
10654: and jwr.interface_type = jwot.interface_type
10674: (
10675: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10676: 1 qual_type_num, TERR_RSC_ACCESS_ID1 TERR_RSC_ACCESS_ID,
10677: TRANS_ACCESS_CODE1 TRANS_ACCESS_CODE
10678: FROM JTY_WEBADI_RESOURCES jut
10679: where jut.USER_SEQUENCE = v_user_sequence
10680: and jut.TRANS_ACCESS_CODE1 is not null
10681: and jut.TERR_RSC_ACCESS_ID1 is null
10682: and jut.header = v_header
10684: union all
10685: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10686: 2 qual_type_num, TERR_RSC_ACCESS_ID2 TERR_RSC_ACCESS_ID,
10687: TRANS_ACCESS_CODE2 TRANS_ACCESS_CODE
10688: FROM JTY_WEBADI_RESOURCES jut
10689: where jut.USER_SEQUENCE = v_user_sequence
10690: and jut.TRANS_ACCESS_CODE2 is not null
10691: and jut.TERR_RSC_ACCESS_ID2 is null
10692: and jut.header = v_header
10694: union all
10695: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10696: 3 qual_type_num, TERR_RSC_ACCESS_ID3 TERR_RSC_ACCESS_ID,
10697: TRANS_ACCESS_CODE3 TRANS_ACCESS_CODE
10698: FROM JTY_WEBADI_RESOURCES jut
10699: where jut.USER_SEQUENCE = v_user_sequence
10700: and jut.TRANS_ACCESS_CODE3 is not null
10701: and jut.TERR_RSC_ACCESS_ID3 is null
10702: and jut.header = v_header
10704: union all
10705: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10706: 4 qual_type_num, TERR_RSC_ACCESS_ID4 TERR_RSC_ACCESS_ID,
10707: TRANS_ACCESS_CODE4 TRANS_ACCESS_CODE
10708: FROM JTY_WEBADI_RESOURCES jut
10709: where jut.USER_SEQUENCE = v_user_sequence
10710: and jut.TRANS_ACCESS_CODE4 is not null
10711: and jut.TERR_RSC_ACCESS_ID4 is null
10712: and jut.header = v_header
10714: union all
10715: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10716: 5 qual_type_num, TERR_RSC_ACCESS_ID5 TERR_RSC_ACCESS_ID,
10717: TRANS_ACCESS_CODE5 TRANS_ACCESS_CODE
10718: FROM JTY_WEBADI_RESOURCES jut
10719: where jut.USER_SEQUENCE = v_user_sequence
10720: and jut.TRANS_ACCESS_CODE5 is not null
10721: and jut.TERR_RSC_ACCESS_ID5 is null
10722: and jut.header = v_header
10724: union all
10725: select user_sequence, TERR_RSC_ID,lay_seq_num, header,
10726: 6 qual_type_num, TERR_RSC_ACCESS_ID6 TERR_RSC_ACCESS_ID,
10727: TRANS_ACCESS_CODE6 TRANS_ACCESS_CODE
10728: FROM JTY_WEBADI_RESOURCES jut
10729: where jut.USER_SEQUENCE = v_user_sequence
10730: and jut.TRANS_ACCESS_CODE6 is not null
10731: and jut.TERR_RSC_ACCESS_ID6 is null
10732: and jut.header = v_header
10734: union all
10735: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10736: 7 qual_type_num, TERR_RSC_ACCESS_ID7 TERR_RSC_ACCESS_ID,
10737: TRANS_ACCESS_CODE7 TRANS_ACCESS_CODE
10738: FROM JTY_WEBADI_RESOURCES jut
10739: where jut.USER_SEQUENCE = v_user_sequence
10740: and jut.TRANS_ACCESS_CODE7 is not null
10741: and jut.TERR_RSC_ACCESS_ID7 is null
10742: and jut.header = v_header
10744: union all
10745: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10746: 8 qual_type_num, TERR_RSC_ACCESS_ID8 TERR_RSC_ACCESS_ID,
10747: TRANS_ACCESS_CODE8 TRANS_ACCESS_CODE
10748: FROM JTY_WEBADI_RESOURCES jut
10749: where jut.USER_SEQUENCE = v_user_sequence
10750: and jut.TRANS_ACCESS_CODE8 is not null
10751: and jut.TERR_RSC_ACCESS_ID8 is null
10752: and jut.header = v_header
10754: union all
10755: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10756: 9 qual_type_num, TERR_RSC_ACCESS_ID9 TERR_RSC_ACCESS_ID,
10757: TRANS_ACCESS_CODE9 TRANS_ACCESS_CODE
10758: FROM JTY_WEBADI_RESOURCES jut
10759: where jut.USER_SEQUENCE = v_user_sequence
10760: and jut.TRANS_ACCESS_CODE9 is not null
10761: and jut.TERR_RSC_ACCESS_ID9 is null
10762: and jut.header = v_header
10764: union all
10765: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10766: 10 qual_type_num, TERR_RSC_ACCESS_ID10 TERR_RSC_ACCESS_ID,
10767: TRANS_ACCESS_CODE10 TRANS_ACCESS_CODE
10768: FROM JTY_WEBADI_RESOURCES jut
10769: where jut.USER_SEQUENCE = v_user_sequence
10770: and jut.TRANS_ACCESS_CODE10 is not null
10771: and jut.TERR_RSC_ACCESS_ID10 is null
10772: and jut.header = v_header
10793: (
10794: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10795: 1 qual_type_num, TERR_RSC_ACCESS_ID1 TERR_RSC_ACCESS_ID,
10796: TRANS_ACCESS_CODE1 TRANS_ACCESS_CODE
10797: FROM JTY_WEBADI_RESOURCES jut
10798: where jut.USER_SEQUENCE = v_user_sequence
10799: and jut.TRANS_ACCESS_CODE1 is not null
10800: and jut.TERR_RSC_ACCESS_ID1 is not null
10801: and jut.header = v_header
10803: union all
10804: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10805: 2 qual_type_num, TERR_RSC_ACCESS_ID2 TERR_RSC_ACCESS_ID,
10806: TRANS_ACCESS_CODE2 TRANS_ACCESS_CODE
10807: FROM JTY_WEBADI_RESOURCES jut
10808: where jut.USER_SEQUENCE = v_user_sequence
10809: and jut.TRANS_ACCESS_CODE2 is not null
10810: and jut.TERR_RSC_ACCESS_ID2 is not null
10811: and jut.header = v_header
10813: union all
10814: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10815: 3 qual_type_num, TERR_RSC_ACCESS_ID3 TERR_RSC_ACCESS_ID,
10816: TRANS_ACCESS_CODE3 TRANS_ACCESS_CODE
10817: FROM JTY_WEBADI_RESOURCES jut
10818: where jut.USER_SEQUENCE = v_user_sequence
10819: and jut.TRANS_ACCESS_CODE3 is not null
10820: and jut.TERR_RSC_ACCESS_ID3 is not null
10821: and jut.header = v_header
10823: union all
10824: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10825: 4 qual_type_num, TERR_RSC_ACCESS_ID4 TERR_RSC_ACCESS_ID,
10826: TRANS_ACCESS_CODE4 TRANS_ACCESS_CODE
10827: FROM JTY_WEBADI_RESOURCES jut
10828: where jut.USER_SEQUENCE = v_user_sequence
10829: and jut.TRANS_ACCESS_CODE4 is not null
10830: and jut.TERR_RSC_ACCESS_ID4 is not null
10831: and jut.header = v_header
10833: union all
10834: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10835: 5 qual_type_num, TERR_RSC_ACCESS_ID5 TERR_RSC_ACCESS_ID,
10836: TRANS_ACCESS_CODE5 TRANS_ACCESS_CODE
10837: FROM JTY_WEBADI_RESOURCES jut
10838: where jut.USER_SEQUENCE = v_user_sequence
10839: and jut.TRANS_ACCESS_CODE5 is not null
10840: and jut.TERR_RSC_ACCESS_ID5 is not null
10841: and jut.header = v_header
10843: union all
10844: select user_sequence, TERR_RSC_ID,lay_seq_num, header,
10845: 6 qual_type_num, TERR_RSC_ACCESS_ID6 TERR_RSC_ACCESS_ID,
10846: TRANS_ACCESS_CODE6 TRANS_ACCESS_CODE
10847: FROM JTY_WEBADI_RESOURCES jut
10848: where jut.USER_SEQUENCE = v_user_sequence
10849: and jut.TRANS_ACCESS_CODE6 is not null
10850: and jut.TERR_RSC_ACCESS_ID6 is not null
10851: and jut.header = v_header
10853: union all
10854: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10855: 7 qual_type_num, TERR_RSC_ACCESS_ID7 TERR_RSC_ACCESS_ID,
10856: TRANS_ACCESS_CODE7 TRANS_ACCESS_CODE
10857: FROM JTY_WEBADI_RESOURCES jut
10858: where jut.USER_SEQUENCE = v_user_sequence
10859: and jut.TRANS_ACCESS_CODE7 is not null
10860: and jut.TERR_RSC_ACCESS_ID7 is not null
10861: and jut.header = v_header
10863: union all
10864: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10865: 8 qual_type_num, TERR_RSC_ACCESS_ID8 TERR_RSC_ACCESS_ID,
10866: TRANS_ACCESS_CODE8 TRANS_ACCESS_CODE
10867: FROM JTY_WEBADI_RESOURCES jut
10868: where jut.USER_SEQUENCE = v_user_sequence
10869: and jut.TRANS_ACCESS_CODE8 is not null
10870: and jut.TERR_RSC_ACCESS_ID8 is not null
10871: and jut.header = v_header
10873: union all
10874: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10875: 9 qual_type_num, TERR_RSC_ACCESS_ID9 TERR_RSC_ACCESS_ID,
10876: TRANS_ACCESS_CODE9 TRANS_ACCESS_CODE
10877: FROM JTY_WEBADI_RESOURCES jut
10878: where jut.USER_SEQUENCE = v_user_sequence
10879: and jut.TRANS_ACCESS_CODE9 is not null
10880: and jut.TERR_RSC_ACCESS_ID9 is not null
10881: and jut.header = v_header
10883: union all
10884: select user_sequence, TERR_RSC_ID, lay_seq_num, header,
10885: 10 qual_type_num, TERR_RSC_ACCESS_ID10 TERR_RSC_ACCESS_ID,
10886: TRANS_ACCESS_CODE10 TRANS_ACCESS_CODE
10887: FROM JTY_WEBADI_RESOURCES jut
10888: where jut.USER_SEQUENCE = v_user_sequence
10889: and jut.TRANS_ACCESS_CODE10 is not null
10890: and jut.TERR_RSC_ACCESS_ID10 is not null
10891: and jut.header = v_header
10967: v_action_flag varchar2) IS
10968: SELECT DISTINCT jwot.TERR_ID
10969: from
10970: JTY_WEBADI_OTH_TERR_INTF jwot,
10971: JTY_WEBADI_RESOURCES jwr,
10972: jtf_rs_resource_extns res
10973: where jwr.RESOURCE_ID = res.resource_id(+)
10974: and jwr.user_sequence = jwot.user_sequence
10975: and jwr.interface_type = jwot.interface_type
11042: --dbms_output.put_line(' # records processed for create: '||SQL%ROWCOUNT);
11043:
11044: if (l_get_terr_rsc_rec.terr_rsc_id.count > 0) then
11045: forall i in l_get_terr_rsc_rec.terr_rsc_id.first..l_get_terr_rsc_rec.terr_rsc_id.last
11046: update JTY_WEBADI_RESOURCES
11047: set terr_rsc_id = l_get_terr_rsc_rec.terr_rsc_id(i)
11048: where resource_id = l_get_terr_rsc_rec.resource_id(i)
11049: -- and terr_id = l_get_terr_rsc_rec.terr_id(i)
11050: and interface_type = l_intf_type
11162: forall i in l_rsc_access_rec.TERR_RSC_ID.first..l_rsc_access_rec.TERR_RSC_ID.last
11163: update JTY_WEBADI_OTH_TERR_INTF jwot
11164: set status = x_return_status
11165: where exists
11166: (select 1 from JTY_WEBADI_RESOURCES jwr
11167: where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
11168: and jwr.header = jwot.header
11169: and jwr.user_sequence = jwot.user_sequence
11170: and jwr.interface_type = jwot.interface_type
11187: update JTY_WEBADI_OTH_TERR_INTF jwot
11188: set status = x_return_status,
11189: error_msg = x_msg_data
11190: where exists
11191: (select 1 from JTY_WEBADI_RESOURCES jwr
11192: where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
11193: and jwr.header = jwot.header
11194: and jwr.user_sequence = jwot.user_sequence
11195: and jwr.interface_type = jwot.interface_type
11228: forall i in l_rsc_access_rec.TERR_RSC_ID.first..l_rsc_access_rec.TERR_RSC_ID.last
11229: update JTY_WEBADI_OTH_TERR_INTF jwot
11230: set status = x_return_status
11231: where exists
11232: (select 1 from JTY_WEBADI_RESOURCES jwr
11233: where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
11234: and jwr.header = jwot.header
11235: and jwr.user_sequence = jwot.user_sequence
11236: and jwr.interface_type = jwot.interface_type
11252: update JTY_WEBADI_OTH_TERR_INTF jwot
11253: set status = x_return_status,
11254: error_msg = x_msg_data
11255: where exists
11256: (select 1 from JTY_WEBADI_RESOURCES jwr
11257: where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
11258: and jwr.header = jwot.header
11259: and jwr.user_sequence = jwot.user_sequence
11260: and jwr.interface_type = jwot.interface_type
11275: update JTY_WEBADI_OTH_TERR_INTF jwot
11276: set status = x_return_status,
11277: error_msg = X_Msg_Data
11278: where exists
11279: (select 1 from JTY_WEBADI_RESOURCES jwr
11280: where jwr.LAY_SEQ_NUM = jwot.LAY_SEQ_NUM
11281: and jwr.header = jwot.header
11282: and jwr.user_sequence = jwot.user_sequence
11283: and jwr.interface_type = jwot.interface_type)