[Home] [Help]
425: IF (g_po_wf_debug = 'Y') THEN
426: PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
427: END IF;
428:
429: FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => l_entity_name,
430: X_pk1_value =>to_char(l_document_id),
431: X_pk2_value =>to_char(l_revision_num),
432: X_pk3_value =>null,
433: X_pk4_value =>null,
1141: -- Join directly to the base table to improve performance
1142: cursor l_get_po_attachments_csr(l_po_header_id number) is
1143: select fl.file_name,dbms_lob.getlength(fl.file_data)
1144: from fnd_documents d,
1145: fnd_attached_documents ad,
1146: fnd_doc_category_usages dcu,
1147: fnd_attachment_functions af,
1148: fnd_lobs fl
1149: where ((ad.pk1_value=to_char(l_po_header_id) and ad.entity_name='PO_HEADERS')
1190: -- Join directly to the base table to improve performance
1191: cursor l_get_release_attachments_csr(l_po_release_id number) is
1192: select fl.file_name,dbms_lob.getlength(fl.file_data)
1193: from fnd_documents d,
1194: fnd_attached_documents ad,
1195: fnd_doc_category_usages dcu,
1196: fnd_attachment_functions af,
1197: fnd_lobs fl
1198: where ((ad.pk1_value=to_char((select po_header_id from po_releases_all
1378: where exists
1379: (
1380: select fl.file_name
1381: from fnd_documents d,
1382: fnd_attached_documents ad,
1383: fnd_doc_category_usages dcu,
1384: fnd_attachment_functions af,
1385: fnd_lobs fl
1386: where ((ad.pk1_value=to_char((select po_header_id from po_releases_all
1441: where exists
1442: (
1443: select fl.file_name
1444: from fnd_documents d,
1445: fnd_attached_documents ad,
1446: fnd_doc_category_usages dcu,
1447: fnd_attachment_functions af,
1448: fnd_lobs fl
1449: where ((ad.pk1_value=to_char(l_document_id) and ad.entity_name='PO_HEADERS')
2271:
2272: l_request_id number := NULL;
2273:
2274: l_progress VARCHAR2(3);
2275: l_entity_name fnd_attached_documents.entity_name%type;
2276: l_buyer_language_code fnd_documents_tl.language%type;
2277: l_pdf_file_name fnd_lobs.file_name%type; --
2278:
2279: BEGIN
2373: -- Brought the call out of the select
2374: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype,'_TERMS_',l_org_id,p_document_id,
2375: l_revision_num,l_buyer_language_code);
2376: --Bug #4865352 - Replaced fnd_documents_tl with fnd_documents_vl
2377: select count(1) into l_pdf_tc_buyer_exists from fnd_lobs fl,fnd_attached_documents fad, fnd_documents_vl fdl
2378: where
2379: fad.pk2_value = TO_CHAR(p_document_id) and
2380: fad.pk3_value = TO_CHAR(l_revision_num) and
2381: fad.entity_name = 'OKC_CONTRACT_DOCS' and
2400:
2401: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype,'_TERMS_',l_org_id,p_document_id,
2402: l_revision_num,l_buyer_language_code); --bug#3463617
2403: --Bug #4865352 - Added a join with fnd_documents
2404: select count(1) into l_pdf_tc_sup_exists from fnd_lobs fl,fnd_attached_documents fad, fnd_documents fd, fnd_documents_tl fdl
2405: where
2406: fad.pk1_value = TO_CHAR(p_document_id) and
2407: fad.pk2_value = TO_CHAR(l_revision_num) and
2408: fad.entity_name = l_entity_name and
2433: -- Brought the call out of the select
2434: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype,'_TERMS_',l_org_id,p_document_id,
2435: l_revision_num,l_buyer_language_code);
2436: --Bug #4865352
2437: select count(1) into l_pdf_nt_buyer_exists from fnd_lobs fl,fnd_attached_documents fad, fnd_documents_vl fdl
2438: where
2439: fad.pk1_value = TO_CHAR(p_document_id) and
2440: fad.pk2_value = TO_CHAR(l_revision_num) and
2441: fad.entity_name = l_entity_name and
2460: -- Brought the call out of the select
2461: l_pdf_file_name := po_communication_pvt.getPDFFileName(l_doctype,'_TERMS_',l_org_id,p_document_id,
2462: l_revision_num,l_language_code);
2463: --Bug #4865352 - Added a join with fnd_documents
2464: select count(1) into l_pdf_nt_sup_exists from fnd_lobs fl,fnd_attached_documents fad, fnd_documents fd, fnd_documents_tl fdl
2465: where
2466: fad.pk1_value = TO_CHAR(p_document_id) and
2467: fad.pk2_value = TO_CHAR(l_revision_num) and
2468: fad.entity_name = l_entity_name and
2908: IF p_blob_type = 'ZIP' THEN
2909: Begin
2910: SELECT fdl.media_id
2911: INTO x_media_id
2912: FROM fnd_attached_documents fad,
2913: fnd_documents_vl fdl
2914: WHERE fad.pk1_value=to_char(p_document_id)
2915: and fad.pk2_value=to_char(p_revision_number)
2916: and fad.entity_name = l_entity_name
2993: 'binary');
2994:
2995: l_progress := '070';
2996:
2997: INSERT INTO fnd_attached_documents (attached_document_id,
2998: document_id,
2999: creation_date,
3000: created_by,
3001: last_update_date,
3030: attribute14,
3031: attribute15,
3032: column1)
3033: VALUES
3034: (fnd_attached_documents_s.nextval,
3035: document_id_tmp,
3036: sysdate,
3037: 1,--NVL(X_created_by,0),
3038: sysdate,
3165: --Bug #4865352 - Added join with fnd_documents and selected media_id from it
3166: --Bug #5232999 - Added file name as criterion so that we get only the PDF
3167: SELECT file_data into l_document
3168: FROM fnd_lobs fl,
3169: fnd_attached_documents fad,
3170: fnd_documents fd,
3171: fnd_documents_tl fdl
3172: WHERE fad.pk1_value=to_char(l_document_id) and fad.pk2_value=to_char(l_revision_number)
3173: and fdl.document_id = fad.document_id and fdl.document_id = fd.document_id and fd.media_id = fl.file_id
3264: -- Added join condition on file name of PDF
3265: IF l_withTerms='Y' AND l_document_type in ('PO','PA') THEN
3266: SELECT file_data into l_document
3267: FROM fnd_lobs fl,
3268: fnd_attached_documents fad,
3269: fnd_documents fd,
3270: fnd_documents_tl fdl
3271: WHERE fad.pk2_value=to_char(l_document_id) and fad.pk3_value=to_char(l_revision_number)
3272: and fdl.document_id = fad.document_id and fdl.document_id = fd.document_id and fd.media_id = fl.file_id
3282: --Bug #4865352 - Added a join with fnd_documents
3283: IF l_document_type in ('PO','PA','RELEASE') AND l_withTerms ='N' THEN
3284: SELECT file_data into l_document
3285: FROM fnd_lobs fl,
3286: fnd_attached_documents fad,
3287: fnd_documents fd,
3288: fnd_documents_tl fdl
3289: WHERE fad.pk1_value=to_char(l_document_id) and fad.pk2_value=to_char(l_revision_number)
3290: and fdl.document_id = fad.document_id and fd.media_id = fl.file_id
3419: -- Bug 4047688
3420: -- Appended join condition on file name of document to prevent return of multiple rows
3421: SELECT file_data into l_document
3422: FROM fnd_lobs fl,
3423: fnd_attached_documents fad,
3424: fnd_documents_vl fdl
3425: WHERE fad.pk2_value=to_char(l_document_id) and fad.pk3_value=to_char(l_revision_number)
3426: and fdl.document_id = fad.document_id and fdl.media_id = fl.file_id and fad.entity_name = 'OKC_CONTRACT_DOCS'
3427: and fl.file_name = l_filename; --Bug 4047688
3444:
3445: --Bug #4865352 - Added a join with fnd_documents
3446: SELECT file_data into l_document
3447: FROM fnd_lobs fl,
3448: fnd_attached_documents fad,
3449: fnd_documents fd,
3450: fnd_documents_tl fdl
3451: WHERE fad.pk1_value=to_char(l_document_id) and fad.pk2_value=to_char(l_revision_number)
3452: and fdl.document_id = fad.document_id and fd.media_id = fl.file_id and fd.document_id = fdl.document_id
3683: l_document_type po_headers_all.type_lookup_code%TYPE;
3684: l_org_id po_headers_all.org_id%type;
3685: l_revision_number po_headers_all.revision_num%type;
3686: l_language fnd_languages.language_code%type;
3687: l_entity_name fnd_attached_documents.entity_name%type;
3688: l_itemtype wf_items.item_type%type;
3689: l_itemkey wf_items.item_key%type;
3690: l_document fnd_lobs.file_data%type;
3691: l_document_length number;
3746: Begin
3747: SELECT fl.file_data,fl.file_content_type
3748: INTO l_document,l_filecontent_type
3749: FROM fnd_lobs fl,
3750: fnd_attached_documents fad,
3751: fnd_documents fd,
3752: fnd_documents_tl fdl
3753: WHERE fad.pk1_value=to_char(l_document_id)
3754: and fad.pk2_value=to_char(l_revision_number)
3971: where exists
3972: (
3973: select fl.file_name
3974: from fnd_documents d,
3975: fnd_attached_documents ad,
3976: fnd_doc_category_usages dcu,
3977: fnd_attachment_functions af,
3978: fnd_lobs fl
3979: where ((ad.pk1_value=to_char((select po_header_id from po_releases_all
4024: where exists
4025: (
4026: select fl.file_name
4027: from fnd_documents d,
4028: fnd_attached_documents ad,
4029: fnd_doc_category_usages dcu,
4030: fnd_attachment_functions af,
4031: fnd_lobs fl
4032: where ((ad.pk1_value=to_char(p_document_id) and ad.entity_name='PO_HEADERS')