[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
SELECT nls_language, nls_territory
INTO l_language, l_territory
FROM fnd_languages
WHERE language_code = l_language_code;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PROCEDURE DELETE_PDF_ATTACHMENTS (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) is
l_document_id number;
x_progress := 'PO_COMMUNICATION_PVT.DELETE_PDF_ATTACHMENTS';
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
x_progress := 'PO_COMMUNICATION_PVT.DELETE_PDF_ATTACHMENTS :Calling the Delete attachments procedure';
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => l_entity_name,
X_pk1_value => to_char(l_document_id),
X_pk2_value => to_char(l_revision_num),
--X_pk3_value => null, -- CLM
X_pk3_value => l_draft_id_char, -- CLM
X_pk4_value => null,
X_pk5_value => null,
X_delete_document_flag => 'Y',
X_automatically_added_flag => 'N');
x_progress := 'PO_COMMUNICATION_PVT.DELETE_PDF_ATTACHMENTS:In Exception handler';
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
wf_core.context('PO_COMMUNICATION_PVT', 'DELETE_PDF_ATTACHMENTS', x_progress);
END DELETE_PDF_ATTACHMENTS;
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
SELECT count(*) into l_count from fnd_lobs fl, fnd_attached_docs_form_vl fad
WHERE
fl.file_id = fad.media_id and
fad.pk2_value = to_char(l_document_id) and
fad.pk3_value = to_char(l_revision_num) and
fl.file_name = l_filename and
fad.entity_name in ('PO_HEAD', 'PO_REL');
/* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
SELECT count(*) into l_count from fnd_lobs fl, fnd_attached_docs_form_vl fad
WHERE
fl.file_id = fad.media_id and
fad.pk1_value = to_char(l_document_id) and
fad.pk2_value = to_char(l_revision_num) and
fl.file_name = l_filename and
fad.entity_name IN ('PO_HEAD', 'PO_REL');
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
select to_char (PO_WF_ITEMKEY_S.NEXTVAL) into l_seq_for_item_key from sys.dual;
PO_WF_DEBUG_PKG.insert_debug (l_itemtype, l_itemkey, l_progress);
SELECT hou.name
into l_operating_unit
FROM
hr_organization_units hou
WHERE
hou.organization_id = l_orgid;
select po_header_id, release_num into l_document_id, l_release_num
from po_releases_all
where
po_release_id = p_document_id;
select segment1, global_agreement_flag into l_docNumber, l_ga_flag
from po_headers_all
where po_header_id = l_document_id;
select DECODE(p_document_subtype, 'BLANKET', FND_MESSAGE.GET_STRING('POS', 'POS_POTYPE_BLKT'),
'CONTRACT', FND_MESSAGE.GET_STRING('POS', 'POS_POTYPE_CNTR'),
'STANDARD', FND_MESSAGE.GET_STRING('POS', 'POS_POTYPE_STD'),
'PLANNED', FND_MESSAGE.GET_STRING('POS', 'POS_POTYPE_PLND')) into l_doc_display_name from dual;
PO_WF_DEBUG_PKG.insert_debug (l_itemtype, l_itemkey, l_progress);
select poh.vendor_site_id, pvs.vendor_site_code, pvs.language
into l_vendor_site_id, l_vendor_site_code, l_vendor_site_lang
from po_headers poh, po_vendor_sites pvs, po_releases por
where pvs.vendor_site_id = poh.vendor_site_id
and poh.po_header_id = por.po_header_id
and por.po_release_id = p_document_id;
select poh.vendor_site_id, pvs.vendor_site_code, pvs.language
into l_vendor_site_id, l_vendor_site_code, l_vendor_site_lang
from po_headers poh, po_vendor_sites pvs
where pvs.vendor_site_id = poh.vendor_site_id
and poh.po_header_id = p_document_id;
SELECT wfl.nls_language, wfl.nls_territory INTO l_adhocuser_lang, l_adhocuser_territory
FROM wf_languages wfl, fnd_languages_vl flv
WHERE wfl.code = flv.language_code AND flv.nls_language = l_vendor_site_lang;
SELECT wfl.nls_language, wfl.nls_territory into l_adhocuser_lang, l_adhocuser_territory
FROM wf_languages wfl, fnd_languages_vl flv
WHERE wfl.code = flv.language_code AND flv.installed_flag = 'B';
PO_WF_DEBUG_PKG.insert_debug (l_itemtype, l_itemkey, l_progress);
select count(1) into l_performer_exists
from wf_users where name = l_po_email_performer;
PO_REQAPPROVAL_INIT1.update_print_count(p_document_id, p_document_type);
SELECT count(*)
INTO l_zip_exists
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdl
WHERE fad.pk1_value = to_char(p_document_id)
and fad.pk2_value = to_char(p_revision_num)
and fad.entity_name = l_entity_name
and fdl.document_id = fad.document_id
and fdl.document_id = fd.document_id
--Bug 5017976 selecting media_id from fd instead of fdl
and fd.media_id = fl.file_id
and fl.file_name = l_filename;
PO_WF_DEBUG_PKG.insert_debug (l_itemtype, l_itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(l_itemtype, l_itemkey, l_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
SELECT pv.language
INTO l_supp_lang
FROM po_vendor_sites_all pv,
po_headers_all ph
WHERE ph.po_header_id = l_header_id
AND ph.vendor_site_id = pv.vendor_site_id;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
SELECT nls_language
INTO l_language
FROM fnd_languages
WHERE language_code = l_language_code;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
SELECT nls_territory
INTO l_territory
FROM fnd_languages
WHERE nls_language = l_supp_lang;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
select fl.file_name, dbms_lob.getlength(fl.file_data)
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where ((ad.pk1_value = to_char(l_po_header_id) and ad.entity_name = 'PO_HEADERS')
OR
(ad.pk1_value = to_char((select vendor_id from po_headers_all
where po_header_id = l_po_header_id)) and ad.entity_name = 'PO_VENDORS')
OR
(ad.pk1_value in (select to_char(po_line_id) from po_lines_all
where po_header_id = l_po_header_id
) and ad.entity_name = 'PO_LINES')
OR
(ad.pk1_value in (select to_char(from_header_id) from po_lines_all
where po_header_id = l_po_header_id
and from_header_id is not null
) and ad.entity_name = 'PO_HEADERS')
OR
(ad.pk1_value in (select to_char(from_line_id) from po_lines_all
where po_header_id = l_po_header_id
and from_line_id is not null
) and ad.entity_name = 'PO_LINES')
OR
(ad.pk1_value in (select to_char(line_location_id) from po_line_locations_all
where po_header_id = l_po_header_id
and shipment_type in ('PRICE BREAK', 'STANDARD', 'PREPAYMENT') --
) and ad.entity_name = 'PO_SHIPMENTS')
OR
(ad.pk2_value in (select item_id from po_lines_all
where po_header_id = l_po_header_id
and to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) = ad.pk1_value --Bug 4673653 Use Inventory OrgId
and item_id is not null
) and ad.entity_name = 'MTL_SYSTEM_ITEMS')
)
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id = 6
and af.function_name = 'PO_PRINTPO'
and d.media_id = fl.file_id
and dcu.enabled_flag = 'Y'
group by fl.file_name, dbms_lob.getlength(fl.file_data)
order by fl.file_name;
select fl.file_name, dbms_lob.getlength(fl.file_data)
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where ((ad.pk1_value = to_char((select po_header_id from po_releases_all
where po_release_id = l_po_release_id
)) and ad.entity_name = 'PO_HEADERS')
OR
(ad.pk1_value = to_char(l_po_release_id) and ad.entity_name = 'PO_RELEASES')
OR
(ad.pk1_value = to_char((select pha.vendor_id
from po_headers_all pha, po_releases_all pra
where pra.po_release_id = l_po_release_id
and pha.po_header_id = pra.po_header_id
)) and ad.entity_name = 'PO_VENDORS')
OR
(ad.pk1_value in (select to_char(po_line_id) from po_line_locations_all
where po_release_id = l_po_release_id
and shipment_type = 'BLANKET'
) and ad.entity_name = 'PO_LINES')
OR
(ad.pk1_value in (select to_char(line_location_id) from po_line_locations_all
where po_release_id = l_po_release_id
and shipment_type = 'BLANKET'
) and ad.entity_name = 'PO_SHIPMENTS')
OR
(ad.pk2_value in (select pl.item_id
from po_lines_all pl, po_line_locations_all pll
where pll.po_release_id = l_po_release_id
and pll.shipment_type = 'BLANKET'
and pll.po_line_id = pl.po_line_id
and to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) = ad.pk1_value --Bug 4673653 Use Inventory OrgId
and pl.item_id is not null
) AND ad.entity_name = 'MTL_SYSTEM_ITEMS')
)
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id = 6
and af.function_name = 'PO_PRINTPO'
and d.media_id = fl.file_id
and dcu.enabled_flag = 'Y'
group by fl.file_name, dbms_lob.getlength(fl.file_data)
order by fl.file_name;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, l_progress);
SELECT nls_language, nls_territory
INTO l_language, l_territory
FROM fnd_languages
WHERE language_code = l_language_code;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,l_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, l_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, l_progress);
select 'Y' into l_duplicate_filenames from dual
where exists
(
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where ((ad.pk1_value = to_char((select po_header_id from po_releases_all
where po_release_id = l_document_id
)) and ad.entity_name = 'PO_HEADERS')
OR
(ad.pk1_value = to_char(l_document_id) and ad.entity_name = 'PO_RELEASES')
OR
(ad.pk1_value = (select pha.vendor_id
from po_headers_all pha, po_releases_all pra
where pra.po_release_id = l_document_id
and pha.po_header_id = pra.po_header_id
) and ad.entity_name = 'PO_VENDORS')
OR
(ad.pk1_value in (select to_char(po_line_id) from po_line_locations_all
where po_release_id = l_document_id
and shipment_type = 'BLANKET'
) and ad.entity_name = 'PO_LINES')
OR
(ad.pk1_value in (select to_char(line_location_id) from po_line_locations_all
where po_release_id = l_document_id
and shipment_type = 'BLANKET'
) and ad.entity_name = 'PO_SHIPMENTS')
OR
(ad.pk2_value in (select pl.item_id
from po_lines_all pl, po_line_locations_all pll
where pll.po_release_id = l_document_id
and pll.shipment_type = 'BLANKET'
and pll.po_line_id = pl.po_line_id
and to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) = ad.pk1_value --Bug 4673653 Use Inventory OrgId
and pl.item_id is not null
) AND ad.entity_name = 'MTL_SYSTEM_ITEMS')
)
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id = 6
and af.function_name = 'PO_PRINTPO'
and d.media_id = fl.file_id
and dcu.enabled_flag = 'Y'
group by fl.file_name
having count(*)>1
);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, l_progress);
select 'Y' into l_duplicate_filenames from dual
where exists
(
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where ((ad.pk1_value = to_char(l_document_id) and ad.entity_name = 'PO_HEADERS')
OR
(ad.pk1_value = to_char((select vendor_id from po_headers_all
where po_header_id = l_document_id)) and ad.entity_name = 'PO_VENDORS')
OR
(ad.pk1_value in (select to_char(po_line_id) from po_lines_all
where po_header_id = l_document_id
) and ad.entity_name = 'PO_LINES')
OR
(ad.pk1_value in (select to_char(from_header_id) from po_lines_all
where po_header_id = l_document_id
and from_header_id is not null
) and ad.entity_name = 'PO_HEADERS')
OR
(ad.pk1_value in (select to_char(from_line_id) from po_lines_all
where po_header_id = l_document_id
and from_line_id is not null
) and ad.entity_name = 'PO_LINES')
OR
(ad.pk1_value in (select to_char(line_location_id) from po_line_locations_all
where po_header_id = l_document_id
and shipment_type in ('PRICE BREAK', 'STANDARD', 'PREPAYMENT') --
) and ad.entity_name = 'PO_SHIPMENTS')
OR
(ad.pk2_value in (select item_id from po_lines_all
where po_header_id = l_document_id
and to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) = ad.pk1_value --Bug 4673653 Use Inventory OrgId
and item_id is not null
) and ad.entity_name = 'MTL_SYSTEM_ITEMS')
)
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id = 6
and af.function_name = 'PO_PRINTPO'
and d.media_id = fl.file_id
and dcu.enabled_flag = 'Y'
group by fl.file_name
having count(*)>1
);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, l_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,'PO_COMMUNICATION_PVT.generate_pdf_buyer: Caught Zip generation exception '|| SQLERRM);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
SELECT po_header_id into l_header_id FROM po_releases_all
WHERE po_release_id = l_document_id;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
SELECT pv.language into l_supp_lang
FROM po_vendor_sites_all pv, po_headers_all ph
WHERE
ph.po_header_id = l_header_id and ph.vendor_site_id = pv.vendor_site_id;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
SELECT nls_language INTO l_language
FROM fnd_languages
WHERE language_code = l_language_code;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
select nls_territory into l_territory from fnd_languages where
nls_language = l_supp_lang;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
select nls_territory into l_territory from fnd_languages where
language_code = l_language_code;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY, x_progress);
select nls_language, nls_territory into l_language , l_territory from fnd_languages where
language_code = l_language_code;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
* Modified the SQLs used for selecting from PO and OKC Repository
* Now selecting release revision number from po_release_archives_all
*/
procedure Communicate(p_authorization_status in varchar2,
p_with_terms in varchar2,
p_language_code in varchar2,
p_mode in varchar2,
p_document_id in number ,
p_revision_number in number,
p_document_type in varchar2,
p_fax_number in varchar2,
p_email_address in varchar2,
p_request_id out nocopy number)
IS
l_conterm_exists_flag po_headers_all.CONTERMS_EXIST_FLAG%type;
select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph, po_releases_all pr
where ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id and
ph.vendor_site_id = pvs.vendor_site_id ;
select language_code, nls_territory into l_language_code, l_territory from fnd_languages fl where
fl.nls_language = l_supp_lang;
select NVL(conterms_exist_flag, 'N') into l_conterm_exists_flag from po_headers_all
where
po_header_id = p_document_id and revision_num = p_revision_number;
select count(1) into l_pdf_tc_buyer_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents_vl fdl
where
fad.pk2_value = TO_CHAR(p_document_id) and
fad.pk3_value = TO_CHAR(l_revision_num) and
fad.entity_name = 'OKC_CONTRACT_DOCS' and
fdl.document_id = fad.document_id and
fdl.media_id = fl.file_id and
fl.file_name = l_pdf_file_name;
select count(1) into l_pdf_tc_sup_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents fd, fnd_documents_tl fdl
where
fad.pk1_value = TO_CHAR(p_document_id) and
fad.pk2_value = TO_CHAR(l_revision_num) and
fad.entity_name = l_entity_name and
fdl.document_id = fad.document_id and
fd.media_id = fl.file_id and
fd.document_id = fdl.document_id and
fdl.language = l_language_code and
fl.file_name = l_pdf_file_name;
select count(1) into l_pdf_nt_buyer_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents_vl fdl
where
fad.pk1_value = TO_CHAR(p_document_id) and
fad.pk2_value = TO_CHAR(l_revision_num) and
fad.entity_name = l_entity_name and
fdl.document_id = fad.document_id and
fdl.media_id = fl.file_id and
fl.file_name = l_pdf_file_name;
select count(1) into l_pdf_nt_sup_exists from fnd_lobs fl, fnd_attached_documents fad, fnd_documents fd, fnd_documents_tl fdl
where
fad.pk1_value = TO_CHAR(p_document_id) and
fad.pk2_value = TO_CHAR(l_revision_num) and
fad.entity_name = l_entity_name and
fdl.document_id = fad.document_id and
fd.media_id = fl.file_id and
fd.document_id = fdl.document_id and
fdl.language = l_language_code and
fl.file_name = l_pdf_file_name;
select max(revision_num)
into l_revision_num
from po_headers_archive_all
where po_header_id = p_document_id
and authorization_status = 'APPROVED';
select max(revision_num)
into l_revision_num
from po_releases_archive_all
where po_release_id = p_document_id
and authorization_status = 'APPROVED';
select nls_language, nls_territory into l_supp_lang, l_territory from fnd_languages fl where
fl.language_code = p_language_code ;
select nls_language, nls_territory into l_supp_lang, l_territory from fnd_languages fl where
fl.language_code = p_language_code ;
select nls_language, nls_territory into l_supp_lang, l_territory from fnd_languages fl where
fl.language_code = l_buyer_language_code;
select nls_language, nls_territory into l_supp_lang, l_territory from fnd_languages fl where
fl.language_code = l_buyer_language_code;
select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph
where po_header_id = p_document_id and ph.vendor_site_id = pvs.vendor_site_id ;
select pvs.language into l_supp_lang from po_vendor_sites pvs , po_headers_all ph, po_releases_all pr
where ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id and
ph.vendor_site_id = pvs.vendor_site_id ;
select language_code, nls_territory into l_language_code, l_territory from fnd_languages fl where
fl.nls_language = l_supp_lang;
select language_code, nls_territory
into l_language_code, l_territory
from fnd_languages fl
where fl.nls_language = l_supp_lang;
select po_output_format into l_format from po_system_parameters;
SELECT org_id INTO l_org_id FROM po_headers_all WHERE po_header_id = p_document_id;
SELECT fdl.media_id
INTO x_media_id
FROM fnd_attached_documents fad,
fnd_documents_vl fdl
WHERE fad.pk1_value = to_char(p_document_id)
and fad.pk2_value = to_char(p_revision_number)
and nvl(fad.pk3_value, '*') = nvl(l_draft_id, '*') -- CLM
and fad.entity_name = l_entity_name
and fdl.document_id = fad.document_id;
SELECT category_id into l_category_id from fnd_document_categories
where name = 'CUSTOM2446' ;
FND_DOCUMENTS_PKG.Insert_Row(
row_id_tmp,
document_id_tmp,
SYSDATE,
1, --NVL(X_created_by,0),
SYSDATE,
1, --NVL(X_created_by,0),
1, --X_last_update_login,
6,
l_category_id, --Get the value for the category id 'PO Documents'
1, --null,--security_type,
null, --security_id,
'Y', --null,--publish_flag,
null, --image_type,
null, --storage_type,
'O', --usage_type,
sysdate, --start_date_active,
null, --end_date_active,
null,--X_request_id, --null
null,--X_program_application_id, --null
null, --X_program_id,--null
SYSDATE,
null, --language,
null, --description,
l_file_name,
x_media_id);
INSERT INTO fnd_lobs (
file_id,
File_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
language,
oracle_charset,
file_format)
VALUES
(x_media_id,
l_file_name, -- Changed p_file_name to l_file_name
l_file_content_type, -- Changed hardcoded value to l_file_content_type
sysdate,
null,
null,
null,
l_blob_data,
null,
null,
'binary');
INSERT INTO fnd_attached_documents (attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
seq_num,
entity_name,
pk1_value,
pk2_value,
pk3_value,
pk4_value,
pk5_value,
automatically_added_flag,
program_application_id,
program_id,
program_update_date,
request_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
column1)
VALUES
(fnd_attached_documents_s.nextval,
document_id_tmp,
sysdate,
1, --NVL(X_created_by,0),
sysdate,
1, --NVL(X_created_by,0),
null,-- X_last_update_login,
10,
l_entity_name,
to_char(p_document_id),
to_char(p_revision_number),
-- null,
l_draft_id, -- CLM
null,
null,
'N',
null,
null,
sysdate,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null);
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, x_progress);
/* SELECT file_data into l_document
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdl
WHERE fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
and fdl.document_id = fad.document_id and fdl.document_id = fd.document_id and fd.media_id = fl.file_id
and fad.entity_name = l_entity_name and fdl.language = l_language
and fl.file_name = l_filename ;*/
SELECT file_data into l_document
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdl
WHERE fad.pk1_value = to_char(l_document_id)
and fad.pk2_value = to_char(l_revision_number)
and fdl.document_id = fad.document_id
and fdl.document_id = fd.document_id
and fd.media_id = fl.file_id
and fad.entity_name = l_entity_name
and fdl.language = l_language
and fl.file_name = l_filename ;
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, 'Fetching from PO_HEAD/PO_REL....');
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, 'Fetching from OKC_CONTRACT_DOC entity.....');
SELECT file_data into l_document
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdl
WHERE fad.pk2_value = to_char(l_document_id)
and fad.pk3_value = to_char(l_revision_number)
and fdl.document_id = fad.document_id
and fdl.document_id = fd.document_id
and fd.media_id = fl.file_id
and fad.entity_name = 'OKC_CONTRACT_DOCS'
and fdl.language = l_language
and fl.file_name = l_filename ;
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, 'Document Length : '||l_document_length);
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, x_progress);
SELECT file_data into l_document
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdl
WHERE fad.pk2_value = to_char(l_document_id) and fad.pk3_value = to_char(l_revision_number)
and fdl.document_id = fad.document_id and fdl.document_id = fd.document_id and fd.media_id = fl.file_id
and fad.entity_name = 'OKC_CONTRACT_DOCS' and fdl.language = l_language
and fl.file_name = l_filename
and rownum = 1; -- Bug 10410956
SELECT file_data into l_document
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdl
WHERE fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
and fdl.document_id = fad.document_id and fd.media_id = fl.file_id
and fd.document_id = fdl.document_id
and fad.entity_name = l_entity_name and fl.file_name = l_filename and fdl.language = l_language and rownum = 1;
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, x_progress);
select fl.language_code into l_language
from po_vendor_sites_all pvs, po_headers_all ph, fnd_languages fl
where ph.vendor_site_id = pvs.vendor_site_id
and ph.po_header_id = l_document_id
and pvs.language = fl.nls_language;
select fl.language_code into l_language
from po_vendor_sites_all pvs , po_headers_all ph,
po_releases_all pr, fnd_languages fl
where ph.po_header_id = pr.po_header_id
and pr.po_release_id = l_document_id
and ph.vendor_site_id = pvs.vendor_site_id
and pvs.language = fl.nls_language;
SELECT file_data into l_document
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents_vl fdl
--Begin Edit Akyanama Bug # 13242469
--Changed the below line
From : fad.pk2_value = to_char(l_document_id) and fad.pk3_value = to_char(l_revision_number)
To : fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
WHERE fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
--Replacing 'OKC_CONTRACT_DOCS' in the below line with l_entity_name since PDF with terms and conditions
--is stored in PO_HEAD or PO_REL entity rather than 'OKC_CONTRACT_DOCS'*/
/* and fdl.document_id = fad.document_id and fdl.media_id = fl.file_id and fad.entity_name = l_entity_name --'OKC_CONTRACT_DOCS'
--End Edit Akyanama Bug # 13242469
and fl.file_name = l_filename; --Bug 4047688
SELECT file_data into l_document
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdl
WHERE fad.pk1_value = to_char(l_document_id) and fad.pk2_value = to_char(l_revision_number)
and fdl.document_id = fad.document_id and fd.media_id = fl.file_id and fd.document_id = fdl.document_id
and fad.entity_name = l_entity_name and fl.file_name = l_filename and fdl.language = l_language; */
SELECT file_data into l_document
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdl
WHERE fad.pk1_value = to_char(l_document_id)
and fad.pk2_value = to_char(l_revision_number)
and fdl.document_id = fad.document_id
and fdl.document_id = fd.document_id
and fd.media_id = fl.file_id
and fad.entity_name = l_entity_name
and fdl.language = l_language
and fl.file_name = l_filename ;
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, 'Fetching from PO_HEAD/PO_REL....');
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, 'Fetching from OKC_CONTRACT_DOC entity.....');
SELECT file_data into l_document
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdl
WHERE fad.pk2_value = to_char(l_document_id)
and fad.pk3_value = to_char(l_revision_number)
and fdl.document_id = fad.document_id
and fdl.document_id = fd.document_id
and fd.media_id = fl.file_id
and fad.entity_name = 'OKC_CONTRACT_DOCS'
and fdl.language = l_language
and fl.file_name = l_filename ;
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, 'Document Length : '||l_document_length);
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(l_itemtype, l_itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(l_itemtype, l_itemkey, x_progress);
PO_WF_DEBUG_PKG.insert_debug(l_itemtype, l_itemkey, x_progress);
SELECT fl.file_name, fl.file_content_type, fl.file_data
INTO l_okc_file_name, l_okc_file_content_type, l_okc_file_data
FROM fnd_lobs fl
WHERE fl.file_id = l_okc_file_id;
PO_WF_DEBUG_PKG.insert_debug(l_itemtype, l_itemkey, x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, x_progress);
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, l_progress);
SELECT fl.file_data, fl.file_content_type
INTO l_document, l_filecontent_type
FROM fnd_lobs fl,
fnd_attached_documents fad,
fnd_documents fd,
fnd_documents_tl fdl
WHERE fad.pk1_value = to_char(l_document_id)
and fad.pk2_value = to_char(l_revision_number)
and fad.entity_name = l_entity_name
and fdl.document_id = fad.document_id
and fdl.document_id = fd.document_id
and fdl.language = l_language
--Bug 5017976 selecting media_id from fd instead of fdl
and fd.media_id = fl.file_id
and fl.file_name = l_filename;
PO_WF_DEBUG_PKG.INSERT_DEBUG(l_itemtype, l_itemkey, l_progress ||' '|| SQLERRM);
PO_WF_DEBUG_PKG.insert_debug(p_itemtype, p_itemkey, l_progress);
select nvl(psp.max_attachment_size, 0)
into l_max_attachment_size
from po_system_parameters psp;
PO_WF_DEBUG_PKG.insert_debug(p_itemtype, p_itemkey, l_progress);
PO_WF_DEBUG_PKG.insert_debug(p_itemtype, p_itemkey, l_progress);
select 'Y' into l_attachments_exist from dual
where exists
(
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where (ad.pk1_value=to_char((select po_header_id from po_releases_all
where po_release_id=p_document_id
))and ad.entity_name='PO_HEADERS')
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
UNION ALL
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where (
(ad.pk1_value=to_char(p_document_id) and ad.entity_name='PO_RELEASES'))
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
UNION ALL
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where (
(ad.pk1_value=to_char((select pha.vendor_id
from po_headers_all pha,po_releases_all pra
where pra.po_release_id=p_document_id
and pha.po_header_id=pra.po_header_id
)) and ad.entity_name='PO_VENDORS'))
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
Union ALL
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where (
(ad.pk1_value in (select po_line_id from po_line_locations_all
where po_release_id=p_document_id
and shipment_type='BLANKET'
) and ad.entity_name='PO_LINES'))
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
union all
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where (
(ad.pk1_value in (select line_location_id from po_line_locations_all
where po_release_id=p_document_id
and shipment_type='BLANKET'
) and ad.entity_name='PO_SHIPMENTS'))
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
union all
/*bug 13528070*/
select /*+ leading(AD) */ fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where ( /*bug 13528070*/
(ad.pk2_value in (select /*+ push_subq no_unnest */ pl.item_id
from po_lines_all pl, po_line_locations_all pll
where pll.po_release_id=p_document_id
and pll.shipment_type='BLANKET'
and pll.po_line_id=pl.po_line_id
and pl.item_id is not null
) AND ad.entity_name='MTL_SYSTEM_ITEMS'))
/*bug 13528070*/
and to_char(PO_COMMUNICATION_PVT.getInventoryOrgId())=ad.pk1_value --Bug 4673653 Use Inventory OrgId
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
);
select 'Y' into l_attachments_exist from dual
where exists
(
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where (ad.pk1_value=to_char(p_document_id) and ad.entity_name='PO_HEADERS')
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
Union all
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where (
(ad.pk1_value=to_char((select vendor_id from po_headers_all
where po_header_id=p_document_id)) and ad.entity_name='PO_VENDORS'))
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
Union all
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where ( /*bug 13528070 Changing to to_char*/
(ad.pk1_value in (select to_char(po_line_id) from po_lines_all
where po_header_id=p_document_id
) and ad.entity_name='PO_LINES'))
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
Union ALL
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where (
(ad.pk1_value in (select from_header_id from po_lines_all
where po_header_id=p_document_id
and from_header_id is not null
) and ad.entity_name='PO_HEADERS'))
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
Union ALL
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where (
(ad.pk1_value in (select from_line_id from po_lines_all
where po_header_id=p_document_id
and from_line_id is not null
) and ad.entity_name='PO_LINES'))
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
Union all
select fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where (
(ad.pk1_value in (select line_location_id from po_line_locations_all
where po_header_id=p_document_id
and shipment_type in ('PRICE BREAK','STANDARD','PREPAYMENT') --
) and ad.entity_name='PO_SHIPMENTS'))
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
Union ALL
/*bug 13528070*/
select /*+ leading(AD) */ fl.file_name
from fnd_documents d,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af,
fnd_lobs fl
where ( /*bug 13528070*/
(ad.pk2_value in (select /*+ push_subq no_unnest */ item_id from po_lines_all
where po_header_id=p_document_id
and item_id is not null
) and ad.entity_name='MTL_SYSTEM_ITEMS'))
/*bug 13528070*/
and to_char(PO_COMMUNICATION_PVT.getInventoryOrgId())=ad.pk1_value --Bug 4673653 Use Inventory OrgId
and d.document_id = ad.document_id
and dcu.category_id = d.category_id
and dcu.attachment_function_id = af.attachment_function_id
and d.datatype_id=6
and af.function_name='PO_PRINTPO'
and d.media_id=fl.file_id
and dcu.enabled_flag = 'Y'
);
SELECT org_id INTO l_org_id FROM po_headers_all WHERE po_header_id = p_document_id;
SELECT TYPE_NAME into PO_COMMUNICATION_PVT.g_documentType FROM PO_DOCUMENT_TYPES_TL
WHERE document_type_code = p_document_type and document_subtype = p_document_subtype and language = USERENV('LANG');
/* SELECT hle.name, vn.vendor_name, ph.segment1, ph.change_summary, ph.vendor_id, ph.currency_code
INTO po_communication_pvt.g_buyer_org, l_supp_org, l_po_number, l_change_summary, l_vendor_id, g_current_currency_code
FROM hr_all_organization_units hle, po_vendors vn, po_headers_all ph
WHERE to_char(hle.organization_id) = (select org_information2 from hr_organization_information where
org_information_context = 'Operating Unit Information' and organization_id = ph.org_id) AND vn.vendor_id = ph.vendor_id
AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num; */
SELECT vn.vendor_name, ph.segment1, ph.change_summary, ph.vendor_id, ph.currency_code
INTO l_supp_org, l_po_number, l_change_summary, l_vendor_id, g_current_currency_code
FROM po_vendors vn, po_headers_all ph
WHERE vn.vendor_id = ph.vendor_id
AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num;
SELECT count(distinct(plla.SHIP_TO_LOCATION_ID)) INTO PO_COMMUNICATION_PVT.g_dist_shipto_count
FROM po_line_locations_all plla
WHERE plla.po_header_id = p_document_id
AND NVL(plla.payment_type, 'NONE') NOT IN ('ADVANCE', 'DELIVERY'); --
SELECT vn.vendor_name, ph.segment1, ph.change_summary, ph.vendor_id, ph.currency_code
INTO l_supp_org, l_po_number, l_change_summary, l_vendor_id, g_current_currency_code
FROM po_vendors vn, po_headers_archive_all ph
WHERE vn.vendor_id = ph.vendor_id
AND ph.po_header_id = p_document_id AND ph.revision_num = p_revision_num;
SELECT count(distinct(plla.SHIP_TO_LOCATION_ID)) INTO PO_COMMUNICATION_PVT.g_dist_shipto_count
FROM po_line_locations_archive_all plla
WHERE plla.po_header_id = p_document_id
and plla.revision_num = p_revision_num
AND NVL(plla.payment_type, 'NONE') NOT IN ('ADVANCE', 'DELIVERY'); --
select org_id into l_org_id from po_headers_all where po_header_id = p_document_id ;
insert into PO_COMMUNICATION_GT(po_header_id, po_release_id, revision_number, format_mask)
values(p_document_id, null, p_revision_num, PO_COMMUNICATION_PVT.getFormatMask);
SELECT po_header_id INTO PO_COMMUNICATION_PVT.g_release_header_id FROM po_releases_all WHERE po_release_id = p_document_id;
SELECT ph.vendor_id, ph.currency_code INTO l_vendor_id, g_current_currency_code
FROM po_vendors vn, po_headers_all ph
WHERE vn.vendor_id = ph.vendor_id
AND ph.po_header_id = PO_COMMUNICATION_PVT.g_release_header_id ;
SELECT count(distinct(plla.SHIP_TO_LOCATION_ID)) INTO PO_COMMUNICATION_PVT.g_dist_shipto_count
FROM po_line_locations_all plla
WHERE plla.po_release_id = p_document_id;
SELECT count(distinct(plla.SHIP_TO_LOCATION_ID)) INTO PO_COMMUNICATION_PVT.g_dist_shipto_count
FROM po_line_locations_archive_all plla
WHERE plla.po_release_id = p_document_id
and plla.revision_num = p_revision_num;
insert into PO_COMMUNICATION_GT(po_header_id, po_release_id, revision_number, format_mask)
values(null, p_document_id, p_revision_num, PO_COMMUNICATION_PVT.getFormatMask);
SELECT name, gmt_deviation_hours into l_timezone, l_offset from HZ_TIMEZONES_VL where timezone_id = to_number(l_timezone_id);
l_head_short_attachment_query := 'CURSOR( SELECT fds.short_text
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_short_text fds
WHERE ((entity_name=''PO_HEADERS'' AND
pk1_value=to_char(phx.po_header_id))OR
(entity_name = ''PO_RELEASES'' AND
pk1_value = to_char(phx.po_release_id)) OR
(entity_name = ''PO_VENDORS'' AND
pk1_value = to_char(phx.vendor_id)) OR
(entity_name = ''PO_VENDOR_SITES'' AND
pk1_value = to_char(phx.vendor_site_id))) AND -- bug6154354
function_name = ''PO_PRINTPO''
AND fad.media_id = fds.media_id
AND fad.datatype_id=1
order by entity_name, seq_num) AS header_short_text'; --bug6133951
SELECT fad.url web_page --Bug#4958642
FROM fnd_attached_docs_form_vl fad
WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
(fad.entity_name=''PO_RELEASES'' AND fad.pk1_value=to_char(phx.po_release_id)) OR
(fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
(fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
)
AND fad.datatype_id=5
AND fad.function_name=''PO_PRINTPO''
order by fad.entity_name,fad.seq_num) AS header_url_attachments'; --bug6133951
SELECT fad.file_name
FROM fnd_attached_docs_form_vl fad
WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
(fad.entity_name=''PO_RELEASES'' AND fad.pk1_value=to_char(phx.po_release_id)) OR
(fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
(fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
)
AND fad.datatype_id=6
AND fad.function_name=''PO_PRINTPO''
order by fad.entity_name,fad.seq_num) AS header_file_attachments'; --bug6133951
l_head_short_attachment_query := 'CURSOR( SELECT fds.short_text
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_short_text fds
WHERE ((entity_name = ''PO_HEADERS'' AND
pk1_value = to_char(phx.po_header_id)) OR
(entity_name = ''PO_VENDORS'' AND
pk1_value = to_char(phx.vendor_id)) OR
(entity_name = ''PO_VENDOR_SITES'' AND
pk1_value = to_char(phx.vendor_site_id))) AND -- bug6154354
function_name = ''PO_PRINTPO''
AND fad.media_id = fds.media_id
AND fad.datatype_id=1 order by fad.seq_num) AS header_short_text'; --bug6133951
SELECT fad.url web_page --Bug#4958642
FROM fnd_attached_docs_form_vl fad
WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
(fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
(fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
)
AND fad.datatype_id=5
AND fad.function_name=''PO_PRINTPO''
order by fad.entity_name,fad.seq_num) AS header_url_attachments'; --bug6133951
SELECT fad.file_name
FROM fnd_attached_docs_form_vl fad
WHERE ((fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(phx.po_header_id)) OR
(fad.entity_name=''PO_VENDORS'' AND fad.pk1_value=to_char(phx.vendor_id)) OR
(fad.entity_name=''PO_VENDOR_SITES'' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
)
AND fad.datatype_id=6
AND fad.function_name=''PO_PRINTPO''
order by fad.entity_name,fad.seq_num) AS header_file_attachments'; --bug6133951
/*Bug7426541 - Added the clauses to select the attachments from BLANKET 'S header and lines
and CONTRACT 'S header- if there is a source document.
Entity types :- source doc's header -> 'PO_HEADERS' source doc's line -> 'PO_IN_GA_LINES'*/
l_line_short_attachment_query := ' CURSOR( SELECT plx.po_line_id , fds.short_text
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_short_text fds
WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
OR
(fad.entity_name=''MTL_SYSTEM_ITEMS'' AND
fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND --Bug6139548
fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)
OR
(fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
AND plx.from_header_id IS NOT NULL)
OR
(fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
AND plx.from_line_id IS NOT NULL)
OR
(fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.CONTRACT_ID)
AND plx.CONTRACT_ID IS NOT NULL)
) AND
function_name = ''PO_PRINTPO''
AND fad.media_id = fds.media_id
AND fad.datatype_id=1 order by fad.seq_num) AS line_short_text'; --bug6133951
/*Bug7426541 - Added the clauses to select the attachments from BLANKET 'S header and lines
and CONTRACT 'S header- if there is a source document.
Entity types :- source doc's header -> 'PO_HEADERS' source doc's line -> 'PO_IN_GA_LINES'*/
l_line_url_attachment_query := 'CURSOR(
SELECT fad.url web_page --Bug#4958642
FROM fnd_attached_docs_form_vl fad
WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
OR
(fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
AND plx.from_header_id IS NOT NULL)
OR
(fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
AND plx.from_line_id IS NOT NULL)
OR
(fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.contract_id)
AND plx.contract_id IS NOT NULL)
OR
(fad.entity_name=''MTL_SYSTEM_ITEMS'' AND
fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND
fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)
)
AND fad.datatype_id=5
AND fad.function_name=''PO_PRINTPO'' order by fad.seq_num) AS line_url_attachments'; --bug6133951
/*Bug7426541 - Added the clauses to select the attachments from BLANKET 'S header and lines
and CONTRACT 'S header- if there is a source document.
Entity types :- source doc's header -> 'PO_HEADERS' source doc's line -> 'PO_IN_GA_LINES'*/
l_line_file_attachment_query := 'CURSOR(
SELECT fad.file_name
FROM fnd_attached_docs_form_vl fad
WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
OR
(fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
AND plx.from_header_id IS NOT NULL)
OR
(fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
AND plx.from_line_id IS NOT NULL)
OR
(fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.contract_id)
AND plx.contract_id IS NOT NULL)
OR
(fad.entity_name=''MTL_SYSTEM_ITEMS'' AND
fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND
fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)
)
AND fad.datatype_id=6
AND fad.function_name=''PO_PRINTPO''
order by fad.seq_num) AS line_file_attachments'; --bug6133951
l_shipment_short_attach_query := 'CURSOR( SELECT pllx.line_location_id, fds.short_text
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_short_text fds
WHERE entity_name = ''PO_SHIPMENTS'' AND
pk1_value = to_char(pllx.line_location_id) AND
function_name = ''PO_PRINTPO''
AND fad.media_id = fds.media_id
AND fad.datatype_id=1
order by fad.seq_num) AS line_loc_short_text'; --bug6133951
SELECT fad.url web_page --Bug#4958642
FROM fnd_attached_docs_form_vl fad
WHERE fad.entity_name=''PO_SHIPMENTS''
AND fad.pk1_value=to_char(pllx.line_location_id)
AND fad.datatype_id=5
AND fad.function_name=''PO_PRINTPO''
order by fad.seq_num ) AS line_loc_url_attachments'; --bug6133951
SELECT fad.file_name
FROM fnd_attached_docs_form_vl fad
WHERE fad.entity_name=''PO_SHIPMENTS''
AND fad.pk1_value=to_char(pllx.line_location_id)
AND fad.datatype_id=6
AND fad.function_name=''PO_PRINTPO''
order by fad.seq_num) AS line_loc_file_attachments'; --bug6133951
l_tax_name_query:= 'CURSOR(SELECT zl.tax_rate_id tax_code_id, zl.tax_rate_code tax_name
, zl.TAX_RATE
, zl.TAX_RATE_TYPE
, zl.ENTITY_CODE
, zl.EVENT_CLASS_CODE
, zl.TRX_ID
, zl.APPLICATION_ID
, zl.TRX_LEVEL_TYPE
, zl.TRX_LINE_ID
, zl.TRX_NUMBER
, zl.TRX_LINE_NUMBER
, zl.TAX_LINE_NUMBER
, zl.TAX_REGIME_CODE
, zl.TAX
, zl.TAX_APPORTIONMENT_LINE_NUMBER
, zl.UNIT_PRICE
, zl.LINE_AMT
, zl.TRX_LINE_QUANTITY
, zl.UNROUNDED_TAXABLE_AMT
, zl.UNROUNDED_TAX_AMT
, zl.TAX_CURRENCY_CODE
, zl.TAX_AMT
, zl.TAX_AMT_TAX_CURR
, zl.TAX_AMT_FUNCL_CURR
, zl.TAXABLE_AMT_TAX_CURR
, zl.TAXABLE_AMT_FUNCL_CURR
, zl.TAX_LINE_ID
, zl.TAX_STATUS_CODE
, zl.TAX_JURISDICTION_CODE
, zl.TAXABLE_AMT
, zl.TAX_TYPE_CODE
FROM zx_lines zl
WHERE zl.application_id = 201
AND zl.entity_code IN (''PURCHASE_ORDER'',''RELEASE'')
AND zl.event_class_code IN (''PO_PA'',''RELEASE'')
AND zl.trx_id = decode(zl.event_class_code, ''RELEASE'', pllx.po_release_id, pllx.po_header_id)
AND zl.trx_line_id = pllx.line_location_id
) AS tax_names';
l_xml_message_query :='CURSOR (SELECT message_name message, message_text text FROM fnd_new_messages WHERE message_name in (
''PO_WF_NOTIF_REVISION'',
''PO_WF_NOTIF_VENDOR_NO'',
''PO_WF_NOTIF_PAYMENT_TERMS'',
''PO_WF_NOTIF_FREIGHT_TERMS'',
''PO_WF_NOTIF_FOB'',
''PO_WF_NOTIF_SHIP_VIA'',
''PO_WF_NOTIF_CONFIRM_TO_TELE'',
''PO_WF_NOTIF_REQUESTER_DELIVER'',
''PO_WF_NOTIF_DESCRIPTION'',
''PO_WF_NOTIF_TAX'',
''PO_WF_NOTIF_UOM'',
''PO_WF_NOTIF_UNIT_PRICE'',
''PO_WF_NOTIF_QUANTITY'',
''PO_WF_NOTIF_PURCHASE_ORDER'',
''PO_WF_NOTIF_BUYER'',
''PO_WF_NOTIF_AMOUNT'',
''PO_WF_NOTIF_EFFECTIVE_DATE'',
''PO_WF_NOTIF_HEADER_NOTE'',
''PO_WF_NOTIF_LINE_NUMBER'',
''PO_WF_NOTIF_LINE_PAYITEM_NUM'', --
''PO_WF_NOTIF_MULTIPLE'',
''PO_WF_NOTIF_PART_NO_DESC'',
''PO_WF_NOTIF_SUPPLIER_ITEM'',
''PO_WF_NOTIF_TOTAL'',
''PO_WF_NOTIF_NOTE'',
''PO_FO_PACKING_INSTRUCTION'',
''PO_FO_CUST_PO_NUMBER'',
''PO_FO_CUST_ITEM_DESC'',
''PO_FO_LINE_NUMBER'',
''PO_FO_SHIP_NUMBER'',
''PO_FO_AMOUNT_BASED'',
''PO_FO_CONTRACTOR_NAME'',
''PO_FO_START_DATE'',
''PO_FO_END_DATE'',
''PO_FO_WORK_SCHEDULE'',
''PO_FO_SHIFT_PATTERN'',
''PO_FO_PRICE_DIFFERENTIALS'',
''PO_FO_DELIVER_TO_LOCATION'',
''PO_FO_EFFECTIVE_START_DATE'',
''PO_FO_AMOUNT_AGREED'',
''PO_FO_ADVANCE'', --
''PO_FO_RETAINAGE_RATE'', --
''PO_FO_MAX_RETAINAGE_AMOUNT'', --
''PO_FO_PROGRESS_PAYMENT_RATE'', --
''PO_FO_RECOUPMENT_RATE'', --
''PO_FO_PRICE_BREAK'',
''PO_FO_CHARGE_ACCOUNT'',
''PO_FO_CONTRACTOR'',
''PO_FO_CONTACT_NAME'',
''PO_FO_TELEPHONE'',
''PO_FO_FAX'',
''PO_FO_NAME'',
''PO_FO_TITLE'',
''PO_FO_DATE'',
''PO_FO_REVISION'',
''PO_FO_AMENDMENT'',
''PO_FO_SHIP_METHOD'',
''PO_FO_SHIPPING_INSTRUCTION'',
''PO_FO_DRAFT'',
''PO_FO_PROPRIETARY_INFORMATION'',
''PO_FO_TRANSPORTAION_ARRANGED'',
''PO_FO_DELIVER_TO_LOCATION'',
''PO_FO_NO'',
''PO_FO_COMPANY'',
''PO_FO_SUBMIT_RESPONSE'',
''PO_FO_EMAIL'',
''PO_WF_NOTIF_EXPIRES_ON'',
''PO_FO_TEST'',
''PO_FO_ORG_AGR_ASS'',
''PO_FO_EFFECTIVE_END_DATE'',
''PO_FO_PURCHASING_ORGANIZATION'',
''PO_FO_PURCHASING_SUPPLIER_SITE'',
''PO_FO_TRANSPORTATION_ARRANGED'',
''PO_WF_NOTIF_ADDRESS'',
''PO_WF_NOTIF_ORDER'',
''PO_WF_NOTIF_ORDER_DATE'',
''PO_FO_VENDOR'',
''PO_FO_SHIP_TO'',
''PO_FO_BILL_TO'',
''PO_FO_CONFIRM_NOT_DUPLICATE'',
''PO_FO_AGREEMENT_CANCELED'',
''PO_FO_FORMAL_ACCEPT'',
''PO_FO_TYPE'',
''PO_FO_REVISION_DATE'',
''PO_FO_REVISED_BY'',
''PO_FO_PRICES_EXPRESSED'',
''PO_FO_NOTES'',
''PO_WF_NOTIF_PREPARER'',
''PO_FO_SUPPLIER_CONFIGURATION'',
''PO_FO_DELIVER_DATE_TIME'',
''PO_FO_LINE_REF_BPA'',
''PO_FO_LINE_REF_CONTRACT'',
''PO_FO_LINE_SUPPLIER_QUOTATION'',
''PO_FO_USE_SHIP_ADDRESS_TOP'',
''PO_FO_LINE_CANCELED'',
''PO_FO_ORIGINAL_QTY_ORDERED'',
''PO_FO_QUANTITY_CANCELED'',
''PO_FO_SHIPMENT_CANCELED'',
''PO_FO_ORIGINAL_SHIPMENT_QTY'',
''PO_FO_CUSTOMER_ACCOUNT_NO'',
''PO_FO_RELEASE_CANCELED'',
''PO_FO_PO_CANCELED'',
''PO_FO_TOTAL'',
''PO_FO_SUPPLIER_ITEM'',
''PO_FO_ORIGINAL_AMOUNT_ORDERED'',
''PO_FO_AMOUNT_CANCELED'',
''PO_FO_UN_NUMBER'',
''PO_WF_NOTIF_PROMISED_DATE'',
''PO_WF_NOTIF_NEEDBY_DATE'',
''PO_FO_HAZARD_CLASS'',
''PO_FO_PAGE'',
''PO_FO_REFERENCE_DOCUMENTS'',
''PO_FO_PAYITEM_CANCELED'', --
''PO_FO_ORIGINAL_PAYITEM_QTY'', --
''PO_FO_PAYITEM_QTY_CANCELED'', --
''PO_FO_ORIGINAL_PAYITEM_AMT'', --
''PO_FO_MODIFIER_TYPE'', --Enhanced Pricing
''PO_FO_BLANKET'', --Enhanced Pricing
''PO_FO_BLANKET_PRICE_STRUCT'', --Enhanced Pricing
''PO_FO_MODIFIER_DESC'', --Enhanced Pricing
''PO_FO_RATE'', --Enhanced Pricing
''PO_FO_ADJUSTED_AMT'', --Enhanced Pricing
''PO_FO_RATE_APP_METHOD'', --Enhanced Pricing
''PO_FO_LIST_LINE_PRICE'', --Enhanced Pricing
''PO_FO_PAYITEM_AMT_CANCELED'', --
''PO_FO_USE_SHIP_ADDRESS'' --Bug 9855114
) AND application_id = 201 AND language_code = '''|| userenv('LANG') ||''') AS message';
', CURSOR(SELECT del.* FROM po_line_locations_xml del'
|| ' WHERE del.po_line_id = plx.po_line_id AND del.payment_type = ''DELIVERY'')'
|| ' AS line_delivery ';
', CURSOR(SELECT adv.* FROM po_distribution_xml adv, po_line_locations_xml pllx2'
|| ' WHERE pllx2.po_line_id = plx.po_line_id AND pllx2.payment_type = ''ADVANCE'''
|| ' AND adv.line_location_id = pllx2.line_location_id) AS line_advance_distributions,'
|| ' CURSOR(SELECT del.*, CURSOR(SELECT deldist.* FROM po_distribution_xml deldist'
|| ' WHERE deldist.line_location_id = del.line_location_id) AS distributions'
|| ' FROM po_line_locations_xml del WHERE del.po_line_id = plx.po_line_id'
|| ' AND del.payment_type = ''DELIVERY'') AS line_delivery ';
', CURSOR(SELECT del.* FROM po_line_locations_archive_xml del'
|| ' WHERE del.po_line_id = plx.po_line_id AND del.payment_type = ''DELIVERY'''
|| ' AND del.revision_num = (SELECT /*+ push_subq no_unnest */ max(dela.revision_num)'
|| ' FROM po_line_locations_archive_all dela WHERE del.line_location_id = '
|| ' dela.line_location_id AND del.revision_num <= pcgt.revision_number) '
|| ' ) AS line_delivery ';
', CURSOR(SELECT adv.* FROM po_distribution_archive_xml adv,'
|| ' po_line_locations_archive_xml pllx2 WHERE pllx2.po_line_id = plx.po_line_id'
|| ' AND pllx2.payment_type = ''ADVANCE'' AND adv.line_location_id = pllx2.line_location_id'
|| ' AND adv.revision_num = (SELECT max(adva.revision_num)'
|| ' FROM po_distributions_archive_all adva WHERE adv.po_distribution_id ='
|| ' adv.po_distribution_id AND adva.revision_num <= pcgt.revision_number))'
|| ' AS line_advance_distributions, CURSOR(SELECT del.*, CURSOR(SELECT deldist.*'
|| ' FROM po_distribution_xml deldist WHERE deldist.line_location_id = del.line_location_id'
|| ' AND deldist.revision_num = (SELECT max(deldista.revision_num)'
|| ' FROM po_distributions_archive_all deldista WHERE deldist.po_distribution_id ='
|| ' deldista.po_distribution_id AND deldista.revision_num <= pcgt.revision_number))'
|| ' AS distributions FROM po_line_locations_xml del WHERE del.po_line_id = plx.po_line_id'
|| ' AND del.payment_type = ''DELIVERY'' AND del.revision_num = ('
|| ' SELECT /*+ push_subq no_unnest */ max(dela.revision_num) FROM po_line_locations_archive_all dela'
|| ' WHERE del.line_location_id = dela.line_location_id'
|| ' AND del.revision_num <= pcgt.revision_number)) AS line_delivery';
l_agreement_assign_query := ' CURSOR( select rownum, PO_COMMUNICATION_PVT.GETOPERATIONINFO(PGA.PURCHASING_ORG_ID) OU_NAME,
PO_COMMUNICATION_PVT.getVendorAddressLine1(PGA.vendor_site_id) VENDOR_ADDRESS_LINE1,
PO_COMMUNICATION_PVT.getVendorAddressLine2() VENDOR_ADDRESS_LINE2,
PO_COMMUNICATION_PVT.getVendorAddressLine3() VENDOR_ADDRESS_LINE3,
PO_COMMUNICATION_PVT.getVendorCityStateZipInfo() VENDOR_CITY_STATE_ZIP,
PO_COMMUNICATION_PVT.getVendorCountry() VENDOR_COUNTRY
FROM po_ga_org_assignments PGA
WHERE PGA.ENABLED_FLAG = ''Y'' and PGA.PO_HEADER_ID = PHX.PO_HEADER_ID) as organization_details ';
l_arc_agreement_assign_query := ' CURSOR( select rownum, PO_COMMUNICATION_PVT.GETOPERATIONINFO(PGA.PURCHASING_ORG_ID) OU_NAME,
PO_COMMUNICATION_PVT.getVendorAddressLine1(PGA.vendor_site_id) VENDOR_ADDRESS_LINE1,
PO_COMMUNICATION_PVT.getVendorAddressLine2() VENDOR_ADDRESS_LINE2,
PO_COMMUNICATION_PVT.getVendorAddressLine3() VENDOR_ADDRESS_LINE3,
PO_COMMUNICATION_PVT.getVendorCityStateZipInfo() VENDOR_CITY_STATE_ZIP,
PO_COMMUNICATION_PVT.getVendorCountry() VENDOR_COUNTRY
FROM po_ga_org_assignments_archive PGA
WHERE PGA.ENABLED_FLAG = ''Y'' and PGA.PO_HEADER_ID = PHX.PO_HEADER_ID) as organization_details ';
l_price_modifier_query1 := 'CURSOR (SELECT pax1.* FROM PO_PRICE_ADJUSTMENTS_XML pax1 '
|| 'WHERE pax1.po_header_id = plx.po_header_id AND pax1.po_line_id = plx.po_line_id '
|| 'AND pax1.parent_adjustment_id is null order by pax1.pricing_group_sequence) AS PRICE_MODIFIERS';
l_price_modifier_query2 := 'CURSOR (SELECT pax2.* FROM PO_PRICE_ADJUSTMENTS_XML pax2 '
|| 'WHERE pax2.po_header_id = plx.from_header_id AND pax2.po_line_id = plx.from_line_id '
|| 'AND pax2.parent_adjustment_id is null order by pax2.pricing_group_sequence) AS ADD_PRICE_MODIFIERS';
l_price_modifier_query3 := 'CURSOR (SELECT pha.segment1 ponum FROM po_headers_all pha '
|| 'WHERE pha.po_header_id = plx.from_header_id) AS ADD_PRICE_PONUM';
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type,
PO_COMMUNICATION_PVT.getCoverMessage() cover_message,PO_COMMUNICATION_PVT.getTimezone() timezone,
PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile,
PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '
|| l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||'
FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
SELECT count(*) into l_count FROM po_lines_all WHERE po_header_id = p_document_id;
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile,PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '||
l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||',
CURSOR(SELECT plx.*, '|| l_adv_amount_query ||' CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER,MIN_MULTIPLIER, MAX_MULTIPLIER FROM po_price_differentials_v
WHERE entity_type='''|| l_eventType ||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
'|| l_line_short_attachment_query || ',' || l_price_modifier_query1 || ',' || l_price_modifier_query2 || ',' || l_price_modifier_query3
|| ',' || l_line_url_attachment_query || ',' || l_line_file_attachment_query ||' FROM po_lines_xml plx
WHERE plx.po_header_id = phx.po_header_id and not exists
(select ''x'' from po_lines_archive_all plaa where
plaa.po_line_id = plx.po_line_id and
plaa.cancel_flag= ''Y'' and plaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) order by plx.line_num) AS lines
FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
SELECT count(*) into l_count FROM po_line_locations_all WHERE po_header_id = p_document_id;
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
PO_COMMUNICATION_PVT.IsDocumentSigned( PO_COMMUNICATION_PVT.getDocumentId()) Signed,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile, PO_COMMUNICATION_PVT.getWithTerms() With_Terms , '||
' PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , ' || l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||',
CURSOR(SELECT plx.*, '|| l_adv_amount_query ||' CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER, MIN_MULTIPLIER,MAX_MULTIPLIER FROM po_price_differentials_v
WHERE entity_type='''|| l_eventType ||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
'|| l_line_short_attachment_query || ',' || l_price_modifier_query1 || ',' || l_price_modifier_query2 || ',' || l_price_modifier_query3 || ',' || l_line_url_attachment_query || ',' || l_line_file_attachment_query ||',
CURSOR(SELECT pllx.*,';
l_xml_query := l_xml_query ||'CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MIN_MULTIPLIER, MAX_MULTIPLIER FROM po_price_differentials_v
WHERE entity_type=''PRICE BREAK'' AND entity_id = pllx.line_location_id and enabled_flag=''Y'') AS price_break,';
(select ''x'' from po_line_locations_archive_all pllaa where pllaa.line_location_id = pllx.line_location_id
and pllaa.cancel_flag=''Y'' and pllaa.revision_num < PO_COMMUNICATION_PVT.getRevisionNum()) order by pllx.shipment_num ) AS line_locations' || l_complex_lloc_query || '
FROM po_lines_xml plx
WHERE plx.po_header_id = phx.po_header_id and not exists
(select ''x'' from po_lines_archive_all plaa where
plaa.po_line_id = plx.po_line_id and
plaa.cancel_flag= ''Y'' and plaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) order by plx.line_num) AS lines
FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
SELECT count(*) into l_count FROM po_distributions_all WHERE po_header_id = p_document_id;
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile,PO_COMMUNICATION_PVT.getWithTerms() With_Terms , '||
' PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , ' || l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||',
CURSOR(SELECT plx.*, '|| l_adv_amount_query ||' CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER FROM po_price_differentials_v
WHERE entity_type=''PO LINE'' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
'|| l_line_short_attachment_query || ',' || l_price_modifier_query1 || ',' || l_price_modifier_query2 || ',' || l_price_modifier_query3 || ',' || l_line_url_attachment_query || ',' || l_line_file_attachment_query ||',
CURSOR(SELECT pllx.*, ' || l_tax_name_query || ',' || l_shipment_short_attach_query || ',' || l_shipment_url_attach_query || ',' || l_shipment_file_attach_query ||',
CURSOR(SELECT pdx.* FROM po_distribution_xml pdx WHERE pdx.po_header_id = phx.po_header_id and pdx.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID) AS distributions
FROM po_line_locations_xml pllx WHERE pllx.po_line_id = plx.po_line_id AND NVL(pllx.payment_type,''NONE'') NOT IN (''ADVANCE'',''DELIVERY'') and not exists
(select ''x'' from po_line_locations_archive_all pllaa where pllaa.line_location_id = pllx.line_location_id
and pllaa.cancel_flag=''Y'' and pllaa.revision_num < PO_COMMUNICATION_PVT.getRevisionNum()) order by pllx.shipment_num ) AS line_locations' || l_complex_dist_query || '
FROM po_lines_xml plx WHERE plx.po_header_id = phx.po_header_id and not exists
(select ''x'' from po_lines_archive_all plaa where
plaa.po_line_id = plx.po_line_id and
plaa.cancel_flag= ''Y'' and plaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) order by plx.line_num) AS lines
FROM PO_HEADERS_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND
phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
l_price_modifier_query1 := 'CURSOR (SELECT pax1.* FROM PO_PRICE_ADJS_ARCHIVE_XML pax1 '
|| 'WHERE pax1.po_header_id = plx.po_header_id AND pax1.po_line_id = plx.po_line_id '
|| 'AND pax1.revision_num= PO_COMMUNICATION_PVT.getRevisionNum() '
|| 'AND pax1.parent_adjustment_id is null order by pax1.pricing_group_sequence) AS PRICE_MODIFIERS';
l_price_modifier_query2 := 'CURSOR (SELECT pax2.* FROM PO_PRICE_ADJS_ARCHIVE_XML pax2 '
|| 'WHERE pax2.po_header_id = plx.from_header_id AND pax2.po_line_id = plx.from_line_id '
|| 'AND pax2.revision_num= PO_COMMUNICATION_PVT.getRevisionNum() AND '
|| 'pax2.parent_adjustment_id is null order by pax2.pricing_group_sequence) AS ADD_PRICE_MODIFIERS';
l_price_modifier_query3 := 'CURSOR (SELECT pha.segment1 ponum FROM po_headers_archive_all pha '
|| 'WHERE pha.po_header_id = plx.from_header_id) AS ADD_PRICE_PONUM';
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type,
PO_COMMUNICATION_PVT.getCoverMessage() cover_message,PO_COMMUNICATION_PVT.getTimezone() timezone,
PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag()
test_flag,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile, PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '||
l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||'
FROM PO_HEADERS_ARCHIVE_XML phx WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
SELECT count(*) into l_count FROM po_lines_archive_all WHERE po_header_id = p_document_id;
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile,PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '||
l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||',
CURSOR(SELECT plx.*, '|| l_adv_amount_query ||' CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER,MIN_MULTIPLIER, MAX_MULTIPLIER FROM po_price_differentials_v
WHERE entity_type='''|| l_eventType ||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
'|| l_line_short_attachment_query || ',' || l_price_modifier_query1 || ',' || l_price_modifier_query2 || ',' || l_price_modifier_query3
|| ',' || l_line_url_attachment_query || ',' || l_line_file_attachment_query ||' FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
AND plx.REVISION_NUM = (select /*+ push_subq no_unnest */ max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
and pla.revision_num <= pcgt.revision_number ) and ''Y'' = decode(nvl(plx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(plx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by plx.line_num) AS lines
FROM PO_HEADERS_ARCHIVE_XML phx, PO_COMMUNICATION_GT pcgt
WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId()
AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
SELECT count(*) into l_count FROM po_line_locations_archive_all WHERE po_header_id = p_document_id;
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
PO_COMMUNICATION_PVT.IsDocumentSigned( PO_COMMUNICATION_PVT.getDocumentId()) Signed,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile, PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '||
l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||',
CURSOR(SELECT plx.*, '|| l_adv_amount_query ||' CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER, MIN_MULTIPLIER,MAX_MULTIPLIER FROM po_price_differentials_v
WHERE entity_type='''|| l_eventType ||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
'|| l_line_short_attachment_query || ',' || l_price_modifier_query1 || ',' || l_price_modifier_query2 || ',' || l_price_modifier_query3 || ',' || l_line_url_attachment_query || ',' || l_line_file_attachment_query ||',
CURSOR(SELECT pllx.*,';
l_xml_query := l_xml_query ||'CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MIN_MULTIPLIER, MAX_MULTIPLIER FROM po_price_differentials_v
WHERE entity_type=''PRICE BREAK'' AND entity_id = pllx.line_location_id and enabled_flag=''Y'') AS price_break,';
and pllx.revision_num = (SELECT /*+ push_subq no_unnest */ MAX(plla.REVISION_NUM) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla
where plla.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID and plla.revision_num <= pcgt.revision_number )
and ''Y'' = decode(nvl(pllx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(pllx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by pllx.shipment_num ) AS line_locations' || l_complex_lloc_query || '
FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
AND plx.REVISION_NUM = (SELECT /*+ push_subq no_unnest */ max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
and pla.revision_num <= pcgt.revision_number ) and ''Y'' = decode(nvl(plx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(plx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by plx.line_num) AS lines
FROM PO_HEADERS_ARCHIVE_XML phx, PO_COMMUNICATION_GT pcgt WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
SELECT count(*) into l_count FROM po_distributions_archive_all WHERE po_header_id = p_document_id;
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile, PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '||
l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||',
CURSOR(SELECT plx.*, '|| l_adv_amount_query ||' CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER FROM po_price_differentials_v
WHERE entity_type=''PO LINE'' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
'|| l_line_short_attachment_query || ',' || l_price_modifier_query1 || ',' || l_price_modifier_query2 || ',' || l_price_modifier_query3 || ',' || l_line_url_attachment_query || ',' || l_line_file_attachment_query ||',
CURSOR(SELECT pllx.*, ' || l_tax_name_query || ',' || l_shipment_short_attach_query || ',' || l_shipment_url_attach_query || ',' || l_shipment_file_attach_query ||',
CURSOR(SELECT pdx.* FROM po_distribution_archive_xml pdx WHERE pdx.po_header_id = phx.po_header_id and pdx.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID
and pdx.REVISION_NUM = (SELECT MAX(pda.REVISION_NUM) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL pda
WHERE pda.PO_DISTRIBUTION_ID = pdx.PO_DISTRIBUTION_ID AND pda.REVISION_NUM <= pcgt.revision_number ) ) AS distributions
FROM PO_LINE_LOCATIONS_ARCHIVE_XML pllx WHERE pllx.po_line_id = plx.po_line_id and SHIPMENT_TYPE in (''BLANKET'',''STANDARD'',''PREPAYMENT'') AND NVL(pllx.payment_type,''NONE'') NOT IN (''ADVANCE'',''DELIVERY'')
and pllx.revision_num = (SELECT /*+ push_subq no_unnest */ MAX(plla.REVISION_NUM) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla
where plla.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID and plla.revision_num <= pcgt.revision_number )
and ''Y'' = decode(nvl(pllx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(pllx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by pllx.shipment_num ) AS line_locations'|| l_complex_dist_query || '
FROM PO_LINES_ARCHIVE_XML plx WHERE plx.po_header_id = phx.po_header_id
AND plx.REVISION_NUM = (select /*+ push_subq no_unnest */ max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
and pla.revision_num <= pcgt.revision_number ) and ''Y'' = decode(nvl(plx.cancel_flag,''N''),''N'',''Y'',''Y'',decode(plx.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) order by plx.line_num) AS lines
FROM PO_HEADERS_ARCHIVE_XML phx, PO_COMMUNICATION_GT pcgt WHERE phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND
phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile, PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '||
l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||'
FROM PO_RELEASE_XML phx WHERE phx.PO_RELEASE_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
SELECT count(*) into l_count FROM po_line_locations_all WHERE po_release_id = p_document_id ;
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile, PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '||
l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||',
CURSOR(SELECT plx.*,CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER FROM po_price_differentials_v
WHERE entity_type='''|| l_eventType ||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
'|| l_line_short_attachment_query || ',' || l_line_url_attachment_query || ',' || l_line_file_attachment_query ||',
CURSOR(SELECT pllx.*,' || l_tax_name_query || ',' || l_shipment_short_attach_query || ',' || l_shipment_url_attach_query || ',' || l_shipment_file_attach_query ||',
CURSOR(SELECT pd.*
FROM po_distribution_xml pd WHERE pd.po_release_id = pllx.po_release_id and pd.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID) AS distributions
FROM po_line_locations_xml pllx WHERE pllx.po_release_id in (select po_release_id from PO_COMMUNICATION_GT) and pllx.po_line_id = plx.po_line_id
and not exists (select ''x'' from po_line_locations_archive_all pllaa where
pllaa.line_location_id = pllx.line_location_id and
pllaa.cancel_flag= ''Y'' and pllaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) order by pllx.shipment_num ) AS line_locations
FROM po_lines_xml plx WHERE exists (SELECT ''x'' from po_line_locations_all
WHERE po_line_locations_all.po_line_id = plx.po_line_id and po_release_id = phx.po_release_id and not exists (select ''x'' from po_line_locations_archive_all pllaa where
pllaa.line_location_id = po_line_locations_all.line_location_id and
pllaa.cancel_flag= ''Y'' and pllaa.revision_num< PO_COMMUNICATION_PVT.getRevisionNum() ) ) and plx.po_header_id = phx.po_header_id order by plx.line_num) AS lines
FROM PO_RELEASE_XML phx WHERE phx.PO_RELEASE_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
/* The following query gets the release details, the outermost cursor selects headers information,
and we move to the details (line, shipments, distributions) as we move inside each cursor. The
lines have to be selected from the corresponding blanket since they are not present in the release */
-- Bug 3727808. Use blanket revision number rather than release revision number. Added the max(pb.revision_num) query in lines SQL
-- Bug 5506417: Added order by pllx.shipment_num and order by plx.line_num clauses
/* Bug 8886244 .
As Part of This Bug ,removed the variables l_price_modifier_query1,l_price_modifier_query2,l_price_modifier_query3 while constructing
l_xml_query. This is because we are initializing these variables incase of Standard,Blanket Purchase Orders Only.
These variables are null incase of RELEASE due to which PO Communication program was failing with missing expression error for Releases.
As we did Pricing Enhancement in case of Standard,Blanket only we need not have these variables incase of Release while constructin the queries.
*/
/*Bug 10388305 Added l_tax_name_query to get tax names*/
l_xml_query := 'SELECT phx.*, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,PO_COMMUNICATION_PVT.getTimezone() timezone,
PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
fnd_profile.value(''PO_GENERATE_AMENDMENT_DOCS'') amendment_profile, PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , '||
l_xml_message_query || ',' || l_head_short_attachment_query || ',' || l_head_url_attachment_query || ',' || l_head_file_attachment_query ||',
CURSOR(SELECT plx.*,CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER FROM po_price_differentials_v
WHERE entity_type='''|| l_eventType ||''' AND entity_id = plx.po_line_id and enabled_flag=''Y'') AS price_diff,
'|| l_line_short_attachment_query || ',' || l_line_url_attachment_query || ',' || l_line_file_attachment_query ||',
CURSOR(SELECT pllx.*,'|| l_tax_name_query || ',' || l_shipment_short_attach_query || ',' || l_shipment_url_attach_query || ',' || l_shipment_file_attach_query ||',
CURSOR(SELECT pd.*
FROM po_distribution_archive_xml pd WHERE pd.po_release_id = pllx.po_release_id and pd.line_location_id = pllx.line_location_id
and pd.REVISION_NUM = (SELECT MAX(pda.REVISION_NUM) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL pda
WHERE pda.PO_DISTRIBUTION_ID = pd.PO_DISTRIBUTION_ID AND pda.REVISION_NUM <= PO_COMMUNICATION_PVT.getRevisionNum() ) ) AS distributions
FROM PO_LINE_LOCATIONS_ARCHIVE_XML pllx WHERE pllx.po_release_id = pcgt.po_release_id and pllx.po_line_id = plx.po_line_id
and pllx.revision_num = (SELECT/*+ push_subq no_unnest */ MAX(plla.REVISION_NUM) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL plla
where plla.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID and plla.revision_num <= pcgt.revision_number ) order by pllx.shipment_num ) AS line_locations
FROM PO_LINES_ARCHIVE_XML plx
WHERE exists (SELECT ''x'' from po_line_locations_archive_all pllaa
WHERE pllaa.po_line_id = plx.po_line_id and po_release_id = phx.po_release_id
and pllaa.REVISION_NUM = (select /*+ push_subq no_unnest */ max(revision_num) from po_line_locations_archive_all pllaa1 where pllaa1.line_location_id = pllaa.line_location_id
and pllaa1.revision_num <= pcgt.revision_number )
and ''Y'' = decode(nvl(pllaa.cancel_flag,''N''),''N'',''Y'',''Y'',decode(pllaa.revision_num,PO_COMMUNICATION_PVT.getRevisionNum(),''Y'',''N'') ) )
and plx.po_header_id = phx.po_header_id
AND plx.REVISION_NUM = (select /*+ push_subq no_unnest */ max(revision_num) from po_lines_archive_all pla where pla.po_line_id = plx.po_line_id
and pla.revision_num <= (select max(pb.revision_num)
from po_headers_archive_all pb, po_releases_archive_all pr
where pb.po_header_id = pr.po_header_id
and pr.po_release_id = pcgt.po_release_id
and pr.revision_num= pcgt.revision_number
and pb.approved_date <= pr.approved_date
) ) order by plx.line_num desc) AS lines
FROM PO_RELEASE_ARCHIVE_XML phx, PO_COMMUNICATION_GT pcgt WHERE phx.PO_RELEASE_ID = PO_COMMUNICATION_PVT.getDocumentId() AND phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()';
l_headerAttachmentsQuery := 'select fdl.long_text long_text
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_long_text fdl
WHERE ( (entity_name=''PO_RELEASES'' AND
pk1_value= to_char(PO_COMMUNICATION_PVT.getDocumentId()) ) OR
(entity_name = ''PO_HEADERS'' AND
pk1_value = to_char(PO_COMMUNICATION_PVT.getReleaseHeaderId())) OR --Bug6139548
(entity_name = ''PO_VENDORS'' AND
pk1_value = to_char(PO_COMMUNICATION_PVT.getVendorId()))) AND
function_name = ''PO_PRINTPO''
and fad.media_id = fdl.media_id
and fad.datatype_id=2
order by entity_name,seq_num'; --bug6133951
l_headerAttachmentsQuery := 'select fdl.long_text long_text
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_long_text fdl
WHERE ((entity_name = ''PO_HEADERS'' AND
pk1_value = to_char(PO_COMMUNICATION_PVT.getReleaseHeaderId())) OR --Bug6139548
(entity_name = ''PO_VENDORS'' AND
pk1_value = to_char(PO_COMMUNICATION_PVT.getVendorId()))) AND
function_name = ''PO_PRINTPO''
and fad.media_id = fdl.media_id
AND fad.datatype_id=2
order by seq_num'; --bug6133951
/*Bug7426541 - Added the clauses to select the attachments from BLANKET 'S header and lines
and CONTRACT 'S header- if there is a source document.
Entity types :- source doc's header -> 'PO_HEADERS' source doc's line -> 'PO_IN_GA_LINES'*/
/*l_lineAttachQuery :='SELECT fds.long_text text, plx.po_line_id id
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_long_text fds,
po_lines_all plx
WHERE ((fad.entity_name=''PO_LINES'' AND fad.pk1_value=to_char(plx.po_line_id))
OR
(fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.from_header_id)
AND plx.from_header_id IS NOT NULL)
OR
(fad.entity_name=''PO_IN_GA_LINES'' AND fad.pk1_value=to_char(plx.from_line_id)
AND plx.from_line_id IS NOT NULL)
OR
(fad.entity_name=''PO_HEADERS'' AND fad.pk1_value=to_char(plx.CONTRACT_ID)
AND plx.CONTRACT_ID IS NOT NULL)
OR
(fad.entity_name=''MTL_SYSTEM_ITEMS'' AND
fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND --Bug6139548
fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)
) AND
function_name = ''PO_PRINTPO''
AND fad.media_id = fds.media_id
AND fad.datatype_id=2
AND plx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId()
order by seq_num'; --bug6133951*/
l_lineAttachQuery :='select text, id from
(SELECT /*+ leading (plx ad) use_nl(ad)*/
fds.long_text text , plx.po_line_id id, seq_num
FROM fnd_documents_long_text fds ,
FND_ATTACHED_DOCUMENTS AD,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_ATTACHMENT_FUNCTIONS AF,
fnd_doc_category_usages DCU,
po_lines_all plx
WHERE ( ad.entity_name=''PO_LINES''
AND ad.pk1_value=to_char ( plx.po_line_id ) )
AND DCU.category_id = D.category_id
AND DCU.attachment_function_id = AF.attachment_function_id
AND AF.function_name = ''PO_PRINTPO''
AND D.media_id = fds.media_id
AND D.datatype_id=2
AND D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DT.LANGUAGE = USERENV (''LANG'')
AND plx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId ()
UNION ALL
SELECT /*+ leading (plx ad) use_nl(ad) */
fds.long_text text , plx.po_line_id id, seq_num
FROM fnd_documents_long_text fds ,
FND_ATTACHED_DOCUMENTS AD,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_ATTACHMENT_FUNCTIONS AF,
fnd_doc_category_usages DCU,
po_lines_all plx
WHERE ( ad.entity_name=''PO_HEADERS''
AND ad.pk1_value=to_char ( plx.from_header_id )
AND plx.from_header_id IS NOT NULL )
AND DCU.category_id = D.category_id
AND DCU.attachment_function_id = AF.attachment_function_id
AND AF.function_name = ''PO_PRINTPO''
AND D.media_id = fds.media_id
AND D.datatype_id=2
AND D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DT.LANGUAGE = USERENV (''LANG'')
AND plx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId ()
UNION ALL
SELECT /*+ leading (plx ad) use_nl(ad) */
fds.long_text text , plx.po_line_id id, seq_num
FROM fnd_documents_long_text fds ,
FND_ATTACHED_DOCUMENTS AD,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_ATTACHMENT_FUNCTIONS AF,
fnd_doc_category_usages DCU,
po_lines_all plx
WHERE ( ad.entity_name=''PO_IN_GA_LINES''
AND ad.pk1_value=to_char ( plx.from_line_id )
AND plx.from_line_id IS NOT NULL )
AND DCU.category_id = D.category_id
AND DCU.attachment_function_id = AF.attachment_function_id
AND AF.function_name = ''PO_PRINTPO''
AND D.media_id = fds.media_id
AND D.datatype_id=2
AND D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DT.LANGUAGE = USERENV (''LANG'')
AND plx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId ()
UNION ALL
SELECT /*+ leading (plx ad) use_nl(ad) */
fds.long_text text , plx.po_line_id id, seq_num
FROM fnd_documents_long_text fds ,
FND_ATTACHED_DOCUMENTS AD,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_ATTACHMENT_FUNCTIONS AF,
fnd_doc_category_usages DCU,
po_lines_all plx
WHERE ( ad.entity_name=''PO_HEADERS''
AND ad.pk1_value=to_char ( plx.CONTRACT_ID )
AND plx.CONTRACT_ID IS NOT NULL )
AND DCU.category_id = D.category_id
AND DCU.attachment_function_id = AF.attachment_function_id
AND AF.function_name = ''PO_PRINTPO''
AND D.media_id = fds.media_id
AND D.datatype_id=2
AND D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DT.LANGUAGE = USERENV (''LANG'')
AND plx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId ()
UNION ALL
SELECT /*+ leading (plx ad) use_nl(ad) */
fds.long_text text , plx.po_line_id id , seq_num
FROM fnd_documents_long_text fds ,
FND_ATTACHED_DOCUMENTS AD,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_ATTACHMENT_FUNCTIONS AF,
fnd_doc_category_usages DCU,
po_lines_all plx
WHERE ( ad.entity_name=''MTL_SYSTEM_ITEMS''
AND ad.pk1_value=to_char ( PO_COMMUNICATION_PVT.getInventoryOrgId ( ) )
AND -- Bug6139548
ad.pk2_value=to_char ( plx.item_id )
AND plx.item_id is not null )
AND DCU.category_id = D.category_id
AND DCU.attachment_function_id = AF.attachment_function_id
AND AF.function_name = ''PO_PRINTPO''
AND D.media_id = fds.media_id
AND D.datatype_id=2
AND D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DT.LANGUAGE = USERENV (''LANG'')
AND plx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId ())
order by seq_num';
/*l_shipmentAttachmentQuery := 'SELECT fds.long_text long_text, pllx.LINE_LOCATION_ID
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_long_text fds,
po_line_locations_all pllx
WHERE entity_name = ''PO_SHIPMENTS'' AND
pk1_value = to_char(pllx.LINE_LOCATION_ID) AND
function_name = ''PO_PRINTPO''
AND fad.media_id = fds.media_id
AND fad.document_description not like ''POR%''
AND fad.datatype_id=2
AND pllx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId()
order by seq_num'; --bug6133951
l_shipmentAttachmentQuery := 'SELECT PO_COMMUNICATION_PVT.get_clob(fds.rowid) long_text, pllx.LINE_LOCATION_ID
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_long_text fds,
po_line_locations_all pllx
WHERE entity_name = ''PO_SHIPMENTS'' AND
pk1_value = to_char(pllx.LINE_LOCATION_ID) AND
function_name = ''PO_PRINTPO''
AND fad.media_id = fds.media_id
AND fad.document_description not like ''POR%''
AND fad.datatype_id=2
AND pllx.po_header_id = PO_COMMUNICATION_PVT.getReleaseHeaderId() ';
select TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') into l_time from dual;
select TO_CHAR(SYSDATE, ''DD-MON-YYYY HH24:MI:SS'') into l_time from dual;
select TO_CHAR(SYSDATE, ''DD-MON-YYYY HH24:MI:SS'') into l_time from dual;
select TO_CHAR(SYSDATE, ''DD-MON-YYYY HH24:MI:SS'') into l_time from dual;
select TO_CHAR(SYSDATE, ''DD-MON-YYYY HH24:MI:SS'') into l_time from dual;
select TO_CHAR(SYSDATE, ''DD-MON-YYYY HH24:MI:SS'') into l_time from dual;
open refcur for ''select :l_fileClob1 as text_file from dual'' using l_fileClob;
select TO_CHAR(SYSDATE, ''DD-MON-YYYY HH24:MI:SS'') into l_time from dual;
/* Call PO_HR_LOCATION.populate_gt procedure to insert address values into global temp table from PL/SQL table*/
PO_HR_LOCATION.populate_gt();
context := dbms_xmlgen.newContext(''select * from po_address_details_gt '');
select TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') into l_time from dual;
/*Delete the records from global temp table*/
DELETE po_address_details_gt;
DELETE po_communication_gt ; -- Added this line for bug:3698674
SELECT
pap.email_address,
pph.phone_number
INTO PO_COMMUNICATION_PVT.g_buyer_email_address,
PO_COMMUNICATION_PVT.g_buyer_phone
FROM per_phones pph,
per_all_people_f pap
WHERE pph.parent_id(+) = pap.person_id
AND pph.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND pph.phone_type (+) = 'W1'
AND pap.person_id = p_agent_id
AND trunc(sysdate) BETWEEN pap.effective_start_date AND pap.effective_end_date
AND trunc(sysdate) BETWEEN nvl(pph.date_from, trunc(sysdate)) AND nvl(pph.date_to, trunc(sysdate))
AND ROWNUM = 1; -- Bug5671523
SELECT
pph.phone_number
INTO PO_COMMUNICATION_PVT.g_buyer_fax
FROM per_phones pph,
per_all_people_f pap
WHERE pph.parent_id(+) = pap.person_id
AND pph.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND pph.phone_type(+) = 'WF'
AND pap.person_id = p_agent_id
AND trunc(sysdate) BETWEEN pap.effective_start_date AND pap.effective_end_date
AND trunc(sysdate) BETWEEN nvl(pph.date_from, trunc(sysdate)) AND nvl(pph.date_to, trunc(sysdate)) -- bug#5999438
AND ROWNUM = 1;
/*select name and location id from hr_all_organization_units*/
SELECT name, location_id into PO_COMMUNICATION_PVT.g_ou_name, l_location_id
FROM hr_all_organization_units
WHERE organization_id = p_org_id;
Select ph.QUOTE_VENDOR_QUOTE_NUMBER, ph.SEGMENT1, ph.GLOBAL_AGREEMENT_FLAG into
PO_COMMUNICATION_PVT.g_quote_number, PO_COMMUNICATION_PVT.g_agreement_number,
PO_COMMUNICATION_PVT.g_agreement_flag
FROM
po_headers_all ph
WHERE
ph.PO_HEADER_ID = p_header_id;
Select LINE_NUM into PO_COMMUNICATION_PVT.g_agreementLine_number
FROM PO_LINES_ALL
WHERE PO_LINE_ID = p_line_id;
SELECT HRE.FIRST_NAME,
HRE.LAST_NAME,
HRL.MEANING,
PHA.AGENT_ID
INTO PO_COMMUNICATION_PVT.g_arcBuyer_fname, PO_COMMUNICATION_PVT.g_arcBuyer_lname,
PO_COMMUNICATION_PVT.g_arcBuyer_title, PO_COMMUNICATION_PVT.g_arcAgent_id
FROM
PER_ALL_PEOPLE_F HRE,
PO_HEADERS_ARCHIVE_ALL PHA,
HR_LOOKUPS HRL
WHERE HRL.LOOKUP_CODE(+) = HRE.TITLE AND
HRL.LOOKUP_TYPE(+) = 'TITLE' AND
HRE.PERSON_ID = PHA.AGENT_ID AND
--HRE.EMPLOYEE_NUMBER IS NOT NULL AND --
TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND
PHA.PO_HEADER_ID = p_header_id AND
PHA.REVISION_NUM = 0 ;
SELECT HRE.FIRST_NAME,
HRE.LAST_NAME,
PHA.AGENT_ID
INTO PO_COMMUNICATION_PVT.g_arcBuyer_fname, PO_COMMUNICATION_PVT.g_arcBuyer_lname, PO_COMMUNICATION_PVT.g_arcAgent_id
FROM
PER_ALL_PEOPLE_F HRE,
PO_RELEASES_ARCHIVE_ALL PHA
WHERE
HRE.PERSON_ID = PHA.AGENT_ID AND
-- HRE.EMPLOYEE_NUMBER IS NOT NULL AND --
TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND
PHA.PO_RELEASE_ID = p_release_id AND
PHA.REVISION_NUM = 0 ;
SELECT PVS.ADDRESS_LINE1 ,
PVS.ADDRESS_LINE2 ,
PVS.ADDRESS_LINE3 ,
PVS.CITY ,
DECODE(PVS.STATE, NULL, DECODE(PVS.PROVINCE, NULL, PVS.COUNTY, PVS.PROVINCE), PVS.STATE),
PVS.ZIP ,
FTE.TERRITORY_SHORT_NAME,
PVS.ADDRESS_LINE4 --bug: 3463617
INTO
l_address_line_1, PO_COMMUNICATION_PVT.g_vendor_address_line_2, PO_COMMUNICATION_PVT.g_vendor_address_line_3,
l_city, l_state, l_zip, PO_COMMUNICATION_PVT.g_vendor_country, PO_COMMUNICATION_PVT.g_vendor_address_line_4
FROM
PO_VENDOR_SITES_ALL PVS,
FND_TERRITORIES_TL FTE
WHERE
PVS.COUNTRY = FTE.TERRITORY_CODE AND
DECODE(FTE.TERRITORY_CODE, NULL, '1', FTE.LANGUAGE) = DECODE(FTE.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND
PVS.VENDOR_SITE_ID = p_vendor_site_id ;
SELECT
name
INTO
PO_COMMUNICATION_PVT.g_job_name
FROM
PER_JOBS_VL
WHERE
job_id = p_job_id;
select acceptance_required_flag
into l_acceptance_req_flag
from po_headers_all
where po_header_id= p_header_id;
SELECT 'Y'
INTO l_signatures
FROM dual
WHERE EXISTS (SELECT 1
FROM PO_ACTION_HISTORY
WHERE object_id = p_header_id
AND object_type_code IN ('PO', 'PA')
AND action_code = 'SIGNED'
AND OBJECT_REVISION_NUM < PO_COMMUNICATION_PVT.g_revision_num);
SELECT userenv('LANG') INTO l_language_code FROM dual;
SELECT ph.segment1 into l_po_number
FROM po_headers_all ph
WHERE po_header_id = p_document_id ;
SELECT modification_number into l_po_number
FROM po_drafts
WHERE draft_id = p_draft_id;
SELECT ph.segment1, release_num into l_po_number, l_release_num
/* End Edit By Akyanama Bug # 13342437*/
FROM po_headers_all ph, po_releases_all pr
WHERE ph.po_header_id = pr.po_header_id and pr.po_release_id = p_document_id ;
select nvl(psp.email_attachment_filename, 'Attachments.zip')
into l_email_attachment_filename
from po_system_parameters_all psp
where org_id = p_org_id;
/* SELECT name, LOCATION_ID
INTO PO_COMMUNICATION_PVT.g_legal_entity_name, l_location_id
FROM hr_all_organization_units
WHERE to_char(organization_id) = ( SELECT org_information2 FROM hr_organization_information WHERE org_information_context = 'Accounting Information'
and organization_id = p_org_id ) ; */
SELECT action_date
INTO l_cancel_date
FROM po_action_history pah
WHERE pah.object_id = p_po_header_id
AND ((pah.object_type_code = 'PO'
AND pah.object_sub_type_code in ('PLANNED', 'STANDARD'))
OR (pah.object_type_code = 'PA'
AND pah.object_sub_type_code in ('BLANKET', 'CONTRACT')))
AND pah.action_code = 'CANCEL';
SELECT sum(AMOUNT_CANCELLED), pl.amount
INTO l_canceled_amount, l_amount
FROM po_line_locations_all pll,
po_lines_all pl
WHERE pll.po_line_id = p_po_line_id AND
pll.po_header_id = p_po_header_id AND
pl.po_line_id = pll.po_line_id AND
pll.CANCEL_FLAG = 'Y'
AND pll.shipment_type <> 'PREPAYMENT' --
group by pl.amount;
SELECT sum(AMOUNT_CANCELLED), pl.amount
INTO l_canceled_amount, l_amount
FROM po_line_locations_archive_all plla,
po_lines_all pl
WHERE plla.po_line_id = p_po_line_id AND
plla.po_header_id = p_po_header_id AND
plla.revision_num = p_po_revision_num AND
pl.po_line_id = plla.po_line_id AND
plla.CANCEL_FLAG = 'Y'
AND plla.shipment_type <> 'PREPAYMENT' --
group by pl.amount;
SELECT NVL(poh.pending_signature_flag, 'N')
, hou.name
INTO x_pendingSignatureFlag
, PO_COMMUNICATION_PVT.g_ou_name
-- FROM po_headers_all poh
FROM po_headers_merge_v poh
, hr_all_organization_units hou
WHERE poh.po_header_id = p_documentID
AND poh.draft_id = p_draftId --CLM
AND hou.organization_id = poh.org_id;
SELECT poha.style_id
INTO l_style_id
FROM po_headers_archive_all poha
WHERE poha.po_header_id = p_document_id
AND poha.revision_num = p_revision_num;
SELECT INVENTORY_ORGANIZATION_ID
INTO l_inventory_org_id
FROM FINANCIALS_SYSTEM_PARAMETERS;
SELECT wf_item_type, wf_item_key
INTO x_item_type, x_item_key
FROM po_releases_all
WHERE po_release_id = p_document_id;
SELECT wf_item_type, wf_item_key
INTO x_item_type, x_item_key
FROM po_headers_all
WHERE po_header_id = p_document_id;
SELECT fds.long_text INTO
l_long
FROM
fnd_documents_long_text fds
WHERE fds.ROWID = p_row_id;
SELECT location_id into l_location_id FROM hr_locations
where location_code = l_one_time_location;
SELECT fds.long_text
INTO
l_one_time_address_clob
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_long_text fds
WHERE entity_name = 'PO_SHIPMENTS' AND
pk1_value = To_Char(p_line_location_id) AND
function_name = 'PO_PRINTPO'
AND fad.media_id = fds.media_id
AND fad.document_description like 'POR%'
AND ROWNUM = 1;
select concatenated_segments
into l_concatenated_segments
from mtl_system_items_kfv
where INVENTORY_ITEM_ID = p_item_id
and organization_id = p_org_id;