samuel025

Report SI For MF

Aug 4th, 2021
803
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION r_print_sales_invoice_for_mf(character varying, bigint, bigint, bigint, character varying, bigint)
  2.   RETURNS SETOF refcursor AS
  3. $BODY$
  4. DECLARE
  5.     pRefHeader              REFCURSOR := 'refHeader';
  6.     pRefDetail              REFCURSOR := 'refDetail';
  7.     pSessionId              ALIAS FOR $1;
  8.     pTenantId               ALIAS FOR $2;
  9.     pUserId                 ALIAS FOR $3;
  10.     pRoleId                 ALIAS FOR $4;
  11.     pDatetime               ALIAS FOR $5;
  12.     pInvoiceId              ALIAS FOR $6;
  13.  
  14.  
  15.     vDoDocTypeId                bigint;
  16.     vReportName                 character varying;
  17.     vDoNo                       character varying;
  18.     vAdditionalCost             numeric;
  19.     vDownPaymentAmount          numeric;
  20.     vDownPaymentAmountWithTax   numeric;
  21.     vTaxDownPaymentAmount       numeric;
  22.     vTotalAmount                numeric;
  23.     vTotalPayment               numeric;
  24.     vTotalTax                   numeric;
  25.     vFlgShowPpn                 character varying := 'Y';
  26.     vOuBuInfoReport             OU_INFO_REPORT%ROWTYPE;
  27.     result                      record;
  28.  
  29.     vRoundingModeTax            character varying(5);
  30.     vRoundingModeNonTax         character varying(5);
  31.     vRoundingScalePrintTotal    integer;
  32.     vNullRefId                  bigint := -99;
  33.     vEmptyString                character varying(1) :='';
  34.     vCtgrProductCode                character varying;
  35.     vCtgrProductName                character varying;
  36.     vYes                        character varying(1) :='Y';
  37.     vNo                         character varying(1) :='N';
  38.     vAll                        character varying;
  39.     vOuBuId                     bigint;
  40.     vOuPkp                      bigint;
  41.     vGovTaxAmount               numeric;
  42.     vZero                       numeric;
  43.  
  44. BEGIN
  45.     vDoDocTypeId := 311;
  46.     vReportName := 'FormSalesInvoiceForMF';
  47.     vZero := 0;
  48.  
  49.      --> Ambil Ou PkP
  50.     SELECT COUNT(1) INTO vOuPkp
  51.     FROM sl_invoice A
  52.     INNER JOIN t_ou_legal B ON A.ou_id = B.ou_id
  53.     WHERE A.invoice_id = pInvoiceId
  54.         AND B.flg_pkp = 'Y' ;
  55.  
  56.     --> Ambil total amount
  57.     SELECT SUM(F.qty_dlv_so * F.price_so) AS total_amount INTO vTotalAmount
  58.     FROM sl_invoice_item  Z
  59.     INNER JOIN sl_invoice A ON Z.invoice_id = A.invoice_id
  60.     INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
  61.     INNER JOIN sl_do C ON B.so_id = C.ref_id
  62.     INNER JOIN sl_so_balance_invoice F ON A.tenant_id = F.tenant_id AND F.ou_id = A.ou_id AND F.ref_doc_type_id = vDoDocTypeId
  63.                 AND F.ref_id = C.do_id AND F.so_id = C.ref_id AND F.ref_item_id = Z.ref_item_id
  64.                 AND F.do_receipt_item_id = Z.do_receipt_item_id
  65.     WHERE A.tenant_id = pTenantId
  66.         AND A.invoice_id = pInvoiceId;
  67.  
  68.     -- Ambil total payment from mapping cb in to so
  69.     SELECT CASE WHEN COALESCE(C.amount, 0) = 0 THEN 0
  70.             ELSE COALESCE(C.amount, 0) - (COALESCE(C.amount, 0) - A.total_amount)
  71.             END AS payment INTO vTotalPayment
  72.     FROM sl_invoice A
  73.     INNER JOIN sl_so B ON A.ref_id = B.so_id
  74.     LEFT JOIN cb_mapping_cbin_so_balance_item C ON B.so_id = C.so_id AND B.doc_no = C.doc_no
  75.     WHERE A.tenant_id = pTenantId
  76.             AND A.invoice_id = pInvoiceId;
  77.  
  78.     --> Ambil Total Tax
  79.     SELECT SUM(L.tax_amount) AS total_amount INTO vTotalTax
  80.     FROM sl_invoice_item  Z
  81.     INNER JOIN sl_invoice A ON Z.invoice_id = A.invoice_id
  82.     INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
  83.     INNER JOIN sl_do C ON B.so_id = C.ref_id
  84.     INNER JOIN sl_so_balance_invoice F ON A.tenant_id = F.tenant_id AND F.ou_id = A.ou_id AND F.ref_doc_type_id = vDoDocTypeId
  85.                 AND F.ref_id = C.do_id AND F.so_id = C.ref_id AND F.ref_item_id = Z.ref_item_id
  86.                 AND F.do_receipt_item_id = Z.do_receipt_item_id
  87.     INNER JOIN sl_so_balance_invoice_tax L ON L.tenant_id = F.tenant_id AND
  88.                                               L.ou_id = F.ou_id AND
  89.                                               L.partner_id = F.partner_id AND
  90.                                               L.ref_doc_type_id = F.ref_doc_type_id AND
  91.                                               L.ref_id = F.ref_id AND
  92.                                               L.ref_item_id = F.ref_item_id AND
  93.                                               L.do_receipt_item_id = F.do_receipt_item_id
  94.     WHERE A.tenant_id = pTenantId
  95.         AND A.invoice_id = pInvoiceId;
  96.  
  97.  
  98.     IF(vOuPkp = 1) THEN
  99.  
  100.      Open pRefHeader FOR
  101.             SELECT  f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_name_pkp') AS ou_name,
  102.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_address') AS ou_address,
  103.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_npwp') AS ou_phone,
  104.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_mail') AS ou_mail,
  105.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_web') AS ou_web,
  106.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark1') AS remark1,
  107.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark5') AS remark2,
  108.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark3') AS remark3,
  109.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark4') AS remark4,
  110.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_account1') AS bank_account1,
  111.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'bank_account2') AS bank_account2,
  112.                     A.doc_no, A.doc_date, f_get_partner_name(B.partner_bill_to_id) AS customer_name, vTotalPayment AS payment,
  113.                     (vTotalAmount + vTotalTax - vTotalPayment) AS total_due, vTotalAmount AS sub_total, vTotalTax AS tax,
  114.                     (vTotalAmount + vTotalTax) AS total_amount, vYes AS Pkp, A.due_date AS terms
  115.             FROM sl_invoice A
  116.             INNER JOIN sl_so B ON A.ref_id = B.so_id
  117.             WHERE A.invoice_id = pInvoiceId;
  118.  
  119.         RETURN NEXT pRefHeader;
  120.  
  121.         Open pRefDetail FOR
  122.             SELECT E.product_desc AS order, F.qty_dlv_so AS qty, F.price_so AS price, (F.qty_dlv_so * F.price_so) AS amount,
  123.                 D.partner_name AS partner_name, D.partner_address1 AS cust_address,
  124.                 E.remark, C.doc_date AS doc_date, E.remark
  125.             FROM sl_invoice_item Z
  126.             INNER JOIN sl_invoice A ON Z.invoice_id = A.invoice_id
  127.             INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
  128.             INNER JOIN sl_do C ON B.so_id = C.ref_id
  129.             INNER JOIN sl_quotation D ON B.ref_id = D.quotation_id
  130.             INNER JOIN sl_so_balance_invoice F ON A.tenant_id = F.tenant_id AND F.ou_id = A.ou_id AND F.ref_doc_type_id = 311
  131.                         AND F.ref_id = C.do_id AND F.so_id = C.ref_id AND F.ref_item_id = Z.ref_item_id
  132.                         AND F.do_receipt_item_id = Z.do_receipt_item_id
  133.             INNER JOIN sl_do_item G ON Z.ref_item_id = G.do_item_id
  134.             INNER JOIN sl_so_item H ON G.ref_id = H.so_item_id
  135.             INNER JOIN sl_quotation_item E ON H.ref_id = E.quotation_item_id
  136.             WHERE A.tenant_id = pTenantId
  137.                 AND A.invoice_id = pInvoiceId;
  138.  
  139.         RETURN NEXT pRefDetail;
  140.  
  141.     ELSE
  142.  
  143.         Open pRefHeader FOR
  144.             SELECT  f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_name_non_pkp') AS ou_name,
  145.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_phone') AS ou_phone,
  146.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_mail') AS ou_mail,
  147.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_web') AS ou_web,
  148.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark1') AS remark1,
  149.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark2') AS remark2,
  150.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark3') AS remark3,
  151.                     f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'remark4') AS remark4,
  152.                     A.doc_no, A.doc_date, f_get_partner_name(B.partner_bill_to_id) AS customer_name, vTotalPayment AS payment,
  153.                     (vTotalAmount - vTotalPayment) AS total_due, vNo AS Pkp, A.due_date AS terms
  154.             FROM sl_invoice A
  155.             INNER JOIN sl_so B ON A.ref_id = B.so_id
  156.             WHERE A.invoice_id = pInvoiceId;
  157.  
  158.         RETURN NEXT pRefHeader;
  159.  
  160.         Open pRefDetail FOR
  161.             SELECT E.product_desc AS order, F.qty_dlv_so AS qty, F.price_so AS price, (F.qty_dlv_so * F.price_so) AS amount,
  162.                 D.partner_name AS partner_name, D.partner_address1 AS cust_address,
  163.                 E.remark, C.doc_date AS doc_date, E.remark
  164.             FROM sl_invoice_item Z
  165.             INNER JOIN sl_invoice A ON Z.invoice_id = A.invoice_id
  166.             INNER JOIN sl_so B ON A.ref_id = B.so_id AND A.ref_doc_type_id = B.doc_type_id
  167.             INNER JOIN sl_do C ON B.so_id = C.ref_id
  168.             INNER JOIN sl_quotation D ON B.ref_id = D.quotation_id
  169.             INNER JOIN sl_so_balance_invoice F ON A.tenant_id = F.tenant_id AND F.ou_id = A.ou_id AND F.ref_doc_type_id = 311
  170.                         AND F.ref_id = C.do_id AND F.so_id = C.ref_id AND F.ref_item_id = Z.ref_item_id
  171.                         AND F.do_receipt_item_id = Z.do_receipt_item_id
  172.             INNER JOIN sl_do_item G ON Z.ref_item_id = G.do_item_id
  173.             INNER JOIN sl_so_item H ON G.ref_id = H.so_item_id
  174.             INNER JOIN sl_quotation_item E ON H.ref_id = E.quotation_item_id
  175.             WHERE A.tenant_id = pTenantId
  176.                 AND A.invoice_id = pInvoiceId;
  177.  
  178.         RETURN NEXT pRefDetail;
  179.  
  180.     END IF;
  181.  
  182.  
  183. END;
  184. $BODY$
  185.   LANGUAGE plpgsql VOLATILE
  186.   COST 100
  187.   ROWS 1000;
  188.   /
RAW Paste Data