Jasper Report with Procedure

-- Function: so_invoice_rpt(numeric)

-- DROP FUNCTION so_invoice_rpt(numeric);

CREATE OR REPLACE FUNCTION so_invoice_rpt(pinstance numeric)
  RETURNS void AS
$BODY$
DECLARE
/**
p--- ERP 帶入的參數
v--- procedure 運算用的變數 
*/
ResultStr VARCHAR (200);
roleaccesslevelwin VARCHAR (200);
sql VARCHAR (2000);
sqldel VARCHAR (2000);

p RECORD;
r RECORD;

p_DateAcct DATE;
p_DateAcct_TO DATE;


p_Record_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
p_User_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_Org_ID NUMERIC(10) := 0;
p_AD_Org_ID_TO NUMERIC(10) := 0;
p_IsSOTrx VARCHAR (1) := 'Y';
p_C_DocType_ID NUMERIC(10) := 0;
p_C_DocType_ID_2 NUMERIC(10) := 0;

p_C_BPartner_Value VARCHAR (40) := 0;
p_C_DocType_Value VARCHAR (40) := 0;
p_C_BPartner_Value_TO VARCHAR (40) := '';
p_C_DocType_Value_TO VARCHAR (40) := '';

p_MovementDate DATE;
p_MovementDate_TO DATE;
p_DateInvoiced DATE;
p_DateInvoiced_TO DATE;

v_message VARCHAR (400) := '';
v_documentno VARCHAR (30) := '';
v_count NUMERIC(10) := 0;
v_NextNo NUMERIC(10) := 0;
v_AD_Workflow_ID NUMERIC(10) := 0;

BEGIN

IF pinstance is null THEN 
	pinstance:=0;
END IF;



v_message :='程式開始:: 更新[呼叫程序]紀錄檔...開始執行時間(created)..程序執行中(isprocessing)..';
--IF pinstance > 0 THEN
BEGIN
ResultStr := 'PInstanceNotFound';
UPDATE adempiere.ad_pinstance
SET created = SYSDATE,
isprocessing = 'Y',
reslut = 0
WHERE ad_pinstance_id = pinstance_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;

END;

v_message :='Parameter loading';

FOR p IN
(SELECT pi.record_id, pi.ad_client_id,pi.ad_org_id, pi.ad_user_id, 
pp.parametername,
pp.p_string, pp.p_number, pp.p_date,
pp.p_string_TO, pp.p_number_TO, pp.p_date_tO
FROM adempiere.ad_pinstance pi
LEFT OUTER JOIN adempiere.ad_pinstance_para pp ON(pi.ad_pinstance_id = pp.ad_pinstance_id)
WHERE pi.ad_pinstance_id = pinstance
ORDER BY pp.SeqNo)
LOOP
p_Record_ID := p.record_id;
p_User_ID := p.AD_User_id;
p_AD_Client_ID := p.AD_Client_ID;
p_AD_Org_ID := p.AD_Org_ID;

  IF p.parametername = 'DateAcct' THEN p_DateAcct = p.p_date;
     p_DateAcct_TO = p.p_date_to;
        
  ELSIF p.parametername = 'AD_Org_ID' THEN p_AD_Org_ID = p.p_number;
     p_AD_Org_ID_TO = p.p_number_to;
  ELSIF p.parametername = 'AD_User_ID' THEN p_AD_User_ID = p.p_number;
  END IF;

END LOOP;



IF p_User_ID IS NULL THEN 
   p_User_ID := 0; 
END IF;

-- 測試用
IF pinstance = 1000000 THEN

p_Record_ID = 1000001;

END IF;

v_message :='Start Process';


TRUNCATE T_Invoice;


/* 保留字需要雙引號  */

/* 測試 CODE
select  so_invoice_rpt(1000000)
select * from T_Invoice
select  ad_pinstance_id, errormsg from  adempiere.ad_pinstance where ad_pinstance_id = 1000000
*/

FOR r IN (
  -- select * from T_Invoice
  -- drop table T_Invoice
  -- create table T_Invoice as 
  select 0 ad_pinstance_id 
  -- ID to Value --

  -- Org Name
  , (select name from c_bpartner where ad_orgbp_id = i.ad_org_id) Org_Name
  -- Org Tax
  , (select taxid from c_bpartner where ad_orgbp_id = i.ad_org_id) Org_taxid
  -- Org Address
  , (select address1 from c_location where C_Location_ID = (select C_Location_ID from C_BPartner_Location where 
	C_BPartner_ID = (select c_bpartner_id from c_bpartner where ad_orgbp_id = i.ad_org_id limit 1) limit 1)) Org_Address  
  -- Org_pastal
  , (select postal from c_location where C_Location_ID = (select C_Location_ID from C_BPartner_Location where 
	C_BPartner_ID = (select c_bpartner_id from c_bpartner where ad_orgbp_id = i.ad_org_id limit 1) limit 1)) Org_Postal
  -- Org_City
  , (select city from c_location where C_Location_ID = (select C_Location_ID from C_BPartner_Location where 
	C_BPartner_ID = (select c_bpartner_id from c_bpartner where ad_orgbp_id = i.ad_org_id limit 1) limit 1)) Org_city
  -- Org Phone
  , (select Phone from C_BPartner_Location where 
	C_BPartner_ID = (select c_bpartner_id from c_bpartner where ad_orgbp_id = i.ad_org_id limit 1) limit 1) Org_phone
  -- Org Fax
  , (select fax from C_BPartner_Location where 
	C_BPartner_ID = (select c_bpartner_id from c_bpartner where ad_orgbp_id = i.ad_org_id limit 1) limit 1) Org_fax
  -- 承辦人
  , (select name from ad_user where ad_user_id = i.salesrep_id) SalesRepresentative  		

  -- 客戶資訊
   
  , (select name from c_bpartner where c_bpartner_id = i.c_bpartner_id) BP_Name

  -- 客戶統編
  , (select taxid from c_bpartner where c_bpartner_id = i.c_bpartner_id) BP_TaxID  

  -- BP Phone
  , (select Phone from C_BPartner_Location where 
	C_BPartner_ID = i.C_Bpartner_ID limit 1) BP_phone  

  -- 客戶聯絡人
  , (select name from ad_user where ad_user_id = i.ad_user_id) BP_user  

  -- 發票地址
  , (select address1 from c_location where C_Location_ID = (select C_Location_ID from C_BPartner_Location where 
	C_BPartner_ID = i.C_BPartner_ID)) BP_Invoice_Address

  -- 出貨地址
  , (select address1 from c_location where C_Location_ID = (select C_Location_ID from C_BPartner_Location where 
	C_BPartner_ID = (select C_BPartner_ID from c_order where C_Order_ID = i.C_Order_ID ))) BP_Delivery_Address
  		
  -- 明細資料
  , il.m_product_id

  , (select value from m_product where m_product_id = il.m_product_id) product_value
  , (select name from m_product where m_product_id = il.m_product_id) product_name
  
  , il.qtyinvoiced
  , il.pricelist 
  , il.priceactual
  , il.pricelimit
  , il.linenetamt
  , il.c_uom_id
  , ( select name from c_uom where c_uom_id = il.c_uom_id ) uom 
  
  , il.c_tax_id
  , ( select name from c_tax where c_tax_id = il.c_tax_id) tax
  , il.taxamt
  , il.m_attributesetinstance_id
  , (select lot from m_attributesetinstance where m_attributesetinstance_id = il.m_attributesetinstance_id) lot
  , il.line
  , il.description lineDescripton
  , i.*
  from C_Invoice i inner join C_InvoiceLine il on i.C_Invoice_ID = il.C_Invoice_ID
  where 1=1 
  and il.qtyinvoiced != 0
  and i.C_Invoice_ID = p_Record_ID
  Order By il.line 
  
)LOOP
v_message :='LOOP process';

INSERT INTO t_invoice(
            ad_pinstance_id, org_name, org_taxid, org_address, org_postal, 
            org_city, org_phone, org_fax, salesrepresentative, bp_name, bp_taxid, 
            bp_phone, bp_user, bp_invoice_address, bp_delivery_address, m_product_id, 
            product_value, product_name, qtyinvoiced, pricelist, priceactual, 
            pricelimit, linenetamt, c_uom_id, uom, c_tax_id, tax, taxamt, 
            m_attributesetinstance_id, lot, line, linedescripton, c_invoice_id, 
            ad_client_id, ad_org_id, isactive, created, createdby, updated, 
            updatedby, issotrx, documentno, docstatus, docaction, processing, 
            processed, posted, c_doctype_id, c_doctypetarget_id, c_order_id, 
            description, isapproved, istransferred, isprinted, salesrep_id, 
            dateinvoiced, dateprinted, dateacct, c_bpartner_id, c_bpartner_location_id, 
            poreference, isdiscountprinted, dateordered, c_currency_id, paymentrule, 
            c_paymentterm_id, c_charge_id, chargeamt, totallines, grandtotal, 
            m_pricelist_id, istaxincluded, c_campaign_id, c_project_id, c_activity_id, 
            ispaid, c_payment_id, c_cashline_id, createfrom, generateto, 
            sendemail, ad_user_id, copyfrom, isselfservice, ad_orgtrx_id, 
            user1_id, user2_id, c_conversiontype_id, ispayschedulevalid, 
            ref_invoice_id, isindispute, invoicecollectiontype, m_rma_id, 
            dunninggrace, c_dunninglevel_id, reversal_id, processedon, c_cashplanline_id, 
            c_invoice_uu, isfixedassetinvoice)
    VALUES (
            pinstance, r.org_name, r.org_taxid, r.org_address, r.org_postal, 
            r.org_city, r.org_phone, r.org_fax, r.salesrepresentative, r.bp_name, r.bp_taxid, 
            r.bp_phone, r.bp_user, r.bp_invoice_address, r.bp_delivery_address, r.m_product_id, 
            r.product_value, r.product_name, r.qtyinvoiced, r.pricelist, r.priceactual, 
            r.pricelimit, r.linenetamt, r.c_uom_id, r.uom, r.c_tax_id, r.tax, r.taxamt, 
            r.m_attributesetinstance_id, r.lot, r.line, r.linedescripton, r.c_invoice_id, 
            r.ad_client_id, r.ad_org_id, r.isactive, r.created, r.createdby, r.updated, 
            r.updatedby, r.issotrx, r.documentno, r.docstatus, r.docaction, r.processing, 
            r.processed, r.posted, r.c_doctype_id, r.c_doctypetarget_id, r.c_order_id, 
            r.description, r.isapproved, r.istransferred, r.isprinted, r.salesrep_id, 
            r.dateinvoiced, r.dateprinted, r.dateacct, r.c_bpartner_id, r.c_bpartner_location_id, 
            r.poreference, r.isdiscountprinted, r.dateordered, r.c_currency_id, r.paymentrule, 
            r.c_paymentterm_id, r.c_charge_id, r.chargeamt, r.totallines, r.grandtotal, 
            r.m_pricelist_id, r.istaxincluded, r.c_campaign_id, r.c_project_id, r.c_activity_id, 
            r.ispaid, r.c_payment_id, r.c_cashline_id, r.createfrom, r.generateto, 
            r.sendemail, r.ad_user_id, r.copyfrom, r.isselfservice, r.ad_orgtrx_id, 
            r.user1_id, r.user2_id, r.c_conversiontype_id, r.ispayschedulevalid, 
            r.ref_invoice_id, r.isindispute, r.invoicecollectiontype, r.m_rma_id, 
            r.dunninggrace, r.c_dunninglevel_id, r.reversal_id, r.processedon, r.c_cashplanline_id, 
            r.c_invoice_uu, r.isfixedassetinvoice);


END LOOP;

v_message :='END Process';

IF pinstance > 0 THEN
BEGIN
UPDATE adempiere.ad_pinstance
SET updated = now(),
isprocessing = 'N',
result = 1,
errormsg = v_message
WHERE ad_pinstance_id = pinstance;
-- COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;

EXCEPTION

WHEN OTHERS THEN
--sqlins := 'INSERT INTO adempiere.t_an_shipment (ad_pinstance_id, t_an_shipment_id, bp_name) VALUES($1, $2, $3)';
--EXECUTE sqlins USING pinstance, 9, v_message;
--v_message :='例外錯誤。。。';
UPDATE adempiere.ad_pinstance
SET updated = now(),
isprocessing = 'N',
result = 0,
errormsg = v_message
WHERE ad_pinstance_id = pinstance;
-- COMMIT;

END; 

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION so_invoice_rpt(numeric)
  OWNER TO adempiere;

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *