PROCEDURE generate_xml_fragment (
p_document_id IN NUMBER,
p_revision_num IN NUMBER,
p_document_type IN VARCHAR2,
p_document_subtype IN VARCHAR2,
x_custom_xml OUT NOCOPY CLOB
)
IS
--1). Declare context
CONTEXT DBMS_XMLGEN.ctxhandle;
context1 DBMS_XMLGEN.ctxhandle;
w_document_type VARCHAR2 (1000);
BEGIN
-- Custom Function
w_document_type := PO_COMMUNICATION_PVT.getdocumenttype ();
--- Query for Standard PO
CONTEXT := DBMS_XMLGEN.newcontext
('SELECT
TO_CHAR(ph.creation_date,''fmddth Month YYYY'') xx_order_date,
TO_CHAR(ph.start_date,''fmddth Month YYYY'') xx_aggrement_start_date,
TO_CHAR(ph.end_date,''fmddth Month YYYY'') xx_aggrement_end_date ,
DECODE
(NVL (ph.bill_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getlocationinfo (ph.bill_to_location_id)
) bill_to_location_id,
DECODE (NVL (ph.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getlocationname ()
)||chr(10) bill_to_location_name,
''Email : ''||pap1.email_address ap_email,
DECODE (NVL (ph.bill_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressline1 ()
)||chr(10) bill_to_address_line1,
DECODE (NVL (ph.bill_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressline2 ()
)||chr(10) bill_to_address_line2,
DECODE (NVL (ph.bill_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressline3 ()
)||chr(10) bill_to_address_line3,
DECODE (NVL (ph.bill_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressline4 ()
) bill_to_address_line4,
DECODE (NVL (ph.bill_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressinfo ()
)||chr(10) bill_to_address_info,
DECODE (NVL (ph.bill_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getterritoryshortname ()
)||chr(10) bill_to_country,
DECODE (NVL (ph.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getlocationinfo (ph.ship_to_location_id)
) ship_to_location_id,
DECODE (NVL (ph.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressline1 ()
)||chr(10) ship_to_address_line1,
DECODE (NVL (ph.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressline2 ()
)||chr(10) ship_to_address_line2,
CURSOR(SELECT xx_po_long_text_f(fad.media_id, ''SHORTTEXT'') xx_to_supplier_text
FROM fnd_attached_docs_form_vl fad
WHERE fad.pk1_value(+) = TO_CHAR(ph.po_header_id)
AND entity_name =''PO_HEADERS''
AND Category_Description =''To Supplier''
AND function_name =''PO_POXPOEPO''
AND datatype_name = ''Short Text''
ORDER BY fad.seq_num ASC
) hdr_short_text,
CURSOR (SELECT xx_po_long_text_f(fad.media_id, ''LONGTEXT'') xx_to_supplier_text
FROM fnd_attached_docs_form_vl fad
WHERE fad.pk1_value(+) = TO_CHAR(ph.po_header_id)
AND entity_name = ''PO_HEADERS''
AND category_description = ''To Supplier''
AND function_name = ''PO_POXPOEPO''
AND datatype_name = ''Long Text''
ORDER BY fad.seq_num ASC
) hdr_long_text,
CURSOR(
SELECT pl.line_num line_num,
item_description item_description,
(pl.note_to_vendor||chr(10)) line_note_to_vendor,
pl.unit_meas_lookup_code unit_meas_lookup_code,
TRIM(TO_CHAR(pl.unit_price,''999,999,999,999.99'')) unit_price,
TRIM(TO_CHAR(pl.quantity,''999,999,999,999.99'')) quantity,
pl.quantity_committed,
DECODE
(NVL (pll.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getlocationinfo (pll.ship_to_location_id)
) ship_to_location_id,
DECODE (NVL (pll.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getlocationname ()
) xx_ship_to_location_name,
DECODE (NVL (pll.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressline1 ()
) xx_ship_to_address_line1,
DECODE (NVL (pll.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressline2 ()
) xx_ship_to_address_line2,
DECODE (NVL (pll.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressline3 ()
) xx_ship_to_address_line3,
DECODE (NVL (pll.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressline4 ()
) xx_ship_to_address_line4,
DECODE (NVL (pll.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getaddressinfo ()
) xx_ship_to_address_info,
DECODE (NVL (pll.ship_to_location_id, -1),
-1, NULL,
PO_COMMUNICATION_PVT.getterritoryshortname ()
) xx_ship_to_country,
TO_CHAR (pl.committed_amount) committed_amount,
TRIM(TO_CHAR((pl.QUANTITY * pl.unit_price),''999,999,999,999.99''))line_amount,
TO_CHAR (NVL (pll.need_by_date, pll.promised_date), ''dd/mm/yyyy'') due_date,
CURSOR (
SELECT xx_po_long_text_f(fad.media_id,''SHORTTEXT'') to_supplier_line_text
FROM fnd_attached_docs_form_vl fad
WHERE fad.pk1_value(+) = TO_CHAR(pl.po_line_id)
AND entity_name = ''PO_LINES''
AND category_description = ''To Supplier''
AND function_name = ''PO_POXPOEPO''
AND datatype_name = ''Short Text''
ORDER BY fad.seq_num ASC
) line_short_text,
CURSOR (
SELECT xx_po_long_text_f(fad.media_id,''LONGTEXT'') to_supplier_line_text
FROM fnd_attached_docs_form_vl fad
WHERE fad.pk1_value(+) = TO_CHAR(pl.po_line_id)
AND entity_name = ''PO_LINES''
AND category_description = ''To Supplier''
AND function_name = ''PO_POXPOEPO''
AND datatype_name = ''Long Text''
ORDER BY fad.seq_num ASC
) line_long_text
FROM po_line_types_b plt,
po_lines_all pl,
po_line_locations_all pll
WHERE pl.line_type_id = plt.line_type_id
AND pl.po_line_id = pll.po_line_id
AND ph.po_header_id = pl.po_header_id
AND pll.po_header_id = pl.po_header_id
AND pll.po_header_id = ph.po_header_id
AND NVL(pl.cancel_flag,''N'') = ''N''
AND NVL(pll.cancel_flag,''N'')= ''N''
) as xx_lines
FROM po_headers_all ph,
hr_operating_units hou,
fnd_lookup_values plc,
ap_suppliers vn,
ap_supplier_sites_all pvs,
hr_all_organization_units hao,
hr_locations hl,
fnd_territories_vl ftv,
fnd_common_lookups fcl,
fnd_territories_tl fte3,
po_agents pa,
fnd_currencies_tl fcc,
ap_terms at,
apps.fnd_attached_documents fadf,
apps.fnd_lobs fl,
fnd_documents fd,
per_phones pph,
per_phones pph1,
per_phones pph2,
per_all_people_f pap,
per_all_assignments_f paa,
per_jobs pj,
per_all_people_f pap1,
(
SELECT fll2.attribute10,
fll2.lookup_code ou_code
FROM fnd_lookup_values fll2
WHERE fll2.lookup_type = ''xx_CUSTOM_LOOKUP''
AND fll2.enabled_flag = ''Y''
AND TRUNC(SYSDATE) BETWEEN TRUNC(fll2.start_date_active)
AND TRUNC(NVL(fll2.end_date_active, SYSDATE))
) ap_email,
(
SELECT fl3.lookup_code,
fl3.description
FROM fnd_lookup_values fl3
WHERE fl3.lookup_type = ''xx_PO_PAY_ON_TO_PREFIX_MAP''
AND fl3.enabled_flag = ''Y''
AND TRUNC(SYSDATE) BETWEEN TRUNC(fl3.start_date_active)
AND TRUNC(NVL(fl3.end_date_active, SYSDATE))
) pay_lookup
WHERE ph.org_id = hou.organization_id
AND hou.name = plc.lookup_code(+)
AND plc.lookup_type(+) =''xx_CUSTOM_LOOKUP''
AND NVL(plc.enabled_flag,''N'') = ''Y''
AND TRUNC(SYSDATE) BETWEEN NVL(plc.start_date_active,TRUNC(SYSDATE))
AND NVL(plc.end_date_active,TRUNC(SYSDATE))
AND vn.vendor_id = ph.vendor_id
AND pvs.vendor_site_id = ph.vendor_site_id
AND hou.organization_id = hao.organization_id
AND hao.location_id = hl.location_id
AND hl.country = ftv.territory_code
AND hl.region_1 = fcl.lookup_code(+)
AND fcl.lookup_type(+) = ''IE_COUNTY''
AND fcl.language(+) = USERENV(''LANG'')
AND NVL(fcl.enabled_flag,''N'') = ''Y''
AND TRUNC(SYSDATE) BETWEEN NVL(fcl.start_date_active,TRUNC(SYSDATE))
AND NVL(fcl.start_date_active,TRUNC(SYSDATE))
AND fcl.lookup_type = ''IE_COUNTY''
AND pvs.country = fte3.territory_code(+)
AND fte3.language = USERENV(''LANG'')
AND pa.agent_id = ph.agent_id
AND fcc.currency_code = ph.currency_code
AND fcc.language = USERENV(''LANG'')
AND ph.terms_id = at.term_id(+)
AND pay_lookup.lookup_code(+) = pvs.pay_on_code
AND fadf.pk1_value(+) = ph.agent_id
AND fd.document_id(+) = fadf.document_id
AND fd.media_id = fl.file_id(+)
AND fadf.entity_name(+) = ''Xxx_PO_BUYER_SIGNATURE''
AND pph.parent_id(+) = pap.person_id
AND pap.person_id = ph.agent_id
AND pph.parent_table(+) = ''PER_ALL_PEOPLE_F''
AND pph.phone_type(+) = ''W1''
AND pph1.parent_id(+) = pap.person_id
AND pph1.parent_table(+) = ''PER_ALL_PEOPLE_F''
AND pph1.phone_type(+) = ''M''
AND pph2.parent_id(+) = pap.person_id
AND pph2.parent_table(+) = ''PER_ALL_PEOPLE_F''
AND pph2.phone_type(+) = ''WF''
AND ap_email.attribute10 = pap1.employee_number(+)
AND hou.name = ap_email.ou_code(+)
AND ph.authorization_status = ''APPROVED''
AND NVL(ph.cancel_flag,''N'') = ''N''
AND pap.person_id = paa.person_id
AND paa.job_id = pj.job_id
AND TRUNC (SYSDATE) BETWEEN NVL(pap1.effective_start_date,TRUNC(SYSDATE))
AND NVL(pap1.effective_end_date,TRUNC(SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL(pap.effective_start_date,TRUNC(SYSDATE))
AND NVL(pap.effective_end_date,TRUNC(SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (pph.date_from, TRUNC (SYSDATE))
AND NVL (pph.date_to, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (pph2.date_from, TRUNC (SYSDATE))
AND NVL (pph1.date_to, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (pph2.date_from, TRUNC (SYSDATE))
AND NVL (pph2.date_to, TRUNC (SYSDATE))
AND ph.po_header_id = PO_COMMUNICATION_PVT.getDocumentId()
AND EXISTS (SELECT ''X''
FROM fnd_user fu
,po_agents buyer
,per_all_people_f pap_buyer
WHERE fu.user_id = FND_GLOBAL.user_id
AND fu.employee_id = buyer.agent_id
AND buyer.agent_id = pap_buyer.person_id
AND TRUNC (SYSDATE) BETWEEN NVL(pap_buyer.effective_start_date,TRUNC(SYSDATE))
AND NVL(pap_buyer.effective_end_date,TRUNC(SYSDATE))
)'
);
--3). Set XML tag of the XML fragment for the result set
DBMS_XMLGEN.setrowsettag (CONTEXT, 'PO_CUSTOM_DATA');
--4). Set XML tag for each row of the result set
DBMS_XMLGEN.setrowtag (CONTEXT, 'CUSTOM_PO');
DBMS_XMLGEN.setconvertspecialchars (CONTEXT, TRUE);
--5). Call dbms_xmlgen to get XML and assign it to output CLOB
x_custom_xml := DBMS_XMLGEN.getxml (CONTEXT, DBMS_XMLGEN.NONE);
DBMS_XMLGEN.closecontext (CONTEXT);