合计小计--CUX_PO_MONTHLY_REPORT_PKG(自己小计包)

create or replace package body CUX_PO_MONTHLY_REPORT_PKG IS
--通过采购对账单验证数据的正确性
PROCEDURE OUT_PUT(P_TYPE IN NUMBER, P_VAR_BUF IN VARCHAR2) IS
  BEGIN
    IF P_TYPE = 1 THEN
      FND_FILE.PUT_LINE(FND_FILE.OUTPUT, P_VAR_BUF);
    ELSE
      FND_FILE.PUT_LINE(FND_FILE.LOG, P_VAR_BUF);
    END IF;
  END OUT_PUT;

  --处理中文乱码
  FUNCTION XML_FORMAT(P_XML IN VARCHAR2) RETURN VARCHAR2 IS
  
  BEGIN
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(P_XML, '&', ';'),
                                           '<',
                                           ';'),
                                   '>',
                                   ';'),
                           '"',
                           ';'),
                   '''',
                   ';');
  
  END XML_FORMAT;

  PROCEDURE MAIN(ERRBUF                OUT VARCHAR2,
                 ERRCODE               OUT VARCHAR2,
                 P_ORGANIZATION_ID1    IN NUMBER,
                 P_ORGANIZATION_ID2    IN NUMBER,
                 P_ORGANIZATION_ID3    IN NUMBER,
                 P_DATE_FROM           IN VARCHAR2,
                 P_DATE_TO             IN VARCHAR2) IS
  
    L_LINE_NUM    NUMBER := 0; --序号
    L_SUM_QTY     NUMBER := 0;
    L_SUM_PRICE   NUMBER := 0;
  
    CURSOR CUR_PO IS
    --采购管理-接收-接收事务处理汇总：‘交货’信息
      SELECT * 
        FROM (
           SELECT POV.VENDOR_NAME SUPPLIER,
                  RT.TRANSACTION_TYPE TRANS_TYPE,
                  nvl(sum(RT.QUANTITY),0) QTY,
                  cast(ROUND(nvl(sum(RT.QUANTITY*RT.PO_UNIT_PRICE),0),2) as numeric(10,2)) PRICE,
                  FFVV.DESCRIPTION ITEM_CATEGORY_DESC
             FROM RCV_TRANSACTIONS RT,
                  RCV_SHIPMENT_LINES RSL, 
                  RCV_SHIPMENT_HEADERS RSH,
                  PO_HEADERS_ALL PHA, 
                  MTL_SYSTEM_ITEMS_B MSIB,
                  MTL_CATEGORIES_B MCB,--MODIFIED BY WRH 20140702:有一些采购订单物料号但有物料类别(东莞市东元研磨机械有限公司),因此用物料类别来判断
                  PO_VENDORS POV,
                  FND_FLEX_VALUES_VL ffvv
            WHERE 1=1
              AND ((RT.ORGANIZATION_ID IN (P_ORGANIZATION_ID1,P_ORGANIZATION_ID2,P_ORGANIZATION_ID3) OR
                   (P_ORGANIZATION_ID1 IS NULL OR P_ORGANIZATION_ID2 IS NULL OR P_ORGANIZATION_ID3 IS NULL)))
              AND RT.TRANSACTION_TYPE = 'DELIVER'
              AND pha.vendor_id = POV.VENDOR_ID
              AND (TO_CHAR(RT.TRANSACTION_DATE,'YYYY/MM/DD hh24:mi:ss')--非YYYY-MM-DD hh24:mi:ss
                  BETWEEN P_DATE_FROM AND P_DATE_TO 
                  OR (P_DATE_FROM IS NULL OR P_DATE_TO IS NULL ))
              AND RT.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
              AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
              AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID(+)
              AND RT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
              AND RSL.ITEM_ID(+) = MSIB.INVENTORY_ITEM_ID 
              AND RSL.CATEGORY_ID = MCB.CATEGORY_ID
              AND ffvv.FLEX_VALUE_SET_ID = 1015328
              --MODIFIED BY WRH 20140702:有一些采购订单物料号但有物料类别(东莞市东元研磨机械有限公司),因此用物料类别来判断
              --AND msib.segment1 LIKE ffvv.FLEX_VALUE ||'%'
              AND MCB.SEGMENT1 || '.' || MCB.SEGMENT2 || '.' || MCB.SEGMENT3 LIKE ffvv.FLEX_VALUE ||'%'
              AND msib.segment1 NOT LIKE '%OSP%'
         GROUP BY POV.VENDOR_NAME,
                  RT.TRANSACTION_TYPE,
                  FFVV.DESCRIPTION
        
       UNION ALL
        
           SELECT POV.VENDOR_NAME SUPPLIER,
                  RT.TRANSACTION_TYPE TRANS_TYPE,
                  nvl(sum(RT.QUANTITY),0) QTY,
                  cast(ROUND(nvl(sum(RT.QUANTITY*RT.PO_UNIT_PRICE),0),2) as numeric(10,2)) PRICE,
                  FFVV.DESCRIPTION ITEM_CATEGORY_DESC
             FROM RCV_TRANSACTIONS RT,
                  RCV_SHIPMENT_LINES RSL, 
                  RCV_SHIPMENT_HEADERS RSH,
                  PO_HEADERS_ALL PHA, 
                  MTL_SYSTEM_ITEMS_B MSIB,
                  PO_VENDORS POV,
                  FND_FLEX_VALUES_VL ffvv
            WHERE 1=1
              AND ((RT.ORGANIZATION_ID IN (P_ORGANIZATION_ID1,P_ORGANIZATION_ID2,P_ORGANIZATION_ID3) OR
                   (P_ORGANIZATION_ID1 IS NULL OR P_ORGANIZATION_ID2 IS NULL OR P_ORGANIZATION_ID3 IS NULL)))
              AND RT.TRANSACTION_TYPE = 'DELIVER'
              AND pha.vendor_id = POV.VENDOR_ID
              AND (TO_CHAR(RT.TRANSACTION_DATE,'YYYY/MM/DD hh24:mi:ss')--非YYYY-MM-DD hh24:mi:ss
                  BETWEEN P_DATE_FROM AND P_DATE_TO 
                  OR (P_DATE_FROM IS NULL OR P_DATE_TO IS NULL ))
              AND RT.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
              AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
              AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID(+)
              AND RT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
              AND RSL.ITEM_ID(+) = MSIB.INVENTORY_ITEM_ID 
              AND ffvv.FLEX_VALUE_SET_ID = 1015328
              --AND msib.segment1 LIKE '%'||ffvv.FLEX_VALUE ||'%' 
              AND msib.segment1 LIKE '%OSP%'
              AND msib.segment1 NOT LIKE '%OSP-%'
              AND FFVV.FLEX_VALUE = 'OSP'
         GROUP BY POV.VENDOR_NAME,
                  RT.TRANSACTION_TYPE,
                  FFVV.DESCRIPTION
         
         UNION ALL
        
           SELECT POV.VENDOR_NAME SUPPLIER,
                  RT.TRANSACTION_TYPE TRANS_TYPE,
                  nvl(sum(RT.QUANTITY),0) QTY,
                  cast(ROUND(nvl(sum(RT.QUANTITY*RT.PO_UNIT_PRICE),0),2) as numeric(10,2)) PRICE,
                  FFVV.DESCRIPTION ITEM_CATEGORY_DESC
             FROM RCV_TRANSACTIONS RT,
                  RCV_SHIPMENT_LINES RSL, 
                  RCV_SHIPMENT_HEADERS RSH,
                  PO_HEADERS_ALL PHA, 
                  MTL_SYSTEM_ITEMS_B MSIB,
                  PO_VENDORS POV,
                  FND_FLEX_VALUES_VL ffvv
            WHERE 1=1
              AND ((RT.ORGANIZATION_ID IN (P_ORGANIZATION_ID1,P_ORGANIZATION_ID2,P_ORGANIZATION_ID3) OR
                   (P_ORGANIZATION_ID1 IS NULL OR P_ORGANIZATION_ID2 IS NULL OR P_ORGANIZATION_ID3 IS NULL)))
              AND RT.TRANSACTION_TYPE = 'DELIVER'
              AND pha.vendor_id = POV.VENDOR_ID
              AND (TO_CHAR(RT.TRANSACTION_DATE,'YYYY/MM/DD hh24:mi:ss')--非YYYY-MM-DD hh24:mi:ss
                  BETWEEN P_DATE_FROM AND P_DATE_TO 
                  OR (P_DATE_FROM IS NULL OR P_DATE_TO IS NULL ))
              AND RT.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
              AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
              AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID(+)
              AND RT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
              AND RSL.ITEM_ID(+) = MSIB.INVENTORY_ITEM_ID 
              AND ffvv.FLEX_VALUE_SET_ID = 1015328
              --AND msib.segment1 LIKE '%'||ffvv.FLEX_VALUE ||'%' 
              AND msib.segment1 LIKE 'OSP-%'
              AND FFVV.FLEX_VALUE = 'OSP-'
         GROUP BY POV.VENDOR_NAME,
                  RT.TRANSACTION_TYPE,
                  FFVV.DESCRIPTION
          UNION ALL
            
           SELECT POV.VENDOR_NAME SUPPLIER,
                  RT.TRANSACTION_TYPE TRANS_TYPE,
                  nvl(sum(RT.QUANTITY),0) QTY,
                  cast(ROUND(nvl(sum(RT.QUANTITY*RT.PO_UNIT_PRICE),0),2) as numeric(10,2)) PRICE,
                  FFVV.DESCRIPTION ITEM_CATEGORY_DESC
             FROM RCV_TRANSACTIONS RT,
                  RCV_SHIPMENT_LINES RSL, 
                  RCV_SHIPMENT_HEADERS RSH,
                  PO_HEADERS_ALL PHA, 
                  PO_VENDORS POV,
                  MTL_CATEGORIES_B MCB,
                  FND_FLEX_VALUES_VL ffvv
            WHERE 1=1
              AND ((RT.ORGANIZATION_ID IN (P_ORGANIZATION_ID1,P_ORGANIZATION_ID2,P_ORGANIZATION_ID3) OR
                   (P_ORGANIZATION_ID1 IS NULL OR P_ORGANIZATION_ID2 IS NULL OR P_ORGANIZATION_ID3 IS NULL)))
              AND RT.TRANSACTION_TYPE = 'DELIVER'
              AND pha.vendor_id = POV.VENDOR_ID
              AND (TO_CHAR(RT.TRANSACTION_DATE,'YYYY/MM/DD hh24:mi:ss')--非YYYY-MM-DD hh24:mi:ss
                  BETWEEN P_DATE_FROM AND P_DATE_TO 
                  OR (P_DATE_FROM IS NULL OR P_DATE_TO IS NULL ))
              AND RT.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
              AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
              AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
              AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID(+)
              AND rsl.item_id IS NULL
              AND ffvv.FLEX_VALUE_SET_ID = 1015328
              AND RSL.CATEGORY_ID = MCB.CATEGORY_ID
              AND MCB.SEGMENT1 || '.' || MCB.SEGMENT2 || '.' || MCB.SEGMENT3 LIKE ffvv.FLEX_VALUE ||'%'
         GROUP BY POV.VENDOR_NAME,
                  RT.TRANSACTION_TYPE,
                  FFVV.DESCRIPTION
            )
       ORDER BY SUPPLIER;
    
       
   BEGIN
    BEGIN                              
     mo_global.set_policy_context('S', fnd_profile.VALUE('ORG_ID')); --当前组织：用到一个OU就初始化一次
    END;

    OUT_PUT(1,
            '<?xml version = ''' || '1.0' || ''' encoding = ' || '''UTF-8' ||
            '''?>');
    OUT_PUT(1, '<LIST_G_SORT_HEADER>');
    OUT_PUT(1, '<G_HEADER>');
    OUT_PUT(1, '<LIST_G_SORT_LINE>');
  
    FOR REC_PO IN CUR_PO LOOP
    
      L_LINE_NUM := L_LINE_NUM + 1;
        OUT_PUT(1, '<G_LINE>');
        OUT_PUT(1, '<LINES_NUM>' || L_LINE_NUM || '</LINES_NUM>');
        OUT_PUT(1,
                '<ITEM_CATEGORY_DESC>' || XML_FORMAT(REC_PO.ITEM_CATEGORY_DESC) || '</ITEM_CATEGORY_DESC>');
        OUT_PUT(1, 
                '<SUPPLIER>' || XML_FORMAT(REC_PO.SUPPLIER) || '</SUPPLIER>');
        OUT_PUT(1,
                '<QTY>' || REC_PO.QTY || '</QTY>');
        OUT_PUT(1,
                '<PRICE>' || REC_PO.PRICE || '</PRICE>');
        OUT_PUT(1, '</G_LINE>'); 
        L_SUM_QTY := REC_PO.QTY + L_SUM_QTY;
        L_SUM_PRICE := REC_PO.PRICE + L_SUM_PRICE;
    END LOOP;
      L_LINE_NUM:=0;
      OUT_PUT(1,'<G_LINE>');
      OUT_PUT(1,'<LINES_NUM>' || '合计' || '</LINES_NUM>');
      OUT_PUT(1,
              '<QTY>' || L_SUM_QTY || '</QTY>');
      OUT_PUT(1,
              '<PRICE>' || L_SUM_PRICE || '</PRICE>');
      OUT_PUT(1,'</G_LINE>');
          
      L_SUM_QTY  := 0;
      L_SUM_PRICE := 0;
    OUT_PUT(1, '</LIST_G_SORT_LINE>');
    OUT_PUT(1, '</G_HEADER>');
    OUT_PUT(1, '</LIST_G_SORT_HEADER>');
  
  EXCEPTION
    WHEN OTHERS THEN
      ERRBUF  := SQLERRM; --报出错误 在页面左下角显示
      ERRCODE := 2;
      fnd_file.PUT_LINE(fnd_file.LOG,SQLERRM||'代码行:'||DBMS_UTILITY.format_error_backtrace);
  END MAIN;

end CUX_PO_MONTHLY_REPORT_PKG;