CREATE FUNCTION I2MP_DOCUMENT_SUMMARY_INSERT (varchar, varchar, varchar, varchar) RETURNS varchar AS ' /*---------------------------- Local declarations ------------------------------------*/ DECLARE --Input variables v_inDocId ALIAS FOR $1; v_inDocType ALIAS FOR $2; v_inDocStatus ALIAS FOR $3; v_inRelatedDocFlag ALIAS FOR $4; --Output variable v_outSummaryStatus varchar; v_inDocFlag int4; /* Either 0 (old) or 1 (new) */ -- Setting display format -- v_MaxCummulativeItem int4 := 5; -- Extract Doc Info -- v_extractDocId i2document_master.document_id%TYPE; v_extractDocType i2document_master.document_type%TYPE; v_extractBuyerDocId i2document_master.document_id%TYPE; v_extractBuyerDocType i2document_master.document_type%TYPE; v_extractCurPtr int4 := 1; /* Current document index from insert list */ v_tempExistDocCount int4 := 0; /* Boolean of document existance */ -- Retrieve Doc Info -- docSummary_rowtype i2document_summary%ROWTYPE; docMaster_rowtype i2document_master%ROWTYPE; docDetail_rowtype i2document_detail%ROWTYPE; v_docSentNum int4; v_docItemNum int4 := 0; -- Retrieve User Info -- v_en_buyerName i2document_summary.en_buyer_name%TYPE; v_tc_buyerName i2document_summary.tc_buyer_name%TYPE; v_sc_buyerName i2document_summary.sc_buyer_name%TYPE; v_buyerId i2document_summary.buyer_id%TYPE; v_en_supplierName i2document_summary.en_supplier_name%TYPE; v_tc_supplierName i2document_summary.tc_supplier_name%TYPE; v_sc_supplierName i2document_summary.sc_supplier_name%TYPE; v_supplierId i2document_summary.supplier_id%TYPE; -- Retrieve Company Info -- v_en_buyerCompanyName i2document_summary.en_buyer_company_name%TYPE; v_tc_buyerCompanyName i2document_summary.tc_buyer_company_name%TYPE; v_sc_buyerCompanyName i2document_summary.sc_buyer_company_name%TYPE; v_buyerCompanyId i2document_summary.buyer_company_id%TYPE; v_en_supplierCompanyName i2document_summary.en_supplier_company_name%TYPE; v_tc_supplierCompanyName i2document_summary.tc_supplier_company_name%TYPE; v_sc_supplierCompanyName i2document_summary.sc_supplier_company_name%TYPE; v_supplierCompanyId i2document_summary.supplier_company_id%TYPE; -- Doc Info -- v_docLang i2document_summary.document_lang%TYPE; v_virtualDocId i2document_summary.related_d_id%TYPE; v_virtualDocType i2document_summary.related_d_type%TYPE; v_related_d_id i2document_summary.related_d_id%TYPE; v_related_d_type i2document_summary.related_d_type%TYPE; v_related_d_version i2document_summary.related_d_version%TYPE; v_target_d_id i2document_summary.target_d_id%TYPE; v_target_d_type i2document_summary.target_d_type%TYPE; v_target_d_version i2document_summary.target_d_version%TYPE; v_docPurchaseType i2document_summary.purchase_type%TYPE; v_docStatus i2document_summary.document_status%TYPE; -- Item Info -- v_itemName i2document_summary.item_name%TYPE := ''''; v_cummulativeItemName i2document_summary.item_name%TYPE := ''''; v_cummulativeItemNameCounter int4 := 0; v_itemNum i2document_summary.item_num%TYPE := 0; v_cumulativeItemNum i2document_summary.item_num%TYPE := 0; v_partNum i2document_summary.part_num%TYPE := ''''; v_cummulativePartNum i2document_summary.part_num%TYPE := ''''; v_cummulativePartNumCounter int4 := 0; moreSymbol varchar(5) := ''...''; -- Date Info -- v_issueDate i2document_summary.issue_date%TYPE; v_expiryDate i2document_summary.expiry_date%TYPE; v_firstArrivalDate i2document_summary.first_arrival_date%TYPE; v_validateDateTo i2document_summary.validate_date_to%TYPE; v_validateDateFrom i2document_summary.validate_date_from%TYPE; -- Action Flag Declaration -- b_getCompanyNameFlag boolean := FALSE; b_getBuyerNameFlag boolean := FALSE; b_getSupplierNameFlag boolean := FALSE; b_keepQCInfoFlag boolean := FALSE; b_getRelatedDocFlag boolean := FALSE; /* Get related doc from i2document_relation table */ b_getDocInfoFlag boolean := FALSE; b_getPurchaseTypeFlag boolean := FALSE; b_getItemInfoFlag boolean := FALSE; b_getItemCumulative boolean := FALSE; b_getIssueDateFlag boolean := FALSE; b_getExpiryDateFlag boolean := FALSE; b_getFirstArrivalDateFlag boolean := FALSE; b_getValiDateToFlag boolean := FALSE; b_getValiDateFromFlag boolean := FALSE; b_docStatusFlag boolean := FALSE; b_virtualDocFlag boolean := FALSE; -- Virtual document status -- virtualDocStatus i2document_summary.document_status%TYPE := ''QC_INIT''; virtual_en_supplierList i2document_summary.en_supplier_company_name%TYPE := ''''; virtual_tc_supplierList i2document_summary.tc_supplier_company_name%TYPE := ''''; virtual_sc_supplierList i2document_summary.sc_supplier_company_name%TYPE := ''''; commaSeparator varchar(1) := '',''; /*-------------------------*/ /* Dynamic SQL declaration */ /*-------------------------*/ sql_doc_summary varchar(500); /*--------------------*/ /* Cursor declaration */ /*--------------------*/ /* FOR cursor_docSummary IN SELECT a.* FROM i2document_summary a WHERE a.document_id = v_extractDocId AND a.document_type = v_extractDocType; LOOP END LOOP; */ temp_row RECORD; /*------------------------------ Begin of procedure --------------------------------------*/ BEGIN ----- Check for the Input Error ----- IF v_inDocId is null OR v_inDocType is null OR v_inDocStatus is null OR v_inRelatedDocFlag is null THEN v_outSummaryStatus := ''-1''; RAISE EXCEPTION ''e_noInput''; ELSE v_inDocFlag := cast( cast( v_inRelatedDocFlag as text ) as int4); END IF; ---- Switch to different action based on the docuemnt status ---- IF split(v_inDocType,1) = ''QC'' THEN -- Obtain buyer doc id -- v_extractBuyerDocId := split(v_inDocId,1); v_extractBuyerDocType := split(v_inDocType,1); -- Escape the relation entry -- v_extractCurPtr := v_extractCurPtr + 1; b_virtualDocFlag := TRUE; b_getCompanyNameFlag := TRUE; b_getBuyerNameFlag := TRUE; b_getDocInfoFlag := TRUE; b_getItemInfoFlag := TRUE; b_getItemCumulative := TRUE; b_getIssueDateFlag := TRUE; b_getExpiryDateFlag := TRUE; b_getFirstArrivalDateFlag := TRUE; b_docStatusFlag := TRUE; b_keepQCInfoFlag := TRUE; ELSE IF split(v_inDocType,1) = ''RF'' THEN b_getCompanyNameFlag := TRUE; b_getBuyerNameFlag := TRUE; b_getDocInfoFlag := TRUE; b_getPurchaseTypeFlag := TRUE; b_getItemInfoFlag := TRUE; b_getItemCumulative := FALSE; b_getIssueDateFlag := TRUE; b_getExpiryDateFlag := TRUE; b_getFirstArrivalDateFlag := TRUE; b_docStatusFlag := TRUE; ELSE IF split(v_inDocType,1) = ''PO'' THEN b_getCompanyNameFlag := TRUE; b_getBuyerNameFlag := TRUE; b_getSupplierNameFlag := TRUE; b_getDocInfoFlag := TRUE; b_getPurchaseTypeFlag := TRUE; b_getItemInfoFlag := TRUE; b_getItemCumulative := FALSE; b_getIssueDateFlag := TRUE; b_getFirstArrivalDateFlag := TRUE; b_docStatusFlag := TRUE; ELSE IF split(v_inDocType,1) = ''PR'' THEN virtualDocStatus := ''PR_INIT''; -- Obtain buyer doc id -- v_extractBuyerDocId := split(v_inDocId,1); v_extractBuyerDocType := split(v_inDocType,1); -- Escape the relation entry -- v_extractCurPtr := v_extractCurPtr + 1; b_virtualDocFlag := TRUE; b_getCompanyNameFlag := TRUE; b_getBuyerNameFlag := TRUE; b_getDocInfoFlag := TRUE; b_getItemInfoFlag := TRUE; b_getItemCumulative := TRUE; b_getIssueDateFlag := TRUE; b_getExpiryDateFlag := TRUE; b_getFirstArrivalDateFlag := TRUE; b_docStatusFlag := TRUE; b_keepQCInfoFlag := TRUE; ELSE IF split(v_inDocType,1) = ''CO'' THEN b_getCompanyNameFlag := TRUE; b_getBuyerNameFlag := TRUE; b_getSupplierNameFlag := TRUE; b_getDocInfoFlag := TRUE; b_getItemInfoFlag := TRUE; b_getItemCumulative := FALSE; b_getIssueDateFlag := TRUE; b_getExpiryDateFlag := TRUE; b_getFirstArrivalDateFlag := TRUE; b_getValiDateToFlag := TRUE; b_getValiDateFromFlag := TRUE; b_docStatusFlag := TRUE; END IF; END IF; END IF; END IF; END IF; -- Check for the needs of updating related doc -- IF v_inDocFlag = 1 THEN b_getRelatedDocFlag := TRUE; -- Escape the relation entry -- v_extractCurPtr := v_extractCurPtr + 1; -- Keep virtual doc id -- v_virtualDocId := split(v_inDocId,1); v_virtualDocType := split(v_inDocType,1); END IF; -- Loop until the return Doc Id less than 0 -- LOOP v_extractDocId := split(v_inDocId,v_extractCurPtr); v_extractDocType := split(v_inDocType,v_extractCurPtr); EXIT WHEN v_extractDocId = ''NONE'' OR v_extractDocType = ''NONE''; -- Clear Item Number value v_docItemNum := 0; v_partNum := ''''; v_itemName := ''''; -- Start obtain current document summary info -- -- check for existing document -- SELECT count(1) INTO v_tempExistDocCount FROM i2document_summary WHERE document_id = v_extractDocId AND document_type = v_extractDocType; IF v_tempExistDocCount > 0 THEN v_outSummaryStatus := ''-4''; RAISE EXCEPTION ''e_recordExists''; END IF; FOR docMaster_rowtype IN SELECT a.* FROM i2document_master a WHERE a.document_id = v_extractDocId AND a.document_type = v_extractDocType LOOP -- End of obtain current document summary info -- -- Update related document id -- IF b_getRelatedDocFlag = TRUE THEN -- Get upper list -- SELECT decode(related_d_id,'''',related_d_id,related_d_id||'','')||document_id, decode(related_d_type,'''',related_d_type,related_d_type||'','')||document_type INTO v_related_d_id, v_related_d_type FROM i2document_summary WHERE document_id = v_virtualDocId AND document_type = v_virtualDocType; -- Update lower related document id -- UPDATE i2document_summary SET TARGET_D_ID = decode(TARGET_D_ID,'''',TARGET_D_ID,TARGET_D_ID||'','')||v_extractDocId, TARGET_D_TYPE = decode(TARGET_D_TYPE,'''',TARGET_D_TYPE,TARGET_D_TYPE||'','')||v_extractDocType WHERE document_id IN (v_related_d_id); END IF; -- End of update related document id -- IF b_getCompanyNameFlag = TRUE THEN /* sql_doc_summary := ''SELECT en_company_name,tc_company_name,sc_company_name FROM i2company WHERE company_id = :c1''; EXECUTE IMMEDIATE sql_doc_summary INTO v_en_buyerCompanyName,v_tc_buyerCompanyName,v_sc_buyerCompanyName USING docMaster_rowtype.buyer_company_id; */ SELECT en_company_name,tc_company_name,sc_company_name INTO temp_row FROM i2company WHERE company_id = docMaster_rowtype.buyer_company_id; v_en_buyerCompanyName := temp_row.en_company_name; v_tc_buyerCompanyName := temp_row.tc_company_name; v_sc_buyerCompanyName := temp_row.sc_company_name; v_buyerCompanyId := docMaster_rowtype.buyer_company_id; /* EXECUTE IMMEDIATE sql_doc_summary INTO v_en_supplierCompanyName,v_tc_supplierCompanyName,v_sc_supplierCompanyName USING docMaster_rowtype.seller_company_id; */ SELECT en_company_name,tc_company_name,sc_company_name INTO temp_row FROM i2company WHERE company_id = docMaster_rowtype.seller_company_id; v_en_supplierCompanyName := temp_row.en_company_name; v_tc_supplierCompanyName := temp_row.tc_company_name; v_sc_supplierCompanyName := temp_row.sc_company_name; v_supplierCompanyId := docMaster_rowtype.seller_company_id; IF b_virtualDocFlag = TRUE AND v_en_supplierCompanyName IS NOT NULL THEN IF length(virtual_en_supplierList) > 0 THEN virtual_en_supplierList := virtual_en_supplierList||commaSeparator||v_en_supplierCompanyName; ELSE virtual_en_supplierList := v_en_supplierCompanyName; END IF; END IF; IF b_virtualDocFlag = TRUE AND v_tc_supplierCompanyName IS NOT NULL THEN IF length(virtual_en_supplierList) > 0 THEN virtual_tc_supplierList := virtual_tc_supplierList||commaSeparator||v_tc_supplierCompanyName; ELSE virtual_tc_supplierList := v_tc_supplierCompanyName; END IF; END IF; IF b_virtualDocFlag = TRUE AND v_sc_supplierCompanyName IS NOT NULL THEN IF length(virtual_en_supplierList) > 0 THEN virtual_sc_supplierList := virtual_sc_supplierList||commaSeparator||v_sc_supplierCompanyName; ELSE virtual_sc_supplierList := v_sc_supplierCompanyName; END IF; END IF; END IF; -- Declare Global User Info Retrieve Query -- /* sql_doc_summary := ''SELECT en_name,tc_name,sc_name FROM i2users WHERE user_id = :u1''; */ IF b_getBuyerNameFlag = TRUE THEN /* EXECUTE IMMEDIATE sql_doc_summary INTO v_en_buyerName,v_tc_buyerName,v_sc_buyerName USING docMaster_rowtype.buyer_id; */ SELECT en_name,tc_name,sc_name INTO temp_row FROM i2users WHERE user_id = docMaster_rowtype.buyer_id; v_en_buyerName := temp_row.en_name; v_tc_buyerName := temp_row.tc_name; v_sc_buyerName := temp_row.sc_name; v_buyerId := docMaster_rowtype.buyer_id; END IF; IF b_getSupplierNameFlag = TRUE AND docMaster_rowtype.seller_response_id <> 0 THEN /* EXECUTE IMMEDIATE sql_doc_summary INTO v_en_supplierName,v_tc_supplierName,v_sc_supplierName USING docMaster_rowtype.seller_response_id; */ SELECT en_name,tc_name,sc_name INTO temp_row FROM i2users WHERE user_id = docMaster_rowtype.seller_response_id; v_en_supplierName := temp_row.en_name; v_tc_supplierName := temp_row.tc_name; v_sc_supplierName := temp_row.sc_name; v_supplierId := docMaster_rowtype.seller_response_id; END IF; IF b_getPurchaseTypeFlag = TRUE THEN v_docPurchaseType := docMaster_rowtype.purchase_type; END IF; IF b_getDocInfoFlag = TRUE THEN v_docLang := docMaster_rowtype.order_language; END IF; IF b_getItemInfoFlag = TRUE THEN FOR docDetail_rowtype IN SELECT a.* FROM i2document_detail a WHERE a.document_id = v_extractDocId AND a.document_type = v_extractDocType LOOP -- Obtain from i2document_detail -- v_docItemNum := v_docItemNum + 1; IF v_docItemNum > 1 AND docDetail_rowtype.name IS NOT NULL THEN v_itemName := v_itemName||commaSeparator||docDetail_rowtype.name; ELSE IF v_docItemNum > 1 THEN v_itemName := v_itemName; ELSE v_itemName := docDetail_rowtype.name; END IF; END IF; IF v_docItemNum > 1 AND docDetail_rowtype.part_no IS NOT NULL THEN v_partNum := v_partNum||commaSeparator||docDetail_rowtype.part_no; ELSE IF v_docItemNum > 1 THEN v_partNum := v_partNum; ELSE v_partNum := docDetail_rowtype.part_no; END IF; END IF; END LOOP; IF b_getItemCumulative = TRUE THEN v_cumulativeItemNum := v_cumulativeItemNum + v_docItemNum; IF length(v_itemName) > 0 AND v_cummulativeItemNameCounter > 0 THEN v_cummulativeItemNameCounter := v_cummulativeItemNameCounter + 1; v_cummulativeItemName := v_cummulativeItemName||commaSeparator||v_itemName; ELSE IF length(v_itemName) > 0 THEN v_cummulativeItemName := v_itemName; END IF; END IF; IF length(v_partNum) > 0 AND v_cummulativePartNumCounter > 0 THEN v_cummulativePartNumCounter := v_cummulativePartNumCounter + 1; v_cummulativePartNum := v_cummulativePartNum||commaSeparator||v_partNum; ELSE IF length(v_partNum) > 0 THEN v_cummulativePartNum := v_partNum; END IF; END IF; ELSE v_cumulativeItemNum := v_docItemNum; END IF; v_itemNum := v_docItemNum; END IF; IF b_getIssueDateFlag = TRUE THEN v_issueDate := docMaster_rowtype.document_date; END IF; IF b_getExpiryDateFlag = TRUE THEN v_expiryDate := docMaster_rowtype.expiry_date; END IF; IF b_getFirstArrivalDateFlag = TRUE THEN v_firstArrivalDate := docDetail_rowtype.delivery_date; END IF; IF b_getValiDateToFlag = TRUE THEN v_validateDateTo := docMaster_rowtype.validate_date_to; END IF; IF b_getValiDateFromFlag = TRUE THEN v_validateDateFrom := docMaster_rowtype.validate_date_from; END IF; -- Default is to perform update document status action -- -- Insert record into summary table -- -- Supplier Document -- /* sql_doc_summary := ''INSERT INTO i2document_summary (document_id, document_type) values (:did,:dtype)''; EXECUTE IMMEDIATE sql_doc_summary USING v_extractDocId, v_extractDocType; */ INSERT INTO i2document_summary (document_id, document_type) values (v_extractDocId, v_extractDocType); -- Update supplier document -- UPDATE i2document_summary SET DOCUMENT_LANG = v_docLang, DOCUMENT_STATUS = v_inDocStatus, RELATED_D_ID = v_related_d_id, RELATED_D_TYPE = v_related_d_type, RELATED_D_VERSION = v_related_d_version, EN_BUYER_COMPANY_NAME = v_en_buyerCompanyName, TC_BUYER_COMPANY_NAME = v_tc_buyerCompanyName, SC_BUYER_COMPANY_NAME = v_sc_buyerCompanyName, BUYER_COMPANY_ID = v_buyerCompanyId, EN_BUYER_NAME = v_en_buyerName, TC_BUYER_NAME = v_tc_buyerName, SC_BUYER_NAME = v_sc_buyerName, BUYER_ID = v_buyerId, EN_SUPPLIER_COMPANY_NAME = v_en_supplierCompanyName, TC_SUPPLIER_COMPANY_NAME = v_tc_supplierCompanyName, SC_SUPPLIER_COMPANY_NAME = v_sc_supplierCompanyName, SUPPLIER_COMPANY_ID = v_supplierCompanyId, EN_SUPPLIER_NAME = v_en_supplierName, TC_SUPPLIER_NAME = v_tc_supplierName, SC_SUPPLIER_NAME = v_sc_supplierName, SUPPLIER_ID = v_supplierId, ITEM_NAME = v_itemName, ITEM_NUM = v_itemNum, PART_NUM = v_partNum, DOCUMENT_SENT = 1, DOCUMENT_RECV = 0, PURCHASE_TYPE = v_docPurchaseType, ISSUE_DATE = v_issueDate, EXPIRY_DATE = v_expiryDate, FIRST_ARRIVAL_DATE = v_firstArrivalDate, VALIDATE_DATE_TO = v_validateDateTo, VALIDATE_DATE_FROM = v_validateDateFrom WHERE document_id = v_extractDocId AND document_type = v_extractDocType; v_extractCurPtr := v_extractCurPtr + 1; -- Start of closing cursor -- END LOOP; /* CLOSE cursor_docMaster */ -- End of closing cursor -- END LOOP; -- Create virtual document view -- IF b_virtualDocFlag = TRUE THEN -- Update extract pointer to the last element -- v_extractCurPtr := 1; -- Handle doc status -- IF b_docStatusFlag = TRUE THEN v_docStatus := v_inDocStatus; END IF; -- Insert or update buyer document -- ---- CHECKING ---- -- check for existing document -- SELECT count(1) INTO v_tempExistDocCount FROM i2document_summary WHERE document_id = v_extractBuyerDocId AND document_type = v_extractDocType AND supplier_company_id = v_supplierCompanyId; IF v_tempExistDocCount > 0 THEN v_outSummaryStatus := ''-4''; RAISE EXCEPTION ''e_recordExists''; END IF; -- END OF CHECKING -- /* sql_doc_summary := ''INSERT INTO i2document_summary (document_id, document_type, document_sent) values (:did,:dtype,:dsent)''; EXECUTE IMMEDIATE sql_doc_summary USING v_extractBuyerDocId, v_extractBuyerDocType, v_extractCurPtr; */ INSERT INTO i2document_summary (document_id, document_type, document_sent) values (v_extractBuyerDocId, v_extractBuyerDocType, v_extractCurPtr); -- Update buyer document -- UPDATE i2document_summary SET DOCUMENT_LANG = v_docLang, DOCUMENT_STATUS = virtualDocStatus, RELATED_D_ID = v_related_d_id, RELATED_D_TYPE = v_related_d_type, RELATED_D_VERSION = v_related_d_version, EN_BUYER_COMPANY_NAME = v_en_buyerCompanyName, TC_BUYER_COMPANY_NAME = v_tc_buyerCompanyName, SC_BUYER_COMPANY_NAME = v_sc_buyerCompanyName, BUYER_COMPANY_ID = v_buyerCompanyId, EN_BUYER_NAME = v_en_buyerName, TC_BUYER_NAME = v_tc_buyerName, SC_BUYER_NAME = v_sc_buyerName, BUYER_ID = v_buyerId, EN_SUPPLIER_COMPANY_NAME = virtual_en_supplierList, TC_SUPPLIER_COMPANY_NAME = virtual_tc_supplierList, SC_SUPPLIER_COMPANY_NAME = virtual_sc_supplierList, SUPPLIER_COMPANY_ID = v_supplierCompanyId, EN_SUPPLIER_NAME = v_en_supplierName, TC_SUPPLIER_NAME = v_tc_supplierName, SC_SUPPLIER_NAME = v_sc_supplierName, SUPPLIER_ID = v_supplierId, ITEM_NAME = v_cummulativeItemName, ITEM_NUM = v_cumulativeItemNum, PART_NUM = v_cummulativePartNum, DOCUMENT_RECV = 0, PURCHASE_TYPE = v_docPurchaseType, ISSUE_DATE = v_issueDate, EXPIRY_DATE = v_expiryDate, FIRST_ARRIVAL_DATE = v_firstArrivalDate, VALIDATE_DATE_TO = v_validateDateTo, VALIDATE_DATE_FROM = v_validateDateFrom WHERE document_id = v_extractBuyerDocId AND document_type = v_extractBuyerDocType; END IF; v_outSummaryStatus := 0; RETURN v_outSummaryStatus; END; ' LANGUAGE 'plpgsql' ;