[Home] [Help]
1003: raise;
1004: End;
1005:
1006: l_progress := 'PO_COMMUNICATION_PVT.Start_Email_WF_Process : Query the Zip blob';
1007: --Bug #4865352 - Added a join with fnd_documents and selected media_id from it
1008:
1009:
1010: IF p_document_type in ('PO', 'PA') THEN
1011: l_entity_name := 'PO_HEAD';
1017: SELECT count(*)
1018: INTO l_zip_exists
1019: FROM fnd_lobs fl,
1020: fnd_attached_documents fad,
1021: fnd_documents fd,
1022: fnd_documents_tl fdl
1023: WHERE fad.pk1_value = to_char(p_document_id)
1024: and fad.pk2_value = to_char(p_revision_num)
1025: and fad.entity_name = l_entity_name
1018: INTO l_zip_exists
1019: FROM fnd_lobs fl,
1020: fnd_attached_documents fad,
1021: fnd_documents fd,
1022: fnd_documents_tl fdl
1023: WHERE fad.pk1_value = to_char(p_document_id)
1024: and fad.pk2_value = to_char(p_revision_num)
1025: and fad.entity_name = l_entity_name
1026: and fdl.document_id = fad.document_id
1360: -- bug4931216
1361: -- Join directly to the base table to improve performance
1362: cursor l_get_po_attachments_csr(l_po_header_id number) is
1363: select fl.file_name, dbms_lob.getlength(fl.file_data)
1364: from fnd_documents d,
1365: fnd_attached_documents ad,
1366: fnd_doc_category_usages dcu,
1367: fnd_attachment_functions af,
1368: fnd_lobs fl
1409: -- bug4931216
1410: -- Join directly to the base table to improve performance
1411: cursor l_get_release_attachments_csr(l_po_release_id number) is
1412: select fl.file_name, dbms_lob.getlength(fl.file_data)
1413: from fnd_documents d,
1414: fnd_attached_documents ad,
1415: fnd_doc_category_usages dcu,
1416: fnd_attachment_functions af,
1417: fnd_lobs fl
1623: select 'Y' into l_duplicate_filenames from dual
1624: where exists
1625: (
1626: select fl.file_name
1627: from fnd_documents d,
1628: fnd_attached_documents ad,
1629: fnd_doc_category_usages dcu,
1630: fnd_attachment_functions af,
1631: fnd_lobs fl
1686: select 'Y' into l_duplicate_filenames from dual
1687: where exists
1688: (
1689: select fl.file_name
1690: from fnd_documents d,
1691: fnd_attached_documents ad,
1692: fnd_doc_category_usages dcu,
1693: fnd_attachment_functions af,
1694: fnd_lobs fl
2660: l_request_id number := NULL;
2661:
2662: l_progress VARCHAR2(3);
2663: l_entity_name fnd_attached_documents.entity_name%type;
2664: l_buyer_language_code fnd_documents_tl.language%type;
2665: l_pdf_file_name fnd_lobs.file_name%type; --
2666:
2667: BEGIN
2668: l_progress := '000';
2760: --
2761: -- Brought the call out of the select
2762: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype, '_TERMS_', l_org_id, p_document_id,
2763: l_revision_num, l_buyer_language_code);
2764: --Bug #4865352 - Replaced fnd_documents_tl with fnd_documents_vl
2765: select count(1) into l_pdf_tc_buyer_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents_vl fdl
2766: where
2767: fad.pk2_value = TO_CHAR(p_document_id) and
2768: fad.pk3_value = TO_CHAR(l_revision_num) and
2761: -- Brought the call out of the select
2762: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype, '_TERMS_', l_org_id, p_document_id,
2763: l_revision_num, l_buyer_language_code);
2764: --Bug #4865352 - Replaced fnd_documents_tl with fnd_documents_vl
2765: select count(1) into l_pdf_tc_buyer_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents_vl fdl
2766: where
2767: fad.pk2_value = TO_CHAR(p_document_id) and
2768: fad.pk3_value = TO_CHAR(l_revision_num) and
2769: fad.entity_name = 'OKC_CONTRACT_DOCS' and
2787: END IF;
2788:
2789: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype, '_TERMS_', l_org_id, p_document_id,
2790: l_revision_num, l_buyer_language_code); --bug#3463617
2791: --Bug #4865352 - Added a join with fnd_documents
2792: select count(1) into l_pdf_tc_sup_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents fd, fnd_documents_tl fdl
2793: where
2794: fad.pk1_value = TO_CHAR(p_document_id) and
2795: fad.pk2_value = TO_CHAR(l_revision_num) and
2788:
2789: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype, '_TERMS_', l_org_id, p_document_id,
2790: l_revision_num, l_buyer_language_code); --bug#3463617
2791: --Bug #4865352 - Added a join with fnd_documents
2792: select count(1) into l_pdf_tc_sup_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents fd, fnd_documents_tl fdl
2793: where
2794: fad.pk1_value = TO_CHAR(p_document_id) and
2795: fad.pk2_value = TO_CHAR(l_revision_num) and
2796: fad.entity_name = l_entity_name and
2821: -- Brought the call out of the select
2822: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype, '_TERMS_', l_org_id, p_document_id,
2823: l_revision_num, l_buyer_language_code);
2824: --Bug #4865352
2825: select count(1) into l_pdf_nt_buyer_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents_vl fdl
2826: where
2827: fad.pk1_value = TO_CHAR(p_document_id) and
2828: fad.pk2_value = TO_CHAR(l_revision_num) and
2829: fad.entity_name = l_entity_name and
2847: --
2848: -- Brought the call out of the select
2849: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype, '_TERMS_', l_org_id, p_document_id,
2850: l_revision_num, l_language_code);
2851: --Bug #4865352 - Added a join with fnd_documents
2852: select count(1) into l_pdf_nt_sup_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents fd, fnd_documents_tl fdl
2853: where
2854: fad.pk1_value = TO_CHAR(p_document_id) and
2855: fad.pk2_value = TO_CHAR(l_revision_num) and
2848: -- Brought the call out of the select
2849: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype, '_TERMS_', l_org_id, p_document_id,
2850: l_revision_num, l_language_code);
2851: --Bug #4865352 - Added a join with fnd_documents
2852: select count(1) into l_pdf_nt_sup_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents fd, fnd_documents_tl fdl
2853: where
2854: fad.pk1_value = TO_CHAR(p_document_id) and
2855: fad.pk2_value = TO_CHAR(l_revision_num) and
2856: fad.entity_name = l_entity_name and
3326: -- For PDF preventing duplicate records is taken care of by explicitly deleting
3327: -- them before generating it everytime. For ZIP, it will be done by returning
3328: -- the same file id as the existing one so that an explicit delete would not be
3329: -- necessary
3330: --Bug #4865352 - Replaced fnd_documents_tl with fnd_documents_vl
3331: IF p_blob_type = 'ZIP' THEN
3332: Begin
3333: SELECT fdl.media_id
3334: INTO x_media_id
3332: Begin
3333: SELECT fdl.media_id
3334: INTO x_media_id
3335: FROM fnd_attached_documents fad,
3336: fnd_documents_vl fdl
3337: WHERE fad.pk1_value = to_char(p_document_id)
3338: and fad.pk2_value = to_char(p_revision_number)
3339: and nvl(fad.pk3_value, '*') = nvl(l_draft_id, '*') -- CLM
3340: and fad.entity_name = l_entity_name
3361: SELECT category_id into l_category_id from fnd_document_categories
3362: where name = 'CUSTOM2446' ;
3363:
3364: l_progress := '006';
3365: FND_DOCUMENTS_PKG.Insert_Row(
3366: row_id_tmp,
3367: document_id_tmp,
3368: SYSDATE,
3369: 1, --NVL(X_created_by,0),
3594: if l_document_type in ('PO', 'PA') then
3595: l_entity_name := 'PO_HEAD';
3596: end if;
3597:
3598: --Bug #4865352 - Added join with fnd_documents and selected media_id from it
3599: --Bug #5232999 - Added file name as criterion so that we get only the PDF
3600: /* SELECT file_data into l_document
3601: FROM fnd_lobs fl,
3602: fnd_attached_documents fad,
3599: --Bug #5232999 - Added file name as criterion so that we get only the PDF
3600: /* SELECT file_data into l_document
3601: FROM fnd_lobs fl,
3602: fnd_attached_documents fad,
3603: fnd_documents fd,
3604: fnd_documents_tl fdl
3605: WHERE fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
3606: and fdl.document_id = fad.document_id and fdl.document_id = fd.document_id and fd.media_id = fl.file_id
3607: and fad.entity_name = l_entity_name and fdl.language = l_language
3600: /* SELECT file_data into l_document
3601: FROM fnd_lobs fl,
3602: fnd_attached_documents fad,
3603: fnd_documents fd,
3604: fnd_documents_tl fdl
3605: WHERE fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
3606: and fdl.document_id = fad.document_id and fdl.document_id = fd.document_id and fd.media_id = fl.file_id
3607: and fad.entity_name = l_entity_name and fdl.language = l_language
3608: and fl.file_name = l_filename ;*/
3612: BEGIN
3613: SELECT file_data into l_document
3614: FROM fnd_lobs fl,
3615: fnd_attached_documents fad,
3616: fnd_documents fd,
3617: fnd_documents_tl fdl
3618: WHERE fad.pk1_value = to_char(l_document_id)
3619: and fad.pk2_value = to_char(l_revision_number)
3620: and fdl.document_id = fad.document_id
3613: SELECT file_data into l_document
3614: FROM fnd_lobs fl,
3615: fnd_attached_documents fad,
3616: fnd_documents fd,
3617: fnd_documents_tl fdl
3618: WHERE fad.pk1_value = to_char(l_document_id)
3619: and fad.pk2_value = to_char(l_revision_number)
3620: and fdl.document_id = fad.document_id
3621: and fdl.document_id = fd.document_id
3636:
3637: SELECT file_data into l_document
3638: FROM fnd_lobs fl,
3639: fnd_attached_documents fad,
3640: fnd_documents fd,
3641: fnd_documents_tl fdl
3642: WHERE fad.pk2_value = to_char(l_document_id)
3643: and fad.pk3_value = to_char(l_revision_number)
3644: and fdl.document_id = fad.document_id
3637: SELECT file_data into l_document
3638: FROM fnd_lobs fl,
3639: fnd_attached_documents fad,
3640: fnd_documents fd,
3641: fnd_documents_tl fdl
3642: WHERE fad.pk2_value = to_char(l_document_id)
3643: and fad.pk3_value = to_char(l_revision_number)
3644: and fdl.document_id = fad.document_id
3645: and fdl.document_id = fd.document_id
3735: l_filename := po_communication_pvt.getPDFFileName(l_document_type, '_TERMS_', l_org_id, l_document_id, l_revision_number, l_language);
3736: ELSE
3737: l_filename := po_communication_pvt.getPDFFileName(l_document_type, '_', l_org_id, l_document_id, l_revision_number, l_language);
3738: END IF;
3739: --Bug #4865352 - Added join with fnd_documents and selected media_id from it
3740: -- Bug 4047688
3741: -- Added join condition on file name of PDF
3742: IF l_withTerms = 'Y' AND l_document_type in ('PO', 'PA') THEN
3743: SELECT file_data into l_document
3742: IF l_withTerms = 'Y' AND l_document_type in ('PO', 'PA') THEN
3743: SELECT file_data into l_document
3744: FROM fnd_lobs fl,
3745: fnd_attached_documents fad,
3746: fnd_documents fd,
3747: fnd_documents_tl fdl
3748: WHERE fad.pk2_value = to_char(l_document_id) and fad.pk3_value = to_char(l_revision_number)
3749: and fdl.document_id = fad.document_id and fdl.document_id = fd.document_id and fd.media_id = fl.file_id
3750: and fad.entity_name = 'OKC_CONTRACT_DOCS' and fdl.language = l_language
3743: SELECT file_data into l_document
3744: FROM fnd_lobs fl,
3745: fnd_attached_documents fad,
3746: fnd_documents fd,
3747: fnd_documents_tl fdl
3748: WHERE fad.pk2_value = to_char(l_document_id) and fad.pk3_value = to_char(l_revision_number)
3749: and fdl.document_id = fad.document_id and fdl.document_id = fd.document_id and fd.media_id = fl.file_id
3750: and fad.entity_name = 'OKC_CONTRACT_DOCS' and fdl.language = l_language
3751: and fl.file_name = l_filename
3756: l_entity_name := 'PO_HEAD';
3757: ELSIF l_document_type = 'RELEASE' THEN
3758: l_entity_name := 'PO_REL';
3759: END IF;
3760: --Bug #4865352 - Added a join with fnd_documents
3761: IF l_document_type in ('PO', 'PA', 'RELEASE') AND l_withTerms = 'N' THEN
3762: SELECT file_data into l_document
3763: FROM fnd_lobs fl,
3764: fnd_attached_documents fad,
3761: IF l_document_type in ('PO', 'PA', 'RELEASE') AND l_withTerms = 'N' THEN
3762: SELECT file_data into l_document
3763: FROM fnd_lobs fl,
3764: fnd_attached_documents fad,
3765: fnd_documents fd,
3766: fnd_documents_tl fdl
3767: WHERE fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
3768: and fdl.document_id = fad.document_id and fd.media_id = fl.file_id
3769: and fd.document_id = fdl.document_id
3762: SELECT file_data into l_document
3763: FROM fnd_lobs fl,
3764: fnd_attached_documents fad,
3765: fnd_documents fd,
3766: fnd_documents_tl fdl
3767: WHERE fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
3768: and fdl.document_id = fad.document_id and fd.media_id = fl.file_id
3769: and fd.document_id = fdl.document_id
3770: and fad.entity_name = l_entity_name and fl.file_name = l_filename and fdl.language = l_language and rownum = 1;
3907: end if;
3908: /*Begin Edit Akyanama Bug # 13242469 */
3909:
3910: /*IF l_withTerms = 'Y' THEN
3911: --Bug #4865352 - Replaced fnd_documents_tl with fnd_documents_vl
3912: -- Bug 4047688
3913: -- Appended join condition on file name of document to prevent return of multiple rows
3914: SELECT file_data into l_document
3915: FROM fnd_lobs fl,
3913: -- Appended join condition on file name of document to prevent return of multiple rows
3914: SELECT file_data into l_document
3915: FROM fnd_lobs fl,
3916: fnd_attached_documents fad,
3917: fnd_documents_vl fdl
3918: --Begin Edit Akyanama Bug # 13242469
3919: --Changed the below line
3920: From : fad.pk2_value = to_char(l_document_id) and fad.pk3_value = to_char(l_revision_number)
3921: To : fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
3930:
3931: END IF;
3932:
3933: if l_document_type in ('PO', 'PA', 'RELEASE') and l_withTerms = 'N' then
3934: --Bug #4865352 - Added a join with fnd_documents
3935: SELECT file_data into l_document
3936: FROM fnd_lobs fl,
3937: fnd_attached_documents fad,
3938: fnd_documents fd,
3934: --Bug #4865352 - Added a join with fnd_documents
3935: SELECT file_data into l_document
3936: FROM fnd_lobs fl,
3937: fnd_attached_documents fad,
3938: fnd_documents fd,
3939: fnd_documents_tl fdl
3940: WHERE fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
3941: and fdl.document_id = fad.document_id and fd.media_id = fl.file_id and fd.document_id = fdl.document_id
3942: and fad.entity_name = l_entity_name and fl.file_name = l_filename and fdl.language = l_language; */
3935: SELECT file_data into l_document
3936: FROM fnd_lobs fl,
3937: fnd_attached_documents fad,
3938: fnd_documents fd,
3939: fnd_documents_tl fdl
3940: WHERE fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
3941: and fdl.document_id = fad.document_id and fd.media_id = fl.file_id and fd.document_id = fdl.document_id
3942: and fad.entity_name = l_entity_name and fl.file_name = l_filename and fdl.language = l_language; */
3943:
3948: BEGIN
3949: SELECT file_data into l_document
3950: FROM fnd_lobs fl,
3951: fnd_attached_documents fad,
3952: fnd_documents fd,
3953: fnd_documents_tl fdl
3954: WHERE fad.pk1_value = to_char(l_document_id)
3955: and fad.pk2_value = to_char(l_revision_number)
3956: and fdl.document_id = fad.document_id
3949: SELECT file_data into l_document
3950: FROM fnd_lobs fl,
3951: fnd_attached_documents fad,
3952: fnd_documents fd,
3953: fnd_documents_tl fdl
3954: WHERE fad.pk1_value = to_char(l_document_id)
3955: and fad.pk2_value = to_char(l_revision_number)
3956: and fdl.document_id = fad.document_id
3957: and fdl.document_id = fd.document_id
3972:
3973: SELECT file_data into l_document
3974: FROM fnd_lobs fl,
3975: fnd_attached_documents fad,
3976: fnd_documents fd,
3977: fnd_documents_tl fdl
3978: WHERE fad.pk2_value = to_char(l_document_id)
3979: and fad.pk3_value = to_char(l_revision_number)
3980: and fdl.document_id = fad.document_id
3973: SELECT file_data into l_document
3974: FROM fnd_lobs fl,
3975: fnd_attached_documents fad,
3976: fnd_documents fd,
3977: fnd_documents_tl fdl
3978: WHERE fad.pk2_value = to_char(l_document_id)
3979: and fad.pk3_value = to_char(l_revision_number)
3980: and fdl.document_id = fad.document_id
3981: and fdl.document_id = fd.document_id
4295: raise;
4296: End;
4297:
4298: l_progress := 'PO_COMMUNICATION_PVT.zip_attach : Query the Zip blob';
4299: --Bug #4865352 - Added a join with fnd_documents and selected media_id from it
4300: Begin
4301: SELECT fl.file_data, fl.file_content_type
4302: INTO l_document, l_filecontent_type
4303: FROM fnd_lobs fl,
4301: SELECT fl.file_data, fl.file_content_type
4302: INTO l_document, l_filecontent_type
4303: FROM fnd_lobs fl,
4304: fnd_attached_documents fad,
4305: fnd_documents fd,
4306: fnd_documents_tl fdl
4307: WHERE fad.pk1_value = to_char(l_document_id)
4308: and fad.pk2_value = to_char(l_revision_number)
4309: and fad.entity_name = l_entity_name
4302: INTO l_document, l_filecontent_type
4303: FROM fnd_lobs fl,
4304: fnd_attached_documents fad,
4305: fnd_documents fd,
4306: fnd_documents_tl fdl
4307: WHERE fad.pk1_value = to_char(l_document_id)
4308: and fad.pk2_value = to_char(l_revision_number)
4309: and fad.entity_name = l_entity_name
4310: and fdl.document_id = fad.document_id
4525: select 'Y' into l_attachments_exist from dual
4526: where exists
4527: (
4528: select fl.file_name
4529: from fnd_documents d,
4530: fnd_attached_documents ad,
4531: fnd_doc_category_usages dcu,
4532: fnd_attachment_functions af,
4533: fnd_lobs fl
4545:
4546: UNION ALL
4547:
4548: select fl.file_name
4549: from fnd_documents d,
4550: fnd_attached_documents ad,
4551: fnd_doc_category_usages dcu,
4552: fnd_attachment_functions af,
4553: fnd_lobs fl
4563:
4564: UNION ALL
4565:
4566: select fl.file_name
4567: from fnd_documents d,
4568: fnd_attached_documents ad,
4569: fnd_doc_category_usages dcu,
4570: fnd_attachment_functions af,
4571: fnd_lobs fl
4585:
4586: Union ALL
4587:
4588: select fl.file_name
4589: from fnd_documents d,
4590: fnd_attached_documents ad,
4591: fnd_doc_category_usages dcu,
4592: fnd_attachment_functions af,
4593: fnd_lobs fl
4606:
4607: union all
4608:
4609: select fl.file_name
4610: from fnd_documents d,
4611: fnd_attached_documents ad,
4612: fnd_doc_category_usages dcu,
4613: fnd_attachment_functions af,
4614: fnd_lobs fl
4627:
4628: union all
4629: /*bug 13528070*/
4630: select /*+ leading(AD) */ fl.file_name
4631: from fnd_documents d,
4632: fnd_attached_documents ad,
4633: fnd_doc_category_usages dcu,
4634: fnd_attachment_functions af,
4635: fnd_lobs fl
4659: select 'Y' into l_attachments_exist from dual
4660: where exists
4661: (
4662: select fl.file_name
4663: from fnd_documents d,
4664: fnd_attached_documents ad,
4665: fnd_doc_category_usages dcu,
4666: fnd_attachment_functions af,
4667: fnd_lobs fl
4676:
4677: Union all
4678:
4679: select fl.file_name
4680: from fnd_documents d,
4681: fnd_attached_documents ad,
4682: fnd_doc_category_usages dcu,
4683: fnd_attachment_functions af,
4684: fnd_lobs fl
4696:
4697: Union all
4698:
4699: select fl.file_name
4700: from fnd_documents d,
4701: fnd_attached_documents ad,
4702: fnd_doc_category_usages dcu,
4703: fnd_attachment_functions af,
4704: fnd_lobs fl
4717:
4718: Union ALL
4719:
4720: select fl.file_name
4721: from fnd_documents d,
4722: fnd_attached_documents ad,
4723: fnd_doc_category_usages dcu,
4724: fnd_attachment_functions af,
4725: fnd_lobs fl
4739:
4740: Union ALL
4741:
4742: select fl.file_name
4743: from fnd_documents d,
4744: fnd_attached_documents ad,
4745: fnd_doc_category_usages dcu,
4746: fnd_attachment_functions af,
4747: fnd_lobs fl
4761:
4762: Union all
4763:
4764: select fl.file_name
4765: from fnd_documents d,
4766: fnd_attached_documents ad,
4767: fnd_doc_category_usages dcu,
4768: fnd_attachment_functions af,
4769: fnd_lobs fl
4783:
4784: Union ALL
4785: /*bug 13528070*/
4786: select /*+ leading(AD) */ fl.file_name
4787: from fnd_documents d,
4788: fnd_attached_documents ad,
4789: fnd_doc_category_usages dcu,
4790: fnd_attachment_functions af,
4791: fnd_lobs fl
5243: --bug6133951 added seq_num in order_by clause.
5244: l_head_short_attachment_query := 'CURSOR( SELECT fds.short_text
5245: FROM
5246: fnd_attached_docs_form_vl fad,
5247: fnd_documents_short_text fds
5248: WHERE ((entity_name=''PO_HEADERS'' AND
5249: pk1_value=to_char(phx.po_header_id))OR
5250: (entity_name = ''PO_RELEASES'' AND
5251: pk1_value = to_char(phx.po_release_id)) OR
5290: --bug6133951 added seq_num in order_by clause.
5291: l_head_short_attachment_query := 'CURSOR( SELECT fds.short_text
5292: FROM
5293: fnd_attached_docs_form_vl fad,
5294: fnd_documents_short_text fds
5295: WHERE ((entity_name = ''PO_HEADERS'' AND
5296: pk1_value = to_char(phx.po_header_id)) OR
5297: (entity_name = ''PO_VENDORS'' AND
5298: pk1_value = to_char(phx.vendor_id)) OR
5347:
5348: l_line_short_attachment_query := ' CURSOR( SELECT plx.po_line_id , fds.short_text
5349: FROM
5350: fnd_attached_docs_form_vl fad,
5351: fnd_documents_short_text fds
5352: WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
5353: OR
5354: (fad.entity_name=''MTL_SYSTEM_ITEMS'' AND
5355: fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND --Bug6139548
5436: --short_text attachment for the current document only
5437: l_shipment_short_attach_query := 'CURSOR( SELECT pllx.line_location_id, fds.short_text
5438: FROM
5439: fnd_attached_docs_form_vl fad,
5440: fnd_documents_short_text fds
5441: WHERE entity_name = ''PO_SHIPMENTS'' AND
5442: pk1_value = to_char(pllx.line_location_id) AND
5443: function_name = ''PO_PRINTPO''
5444: AND fad.media_id = fds.media_id
6184:
6185: l_headerAttachmentsQuery := 'select fdl.long_text long_text
6186: FROM
6187: fnd_attached_docs_form_vl fad,
6188: fnd_documents_long_text fdl
6189: WHERE ( (entity_name=''PO_RELEASES'' AND
6190: pk1_value= to_char(PO_COMMUNICATION_PVT.getDocumentId()) ) OR
6191: (entity_name = ''PO_HEADERS'' AND
6192: pk1_value = to_char(PO_COMMUNICATION_PVT.getReleaseHeaderId())) OR --Bug6139548
6208:
6209: l_headerAttachmentsQuery := 'select fdl.long_text long_text
6210: FROM
6211: fnd_attached_docs_form_vl fad,
6212: fnd_documents_long_text fdl
6213: WHERE ((entity_name = ''PO_HEADERS'' AND
6214: pk1_value = to_char(PO_COMMUNICATION_PVT.getReleaseHeaderId())) OR --Bug6139548
6215: (entity_name = ''PO_VENDORS'' AND
6216: pk1_value = to_char(PO_COMMUNICATION_PVT.getVendorId()))) AND
6237:
6238: /*l_lineAttachQuery :='SELECT fds.long_text text, plx.po_line_id id
6239: FROM
6240: fnd_attached_docs_form_vl fad,
6241: fnd_documents_long_text fds,
6242: po_lines_all plx
6243: WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
6244: OR
6245: (fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
6269: -- Bug 14476193 : Changing condition DT.media_id = fds.media_id to D.media_id = fds.media_id
6270: l_lineAttachQuery :='select text, id from
6271: (SELECT /*+ leading (plx ad) use_nl(ad)*/
6272: fds.long_text text , plx.po_line_id id, seq_num
6273: FROM fnd_documents_long_text fds ,
6274: FND_ATTACHED_DOCUMENTS AD,
6275: FND_DOCUMENTS_TL DT ,
6276: FND_DOCUMENTS D ,
6277: FND_ATTACHMENT_FUNCTIONS AF,
6271: (SELECT /*+ leading (plx ad) use_nl(ad)*/
6272: fds.long_text text , plx.po_line_id id, seq_num
6273: FROM fnd_documents_long_text fds ,
6274: FND_ATTACHED_DOCUMENTS AD,
6275: FND_DOCUMENTS_TL DT ,
6276: FND_DOCUMENTS D ,
6277: FND_ATTACHMENT_FUNCTIONS AF,
6278: fnd_doc_category_usages DCU,
6279: po_lines_all plx
6272: fds.long_text text , plx.po_line_id id, seq_num
6273: FROM fnd_documents_long_text fds ,
6274: FND_ATTACHED_DOCUMENTS AD,
6275: FND_DOCUMENTS_TL DT ,
6276: FND_DOCUMENTS D ,
6277: FND_ATTACHMENT_FUNCTIONS AF,
6278: fnd_doc_category_usages DCU,
6279: po_lines_all plx
6280: WHERE ( ad.entity_name=''PO_LINES''
6290: AND plx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId ()
6291: UNION ALL
6292: SELECT /*+ leading (plx ad) use_nl(ad) */
6293: fds.long_text text , plx.po_line_id id, seq_num
6294: FROM fnd_documents_long_text fds ,
6295: FND_ATTACHED_DOCUMENTS AD,
6296: FND_DOCUMENTS_TL DT ,
6297: FND_DOCUMENTS D ,
6298: FND_ATTACHMENT_FUNCTIONS AF,
6292: SELECT /*+ leading (plx ad) use_nl(ad) */
6293: fds.long_text text , plx.po_line_id id, seq_num
6294: FROM fnd_documents_long_text fds ,
6295: FND_ATTACHED_DOCUMENTS AD,
6296: FND_DOCUMENTS_TL DT ,
6297: FND_DOCUMENTS D ,
6298: FND_ATTACHMENT_FUNCTIONS AF,
6299: fnd_doc_category_usages DCU,
6300: po_lines_all plx
6293: fds.long_text text , plx.po_line_id id, seq_num
6294: FROM fnd_documents_long_text fds ,
6295: FND_ATTACHED_DOCUMENTS AD,
6296: FND_DOCUMENTS_TL DT ,
6297: FND_DOCUMENTS D ,
6298: FND_ATTACHMENT_FUNCTIONS AF,
6299: fnd_doc_category_usages DCU,
6300: po_lines_all plx
6301: WHERE ( ad.entity_name=''PO_HEADERS''
6312: AND plx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId ()
6313: UNION ALL
6314: SELECT /*+ leading (plx ad) use_nl(ad) */
6315: fds.long_text text , plx.po_line_id id, seq_num
6316: FROM fnd_documents_long_text fds ,
6317: FND_ATTACHED_DOCUMENTS AD,
6318: FND_DOCUMENTS_TL DT ,
6319: FND_DOCUMENTS D ,
6320: FND_ATTACHMENT_FUNCTIONS AF,
6314: SELECT /*+ leading (plx ad) use_nl(ad) */
6315: fds.long_text text , plx.po_line_id id, seq_num
6316: FROM fnd_documents_long_text fds ,
6317: FND_ATTACHED_DOCUMENTS AD,
6318: FND_DOCUMENTS_TL DT ,
6319: FND_DOCUMENTS D ,
6320: FND_ATTACHMENT_FUNCTIONS AF,
6321: fnd_doc_category_usages DCU,
6322: po_lines_all plx
6315: fds.long_text text , plx.po_line_id id, seq_num
6316: FROM fnd_documents_long_text fds ,
6317: FND_ATTACHED_DOCUMENTS AD,
6318: FND_DOCUMENTS_TL DT ,
6319: FND_DOCUMENTS D ,
6320: FND_ATTACHMENT_FUNCTIONS AF,
6321: fnd_doc_category_usages DCU,
6322: po_lines_all plx
6323: WHERE ( ad.entity_name=''PO_IN_GA_LINES''
6334: AND plx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId ()
6335: UNION ALL
6336: SELECT /*+ leading (plx ad) use_nl(ad) */
6337: fds.long_text text , plx.po_line_id id, seq_num
6338: FROM fnd_documents_long_text fds ,
6339: FND_ATTACHED_DOCUMENTS AD,
6340: FND_DOCUMENTS_TL DT ,
6341: FND_DOCUMENTS D ,
6342: FND_ATTACHMENT_FUNCTIONS AF,
6336: SELECT /*+ leading (plx ad) use_nl(ad) */
6337: fds.long_text text , plx.po_line_id id, seq_num
6338: FROM fnd_documents_long_text fds ,
6339: FND_ATTACHED_DOCUMENTS AD,
6340: FND_DOCUMENTS_TL DT ,
6341: FND_DOCUMENTS D ,
6342: FND_ATTACHMENT_FUNCTIONS AF,
6343: fnd_doc_category_usages DCU,
6344: po_lines_all plx
6337: fds.long_text text , plx.po_line_id id, seq_num
6338: FROM fnd_documents_long_text fds ,
6339: FND_ATTACHED_DOCUMENTS AD,
6340: FND_DOCUMENTS_TL DT ,
6341: FND_DOCUMENTS D ,
6342: FND_ATTACHMENT_FUNCTIONS AF,
6343: fnd_doc_category_usages DCU,
6344: po_lines_all plx
6345: WHERE ( ad.entity_name=''PO_HEADERS''
6356: AND plx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId ()
6357: UNION ALL
6358: SELECT /*+ leading (plx ad) use_nl(ad) */
6359: fds.long_text text , plx.po_line_id id , seq_num
6360: FROM fnd_documents_long_text fds ,
6361: FND_ATTACHED_DOCUMENTS AD,
6362: FND_DOCUMENTS_TL DT ,
6363: FND_DOCUMENTS D ,
6364: FND_ATTACHMENT_FUNCTIONS AF,
6358: SELECT /*+ leading (plx ad) use_nl(ad) */
6359: fds.long_text text , plx.po_line_id id , seq_num
6360: FROM fnd_documents_long_text fds ,
6361: FND_ATTACHED_DOCUMENTS AD,
6362: FND_DOCUMENTS_TL DT ,
6363: FND_DOCUMENTS D ,
6364: FND_ATTACHMENT_FUNCTIONS AF,
6365: fnd_doc_category_usages DCU,
6366: po_lines_all plx
6359: fds.long_text text , plx.po_line_id id , seq_num
6360: FROM fnd_documents_long_text fds ,
6361: FND_ATTACHED_DOCUMENTS AD,
6362: FND_DOCUMENTS_TL DT ,
6363: FND_DOCUMENTS D ,
6364: FND_ATTACHMENT_FUNCTIONS AF,
6365: fnd_doc_category_usages DCU,
6366: po_lines_all plx
6367: WHERE ( ad.entity_name=''MTL_SYSTEM_ITEMS''
6398: -- Rewriting the following query for performance improvements.
6399: /*l_shipmentAttachmentQuery := 'SELECT fds.long_text long_text, pllx.LINE_LOCATION_ID
6400: FROM
6401: fnd_attached_docs_form_vl fad,
6402: fnd_documents_long_text fds,
6403: po_line_locations_all pllx
6404: WHERE entity_name = ''PO_SHIPMENTS'' AND
6405: pk1_value = to_char(pllx.LINE_LOCATION_ID) AND
6406: function_name = ''PO_PRINTPO''
6412: */
6413: l_shipmentAttachmentQuery := 'SELECT PO_COMMUNICATION_PVT.get_clob(fds.rowid) long_text, pllx.LINE_LOCATION_ID
6414: FROM
6415: fnd_attached_docs_form_vl fad,
6416: fnd_documents_long_text fds,
6417: po_line_locations_all pllx
6418: WHERE entity_name = ''PO_SHIPMENTS'' AND
6419: pk1_value = to_char(pllx.LINE_LOCATION_ID) AND
6420: function_name = ''PO_PRINTPO''
8404: BEGIN
8405: SELECT fds.long_text INTO
8406: l_long
8407: FROM
8408: fnd_documents_long_text fds
8409: WHERE fds.ROWID = p_row_id;
8410:
8411: l_clob := l_long;
8412:
8450: INTO
8451: l_one_time_address_clob
8452: FROM
8453: fnd_attached_docs_form_vl fad,
8454: fnd_documents_long_text fds
8455: WHERE entity_name = 'PO_SHIPMENTS' AND
8456: pk1_value = To_Char(p_line_location_id) AND
8457: function_name = 'PO_PRINTPO'
8458: AND fad.media_id = fds.media_id