[Home] [Help]
526: IF (g_po_wf_debug = 'Y') THEN
527: PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
528: END IF;
529:
530: FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => l_entity_name,
531: X_pk1_value => to_char(l_document_id),
532: X_pk2_value => to_char(l_revision_num),
533: --X_pk3_value => null, -- CLM
534: X_pk3_value => l_draft_id_char, -- CLM
1016: Begin
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)
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
1369: where ((ad.pk1_value = to_char(l_po_header_id) and ad.entity_name = 'PO_HEADERS')
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
1418: where ((ad.pk1_value = to_char((select po_header_id from po_releases_all
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
1632: where ((ad.pk1_value = to_char((select po_header_id from po_releases_all
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
1695: where ((ad.pk1_value = to_char(l_document_id) and ad.entity_name = 'PO_HEADERS')
2659:
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
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
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
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
3331: IF p_blob_type = 'ZIP' THEN
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
3417: 'binary');
3418:
3419: l_progress := '070';
3420:
3421: INSERT INTO fnd_attached_documents (attached_document_id,
3422: document_id,
3423: creation_date,
3424: created_by,
3425: last_update_date,
3454: attribute14,
3455: attribute15,
3456: column1)
3457: VALUES
3458: (fnd_attached_documents_s.nextval,
3459: document_id_tmp,
3460: sysdate,
3461: 1, --NVL(X_created_by,0),
3462: sysdate,
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,
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
3611: If document does not exists on PO_HEAD and PO_REL, fetch it from OKC_CONTRACT_DOCS*/
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)
3635: END IF;
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)
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
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
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,
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
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,
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)
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,
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
3947:
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)
3971: END IF;
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)
4237: l_document_type po_headers_all.type_lookup_code%TYPE;
4238: l_org_id po_headers_all.org_id%type;
4239: l_revision_number po_headers_all.revision_num%type;
4240: l_language fnd_languages.language_code%type;
4241: l_entity_name fnd_attached_documents.entity_name%type;
4242: l_itemtype wf_items.item_type%type;
4243: l_itemkey wf_items.item_key%type;
4244: l_document fnd_lobs.file_data%type;
4245: l_document_length number;
4300: Begin
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)
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
4534: where (ad.pk1_value=to_char((select po_header_id from po_releases_all
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
4554: where (
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
4572: where (
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
4594: where (
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
4615: where (
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
4636: where ( /*bug 13528070*/
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
4668: where (ad.pk1_value=to_char(p_document_id) and ad.entity_name='PO_HEADERS')
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
4685: where (
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
4705: where ( /*bug 13528070 Changing to to_char*/
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
4726: where (
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
4748: where (
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
4770: where (
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
4792: where ( /*bug 13528070*/
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,
6278: fnd_doc_category_usages DCU,
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,
6299: fnd_doc_category_usages DCU,
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,
6321: fnd_doc_category_usages DCU,
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,
6343: fnd_doc_category_usages DCU,
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,
6365: fnd_doc_category_usages DCU,