Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION r_print_sales_invoice_temporary_escp_for_jrp(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pRoleId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pInvoiceId ALIAS FOR $6;
- pFlgPkp ALIAS FOR $7;
- pNamaPenanggungJwb ALIAS FOR $8;
- pSikaPenanggungJwb ALIAS FOR $9;
- vDoDocTypeId bigint;
- vReportName character varying;
- vDoNo character varying;
- vAdditionalCost numeric;
- vDownPaymentAmount numeric;
- vDownPaymentAmountWithTax numeric;
- vTaxDownPaymentAmount numeric;
- vFlgShowPpn character varying := 'Y';
- vOuBuInfoReport OU_INFO_REPORT%ROWTYPE;
- result record;
- vRoundingModeTax character varying(5);
- vRoundingModeNonTax character varying(5);
- vRoundingScalePrintTotal integer;
- vNullRefId bigint := -99;
- vEmptyString character varying(1) :='';
- vCtgrProductCode character varying;
- vCtgrProductName character varying;
- vYes character varying(1) :='Y';
- vAll character varying;
- vOuBuId bigint;
- vGovTaxAmount numeric;
- BEGIN
- vDoDocTypeId := 311;
- vReportName := 'FormTemporarySalesInvoiceForJrp';
- vAll := 'ALL';
- select (f_get_ou_bu_structure(A.ou_id)).ou_bu_id
- FROM sl_invoice_temp A
- WHERE A.invoice_temp_id = pInvoiceId INTO vOuBuId ;
- SELECT f_get_ou_bu_info_report(pTenantId, A.ou_id) AS info
- FROM sl_invoice_temp A
- WHERE A.invoice_temp_id = pInvoiceId INTO result ;
- vOuBuInfoReport := result.info;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.tax') INTO vRoundingModeTax;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, 'rounding.scale.print.total') AS integer) INTO vRoundingScalePrintTotal;
- vRoundingScalePrintTotal := COALESCE(vRoundingScalePrintTotal,2);
- DELETE FROM tr_sl_invoice_item WHERE session_id = pSessionId;
- /* get delivery order number */
- SELECT STRING_AGG(Z.doc_no, ' , ' ) INTO vDoNo
- FROM (
- SELECT B.doc_no
- FROM sl_invoice_temp_item A
- INNER JOIN sl_do B ON A.tenant_id = B.tenant_id AND A.ref_id = B.do_id
- WHERE A.tenant_id = pTenantId
- AND A.invoice_temp_id = pInvoiceId
- GROUP BY B.do_id
- ORDER BY B.doc_no) Z;
- SELECT ctgr_product_code, ctgr_product_name INTO vCtgrProductCode, vCtgrProductName
- FROM (
- SELECT f_get_ctgr_product_code(C.ctgr_product_id) AS ctgr_product_code,
- f_get_ctgr_product_name(C.ctgr_product_id) AS ctgr_product_name
- FROM sl_invoice_temp_item A
- INNER JOIN sl_do_item B ON A.tenant_id = B.tenant_id AND A.ref_item_id = B.do_item_id
- INNER JOIN m_product C ON B.product_id = C.product_id
- WHERE A.tenant_id = pTenantId
- AND A.invoice_temp_id = pInvoiceId
- limit 1) Z;
- /* insert into table tr_sl_invoice_item*/
- INSERT INTO tr_sl_invoice_item(
- session_id, tenant_id, product_code, product_name,
- qty_so,
- uom_name,
- nett_sell_price,
- gross_sell_price,
- nett_item_amount,
- gross_item_amount,
- tax_price,
- tax_amount,
- warranty,
- discount_pct_so,
- serial_number,
- product_expired_date,
- regular_disc_amount,
- promo_disc_amount,
- adj_regular_disc_amount,
- adj_promo_disc_amount,
- dpp_ppn_in_so,
- item_amount_in_so_balance,
- regular_disc_amount_in_so_balance,
- promo_disc_amount_in_so_balance,
- adj_regular_disc_amount_in_so_balance,
- adj_promo_disc_amount_in_so_balance,
- tax_amount_in_so_balance,
- so_balance_invoice_id,
- line_no
- )
- SELECT pSessionId, pTenantId, D.product_code, D.product_name,
- CASE WHEN G.do_receipt_item_id = vNullRefId THEN
- COALESCE(F.qty_dlv_int, G.qty_dlv_so) ELSE COALESCE(K.qty_return, 0)*-1
- END AS qty_so,
- E.uom_name,
- G.price_so AS nett_sell_price,
- (G.item_amount + COALESCE(L.tax_amount, 0)) / G.qty_dlv_so AS gross_sell_price,
- 0 as nett_item_amount,--(G.qty_dlv_so * G.price_so) AS nett_item_amount,
- 0 AS gross_item_amount,--(G.price_so + f_tax_rounding(G.tenant_id, G.price_so, sl_get_tax_pct_from_so_balance_inv_tax(pTenantId, G.ou_id, G.partner_id, G.ref_doc_type_id, G.ref_id, G.ref_item_id, G.do_receipt_item_id))) * G.qty_dlv_so AS gross_item_amount,
- f_tax_rounding(G.tenant_id, G.price_so, sl_get_tax_pct_from_so_balance_inv_tax(pTenantId, G.ou_id, G.partner_id, G.ref_doc_type_id, G.ref_id, G.ref_item_id, G.do_receipt_item_id)) AS tax_price,
- COALESCE(L.tax_amount, 0) / G.qty_dlv_so AS tax_amunt,--f_tax_rounding(G.tenant_id, G.price_so, sl_get_tax_pct_from_so_balance_inv_tax(pTenantId, G.ou_id, G.partner_id, G.ref_doc_type_id, G.ref_id, G.ref_item_id, G.do_receipt_item_id)) * G.qty_dlv_so AS tax_amount,
- (f_get_product_warranty_service(C.product_id) :: integer) AS warranty,
- J.discount_percentage,
- COALESCE (I.serial_number, vEmptyString) as serial_number,
- COALESCE (I.product_expired_date, vEmptyString) as product_expired_date,
- G.regular_disc_amount/G.qty_dlv_so AS regular_disc_amount,
- G.promo_disc_amount/G.qty_dlv_so AS promo_disc_amount,
- G.adj_regular_disc_amount/G.qty_dlv_so AS adj_regular_disc_amount,
- G.adj_promo_disc_amount/G.qty_dlv_so AS adj_promo_disc_amount,
- f_calculate_dpp_amount_from_gross_price(J.gross_sell_price, 1, 0,J.flg_tax_amount,J.tax_percentage,vRoundingScalePrintTotal,vRoundingModeNonTax) +
- f_calculate_tax_amount_from_gross_price(J.gross_sell_price, 1, 0,J.flg_tax_amount,J.tax_percentage,vRoundingScalePrintTotal,vRoundingModeNonTax) AS dpp_ppn_in_so,
- G.item_amount AS item_amount_in_so_balance,
- G.regular_disc_amount AS regular_disc_amount_in_so_balance,
- G.promo_disc_amount AS promo_disc_amount_in_so_balance,
- G.adj_regular_disc_amount AS adj_regular_disc_amount_in_so_balance,
- G.adj_promo_disc_amount AS adj_promo_disc_amount_in_so_balance,
- COALESCE(L.tax_amount, 0) AS tax_amount_in_so_balance,
- G.so_balance_invoice_id,
- row_number() OVER(PARTITION BY G.so_balance_invoice_id ORDER BY (G.item_amount + COALESCE(L.tax_amount, 0)) / G.qty_dlv_so * CASE WHEN G.do_receipt_item_id = vNullRefId THEN COALESCE(F.qty_dlv_int, G.qty_dlv_so) ELSE COALESCE(K.qty_return, G.qty_dlv_so)*-1 END DESC,
- D.product_code, COALESCE (I.serial_number, vEmptyString))
- FROM sl_invoice_temp_item A
- INNER JOIN sl_invoice_temp H ON A.tenant_id = H.tenant_id AND A.invoice_temp_id = H.invoice_temp_id
- INNER JOIN sl_do B ON A.tenant_id = B.tenant_id AND A.ref_id = B.do_id
- INNER JOIN sl_do_item C ON A.tenant_id = C.tenant_id AND A.ref_item_id = C.do_item_id
- INNER JOIN m_product D ON A.tenant_id = D.tenant_id AND C.product_id = D.product_id
- INNER JOIN sl_so_balance_invoice G ON A.tenant_id = G.tenant_id AND G.ou_id = H.ou_id AND G.ref_doc_type_id = vDoDocTypeId
- AND G.ref_id = B.do_id AND G.so_id = B.ref_id AND G.ref_item_id = A.ref_item_id AND G.do_receipt_item_id = A.do_receipt_item_id
- LEFT JOIN sl_so_balance_invoice_tax L ON L.tenant_id = G.tenant_id AND
- L.ou_id = G.ou_id AND
- L.partner_id = G.partner_id AND
- L.ref_doc_type_id = G.ref_doc_type_id AND
- L.ref_id = G.ref_id AND
- L.ref_item_id = G.ref_item_id AND
- L.do_receipt_item_id = G.do_receipt_item_id
- INNER JOIN m_uom E ON A.tenant_id = E.tenant_id AND G.so_uom_id = E.uom_id
- INNER JOIN sl_so_item J ON J.so_item_id = C.ref_id
- LEFT JOIN sl_do_product F ON F.do_item_id = C.do_item_id --AND G.do_receipt_item_id = vNullRefId
- LEFT JOIN in_do_receipt_product K ON K.do_receipt_item_id = G.do_receipt_item_id AND F.product_balance_id = K.product_balance_id
- LEFT JOIN in_product_balance I ON I.product_balance_id = F.product_balance_id
- WHERE A.tenant_id = pTenantId
- AND A.invoice_temp_id = pInvoiceId;
- UPDATE tr_sl_invoice_item
- SET nett_item_amount = ROUND(qty_so * nett_sell_price,vRoundingScalePrintTotal),
- gross_item_amount = ROUND(qty_so * gross_sell_price,vRoundingScalePrintTotal),
- regular_disc_amount = ROUND(regular_disc_amount * qty_so,vRoundingScalePrintTotal),
- promo_disc_amount = ROUND(promo_disc_amount* qty_so,vRoundingScalePrintTotal),
- adj_regular_disc_amount = ROUND(adj_regular_disc_amount* qty_so,vRoundingScalePrintTotal),
- adj_promo_disc_amount = ROUND(adj_promo_disc_amount* qty_so,vRoundingScalePrintTotal)
- WHERE session_id = pSessionId;
- --hitung selisih regular_disc_amount dari data balance SO, dan dimasukan ke
- -- harga paling tingi (line no nya 1)
- WITH data_selisih AS (
- SELECT so_balance_invoice_id, (regular_disc_amount_in_so_balance) - sum(regular_disc_amount) AS selisih
- FROM tr_sl_invoice_item
- WHERE session_id = pSessionId
- GROUP BY so_balance_invoice_id, regular_disc_amount_in_so_balance
- )
- UPDATE tr_sl_invoice_item Z
- SET regular_disc_amount = Z.regular_disc_amount + A.selisih
- FROM data_selisih A
- WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
- Z.line_no = 1 AND
- Z.session_id = pSessionId;
- --hitung selisih promo_disc_amount dari data balance SO, dan dimasukan ke
- -- harga paling tingi (line no nya 1)
- WITH data_selisih AS (
- SELECT so_balance_invoice_id, (promo_disc_amount_in_so_balance) - sum(promo_disc_amount) AS selisih
- FROM tr_sl_invoice_item
- WHERE session_id = pSessionId
- GROUP BY so_balance_invoice_id, promo_disc_amount_in_so_balance
- )
- UPDATE tr_sl_invoice_item Z
- SET promo_disc_amount = Z.promo_disc_amount + A.selisih
- FROM data_selisih A
- WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
- Z.line_no = 1 AND
- Z.session_id = pSessionId;
- --hitung selisih item amount dari data balance SO, dan dimasukan ke
- -- harga paling tingi (line no nya 1)
- WITH data_selisih AS (
- SELECT so_balance_invoice_id, (item_amount_in_so_balance + tax_amount_in_so_balance) - sum(gross_item_amount) AS selisih
- FROM tr_sl_invoice_item
- WHERE session_id = pSessionId
- GROUP BY so_balance_invoice_id, item_amount_in_so_balance + tax_amount_in_so_balance
- )
- UPDATE tr_sl_invoice_item Z
- SET gross_item_amount = Z.gross_item_amount + A.selisih
- FROM data_selisih A
- WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
- Z.line_no = 1 AND
- Z.session_id = pSessionId;
- --hitung selisih nett item amount dari data balance SO, dan dimasukan ke
- -- harga paling tingi (line no nya 1)
- WITH data_selisih AS (
- SELECT so_balance_invoice_id, (item_amount_in_so_balance - regular_disc_amount_in_so_balance - promo_disc_amount_in_so_balance) - sum(nett_item_amount) AS selisih
- FROM tr_sl_invoice_item
- WHERE session_id = pSessionId
- GROUP BY so_balance_invoice_id, item_amount_in_so_balance - regular_disc_amount_in_so_balance - promo_disc_amount_in_so_balance
- )
- UPDATE tr_sl_invoice_item Z
- SET nett_item_amount = Z.nett_item_amount + A.selisih
- FROM data_selisih A
- WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
- Z.line_no = 1 AND
- Z.session_id = pSessionId;
- --hitung selisih tax amount dari data balance SO, dan dimasukan ke
- -- harga paling tingi (line no nya 1)
- SELECT gov_tax_amount INTO vGovTaxAmount
- FROM sl_invoice_temp_tax
- WHERE invoice_temp_id = pInvoiceId;
- vGovTaxAmount := COALESCE(vGovTaxAmount, 0);
- WITH data_yg_diupdate AS (
- SELECT so_balance_invoice_id
- FROM tr_sl_invoice_item
- WHERE session_id = pSessionId
- ORDER BY gross_item_amount DESC, line_no
- LIMIT 1
- )
- ,data_selisih AS (
- SELECT B.so_balance_invoice_id, vGovTaxAmount - sum(+A.tax_amount) AS selisih
- FROM tr_sl_invoice_item A , data_yg_diupdate B
- WHERE A.session_id = pSessionId
- GROUP BY B.so_balance_invoice_id
- )
- UPDATE tr_sl_invoice_item Z
- SET tax_amount = Z.tax_amount + A.selisih
- FROM data_selisih A
- WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
- Z.line_no = 1 AND
- Z.session_id = pSessionId;
- -- penyamaan gross amount dengan gross amount yg ada di invoice
- WITH data_yg_diupdate AS (
- SELECT so_balance_invoice_id
- FROM tr_sl_invoice_item
- WHERE session_id = pSessionId
- ORDER BY gross_item_amount DESC, line_no
- LIMIT 1
- )
- ,data_selisih AS (
- SELECT B.so_balance_invoice_id,
- sum(A.nett_item_amount + A.tax_amount + A.regular_disc_amount + A.promo_disc_amount) - sum(A.gross_item_amount) AS selisih
- FROM tr_sl_invoice_item A , data_yg_diupdate B
- WHERE A.session_id = pSessionId
- GROUP BY B.so_balance_invoice_id
- )
- UPDATE tr_sl_invoice_item Z
- SET gross_item_amount = Z.gross_item_amount + A.selisih
- FROM data_selisih A
- WHERE A.so_balance_invoice_id = Z.so_balance_invoice_id AND
- Z.line_no = 1 AND
- Z.session_id = pSessionId;
- /* get vFlgShowPpn */
- SELECT (CASE WHEN (pFlgPkp = 'Y' AND B.flg_show_inv_tax = 'Y') THEN 'Y' ELSE 'N' END) INTO vFlgShowPpn
- FROM sl_invoice_temp A
- INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ref_id = B.so_id
- WHERE A.invoice_temp_id = pInvoiceId;
- /* get additional amount*/
- SELECT CASE WHEN vRoundingModeNonTax = 'RD' THEN
- TRUNC(COALESCE(SUM(A.add_amount), 0), vRoundingScalePrintTotal)
- ELSE
- ROUND(COALESCE(SUM(A.add_amount), 0), vRoundingScalePrintTotal)
- END
- INTO vAdditionalCost
- FROM sl_invoice_temp_cost A
- WHERE A.tenant_id = pTenantId
- AND A.invoice_temp_id = pInvoiceId;
- /* get down payment amount in invoice */
- SELECT CASE WHEN vRoundingModeNonTax = 'RD' THEN
- TRUNC(COALESCE(SUM(A.alloc_amount), 0), vRoundingScalePrintTotal)
- ELSE
- ROUND(COALESCE(SUM(A.alloc_amount), 0), vRoundingScalePrintTotal)
- END,
- CASE WHEN vRoundingModeNonTax = 'RD' THEN
- TRUNC(COALESCE(SUM(A.alloc_amount + f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
- ELSE
- ROUND(COALESCE(SUM(A.alloc_amount + f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
- END,
- CASE WHEN vRoundingModeTax = 'RD' THEN
- TRUNC(COALESCE(SUM(f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
- ELSE
- ROUND(COALESCE(SUM(f_tax_rounding(A.tenant_id, A.alloc_amount, B.tax_percentage)),0), vRoundingScalePrintTotal)
- END
- INTO vDownPaymentAmount, vDownPaymentAmountWithTax, vTaxDownPaymentAmount
- FROM sl_invoice_temp_advance A
- INNER JOIN sl_so_balance_advance_invoice B ON A.tenant_id = B.tenant_id AND A.ref_doc_type_id = B.ref_doc_type_id AND A.ref_id = B.ref_id
- WHERE A.tenant_id = pTenantId
- AND A.invoice_temp_id = pInvoiceId;
- Open pRefHeader FOR
- SELECT A.invoice_temp_id, A.ou_id, A.ou_legal_id,
- UPPER(vOuBuInfoReport.printed_ou_name) AS ou_name,
- UPPER(COALESCE(vOuBuInfoReport.address1 || ' ' || vOuBuInfoReport.address2 || ' ' || vOuBuInfoReport.address3, '-')) AS ou_addr_1,
- UPPER(COALESCE(vOuBuInfoReport.city || ', ' || vOuBuInfoReport.state_or_province || ', ' || vOuBuInfoReport.zip_code, '-')) AS ou_addr_2,
- UPPER(vOuBuInfoReport.npwp_no) AS ou_npwp,
- UPPER(vOuBuInfoReport.image_url) AS path_logo_ou,
- (vOuBuInfoReport.phone1) AS ou_phone,
- (vOuBuInfoReport.fax1) AS ou_fax,
- UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ou_email')) AS ou_email,
- UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'ijin_pbf')) AS ijin_pbf,
- UPPER(vCtgrProductCode) AS ctgr_product_code,
- UPPER(vCtgrProductName) AS ctgr_product_name,
- A.partner_id,
- UPPER(C.partner_name) as customer_name,
- UPPER(COALESCE(H.address1 || ' ' || H.address2 || ' ' || H.address3, '-')) AS customer_address_1,
- UPPER(COALESCE(H.city || ' ' || H.state_or_province || ' ' || H.zip_code, '-')) AS customer_address_2,
- UPPER(COALESCE(I.npwp_no, vEmptyString)) AS customer_npwp,
- A.doc_type_id,
- UPPER(A.inv_doc_no) AS invoice_no, A.inv_doc_date AS invoice_date,
- TO_DATE(A.due_date, 'YYYYMMDD') - TO_DATE(A.doc_date, 'YYYYMMDD') AS top,
- A.due_date,
- COALESCE(J.tax_no, vEmptyString) AS tax_no,
- UPPER(E.doc_no) AS so_no,
- UPPER(vDoNo) AS do_no,
- UPPER(E.ext_doc_no) AS po_no, A.salesman_id,
- UPPER(COALESCE(F.partner_name, ' ')) AS salesman_name,
- UPPER(A.curr_code) AS currency,
- UPPER(A.remark) AS remark,
- UPPER((CASE WHEN A.curr_code = 'IDR' THEN 'rupiah' ELSE A.curr_code END)) AS curr_name, vFlgShowPpn AS flg_show_ppn,
- CASE WHEN vRoundingModeNonTax = 'RD' THEN
- TRUNC(SUM(gross_item_amount - (regular_disc_amount + promo_disc_amount)), vRoundingScalePrintTotal)::character varying
- ELSE
- ROUND(SUM(gross_item_amount - (regular_disc_amount + promo_disc_amount)), vRoundingScalePrintTotal)::character varying
- END AS sub_total,
- --SUM(regular_disc_amount + promo_disc_amount + adj_regular_disc_amount + adj_promo_disc_amount)::character varying AS total_disc,
- 0::character varying AS total_disc,
- vAdditionalCost::character varying AS additional_cost,
- CASE WHEN vRoundingModeNonTax = 'RD' THEN
- TRUNC(SUM(G.tax_amount), vRoundingScalePrintTotal)::character varying
- ELSE
- ROUND(SUM(G.tax_amount), vRoundingScalePrintTotal)::character varying
- END AS ppn,
- vDownPaymentAmountWithTax::character varying AS dp_amount,
- CASE WHEN vRoundingModeNonTax = 'RD' THEN
- TRUNC(SUM(G.nett_item_amount), vRoundingScalePrintTotal)::character varying
- ELSE
- ROUND(SUM(G.nett_item_amount), vRoundingScalePrintTotal)::character varying
- END AS dpp,
- CASE WHEN vRoundingModeNonTax = 'RD' THEN
- TRUNC(SUM(G.nett_item_amount + G.tax_amount) - vDownPaymentAmountWithTax, vRoundingScalePrintTotal)::character varying
- ELSE
- ROUND(SUM(G.nett_item_amount + G.tax_amount) - vDownPaymentAmountWithTax, vRoundingScalePrintTotal)::character varying
- END AS total,
- UPPER(terbilang(
- CASE WHEN vRoundingModeNonTax = 'RD' THEN
- TRUNC(SUM(G.nett_item_amount + G.tax_amount) - vDownPaymentAmountWithTax, vRoundingScalePrintTotal)
- ELSE
- ROUND(SUM(G.nett_item_amount + G.tax_amount) - vDownPaymentAmountWithTax, vRoundingScalePrintTotal)
- END
- )) AS terbilang,
- UPPER(f_get_report_parameter_config_value(pTenantId, vAll, vOuBuId, 'email')) AS ou_email,
- UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note1')) AS footer_note_1,
- UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note2')) AS footer_note_2,
- UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note3')) AS footer_note_3,
- UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note4')) AS footer_note_4,
- UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note5')) AS footer_note_5,
- UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note6')) AS footer_note_6,
- UPPER(f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, 'note7')) AS footer_note_7,
- UPPER(f_get_username(pUserId)) AS username,
- pDatetime AS datetime,
- UPPER(pNamaPenanggungJwb) AS penanggung_jawab_name,
- UPPER(pSikaPenanggungJwb) AS penanggung_jawab_sika
- FROM sl_invoice_temp A
- INNER JOIN m_partner C ON A.tenant_id = C.tenant_id AND A.partner_id = C.partner_id
- INNER JOIN sl_so E ON A.tenant_id = E.tenant_id AND A.ref_id = E.so_id
- LEFT OUTER JOIN m_partner F ON A.tenant_id = F.tenant_id AND A.salesman_id = F.partner_id
- INNER JOIN tr_sl_invoice_item G ON A.tenant_id = G.tenant_id AND G.session_id = pSessionId
- LEFT JOIN m_partner_address H ON H.partner_id = A.partner_id AND H.flg_official = vYes
- LEFT JOIN m_partner_npwp I ON I.partner_id = A.partner_id AND I.active = vYes
- LEFT JOIN sl_invoice_temp_tax J ON J.invoice_temp_id = A.invoice_temp_id
- WHERE A.tenant_id = pTenantId
- AND A.invoice_temp_id = pInvoiceId
- GROUP BY A.invoice_temp_id, C.partner_id, E.so_id, F.partner_id, H.partner_address_id, I.npwp_id, J.tax_no;
- RETURN NEXT pRefHeader;
- Open pRefDetail FOR
- SELECT row_number() OVER(order by product_code, product_name) as line_no,
- UPPER(product_code) AS product_code,
- UPPER(product_name) AS product_name, SUM(qty_so)::character varying AS qty, UPPER(uom_name) uom_name,
- dpp_ppn_in_so::character varying AS unit_price, SUM(gross_item_amount - (regular_disc_amount + promo_disc_amount))::character varying AS amount,
- UPPER(serial_number) as serial_number, product_expired_date,
- discount_pct_so
- FROM tr_sl_invoice_item
- WHERE session_id = pSessionId
- AND tenant_id = pTenantId
- GROUP BY product_code, product_name, uom_name, dpp_ppn_in_so, serial_number, product_expired_date,
- discount_pct_so
- HAVING SUM(qty_so) > 0
- ORDER BY product_code, product_name;
- RETURN NEXT pRefDetail;
- DELETE FROM tr_sl_invoice_item WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement