[Home] [Help]
467: l_terms_flag po_headers_all.CONTERMS_EXIST_FLAG%type;
468: l_document_subtype po_headers_all.type_lookup_code%TYPE;
469: l_document_type po_headers_all.type_lookup_code%TYPE;
470: l_count number;
471: l_filename fnd_lobs.file_name%type;
472: l_orgid number;
473: x_progress varchar2(100);
474: l_with_terms PO_HEADERS_ALL.CONTERMS_EXIST_FLAG%TYPE;
475: l_terms varchar2(10);
538: /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
539: END IF;
540:
541:
542: SELECT count(*) into l_count from fnd_lobs fl,fnd_attached_docs_form_vl fad
543: WHERE
544: fl.file_id = fad.media_id and
545: fad.pk2_value=to_char(l_document_id) and
546: fad.pk3_value=to_char(l_revision_num) and
559: -- Added TO_CHAR() wherever a numeric value is compared with pk1_value or pk2_value.
560:
561: -- bug4931216
562: -- Add enttiy name filtering to utilize the index
563: SELECT count(*) into l_count from fnd_lobs fl,fnd_attached_docs_form_vl fad
564: WHERE
565: fl.file_id = fad.media_id and
566: fad.pk1_value=to_char(l_document_id) and
567: fad.pk2_value=to_char(l_revision_num) and
1131: -- file attachments with same filename
1132: l_error_flag number; -- determines if the error condition (same file name
1133: -- but different file lengths has been met or not)
1134: l_max_attachment_size po_system_parameters_all.max_attachment_size%type;
1135: l_filename fnd_lobs.file_name%type;
1136: l_filename_new fnd_lobs.file_name%type;
1137: l_length number;
1138: l_length_new number;
1139:
1132: l_error_flag number; -- determines if the error condition (same file name
1133: -- but different file lengths has been met or not)
1134: l_max_attachment_size po_system_parameters_all.max_attachment_size%type;
1135: l_filename fnd_lobs.file_name%type;
1136: l_filename_new fnd_lobs.file_name%type;
1137: l_length number;
1138: l_length_new number;
1139:
1140: -- bug4931216
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')
1150: OR
1151: (ad.pk1_value=to_char((select vendor_id from po_headers_all
1152: where po_header_id=l_po_header_id)) and ad.entity_name='PO_VENDORS')
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
1199: where po_release_id=l_po_release_id
1200: )) and ad.entity_name='PO_HEADERS')
1201: OR
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
1387: where po_release_id=l_document_id
1388: )) and ad.entity_name='PO_HEADERS')
1389: OR
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')
1450: OR
1451: (ad.pk1_value=to_char((select vendor_id from po_headers_all
1452: where po_header_id=l_document_id)) and ad.entity_name='PO_VENDORS')
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; --
2279: BEGIN
2280: l_progress := '000';
2281: IF g_debug_stmt THEN
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
2851: l_category_id number;
2852: l_count number;
2853:
2854: --
2855: l_file_name fnd_lobs.file_name%type;
2856: l_file_content_type fnd_lobs.file_content_type%type;
2857: l_org_id po_headers_all.org_id%type;
2858: l_api_name CONSTANT VARCHAR2(25):= 'Store_Blob';
2859: l_progress VARCHAR2(3);
2852: l_count number;
2853:
2854: --
2855: l_file_name fnd_lobs.file_name%type;
2856: l_file_content_type fnd_lobs.file_content_type%type;
2857: l_org_id po_headers_all.org_id%type;
2858: l_api_name CONSTANT VARCHAR2(25):= 'Store_Blob';
2859: l_progress VARCHAR2(3);
2860:
2917: and fdl.document_id = fad.document_id;
2918:
2919: Exception when others Then
2920: -- do nothing. If no_data_found then x_media_id will be null and a new lob
2921: -- locator will be inserted into fnd_lobs. If too_many_rows (though should
2922: -- not occur because the locator gets overwritten everytime) then the first blob found will be overwritten
2923: null;
2924: End;
2925: END IF;
2966: x_media_id);
2967:
2968: l_progress := '060';
2969:
2970: INSERT INTO fnd_lobs (
2971: file_id,
2972: File_name,
2973: file_content_type,
2974: upload_date,
3086: procedure pdf_attach_app (document_id in varchar2,
3087: content_type in varchar2,
3088: document in out NOCOPY blob,
3089: document_type in out NOCOPY varchar2) IS
3090: l_filename fnd_lobs.file_name%type;
3091: l_document_id number;
3092: l_document_type po_headers.type_lookup_code%TYPE;
3093: l_org_id number;
3094: l_revision_number number;
3164:
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)
3203: PROCEDURE pdf_attach_supp(document_id in varchar2,
3204: content_type in varchar2,
3205: document in out nocopy blob,
3206: document_type in out nocopy varchar2) IS
3207: l_filename fnd_lobs.file_name%type;
3208: l_document_id number;
3209: l_document_type po_headers.type_lookup_code%TYPE;
3210: l_org_id number;
3211: l_revision_number number;
3263: -- Bug 4047688
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)
3281: END IF;
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)
3316: procedure pdf_attach(document_id in varchar2,
3317: content_type in varchar2,
3318: document in out nocopy blob,
3319: document_type in out nocopy varchar2) IS
3320: l_filename fnd_lobs.file_name%type;
3321: l_document_id number;
3322: l_document_type po_headers.type_lookup_code%TYPE;
3323: l_org_id number;
3324: l_revision_number number;
3418: --Bug #4865352 - Replaced fnd_documents_tl with fnd_documents_vl
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'
3443: if l_document_type in ('PO','PA','RELEASE') and l_withTerms ='N' then
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)
3503: document in out nocopy blob,
3504: document_type in out nocopy varchar2)
3505: IS
3506:
3507: l_okc_file_id fnd_lobs.file_id%TYPE;
3508: l_okc_file_name fnd_lobs.file_name%TYPE;
3509: l_okc_file_data fnd_lobs.file_data%TYPE;
3510: l_okc_file_content_type fnd_lobs.file_content_type%TYPE;
3511:
3504: document_type in out nocopy varchar2)
3505: IS
3506:
3507: l_okc_file_id fnd_lobs.file_id%TYPE;
3508: l_okc_file_name fnd_lobs.file_name%TYPE;
3509: l_okc_file_data fnd_lobs.file_data%TYPE;
3510: l_okc_file_content_type fnd_lobs.file_content_type%TYPE;
3511:
3512: l_po_document_id number;
3505: IS
3506:
3507: l_okc_file_id fnd_lobs.file_id%TYPE;
3508: l_okc_file_name fnd_lobs.file_name%TYPE;
3509: l_okc_file_data fnd_lobs.file_data%TYPE;
3510: l_okc_file_content_type fnd_lobs.file_content_type%TYPE;
3511:
3512: l_po_document_id number;
3513: l_po_document_type po_headers.type_lookup_code%TYPE;
3506:
3507: l_okc_file_id fnd_lobs.file_id%TYPE;
3508: l_okc_file_name fnd_lobs.file_name%TYPE;
3509: l_okc_file_data fnd_lobs.file_data%TYPE;
3510: l_okc_file_content_type fnd_lobs.file_content_type%TYPE;
3511:
3512: l_po_document_id number;
3513: l_po_document_type po_headers.type_lookup_code%TYPE;
3514: l_po_document_subtype po_headers.type_lookup_code%TYPE;
3596:
3597: IF (l_okc_file_id > 0)
3598: THEN
3599:
3600: -- Bug 4173198: Select file_data from fnd_lobs into local variable
3601: -- l_okc_file_data first and then use dbms_lob.copy
3602:
3603: SELECT fl.file_name, fl.file_content_type, fl.file_data
3604: INTO l_okc_file_name, l_okc_file_content_type, l_okc_file_data
3601: -- l_okc_file_data first and then use dbms_lob.copy
3602:
3603: SELECT fl.file_name, fl.file_content_type, fl.file_data
3604: INTO l_okc_file_name, l_okc_file_content_type, l_okc_file_data
3605: FROM fnd_lobs fl
3606: WHERE fl.file_id = l_okc_file_id;
3607:
3608: document_type := l_okc_file_content_type || '; name=' || l_okc_file_name;
3609:
3676: PROCEDURE zip_attach(document_id in varchar2,
3677: content_type in varchar2,
3678: document in out nocopy blob,
3679: document_type in out nocopy varchar2) IS
3680: l_filename fnd_lobs.file_name%type;
3681: l_document_id po_headers_all.po_header_id%type;
3682: l_document_number po_headers_all.segment1%type;
3683: l_document_type po_headers_all.type_lookup_code%TYPE;
3684: l_org_id po_headers_all.org_id%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;
3692: l_progress varchar2(300);
3693: l_filecontent_type fnd_lobs.file_content_type%type;
3694: l_message FND_NEW_MESSAGES.message_text%TYPE;
3689: l_itemkey wf_items.item_key%type;
3690: l_document fnd_lobs.file_data%type;
3691: l_document_length number;
3692: l_progress varchar2(300);
3693: l_filecontent_type fnd_lobs.file_content_type%type;
3694: l_message FND_NEW_MESSAGES.message_text%TYPE;
3695:
3696:
3697: BEGIN
3745: --Bug #4865352 - Added a join with fnd_documents and selected media_id from it
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)
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
3980: where po_release_id=p_document_id
3981: )) and ad.entity_name='PO_HEADERS')
3982: OR
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')
4033: OR
4034: (ad.pk1_value=to_char((select vendor_id from po_headers_all
4035: where po_header_id=p_document_id)) and ad.entity_name='PO_VENDORS')
6413:
6414: l_po_number po_headers_all.segment1%type;
6415: l_language_code fnd_languages.language_code%type;
6416: l_api_name CONSTANT VARCHAR2(25):= 'PDFFileName';
6417: l_file_name fnd_lobs.file_name%type;
6418: l_progress VARCHAR2(3);
6419: BEGIN
6420:
6421: l_progress := '000';