CREATE OR REPLACE PACKAGE APPS.cux_po_undelivery_pkg IS
/**************************************************************************
File name : cux_po_undelivery_pkg.pck
Doc Ref(s) :
Project :
Description : po have been received but not delivery
Change History Information
--------------------------
Version Date Author Change Reference / Description
------- ----------- --------------- ------------------------------------
1.0 2009-07-31 Randolph First Version
**************************************************************************/
PROCEDURE xml_main(retbuffer OUT VARCHAR2,
retcode OUT NUMBER,
p_po_number IN VARCHAR2,
p_vendor_id IN NUMBER,
p_shipnum_fm IN VARCHAR2,
p_shipnum_to IN VARCHAR2,
p_date_fm IN VARCHAR2,
p_date_to IN VARCHAR2);
END cux_po_undelivery_pkg;
/
CREATE OR REPLACE PACKAGE BODY APPS.cux_po_undelivery_pkg IS
/*=======================================================================
-- PROCEDURE NAME: output_xml()
=========================================================================*/
PROCEDURE output_xml(p_tag IN VARCHAR2,
p_value IN VARCHAR2) AS
BEGIN
fnd_file.put_line(fnd_file.output,
'' ||
nvl(p_value,
' ') || '' || upper(TRIM(p_tag)) || '>');
END output_xml;
/*=======================================================================
-- PROCEDURE NAME: output_tail()
========================================================================*/
PROCEDURE output_tail(p_tag IN VARCHAR2) AS
BEGIN
fnd_file.put_line(fnd_file.output,
'' || upper(TRIM(p_tag)) || '>');
END output_tail;
/*=======================================================================
-- PROCEDURE NAME: output_header()
=========================================================================*/
PROCEDURE output_header(p_tag IN VARCHAR2) AS
BEGIN
fnd_file.put_line(fnd_file.output,
'');
END output_header;
PROCEDURE xml_main(retbuffer OUT VARCHAR2,
retcode OUT NUMBER,
p_po_number IN VARCHAR2,
p_vendor_id IN NUMBER,
p_shipnum_fm IN VARCHAR2,
p_shipnum_to IN VARCHAR2,
p_date_fm IN VARCHAR2,
p_date_to IN VARCHAR2) IS
CURSOR cur_data(c_date_fm IN DATE, c_date_to IN DATE) IS
SELECT rt.transaction_type,
rt.primary_quantity,
to_char(rt.transaction_date,'YYYY-MM-DD') transaction_date,
ph.segment1 po_number,
pv.vendor_name,
pv.segment1 vendor_num,
rsh.receipt_num,
rt.unit_of_measure,
rt.currency_code,
msi.concatenated_segments item_num,
msi.description item_desc,
NVL(cic.ITEM_COST,0) cost_type
FROM rcv_transactions rt,
po_headers_all ph,
po_vendors pv,
rcv_shipment_headers rsh,
po_lines_all pl,
po_line_locations_all pll,
mtl_system_items_kfv msi,
BOM.CST_ITEM_COSTS cic
WHERE rt.transaction_type = 'RECEIVE'
AND ph.po_header_id = rt.po_header_id
AND ph.vendor_id = pv.vendor_id
AND ph.po_header_id = pl.po_header_id
AND pll.po_header_id = ph.po_header_id
AND pll.po_line_id = pl.po_line_id
AND msi.inventory_item_id = pl.item_id
AND msi.organization_id = rt.organization_id
AND msi.inventory_item_id =cic.INVENTORY_ITEM_ID(+)
AND msi.organization_id =cic.ORGANIZATION_ID(+)
AND cic.COST_TYPE_ID(+)=1
--parameter
AND rt.transaction_date >=
nvl(c_date_fm,rt.transaction_date)
AND rt.transaction_date <=
nvl(c_date_to + .99999,rt.transaction_date)
AND ph.segment1 = nvl(p_po_number,ph.segment1)
AND ph.vendor_id = nvl(p_vendor_id,ph.vendor_id)
AND rsh.receipt_num >=
nvl(p_shipnum_fm,rsh.receipt_num)
AND rsh.receipt_num <=
nvl(p_shipnum_to,rsh.receipt_num)
--
AND rt.shipment_header_id = rsh.shipment_header_id(+)
AND NOT EXISTS
(SELECT 'x'
FROM rcv_transactions rt1
WHERE rt1.transaction_type = 'DELIVER'
CONNECT BY rt1.parent_transaction_id = PRIOR
rt1.transaction_id
AND PRIOR rt1.transaction_type = 'DELIVER'
START WITH rt1.transaction_id = rt.transaction_id);
l_date_fm DATE;
l_date_to DATE;
BEGIN
fnd_file.put_line(fnd_file.output,
'<?xml version="1.0" encoding="UTF-8" ?>');
output_header('G_DATA');
l_date_fm := fnd_date.canonical_to_date(p_date_fm);
l_date_to := fnd_date.canonical_to_date(p_date_to);
FOR rec_data IN cur_data(l_date_fm,
l_date_to) LOOP
output_header('G_RECEIVING');
output_xml('TRANSACTION_TYPE',
rec_data.transaction_type);
output_xml('PRIMARY_QUANTITY',
rec_data.primary_quantity);
output_xml('TRANSACTION_DATE',
rec_data.transaction_date);
output_xml('PO_NUMBER',
rec_data.po_number);
output_xml('VENDOR_NAME',
rec_data.vendor_name);
output_xml('VENDOR_NUM',
rec_data.vendor_num);
output_xml('ITEM_NUM',
rec_data.item_num);
output_xml('ITEM_DESC',
rec_data.item_desc);
output_xml('UOM',
rec_data.unit_of_measure);
output_xml('StdCost',
rec_data.cost_type);
output_xml('CURRENCY_CODE',
rec_data.currency_code);
output_xml('RECEIPT_NUM',
rec_data.receipt_num);
output_tail('G_RECEIVING');
END LOOP;
output_tail('G_DATA');
EXCEPTION
WHEN OTHERS THEN
retbuffer := SQLCODE || ':' || SQLERRM;
retcode := 1;
END xml_main;
END cux_po_undelivery_pkg;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11536986/viewspace-620206/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11536986/viewspace-620206/