[Home] [Help]
53: --DATATYPE ID 6 = LOBS
54: --Cursor to get the SR attachemnts
55: CURSOR l_SRAtt_csr
56: IS
57: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
58: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
59: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
60: fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
61: fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
79: AND fndattdoc.entity_name = 'CS_INCIDENTS'
80: AND fnddoc.datatype_id in(1,5,6)
81: AND doctype.datatype_id = fnddoc.datatype_id
82: AND doctype.language = asg.language
83: AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
84: WHERE cflacc.document_id = fnddoc.document_id
85: AND cflacc.user_id = acc.user_id);
86:
87: --Cursor to get the task attachemnts
86:
87: --Cursor to get the task attachemnts
88: CURSOR l_TaskAtt_csr
89: IS
90: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
91: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
92: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
93: fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
94: fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
112: AND fndattdoc.entity_name = 'JTF_TASKS_B'
113: AND fnddoc.datatype_id in(1,5,6)
114: AND doctype.datatype_id = fnddoc.datatype_id
115: AND doctype.language = asg.language
116: AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
117: WHERE cflacc.document_id = fnddoc.document_id
118: AND cflacc.user_id = acc.user_id);
119:
120: --Bug 5726888
119:
120: --Bug 5726888
121: --Cursor to get the Signature attachemnts
122: CURSOR c_Signature IS
123: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
124: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
125: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
126: fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
127: fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
145: AND doctype.datatype_id = fnddoc.datatype_id
146: AND doctype.language = asg.language
147: AND acc.user_id = asg.user_id
148: AND asg.user_id=asg.owner_id
149: AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
150: WHERE cflacc.document_id = fnddoc.document_id
151: AND cflacc.user_id = acc.user_id);
152:
153: CURSOR c_ha_parent_pld_id(b_pk_value NUMBER)
252: );
253: END IF;
254:
255: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
256: INSERT INTO CSM_FND_LOBS_ACC
257: (access_id, file_id, user_id, name, description,
258: language, entity_name, pk1_value, pk2_value, seq_num,
259: update_user, category, usage_type, data_type, counter,
260: last_update_date, last_updated_by, creation_date,created_by, data_type_id,
331: );
332: END IF;
333: --insert the rows into access table
334: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
335: INSERT INTO CSM_FND_LOBS_ACC
336: (access_id, file_id, user_id, name, description,
337: language, entity_name, pk1_value, pk2_value, seq_num,
338: update_user, category, usage_type, data_type, counter,
339: last_update_date, last_updated_by, creation_date,created_by, data_type_id,
411: );
412: END IF;
413: --insert the rows into access table
414: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
415: INSERT INTO CSM_FND_LOBS_ACC
416: (access_id, file_id, user_id, name, description,
417: language, entity_name, pk1_value, pk2_value, seq_num,
418: update_user, category, usage_type, data_type, counter,
419: last_update_date, last_updated_by, creation_date,created_by, data_type_id,
478: PROCEDURE INSERT_ALL_ACC_RECORDS (p_user_id IN NUMBER )
479: IS
480: --Cursor to get the SR attachemnts
481: CURSOR c_SRAtt (b_user_id NUMBER) IS
482: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
483: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
484: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
485: fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
486: fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
507: AND acc.user_id = b_user_id;
508:
509: --Cursor to get the task attachemnts
510: CURSOR c_TaskAtt (b_user_id NUMBER) IS
511: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
512: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
513: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
514: fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
515: fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
536: AND acc.user_id = b_user_id;
537:
538: --Cursor to get the Signature attachemnts
539: CURSOR c_Signature (b_user_id NUMBER) IS
540: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
541: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
542: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
543: fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
544: fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
599: );
600: END IF;
601: --Empty the access table for the user before insert it freshly
602: --bug 5213097
603: DELETE FROM CSM_FND_LOBS_ACC WHERE user_id = p_user_id;
604:
605: --SIGNATURE
606: l_tab_access_id.delete;
607: l_tab_user_id.delete;
638: );
639: END IF;
640:
641: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
642: INSERT INTO CSM_FND_LOBS_ACC
643: (access_id, file_id, user_id, name, description,
644: language, entity_name, pk1_value, pk2_value, seq_num,
645: update_user, category, usage_type, data_type, counter,
646: last_update_date, last_updated_by, creation_date,created_by, data_type_id,
721: );
722: END IF;
723:
724: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
725: INSERT INTO CSM_FND_LOBS_ACC
726: (access_id, file_id, user_id, name, description,
727: language, entity_name, pk1_value, pk2_value, seq_num,
728: update_user, category, usage_type, data_type, counter,
729: last_update_date, last_updated_by, creation_date,created_by, data_type_id,
789: );
790: END IF;
791:
792: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
793: INSERT INTO CSM_FND_LOBS_ACC
794: (access_id, file_id, user_id, name, description,
795: language, entity_name, pk1_value, pk2_value, seq_num,
796: update_user, category, usage_type, data_type, counter,
797: last_update_date, last_updated_by, creation_date,created_by, data_type_id,
855:
856: CURSOR l_signature_csr(b_task_assignment_id IN NUMBER, b_user_id IN NUMBER,
857: b_lang IN VARCHAR)
858: IS
859: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
860: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
861: fndatt.entity_name, fndatt.pk1_value, fndatt.pk2_value,
862: fndatt.seq_num,asg.user_name,fnddoccat_tl.user_name,
863: fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
883: AND fnddoccat_tl.name = 'SIGNATURE'
884: AND fnddoc.datatype_id = 6
885: AND doctype.datatype_id = fnddoc.datatype_id
886: AND doctype.language = asg.language
887: AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
888: WHERE cflacc.document_id = fnddoc.document_id
889: AND cflacc.user_id = acc.user_id);
890:
891: -- get SR attachments
891: -- get SR attachments
892: CURSOR l_SRAtt_csr (b_task_assignment_id IN NUMBER, b_user_id IN NUMBER,
893: b_lang VARCHAR)
894: IS
895: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
896: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
897: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
898: fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
899: fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
922: AND fndattdoc.entity_name = 'CS_INCIDENTS'
923: AND fnddoc.datatype_id in (1,5,6)
924: AND doctype.datatype_id = fnddoc.datatype_id
925: AND doctype.language = asg.language
926: AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
927: WHERE cflacc.document_id = fnddoc.document_id
928: AND cflacc.user_id = acc.user_id);
929:
930: --get the task attachemnts
930: --get the task attachemnts
931: CURSOR l_TaskAtt_csr (b_task_assignment_id IN NUMBER, b_user_id IN NUMBER,
932: b_lang IN VARCHAR)
933: IS
934: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
935: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
936: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
937: fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
938: fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
959: AND fndattdoc.entity_name = 'JTF_TASKS_B'
960: AND fnddoc.datatype_id in (1,5,6)
961: AND doctype.datatype_id = fnddoc.datatype_id
962: AND doctype.language = asg.language
963: AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
964: WHERE cflacc.document_id = fnddoc.document_id
965: AND cflacc.user_id = acc.user_id);
966:
967: CURSOR l_user_csr(b_user_id IN NUMBER)
1060: );
1061: END IF;
1062:
1063: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1064: INSERT INTO CSM_FND_LOBS_ACC
1065: (access_id, file_id, user_id, name, description,
1066: language, entity_name, pk1_value, pk2_value, seq_num,
1067: update_user, category, usage_type, data_type, counter,
1068: last_update_date, last_updated_by, creation_date,created_by, data_type_id,
1145: );
1146: END IF;
1147:
1148: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1149: INSERT INTO CSM_FND_LOBS_ACC
1150: (access_id, file_id, user_id, name, description,
1151: language, entity_name, pk1_value, pk2_value, seq_num,
1152: update_user, category, usage_type, data_type, counter,
1153: last_update_date, last_updated_by, creation_date,created_by, data_type_id,
1207: );
1208: END IF;
1209:
1210: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1211: INSERT INTO CSM_FND_LOBS_ACC
1212: (access_id, file_id, user_id, name, description,
1213: language, entity_name, pk1_value, pk2_value, seq_num,
1214: update_user, category, usage_type, data_type, counter,
1215: last_update_date, last_updated_by, creation_date,created_by, data_type_id,
1255: --Cursor for signature
1256: CURSOR l_signature_csr(b_task_assignment_id IN number, b_user_id IN number)
1257: IS
1258: SELECT acc.access_id, acc.user_id
1259: FROM csm_fnd_lobs_acc acc,
1260: csf_debrief_headers dbh
1261: WHERE acc.user_id = b_user_id
1262: AND acc.entity_name = 'CSF_DEBRIEF_HEADERS'
1263: AND dbh.task_assignment_id = b_task_assignment_id
1266: --Cursor for SR attachments
1267: CURSOR c_SRAtt ( b_task_assignment_id IN NUMBER,
1268: b_user_id IN NUMBER) IS
1269: SELECT acc.access_id, acc.user_id
1270: FROM csm_fnd_lobs_acc acc,
1271: jtf_task_assignments jta,
1272: jtf_tasks_b jt
1273: WHERE acc.user_id = b_user_id
1274: AND acc.entity_name = 'CS_INCIDENTS'
1279: --Cursor for Task attachments
1280: CURSOR l_TaskAtt_csr ( b_task_assignment_id IN NUMBER,
1281: b_user_id IN NUMBER) IS
1282: SELECT acc.access_id, acc.user_id
1283: FROM csm_fnd_lobs_acc acc,
1284: jtf_task_assignments jta
1285: WHERE acc.user_id = b_user_id
1286: AND acc.entity_name = 'JTF_TASKS_B'
1287: AND jta.task_assignment_id = b_task_assignment_id
1349: );
1350: END IF;
1351:
1352: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1353: DELETE FROM CSM_FND_LOBS_ACC WHERE ACCESS_ID = l_tab_access_id(i);
1354:
1355: l_dummy := asg_download.mark_dirty(
1356: P_PUB_ITEM => l_publicationitemname
1357: , P_ACCESSLIST => l_tab_access_id
1386: );
1387: END IF;
1388:
1389: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1390: DELETE FROM CSM_FND_LOBS_ACC WHERE ACCESS_ID = l_tab_access_id(i);
1391:
1392: l_dummy := asg_download.mark_dirty(
1393: P_PUB_ITEM => l_publicationitemname
1394: , P_ACCESSLIST => l_tab_access_id
1425: );
1426: END IF;
1427:
1428: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1429: DELETE FROM CSM_FND_LOBS_ACC WHERE ACCESS_ID = l_tab_access_id(i);
1430:
1431: l_dummy := asg_download.mark_dirty(
1432: P_PUB_ITEM => l_publicationitemname
1433: , P_ACCESSLIST => l_tab_access_id
1473: --for this is the file id sent from client may not be proper and so we allow the server to generate the file id
1474: CURSOR l_SRAtt_csr (b_incident_id IN NUMBER, b_user_id IN NUMBER,
1475: b_data_typeid IN NUMBER)
1476: IS
1477: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
1478: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
1479: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
1480: fndattdoc.seq_num, asg.user_name, fnddoccat_tl.user_name,
1481: fnddoc.usage_type, doctype.user_name, fnddoc.datatype_id,
1501: AND doctype.datatype_id = fnddoc.datatype_id
1502: AND doctype.language = asg.language
1503: AND acc.user_id = b_user_id
1504: AND NOT EXISTS (SELECT 1
1505: FROM CSM_FND_LOBS_ACC cflacc
1506: WHERE cflacc.document_id = fnddoc.document_id
1507: AND cflacc.user_id = acc.user_id);
1508:
1509: --Cursor to get the task attachemnts
1509: --Cursor to get the task attachemnts
1510: CURSOR l_TaskAtt_csr(b_task_id IN NUMBER, b_user_id IN NUMBER,
1511: b_data_typeid IN NUMBER)
1512: IS
1513: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
1514: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
1515: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
1516: fndattdoc.seq_num, asg.user_name, fnddoccat_tl.user_name,
1517: fnddoc.usage_type, doctype.user_name, fnddoc.datatype_id,
1537: AND doctype.datatype_id = fnddoc.datatype_id
1538: AND doctype.language = asg.language
1539: AND acc.user_id = b_user_id
1540: AND NOT EXISTS (SELECT 1
1541: FROM CSM_FND_LOBS_ACC cflacc
1542: WHERE cflacc.document_id = fnddoc.document_id
1543: AND cflacc.user_id = acc.user_id);
1544:
1545: --Bug 5726888
1545: --Bug 5726888
1546: --Cursor to get the Signature attachemnts
1547: CURSOR c_Signature (b_debrief_id IN NUMBER, b_user_id IN NUMBER,
1548: b_data_typeid IN NUMBER) IS
1549: SELECT csm_fnd_lobs_acc_s.NEXTVAL, fnddoc.media_id, acc.user_id,
1550: fnddoccat_tl.name, fnddoc_tl.description, fnddoc_tl.language,
1551: fndattdoc.entity_name, fndattdoc.pk1_value, fndattdoc.pk2_value,
1552: fndattdoc.seq_num,asg.user_name,fnddoccat_tl.user_name,
1553: fnddoc.usage_type,doctype.user_name,fnddoc.datatype_id,
1570: AND doctype.datatype_id = fnddoc.datatype_id
1571: AND doctype.language = asg.language
1572: AND acc.user_id = asg.user_id
1573: AND acc.user_id=b_user_id
1574: AND NOT EXISTS (SELECT 1 FROM CSM_FND_LOBS_ACC cflacc
1575: WHERE cflacc.document_id = fnddoc.document_id
1576: AND cflacc.user_id = acc.user_id);
1577:
1578: TYPE Number_TAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1708: );
1709: END IF;
1710:
1711: FORALL i in l_tab_access_id.FIRST .. l_tab_access_id.LAST
1712: INSERT INTO CSM_FND_LOBS_ACC
1713: (access_id, file_id, user_id, name, description,
1714: language, entity_name, pk1_value, pk2_value, seq_num,
1715: update_user, category, usage_type, data_type, counter,
1716: last_update_date, last_updated_by, creation_date,created_by, data_type_id,